数据库查询习题整理（二）

--26、查询存在有85分以上成绩的课程Cno. 需要用distinct除重

select distinct cno from score where  degree>85

--27、查询出“计算机系“教师所教课程的成绩表。

三个表join起来，筛选条件depart为计算机系
 select score.cno,score.sno,score.degree from score
 join course on score.cno=course.cno
 join teacher on course.tno=teacher.tno
 where depart like '计算机系'

--28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。//相关子查询

1.选计算机系的所有教师的职称和电子工程系的教师的职称不一样的

2.选电子工程系的所有教师的职称和计算机系的教师的职称不一样的

3.联合起来
select *from teacher t1 where depart='计算机系'and not exists
(
select *from teacher t2 where depart='电子工程系' and t1.prof=t2.prof
)
union
select *from teacher t1 where depart='电子工程系'and not exists
(
select *from teacher t2 where depart='计算机系' and t1.prof=t2.prof
)

--29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低
--查询3-105成绩大于3-245最高分所有成绩记录
select *from score
where cno='3-105'and degree >
(select max(degree) from score where cno='3-245')order by degree desc

--30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.//相关子查询

选出同时选修这两门课的学生的sno

选出选修3-325这门课的学生的成绩

查询选修3-105且成绩高于选修3-325成绩的学生

select *from score s1 where sno in
(
select sno from score where cno in('3-105','3-245')group by sno having COUNT(*)>1
)
and cno='3-105' and degree>
(
select degree from score s2 where sno in
(
select sno from score where cno in('3-105','3-245')group by sno having COUNT(*)>1
)and cno='3-245' and s2.sno=s1.sno
)

--31、查询所有教师和同学的name、sex和birthday.用联合union
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 where s1.cno=s2.cno group by cno
)

--34、查询所有任课教师的Tname和Depart.
select tname,depart from teacher

--35  查询所有未讲课的教师的Tname和Depart. //遇到null是用is 不是用=

第一种方法用join，leftjoin来筛选sno为空的列
select tname,depart from teacher
left join course on teacher.tno=course.tno
left join score on course.cno=score.cno
where score.sno is null
--第二种方法用无关子查询：
select tname,depart from teacher where tno in
(
select tno from course where cno not in
(
select distinct cno from score
)
)

--36、查询至少有2名男生的班号。
select class from student where ssex='男' group by class having COUNT(*)>1

--37、查询Student表中不姓“王”的同学记录。
select *from student where sname not like '王%'

--38、查询Student表中每个学生的姓名和年龄。
 select sname,YEAR(GETDATE())-YEAR(sbirthday)from student  --get date()是取现在的时间

--39、查询Student表中最大和最小的Sbirthday日期值。

select MAX(sbirthday),min(sbirthday)from student

--40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select *from student order by class desc,sbirthday asc

--41、查询“男”教师及其所上的课程。//在select 后面写course.*就是输出course的所有列
select tname,tsex,cname from course
join teacher on course.tno =teacher.tno
where tsex='男'

--42、查询最高分同学的Sno、Cno和Degree列。
select sno,cno,degree from score
where degree=(select MAX(degree)from score
)

--43、查询和“李军”同性别的所有同学的Sname.
select sname from student
where ssex=(select ssex from student where sname='李军'
)

--44、查询和“李军”同性别并同班的同学Sname.
select sname from student
where ssex=(select ssex from student where sname='李军'
) and class=(select class from student where sname='李军'
)

--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select student.sno,sname,ssex,cname,degree from student
join score on student.sno=score.sno
join course on score.cno= course.cno
where student.ssex ='男' and course.cname='计算机导论'

--46、查询score表中分数最高的学生的信息。//多层嵌套
select student.*from student
join score on student.sno=score.sno
where degree=(select MAX(degree)from score
)

--47、查询score表中的平均分在80分以上的学生信息。

按照sno分组，求出平均值大于80分的学生的sno

用sno来筛选score

select *from student where sno in(
select sno from score group by sno having AVG(degree)>80
)

无关子查询：子查询可以独立执行，执行完之后拿到外层来使用

相关子查询：外层记录拿到里层进行条件比较，符合条件的查询返回，不可以独立执行，里层和外层相互关联