CUMT数据库SQL实验

目录

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

---

代码不能运行可能是少空格或者word的锅？🤔

导入的表：

学院（学院代码，学院名称）

学生（学号，姓名，性别，学院代码）

教师（教师号，教师名，学院代码）

课程（课程号，课程名，学时）

学习（学号，课程号，教师号，成绩）

授课（教师号，课程号）

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

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

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

select 姓名,学院名称
from 学生,学院
where 学生 . 学院代码=学院 . 学院代码
and 学生 . 学号 in ( select distinct 学号
from 学习
where not exists ( select *
from 教师,授课
where 教师 . 教师号=授课 . 教师号
and 教师 . 教师名="王刚"
and not exists ( select *
from 学习 as xuexi
where xuexi. 学号 = 学习 . 学号
and xuexi. 课程号=授课 . 课程号 ) ) );

思路（可能不太好理解:-|）：

一：最终要选出姓名和学院名称，就先学生和学院做连接，连接需要第一个条件“学生 . 学院代码=学院 . 学院代码”，接下来考虑其他要求。

二：要求出选修王刚老师所受全部课程的学生，第一步连接了学生和学院，其中学号属性和其他表比较好比较，就变成了求选修王刚老师所受全部课程的学生的学号，然后第一步中学生的学号在求出的学号中（用IN连接第一步的处理和筛选出的学号）。

三：筛选出学号，就要select学号from学习（学习中有课程号和教师号，容易与“王刚”连接）

四：选学号的条件是选了“王刚”的全部课程，肯定要两层（选出“王刚”的全部课程，再与学生选的课程比较）想到用not exists（相当于取上层“学生.学号”，在下层中选出王刚的课程，再和课程中学号进行对比，如果上层的“学生.学号”在每次对比中都存在就是选修了王刚的所有课程，有一次不在就说明没有选择王刚的所有课程，exists只要存在一次就正确，不太好用，所以可以用两次not exists表示返回满足每次比较的“学生.学号”）

五：not exists中第一层先选出王刚的课程，再第二层中将选出的课程与第三步的学号进行对比。注意最内层再次用了“from 学习”，可能与第二步中学习搞混，要用as。

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

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

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

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

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

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

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

select distinct 学号
from 学习
where not exists(
select *
from 学习 as xx
where xx.学号='T06'
and not exists(
select *
from 学习 as xxx,课程
where xxx.课程号=xx.课程号
and 学习.学号=xxx.学号
)
);

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

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

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

select 课程号,课程名
from 课程
where 课程号 not in(
select kc.课程号
from 学生,学习,课程 as kc
where 学生.学号=学习.学号
and 学习.课程号=kc.课程号
and 学生.姓名='王石'
);

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

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

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

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

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

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

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

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

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

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

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

select 姓名
from 学生,学习,课程
where 学生.学号=学习.学号
and 课程.课程号=学习.课程号
and 课程.课程名="经济学"
and 成绩=(
select max(成绩)
from 学习 as a,课程 as b
where a.课程号=b.课程号
and b.课程名="经济学");

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

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