数据库原理实验-数据库高级查询

---

接着上一部分数据库基本查询二来。
这一次实验的查询在逻辑上加深了一些，难度更大

---

题目：

1. 将素材中的数据库附加到SQL SERVER中
   下面是附加操作：
   
   

表结构

2. 新建查询，查询那些同学选修了3个以上的课程，显示学号和选修门数

select 学号,count(*) 选修门数
from 学生成绩
group by 学号
having count(*)>3

4. 新建查询，查询每个学生的成绩在80分以上的各有多少门，显示姓名和门数

select 姓名,count(*) 门数
from 成绩表
where 成绩>80
group by 学号

5. 新建查询，查询所有课程的平均成绩比王永林平均分高的学生的学生名和平均成绩

select 学生名,avg(成绩)
from 学生成绩
group by 学号
having avg(成绩)>(
	select avg(成绩)
	from 学生成绩
	where 姓名='王永林'
)

6. 新建查询，查询教师或学生的学号（或教师编号）、姓名、性别、政治面貌，第一个字段以编号显示

--联合两表的查询结果用union字段
select 教师编号 编号,姓名,性别,政治面貌
from 教师信息
union
select 学号 编号,姓名,性别,政治面貌
from 学生信息

7. 新建查询，查询入学分数在前25%同学选修哪些课程，显示姓名、课程名和成绩，

select 姓名,课程号,成绩
from 学生成绩
where 姓名 in (
	select 25 percent 姓名
	from 学生信息
	order by 入学成绩 desc
)

8. 新建查询，查询大于入学分数平均值的男生的人数，

select count(*) 人数
from 学生信息
where 性别='男' and 入学分数>(
	select avg(入学分数)
	from 学生信息
)

9. 新建查询，查询既有先修课，同时自己又是其他课的先修课的课程名，

--这里使用自连接
select a1.课程名 课程名
from 课程信息 a1 join 课程信息 a2 on a1.课程名=a2.先修课--同时自己又是其他先修课的课程名
where a1.先修课 is not null --既有先修课

select 先修课 课程名
from 课程信息
where 先修课 in(
	select 课程名
	from 课程信息
	where 先修课 is not null
)

11. 新建查询，查询入学分数前3同学选修的所有课程中学生的学号、姓名和性别==（双重嵌套）==

select 学号,性别,姓名
from 学生成绩 join 学生信息 on 学生成绩.学号=学生信息.学号
where 课程号 in (
	select 课程号
	from 学生成绩 
	where 学号 in (
		select top 3 学号
		from 学生信息
		order by 入学分数 desc
	)
)

12. 新建查询，查询教师中工龄超过18年（包括）的未婚教师的姓名、工龄、结婚状态（婚否的别名）和院系名称

select 姓名,(year(getdate())-year(参加工作时间)) 工龄,结婚状态,院系名称
from 教师信息
where (year(getdate())-year(参加工作时间))>18 and 婚否=0

13. 新建查询，查询哪些课程没有同学选，显示课程号和课程名

select 课程信息.课程号,课程名
from 课程信息 left join 学生成绩 on 课程信息.课程号=学生成绩.课程号
where 学生成绩.课程号 is null

14. 新建查询，查询最受学生欢迎（选课人数最多）的课程号、课程名，

select top 1 学生成绩.课程号,课程名
from 学生成绩 join 课程信息 on 学生成绩.课程号=课程信息.课程号
group by 课程号
order by count(*) desc

select 课程号,课程名
from 课程信息 
where 课程号=(
	select top 1 课程号 
	from 学生成绩 group by 课程号 
	order by COUNT(*) desc
	)

15. 新建查询，查询在出生人数最多年份出生的学生学号、姓名和出生年年份

select 学号,姓名,year(出生日期) 出生年份
from 学生信息
where year(出生日期)=(
	select top 1 year(出生日期)
	from 学生信息
	group by year(出生日期)
	order by count(*) desc
	)

---

如有错误，烦请指正！

下一部分我们复习一下数据更新操作

---