一、SQL Server多表查询，包括连接操作和嵌套子查询

背景知识：
一、连接：分成内连接和外连接，内连接相当于取交集，外连接相当于取并集
二、嵌套子查询：连接操作浪费资源，使用嵌套子查询可以避免连接同时加快执行速度，分成相关子查询和非相关子查询

名称解释（不区分大小写）

• student学生表：
  • 包含属性列：sno学号、sname学生姓名、age年龄、sex性别、dno学院编号、birthday生日
• sc选课信息表：
  • 包含属性列：sno学号、cno课程号、grade成绩
• dept学院信息表：
  • 包含属性列：dno学院编号、dname学院名称、dean学院负责人
• course课程信息表：
  • 包含属性列：cno课程号、cname课程名称、tname老师名称、credit学分、room教室

下面语句可以直接复制到SQL Server运行

• 运行方法，选中执行语句

方法不唯一，有问题欢迎留言讨论！代码块见文章末尾

二、代码

select * from student
select * from sc
select * from course

– 求学号为‘20022037’的同学的每门课的成绩，输出格式为：学号，课程名，课程成绩
select sno,cname,grade from sc,course
where sc.cno = course.cno and sno = ‘20022037’

select sno,cname,grade from sc inner join course on sc.cno = course.cno
where sno = ‘20022037’

– 查询每个学生的每门课程的成绩，要求输出学号，课程名，成绩
select sno,cname,grade from sc,course
where sc.cno = course.cno

select sno,cname,grade from sc inner join course on sc.cno = course.cno

– 查询每个学生的每门课程的成绩，要求输出学号，姓名，课程号，成绩
select student.sno,sname,cno,grade from sc,student
where sc.sno = student.sno

select student.sno,sname,cno,grade from sc inner join student on sc.sno = student.sno

– 查询选修了’线性代数’课程的学生学号、姓名
select cname,sno,sname from student,course
where cname = ‘线性代数’

–从选修’218801’课程的同学中，选出成绩高于’季莹’的学生的学号和成绩
select sno,grade from sc
where cno = ‘218801’ and grade > (
select grade from sc
where cno = ‘218801’ and sno = (
select sno from student where sname = ‘季莹’))

–查询成绩比该课程平均成绩低的学生成绩表
select sno,cno,grade from sc as a
where grade < (
select avg(grade) from sc as b
where a.cno = b.cno)

–H 查询所有学生都选修的课程名
select cname from course
where not exists(
select sno from student
where not exists(
select sno from sc
where sc.sno = student.sno and sc.cno = course.cno
)
)
select cname from course
where cno in(
select distinct cno from sc
group by cno
having count(distinct sno) in (select sno from sc)
)

–查询选修了’线性代数’课程或’英语口语’课程的学生学号、姓名
select sno,sname from student
where sno in (
select sno from sc
where sno in (
select sno from course
where cname = ‘线性代数’ or cname = ‘英语口语’)
)

–用集合操作符 UNION 查询选修了’线性代数’课程或’英语口语’课程的学生学号、姓名
select sno,sname from student
where sno in (
select sno from sc
where sno in (
select sno from course
where cname = ‘英语口语’)
)
union
select sno,sname from student
where sno in (
select sno from sc
where sno in (
select sno from course
where cname = ‘线性代数’)
)

–查询选修了’218801’课程但没有选修’216301’课程的学生学号。
select sno from sc
where cno = ‘218801’
except
select sno from sc
where cno = ‘216301’
– 方法二
select sno from sc
where cno = '218801’and sno not in(
select sno from sc
where cno = ‘216301’)

–求同时选修’218801’课程和’216301’课程的学生学号、姓名。,也可以使用多表查询
select sc.sno,sname from sc,student
where sc.sno = ‘218801’
intersect
select sc.sno,sname from sc,student
where sc.sno = ‘216301’

–查询所有学生及其选课信息
select student.sno,sname,cno,grade
from student left outer JOIN sc
on student.SNO=sc.SNO

–创建课程平均分视图

–以列的方式统计每门课程的分数段人数。分数段为：不及格、60-70、70-80、80-90、90-100
(select cname,‘不及格’ AS fsd ,COUNT() AS rs
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE <60
GROUP BY cname)
UNION
(SELECT CNAME ,‘60-70’ AS fsd , COUNT ()
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 60 AND 70
GROUP BY CNAME)
UNION
(SELECT CNAME ,‘70-80’ AS fsd , COUNT ()
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 70 AND 80
GROUP BY CNAME)
UNION
(SELECT CNAME ,‘80-90’ AS fsd , COUNT ()
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 80 AND 90
GROUP BY CNAME)
UNION
(SELECT CNAME ,‘90-100’ AS fsd , COUNT (*)
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 90 AND 100
GROUP BY CNAME)

–1．查询所有选课学生的姓名
select sname,sno from student
where exists(select * from sc where sc.sno = student.sno)
order by sname

–2．查询所有未选课的学生的姓名
select sname from student
where not exists(select * from sc where student.sno = sc.sno)

–3．按学生分类查询其选修课程的平均分，输出学号、姓名和平均成绩
select student.sno,student.sname,avg(grade) as 平均成绩 from student,sc
where student.sno = sc.sno
group by student.sno,student.sname

–4．查询所有课程的平均分，输出课程名和平均成绩，并按平均成绩递增
select cname,avg(grade) as 平均成绩 from sc,course
group by cname
order by 平均成绩

–5．查询少于 10 名同学选修的课程名称，授课班号，教师名，选课人数
select cname,sc.cno,tname,count(sno) as 选课人数 from course,sc
group by cname,sc.cno,tname
having count(*) < 10
order by sc.cno

–6．按学号显示信息学院，‘通信专业’或‘电子科学专业’的每个学生的每门课程的成绩明细，并统计每个学生的总成绩，平均成绩
select DNAME,student.SNO,sum(GRADE)‘总成绩’,AVG(GRADE)‘平均成绩’
from sc,student,dept
where student.DNO=dept.DNO and student.SNO=sc.SNO
and DNAME=‘信息学院’
GROUP BY DNAME,student.SNO

–7．统计每门课的不及格人数，列出课程名和不及格人数
select cname,count(*) from sc,course
where grade < 60
group by cname

SELECT CNAME ,‘不及格分数段’ AS fsd ,COUNT(*) AS rs
FROM sc, course
WHERE sc.CNO= course.CNO AND GRADE<60
GROUP BY CNAME

–（1） 使用嵌套方法查询存在有 95 分以上成绩的课程 CNO
select cno from sc
where grade > 95

–（2） 查询成绩比该课程平均成绩低的学生成绩表
select sno,grade from sc as a
where grade < (select avg(grade) from sc as b where a.sno = b.sno)

–（3） 按课程名称统计每一门课程的平均分，输出课程名称和平均分
select cname,avg(grade) as 平均分 from sc,course
group by cname

–（4） 按学生姓名统计其选修课程的总学分，输出学生姓名和总学分
select sname,student.sno,总成绩 from student
right join
(select sno,sum(grade) as 总成绩 from sc group by sno) as d
on student.sno = d.sno

–（5） 查询同时选修了‘203402’和‘244501’课程的同学名称
select sname from student
where student.sno in (select sno from sc
where cno = ‘203402’ or cno = ‘244501’)

–（6） 求最高分学生的学号
select sno,grade from sc
where grade >= all(select grade from sc)

–（7） 查询“线性代数”的所有授课班级的平均成绩，列出课程名和平均成绩
select avg(grade) as 平均成绩,cname from sc,course
where cname = ‘线性代数’
group by cname

–（8） 查询“线性代数”成绩最高的前 5 名学生的姓名及成绩，结果按成绩降序
select top 5 sname,grade from sc,student
order by grade desc

–（9） 查询学生“20002059”选修课程的总学分数
select sum(grade) as 总分数 from sc
where cno = ‘20002059’

–（10） 对每个同学，查找其获得最高成绩的课程号
select sno,cno from sc as a
where grade >= all(select grade from sc as b
where a.sno = b.sno)

三、代码块

select * from student
select * from sc
select * from course
-- 求学号为‘20022037’的同学的每门课的成绩，输出格式为：学号，课程名，课程成绩
select sno,cname,grade from sc,course
where sc.cno = course.cno and sno = '20022037'
--方法二
select sno,cname,grade from sc inner join course on sc.cno = course.cno
where sno = '20022037'

-- 查询每个学生的每门课程的成绩，要求输出学号，课程名，成绩
select sno,cname,grade from sc,course
where sc.cno = course.cno

select sno,cname,grade from sc inner join course on sc.cno = course.cno

-- 查询每个学生的每门课程的成绩，要求输出学号，姓名，课程号，成绩
select student.sno,sname,cno,grade from sc,student
where sc.sno = student.sno

select student.sno,sname,cno,grade from sc inner join student on sc.sno = student.sno

-- 查询选修了'线性代数'课程的学生学号、姓名
select cname,sno,sname from student,course
where cname = '线性代数'

--从选修’218801’课程的同学中，选出成绩高于’季莹’的学生的学号和成绩
select sno,grade from sc
where cno = '218801' and grade > (
	select grade from sc
	where cno = '218801' and sno = (
	select sno from student where sname = '季莹'))

--查询成绩比该课程平均成绩低的学生成绩表
select sno,cno,grade from sc as a
where grade < (
	select avg(grade) from sc as b
	where a.cno = b.cno)

--H 查询所有学生都选修的课程名
select cname from course
where not exists(
	select sno from student
	where not exists(
		select sno from sc
		where sc.sno = student.sno and sc.cno = course.cno
	)
)
--方法二
select cname from course
where cno in(
	select distinct cno from sc
	group by cno
	having count(distinct sno) in (select sno from sc)
)

--查询选修了'线性代数'课程或'英语口语'课程的学生学号、姓名
select sno,sname from student
where sno in (
	select sno from sc
	where sno in (
		select sno from course
		where cname = '线性代数' or cname = '英语口语')
)

--用集合操作符 UNION 查询选修了'线性代数'课程或'英语口语'课程的学生学号、姓名
select sno,sname from student
where sno in (
	select sno from sc
	where sno in (
		select sno from course
		where cname = '英语口语')
)
union
select sno,sname from student
where sno in (
	select sno from sc
	where sno in (
		select sno from course
		where cname = '线性代数')
)

--查询选修了'218801'课程但没有选修'216301'课程的学生学号。
select sno from sc 
where cno = '218801'
except
select sno from sc
where cno = '216301'
-- 方法二
select sno from sc 
where cno = '218801'and sno not in(
	select sno from sc
	where cno = '216301')
	
--求同时选修'218801'课程和'216301'课程的学生学号、姓名。,也可以使用多表查询
select sc.sno,sname from sc,student
where sc.sno = '218801'
intersect
select sc.sno,sname from sc,student
where sc.sno = '216301'


--查询所有学生及其选课信息
select student.sno,sname,cno,grade
from student left outer JOIN sc
on student.SNO=sc.SNO

--以列的方式统计每门课程的分数段人数。分数段为：不及格、60-70、70-80、80-90、90-100 
(select cname,'不及格' AS fsd ,COUNT(*) AS rs
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE <60
GROUP BY cname)
UNION
(SELECT CNAME ,'60-70' AS fsd , COUNT (*)
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 60 AND 70
GROUP BY CNAME)
UNION
(SELECT CNAME ,'70-80' AS fsd , COUNT (*)
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 70 AND 80
GROUP BY CNAME)
UNION
(SELECT CNAME ,'80-90' AS fsd , COUNT (*)
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 80 AND 90
GROUP BY CNAME)
UNION
(SELECT CNAME ,'90-100' AS fsd , COUNT (*)
FROM sc,course
WHERE sc.CNO=course.CNO AND GRADE BETWEEN 90 AND 100
GROUP BY CNAME)

--1．查询所有选课学生的姓名
select sname,sno from student
where exists(select * from sc where sc.sno = student.sno)
order by sname

--2．查询所有未选课的学生的姓名
select sname from student
where not exists(select * from sc where student.sno = sc.sno)

--3．按学生分类查询其选修课程的平均分，输出学号、姓名和平均成绩
select student.sno,student.sname,avg(grade) as 平均成绩 from student,sc
where student.sno = sc.sno
group by student.sno,student.sname

--4．查询所有课程的平均分，输出课程名和平均成绩，并按平均成绩递增
select cname,avg(grade) as 平均成绩 from sc,course
group by cname
order by 平均成绩

--5．查询少于 10 名同学选修的课程名称，授课班号，教师名，选课人数
select cname,sc.cno,tname,count(sno) as 选课人数 from course,sc
group by cname,sc.cno,tname
having count(*) < 10
order by sc.cno

--6．按学号显示信息学院，‘通信专业’或‘电子科学专业’的每个学生的每门课程的成绩明细，并统计每个学生的总成绩，平均成绩
select DNAME,student.SNO,sum(GRADE)'总成绩',AVG(GRADE)'平均成绩'
from sc,student,dept
where   student.DNO=dept.DNO and student.SNO=sc.SNO
and DNAME='信息学院'
GROUP BY  DNAME,student.SNO

--7．统计每门课的不及格人数，列出课程名和不及格人数
select cname,count(*) from sc,course
where grade < 60
group by cname

SELECT CNAME ,'不及格分数段' AS fsd ,COUNT(*) AS rs
FROM sc, course
WHERE sc.CNO= course.CNO AND GRADE<60
GROUP BY CNAME

--（1） 使用嵌套方法查询存在有 95 分以上成绩的课程 CNO
select cno from sc
where grade > 95

--（2） 查询成绩比该课程平均成绩低的学生成绩表
select sno,grade from sc as a
where grade < (select avg(grade) from sc as b where a.sno = b.sno)

--（3） 按课程名称统计每一门课程的平均分，输出课程名称和平均分
select cname,avg(grade) as 平均分 from sc,course
group by cname

--（4） 按学生姓名统计其选修课程的总学分，输出学生姓名和总学分
select sname,student.sno,总成绩 from student
right join
(select sno,sum(grade) as 总成绩 from sc group by sno) as d
on student.sno = d.sno

--（5） 查询同时选修了‘203402’和‘244501’课程的同学名称
select sname from student
where student.sno in (select sno from sc
						where cno = '203402' or cno = '244501')
						
--（6） 求最高分学生的学号
select sno,grade from sc
where grade >= all(select grade from sc)

--（7） 查询“线性代数”的所有授课班级的平均成绩，列出课程名和平均成绩
select avg(grade) as 平均成绩,cname from sc,course
where cname = '线性代数'
group by cname

--（8） 查询“线性代数”成绩最高的前 5 名学生的姓名及成绩，结果按成绩降序
select top 5 sname,grade from sc,student
order by grade desc

--（9） 查询学生“20002059”选修课程的总学分数
select sum(grade) as 总分数 from sc
where cno = '20002059'

--（10） 对每个同学，查找其获得最高成绩的课程号
select sno,cno from sc as a
where grade >= all(select grade from sc as b
					where a.sno = b.sno)