实验5 复杂查询（2）

最新推荐文章于 2024-04-25 07:30:00 发布

「已注销」

最新推荐文章于 2024-04-25 07:30:00 发布

阅读量467

点赞数

分类专栏：数据库系统概论平时练习及课堂笔记

本文链接：https://blog.csdn.net/qq_46139801/article/details/117603174

版权

数据库系统概论平时练习及课堂笔记专栏收录该内容

17 篇文章 10 订阅

订阅专栏

注意事项

1.SQL语句中用单引号表示字符串、日期，不是用双引号！！！
2.外连接格式：
左外连接：表1 left [outer] join 表2 on 条件
右外连接：表1 right [outer] join 表2 on 条件
完整外连接：表1 full [outer] join 表2 on 条件
3.insert语句格式：
insert
into <表名> [(<属性列1>,<属性列2>,…)]
values(<常量1>,<常量2>,…)
4.delete语句格式：
delete
[from] <表名>
where <条件>
5.update语句格式：
update <表名>
set <列名>=<表达式>,…
where <条件>
6.SQL语句用create view 命令建立视图，其一般格式为
create view <视图名> [<列名>,…]
as
子查询
[with check option]
/*
检查选项（检查where条件），对视图进行update、insert和delete操作时
要保证更新、插入或删除的行满足视图定义中的谓词条件（where条件）
*/

一、实验内容

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

二. 实验内容：
打开“教学数据库”。再创建两个表T和TC，各表的结构如表所示。
在这里插入图片描述

写出下列查询的SQL语句：

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

二、参考解答

/*a.创建T（教师）表*/
create table T
(TNO char(5) primary key,  
--为主键的属性非空，可不加not null。因为主键自动不为空（实体完整性规则：主键不为空）。
 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) references T(TNO),
--为主键的属性非空，可不加not null。因为主键自动不为空（实体完整性规则：主键不为空）。
 CNO char(5) references C(CNO),
--为主键的属性非空，可不加not null。因为主键自动不为空（实体完整性规则：主键不为空）。
 primary key(TNO,CNO)
)

/*b.创建TC（授课）表结构（法二）*/
create table TC
(TNO char(5),
 CNO char(5),
 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) references T(TNO),
 CNO char(5) 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='刘伟'
			 )

老师提供的参考答案：

1.利用SQL语句创建TC表。
create table TC(
tno char(5) references t(tno),
cno char(5) 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 INDEX1 on t(tn)

4.查询所有学生选修所有课程的姓名、课程名和成绩，包括没有选课的学生，包括没有被选修的课程。
select sn,cn,score
from (s full join sc on sc.sno=s.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 c,sc where sc.cno=c.cno and cn='程序设计')

7.创建一个试图avg_view，求每门课程的课程号、课程名和平均分。
create view avg_view as
select c.cno,cn,avg(score) 平均分
from c,sc
where c.cno=sc.cno
group by c.cno,cn

8.把讲授C5课程的教师的岗位津贴增加100元。
update t
set comm=comm+100
where tno in(select tno from tc where cno='c5')

9.删除刘伟教师的授课记录。
delete tc
where tno=(select tno from t where tn='刘伟')