MySQL实验

表如下：
学院（学院代码，学院名称）
学生（学号，姓名，性别，学院代码）
教师（教师号，教师名，学院代码）
课程（课程号，课程名，学时）
学习（学号，课程号，教师号，成绩）
授课（教师号，课程号）

括号内是结果显示的字段

1．求选修了老师“王刚”开课课程且成绩在90分以上的学生姓名、课程名称和成绩；（姓名，课程名，成绩）

select 姓名, 课程名, 成绩
from exp.教师, exp.授课, exp.课程, exp.学生, exp.学习, exp.学院
where 教师.教师号 = 授课.教师号
and 授课.课程号 = 课程.课程号
and 学生.学号 = 学习.学号
and 学院.学院代码 = 学生.学院代码
and 成绩>="90"
and 教师名 = "王刚";

2．求选修了“王刚”老师所授全部课程的学生姓名和学院名称；（姓名，学院名称）

-- 重要！理解相关子查询的过程！
select 姓名, 学院名称
from exp.学生, exp.学院
where 学生.学院代码=学院.学院代码
and 学生.学号 in
(	select 学号
	from exp.学习
    where not exists
    (	select *
		from exp.教师, exp.授课
		where 教师.教师号=授课.教师号
        and 教师.教师名='王刚'
        and not exists
        (	select *
			from exp.学习 as 学习1
            where 学习1.课程号=授课.课程号
            and 学习1.学号=学习.学号
        )
    )
);

3．求没有选修课程“软件工程”的学生学号和姓名；（学生学号，姓名）

select distinct 学号, 姓名
from exp.学生
where not exists
(	select *
	from exp.课程, exp.学习
    where 课程.课程号=学习.课程号
    and 学习.学号=学生.学号
    and 课程.课程名='软件工程'
);	

4．求至少选修了两门课程的学生学号；（学号）

select 学生.学号
from exp.学生, exp.学习
where 学生.学号=学习.学号
group by 学习.学号
having count(课程号)>=2;

5．求课程“经济学”不及格学生姓名和考试成绩；（姓名，成绩）

select 姓名, 成绩
from exp.学生, exp.学习, exp.课程
where 学生.学号=学习.学号
and 学习.课程号=课程.课程号
and 课程名='经济学'
and 成绩<=60;

6．求至少选修了与学号“T06” 同学选修的课程相同的学生学号；（学号）

select distinct 学号		
from exp.学习 as 学习0
where not exists
(	select *
	from exp.学习 as 学习1
    where 学习1.学号='T06'
    and not exists
    (	select *
		from exp.学习 as 学习2
        where  学习2.课程号=学习1.课程号
		and 学习0.学号=学习2.学号
	)
);

7．求至少选修了“C3，C4”两门课程的学生姓名和学院名称；（姓名，学院名称）

select distinct 姓名, 学院名称
from exp.学生, exp.学院, exp.学习
where 学生.学院代码=学院.学院代码
and 学生.学号=学习.学号
and 课程号='C3'
and 学生.学号 in
(	select distinct 学习1.学号
	from exp.学习 as 学习1
    where 学习1.课程号='C4'
);

8．查询“王石”同学没有选修的课程号和课程名； （课程号，课程名）

select 课程号, 课程名
from  exp.课程
where not exists
(	select *
	from exp.学生, exp.学习
    where 学生.学号=学习.学号 
    and 课程.课程号=学习.课程号
    and 姓名='王石'
);

9．查询没有被任何学生选修的课程的课程号；（课程号）

select 课程号
from exp.课程
where 课程.课程号 not in
(	select 课程号
	from exp.学习
);

/*Q9 另外一种写法
select 课程号
from exp.课程
where not exists
(	select 课程号
	from exp.学习
    where 学习.课程号=课程.课程号
);*/

10．求选修了全部课程的学生姓名；（姓名）

select 姓名
from exp.学生
where not exists
(	select *
	from exp.课程
    where not exists
    (	select *
		from exp.学习
        where 学习.课程号=课程.课程号
        and 学习.学号=学生.学号
    )
);

11．查询各学院课程“经济学”的平均分，并按照成绩从高到低的顺序排列；（学院名称，平均分）

select 学院名称, avg(成绩) as 平均分
from exp.学院, exp.学生, exp.学习, exp.课程
where 学生.学号=学习.学号
and 学院.学院代码=学生.学院代码
and 课程.课程号=学习.课程号
and 课程名="经济学"
group by 学院名称
order by avg(成绩) desc;

12．查询选修课程“经济学”的学生姓名和所在院系，结果按各院系排列，同时成绩从高到低排列；（姓名，学院名称，成绩）

select 姓名, 学院名称, 成绩
from exp.学院, exp.学生, exp.学习, exp.课程
where 学生.学号=学习.学号
and 学院.学院代码=学生.学院代码
and 课程.课程号=学习.课程号
and 课程名="经济学"
order by 学院名称, 成绩 desc;

13．求学时在30-45之间（含30和45）的课程的课程号和课程名称及授课教师；（课程号，课程名，教师姓名）

select 课程.课程号, 课程名, 教师名
from exp.教师, exp.课程, exp.授课
where 教师.教师号=授课.教师号
and 课程.课程号=授课.课程号
and 学时 between 30 and 45;

14．检索选修课程“经济学”的最高分学生的姓名；（姓名）

select distinct 姓名
from exp.学生, exp.学习, exp.课程
where 学生.学号=学习.学号
and 课程.课程号=学习.课程号
and 课程名='经济学'
and 成绩 >= all
(	select 成绩
	from exp.学生, exp.学习, exp.课程
	where 学生.学号=学习.学号
	and 课程.课程号=学习.课程号
	and 课程名='经济学'
);

15．查询选课人数超过5人的课程的课程号及课程名；（课程号，课程名）

select 课程.课程号, 课程名
from exp.课程, exp.学习
where 课程.课程号=学习.课程号
group by 课程号
having count(学号)>5;