注意事项

1.SQL语句中用单引号表示字符串、日期，不是用双引号！！！

一、实验内容

一. 实验目的：
1. 理解左外连接、右外连接和完整外连接的区别。
2. 掌握SQL语句中的join子句。
3. 掌握用SQL语句实现数据的增删改。
4. 掌握SQL语句中的如何创建视图。

二. 实验内容：
打开“教学数据库”。再创建两个表T和TC，各表的结构如表所示。

写出下列查询的SQL语句：

1. 利用SQL语句创建TC表。
2. 在C表中插入一门课程：C8，C语言程序设计，72。
3. 在T表的TN上创建一个唯一性索引。
4. 查询所有学生选修所有课程的姓名、课程名和成绩，包括没有选课的学生，包括没有被选修的课程。
5. 查询姓张的教师的姓名和所教课程的门数。
6. 查询没有选修“程序设计”课程的学生姓名。
7. 创建一个试图avg_view，求每门课程的课程号、课程名和平均分。
8. 把讲授C5课程的教师的岗位津贴增加100元。
9. 删除刘伟教师的授课记录。

二、参考解答

/*a.创建T（教师）表*/
create table T
(TNO char(5) not null primary key,
 TN char(8) not null,
 SEX char(2),
 AGE tinyint,
 PROF char(10),
 SAL int,
 COMM int,
 DEPT char(20)
)

/*b.创建TC（授课）表结构（法一）*/
create table TC
(TNO char(5) not null references T(TNO),
 CNO char(5) not null references C(CNO),
 primary key(TNO,CNO)
)

/*b.创建TC（授课）表结构（法二）*/
create table TC
(TNO char(5) not null,
 CNO char(5) not null,
 primary key(TNO,CNO),
 foreign key(TNO) references T(TNO)
 foreign key(CNO) references C(CNO)
)

/*1.利用SQL语句创建TC表（同b）*/
create table TC
(TNO char(5) not null references T(TNO),
 CNO char(5) not null references C(CNO),
 primary key(TNO,CNO)
)

/*2.在C表中插入一门课程：C8，C语言程序设计，72*/
insert
into C
values('C8','C语言程序设计',72)

/*3.在T表的TN上创建一个唯一性索引*/
create unique index tname on T(TN)

/*4.查询所有学生选修所有课程的姓名、课程名和成绩，
包括没有选课的学生，包括没有被选修的课程*/
select SN 姓名,CN 课程名,SCORE 成绩
from S full join SC on S.SNO=SC.SNO full join C on SC.CNO=C.CNO

/*5.查询姓张的教师的姓名和所教课程的门数*/
select TN 姓张的教师姓名,COUNT(*) 所教课程的门数
from T,TC
where T.TNO=TC.TNO and TN like '张%'
group by TN

/*6.查询没有选修“程序设计”课程的学生姓名*/
select SN  没有选修程序设计课程的学生姓名
from S
where SNO not in(select SNO
				 from SC,C
				 where SC.CNO=C.CNO and CN='程序设计'
				 )

/*7.创建一个试图avg_view，求每门课程的课程号、课程名和平均分*/
create view avg_view
as
select C.CNO 课程号,CN 课程名,AVG(SCORE) 平均分
from SC,C
where SC.CNO=C.CNO
group by C.CNO,CN

/*8.把讲授C5课程的教师的岗位津贴增加100元*/
update T
set COMM+=100
where TNO in(select TNO
			 from TC
			 where CNO='C5'
			 )

/*9.删除刘伟教师的授课记录*/
delete
TC
where TNO in(select TNO
			 from T
			 where TN='刘伟'
			 )