1 #查询所有的课程的名称以及对应的任课老师姓名
2 SELECT cname '课程',TNAME '教师名' FROM COURSE C,TEACHER T WHERE C.teacher_id=T.tid;
3
4 #查询学生表中男女生各有多少人
5 SELECT GENDER '性别',COUNT(GENDER) '人数' FROM STUDENT1 GROUP BY(GENDER);
6
7 #查询物理成绩等于100的学生的姓名
8 SELECT SNAME '姓名',CNAME '课程',NUM '分数' from STUDENT1 S,COURSE C,SCORE1 SC WHERE S.sid=SC.student_id AND C.cid=SC.course_id AND CNAME='物理' AND NUM=100;
9
10 #查询平均成绩大于八十分的同学的姓名和平均成绩
11 SELECT SNAME '姓名',AVG(NUM) '平均分' FROM STUDENT1 S,SCORE1 SC WHERE S.sid=SC.student_id GROUP BY(SNAME) HAVING AVG(NUM)>80;
12
13 #查询所有学生的学号，姓名，选课数，总成绩
14 SELECT S.SID '学号',SNAME '姓名',COUNT(course_id) '选课数',SUM(NUM) '总成绩' FROM STUDENT1 S,SCORE1 SC WHERE S.sid=SC.student_ID GROUP BY(student_ID);
15
16 #查询姓李老师的个数
17 SELECT COUNT(TNAME) '个数' FROM TEACHER WHERE TNAME LIKE '李%';
18
19 #查询没有报李平老师课的学生姓名
20 select sname from student1 where sid not in(select student_id from score1 where course_id in(select course_id from course where teacher_id=(select tid from teacher where tname='李平老师')));
21
22 #查询物理课程比生物课程高的学生的学号
23 select S1.student_id '学号' from (select student_id,num from score1 WHERE course_id=(select cid from course where cname='物理'))AS S1,(select student_id,num from score1 WHERE course_id=(select cid from course where cname='生物'))AS S2 WHERE S1.student_id=S2.student_id AND S1.num>S2.num;
24
25 #查询没有同时选修物理课程和体育课程的学生姓名
26 SELECT SNAME '姓名' FROM STUDENT1 WHERE sid NOT IN(SELECT student_id FROM SCORE1 WHERE student_id IN(SELECT student_id FROM SCORE1 WHERE course_id=(SELECT cid FROM course WHERE CNAME='物理')) AND course_id=(SELECT cid FROM course WHERE CNAME='体育'));
27
28 #查询挂科超过两门(包括两门)的学生姓名和班级
29 SELECT SNAME '姓名',caption '班级' FROM student1 S,CLASS C,score1 S1 WHERE S.class_id=C.cid AND S.sid=S1.student_id AND student_id IN(SELECT student_id FROM score1 WHERE NUM<60) HAVING COUNT(student_id);
30
31 #查询选修了所有课程的学生姓名
32 SELECT SNAME '姓名' FROM STUDENT1 WHERE SID IN(SELECT student_id FROM SCORE1 GROUP BY(student_id) HAVING COUNT(course_id) = (SELECT count(cid) FROM course));
33
34 #查询李平老师教的课程的所有成绩记录
35 SELECT NUM '成绩' FROM score1 WHERE course_id IN(SELECT cid FROM course WHERE teacher_id=(SELECT TID FROM TEACHER WHERE TNAME='李平老师'));
36
37 #查询全部学生都选修了的课程号和课程名
38 SELECT CID '课程号',CNAME '课程名' FROM course WHERE CID IN(SELECT course_id FROM score1 GROUP BY(course_id) HAVING COUNT(student_id)=(SELECT count(Sid) FROM STUDENT1));
39
40 #查询每门课程被选修的次数
41 SELECT course_id '课程号',COUNT(student_id) '次数' FROM score1 GROUP BY(course_id);
42
43 #查询只选修了一门课程的学生姓名和学号
44 SELECT SNAME '姓名',SID '学号' FROM STUDENT1 WHERE SID IN(SELECT student_id FROM score1 GROUP BY(student_id) HAVING COUNT(course_id)=1);
45
46 #查询所有学生考出的成绩并按从高到低排序（成绩去重）
47 SELECT DISTINCT NUM FROM score1 ORDER BY NUM DESC;
48
49 #查询平均成绩大于85的学生姓名和平均成绩
50 SELECT SNAME '姓名',AVG(NUM) '平均分' FROM STUDENT1 S,score1 SC WHERE S.sid=SC.student_id AND S.SID IN(SELECT student_id FROM score1 GROUP BY(student_id) HAVING AVG(NUM)>85);
51
52 #查询生物成绩不及格的学生姓名和对应生物分数
53 SELECT SNAME '姓名',NUM '分数' FROM STUDENT1 S,score1 SC,course C WHERE S.sid=SC.student_id AND SC.course_id=C.cid AND c.cname = '生物' AND sc.num < 60;
54
55 #查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
56 SELECT SNAME '姓名' FROM STUDENT1 WHERE sid=(SELECT student_id FROM score1 WHERE course_id IN(SELECT CID FROM course WHERE teacher_id IN(SELECT TID FROM TEACHER WHERE TNAME='李平老师')) GROUP BY(student_id) ORDER BY AVG(num) DESC LIMIT 1);
57
58 #查询每门课程成绩最好的前两名学生姓名
59 SELECT
60 SNAME '姓名',S.SID '学号',NUM '分数'
61 FROM
62 student1 S
63 JOIN (
64 (SELECT * FROM score1 WHERE course_id = 1 ORDER BY NUM DESC LIMIT 2 ) UNION
65 ( SELECT * FROM score1 WHERE course_id = 2 ORDER BY NUM DESC LIMIT 2 ) UNION
66 ( SELECT * FROM score1 WHERE course_id = 3 ORDER BY NUM DESC LIMIT 2 ) UNION
67 ( SELECT * FROM score1 WHERE course_id = 4 ORDER BY NUM DESC LIMIT 2 ))
68 AS a WHERE s.sid=a.student_id;
69
70 #查询不同课程但成绩相同的学号，课程号，成绩
71 select DISTINCT S.SID,SC.course_id '课程号',SC.NUM '分数' FROM STUDENT1 S,score1 SC,score1 SC1 WHERE S.sid=SC.student_id AND SC.course_id<>SC1.course_id AND SC.num=SC1.num ORDER BY SC.NUM DESC,SC.course_id;
72
73 #查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
74 SELECT S.SNAME '姓名', GROUP_CONCAT(C.cname) '课程名' FROM student1 S, course C,score1 SC,teacher T WHERE S.sid=SC.student_id AND SC.course_id=C.cid AND C.teacher_id=T.tid AND tid NOT IN(SELECT tid FROM teacher WHERE tname='叶平%') GROUP BY s.sname;
75
76 #查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
77 select SID '学号',SNAME '姓名' FROM STUDENT1 WHERE sid IN(SELECT student_id FROM score1 WHERE course_id IN(SELECT cid FROM course WHERE cid=1));
78
79 #任课最多的老师中学生单科成绩最高的学生姓名
80 SELECT ST.sname,MAX(SC.num) FROM student1 ST,score1 SC WHERE ST.sid=SC.student_id and course_id=(select a.cid from (SELECT *,COUNT(teacher_id) from course GROUP BY(teacher_id) ORDER BY COUNT(teacher_id) desc LIMIT 1)AS a);
