文章目录
一、针对教学管理数据库SCT，进行以下各种嵌套查询与集合查询
1.查询选修了“数据库”课程的学生信息
select *
from STUDENT055
where SNO in
(select SNO
from SCT055
where CNO in
(select CNO
from COURSE055
where CNAME = '数据库'
)
);
2.查询与学生“李维”在同一个院系的学生选课信息（包括李维）
select *
from SCT055
where SNO in
(select SNO
from STUDENT055
where SDEPT in
(select SDEPT
from STUDENT055
where SNAME = '李维'
)
);
3.对每个选修了课程的学生，查询他获得最高成绩的课程号和成绩
select SNO,CNO,GRADE
from SCT055
where GRADE in
(select MAX(GRADE)
from SCT055
group by SNO
)
4.查询与“王丽”职称相同的其他女教师信息
select *
from TEACHER055
where TITLE in
(select TITLE
from TEACHER055
where TNAME = '王丽'
)
and TSEX = '女';
5.查询教师“周小平”和“王建宁”的授课信息，结果按照教师号升序显示
select distinct TNO,CNO
from SCT055
where TNO in
(select TNO
from TEACHER055
where TNAME = '周小平' or TNAME = '王建宁'
)
order by TNO;
6.查询至少选修了学生“李维”所选的所有课程的其他学生信息
select *
from STUDENT055
where SNO in
(
select distinct SNO
from SCT055 STU1
where not exists
(select *
from SCT055 STU2
where STU2.SNO in
(select SNO
from STUDENT055
where SNAME = '李维'
)
and
not exists
(select *
from SCT055 STU3
where STU3.SNO = STU1.SNO and
STU3.CNO = STU2.CNO))
);
7.查询至少选修了“数据库”和“C语言”课程的学生信息
intersect取交集
select *
from STUDENT055
where SNO in(
select SNO
from SCT055
where CNO in(
select CNO
from COURSE055
where CNAME = '数据库'
)
)
intersect
select *
from STUDENT055
where SNO in(
select SNO
from SCT055
where CNO in(
select CNO
from COURSE055
where CNAME = 'C语言'
)
)
8.查询“计算机”学院与“电子”学院的女学生信息
union求并集
select *
from STUDENT055
where SSEX = '女' and SDEPT = '计算机'
union
select *
from STUDENT055
where SSEX = '女' and SDEPT = '电子'
9.查询选修了“C语言”但是未选修“数据库”课程的学生信息
差集
select *
from STUDENT055
where SNO in(
select SNO
from SCT055
where CNO in(
select CNO
from COURSE055
where CNAME = 'C语言'
)
)
except
select *
from STUDENT055
where SNO in(
select SNO
from SCT055
where CNO in(
select CNO
from COURSE055
where CNAME = '数据库'
)
)
10.查询学分是3或4的课程信息
select *
from COURSE055
where CREDIT = '3'
union
select *
from COURSE055
where CREDIT = '4'
11.查询无人选修的课程的详细信息
select *
from COURSE055
where CNO in(
select CNO
from COURSE055
)
except
select *
from COURSE055
where CNO in(
select distinct CNO
from SCT055
)
二、针对教学管理数据库SCT，进行以下各种查询：
（以下题目不限制查询方法）
1.查询选修了“CS-001”课程，且成绩为第3到第5名的学生姓名，要求按照成绩的降序输出
按照逻辑应该是top5减去top2，但是这样在SQL server2019里得不到正确答案，必须用top5减去top3才能得到第3到第5名。
没有排序order by时 返回记录默认从高到低，由于GRADE的index是降序，所以order by GRADE直接得到降序输出
select top 5 SNAME,GRADE
from STUDENT055,SCT055
where CNO = 'CS-001' and
SCT055.SNO = STUDENT055.SNO
except
select top 3 SNAME,GRADE
from STUDENT055,SCT055
where CNO = 'CS-001' and
SCT055.SNO = STUDENT055.SNO
order by GRADE
2.查询各门课程选修学生数占学生总数的百分比；（没有学生选修的课程也要考虑）
convert转换成浮点数相除，concat函数加上百分号，没有学生选修的课程也要考虑所以使用左外连接。
select CNAME,concat(convert(float,count(SCT055.SNO))/(
select count(*)
from STUDENT055
)*100,'%') as '选课人数占比'
from COURSE055 left join SCT055
on COURSE055.CNO = SCT055.CNO
group by COURSE055.CNAME
order by count (*) desc
3.查询选课门数唯一的学生姓名（选课门数与其他学生都不相同）。（只考虑选修了课程的学生）
select SNAME
from STUDENT055
where SNO in(
select X.SNO
from STUDENT055 X,SCT055
where X.SNO = SCT055.SNO
group by X.SNO
having count(CNO) not in(
select count(CNO)
from STUDENT055 Y,SCT055
where Y.SNO = SCT055.SNO and Y.SNO !=X.SNO
group by Y.SNO
)
)