已知有如下4张表：
学生表：STUDENT(S#,SNAME,SAGE,SSEX)
课程表：COURSE(C#,CNAME,T#)
成绩表：SC(S#,C#,SCORE)
教师表：TEACHER(T#,TNAME)
其中，S#代表学号，SNAME代表学生姓名，SAGE代表学生年龄，SSEX代表学生性别，C#代表课程编号，CNAME代表课程名字，T#代表教师编号，TNAME代表教师名字，SCORE代表成绩。
根据以上信息按照下面要求写出对应的SQL语句。
1、查询课程编号为“001”的课程比“002”的课程成绩高的所有学生的学号
2、查询平均成绩大于60分的学生的学号和平均成绩
3、查询所有学生的学号、姓名、选课数、总成绩
4、查询姓“李”的老师的个数
5、查询没学过“李美玲”老师课的学生的学号、姓名
6、查询学过编号为“001”的课程并且也学过编号为“002”的课程的学生的学号、姓名
7、查询学过“李多多”老师所教的所有课的学生的学号、姓名
8、查询课程编号为“002”的总成绩
9、查询所有课程成绩小于60分的学生的学号、姓名
10、查询没有学全所有课的学生的学号、姓名
11、查询至少有一门课与学号为“1001”的学生所学课程相同的学生的学号和姓名
12、查询所学课程和学号为“001”的学生所有课程一样的其他学生的学号和姓名
13、把“SC”表中“李多多”老师教的课的成绩都更改为此课程的平均成绩
14、查询没有学习过“1002”号课程的的学生的学号和姓名
15、删除学习“李多多”老师课的SC表记录
16、向SC表中插入一些记录这些记录要求符合以下条件：没有上过编号为“003”课程的学生的学号、编号为002的课程的平均成绩
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，其中数据库的c#为004，企业管理的c#为001，英语的c#为006，按如下形式显示：
学生ID 数据库 企业管理 英语 有效课程数 有效平均成绩
18、查询各科成绩最高和最低的分，以如下形式显示课程ID最高分最低分
19、按各科平均成绩从低到高和及格率的百分数从高到低排列，以如下形式显示：
课程号 课程名 平均成绩 及格百分数
20、查询如下课程平均成绩和及格率的百分数（用1行显示），其中企业管理为001，马克思为002，UML为003，数据库为004
21、查询不同老师所教不同课程平均分从高到低显示
22、查询如下课程成绩第3名到第6名的学生成绩单，其中企业管理为001，马克思为002，UML为003，数据库为004，以如下形式显示：
学生ID 学生姓名 企业管理 马克思 UML 数据库 平均成绩
23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
24、查询学生平均成绩及其名次
25、查询各科成绩前三名的记录（不考虑成绩并列情况）
。C、D
。本题考察SQL的编写能力，对于这类型的题目，只要把4张表之间的关联关系搞清楚了，编写对应的SQL语句就比较容易了，本题给出的四张表之间的关系如下图所示：
1、答案：首先查询课程编号分别为001和002的所有学生的学号及其分数作为内嵌视图A和B，然后将A和B通过学号关联，过滤条件就是A的分数大于B的分数，最终SQL如下：
SELECT A.S#
FROM (SELECT S#, SCORE FROM SC WHERE C# = '001') A,
(SELECT S#, SCORE FROM SC WHERE C# = '002') B
WHERE A.SCORE > B.SCORE
AND A.S# = B.S#;
2、答案：该内容通过SC表即可获取，按照SC表的S#分组后即可求出平均成绩，最后通过HAVING子句来过滤平均分大于60的学生，最终SQL如下：
SELECT S#, AVG(SCORE)
FROM SC
GROUP BY S#
HAVING AVG(SCORE) > 60;
3、答案：学生姓名通过STUDENT表获取，成绩通过SC表获取，考察的是COUNT和GROUP BY函数，最终SQL如下：
SELECT STUDENT.S#, STUDENT.SNAME, COUNT(SC.C#), SUM(SCORE)
FROM STUDENT
LEFT OUTER JOIN SC
ON STUDENT.S# = SC.S#
GROUP BY STUDENT.S#, SNAME;
4、答案：老师通过TEACHER表即可查询，考察模糊查询，最终SQL如下：
SELECT COUNT(DISTINCT(TNAME))
FROM TEACHER
WHERE TNAME LIKE '李%';
5、答案：首先查询学习过“李美玲”老师课的学生的学号作为子查询，而“李美玲”老师涉及到TEACHER表，TEACHER表要和学生有关联必须通过课程和成绩表，最终SQL如下：
SELECT STUDENT.S#, STUDENT.SNAME
FROM STUDENT
WHERE S# NOT IN (SELECT DISTINCT (SC.S#)
FROM SC, COURSE, TEACHER
WHERE SC.C# = COURSE.C#
AND TEACHER.T# = COURSE.T#
AND TEACHER.TNAME = '李美玲');
6、答案：这道题需要注意的是“且”这个关键词，说明查询出来的学生即学习过001也学习过002的课程，最终SQL如下：
SELECT STUDENT.S#, STUDENT.SNAME
FROM STUDENT, SC
WHERE STUDENT.S# = SC.S#
ANDSC.C# = '001'
AND EXISTS (SELECT *
FROM SC AS SC_2
WHERE SC_2.S# = SC.S#
AND SC_2.C# = '002');
错误答案：
SELECT STUDENT.S#, STUDENT.SNAME
FROM STUDENT, SC
WHERE STUDENT.S# = SC.S#
AND SC.C# IN ('001' ，'002');
7、答案：这道题和第6题比较相似，需要理解题意，题目说的是查询学过“李多多”老师所教的所有课的同学的学号、姓名，举个例子，比如“李多多”老师教过语文和数学，那么就得找出哪些同学同时学习了语文和数学这2门课程，最终SQL如下：
SELECT S#, SNAME
FROM STUDENT
WHERE S# IN (SELECT S#
FROM SC, COURSE, TEACHER
WHERE SC.C# = COURSE.C#
AND TEACHER.T# = COURSE.T#
AND TEACHER.TNAME = '李多多'
GROUP BY S#
HAVING COUNT(SC.C#) = (SELECT COUNT(C#)
FROM COURSE, TEACHER
WHERE TEACHER.T# = COURSE.T#
AND TNAME = '李多多'));
错误答案：
SELECT S#, SNAME
FROM STUDENT
WHERE S# IN (SELECT S# FROM SC, COURSE, TEACHER
WHERESC.C# = COURSE.C#
AND TEACHER.T# = COURSE.T#
AND TEACHER.TNAME = '李多多');
8、答案：本题考查SUM函数，最终SQL如下所示：
SELECT SUM(SCORE) FROM SC SC_2 WHERE SC_2.C# = '002';
9、答案：涉及到学生表和成绩表，比较简单，最终SQL如下：
SELECT S#, SNAME
FROM STUDENT
WHERE S# NOT IN (SELECT STUDENT.S#
FROM SC
WHERE SCORE >= 60);
10、答案：没有学全所有课，翻译一下就是学生学习的课程数小于总的课程数，最终SQL如下：
SELECT STUDENT.S#, STUDENT.SNAME
FROM STUDENT, SC
WHERE STUDENT.S# = SC.S#
GROUP BY STUDENT.S#, STUDENT.SNAME
HAVING COUNT(C#) < (SELECT COUNT(C#) FROM COURSE);
11、答案：首先找出学号为1001的学生学习过的课程，然后根据这些课程号就可以找到有哪些学生学习过部分1001学生学习过的课程，最终SQL如下：
SELECT S#, SNAME
FROM STUDENT, SC
WHERE STUDENT.S# = SC.S#
AND C# IN (SELECT C# FROM SC WHERE S# = '1001');
SELECT SC.S#, SNAME;
12、答案：首先找出学号为1001的学生学习过的课程，然后根据这些课程号和所学课程总数就可以找到有哪些同学学习过和他一样的课程，最终SQL如下：
SELECT STUDENT.S#, STUDENT.SNAME
FROM STUDENT, SC
WHERE STUDENT.S# = SC.S#
AND C# IN (SELECT C# FROM SC WHERE S# = '001')
GROUP BY STUDENT.S#, STUDENT.SNAME
HAVING COUNT(C#) = (SELECT COUNT(C#) FROM SC WHERE S# = '001');
13、答案：首先找到李多多老师教过哪些课程及其课程的平均成绩，然后根据课程号关联成绩表进行更新，最终SQL如下：
UPDATE SC
SET SCORE =
(SELECT AVG(SC_2.SCORE)
FROM COURSE, TEACHER, SC SC_2
WHERE COURSE.T# = TEACHER.T#
AND COURSE.C# = SC_2.C#
AND SC_2.C# =SC.C#
AND TEACHER.TNAME = '李多多'
GROUP BY COURSE.C#)
WHERE EXISTS(SELEC 1 FROM COURSE,
TEACHER,
SC SC_2 WHERE COURSE.T# = TEACHER.T#
AND COURSE.C# = SC_2.C#
AND SC_2.C# =SC.C#
AND TEACHER.TNAME = '李多多'
GROUP BY COURSE.C#
);
14、答案：本题比较简答，最终SQL如下：
SELECT STUDENT.S#, STUDENT.SNAME
FROM STUDENT, SC
WHERE STUDENT.S# = SC.S#
AND C# NOT IN (SELECT C# FROM SC WHERE S# = '1002');
15、答案：本题比较简答，最终SQL如下：
DELETE FROM SC
WHERE SC.C# IN
(SELECT COURSE.C# FROM COURSE C, TEACHER T WHERE C.T# = T.T# AND T.TNAME='李多多');
16、答案：2个点，002课程的平均成绩和没有学习过003课程的学生，最终SQL如下：
INSERT INTO SC(S#,C#,SCORE)
SELECT S#, '002', (SELECT AVG(SCORE) FROM SC WHERE C# = '002')
FROM STUDENT
WHERE S# NOT IN (SELECT S# FROM SC WHERE C# = '003');
17、答案：查看标量子查询，最终SQL如下：
SELECT S# AS 学生ID,
(SELECT SCORE FROM SC WHERE SC.S# = T.S# AND C# = '004') AS 数据库,
(SELECT SCORE FROM SC WHERE SC.S# = T.S# AND C# = '001') AS 企业管理,
(SELECT SCORE FROM SC WHERE SC.S# = T.S# AND C# = '006') AS 英语,
COUNT(*) AS 有效课程数,
AVG(T.SCORE) AS 平均成绩
FROM SC AS T
GROUP BY S#
ORDER BY AVG(T.SCORE) DESC;
18、答案：最终SQL如下：
SELECT C# AS 课程ID, MAX(SCORE) AS 最高分, MIN(SCORE) AS 最低分
FROM SC
GROUP BY C#;
19、答案：最终SQL如下：
SELECT T.C# AS 课程号,
MAX(COURSE.CNAME) AS 课程名,
NVL(AVG(SCORE), 0) AS 平均成绩,
((100 * SUM(CASE
WHEN NVL(SCORE, 0) >= 60 THEN
1
ELSE
0
END)) COUNT(*)) AS 及格百分数
FROM SC T, COURSE C
WHERE T.C# = C.C#
GROUP BY T.C#
ORDER BY 平均成绩,
((100 * SUM(CASE
WHEN NVL(SCORE, 0) >= 60 THEN
1
ELSE
0
END)) COUNT(*));
20、答案：最终SQL如下：
SELECT SUM(CASE WHEN C#='001' THEN SCORE ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分,
100*SUM(CASE WHEN C#='001' AND SCORE>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C#='001' THEN 1 ELSE 0 END) AS 企业管理及格百分数,
SUM(CASE WHEN C#='002' THEN SCORE ELSE 0 END)/SUM(CASE C# WHEN'002' THEN 1 ELSE 0 END) AS 马克思平均分,
100*SUM(CASE WHEN C#='002' AND SCORE>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C#='002' THEN 1 ELSE 0 END) AS 马克思及格百分数,
SUM(CASE WHEN C#='003' THEN SCORE ELSE 0 END)/SUM(CASE C# WHEN'003' THEN 1 ELSE 0 END) AS UML平均分 ,
100*SUM(CASE WHEN C#='003' AND SCORE>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C#='003' THEN 1 ELSE 0 END) AS UML及格百分数,
SUM(CASE WHEN C#='004' THEN SCORE ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS数据库平均分,
100*SUM(CASE WHEN C#='004'AND SCORE>=60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C#='004'THEN 1 ELSE 0 END) AS 数据库及格百分数
FROM SC;
21、答案：最终SQL如下：
SELECT Z.T# AS 教师ID,
Z.TNAME AS 教师姓名,
C.C# AS 课程,
C.CNAME AS 课程名称,
AVG(SCORE) AS 平均成绩
FROM SC AS T, COURSE AS C, TEACHER AS Z
WHERE T.C# = C.C#
AND C.T# = Z.T#
GROUP BY C.C#,Z.T#,Z.TNAME,C.CNAME
ORDER BY AVG(SCORE) DESC;
22、 答案：最终SQL如下：
SELECT SC.S# AS 学生学号,
STUDENT.SNAME AS 学生姓名,
T1.SCORE AS 企业管理,
T2.SCORE AS 马克思,
T3.SCORE AS UML,
T4.SCORE AS 数据库,
NVL(T1.SCORE, 0) + NVL(T2.SCORE, 0) + NVL(T3.SCORE, 0) +
NVL(T4.SCORE, 0) AS 总分
FROM STUDENT, SC
LEFT JOIN (SELECT *
FROM (SELECT NB.S#,
NB.SCORE,
(RANK（） OVER(PARTITION BY NB.S# ORDER BY NB.SCORE)) RK
FROM SC NB
WHERE NB.C# = '001')
WHERE RK <= 6
AND RK >= 3) AS T1
ON SC.S# = T1.S#
LEFT JOIN (SELECT *
FROM (SELECT NB.S#,
NB.SCORE,
(RANK（） OVER(PARTITION BY NB.S# ORDER BY NB.SCORE)) RK
FROM SC NB
WHERE NB.C# = '002')
WHERE RK <= 6
AND RK >= 3) AS T2
ON SC.S# = T2.S#
LEFT JOIN (SELECT *
FROM (SELECT NB.S#,
NB.SCORE,
(RANK() OVER(PARTITION BY NB.S# ORDER BY NB.SCORE)) RK
FROM SC NB
WHERE NB.C# = '003')
WHERE RK <= 6
AND RK >= 3) AS T3
ON SC.S# = T3.S#
LEFT JOIN (SELECT *
FROM (SELECT NB.S#,
NB.SCORE,
(RANK() OVER(PARTITION BY NB.S# ORDER BY NB.SCORE)) RK
FROM SC NB
WHERE NB.C# = '004')
WHERE RK <= 6
AND RK >= 3) AS T4
ON SC.S# = T4.S#
WHERE STUDENT.S# = SC.S#;
23、答案：最终SQL如下：
SELECTSC.C# AS 课程ID,
CNAME AS 课程名称,
SUM(CASE WHEN SCORE BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS "[100-85]" ,
SUM(CASE WHEN SCORE BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS "[85-70]" ,
SUM(CASE WHEN SCORE BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS "[70-60]" ,
SUM(CASE WHEN SCORE<60 THEN 1 ELSE 0 END) AS "[60-]"
FROM SC,COURSE
WHERE SC.C#=COURSE.C#
GROUP BY SC.C#,CNAME;
24、 答案：最终SQL如下：
SELECT S# AS 学生学号, RK AS 名次, 平均成绩
FROM (SELECT S#,
AVG(SCORE) 平均成绩,
SUM(SCORE) 总成绩,
(RANK() OVER(PARTITION BY S# ORDER BY SUM(SCORE) DESC)) RK
FROM SC
GROUP BY S#) AS T2
ORDER BY 名次 DESC;
25、答案：最终SQL如下：
SELECT T1.S# AS 学生ID, T1.C# AS 课程ID, SCORE AS 分数
FROM SC T1
WHERE SCORE IN
(SELECT TOP 3 SCORE FROM SC WHERE T1.C# = C# ORDER BY SCORE DESC)
ORDER BY T1.C#;
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 文章内容来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
