SQL练习(using MySQL)

还是这个经典场景，但是却能衍生出很多较复杂的SQL语句。学生选课，一个学生表student，课程表course，选课表sc，在MySQL下建立数据库：

drop database course_select;
create database course_select;
use course_select;
create table student
(
 snum int primary key,
 sname varchar(128),
 sage int,
 sclass varchar(64)
);
create table course
(
 cnum int primary key,
 cname varchar(128),
 credit float,
 tnum int
);
create table sc
(
 snum int,
 cnum int,
 score double,
 primary key(snum, cnum),
 foreign key(snum) references student(snum),
 foreign key(cnum) references course(cnum)
);
insert into student values(1, 'zhangsan', 21, 'class2');
insert into student values(2, 'lisi', 22, 'class1');
insert into student values(3, 'wangwu', 22, 'class3');
insert into student values(4, 'zhao', 22, 'class1');
insert into student values(5, 'liu', 20, 'class2');
insert into student values(6, 'qi', 23, 'class1');
insert into student values(7, 'ba', 24, 'class2');
insert into student values(8, 'jiu', 24, 'class2');
insert into course values(1, 'C++', 2.5, 1);
insert into course values(2, 'Data Structure', 3, 2);
insert into course values(3, 'Operating Systems', 1.5, 1);
insert into course values(4, 'Algorithms', 5, 2);
insert into course values(5, 'Database', 4, 2);
insert into course values(6, 'English', 3, 3);
insert into course values(7, 'Design Patterns', 2, 4);
insert into sc values(1, 1, 28);
insert into sc values(1, 2, 68);
insert into sc values(1, 3, 38);
insert into sc values(1, 4, 58);
insert into sc values(1, 7, 90);
insert into sc values(2, 1, 79);
insert into sc values(2, 3, 60);
insert into sc values(2, 5, 63);
insert into sc values(2, 7, 88);
insert into sc values(3, 2, 92);
insert into sc values(3, 4, 73);
insert into sc values(3, 5, 64);
insert into sc values(4, 1, 58);
insert into sc values(4, 2, 43);
insert into sc values(5, 7, 31);
insert into sc values(6, 5, 80);
insert into sc values(6, 1, 97);
insert into sc values(6, 3, 41);
insert into sc values(6, 7, 54);
insert into sc values(7, 3, 92);
insert into sc values(7, 6, 83);

问题：

学过2号老师讲过一门课的学生

select snum  from course natural join sc 
where course.tnum = 2 group by snum having count(cnum) = 1;

学全了2号老师讲过所有课程的学生

select snum from student as s where not exists 
(select cnum from course where tnum = 2 and cnum not in
(select cnum from course natural join sc where sc.snum = s.snum));

未学全2号老师讲过所有课程的学生

select snum from student as s where exists 
(select cnum from course where tnum = 2 and cnum not in
(select cnum from course natural join sc where sc.snum = s.snum));

一门2号老师讲的课也未学过的学生

select snum from student as s where not exists
(select cnum from course where tnum = 2 and cnum in
(select cnum from course natural join sc where sc.snum = s.snum));

有两门成绩不及格课程同学的平均成绩

select snum,avg(score) from sc as temp
where temp.snum in ((select snum from course natural join sc 
where sc.score < 60 group by snum having count(cnum) = 2))  group by snum;

平均成绩高于80分的同学

select snum from course natural join sc group by snum having avg(score) > 80;

既学过1号课程又学过2号课程的学号，本应该用集合差(except)实现，但MySQL中不支持except，上面几个例子也是如此

select snum from sc where sc.cnum = 1 and snum in (select snum from sc where sc.cnum = 2);