实验二 SQL 语言——SELECT 查询操作(第三部分)

写在前面：本文中可能有错误，还请大佬斧正，如果小伙伴有什么好的方法，也可以发给我，我添加到里面

5、 复杂查询

实验二：第二部分               实验二：第四部分
Student、Course、SC三表在 第一部分 中
（1） 查询与“钱横”在同一系学习的学生信息。
（2） 找出同系、同年龄、同性别的学生。
（3） 查询选修了课程名为“数据库系统”的学生学号、姓名和所在系。
（4） 查询至少不学 1002 号和 1004 号课程的学生学号与姓名。
（5） 查询王同学不学的课程的课程号。
（6） 查询其他系中比信息系（IS）所有学生年龄均大的学生名单，并排序输出。
（7） 查询哪些课程只有女生选读。
（8） 查询所有未修 1001 号课程的学生姓名。
（9） 统计所有学生选修的课程门数。
（10） 查询选修了全部课程的学生姓名。
（11） 查询全部学生都选修的课程的课程号与课程名。
（12） 查询至少选修了学生“98001”选修的全部课程的学生号。
（13） 查询选修了课程 1001 或者选修了课程 1002 的学生学号集。
（14） 查询计算机科学系的学生与年龄不大于 19 岁的学生的交集。
（15） 查询选修课程 1002 的学生集合与选修课程 1001 的学生集合的差集。 （16） 查询平均成绩大于 85 分的学号、姓名、平均成绩。
（17） 查询课程成绩在 90 分以上的男学生的姓名、课程名和成绩。
（18） 查询选修了所有 3 学分课程的学生学号。
（19） 求学分为 3 的每门课程的学生平均成绩。
（20） 查询学号比王林同学大，而年龄比他小的学生姓名。
（21） 查询姓名以“王”开头的所有学生的姓名和年龄。
（22） 在 SC 中检索成绩为空值的学生学号和课程号。
（23） 求年龄大于女同学平均年龄的男学生的姓名和年龄。
（24） 求年龄大于所有女同学年龄的男学生的姓名和年龄。
（25） 查询选修 1002 号课程的学生中成绩最高的学生的学号。
（26） 查询选修 1002 号课程的学生的平均年龄。
（27） 查询各不同平均成绩所对应的学生人数（给出平均成绩与其对应的人数）。
（28） 查询学生、课程及成绩的明细信息及课程门数、总成绩及平均成绩。 （29） 删除“数据结构“课程及所有对应它的选课情况。
（30） 统计每门课程的学生选修人数，超过 2（包含 2）人的课程才统计，要求输出课程号 和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

SQL语句如下
----------------------复杂查询----------------------
--1.查询与“钱横”在同一系学习的学生信息。
select *
from Student
where Student.Sdept=(
	select Student.Sdept
	from Student
	where Student.Sname='钱横'
)

--2.找出同系、同年龄、同性别的学生
select *
from Student as s1,Student as s2
where (s1.Sdept = s2.Sdept)
and (s1.Sage = s2.Sage)
and (s1.Ssex = s2.Ssex)
and (s1.Sname <> s2.Sname)

--3.查询选修了课程名为“数据库系统”的学生学号、姓名和所在系
select Student.Sno,Student.Sname,Student.Sdept
from SC
inner join Student on SC.Sno = Student.Sno
inner join Course on SC.Cno = Course.Cno
where Course.Cname = '数据库系统'

--4.查询至少不学 1002 号和 1004 号课程的学生学号与姓名
select distinct Student.Sno,Student.Sname
from Student
inner join SC on SC.Sno = Student.Sno
where SC.Cno <> 1002
or SC.Cno <> 1004

--5.查询王同学不学的课程的课程号
select Course.Cno
from Course
where Course.Cno not in(
	select Course.Cno
	from SC
	inner join Student on SC.Sno = Student.Sno
	inner join Course on SC.Cno = Course.Cno
	where Student.Sname like '王%'
)

--6.查询其他系中比信息系（IS）所有学生年龄均大的学生名单，并排序输出
select Student.Sname,Student.Sage
from Student
where Student.Sage>(select avg(Student.Sage) from Student where Student.Sdept='IS')
and Student.Sdept<>'IS'
order by Student.Sage asc	--升序
-- order by Student.Sage desc	--降序

--7.查询哪些课程只有女生选读
select SC.Cno
from SC
where SC.Cno not in (
	select Course.Cno
	from SC
	inner join Student on SC.Sno = Student.Sno
	inner join Course on SC.Cno = Course.Cno
	where Student.Ssex='男'
)

--8.查询所有未修 1001 号课程的学生姓名
select Student.Sname
from Student
where Student.Sno not in(
	select SC.Sno
	from SC
	where SC.Cno=1001
)

--9.统计所有学生选修的课程门数
select count(distinct SC.Cno) as 课程数
from SC

--10.查询选修了全部课程的学生姓名
select Student.Sname
from Student
where not exists (
	select *
	from Course
	where not exists (
		select *
		from SC
		where Student.sno=SC.sno and Course.cno=SC.cno
	)
)

--11.查询全部学生都选修的课程的课程号与课程名
select Course.Cno,Course.Cname
from Course
where Course.Cno in (
	select SC.Cno
	from SC
	group by SC.Cno
	having count(1) = (select count(distinct SC.Sno) from SC)
)

--12.查询至少选修了学生“98001”选修的全部课程的学生号
select Sno
from SC t
where not exists (
	select 1
	from SC
	where SC.Sno='98001' 
	and SC.Cno not in (select SC.Cno from SC where SC.Sno = t.Sno)
)
group by Sno

--13. 查询选修了课程 1001 或者选修了课程 1002 的学生学号集
select distinct SC.Sno
from SC
where SC.Cno = '1001'
or SC.Cno = '1002'

--14. 查询计算机科学系的学生与年龄不大于 19 岁的学生的交集
select * 
from Student 
where Student.Sdept = 'CS' 
and Student.Sage <= 19

--15. 查询选修课程 1002 的学生集合与选修课程 1001 的学生集合的差集
select Student.*
from Student
inner join SC on SC.Sno = Student.Sno
where SC.Cno = '1002'
except
select Student.*
from Student
inner join SC on SC.Sno = Student.Sno
where SC.Cno = '1001'

--16.查询平均成绩大于 85 分的学号、姓名、平均成绩
select Student.Sno,Student.Sname,avgr
from Student,(select SC.Sno,avg(SC.Grade) from SC group by SC.Sno) as SG(SG_Sno,avgr)
where Student.Sno = SG.SG_Sno
and SG.avgr > 85

--17.查询课程成绩在 90 分以上的男学生的姓名、课程名和成绩
select Student.Sname,Course.Cname,SC.Grade
from SC
inner join Student on SC.Sno = Student.Sno
inner join Course on SC.Cno = Course.Cno
where SC.Grade > 90
and Student.Ssex = '男'

--18.查询选修了所有 3 学分课程的学生学号
select Student.Sno
from SC
inner join Student on SC.Sno = Student.Sno
inner join Course on SC.Cno = Course.Cno
where Course.Ccredit = 3

--19. 求学分为 3 的每门课程的学生平均成绩
select Course.Cno,avg(SC.Grade)
from SC
inner join Course on SC.Cno = Course.Cno
where Course.Ccredit = 3
group by Course.Cno

--20.查询学号比王林同学大，而年龄比他小的学生姓名
select Student.Sname
from Student
where Student.Sno > (
	select Student.Sno
	from Student
	where Student.Sname = '王林')
and Student.Sage < (
	select Student.Sage
	from Student
	where Student.Sname = '王林')

--21.查询姓名以“王”开头的所有学生的姓名和年龄
select Student.Sname,Student.Sage
from Student
where Student.Sname like '王%'

--22.在 SC 中检索成绩为空值的学生学号和课程号
select SC.Sno,SC.Cno
from SC
where SC.Grade is null

--23.求年龄大于女同学平均年龄的男学生的姓名和年龄
-- 查年龄？？！！学校这是要包分配女朋友啊？？
select Student.Sname,Student.Sage
from Student
where Student.Ssex = '男'
and Student.Sage > (
	select avg(Student.Sage)
	from Student
	where Student.Ssex = '女')

--24.求年龄大于所有女同学年龄的男学生的姓名和年龄
select Student.Sname,Student.Sage
from Student
where Ssex = '男'
and Student.Sage > (
	select max(Student.Sage)
	from Student
	where Student.Ssex = '女')

--25.查询选修 1002 号课程的学生中成绩最高的学生的学号
select SC.Sno,SC.Cno
from SC
where SC.Cno = 1002
and SC.Grade = (
	select max(SC.Grade)
	from SC
	where SC.Cno = 1002)

--26.查询选修 1002 号课程的学生的平均年龄
select avg(Student.Sage)
from Student
inner join SC on Student.Sno = SC.Sno
where SC.Cno = 1002

--27.查询各不同平均成绩所对应的学生人数（给出平均成绩与其对应的人数）。 
select avg(SC.Grade) as avg,count(SC.Sno) as 人数
from SC
group by SC.Cno

--28.查询学生、课程及成绩的明细信息及课程门数、总成绩及平均成绩
select Student.*,Course.*,SC.Grade
from SC
inner join Student on SC.Sno = Student.Sno
inner join Course on SC.Cno = Course.Cno

select count(distinct SC.Cno) as 课程数,
	   sum(SC.Grade) as 总成绩,
	   avg(SC.Grade) as 平均成绩
from SC

--29.删除“数据结构“课程及所有对应它的选课情况
delete from SC
where SC.Cno in (
	select Course.Cno
	from Course
	where Course.Cname = '数据结构')
delete from Course where Course.Cname = '数据结构'

--30. 统计每门课程的学生选修人数，超过 2（包含 2）人的课程才统计
select SC.Cno,count(distinct SC.Sno) as count
from SC
group by SC.Cno
having count(distinct SC.Sno) >= 2
order by count(distinct SC.Sno) desc, SC.Cno asc

错误之处还请大家多多斧正
实验二：第二部分               实验二：第四部分