Demo entry 6687679

sql

   

Submitted by anonymous on Dec 30, 2017 at 07:30
Language: MySQL. Code size: 6.2 kB.

#1.	查询以‘数’开头的课程的详细情况(对应的英文);
 SELECT
  *
FROM
  `course`
WHERE course.Cname LIKE '数%';

#2.	列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩;
 SELECT
  `student`.`Sno`,
  `student`.`Sname`,
  `student`.`Sdept`,
  `SC`.`Cno`,
  `SC`.`Grade`
FROM
  `student`,
  `SC`
WHERE `student`.`Sno` = `SC`.`Sno`
  AND `SC`.`Cno` IN
  (SELECT
    `course`.`Cno`
  FROM
    `course`
  WHERE `course`.`Cname` = '数学'
    OR `course`.`Cname` = '大学英语');

#3.	查询缺少成绩的所有学生的详细情况;
 SELECT
  *
FROM
  `student`
  INNER JOIN `sc`
    ON `sc`.`Sno` = `student`.`Sno`
    AND `sc`.`Grade` IS NULL;

#4.	查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息;
 SELECT
  *
FROM
  `student`
WHERE NOT `student`.`Sage` IN
  (SELECT
    `Sage`
  FROM
    `student`
  WHERE `Sname` = '张力');

#5.	查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩;
 SELECT
  `student`.`Sno`,
  `student`.`Sname`,
  AVG(`sc`.`Grade`) AS `Average Grade`
FROM
  `student`,
  `sc`
WHERE `student`.`Sno` = `sc`.`Sno`
GROUP BY `student`.`Sname`
HAVING AVG(`sc`.`Grade`) >
  (SELECT
    AVG(`sc`.`Grade`)
  FROM
    `sc`,
    `student`
  WHERE `sc`.`Sno` = `student`.`Sno`
    AND `student`.`Sname` = '张力');

#6.	查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号;
 SELECT
  `student`.`Sno`,
  `student`.`Sname`,
  `sc`.`Cno`
FROM
  `student`,
  `sc`
WHERE `student`.`Sno` = `sc`.`Sno`
  AND `student`.`Sno` IN
  (SELECT
    `student`.`Sno`
  FROM
    `student`,
    `sc`
  WHERE `Cno` IN
    (SELECT
      `Cno`
    FROM
      `sc`,
      `student`
    WHERE `Sname` = '张力'
      AND `student`.`Sno` = `sc`.`Sno`)
    AND `student`.`Sno` = `sc`.`Sno`);

#7.	只选修“数据库”和“数据结构”两门课程的学生的基本信息;
 SELECT DISTINCT
  `student`.*
FROM
  `student`,
  `sc`
WHERE `student`.`Sno` IN
  (SELECT
    `Sno`
  FROM
    `sc`,
    `course`
  WHERE Cname = '数据库'
    AND `sc`.`Cno` = `course`.`Cno`)
  AND `student`.`Sno` IN
  (SELECT
    `Sno`
  FROM
    `sc`,
    `course`
  WHERE Cname = '数据结构'
    AND `sc`.`Cno` = `course`.`Cno`)
  AND `student`.`Sno` = `sc`.`Sno`;

#8.	列出所有课程被选修的详细情况,包括课程号、课程名、学号、姓名及成绩;
 SELECT
  `course`.`Cno`,
  `course`.`Cname`,
  `sc`.`Sno`,
  `sc`.`Cno`,
  `student`.`Sname`,
  `sc`.`Grade`
FROM
  `course`,
  `sc`,
  `student`
WHERE `course`.`Cno` = `sc`.`Cno`
  AND `student`.`Sno` = `sc`.`Sno` 
  
 #9.	检索所学课程包含学生‘张向东’所学课程的学生学号、姓名;
   SELECT DISTINCT
    `student`.`Sno`,
    `student`.`Sname`
  FROM
    `student`,
    `sc`
  WHERE `student`.`Sno` = `sc`.`Sno`
    AND `sc`.`Cno` IN
    (SELECT
      `sc`.`Cno`
    FROM
      `student`,
      `sc`
    WHERE `student`.`Sno` = `sc`.`Sno`
      AND `student`.`Sname` = '张向东');

#10.	使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名;
 SELECT
  `Sno`,
  `Sname`
FROM
  `student`
WHERE `Sno` IN
  (SELECT
    `Sno`
  FROM
    `sc`,
    `course`
  WHERE `sc`.`Cno` = `course`.`Cno`
    AND `course`.`Cname` = '数据结构');

#11.	使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系;
 SELECT
  `student`.`Sname`,
  `student`.`Sage`,
  `student`.`Sdept`
FROM
  `student`
WHERE `student`.`Sage` < SOME
  (SELECT
    `Sage`
  FROM
    `student`
  WHERE `Sdept` = 'cs')
  AND `Sdept` != 'cs';

#12.	使用SOME、ALL 查询,列出其他院系中比CS系所有学生年龄小的学生;
 SELECT
  `student`.`Sname`
FROM
  `student`
WHERE `student`.`Sage` < ALL
  (SELECT
    `Sage`
  FROM
    `student`
  WHERE `Sdept` = 'cs')
  AND `student`.`Sdept` <> 'cs';

#13.分别使用连接查询和嵌套查询,列出与‘张力’在一个院系的学生的信息;
#(1)连接
 SELECT
  `stu1`.*
FROM
  `student` AS `stu1`,
  `student` AS `stu2`
WHERE `stu1`.`Sdept` = `stu2`.`Sdept`
  AND `stu2`.`Sname` = '张力';

#(2)嵌套
 SELECT
  *
FROM
  `student`
WHERE `Sdept` IN
  (SELECT
    `Sdept`
  FROM
    `student`
  WHERE `Sname` = '张力');

#14.	使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集;
 SELECT
  `student`.`Sname`
FROM
  `student`
WHERE `student`.`Sdept` = 'cs' #INTERSECT
#EXCEPT
#mysql不支持差集、交集,所以这个结果没有测试
   SELECT
    `student`.`Sname`
  FROM
    `student`
  WHERE `student`.`Sage` <= 19;

#换用左连接的方式代替差集
#方式是先用视图创建两个已经做好了上述操作的表,再进行操作
 CREATE VIEW `stu1` AS
(SELECT
  *
FROM
  `student`
WHERE `student`.`Sdept` = 'cs');

CREATE VIEW `stu2` AS
(SELECT
  *
FROM
  `student`
WHERE `student`.`Sage` <= 19);

`student`
SELECT
  `stu1`.*
FROM
  `stu1`
  LEFT JOIN `stu2` USING (`Sno`)
WHERE `stu2`.`Sno` IS NULL #换用左连接的方式代替交集
   SELECT
    `stu1`.*
  FROM
    `stu1`
    LEFT JOIN `stu2` USING (`Sno`)
  WHERE `stu2`.`Sno` IS NOT NULL #15.	使用集合查询列出选修课程1的学生集合与选修课程2的学生集合的交集;
     SELECT
      *
    FROM
      `sc`
    WHERE `Cno` = 1;

#intersect
#mysql没有交集运算
 SELECT
  `Sno`
FROM
  `sc`
WHERE `Cno` = 2;

#同样改为先创建视图然后再join选不空的值
 CREATE VIEW `stu_15_1` AS
SELECT
  *
FROM
  `sc`
WHERE `Cno` = 1;

CREATE VIEW `stu_15_2` AS
SELECT
  *
FROM
  `sc`
WHERE `Cno` = 2;

SELECT
  `stu_15_1`.`Sno`,
  `student`.`Sname`
FROM
  `stu_15_1`
  INNER JOIN `stu_15_2` USING (`Sno`)
  INNER JOIN `student` USING (`Sno`)
WHERE NOT `stu_15_2`.`Sno` IS NULL;

#验证写的是不是对的	
 SELECT
  *
FROM
  `sc` AS `sc1`,
  `sc` AS `sc2`
WHERE `sc1`.`Sno` = `sc2`.`Sno`
  AND `sc1`.`Cno` = 1
  AND `sc2`.`Cno` = 2;

#16.	列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩;
 SELECT
  `student`.`Sno`,
  `student`.`Sname`,
  `student`.`Sdept`,
  `student`.`Sno`,
  `sc`.`Grade`
FROM
  `student`
  INNER JOIN `sc` USING (`Sno`)
  INNER JOIN `course` USING (`Cno`)
WHERE `course`.`Cname` IN ('数学', '大学英语');

#17.	按照“学号,姓名,所在院系,已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和;
 SELECT
  `student`.`Sno`,
  `student`.`Sname`,
  `student`.`Sdept`,
  SUM(`course`.`Ccredit`) AS `studied_credit`
FROM
  `student`
  INNER JOIN `sc` USING (`Sno`)
  INNER JOIN `course` USING (`Cno`)
WHERE `sc`.`Grade` >= 60
GROUP BY `student`.`Sno`;

#用简写的形式
 SELECT
  `student`.`Sno`,
  `student`.`Sname`,
  `student`.`Sdept`,
  SUM(`course`.`Ccredit`) AS `studied_credit`
FROM
  `student`,
  `sc`,
  `course`
WHERE `grade` > 60
  AND `student`.`Sno` = `sc`.`Sno`
  AND `course`.`Cno` = `sc`.`Cno`
GROUP BY `student`.`Sno`;

#18.	查询只被一名学生选修的课程的课程号、课程名;
 SELECT
  `sc`.`Cno`,
  `course`.`Cname`
FROM
  `sc`
  INNER JOIN `course` USING (`Cno`)
GROUP BY `sc`.`Cno`
HAVING COUNT(*) = 1;

This snippet took 0.01 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).