mysql练习《多表连接操作》

题目数据：
create database 学生课程系统;

use 学生课程系统;
create table Student
(Sno int primary key,
Sname nchar(10) ,
Ssex nchar(2),
Sage int,
Sdept nvarchar(30));

create table Course
(Cno int primary key,
Cname nvarchar(30),
Cpno int,
Ccredit int);

create table SC
(Sno int,
Cno int,
Grade int,
primary key(Sno,Cno));

insert into Student values
(201215121,‘李勇’,‘男’,20,‘CS’),
(201215122,‘刘晨’,‘女’,19,‘CS’),
(201215123,‘王敏’,‘女’,18,‘MA’),
(201215124,‘张立’,‘男’,19,‘IS’),
(201215125,‘刘婷’,‘女’,21,‘MA’),
(201215126,‘李悦’,‘女’,19,‘CS’),
(201215127,‘寒梅’,‘女’,18,‘IS’),
(201215128,‘夏雨’,‘女’,19,‘MA’),
(201215129,‘童琳’,‘女’,21,‘MA’);

insert into Course values
(1,‘数据库’,5,4),
(2,‘数学’,NULL,2),
(3,‘信息系统’,1,4),
(4,‘操作系统’,6,3),
(5,‘数据结构’,7,4),
(6,‘数据处理’,NULL,2),
(7,‘PASCAL’,6,4);

insert into SC values
(201215121,1,92),
(201215121,2,85),
(201215121,3,88),
(201215121,4,60),
(201215122,2,90),
(201215122,3,80),
(201215122,4,66),
(201215124,1,55);

1.检索“数据库”课程的平均成绩。

select avg(sc.grade) from course c
join sc on c.cno=sc.cno
where c.cname=‘数据库’;

2.列出所有年龄在19岁以下，或者“男”同学的名单。

select sname from student where sage < 19 or ssex = ‘男’;

select sname from student
where sage < 19 union all
select sname from student where ssex = ‘男’;

3.列出没有先修课的课程名。

select cname from course where cpno is null;

4.查询每一门课的间接先修课（即先修课的先修课），使用别名显示“课程编号”，“课程名称”，“间接先修课”。（course 自连接，使用的别名c1，c2，c3）

select c1.cno 课程编号, c1.cname 课程名称, c3.cname 间接先修课 from course c1
join course c2 on c1.cpno = c2.cno
join course c3 on c2.cpno = c3.cno;

5.查询每个学生的学号、姓名、选修的课程名及成绩。

select s.sno, s.sname, c.cname, sc.grade from student s
join sc on s.sno = sc.sno
join course c on sc.cno = c.cno;

6.检索所有学生的学号及其选修的课程名和成绩（没有参加选修课的学生学号也检索在结果中）。

select s.sno,c.cname,sc.grade from student s
left join sc on s.sno=sc.sno
left join course c on sc.cno=c.cno;

7.列出既不是‘MA’系，也不是“IS”系的学生的学号、姓名、所在院系、选修的课程号及成绩。

select s.sno, s.sname,s.sdept,sc.cno,sc.grade from student s
join sc on s.sno = sc.sno
where s.sdept not in(“MA”,“IS”);

8.检索选修了“数学”课程的学生学号和姓名。

select s.sno,s.sname from student s
join sc on s.sno=sc.sno
join course c on sc.cno=c.cno and c.cname=‘数学’;

9.查询选修2号课程且成绩在90分以上（含90分）的所有学生的学号和姓名。

select sno,sname from student
where sno in ( select sno from sc where cno = 2 and grade >= 90 );

10.找出每个学生超过他选修课程平均成绩的课程号。

select sc1.sno,sc1.cno from sc sc1
where grade >(select avg(grade) from sc sc2 where sc1.sno=sc2.sno);

11.查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

select sname ,sage from student
where sdept<>‘CS’ and sage <(select min(sage) from student where sdept =‘CS’);

12.查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄。

select sname ,sage from student
where sdept<>‘CS’ and sage <(select max(sage) from student where sdept =‘CS’);

13.查询与“刘晨”在同一个系学习的学生，显示学生的所有信息。

select * from student
where sdept =(select sdept from student where sname=‘刘晨’);

14.查询‘CS’系的学生及年龄不大于19岁的学生信息。

select * from student
where sdept ='CS’or sage<=19;

15.列出选修了全部课程的学生学号和姓名。先删除课程号大于3的课程，再测试查询命令（答案只写查询）。

select s.sno,s.sname from student s
where not exists (select * from course c where not exists(select * from sc where s.sno=sc.sno and c.cno=sc.cno));