SQL笔试经典50题

MySQL经典50题

1、

#1、查询“01”课程比“02”课程成绩高的所有学生的学号；
SELECT s1.sid
FROM SC s1 JOIN SC s2 ON s1.sid=s2.sid AND s1.cid='01' AND s2.cid='02' AND s1.score>s2.score

2、

#2、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sid as 学号, avg(score) as 平均成绩
FROM SC GROUP BY sid HAVING 平均成绩>60

3、

#3、查询所有同学的学号、姓名、选课数、总成绩
SELECT s2.sid as 学号, s2.sname as 姓名, a.b as 选课数, a.c as 总成绩
FROM Student s2,
(SELECT s1.sid as id ,count(s1.cid) as b, sum(s1.score) as c
FROM SC s1 JOIN Student s2 ON s1.sid=s2.sid GROUP BY s1.sid)a
WHERE s2.sid=a.id 

4、

#4、查询姓“李”的老师的个数；
SELECT * FROM Teacher WHERE tname LIKE '李%'

5、

#5、查询没学过“张三”老师课的同学的学号、姓名；
SELECT DISTINCT(s1.sid) as 学号,s1.sname as 姓名
FROM Student s1 JOIN SC s2 ON s1.sid=s2.sid AND s2.cid IN
(SELECT c1.cid FROM Teacher t1 JOIN Course c1 ON t1.tid=c1.tid AND t1.tname NOT LIKE '张三') 

6、

#6、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；
SELECT sid as 学号, sname as 姓名 FROM Student WHERE sid in 
(SELECT s1.sid
FROM SC s1 JOIN SC s2 ON s1.sid=s2.sid AND s1.cid='01' AND s2.cid='02')

7、

#7、查询学过“张三”老师所教的课的同学的学号、姓名；
SELECT s2.sid as 学号, s2.sname as 姓名
FROM SC s1 JOIN Student s2 ON s1.sid=s2.sid AND s1.cid IN
(SELECT cid FROM Course WHERE tid IN
(SELECT tid FROM Teacher WHERE tname='张三'))

8、

#8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
SELECT a.sid as 学号, s3.sname as 姓名
FROM (SELECT s1.sid FROM SC s1 JOIN SC s2 ON s1.sid=s2.sid AND s1.cid='01' AND s2.cid='02' AND s1.score<s2.score)a JOIN Student s3 ON a.sid=s3.sid  

9、

#9、查询所有课程成绩小于60分的同学的学号、姓名；
SELECT DISTINCT(s2.sid) as 学号, s2.sname