SQL server 45题

char(3) not null primary key, Sname char(8) not null, Ssex char(2) not null, Sbirthday datetime, Class char(5) ) create table Teacher ( Tno char(3) not null primary key, Tname char(4) not null, Tsex char(2) not null, Tbirthday datetime, Prof char(6), Depart varchar(10) not null ) go create table Course ( Cno char(5) not null primary key, Cname varchar(10) not null, Tno char(3) not null references Teacher(Tno) ) go create table Score ( Sno char(3) not null references Student(Sno), Cno char(5) not null references Course(Cno), Degree Decimal(4,1), primary key(Sno,Cno) ) --向学生表添加数据 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‘) select * from Student --向教师表添加数据 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‘,‘助教‘,‘电子工程系‘) select * from Teacher --向课程表中添加数据 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‘) select * from Course --向成绩表添加数据 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) select * from Score --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=‘女‘ --7、 以Class降序查询Student表的所有记录。 select * from Student order by Class desc --8、 以Cno升序、Degree降序查询Score表的所有记录。 select * from Score order by Cno asc,Degree desc --9、 查询“95031”班的学生人数。 select COUNT(1) from Student where Class=‘95031‘ --10、 查询Score表中的最高分的学生学号和课程号。（子查询或者排序） select Sno,Cno from Score where degree=( select MAX(Degree) from Score ) select top 1 Sno,Cno from Score order by Degree desc --排序 --11、 查询每门课的平均成绩。 select Cno,AVG(Degree) from Score group by Cno --12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 select AVG(Degree) from Score where Cno like ‘3%‘ group by Cno having count(Sno)>=5 --13、查询分数大于70，小于90的Sno列 select sno from Score where Degree>70 and Degree<90 --14、查询所有学生的Sname、Cno和Degree列 select Sname,Cno,Degree from Student join Score on Student.Sno=Score.Sno --15、查询所有学生的Sno、Cname和Degree列 select Sno,Cname,Degree from Score join Course on Course.Cno=Score.Cno --16、查询所有学生的Sname、Cname和Degree列 select Sname,Cname,Degree from Student join Score on Student.Sno=Score.Sno join Course on Course.Cno=Score.Cno --17、查询“95033”班学生的平均分 select AVG(Degree) from Score join Student on Student.Sno=Score.Sno where Class=‘95033‘ --18、 假设使用如下命令建立了一个grade表： --现查询所有同学的Sno、Cno和rank列。 select Sno,Cno,rank from Score join grade on Degree between low and upp --19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。 select * from Score where Cno=‘3-105‘ and Degree>( select Degree from Score where Sno=‘109‘ and Cno=‘3-105‘ ) --20、查询score中选学多门课程的同学中分数为非最高分成绩的记录 select * from Score where Degree not in( select MAX(Degree) from Score ) and Sno in ( select Sno from Score group by Sno having COUNT(*)>1 ) --21、查询成绩高于学号为“109”并且课程号为“3-105”的成绩的所有记录。 select * from Score where Cno=‘3-105‘ and Degree>( select Degree from Score where Sno=‘109‘ and Cno=‘3-105‘ ) --22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列 select Sno,Sname,Sbirthday from Student where YEAR(Sbirthday)=( select YEAR(Sbirthday) from Student where Sno=‘108‘ ) --23、查询“张旭“教师任课的学生成绩 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(Cno)>5 ) ) --25、查询95033班和95031班全体学生的记录 select * from Student where Class=‘95033‘ or Class=‘95031‘ --26、 查询存在有85分以上成绩的课程Cno select distinct Cno from Score where Degree in( select Degree from Score where Degree>85 ) --27、查询出“计算机系“教师所教课程的成绩表。 select * 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 t1 where Depart=‘计算机系‘ and Prof not in( select Prof from Teacher t2 where Depart=‘电子工程系‘ ) union select Tname,Prof from Teacher t1 where Depart=‘电子工程系‘ and Prof not in( select Prof from Teacher t2 where Depart=‘计算机系‘ ) --29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的一个同学成绩的Cno、Sno和Degree,并按Degree从高到低次序排序。 select * from Score where Cno=‘3-105‘ and Degree >any( select Degree from Score where Cno=‘3-245‘ ) order by Degree desc --30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的所有同学成绩的Cno、Sno和Degree select * from Score where Cno=‘3-105‘ and Degree>All( select Degree from Score where Cno=‘3-245‘ ) --31、 查询所有教师和同学的name、sex和birthday. select Sname,Ssex,Sbirthday from Student union select Tname,Tsex,Tbirthday from Teacher --32、查询所有“女”教师和“女”同学的name、sex和birthday select Sname,Ssex,Sbirthday from Student where Ssex=‘女‘ union select Tname,Tsex,Tbirthday from Teacher where Tsex=‘女‘ --33、 查询成绩比该课程平均成绩低的同学的成绩表 select * from Score s1 where Degree<( select AVG(Degree) from Score s2 group by Cno having s1.Cno=s2.Cno ) --34、 查询所有任课教师的Tname和Depart. select Tname,Depart from Teacher where Tno in( select Tno from Course where Cno in( select Cno from Score ) ) --35 、查询所有未讲课的教师的Tname和Depart select Tname,Depart from Teacher where Tno in( select Tno from Course where Cno not in( select Cno from Score ) ) --36、查询至少有2名男生的班号 select class from Student where Ssex=‘男‘ group by Class having COUNT(*)>=2 --37、查询Student表中不姓“王”的同学记录。 select * from Student where SUBSTRING(Sname,1,1) not in(‘王‘) --38、查询Student表中每个学生的姓名和年龄 select sname as ‘姓名‘,YEAR(GETDATE())-YEAR(Sbirthday) as ‘年龄‘ from Student --39、查询Student表中最大和最小的Sbirthday日期值 select MAX(Sbirthday),MIN(Sbirthday) from Student --40、以班号和年龄从大到小的顺序查询Student表中的全部记录。 select * from Student order by Class desc,Sbirthday asc --41、查询“男”教师及其所上的课程 select Cname from Course where Tno in( select Tno from Teacher where Tsex=‘男‘ ) --42、查询最高分同学的Sno、Cno和Degree列 select * from Score s1 where Degree in( --各门课最高分的学生(3个) select max(Degree) from Score s2 group by Cno having s1.Cno=s2.Cno ) --所有课程分数和最大的学生(1个) select * from Score where sno in( select top 1 sno from Score group by Sno Order by SUM(Degree) desc ) --43、查询和“李军”同性别的所有同学的Sname. select Sname from Student where Ssex in( select Ssex from Student where Sname=‘李军‘ ) --44、查询和“李军”同性别并同班的同学Sname select Sname from Student where Ssex in( select Ssex from Student where Sname=‘李军‘ ) and Class in( select Class from Student where Sname=‘李军‘ ) --45、查询所有选修“计算机导论”课程的“男”同学的成绩表 select * from Score where Cno in( select Cno from Course where Cname=‘计算机导论‘ ) and Sno in( select Sno from Student where Ssex=‘男‘ ) --******************************************************************* select * from Student select * from Score select * from Course select * from Teacher

 

SQL server 45题

标签：