1.写出创建如下三张数据表的SQL语句 “教师表”表结构：

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,thourFROM 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，已测试。