--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
)
无关子查询：子查询可以独立执行，执行完之后拿到外层来使用
相关子查询：外层记录拿到里层进行条件比较，符合条件的查询返回，不可以独立执行，里层和外层相互关联