实验4 多表查询-连接查询和嵌套查询
二、背景知识
同实验3。
四、实验步骤
1．求学号为‘20022037’的同学的每门课的成绩，输出格式为：学号，课程名，课程成绩
SELECT SNO AS 学号, rtrim(CNAME) AS 课程名 ,GRADE AS 课程成绩 FROM sc,course
WHERE sc.CNO=course.CNO AND SNO='20022037'
2．查询每个学生的每门课程的成绩，要求输出学号，课程名，成绩
SELECT SNO,CNAME,GRADE FROM sc, course
WHERE sc.CNO=course.CNO
3．查询每个学生的每门课程的成绩，要求输出学号，姓名，课程名，成绩
SELECT student.SNO,SNAME,CNAME,GRADE FROM sc, course,student
WHERE sc.CNO=course.CNO AND student.SNO=sc.SNO 4．查询选修了'线性代数'课程的学生学号、姓名
SELECT student.SNO,SNAME FROM sc,course,student
WHERE sc.SNO=student.SNO AND sc.CNO=course.CNO AND course.CNAME='线性代数'
5．查询'线性代数'的所有授课班级的平均成绩，并列出授课班号、教师名、平均成绩，且按平均成绩排序
SELECT sc.CNO,course.TNAME,AVG(GRADE) AS 平均成绩 FROM sc,course
WHERE sc.CNO=course.CNO AND CNAME='线性代数' GROUP BY sc.CNO,course.TNAME ORDER BY AVG(GRADE)
6．使用多表连接方法，查询和学号为‘20000156’的同学同年同月同日出生的所有学生的学号、姓名、生日。
SELECT a.SNO,a.SNAME,a.BIRTHDAY FROM student AS a, student b
WHERE a.BIRTHDAY =b.BIRTHDAY AND b.SNO='20000156'
7．使用嵌套查询方法，查询和学号为‘20000156’的同学同年同月出生的所有学生的学号、姓名、生日。
SELECT SNO,SNAME,BIRTHDAY FROM student
WHERE YEAR(BIRTHDAY)+MONTH(BIRTHDAY)=
(
SELECT YEAR(BIRTHDAY)+MONTH(BIRTHDAY) FROM student WHERE SNO='20000156' )
说明：该嵌套子查询只执行一次，整个查询效率比第6题快
8．使用嵌套查询方法，查询“赵蓉”教师任课的学生成绩，并按成绩递增排列
SELECT CNO,SNO,GRADE FROM sc WHERE CNO IN
( SELECT CNO FROM course WHERE TNAME='赵蓉' ) ORDER BY GRADE
说明：该嵌套子查询只执行一次，执行效率比多表连接查询效率高
9. 使用嵌套查询方法，查询课程最低分大于70，最高分小于90的学生学号和姓名 SELECT SNO,SNAME FROM student WHERE SNO IN
( SELECT SNO FROM sc GROUP BY sc.SNO
HAVING MIN(GRADE)>70 AND MAX(GRADE)<90 )
10．用嵌套法查询选修了“线性代数“的学生学号和姓名
SELECT SNO,SNAME FROM student WHERE SNO IN
(
SELECT SNO FROM sc WHERE CNO IN
( SELECT CNO FROM course WHERE CNAME='线性代数' ) )
说明：该查询使用了两层嵌套查询，查询次序为从里向外执行
11．从选修’218801’课程的同学中，选出成绩高于’季莹’的学生的学号和成绩
SELECT SNO,GRADE FROM sc
WHERE CNO='218801' AND GRADE >
(
SELECT GRADE FROM sc
WHERE CNO='218801' AND SNO= ( SELECT SNO FROM student WHERE SNAME='季莹 ' ) )
说明：先执行子查询，再执行主查询，该子查询只执行一次 12．查询成绩比该课程平均成绩低的学生成绩表
SELECT SNO,CNO,GRADE FROM sc AS a WHERE GRADE<
( SELECT AVG(GRADE) FROM sc AS b WHERE a.CNO=b.CNO )
说明：主查询在判断每个待选行时，唤醒子查询，告诉它该学生选修的课程号，并由子查询计算该课程的平均成绩，然后将该学生的成绩与平均成绩进行比较，找出符合条件的记录，这种子查询称为相关子查询。
13．查询选修了'线性代数'这门课程的学生学号
SELECT SNO,SNAME FROM student WHERE EXISTS
( SELECT *
FROM sc ,course
WHERE sc.CNO=course.CNO AND student.SNO=sc.SNO AND course.CNAME='
线性代数'
)
说明：主查询在判断每个学生时，执行子查询，根据主查询中的当前行的学号，在子查询中，从头到尾进行扫描，判断是否存在该学生的选课记录，如果存在这样的行，EXISTS子句返回真，主查询选中当前行；如果子查询未找到这样的行，EXISTS子句返回假，主查询不选中当前行。
14．查询所有学生都选修的课程名
SELECT CNAME FROM course WHERE not EXISTS
(
SELECT * FROM student WHERE not EXISTS ( SELECT * FROM sc
WHERE SNO=student.SNO AND CNO=course.CNO ) )
15．查询选修了'线性代数'课程或'英语口语'课程的学生学号、姓名。
SELECT DISTINCT student.SNO,SNAME FROM sc,course,student
WHERE sc.SNO=student.SNO AND sc.CNO=course.CNO AND
(course.CNAME='线性代数' OR course.CNAME='英语口语')
16. 用集合操作符UNION查询选修了'线性代数'课程或'英语口语'课程的学生学号、姓名。
SELECT student.SNO,SNAME FROM sc,course,student
WHERE sc.SNO=student.SNO AND sc.CNO=course.CNO AND course.CNAME='线性代数' UNION
SELECT student.SNO,SNAME FROM sc,course,student WHERE sc.SNO=student.SNO AND sc.CNO=course.CNO AND course.CNAME='英语口语'
说明： SQLSERVER支持UNION并操作符，本题也可以用IN嵌套查询来实现 17．查询选修了'218801'课程但没有选修'216301'课程的学生学号。
SELECT SNO，SNAME FROM student WHERE SNO IN
( SELECT SNO FROM sc WHERE CNO='218801' ) AND SNO NOT IN
( SELECT SNO FROM sc WHERE CNO='216301' )
说明：SQLSERVER不支持SQL中的差操作符
18．求同时选修'218801'课程和'216301'课程的学生学号、姓名。 SELECT SNO FROM sc
WHERE CNO='218801' AND SNO IN
( SELECT SNO FROM sc
WHERE CNO='216301' )
19．查询所有学生及其选课信息
SELECT student.SNO,SNAME,CNO,GRADE FROM student left outer JOIN sc ON student.SNO=sc.SNO 20．创建课程平均分视图
CREATE VIEW 查询课程平均分 AS SELECT (AVG(GRADE)) AS 平均分, CNAME FROM sc,course
WHERE sc.CNO=course.CNO GROUP BY CNAME
21．以列的方式统计每门课程的分数段人数。分数段为：不及格、60-70、70-80、80-90、90-100
(SELECT CNAME ,'不及格' AS fsd ,COUNT(*) AS rs FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE <60 GROUP BY CNAME) UNION
(SELECT CNAME ,'60-70' AS fsd , COUNT (*) FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 60 AND 70 GROUP BY CNAME) UNION
(SELECT CNAME ,'70-80' AS fsd , COUNT (*) FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 70 AND 80 GROUP BY CNAME) UNION
(SELECT CNAME ,'80-90' AS fsd , COUNT (*) FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 80 AND 90 GROUP BY CNAME) UNION
(SELECT CNAME ,'90-100' AS fsd , COUNT (*) FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 90 AND 100 GROUP BY CNAME)