SQL server数据库实验（三）数据库的嵌套查询和集合查询

---

一、针对教学管理数据库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.查询至少选修了学生“李维”所选的所有课程的其他学生信息

SQL语句中没有蕴涵逻辑运算，但是可以利用谓词演算等价转换，它所表达的语义为：不存在这样的课程y，学生李维选修了y，而学生x没有选。

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
	)
)