昨天分享了前25道，今天继续分享后25道题。
【DB笔试面试11】根据题目要求写出以下50道SQL语句（1）
已知有如下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语句。
26、查询每门课程被选修的学生数
27、查询出只选修了一门课程的全部学生的学号和姓名
28、查询男生、女生人数
29、查询姓“张”的学生名单
30、查询同名同性学生名单并统计同名人数
31、1981年出生的学生名单（注：STUDENT表中SAGE列的类型是DATE）
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时按课程号降序排列
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
34、查询课程名称为“数据库”且分数低于60的学生姓名和分数
35、查询所有学生的选课情况
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
37、查询不及格的课程并按课程号从大到小排列
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
39、查询选了课程的学生人数
40、查询选修“李多多”老师所授课程的学生中成绩最高的学生姓名及其成绩
41、查询各个课程及相应的选修人数
42、查询有2门不同课程成绩相同的学生的学号、课程号、学生成绩
43、查询每门课程成绩最好的前两名
44、查询每门课程的学生选修人数，超过10人的课程才统计。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同按课程号升序排列
45、查询至少选修两门课程的学生学号
46、查询全部学生都选修的课程的课程号和课程名
47、查询没学过“李多多”老师讲授的任一门课程的学生姓名
48、查询两门以上不及格课程的同学的学号及其平均成绩
49、检索课程编号为“004”且分数小于60的学生学号，结果按按分数降序排列
50、删除学生编号为“002”的课程编号为“001”的课程的成绩
。C、D
。本题考察SQL的编写能力，对于这类型的题目，只要把4张表之间的关联关系搞清楚了，编写对应的SQL语句就比较容易了，本题给出的四张表之间的关系如下图所示：
26、答案：最终SQL如下：
SELECT C#, COUNT(S#) FROM SC GROUP BY C#;
27、答案：最终SQL如下：
SELECT SC.S#, STUDENT.SNAME
FROM SC, STUDENT
WHERE SC.S# = STUDENT.S#
GROUP BY SC.S#, STUDENT.SNAME
HAVING COUNT(C#) = 1;
28、答案：最终SQL如下：
SELECT SUM(CASE WHEN SSEX = '男' THEN 1 ELSE 0) AS 男生人数,
SUM(CASE WHEN SSEX = '女' THEN 1 ELSE 0) AS 女生人数
FROM STUDENT
GROUP BY SSEX;
29、答案：最终SQL如下：
SELECT SNAME FROM STUDENT WHERE SNAME LIKE '张%';
30、答案：最终SQL如下：
SELECT SNAME, COUNT(*) FROM STUDENT GROUP BY SNAME HAVING COUNT(*) > 1;
31、答案：最终SQL如下：
SELECT SNAME
FROM STUDENT
WHERE TO_CHAR(STUDENT.SAGE,'YYYY')='1981';
32、答案：最终SQL如下：
SELECT C#, AVG(SCORE) FROM SC GROUP BY C# ORDER BY AVG(SCORE), C# DESC;
33、答案：最终SQL如下：
SELECT SNAME, SC.S#, AVG(SCORE)
FROM STUDENT, SC
WHERE STUDENT.S# = SC.S#
GROUP BY SC.S#, SNAME
HAVING AVG(SCORE) > 85;
34、答案：最终SQL如下：
SELECT SNAME, NVL(SCORE, 0)
FROM STUDENT, SC, COURSE
WHERE SC.S# = STUDENT.S#
ANDSC.C# = COURSE.C#
AND COURSE.CNAME = '数据库'
AND SCORE < 60;
35、 答案：最终SQL如下：
SELECT SC.S#,SC.C#, SNAME, CNAME
FROM SC, STUDENT, COURSE
WHERE SC.S# = STUDENT.S#
ANDSC.C# = COURSE.C#;
36、答案：最终SQL如下：
SELECT DISTINCT STUDENT.S#, STUDENT.SNAME,SC.C#, SC.SCORE
FROM STUDENT, SC
WHERE SC.SCORE >= 70
AND SC.S# = STUDENT.S#;
37、答案：最终SQL如下：
SELECT C# FROM SC WHERE SCORE < 60 ORDER BY C#;
38、答案：最终SQL如下：
SELECT SC.S#, STUDENT.SNAME
FROM SC, STUDENT
WHERE SC.S# = STUDENT.S#
AND SCORE > 80
AND C# = '003';
39、答案：最终SQL如下：
SELECT COUNT(DISTINCT S#) FROM SC GROUP BY S#;
40、答案：最终SQL如下：
SELECT STUDENT.SNAME, SCORE
FROM STUDENT, SC, COURSE C, TEACHER
WHERE STUDENT.S# = SC.S#
AND SC.C# = C.C#
AND C.T# = TEACHER.T#
AND TEACHER.TNAME = '李多多'
AND SC.SCORE = (SELECT MAX(SCORE) FROM SC WHERE C# = C.C#);
41、答案：最终SQL如下：
SELECT C#, COUNT(*) FROM SC GROUP BY C#;
42、答案：最终SQL如下：
SELECT DISTINCT A.S#, A.C#, B.SCORE
FROM SC A, SC B
WHERE A.SCORE = B.SCORE
AND A.S# = B.S#
AND A.C# <> B.C#;
43、答案：最终SQL如下：
SELECT T1.S# AS 学生ID, T1.C# AS 课程ID, SCORE AS 分数
FROM (SELECT S#, C#, (RANK（） OVER(PARTITION BY C# ORDER BY SCORE DESC)) RK
FROM SC) T1
WHERE RK <= 2;
44、答案：最终SQL如下：
SELECT C# AS 课程号, COUNT(*) AS 人数
FROM SC
GROUP BY C#
HAVING COUNT(*)>10
ORDER BY COUNT(*) DESC, C#;
45、答案：最终SQL如下：
SELECT S# FROM SC GROUP BY S# HAVING COUNT(*) >= 2;
46、答案：最终SQL如下：
SELECT C#, CNAME
FROM COURSE
WHERE C# IN (SELECT C#
FROM (SELECT C#, COUNT(DISTINCT S#) CS# FROM SC GROUP BY C#)
WHERE CS# = (SELECT COUNT(*) FROM STUDENT));
47、答案：最终SQL如下：
SELECT SNAME
FROM STUDENT
WHERE S# NOT IN (SELECT S#
FROM COURSE, TEACHER, SC
WHERE COURSE.T# = TEACHER.T#
AND SC.C# = COURSE.C#
AND TNAME = '李多多');
48、答案：最终SQL如下：
SELECT S#, AVG(NVL(SCORE, 0))
FROM SC
WHERE S# IN
(SELECT S# FROM SC WHERE SCORE < 60 GROUP BY S# HAVING COUNT(*) > 2)
GROUP BY S#;
49、答案：最终SQL如下：
SELECT S#
FROM SC
WHERE C# = '004'
AND SCORE < 60
ORDER BY SCORE DESC;
50、答案：最终SQL如下：
DELETE FROM SC
WHERE S# = '002'
AND C# = '001';
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 文章内容来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
