SQL Server-【知识与实战IV】多表查询、个数计算、多重约束条件、两表间的交集、多表之间的关系分析

一丶 题目&答案

【1】查询15级网络技术301班所有姓陈的男同学的信息

USE EMIS5
GO

select 班级代码 from 班级 where 班级名称='15级网络技术301班' //通过班级名称找到班级代码

select * from 学生 
where 班级代码 in(select 班级代码 from 班级 where 班级代码='15级网络技术301班')
	and 姓名 like '陈%' and 性别='男'
go

【2】查询每个班的男女生各有多少人

select 班级代码,性别,count(*) as 总人数 from 学生 group by 性别,班级代码

【3】查询平均分大于80分的学生的学号、姓名和班级名称信息

select 学号 from 课程注册 group by 学号 having avg(成绩)>80	//平均分大于80分学生学号

select 学号,姓名,班级名称 from 学生 inner join 班级 on 学生.班级代码=班级.班级代码
	and	学号 in(select 学号 from 课程注册 group by 学号 having avg(成绩>80))
go

【4】查询大学英语成绩前三名的同学的学号，姓名以及所在系部名称

//先找到大学英语成绩排名前三的学生学号
select top 3 学号 from  课程注册 inner join 课程 
		on 课程注册.课程号=课程.课程号 and 课程名称='大学英语'	
select 学号,姓名,系部名称
from 学生 inner join 班级 on 学生.班级代码=班级.班级代码
		  inner join 专业 on 专业.专业代码=班级.专业代码
		  inner join 系部 on 系部.系部代码=专业.系部代码
where 学号 in(select top 3 学号 from 课程注册 inner join 课程
			on 课程注册.课程号=课程.课程号 and 课程名称='大学英语' order by 成绩 DESC)
go

思路

1、先在课程注册表中通过课程号关联课程表中的课程号，找到匹配的同一行，然后就可以知道它的课程名称是否为大学英语。
2. 然后再通过成绩找到前三名同学，约束条件是大学英语，而且要倒序从高到低排
3. 找到了这三名学生后，就可以在课程注册表中确定他们三位的学号，再进入到学生表中拿到他们的班级代码。
4. ，通过学生表的班级代码与班级表的班级代码，找到他们的专业代码，拿到他们的系部代码
5. 通过专业表的系部代码与系部的系部代码，就可以找到他们的系部名称。

【5】查询只有一门课程不及格的同学的最高分、最低分及平均分信息

//查询只有一门课程不及格的学生学号
select 学号 from 课程注册 where 成绩<=60 group by 学号 having count(成绩)=1

select 学号,max(成绩) as 最高分,min(成绩) as 最低分,avg(成绩) from 课程注册
	where 学号 in(select 学号 from 课程注册 where 成绩<60 group by 学号 
	having count(成绩)=1)
	group by 学号
go

二丶 所有表的结构

班级：

管理员：

教师：

教师任课：

教学计划：

课程：

课程类型：

课程收费：

课程注册：

系部：

学籍状态：

学生：

专业：