SQL进阶练习题

SQL进阶练习题是对SQL基础练习题的深入，有些题目会用到多层嵌套，对新手培养逻辑思维有一定的益处。有些题目的解法不止一种，这里只给出了个人喜好的解法。如果有想探讨多种解法的朋友可以留下邮箱，我这里有整合多种解法的文档可供参考。另外，刚学习的同学也可参考SQL基础练习题。
SQL基础练习题

导入数据表

CREATE DATABASE PRACTICE;
CREATE TABLE STUDENT(SNO varchar(10),SNAME nvarchar(10),SAGE datetime,SSEX nvarchar(10));
INSERT INTO STUDENT VALUES
('01' , N'赵雷' , '1990-01-01' , N'男'),
('02' , N'钱电' , '1990-12-21' , N'男'),
('03' , N'孙风' , '1990-05-20' , N'男'),
('04' , N'李云' , '1990-08-06' , N'男'),
('05' , N'周梅' , '1991-12-01' , N'女'),
('05' , N'周梅' , '1991-12-01' , N'女'),
('06' , N'吴兰' , '1992-03-01' , N'女'),
('07' , N'郑竹' , '1989-07-01' , N'女'),
('08' , N'王菊' , '1990-01-20' , N'女');
CREATE TABLE COURSE(CNO varchar(10),CNAME nvarchar(10),TNO varchar(10));
INSERT INTO COURSE VALUES
('01' , N'语文' , '02'),
('02' , N'数学' , '01'),
('03' , N'英语' , '03');
CREATE TABLE TEACHER(TNO varchar(10),TNAME nvarchar(10));
INSERT INTO TEACHER VALUES
('01' , N'张三'),
('02' , N'李四'),
('03' , N'王五');
CREATE TABLE SC(SNO varchar(10),CNO varchar(10),SCORE decimal(18,1));
INSERT INTO SC VALUES
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);

题目

1、查询”01”课程比”02”课程成绩高的学生的信息及课程分数;

SELECT A.* , B.SCORE '01课程成绩' ,C.SCORE '02课程成绩'
FROM STUDENT A INNER JOIN (SC B, SC C)
ON A.SNO = B.SNO AND A.SNO = C.SNO AND B.CNO = '01' AND C.CNO = '02'
WHERE B.SCORE>C.SCORE;

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

SELECT A.SNO,B.SNAME,AVG(SCORE) AVG_SCORE
FROM SC A INNER JOIN STUDENT B
ON A.SNO=B.SNO
GROUP BY SNO 
HAVING AVG(SCORE)>=60;

3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩;

SELECT A.SNO,A.SNAME,COUNT(B.CNO) COUNT_CNO,SUM(SCORE) SUM_SCORE
FROM STUDENT A INNER JOIN SC B
ON A.SNO=B.SNO GROUP BY A.SNO; 

4、查询”李”姓老师的数量;

SELECT COUNT(*) FROM TEACHER
WHERE TNAME LIKE '李%';

5、查询学过”张三”老师授课的同学的信息；

SELECT P.* FROM STUDENT P INNER JOIN SC Q
ON P.SNO=Q.SNO
WHERE Q.CNO=(
SELECT B.CNO
FROM TEACHER A ,COURSE B
WHERE A.TNO=B.TNO AND A.TNAME='张三');

6、查询没学过”张三”老师授课的同学的信息;

SELECT * FROM STUDENT 
WHERE SNO NOT IN 
(SELECT DISTINCT B.SNO
FROM TEACHER A,SC B,COURSE C
WHERE A.TNO=C.TNO AND B.CNO=C.CNO AND A.TNAME='张三');

7、查询学过编号为”01”并且也学过编号为”02”的课程的同学的信息;

SELECT * FROM STUDENT
WHERE SNO IN (
SELECT A.SNO FROM SC A ,SC B 
WHERE A.SNO=B.SNO AND A.CNO='01' AND B.CNO='02');

8、查询学过编号为”01”但是没有学过编号为”02”的课程的同学的信息;

SELECT * FROM STUDENT
WHERE SNO IN (
SELECT SNO FROM SC WHERE CNO='01') 
AND SNO NOT IN (
SELECT SNO FROM SC WHERE CNO='02')

9、查询没有学全所有课程的同学的信息;

SELECT * FROM STUDENT
WHERE SNO NOT IN (
SELECT SNO FROM SC GROUP BY SNO
HAVING COUNT(CNO)=3);

10、查询至少有一门课与学号为”01”的同学所学相同的同学的信息;

SELECT DISTINCT A.* FROM STUDENT A ,SC B
WHERE A.SNO=B.SNO AND CNO IN
(SELECT CNO FROM SC WHERE SNO=01) 
AND A.SNO<>01

11、查询和”01”号的同学学习的课程完全相同的其他同学的信息 ;

SELECT * FROM STUDENT
WHERE SNO IN (
SELECT A.SNO FROM STUDENT A  INNER JOIN SC B ON A.SNO=B.SNO
WHERE A.SNO<>01 AND B.CNO IN (SELECT CNO FROM SC WHERE SNO=01)
GROUP BY A.SNO HAVING COUNT(B.CNO)=(SELECT count(*) FROM SC WHERE SNO=01)
);  

12、查询没学过”张三”老师讲授的任一门课程的学生姓名；

SELECT SNAME FROM STUDENT
WHERE SNO NOT IN (
SELECT SNO FROM SC 
WHERE CNO IN (
SELECT B.CNO FROM TEACHER A,COURSE B WHERE A.TNO=B.TNO AND A.TNAME='张三')
)

13、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩；

SELECT A.SNO,A.SNAME,AVG(B.SCORE) AVG_SCORE
FROM STUDENT A,SC B 
WHERE A.SNO=B.SNO AND B.SCORE<60
GROUP BY A.SNO 
HAVING  COUNT(*)>=2

14、检索”01”课程分数小于60，按分数降序排列的学生信息；

SELECT A.*,B.CNO,B.SCORE
FROM STUDENT A,SC B
WHERE A.SNO=B.SNO AND B.CNO=01 AND B.SCORE<60
ORDER BY SCORE DESC;

15、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩;

SELECT SNO,GROUP_CONCAT(CNO),GROUP_CONCAT(SCORE),AVG(SCORE) FROM SC
GROUP BY SNO ORDER BY AVG(SCORE) DESC

16、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率;

SELECT CNO,CNAME,
(SELECT MAX(SCORE) FROM SC WHERE CNO=A.CNO)MAX_SCORE,
(SELECT MIN(SCORE) FROM SC WHERE CNO=A.CNO)MIN_SCORE,
(SELECT AVG(SCORE) FROM SC WHERE CNO=A.CNO)AVG_SCORE,
(SELECT COUNT(*) FROM SC WHERE CNO=A.CNO AND SCORE>=60)/(SELECT COUNT(*) FROM SC WHERE CNO=A.CNO)*100 '及格率%'
FROM COURSE AS A ORDER BY CNO

17、按各科成绩进行排序，并显示排名

SELECT SNO,CNO,SCORE,(SELECT COUNT(1)+1 FROM SC WHERE SCORE> A.SCORE AND CNO=A.CNO) RANK
FROM SC A
ORDER BY CNO,SCORE DESC;

18、查询学生的总成绩并进行排名

SELECT SNO,SUM(SCORE)
FROM SC
GROUP BY SNO 
ORDER BY SUM(SCORE) DESC

19、查询不同老师所教不同课程平均分从高到低显示

SELECT A.TNAME,AVG(B.SCORE)
FROM TEACHER A,SC B,COURSE C
WHERE A.TNO=C.TNO AND B.CNO=C.CNO
GROUP BY A.TNO
ORDER BY AVG(B.SCORE) DESC

20、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

21、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

SELECT CNO,
SUM(CASE WHEN SCORE<=100 AND SCORE>=85 THEN 1 ELSE 0 END) '[100-85]人数',
CAST(SUM(CASE WHEN SCORE<=100 AND SCORE>=85 THEN 1 ELSE 0 END)/COUNT(*) AS DECIMAL(5,2)) '占比',
SUM(CASE WHEN SCORE<85 AND SCORE>=70 THEN 1 ELSE 0 END) '[70-85]人数',
CAST(SUM(CASE WHEN SCORE<85 AND SCORE>=70 THEN 1 ELSE 0 END)/COUNT(*) AS DECIMAL(5,2)) '占比',
SUM(CASE WHEN SCORE<70 AND SCORE>=60 THEN 1 ELSE 0 END) '[60-70]人数',
CAST(SUM(CASE WHEN SCORE<70 AND SCORE>=60 THEN 1 ELSE 0 END)/COUNT(*) AS DECIMAL(5,2)) '占比',
SUM(CASE WHEN SCORE<60 THEN 1 ELSE 0 END) '[0-60]人数',
CAST(SUM(CASE WHEN SCORE<60 THEN 1 ELSE 0 END)/COUNT(*) AS DECIMAL(5,2)) '占比'
FROM SC
GROUP BY CNO

22、查询学生平均成绩及其名次

SELECT A.SNO,A.AVG,COUNT(*) '名次'
FROM (SELECT SNO,AVG(SCORE) AVG FROM SC GROUP BY SNO) A
INNER JOIN (SELECT SNO,AVG(SCORE) AVG FROM SC GROUP BY SNO) B
ON A.AVG<=B.AVG
GROUP BY A.SNO
ORDER BY A.AVG DESC

23、查询各科成绩前三名的记录

24、查询每门课程被选修的学生数

SELECT CNO,COUNT(*)
FROM SC GROUP BY CNO

25、查询出只选修两门课程的全部学生的学号和姓名

SELECT A.SNO,B.SNAME
FROM SC  A INNER JOIN STUDENT B ON A.SNO=B.SNO
GROUP BY A.SNO HAVING COUNT(*)=2

26、查询男、女生人数

 SELECT SSEX,COUNT(*) FROM STUDENT GROUP BY SSEX

27、查询名字中含有”风”字的学生信息

SELECT * FROM STUDENT WHERE SNAME LIKE '%风%'

28、查询同名同性学生名单，并统计同名人数

SELECT SNAME,COUNT(*)
FROM STUDENT 
GROUP BY SNAME
HAVING COUNT(*)>1

29、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

SELECT SNAME FROM STUDENT
WHERE YEAR(SAGE)=1990

30、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT CNO,AVG(SCORE)
FROM SC GROUP BY CNO
ORDER BY AVG(SCORE) DESC,CNO

31、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT A.SNO,B.SNAME,AVG(A.SCORE)
FROM SC A INNER JOIN STUDENT B
ON A.SNO=B.SNO
GROUP BY A.SNO
HAVING AVG(A.SCORE)>=85

32、查询课程名称为”数学”，且分数低于60的学生姓名和分数

SELECT A.SNO,A.SNAME,B.SCORE
FROM STUDENT A JOIN (SC B,COURSE C)
ON A.SNO=B.SNO AND B.CNO=C.CNO
WHERE C.CNAME='数学' AND B.SCORE<60

33、查询所有学生的课程及分数情况；

SELECT A.SNAME,C.CNAME,B.SCORE
FROM STUDENT A,SC B,COURSE C
WHERE A.SNO=B.SNO AND B.CNO=C.CNO

34、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数；

SELECT A.SNAME,C.CNAME,B.SCORE
FROM STUDENT A,SC B,COURSE C
WHERE A.SNO=B.SNO AND B.CNO=C.CNO
AND B.SCORE>=70

35、查询不及格的学生的学号和课程号

SELECT SNO,CNO FROM SC WHERE SCORE<60

36、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

SELECT B.SNO,B.SNAME
FROM SC A INNER JOIN STUDENT B ON A.SNO=B.SNO
WHERE A.CNO=01 AND A.SCORE>=80

37、求每门课程的学生人数

SELECT CNO,COUNT(*) FROM SC GROUP BY CNO;

38、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT A.*,MAX(B.SCORE)
FROM STUDENT A,SC B,COURSE C,TEACHER D
WHERE A.SNO=B.SNO AND B.CNO=C.CNO AND C.TNO=D.TNO
AND D.TNAME='张三'

39、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT DISTINCT A.*
FROM SC A INNER JOIN SC B 
ON A.SNO=B.SNO
WHERE A.CNO<>B.CNO AND A.SCORE=B.SCORE 

40、查询每门功成绩最好的前两名

SELECT A.*,MAX(B.SCORE)
FROM STUDENT A JOIN (SC B,COURSE C,TEACHER D)
ON A.SNO=B.SNO AND B.CNO=C.CNO AND C.TNO=D.TNO
WHERE D.TNAME='张三' 

41、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT CNO,COUNT(*)
FROM SC
GROUP BY CNO
HAVING COUNT(*)>=5
ORDER BY COUNT(*) DESC,CNO

42、检索至少选修两门课程的学生学号

SELECT SNO FROM SC
GROUP BY SNO
HAVING COUNT(*)>=2

43、查询选修了全部课程的学生信息

SELECT B.*
FROM SC A INNER JOIN STUDENT B ON A.SNO=B.SNO
GROUP BY A.SNO
HAVING COUNT(*)=(SELECT COUNT(*) FROM COURSE)

44、查询各学生的年龄

SELECT SNAME,YEAR(NOW())-YEAR(SAGE) AGE
FROM STUDENT

45、查询本周过生日的学生

SELECT SNAME FROM STUDENT WHERE WEEK(SAGE)=WEEK(NOW())

46、查询下周过生日的学生

SELECT SNAME FROM STUDENT WHERE WEEK(SAGE)=WEEK(NOW())+1

47、查询本月过生日的学生

SELECT SNAME FROM STUDENT WHERE MONTH(SAGE)=MONTH(NOW())

48、查询下月过生日的学生

SELECT SNAME FROM STUDENT WHERE MONTH(SAGE)=MONTH(NOW())+1

49、查询学生成绩的及格状况，以学生姓名、科目、及格状况显示。

SELECT B.SNAME,C.CNAME,A.SCORE,IF(A.SCORE>=60,'及格','不及格')
FROM SC A,STUDENT B,COURSE C
WHERE A.SNO=B.SNO AND A.CNO=C.CNO