江湖流传超经典的50道SQL题(答案)

2021年04月08日 16:37 · 阅读 121

1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数(同一个学生的)

SELECT * FROM sc a, sc b, student c WHERE a.SId = b.SId and a.SId = c.SId AND a.CId = 01 AND b.CId = 02 AND a.score > b.score

1.1查询同时存在" 01 "课程和" 02 "课程的情况

SELECT * FROM sc a, sc b WHERE a.SId = b.SId AND a.CId = 01 AND b.CId = 02

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

SELECT * FROM sc a LEFT JOIN sc b ON a.SId = b.SId AND b.CId = 02 WHERE a.CId = 01

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况

SELECT * FROM sc b WHERE b.SId NOT IN ( SELECT a.SId FROM sc a WHERE a.CId = 01 ) and b.CId = 02

2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT AVG( a.score ) AS score, b.sid, b.sname FROM sc a, student b WHERE a.SId = b.SId GROUP BY a.SId HAVING AVG( a.score )> 60

3. 查询在 SC 表存在成绩的学生信息

SELECT DISTINCT b.* FROM sc a, student b WHERE a.SId = b.SId;

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT a.sid, a.sname, count( b.cid ), sum( b.score ) FROM student a LEFT JOIN sc b ON a.SId = b.SId GROUP BY a.sid

4.1 查有成绩的学生信息

SELECT a.sid, a.sname, count( b.cid ), sum( b.score ) FROM student a JOIN sc b ON a.SId = b.SId GROUP BY a.sid

5. 查询「李」姓老师的数量

SELECT count(*) FROM teacher t WHERE t.Tname LIKE '李%'

6. 查询学过「张三」老师授课的同学的信息

SELECT a.* FROM student a, sc b, course c WHERE a.SId = b.SId AND b.CId = c.CId AND c.TId = ( SELECT TId FROM teacher WHERE Tname = '张三' )

7. 查询没有学全所有课程的同学的信息

SELECT * FROM student a JOIN sc b ON a.SId = b.SId GROUP BY a.SId HAVING count( b.CId ) < ( SELECT count(*) FROM course)

8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT c.* FROM sc b, student c WHERE b.SId = c.SId AND b.SId != 01 AND b.CId IN ( SELECT a.CId FROM sc a WHERE a.SId = 01 ) GROUP BY sid

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

SELECT sid FROM sc WHERE sid NOT IN ( SELECT sid FROM sc a WHERE a.cid NOT IN ( SELECT cid FROM sc WHERE SId = '01' )) AND sid != '01' GROUP BY sid HAVING count( 1 ) =( SELECT count(*) FROM sc WHERE sid = '01')

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT * FROM student d WHERE d.SId NOT IN ( SELECT a.SId FROM sc a WHERE a.CId = ( SELECT c.CId FROM course c WHERE c.TId = ( SELECT b.TId FROM teacher b WHERE b.Tname = '张三' ) ))