50道SQL练习题及答案
--1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select s.* ,sc.score
from Student s ,
(
select sc1.sid ,sc1.score from
(select sid , score from SC where CId = '01') sc1,
(select sid , score from SC where CId = '02') sc2
where sc1.SId = sc2.SId and sc1.score > sc2.score
) sc
where s.SId = sc.SId
--1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select* from
(select * from SC where CId = '01') sc1,
(select * from SC where CId = '02') sc2
where sc1.SId = sc2.SId
--1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为null )
select * from
(select * from SC where CId = '01') sc1
left join
(select * from SC where CId = '02') sc2
on sc1.SId = sc2.SId
--1.3查询不存在" 01 "课程但存在" 02 "课程的情况
select * from SC where SId not in (
select sid from SC where CId = '01')
and cid = '02'
--2.查询平均成绩大于等于60 分的同学的学生编号和学生姓名和平均成绩
select sc.SId ,s.Sname , AVG(sc.score)
from SC sc , Student s
where sc.SId = s.SId
group by sc.SId ,s.Sname having AVG(sc.score)>=60
--3.查询在SC 表存在成绩的学生信息
select distinct s.*
from SC sc , Student s
where sc.SId = s.SId
--4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
select sc1.SId , s.Sname ,sc1.ccd , sc1.scc
from Student s ,
(select sid ,COUNT(cid) ccd , sum(score) scc
from sc group by sid ) sc1
where s.SId = sc1.SId
--4.1 查有成绩的学生信息
select * from Student where SId in
(select distinct SId from sc )
-- 5.查询「李」姓老师的数量
select COUNT(1) from Teacher where Tname like '李%';
--6.查询学过「张三」老师授课的同学的信息
select s.*
from Student s , Sc sc, Course c, Teacher t
where s.SId = sc.SId and sc.CId = c.CId
and c.TId = t.TId and t.Tname = '张三'
--7.查询没有学全所有课程的同学的信息
select * from Student where SId not in
(select sid from SC
group by SId having COUNT(CId) = (select count(1) from Course))
--8.查询至少有一门课与学号为" 07 "的同学所学相同的同学的信息
select * from Student where SId in(
select distinct sid from SC where CId in(
select cid from SC where SId = '07')
)
--9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select * from Student where SId in
(select sid from SC where CId in (
select CId from SC where SId = '01')
group by SId having COUNT(cid) =
(select COUNT(1) from SC where SId = '01'))
--10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select sname from Student where SId not in
(select sc.sid
from SC sc, Course c ,Teacher t
where sc.CId = c.CId and c.TId = t.TId
and t.Tname = '张三')
--11.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select s.SId ,s.sname , AVG(sc.score)
from student s , SC sc
where s.SId = sc.SId and sc.score<60
group by sc.SId ,s.sid, s.sname
having count(*)>1
--12.检索" 01 "课程分数小于60,按分数降序排列的学生信息
select s.*
from student s , SC sc
where s.SId = sc.SId and sc.score<60 and sc.CId = '01'
order by sc.score desc
--13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sc.*,sc1.avgs
from SC sc left join
(select sid, AVG(score) avgs from SC group by sid) sc1
on sc.SId = sc1.SId
order by sc1.avgs desc
--14.查询各科成绩最高分、最低分和平均分:
select CId,MAX(score),MIN(score),AVG(score) from SC group by cid
--15.按各科成绩进行排序,并显示排名,Score 重复时保留名次空缺
select CId,sid,score,dense_rank () over(partition by cid order by cid , score desc) from SC
/*row_number 返回分组后的连续排序,不会出现重复的排序值*/ -- 1,2,3,4,5,6
/*rank 返回分组后的连续排序,会出现跳跃排序值*/ -- 1,1,3,4,5,6
/*dense_rank 返回分组后的连续排序,不会出现跳跃排序值,但是会出现重复的排序值*/ --1,1,2,3,4,5
--16.查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
select sid ,ss , rank() over(order by ss desc) from
(select sid , SUM(score) ss from sc group by sid) cs1
--17.统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-- sum not count
select sc.CId ,c.Cname,
sum(case when sc.score <=100 and sc.score >85 then 1 else 0 end) as '[100-85]',
sum(case when sc.score <=85 and sc.score >70 then 1 else 0 end) as '[85-70]',
sum(case when sc.score <=70 and sc.score >60 then 1 else 0 end) as '[70-60]',
sum(case when sc.score <=60 and sc.score >0 then 1 else 0 end) as '[60-0]'
from SC sc , Course c
where sc.CId = c.CId
group by sc.CId ,c.Cname
--18.查询各科成绩前三名的记录
--select CId,score from SC group by CId,score order by cid,score desc
-- 计算比自己分数大的记录有几条,如果小于就select,因为对前三名来说不会有个及以上的分数比自己大了,最后再对所有select到的结果按照分数和课程编号排名即可。
select * from SC sc
where (select COUNT(*) from SC a where sc.CId = a.cid and sc.score < a.score) <3
order by CId , score desc
select * from (
select cid , sid ,score ,
ROW_NUMBER() over( partition by cid order by score desc) rk from sc
)sc1 where rk in (1,2,3)
--19.查询每门课程被选修的学生数
select cid , COUNT(*) cs from SC group by cid
--20.查询出只选修两门课程的学生学号和姓名
select s.SId , s.Sname
from Student s ,
(select sid from SC group by SId having COUNT(*) = 2) cc
where s.SId = cc.sid
--21.查询男生、女生人数
select Ssex , COUNT(*)csex from Student group by Ssex
--22.查询名字中含有「风」字的学生信息
select * from Student where Sname like '%风%'
--23.查询同名学生名单,并统计同名人数
select Sname ,COUNT(*) rs from Student group by Sname having COUNT(*) >1
--24.查询1990 年出生的学生名单
select * from Student where DATEPART(YYYY,Sage)='1990'
select * from Student where YEAR(Sage)='1990'
--25.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select CId , AVG(score) from SC group by cid order by AVG(score) desc , CId
--26.查询平均成绩大于等于80 的所有学生的学号、姓名和平均成绩
select s.SId , s.Sname , sc.avgs
from Student s ,
(select sid,AVG(score) avgs from SC group by SId having AVG(score)>=80 )sc
where s.SId = sc.SId
select s.SId , s.Sname , AVG(sc.score)
from Student s , SC sc
where s.SId = sc.SId group by s.SId , s.Sname having AVG(sc.score)>80
--27.查询课程名称为「数学」,且分数低于60 的学生姓名和分数
select s.Sname , sc.score
from Student s, SC sc , Course c
where s.SId = sc.SId and sc.CId = c.CId
and c.Cname = '数学' and sc.score<60
--28.查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
select s.Sname , sc.score
from Student s left join SC sc
on s.SId = sc.SId
--29.查询任何一门课程成绩在70 分以上的姓名、课程名称和分数
select s.Sname , c.Cname ,sc.score
from Student s , SC sc , Course c
where s.SId = sc.SId and sc.CId = c.CId
and sc.score>70
--30.查询存在不及格的课程
select c.Cname , sc.score
from SC sc , Course c
where sc.cid = c.cid and sc.score < 60
--31.查询课程编号为01 且课程成绩在70 分及以上的学生的学号和姓名
select s.SId , s.Sname
from Student s , SC sc
where s.SId = sc.SId and sc.CId = '01' and sc.score>70
--32.求每门课程的学生人数
select cid , count(*)rs from SC group by cid
--33.成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select top 1 student.*, sc.score, sc.cid from student, teacher, course,sc
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = '张三'
order by score desc
--34.成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select s.* , sc2.score
from Student s right join
(select * from (
select sc.SId ,sc.score ,
dense_rank() over(order by sc.score desc) rk
from SC sc , Course c , Teacher t
where sc.CId = c.CId and c.TId = t.TId
and t.Tname= '张三'
) sc1 where rk =1) sc2
on s.SId = sc2.SId
select student.*, sc.score, sc.cid from student, teacher, course,sc
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = '张三'
and sc.score = (
select Max(sc.score)
from sc,student, teacher, course
where teacher.tid = course.tid
and sc.sid = student.sid
and sc.cid = course.cid
and teacher.tname = '张三'
);
--35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select sc1.SId , sc1.cid , sc1.score
from SC sc1 , sc sc2
where sc1.SId= sc2.SId and sc1.CId!=sc2.CId and sc1.score = sc2.score
--36.查询每门功成绩最好的前两名
select * from (
select cid , sid ,score ,
ROW_NUMBER() over( partition by cid order by score desc) rk from sc
)sc1 where rk in (1,2)
-- 37.统计每门课程的学生选修人数(超过5 人的课程才统计)
select cid , COUNT(cid) from SC group by CId having COUNT(cid)>5
--38.检索至少选修三门课程的学生学号
select sid from SC group by sid , sid having count(sid) > 2
--39.查询选修了全部课程的学生信息
select s.*
from Student s where SId in(
select sid from SC group by sid having count(sid) =
(select distinct count(cid) from Course)
)
--40.查询各学生的年龄,只按年份来算
select *,datediff(YEAR,sage,GETDATE()) sage from Student
--41.按照出生日期来算,当前月日< 出生年月的月日则,年龄减一
--42.查询本周过生日的学生
select * from Student where
datediff(week,dateadd(yy,DATEDIFF(YY,Sage,GETDATE()),Sage),GETDATE())=0
--43.查询下周过生日的学生
select * from Student where
datediff(week,dateadd(yy,DATEDIFF(YY,Sage,GETDATE()),Sage),DATEADD(WEEK,1,GETDATE()))=0
-- 下周时间,从周日开始
select DATEADD(WEEK,1,GETDATE())
-- getdate() --2019-07-28 17:39:27.893 周日 [本周07-28 -- 08-03]
select datediff(week,dateadd(yy,DATEDIFF(YY,'2010-08-06',GETDATE()),'2010-08-06'),GETDATE()) -- print -1
select datediff(week,dateadd(yy,DATEDIFF(YY,'2010-07-22',GETDATE()),'2010-08-22'),GETDATE()) -- print -3
-- 44.查询本月过生日的学生
select * from Student where MONTH(sage) = MONTH(GETDATE())
--45.查询下月过生日的学生
select * from Student where MONTH(sage) = MONTH(GETDATE())+1
CREATE TABLE [dbo].[Teacher](
[TId] [varchar](10) NULL,
[Tname] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Teacher] ([TId], [Tname]) VALUES (N'01', N'张三')
INSERT [dbo].[Teacher] ([TId], [Tname]) VALUES (N'02', N'李四')
INSERT [dbo].[Teacher] ([TId], [Tname]) VALUES (N'03', N'王五')
CREATE TABLE [dbo].[Course](
[CId] [varchar](10) NULL,
[Cname] [nvarchar](10) NULL,
[TId] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Course] ([CId], [Cname], [TId]) VALUES (N'01', N'语文', N'02')
INSERT [dbo].[Course] ([CId], [Cname], [TId]) VALUES (N'02', N'数学', N'01')
INSERT [dbo].[Course] ([CId], [Cname], [TId]) VALUES (N'03', N'英语', N'03')
CREATE TABLE [dbo].[SC](
[SId] [varchar](10) NULL,
[CId] [varchar](10) NULL,
[score] [decimal](18, 1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'01', N'01', CAST(80.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'01', N'02', CAST(90.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'01', N'03', CAST(92.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'02', N'01', CAST(70.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'02', N'02', CAST(60.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'02', N'03', CAST(80.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'03', N'01', CAST(60.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'03', N'02', CAST(90.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'03', N'03', CAST(60.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'04', N'01', CAST(50.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'04', N'02', CAST(30.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'04', N'03', CAST(65.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'05', N'01', CAST(70.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'05', N'02', CAST(87.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'06', N'01', CAST(31.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'06', N'03', CAST(34.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'07', N'02', CAST(89.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'07', N'03', CAST(56.0 AS Decimal(18, 1)))
INSERT [dbo].[SC] ([SId], [CId], [score]) VALUES (N'08', N'01', NULL)
CREATE TABLE [dbo].[Student](
[SId] [varchar](10) NULL,
[Sname] [varchar](10) NULL,
[Sage] [datetime] NULL,
[Ssex] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'01', N'赵雷', CAST(0x0000806800000000 AS DateTime), N'男')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'02', N'钱电', CAST(0x000081CA00000000 AS DateTime), N'男')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'03', N'孙风', CAST(0x000081C900000000 AS DateTime), N'男')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'04', N'李云', CAST(0x000081BB00000000 AS DateTime), N'男')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'05', N'周梅', CAST(0x000082A600000000 AS DateTime), N'女')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'06', N'吴兰', CAST(0x0000841B00000000 AS DateTime), N'女')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'07', N'郑竹', CAST(0x00007EFB00000000 AS DateTime), N'女')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'09', N'张三', CAST(0x0000A84F00000000 AS DateTime), N'女')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'10', N'李四', CAST(0x0000A85400000000 AS DateTime), N'女')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'11', N'李四', CAST(0x0000A06800000000 AS DateTime), N'女')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'12', N'赵六', CAST(0x0000A1DC00000000 AS DateTime), N'女')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'13', N'孙七', CAST(0x0000A33D00000000 AS DateTime), N'女')
INSERT [dbo].[Student] ([SId], [Sname], [Sage], [Ssex]) VALUES (N'14', N'张三', CAST(0x0000A33D00000000 AS DateTime), N'女')