复杂sql教学

复杂sql练习，使用not exists，两表作差运算

本文主要以复杂sql的形式，讲解如何用not exists实现复杂sql，如何用not exists实现两个集合的差运算，首先上表的定义，college(id,name),student(id,name,sex,coid),course(id,name),sc(sid,cid,score),teacher(id,name,coid),tc(tid,cid)

学院表：

---------------------------------------------------------------------------------------------学院表begin
DROP TABLE IF EXISTS `college`;
CREATE TABLE `college` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `college` VALUES ('9001','计算机与信息工程学院'); 
INSERT INTO `college` VALUES ('9002','数统学院'); 
---------------------------------------------------------------------------------------------学院表end

学生表：

---------------------------------------------------------------------------------------------学生表begin
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(22) DEFAULT NULL,
  `sex` varchar(2) DEFAULT NULL,
  `coid` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES ('10001', 'tom','男','9001');
INSERT INTO `student` VALUES ('10002', 'json','女','9001');
INSERT INTO `student` VALUES ('10003', 'ak','男','9001');
INSERT INTO `student` VALUES ('10004', 'km','女','9002');
INSERT INTO `student` VALUES ('10005', 'mk','男','9002');
---------------------------------------------------------------------------------------------学生表end

课程表：

---------------------------------------------------------------------------------------------课程表begin
DROP