50道SQL练习题详解-CSDN博客

本文链接：https://blog.csdn.net/u010835426/article/details/97619488

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'女')