MySQL -- 查询案例（一）

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号

SELECT A.sid FROM
(SELECT sid , score from sc WHERE cid = 001) A,
(SELECT sid , score from sc WHERE cid = 002) B
WHERE (A.score > B.score) AND (A.sid = B.sid);

2、查询平均成绩大于60 分的同学的学号和平均成绩

SELECT sid , AVG(score) FROM sc
GROUP BY sid
HAVING AVG(score) > 60;

3、查询所有同学的学号、姓名、选课数、总成绩

SELECT sc.sid , sname , sc.cid , SUM(score) from SCINNER JOIN student
ON sc.sid = student.sid
GROUP BY sid;

4、查询姓“刘”的老师的个数

SELECT COUNT(tName) FROM teacher
WHERE tName LIKE("刘%");

5、查询没学过“李老师”课的同学的学号、姓名

SELECT sid , sname from student
WHERE sid NOT IN
(SELECT sid from sc WHERE cid IN
(SELECT cid from course WHERE tid IN
(SELECT tid from teacher WHERE tName = '李老师')))；

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名

SELECT student.sid , student.sname from student , sc
WHERE (student.sid = sc.sid) AND (sc.cid = 001) AND (EXISTS(SELECT * from sc as sc2
WHERE sc2.sid = sc.sid AND sc2.cid = 002));

7、查询学过“李老师”所教的所有课的同学的学号、姓名

(SELECT sid from sc WHERE cid IN
(SELECT cid from course WHERE tid IN
(SELECT tid from teacher WHERE tName = '李老师')));

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名

SELECT student.sid , student.sname FROM student,
(SELECT cid , sid , score FROM sc WHERE cid = 001) s1,
(SELECT cid , sid , score FROM sc WHERE cid = 002) s2
WHERE (s1.score > s2.score) AND (s2.sid = student.sid) AND (student.sid = s1.sid);