SQL面试题泄露!做完这50道练习题你就过关了!

标签:最新文章

-?点击上方“中国统计网”订阅我吧!-

?文末领取【SQL面试题试卷

使用方法:我用的数据库是 Ms SQL Server 2008 ,练习时应当自己建数据,自己先思考,切勿急躁翻答案!否则效果减半!

做完这些题,恭喜你!你的 SQL 就算过关了!


测试表格


1. 学生表

Student(S#,Sname,Sage,Ssex)?

S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

2. 课程表?

Course(C#,Cname,T#)?

C# --课程编号,Cname 课程名称,T# 教师编号

3. 教师表?

Teacher(T#,Tname)

T# 教师编号,Tname 教师姓名

4. 成绩表?

SC(S#,C#,score)

S# 学生编号,C# 课程编号,score 分数


创建测试数据


1. 学生表 Student

create table Student(S# varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10))insert into Student values("01" , N"赵雷" , "1990-01-01" , N"男")insert into Student values("02" , N"钱电" , "1990-12-21" , N"男")insert into Student values("03" , N"孙风" , "1990-05-20" , N"男")insert into Student values("04" , N"李云" , "1990-08-06" , N"男")insert into Student values("05" , N"周梅" , "1991-12-01" , N"女")insert into Student values("06" , N"吴兰" , "1992-03-01" , N"女")insert into Student values("07" , N"郑竹" , "1989-07-01" , N"女")insert into Student values("08" , N"王菊" , "1990-01-20" , N"女")

2. 科目表 Course

create table Course(C# varchar(10),Cname nvarchar(10),T# varchar(10))insert into Course values("01" , N"语文" , "02")insert into Course values("02" , N"数学" , "01")insert into Course values("03" , N"英语" , "03")

3. 教师表 Teacher

create table Teacher(T# varchar(10),Tname nvarchar(10))insert into Teacher values("01" , N"张三")insert into Teacher values("02" , N"李四")insert into Teacher values("03" , N"王五")

4. 成绩表 SC

create table SC(S# varchar(10),C# varchar(10),score decimal(18,1))insert into SC values("01" , "01" , 80)insert into SC values("01" , "02" , 90)insert into SC values("01" , "03" , 99)insert into SC values("02" , "01" , 70)insert into SC values("02" , "02" , 60)insert into SC values("02" , "03" , 80)insert into SC values("03" , "01" , 80)insert into SC values("03" , "02" , 80)insert into SC values("03" , "03" , 80)insert into SC values("04" , "01" , 50)insert into SC values("04" , "02" , 30)insert into SC values("04" , "03" , 20)insert into SC values("05" , "01" , 76)insert into SC values("05" , "02" , 87)insert into SC values("06" , "01" , 31)insert into SC values("06" , "03" , 34)insert into SC values("07" , "02" , 89)insert into SC values("07" , "03" , 98)

50道练习题


1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

查询同时存在" 01 "课程和" 02 "课程的情况

查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

查询不存在" 01 "课程但存在" 02 "课程的情况

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

3. 查询在 SC 表存在成绩的学生信息

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

查有成绩的学生信息

5. 查询「李」姓老师的数量?

6. 查询学过「张三」老师授课的同学的信息?

7. 查询没有学全所有课程的同学的信息?

8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息?

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息?

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名?

45题的内容实在太多了,剩余的35道题和答案小编已经分别整理成了一份试卷,请各位主动联系客服领取【SQL面试题的试卷】哦~


答案参考


铛铛铛!!答案来了。以下为笔者亲自做的答案,翻到这的时候你的答案也做完了吧!

下面的解析内容可能不是最佳,如有更优解答,有劳各位献上,供大家学习参考!

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

select A.*,B.C#,B.score from (select * from SC where C#="01")A left join(select * from SC where C#="02")B on A.S#=B.S# where A.score>B.score

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

select * from (select * from SC where C#="01")A left join (select * from SC where C#="02")B on A.S#=B.S#where B.S# is not null

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为null)

select * from (select * from SC where C#="01")Aleft join (select * from SC where C#="02")B on A.S#=B.S#

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况?

select * from SC where C#="02"and S# not in(select S# from SC where C#="01")

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

select A.S#,B.Sname,A.dc from(select S#,AVG(score)dc from SC group by S#)Aleft join Student B on A.S#=B.S# where A.dc>=60

3. 查询在 SC 表存在成绩的学生信息?

select * from Student where S# in (select distinct S# from SC)

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)

select B.S#,B.Sname,A.选课总数,A.总成绩 from(select S#,COUNT(C#)选课总数,sum(score)总成绩 from SC group by S#)Aright join Student B on A.S#=B.S#

查有成绩的学生信息

select A.S#,B.Sname,A.选课总数,A.总成绩 from(select S#,COUNT(C#)选课总数,sum(score)总成绩 from SC group by S#)Aleft join Student B on A.S#=B.S#

5. 查询「李」姓老师的数量

select COUNT(*)李姓老师数量 from Teacher where Tname like "李%"

6. 查询学过「张三」老师授课的同学的信息?

select * from Studentwhere S# in(select distinct S# from SC where C#=(select C# from Course where T#=(select T# from Teacher where Tname="张三")))

7. 查询没有学全所有课程的同学的信息

select * from Student where S# in(select S# from SC group by S# having COUNT(C#)<3)

8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

select * from Student where S# in(select distinct S# from SC where C# in(select C# from SC where S#="01"))

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

select * from Student where S# in(select S# from SC where C# in(select distinct C# from SC where S#="01") and S#<>"01"group by S#having COUNT(C#)>=3)

10. 查询没学过「张三」老师讲授的任一门课程的学生姓名

select Sname from Student where S# not in(select S# from SC where C# in(select C# from Course where T# in(select T# from Teacher where Tname="张三")))

End.

作者:flycat296

来源:CSDN

https://blog.csdn.net/flycat296/article/details/63681089

本文为转载分享,如有侵权请联系后台删除

你的SQL过关了吗?过关后想要更高的提升吗?今晚爱数据学院的王老师将为大家分享用Shell语言模拟SQL语句和批量检查SQL语法,想要提升数据开发技能的千万别错过啦!今晚20:30我们在爱数据学院的直播间不见不散哦~


爱数据学院5月公开课

直播主题:数据开发必备技能——shell轻松入门

课程亮点:

3招入门大数据开发语言Shell

Shell批量操作之快速修改文件名

用Shell语言帮你模拟一个SQL语句

批量检查SQL语法怎么做?Shell轻松搞定

直播时间:5月20日 今晚20:30

点击“阅读原文”0元参与课程直播

    相关文章: