SQL server 练习查询45题（18-45）及笔记4

--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‘)
select *from grade
select*from student
select*from course
--现查询所有同学的Sno、Cno和rank列。
select sno from  student full join course 
select * from course 
select rank from grade 
--两个表连接，degree 这一列 按grade表中low和upp列的范围进行筛选，最后符合条件的换成rank列中对应的字母
select Sno,Cno,rank from score join grade on degree between low and upp order by rank 
--19、  查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from course 
select * from score where Cno=‘3-105‘
select * from score where Degree not in(select MAX(Degree)from score where Sno=109)and Cno=‘3-105‘
select x.Sno,x.Cno,x.degree from score x,score y --这是笛卡尔积的查询方法,不好
where x.cno=‘3-105‘ and x.degree>y.degree and y.sno=‘109‘and y.cno=‘3-105‘
--20、查询score中选学多门课程的同学中分数为(非最高分成绩)的记录。
select Sno from score where Degree not in(select top 1 MAX(Degree)as fenshu from score group by Sno having COUNT(Sno)>1 order by fenshu desc) group by Sno having COUNT(Sno)>1
--21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select *from score where Degree>(select Degree from score where Sno=109 and Cno=‘3-105‘)
--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
--datename(year,日期) 这个函数截取日期的年
select sno,sname,sbirthday from student where DATENAME(YEAR,Sbirthday)=DATENAME(YEAR,(select Sbirthday from student where Sno=109))
--23、查询“张旭“教师任课的学生成绩。
select*from Teacher where Tname=‘张旭‘
select*from score
select*from course
select sno,Degree from score join course on score.Cno=course.Cno and course.Tno=(select Tno from Teacher where Tname=‘张旭‘)
--24、查询选修某课程的同学人数多于5人的教师姓名。
select*from Teacher
select*from course
select*from score
select cno from score  group by Cno having COUNT(*)>5 --查询课程人数多于5人的课程号
select tno from course where Cno=(select cno from score  group by Cno having COUNT(*)>5)--查询此课程号的任课老师代号
select Tname from Teacher where 
Tno=(select tno from course where Cno=(select cno from score  group by Cno having COUNT(*)>5))--查询此老师是谁
--25、查询95033班和95031班全体学生的记录。
select*from Teacher
select*from course
select*from score
select*from student
select * from student where class in(‘95033‘,‘95031‘)
--26、  查询85分以上成绩的课程Cno
select distinct cno from score where Degree>85
--27、查询出“计算机系“教师所教课程的成绩表。
select Tno from Teacher where Depart=‘计算机系‘--查询计算机系老师的Tno
select Cno from course where Tno in(select Tno from Teacher where Depart=‘计算机系‘)--查询cno
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 depart=‘计算机系‘ and prof not in (select prof from teacher where depart=‘电子工程系‘) or 
depart=‘电子工程系‘ and prof not in (select prof from teacher where depart=‘计算机系‘)
select *from Teacher a where Depart=‘计算机系‘ and Prof not in(select Prof from Teacher b where a.Prof=b.Prof and Depart=‘电子工程系‘)
union 
select *from Teacher a where Depart=‘电子工程系‘ and Prof not in(select Prof from Teacher b where a.Prof=b.Prof and Depart=‘计算机系‘)

--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select *from course
select*from score
select min(Degree) from score where Cno=‘3-245‘
select Cno,Sno,Degree from score where Degree>(select min(Degree) from score where Cno=‘3-245‘)and Cno=‘3-105‘ order by Degree desc
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 Cno,Sno,Degree from score where Degree>(select max(Degree) from score where Cno=‘3-245‘)and Cno=‘3-105‘ order by Degree desc
select * from score where cno=‘3-105‘ and degree>all(select degree from score where cno=‘3-245‘)
--31、 查询所有教师和同学的name、sex和birthday.
--union 合并数据集。将两个或者多个查询结果组成一个结果集
select * from Teacher
select*from student
select Tname as name,Tsex as sex,Tbirthday as birthday from Teacher union select Sname,Ssex,Sbirthday from student
--32、查询所有“女”教师和“女”同学的name、sex和birthday.
select Tname as name,Tsex as sex,Tbirthday as birthday from Teacher where Tsex=‘女‘ 
union select Sname,Ssex,Sbirthday from student where Ssex=‘女‘
--33、 查询成绩比该课程平均成绩低的同学的成绩表
select * from score a where degree<(select avg(degree)from score b where a.cno=b.cno)
--34、 查询所有任课教师的Tname和Depart.
--exists 返回的是一个bool值，可以理解为其内部有一个（select from）查询语句
--一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，
--这一行可作为外查询的结果行，否则不能作为结果。
select Tname,Depart from Teacher
select tname,depart from teacher a where exists (select * from course b where a.tno=b.tno)
--exists (select * from course b where a.tno=b.tno) 这个查询语句可以通俗理解为：先查询主键表与外键表对应的老师编号，然后筛选出返回的true值
--35 、 查询所有未讲课的教师的Tname和Depart.
select*from Teacher
select*from course 
select tname,depart from teacher a where not exists (select * from course b where a.tno=b.tno)
--36、查询至少有2名男生的班号。
select*from student where Ssex=‘男‘
select Class from student where Ssex=‘男‘ group by Class having COUNT(*)>=2 
--这条语句大体执行顺序，先筛选出男生，然后按班级分组，再筛选大于2人的班号
--37、查询Student表中不姓“王”的同学记录。
select*from student where Sname not like ‘王%‘
--38、查询Student表中每个学生的姓名和年龄。
--DATEDIFF(datepart,startdate,enddate) 截取两个日期之间的差值 
--getdate() 截取当前系统日期和时间
select Sname,DATEDIFF(YEAR,Sbirthday,GETDATE())as age from student 
--39、查询Student表中最大和最小的Sbirthday日期值。
--截取日期的某一部分
--day() month() year()
select MAX(Sbirthday)as 最小生日,MIN(Sbirthday)as 最大生日 from student

--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select class,sname,sbirthday from student order by class desc,sbirthday
--41、查询“男”教师及其所上的课程。
select Tno from Teacher where Tsex=‘男‘--查询男教师
select Cname from course where Tno in(select Tno from Teacher where Tsex=‘男‘)
select x.tname,y.cname from teacher x,course y where x.tno=y.tno and x.tsex=‘男‘
--42、查询最高分同学的Sno、Cno和Degree列。
select MAX(Degree) from score --查询最高分
select*from score where Degree=(select MAX(Degree) from score) 
--43、查询和“李军”同性别的所有同学的Sname.
select Ssex from student where Sname=‘李军‘
select Sname from student where Ssex=(select Ssex from student where Sname=‘李军‘) and Sname!=‘李军‘
--44、查询和“李军”同性别并同班的同学Sname.
select Sname from student where Ssex=(select Ssex from student where Sname=‘李军‘) and Sname!=‘李军‘and 
Class=(select Class from student where Sname=‘李军‘)
--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select*from course
select*from score
select Sno from student where Ssex=‘男‘ 
select Sno from score where Cno=(select Cno from course where Cname=‘计算机导论‘)--查询学习该课程的学生
--思路 筛出男生学号 并且 课程号是计算机导论
select * from score where Sno in (select Sno from student where Ssex=‘男‘) and Cno=(select Cno from course where Cname=‘计算机导论‘)