SQL Exercises（SQL查询练习）

SQL Exercises

Create Tables

--DROP TABLE department CASCADE;
--DROP TABLE student CASCADE;
--DROP TABLE course CASCADE;
--DROP TABLE sc CASCADE;

--department(dNo,dName,officeRoom,homepage)
--student(sNo,sName,sex,age,dNo)
--course(cNo,cName,cPNo,credit,dNo)
--sc(sNo,cNo,score,recordDate)

CREATE TABLE department(
  dNo           CHAR(2)        NOT NULL UNIQUE,
  dName         VARCHAR(20),
  officeRoom    VARCHAR(40),
  homePage      VARCHAR(80),
  PRIMARY KEY(dNo)
);

CREATE TABLE student(
  sNo 		CHAR(6)        NOT NULL UNIQUE,
  sName		VARCHAR(20)    NOT NULL,
  sex		CHAR(2)        CHECK (sex IN('男','女')),      
  age		INT,
  dNo		CHAR(2),
  PRIMARY KEY(sNo),
  FOREIGN KEY (dNo) REFERENCES  department(dNo)
);

CREATE TABLE course(
  cNo		CHAR(6)	       NOT NULL UNIQUE,
  cName		VARCHAR(20)    NOT NULL,
  cPNo		CHAR(6),
  credit	INT,
  dNo           CHAR(2),
  PRIMARY KEY(cNo),
  FOREIGN KEY (cPNo) REFERENCES  course(cNo),
  FOREIGN KEY (dNo)  REFERENCES  department(dNo)
);

CREATE TABLE sc(
  sNo		CHAR(6)	       NOT NULL,
  cNo		CHAR(6)	       NOT NULL,
  score		INT,
  recordDate    date,
  PRIMARY KEY(sNo,cNo),
  FOREIGN KEY (sNo) REFERENCES  student(sNo),
  FOREIGN KEY (cNo) REFERENCES  course(cNo)
);

Insert Values

INSERT INTO department VALUES('01','信息学院','行政楼409','www.xxx.edu.cn');
INSERT INTO department VALUES('02','软件学院',null,null);
INSERT INTO department VALUES('03','理学院',null,null);
INSERT INTO department VALUES('04','文学院',null,null);
INSERT INTO department VALUES('05','外国语学院',null,null);

INSERT INTO student VALUES('170101','宁灿',  '女',19,'01');
INSERT INTO student VALUES('170102','尹江月','女',19,'01');
INSERT INTO student VALUES('170103','杨佳伟','男',null,null);
INSERT INTO student VALUES('170104','杨何宇','男',19,'01');
INSERT INTO student VALUES('170105','胡耀斌','男',19,null);
INSERT INTO student VALUES('170106','李杨阳','女',20,'01');
INSERT INTO student VALUES('170107','杜利俊','女',18,'01');
INSERT INTO student VALUES('170108','钱多多','女',17,'01');
INSERT INTO student VALUES('170109','李佳伟','女',null,'01');
INSERT INTO student VALUES('170110','吴莫愁','女',21,'01');

INSERT INTO student VALUES('170201','安相成','男',19,'02');
INSERT INTO student VALUES('170202','曹师好','男',null,'02');
INSERT INTO student VALUES('170203','雷霆',  '男',18,'02');
INSERT INTO student VALUES('170204','刘书敏','男',20,'02');
INSERT INTO student VALUES('170205','王兵',  '男',21,'02');
INSERT INTO student VALUES('170206','李佳成','男',19,null);
INSERT INTO student VALUES('170207','唐玉迎','女',17,'02');
INSERT INTO student VALUES('170208','杨曼婷','女',19,'02');

INSERT INTO student VALUES('170301','张望',  '男',21,'03');
INSERT INTO student VALUES('170302','王芳',  '女',18,'03');
INSERT INTO student VALUES('170303','赵四海','男',19,'03');

INSERT INTO student VALUES('170401','孙敏',  '女',null,null);
INSERT INTO student VALUES('170402','李忠国','男',null,'04');
INSERT INTO student VALUES('170403','钱紧',  '男',17,'04');
INSERT INTO student VALUES('170404','钱多多','女',20,'04');
INSERT INTO student VALUES('170405','管八方','男',21,'04');
INSERT INTO student VALUES('170406','王兵',  '男',19,'04');
INSERT INTO student VALUES('170407','张三丰','男',100,null);

INSERT INTO course VALUES('030101','高等数学',null,2,'03');
INSERT INTO course VALUES('030102','线性代数',null,2,'03');
INSERT INTO course VALUES('030201','矩阵论','030102',3,'03');
INSERT INTO course VALUES('030202','概率论','030101',2,'03');
INSERT INTO course VALUES('030301','数理统计','030202',3,'03');

INSERT INTO course VALUES('010101','信号与系统',null,2,'01');
INSERT INTO course VALUES('010102',&#