SQL 经典练习题

create database 练习题
go
use 练习题
go

create table Student
(
 Sno char(3) primary key,
 Sname char(8) not null,
 Ssex char(2) not null,
 Sbirthday date ,
 Class char(5)
)
go
create table Teacher
(
 Tno char(3) primary key,
 Tname char(4) not null,
 Tsex char(2) not null,
 Tbirthday date ,
 Prof char(6),
 Depart varchar(10) not null
)
go
create table Course
(
 Cno char(5) primary key,
 Cname varchar(10) not null,
 Tno  char(3) references [dbo].[Teacher]([Tno]) not null
)
go
create table Score
(
 Sno char(3) references [dbo].[Student]([Sno]) ,
 Cno char(5) references [dbo].[Course]([Cno]) ,
 primary key (Sno,Cno),
 Degree decimal(4,1)
)
go
--------------------Student------------------
insert into Student values (‘108‘,‘曾华‘,‘男‘,‘1977-09-01‘,‘95033‘)
insert into Student values (‘105‘,‘匡明‘,‘男‘,‘1975-10-02‘,‘95031‘)
insert into Student values (‘107‘,‘王丽‘,‘女‘,‘1976-01-23‘,‘95033‘)
insert into Student values (‘101‘,‘李军‘,‘男‘,‘1976-02-20‘,‘95033‘)
insert into Student values (‘109‘,‘王芳‘,‘女‘,‘1975-02-10‘,‘95031‘)
insert into Student values (‘103‘,‘陆君‘,‘男‘,‘1974-06-03‘,‘95031‘)
-------------Teacher--------------
insert into Teacher values (‘804‘,‘李诚‘,‘男‘,‘1958-12-02‘,‘副教授‘,‘计算机系‘)
insert into Teacher values (‘856‘,‘张旭‘,‘男‘,‘1969-03-12‘,‘讲师‘,‘电子工程系‘)
insert into Teacher values (‘825‘,‘王萍‘,‘女‘,‘1972-05-05‘,‘助教‘,‘计算机系‘)
insert into Teacher values (‘831‘,‘刘冰‘,‘女‘,‘1977-08-14‘,‘助教‘,‘电子工程系‘)
-----------------course---------------------
insert into Course values (‘3-105‘,‘计算机导论‘,‘825‘)
insert into Course values (‘3-245‘,‘操作系统‘,‘804‘)
insert into Course values (‘6-166‘,‘数字电路‘,‘856‘)
insert into Course values (‘9-888‘,‘高等数学‘,‘831‘)
------------------Score--------------------
insert into Score values (‘103‘,‘3-245‘,‘86‘)
insert into Score values (‘105‘,‘3-245‘,‘75‘)
insert into Score values (‘109‘,‘3-245‘,‘68‘)
insert into Score values (‘103‘,‘3-105‘,‘92‘)
insert into Score values (‘105‘,‘3-105‘,‘88‘)
insert into Score values (‘109‘,‘3-105‘,‘76‘)
insert into Score values (‘101‘,‘3-105‘,‘64‘)
insert into Score values (‘107‘,‘3-105‘,‘91‘)
insert into Score values (‘108‘,‘3-105‘,‘78‘)
insert into Score values (‘101‘,‘6-166‘,‘85‘)
insert into Score values (‘107‘,‘6-166‘,‘79‘)
insert into Score values (‘108‘,‘6-166‘,‘81‘)

go

--==================================================
--1、 查询Student表中的所有记录的Sname、Ssex和Class列。
select sname,ssex,class from student
--2、 查询教师所有的单位即不重复的Depart列。
select distinct depart from teacher
--3、 查询Student表的所有记录。
select * from student
--4、 查询Score表中成绩在60到80之间的所有记录。
select * from score where degree between 60 and 80
--5、 查询Score表中成绩为85，86或88的记录。
select * from score where degree in(85,86,88)

--6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student where class=‘95031‘ or ssex=‘女‘
--此处where条件后面使用到or，or是一个逻辑运算符，
--是或者的意思，满足任意一个条件即返回true

--7、 以Class降序查询Student表的所有记录。
select * from student order by class desc
--此处使用到排序语法order by 列名 asc或者desc，意思是对某一列升序或者降序排列

--8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from score order by cno asc,degree desc
--此处是对两列进行排序，当对两列进行排序的时候，
--会优先对order by后的第一列进行排序，
--然后在不影响第一列排序的情况下，再对第二列进行排序

--=============================================================
--9、 查询“95031”班的学生人数。
select count(*) from student where class=‘95031‘
--此处使用到聚合函数count(*)，意思是按where选择条件查询后计算数据个数

--10、 查询Score表中的最高分的学生学号和课程号。
select top 1 sno,cno from score order by degree desc
--此处结合排序使用，按分数排序后，查第一个数据，
--使用到top关键字，top 数字，代表取前几条

--11、 查询每门课的平均成绩
select cno,avg(degree) from score group by cno
--此处使用到聚合函数avg(),以及group by
--avg(列) 意思是求一列的平均值
--group by 是对某一列进行分组，列值相同的分到一组中
--当group by 结合其他聚合函数使用时候，会先分组，再分别操作每组数据

--12、            查询‘3-105’号课程的平均分。
select cno,avg(degree) from score group by cno having  cno=‘3-105‘
select * from score

--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(degree) from score group by cno having  cno like ‘3%‘ and count(sno)>=5
--此题用到了having选择条件，having是配合group by使用的
--意思是在分组前提下，选择符合条件的某些数据然后进行操作
--select与from之间还是只能用聚合函数或者group by的那一列

--13、查询分数大于70，小于90的Sno列。
select sno from score where degree>70 and degree<90
--使用and连接两个选择条件，因为此处说的是大于小于，所以不能使用between and

--==============================================================

--14、查询所有学生的Sname、Cno和Degree列。
select sname,cno,degree from student a,score b where a.sno=b.sno
select sname,cno,DEGREE from student join Score on Student.Sno=Score.Sno
--此题首先将两个表进行表连接，通过where条件说明关系，然后再查询数据

--15、查询所有学生的Sno、Cname和Degree列。
select sno,cname,degree from score a,course b where a.cno=b.cno
--此题同14题，进行表连接然后查询对应列

select b.sno,cname,degree from student a,score b,course c
where a.sno=b.sno and c.cno=b.cno

--16、查询所有学生的Sname、Cname和Degree列。
select sname,cname,degree from student a inner join score b on a.sno=b.sno inner join course c on c.cno=b.cno
--此题是三表联合查询，使用join进行连接，join表on关系条件

--17、            查询“95033”班学生的平均分。
select avg(degree) from score  where sno  in (select sno from student where class=‘95033‘)
--此处使用子查询，in括号内本来应该放参数，
--使用查询语句查询出一列来当做参数使用，注意必须是一列

--18、            假设使用如下命令建立了一个grade表：
create table grade(low  int,upp  int,rank  char(1))
insert into grade values(90,100,‘A‘)
insert into grade values(80,89,‘B‘)
insert into grade values(70,79,‘C‘)
insert into grade values(60,69,‘D‘)
insert into grade values(0,59,‘E‘)
--现查询所有同学的Sno、Cno和rank列(即分数表的分数对应级别显示)。
select sno,cno,degree,rank from score,grade where degree between low and upp
select *from grade
--此题首先创建表，然后插入数据再进行查询，进行表连接之后通过degree与高低范围建立关系

--19、  查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select *from student where Sno in(select sno from Score where Cno=‘3-105‘ and Degree>(select Degree from Score where Cno=‘3-105‘ and Sno=‘109‘))

--最里面一个子查询查出109号学生的3-105这门课的分数，然后作为参数比较
--中间一个子查询是查出比刚才查出的分数高的同学的学号
--然后最后根据这些学号查出所有信息

--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
select *from Score as a where Sno in(select sno from Score as b group by sno having COUNT(sno)>1 )
and Degree <(select max(Degree) from Score as c where c.Cno=a.Cno)
--此题使用子查询查询出按学号分组后的个数大于1的学号信息,
--然后根据学号查分数信息并要满足外面表的分数小于分组后每组的最高分

--21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select a.sname,a.sno,b.cno,b.degree from student a,score b where a.sno=b.sno
and b.degree>all(select degree from score where sno=‘109‘ and cno=‘3-105‘)
--此处使用all关键字，all结合关系运算符> < =来使用，
--此处表示degree列里的数据满足大于后面查询出来的degree列中的所有数据才符合条件

--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select sno,sname,sbirthday from student
where year(sbirthday)=
year((select sbirthday from student where sno=‘108‘)) --and sno<>‘108‘
--此题使用取年份的时间日期函数，可以获取时间中的年份返回一个整数值
 
--23、查询“王萍“教师任课的学生成绩。
select a.tname,b.degree,c.cno from teacher a,score b,course c
where a.tno=c.tno and b.cno=c.cno and tname=‘王萍‘

select *from Score where Cno in (select Cno from Course where Tno in(select Tno from Teacher where Tname=‘王萍‘))
--连接查询，加选择条件和关系

--24、查询选修某课程的同学人数多于5人的教师姓名。
select tname from teacher where tno in (
select tno from course where cno in(
select cno from score group by cno having count(sno)>5 )
)
--此题使用子查询，逻辑是先查人数多于5的课程号
--再查这些课程号对应的教师号
--再根据教师号查教师姓名，然后逆向组合起来

--25、查询95033班和95031班全体学生的记录。
select * from student where class=‘95033‘ or class=‘95031‘
--逻辑或条件关联查询，满足任意一个条件即查询出数据

--26、查询存在有85分以上成绩的课程Cno.
select distinct cno from score where degree>85
--使用distinct 关键字去重并加where选择条件筛选

--27、查询出“计算机系“教师所教课程的成绩表。
 select cno ,degree from score where cno in (
 select cno from course where tno in (
 select tno from teacher where depart=‘计算机系‘))
 --子查询，根据计算系只能查询出教师编号
 --查出教师号后可以查出对应的课程号，然后根据课程号查分数表

--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select Tname,Prof from Teacher where Prof<>
(select Prof from Teacher group by Prof having count(Prof)>=2)
--第二种解法：
select *from Teacher where Prof not in(
select prof from Teacher where Depart=‘计算机系‘ and Prof in (
select prof from Teacher where Depart=‘电子工程系‘))

select prof,tname from Teacher where Prof not in(
select prof from Teacher where Depart=‘计算机系‘ and
Prof in (select Prof from Teacher where Depart=‘电子工程系‘))
and Depart in(‘计算机系‘,‘电子工程系‘)

--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select cno,sno,degree from score where cno=‘3-105‘ and degree >any (
select degree from score where cno=‘3-245‘)
order by degree desc
--第一种解法使用any,any是满足大于任何一个就可以，也可以用大于最小的那个值来操作替代any
select cno,sno,degree from score where cno=‘3-105‘ and degree > (
select min(degree) from score where cno=‘3-245‘)
order by degree desc

--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select cno,sno,degree from score where cno=‘3-105‘ and
degree >all (select degree from score where cno=‘3-245‘) order by degree desc
--此题使用子查询，然后使用all来操作使3-105的大于所有3-245的才筛选出来

--31、查询所有教师和同学的name、sex和birthday.
select sname 姓名,ssex 性别,sbirthday 生日 from  student
union
select tname,tsex,tbirthday from teacher
--这个是将两个表的数据上下拼接起来，使用union关键词来纵向拼接，
--然后上面的列的数据类型要和下面的列的数据类型对应

--32、查询所有“女”教师和“女”同学的name、sex和birthday.
select sname 姓名,ssex 性别,sbirthday 生日 from  student where ssex=‘女‘
union
select tname,tsex,tbirthday from teacher where tsex=‘女‘
--此题同上，只不过每个查询后加了一个where进行筛选选择

--33、查询成绩比该课程平均成绩低的同学的成绩表。

select sno,cno,degree  from score a where degree<(
select avg(degree) from score b group by cno having b.cno=a.cno)
--此题是运用子查询,并在子查询中进行分组,并将分组后的数据与外表的列值建立筛选关系

--34、 查询所有任课教师的Tname和Depart.
select tname,depart from teacher where tno  in (select tno from course)

--子查询,去教师表里查教师标号在课程表里有的那些,将课程表里的教师编号作为参数使用

--35   查询所有未讲课的教师的Tname和Depart.
select tname,depart from teacher where tno not in (select tno from course)
--同上题,只是去相反范围,在in前面加not，not的意思是非的意思

--36、查询至少有2名男生的班号。
select class from student where ssex=‘男‘ group by class having count(sno)>=2
--分组加having筛选

--37、查询Student表中不姓“王”的同学记录。
select * from student where sname not like ‘王%‘
--此处使用到like模糊查询，like的意思是好像的意思，在字符串中加%,%代表任意一串字符

--38、查询Student表中每个学生的姓名和年龄。
select sname,year(getdate())-year(sbirthday) 年龄 from student
--此处使用取当前日期函数getdate(),另外使用取年份函数year(),并运用减法运算

--39、查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday),min(sbirthday) from student
--此题使用聚合函数取最大最小值

--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from student order by class desc,sbirthday asc
--使用排序order by ，然后先按class排，在不影响class排序的情况下排sbirthday

--41、查询“男”教师及其所上的课程。
select tname,cname from teacher a,course b where a.tno=b.tno and tsex=‘男‘
--表连接,将两个表通过关系拼接,然后加条件筛选

--42、查询最高分同学的Sno、Cno和Degree列。
select top 1 cno,sno,degree from score  order by degree desc
--使用order by 将分数排序后，取第一个使用top 1来获取第一条数据

--43、查询和“李军”同性别的所有同学的Sname.
select sname from student where ssex= (select ssex from student where sname=‘李军‘) and sname <>‘李军‘
--子查询，查出李军的性别作为where选择条件的参数，然后and是排除李军，使用到不等号<>

--44、查询和“李军”同性别并同班的同学Sname.
select sname from student where ssex= (select ssex from student where sname=‘李军‘)
and sname <>‘李军‘ and class in(select class from student where sname=‘李军‘)
--分别使用子查询查出李军的班级和性别作为where选择条件的参数，并用and连接选择条件

--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select * from score where sno in (select sno from student where ssex=‘男‘)
and cno in(select cno from course where cname=‘计算机导论‘)
--子查询，子查询的一个根本道理就是，查出一列作为参数使用

--======================================================================
select * from student
select * from score
select * from course
select * from teacher