48道SQL练习题

1.1 本题目的表结构
Student(S#,Sname,Sage,Ssex) 　　 学生表 Course(C#,Cname,T#) 　　　　　　 课程表 SC(S#,C#,score) 　　　　　　　　　成绩表 Teacher(T#,Tname) 　　　　　　　 教师表
1.2 本题目的建表及测试数据
（1）建表
CREATE TABLE Student ( S# INT, Sname nvarchar(32), Sage INT, Ssex nvarchar(8) ) CREATE TABLE Course ( C# INT, Cname nvarchar(32), T# INT ) CREATE TABLE Sc ( S# INT, C# INT, score INT ) CREATE TABLE Teacher ( T# INT, Tname nvarchar(16) )
（2）插测试数据
insert into Student select 1,N’刘一’,18,N’男’ union all select 2,N’钱二’,19,N’女’ union all select 3,N’张三’,17,N’男’ union all select 4,N’李四’,18,N’女’ union all select 5,N’王五’,17,N’男’ union all select 6,N’赵六’,19,N’女’ insert into Teacher select 1,N’叶平’ union all select 2,N’贺高’ union all select 3,N’杨艳’ union all select 4,N’周磊’ insert into Course select 1,N’语文’,1 union all select 2,N’数学’,2 union all select 3,N’英语’,3 union all select 4,N’物理’,4 insert into SC select 1,1,56 union all select 1,2,78 union all select 1,3,67 union all select 1,4,58 union all select 2,1,79 union all select 2,2,81 union all select 2,3,92 union all select 2,4,68 union all select 3,1,91 union all select 3,2,47 union all select 3,3,88 union all select 3,4,56 union all select 4,2,88 union all select 4,3,90 union all select 4,4,93 union all select 5,1,46 union all select 5,3,78 union all select 5,4,53 union all select 6,1,35 union all select 6,2,68 union all select 6,4,71

（1）查询“001”课程比“002”课程成绩高的所有学生的学号；
SELECT a.S# FROM dbo.Sc a,dbo.Sc b WHERE a.S#=b.S# AND a.C#=1 AND b.C#=2 AND a.score>b.score
（2）查询平均成绩大于60分的同学的学号和平均成绩；
SELECT S# ,AVG(score) AS ‘平均数’ FROM dbo.Sc GROUP BY S# HAVING AVG(score)>60
（3）查询所有同学的学号、姓名、选课数、总成绩；
SELECT dbo.Sc.S#,Sname,COUNT(dbo.Student.S#) AS ‘选课数’,SUM(score) AS ‘总分’ FROM dbo.Student LEFT JOIN dbo.Sc ON Sc.S# = Student.S# GROUP BY Sc.S#,Sname
（4）查询姓“李”的老师的个数；
SELECT COUNT(distinct(Tname)) AS ‘李姓人数’ FROM dbo.Teacher WHERE Tname LIKE ‘李%’
（5）查询没学过“叶平”老师课的同学的学号、姓名；
SELECT DISTINCT(dbo.Student.S#) , Sname FROM dbo.Student INNER JOIN dbo.Sc ON Sc.S# = Student.S# WHERE dbo.Student.S# NOT IN(SELECT S# FROM dbo.Course INNER JOIN dbo.Teacher ON Teacher.T# = Course.T# INNER JOIN dbo.Sc ON Sc.C# = Course.C# WHERE Tname=‘叶平’)
（6）查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT dbo.Sc.S#,Sname FROM dbo.Student LEFT JOIN dbo.Sc ON Sc.S# = Student.S# WHERE C#=1 or C#=2 GROUP BY Sname,Sc.S# HAVING COUNT(Sname)>1
（7）查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT dbo.Student.S#, Sname FROM dbo.Student INNER JOIN dbo.Sc ON Sc.S# = Student.S# WHERE C# =(SELECT C# FROM dbo.Course INNER JOIN dbo.Teacher ON Teacher.T# = Course.T# WHERE Tname=‘叶平’) GROUP BY Student.S#,Sname
（8）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
SELECT a.S# ,Sname FROM dbo.Sc a,dbo.Sc b LEFT JOIN dbo.Student ON Student.S# = b.S# WHERE a.S#=b.S# AND a.C#=1 AND b.C#=2 AND a.score>b.score
（9）查询有课程成绩小于60分的同学的学号、姓名；
SELECT DISTINCT(dbo.Sc.S#) ,Sname FROM dbo.Sc LEFT JOIN dbo.Student ON Student.S# = dbo.Sc.S# WHERE score<60
（10）查询没有学全所有课的同学的学号、姓名；
SELECT dbo.Sc.S#,Sname FROM dbo.Student LEFT JOIN dbo.Sc ON Sc.S# = Student.S# GROUP BY Sname,Sc.S# HAVING COUNT(Sname)<4
（11）查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
SELECT DISTINCT(dbo.Sc.S#),Sname FROM dbo.Sc LEFT JOIN dbo.Student ON Student.S# = dbo.Sc.S# WHERE C# IN (SELECT C# FROM dbo.Sc WHERE S#=1) AND Sc.S#!=1
（12）查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
SELECT dbo.Sc.S#,Sname FROM dbo.Sc LEFT JOIN dbo.Student ON Student.S# = dbo.Sc.S# WHERE C# IN(SELECT C# FROM dbo.Sc WHERE S#=1) AND Sc.S#!=1 GROUP BY Sname,Sc.S# HAVING COUNT(Sname)=(SELECT COUNT(*) FROM dbo.Sc WHERE S#=1)
（13）把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
UPDATE dbo.Sc SET score=( SELECT AVG(score) FROM dbo.Sc WHERE C#=( SELECT dbo.Course.C# FROM dbo.Course INNER JOIN dbo.Teacher ON Teacher.T# = Course.T# WHERE Tname=‘叶平’)) WHERE Sc.C#=( SELECT dbo.Course.C# FROM dbo.Course INNER JOIN dbo.Teacher ON Teacher.T# = Course.T# WHERE Tname=‘叶平’)
（14）查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
SELECT dbo.Sc.S#,Sname FROM dbo.Sc LEFT JOIN dbo.Student ON Student.S# = dbo.Sc.S# WHERE C# IN(SELECT C# FROM dbo.Sc WHERE S#=2) AND Sc.S#!=2 GROUP BY Sname,Sc.S# HAVING COUNT(Sname)=(SELECT COUNT(*) FROM dbo.Sc WHERE S#=2)
（15）删除学习“叶平”老师课的SC表记录；
DELETE dbo.Sc WHERE C#=( SELECT dbo.Course.C# FROM dbo.Course INNER JOIN dbo.Teacher ON Teacher.T# = Course.T# WHERE Tname=‘叶平’)
（16）向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
INSERT INTO Sc(S#, C#, score) SELECT S#,2,(select avg(score) from dbo.Sc where C# = 2) from dbo.Student where S# not in ( select S# from dbo.Sc where C# = 2 )
（17）按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
SELECT S# as 学生ID ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘1’) AS 语文 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘2’) AS 数学 ,(SELECT score FROM SC WHERE SC.S#=t.S# AND C#=‘3’) AS 英语 ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 FROM SC AS t GROUP BY S# ORDER BY avg(t.score)
（18）查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
SELECT C#,MAX(score) AS ‘最高分’,MIN(score) AS ‘最低分’ FROM dbo.Sc GROUP BY C#
（19）按各科平均成绩从低到高和及格率的百分数从高到低顺序；
SELECT C#,AVG(score) AS ‘平均分’,Convert(varchar(12),100*SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(1))+’%’ AS ‘及格率’ FROM dbo.Sc GROUP BY C# ORDER BY AVG(score),及格率 DESC
（20）查询如下课程平均成绩和及格率的百分数(备注：需要在1行内显示): 企业管理（002），OO&UML （003），数据库（004）
SELECT SUM(CASE WHEN C# = ‘2’ THEN score ELSE 0 END)/SUM(CASE C# WHEN ‘002’ THEN 1 ELSE 0 END) AS 数学平均分 ,CONVERT(NVARCHAR(20),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 英语平均分 ,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 英语及格百分数 ,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）查询不同老师所教不同课程平均分从高到低显示；
SELECT Tname,Cname,AVG(score) AS ‘平均数’ FROM dbo.Sc INNER JOIN dbo.Course ON Course.C# = Sc.C# INNER JOIN dbo.Teacher ON Teacher.T# = Course.T# GROUP BY Cname,dbo.Sc.C#,Tname ORDER BY AVG(score) DESC
（22）查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004）
SELECT DISTINCT top 3 SC.S# As 学生学号, Student.Sname AS 学生姓名 , T1.score AS 企业管理, T2.score AS 马克思, T3.score AS UML, T4.score AS 数据库, ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分 FROM Student,SC LEFT JOIN SC AS T1 ON SC.S# = T1.S# AND T1.C# = ‘001’ LEFT JOIN SC AS T2 ON SC.S# = T2.S# AND T2.C# = ‘002’ LEFT JOIN SC AS T3 ON SC.S# = T3.S# AND T3.C# = ‘003’ LEFT JOIN SC AS T4 ON SC.S# = T4.S# AND T4.C# = ‘004’ WHERE student.S#=SC.S# and ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) NOT IN (SELECT DISTINCT TOP 15 WITH TIES ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) FROM sc LEFT JOIN sc AS T1 ON sc.S# = T1.S# AND T1.C# = ‘k1’ LEFT JOIN sc AS T2 ON sc.S# = T2.S# AND T2.C# = ‘k2’ LEFT JOIN sc AS T3 ON sc.S# = T3.S# AND T3.C# = ‘k3’ LEFT JOIN sc AS T4 ON sc.S# = T4.S# AND T4.C# = ‘k4’ ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC)
（23）统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT SC.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-0] FROM SC,Course where SC.C#=Course.C# GROUP BY SC.C#,Cname;
（24）查询学生平均成绩及其名次；
SELECT S# ,AVG(score) AS ‘平均分’,Row_Number() over ( order by getdate() ) AS ‘排名’ FROM dbo.Sc GROUP BY S# ORDER BY AVG(score) DESC
（25）查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT a.C#,a.S#,a.score FROM SC as a LEFT JOIN dbo.Sc as b ON a.C# = b.C# and a.score<b.score GROUP BY a.S#, a.C#,a.score HAVING count(b.C#)❤️ ORDER BY a.C#
（26）查询每门课程被选修的学生数；
SELECT Cname,COUNT(dbo.Sc.C#) AS ‘人数’ FROM dbo.Sc INNER JOIN dbo.Course ON Course.C# = Sc.C# GROUP BY dbo.Sc.C#,Cname
（27）查询出只选修了一门课程的全部学生的学号和姓名；
SELECT dbo.Sc.S# AS ‘学号’,Sname AS ‘姓名’ FROM dbo.Student LEFT JOIN dbo.Sc ON Sc.S# = Student.S# GROUP BY Sc.S#,Sname HAVING COUNT(dbo.Sc.C#)=1
（28）查询男生、女生的人数；
SELECT Ssex AS ‘性别’,COUNT(*) AS ‘人数’ FROM dbo.Student GROUP BY Ssex
（29）查询姓“张”的学生名单；
SELECT * FROM dbo.Student WHERE Sname LIKE ‘张%’
（30）查询同名同姓学生名单，并统计同名人数；
if exists(SELECT Sname AS ‘姓名’,COUNT(S#) AS ‘同名人数’ FROM dbo.Student GROUP BY Sname HAVING COUNT(S#)>1) begin SELECT Sname AS ‘姓名’,COUNT(S#) AS ‘同名人数’ FROM dbo.Student GROUP BY Sname HAVING COUNT(S#)>1 end else select 0 as ‘无同名同姓’
（31）查询1981年出生的学生名单；
select * FROM dbo.Student WHERE YEAR(getdate())-Sage=1981
（33）查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
SELECT dbo.Sc.S# ,Sname,AVG(score) AS ‘平均数’ FROM dbo.Sc INNER JOIN dbo.Student ON Sc.S# = Student.S# GROUP BY dbo.Sc.S#,Sname HAVING AVG(score)>85
（34）查询课程名称为“数学”，且分数低于60的学生姓名和分数；
SELECT Sname,score AS ‘分数’ FROM dbo.Sc INNER JOIN dbo.Student ON Sc.S# = Student.S# WHERE C#=(SELECT C# FROM dbo.Course WHERE Cname=‘数学’) AND score<60
（35）查询所有学生的选课情况；
SELECT dbo.Sc.S#,Sname,Cname FROM dbo.Sc INNER JOIN dbo.Course ON Course.C# = Sc.C# INNER JOIN dbo.Student ON Student.S# = Sc.S#
（36）查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
SELECT dbo.Sc.S#,Sname,Cname,score FROM dbo.Sc INNER JOIN dbo.Course ON Course.C# = Sc.C# INNER JOIN dbo.Student ON Student.S# = Sc.S# WHERE Sc.S# NOT IN ( SELECT S# FROM dbo.Sc WHERE score<70 )
（37）查询不及格的课程，并按课程号从大到小排列；
SELECT score,C# FROM dbo.Sc WHERE score<60 ORDER BY C# DESC
（38）查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
SELECT dbo.Sc.S# AS ‘学号’,Sname AS ‘姓名’ FROM dbo.Student INNER JOIN dbo.Sc ON Sc.S# = Student.S# WHERE C#=3 AND score>80
（39）求选了课程的学生人数
SELECT COUNT(DISTINCT S#) AS ‘选课人数’ FROM dbo.Sc
（40）查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
SELECT TOP 1 Sname AS ‘姓名’ , score FROM dbo.Student INNER JOIN dbo.Sc ON Sc.S# = Student.S# WHERE C#=(SELECT dbo.Course.C# FROM dbo.Course INNER JOIN dbo.Teacher ON Teacher.T# = Course.T# WHERE Tname=‘杨艳’) ORDER BY score DESC
（41）查询各个课程及相应的选修人数；
SELECT Cname AS ‘课程名’,COUNT(dbo.Sc.C#) AS ‘选课人数’ FROM dbo.Sc INNER JOIN dbo.Course ON Course.C# = Sc.C# GROUP BY dbo.Sc.C#,Cname
（42）查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select DISTINCT s1.C#,s2.C#,s1.score,s2.score from dbo.Sc as s1, dbo.Sc as s2 where s1.score = s2.score and s1.C# != s2.C#
（43）查询每门课程成绩最好的前两名；
SELECT a.C#,a.S#,a.score FROM SC as a LEFT JOIN dbo.Sc as b ON a.C# = b.C# and a.score<b.score GROUP BY a.S#, a.C#,a.score HAVING count(b.C#)<2 ORDER BY a.C#
SELECT * FROM ( SELECT *,ROW_NUMBER() OVER (PARTITION BY C# ORDER BY score DESC) AS c FROM dbo.Sc )t WHERE c<=2
（44）统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列
SELECT dbo.Sc.C#,COUNT(dbo.Sc.C#) AS ‘选课人数’ FROM dbo.Sc INNER JOIN dbo.Course ON Course.C# = Sc.C# GROUP BY dbo.Sc.C#,Cname HAVING COUNT(Course.C#)>10 ORDER BY 选课人数 DESC
（45）检索至少选修两门课程的学生学号；
SELECT dbo.Sc.S# AS '学号’FROM dbo.Student LEFT JOIN dbo.Sc ON Sc.S# = Student.S# GROUP BY Sc.S#,Sname HAVING COUNT(dbo.Sc.C#)>=2
（46）查询全部学生都选修的课程的课程号和课程名；
SELECT dbo.Sc.C# ,Cname AS ‘课程名’ FROM dbo.Sc INNER JOIN dbo.Course ON Course.C# = Sc.C# GROUP BY Sc.C#,Cname HAVING COUNT(SC.C#)=( SELECT COUNT(*) FROM dbo.Student )
（47）查询没学过“叶平”老师讲授的任一门课程的学生姓名；
SELECT Sname FROM dbo.Student WHERE S# NOT IN(SELECT S# FROM dbo.Sc WHERE C#=(SELECT dbo.Course.C# FROM dbo.Course INNER JOIN dbo.Teacher ON Teacher.T# = Course.T# WHERE Tname=‘叶平’))
（48）查询两门以上不及格课程的同学的学号及其平均成绩；
SELECT S#,AVG(score) AS ‘平均数’ FROM dbo.Sc WHERE S# IN (SELECT S# FROM dbo.Sc WHERE score<60 GROUP BY S# HAVING COUNT(S#)>=2) GROUP BY S#