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);