1.写出创建如下三张数据表的SQL语句“教师表”表结构：教师号：普通编码定长字符类型，长度为10，主键。教师名：普通编码可变长字符类型，长度为20，非空。所在部门：普通编码可变长

Question

1.写出创建如下三张数据表的SQL语句 “教师表”表结构：
教师号：普通编码定长字符类型，长度为10，主键。
教师名：普通编码可变长字符类型，长度为20，非空。
所在部门：普通编码可变长字符类型，长度为30.
职称：普通编码定长字符类型，长度为6，取值为：“教授”、“副教授”、“其他”。

“课程”表结构：
课程号：普通编码定长字符类型，长度为20，主键。
课程名：普通编码可变长字符类型，长度为40，非空。
学时数：微整形。
开课学期：微整形

“授课”表结构：
教师号：普通编码定长字符类型，长度为10，引用教师表的外键。
课程号：普通编码定长字符类型，长度为20，引用课程表的外键。
授课时数：整形。
授课年份：整形。
主键：（教师号，课程号，授课年份）。

2.依据上题所创建的三张表，写出下列操作的SQL语言。
a、查询教授职称的教师姓名和所在部门
b、查询每学期开设的学时数大于40的课程门数
c、查询2012年全部课程的授课情况，列出课程名、学时数、教师名和授课时数。
d、删除没人讲授的课程。

3.依据第一题所创建的三张表，写出创建满足如下要求的视图的QL语句：查询2014授课年份全体授课教师的教师名、所授的课程名和授课时数。

Answer 1

CREATE TABLE TEACHER
(
 TID CHAR(10) primary key,
 TNAME VARCHAR(20),
 TDEPT VARCHAR(30),
 TJNAME CHAR(6)
)
insert into TEACHER values('110','黄嘉欣','管理学系','教授')
insert into TEACHER values('111','刘而已','计算机系','教师')
insert into TEACHER values('112','王嘉骥','外语系','副教授')

CREATE TABLE COURSE1
(
  CNO CHAR(20) primary key,
  CNAME VARCHAR(40),
  CHOUR tinyint ,
  TERM tinyint 
)
insert into COURSE1 values('011','酒店管理',35,1)
insert into COURSE1 values('012','管理学基础',75,1)
insert into COURSE1 values('021','JAVA基础',80,2)
insert into COURSE1 values('022','安卓开发',70,2)
insert into COURSE1 values('031','英语作文书写',40,1)
insert into COURSE1 values('032','日语听力练习',70,1)

insert into COURSE1 values('033','日语写作',70,1)
insert into COURSE1 values('023','数据结构',20,1)

create table tcourse
(
TID CHAR(10) ,
CNO CHAR(20) ,
thour int,
tyear int --这个题有些矛盾,如果这里设为了主键,下面插入数据就没法插入重复的,c.3.就没有意义
)
ALTER TABLE tcourse ADD CONSTRAINT TID_cons FOREIGN KEY (TID) REFERENCES teacher;
ALTER TABLE tcourse ADD CONSTRAINT CNO_cons FOREIGN KEY (CNO) REFERENCES course1;

insert into tcourse values('110','011',35,2014)
insert into tcourse values('110','012',75,2012)
insert into tcourse values('111','021',80,2015)
insert into tcourse values('111','022',70,2013)
insert into tcourse values('112','031',40,2014)
insert into tcourse values('112','032',70,2014)
--a.
SELECT TNAME,TDEPT FROM TEACHER WHERE TJNAME='教授'
--b.
SELECT COUNT(CNO) FROM COURSE1 WHERE CHOUR>40
--C.
select CNAME,CHOUR,TNAME,thour FROM COURSE1 C1,TEACHER TR ,tcourse TE 
WHERE C1.CNO=TE.CNO AND TR.TID = TE.TID AND TE.tyear=2012
--D.
DELETE COURSE1 WHERE CNO NOT IN(SELECT CNO FROM tcourse)
--3.
CREATE VIEW QL
as
SELECT TNAME,CNAME,thour  FROM COURSE1 C1,TEACHER TR ,tcourse TE 
WHERE C1.CNO=TE.CNO AND TR.TID = TE.TID AND TE.tyear=2014

SELECT * FROM QL

我用的数据库是SQL2008 R2，已测试。