数据库实验3

数据库实验3

第1关：内连接(1)

实验要求

查询参加了G001课程的学生人数。

class（班级表）：

gno（班级号）	gname（班级名）	grade（年级）	dept（学院）	gnum（班级人数）
0211801	软件18级1班	18级	软件	39
0211903	软件19级3班	19级	软件	38
0131901	机械19级1班	19级	机械	37
0132003	机械20级3班	20级	机械	35

teaching（教师授课表）：

cno（课程号）	tno（工号）	gno（班级号）	term（开课学期）	classroom（教室）
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

代码

---------- BEGIN ---------- 
SELECT sum(gnum) FROM class INNER JOIN teaching ON class.gno = teaching.gno WHERE teaching.cno = 'G001';
---------- END ---------- 

第2关：内连接(2)

实验要求

查询“杨梅”老师所讲授的课程，要求列出教师号、教师姓名和课程号。

teacher（教师表）

tno（工号）	tname（姓名）	sex（性别）	title（职称）	birthday（出生日期）
0014	李欣	男	教授	1969/7/25
0078	张云	女	副教授	1975/11/25
0118	王立	男	高级工程师	1985/4/28
0193	赵玲	女	讲师	1992/9/26
0213	杨梅	女	副教授	1986/6/7
0030	覃刚	男	副教授	1980/2/15

teaching（教师授课表）：

cno（课程号）	tno（工号）	gno（班级号）	term（开课学期）	classroom（教室）
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

代码

---------- BEGIN ---------- 
SELECT teacher.tno,teacher.tname,teaching.cno FROM teacher INNER JOIN teaching ON teacher.tno = teaching.tno WHERE teacher.tname = '杨梅';
---------- END ---------- 

第3关：外连接

查询18级所有班级的上课信息，要求列出班级号、课程号、开课学期和教室名。

class（班级表）

gno（班级号）	gname（班级名）	grade（年级）	dept（学院）	gnum（班级人数）
0211801	软件18级1班	18级	软件	39
0211903	软件19级3班	19级	软件	38
0131901	机械19级1班	19级	机械	37
0132003	机械20级3班	20级	机械	35

teaching（教师授课表）：

cno（课程号）	tno（工号）	gno（班级号）	term（开课学期）	classroom（教室）
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

代码

---------- BEGIN ---------- 
SELECT class.gno,teaching.cno,teaching.term,teaching.classroom FROM class FULL OUTER JOIN teaching ON class.gno = teaching.gno WHERE class.grade = '18级';
---------- END ---------- 

第4关：外连接-1

实验要求

查询所有班级的上课信息，要求列出班级号、课程号、开课学期和教室名。

class（班级表）：

gno（班级号）	gname（班级名）	grade（年级）	dept（学院）	gnum（班级人数）
0211801	软件18级1班	18级	软件	37
0211903	软件19级3班	19级	软件	35
0131901	机械19级1班	19级	机械	37
0132003	机械20级3班	20级	机械	35

teaching（教师授课表）：

cno（课程号）	tno（工号）	gno（班级号）	term（开课学期）	classroom（教室）
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

代码

---------- BEGIN ---------- 
SELECT class.gno,teaching.cno,teaching.term,teaching.classroom FROM class FULL OUTER JOIN teaching ON class.gno = teaching.gno;
---------- END ---------- 

第5关：外连接-2

实验要求

查询查询授课班级数量大于1的教师工号、姓名和授课班级数量。

teacher（教师表）：

tno（工号）	tname（姓名）	sex（性别）	title（职称）	birthday（出生日期）
0014	李欣	男	教授	1969/7/25
0078	张云	女	副教授	1975/11/25
0118	王立	男	高级工程师	1985/4/28
0193	赵玲	女	讲师	1992/9/26
0213	杨梅	女	副教授	1986/6/7
0030	覃刚	男	副教授	1980/2/15

teaching（教师授课表）：

cno（课程号）	tno（工号）	gno（班级号）	term（开课学期）	classroom（教室）
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

代码

---------- BEGIN ---------- 
SELECT teacher.tno,teacher.tname,
COUNT(DISTINCT teaching.gno)
FROM teacher
LEFT OUTER JOIN 
    teaching ON teacher.tno = teaching.tno
GROUP BY 
    teacher.tno, teacher.tname
HAVING 
    COUNT(DISTINCT teaching.gno) > 1;
---------- END ---------- 

第6关：自连接

实验要求

查询比“软件19级3班”人数少的班级信息（班级号、班级名、班级人数）。

class（班级表）：

gno（班级号）	gname（班级名）	grade（年级）	dept（学院）	gnum（班级人数）
0211801	软件18级1班	18级	软件	39
0211903	软件19级3班	19级	软件	38
0131901	机械19级1班	19级	机械	37
0132003	机械20级3班	20级	机械	35

代码

---------- BEGIN ---------- 
SELECT A.gno,A.gname,A.gnum FROM class AS a,class AS b WHERE a.gnum < b.gnum AND b.gname = '软件19级3班';
---------- END ---------- 

第7关：不相关子查询(1)

实验要求

查询讲授课程号‘R003’的教师的姓名。

teacher（教师表）

tno（工号）	tname（姓名）	sex（性别）	title（职称）	birthday（出生日期）
0014	李欣	男	教授	1969/7/25
0078	张云	女	副教授	1975/11/25
0118	王立	男	高级工程师	1985/4/28
0193	赵玲	女	讲师	1992/9/26
0213	杨梅	女	副教授	1986/6/7
0030	覃刚	男	副教授	1980/2/15

teaching（教师授课表）

cno（课程号）	tno（工号）	gno（班级号）	term（开课学期）	classroom（教室）
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

代码

---------- BEGIN ---------- 
SELECT tname FROM teacher WHERE tno IN (SELECT tno FROM teaching WHERE cno = 'R003');
---------- END ---------- 

第8关：不相关子查询(2)

实验要求

查询比“软件19级3班”人数多的班级上课信息，要求列出课程号、班级号和教师工号。

class（班级表）

gno（班级号）	gname（班级名）	grade（年级）	dept（学院）	gnum（班级人数）
0211801	软件18级1班	18级	软件	39
0211903	软件19级3班	19级	软件	38
0131901	机械19级1班	19级	机械	37

teaching（教师授课表）

cno（课程号）	tno（工号）	gno（班级号）	term（开课学期）	classroom（教室）
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

代码

 ---------- BEGIN ---------- 
SELECT cno,gno,tno FROM teaching WHERE gno IN (SELECT a.gno FROM class AS a,class AS b WHERE a.gnum > b.gnum AND b.gname = '软件19级3班');
---------- END ---------- 

第9关：相关子查询(1)

实验要求

查询参加课程号为G001班级的班级号、班级名。

class（班级表）

gno（班级号）	gname（班级名）	grade（年级）	dept（学院）	gnum（班级人数）
0211801	软件18级1班	18级	软件	39
0211903	软件19级3班	19级	软件	38
0131901	机械19级1班	19级	机械	37

teaching（教师授课表）

cno（课程号）	tno（工号）	gno（班级号）	term（开课学期）	classroom（教室）
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

代码

---------- BEGIN ---------- 
SELECT gno,gname FROM class WHERE EXISTS(SELECT cno FROM teaching WHERE gno = class.gno And cno ='G001');
---------- END ---------- 

第10关：相关子查询(2)

实验要求

查询给19级讲授课程的教师工号、姓名，查询结果按工号升序排列。

teacher（教师表）

tno（工号）	tname（姓名）	sex（性别）	title（职称）	birthday（出生日期）
0014	李欣	男	教授	1969/7/25
0078	张云	女	副教授	1975/11/25
0118	王立	男	高级工程师	1985/4/28
0193	赵玲	女	讲师	1992/9/26
0213	杨梅	女	副教授	1986/6/7
0030	覃刚	男	副教授	1980/2/15

class（班级表）

gno（班级号）	gname（班级名）	grade（年级）	dept（学院）	gnum（班级人数）
0211801	软件18级1班	18级	软件	39
0211903	软件19级3班	19级	软件	38
0131901	机械19级1班	19级	机械	37

teaching（教师授课表）

cno（课程号）	tno（工号）	gno（班级号）	term（开课学期）	classroom（教室）
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

代码

---------- BEGIN ---------- 
SELECT tno,tname FROM teacher WHERE EXISTS(SELECT * FROM teaching WHERE tno = teacher.tno AND EXISTS(SELECT * FROM class WHERE gno = teaching.gno AND grade = '19级'))order by tno;
----------END----------

第11关：数据操纵中的子查询-更新

实验要求

将讲授‘离散数学’课程的教室改为‘B001’。

course（课程表）

cno（课程号）	cname（课程名）	credit（学分）	hours（学时）	examination（考核方式）
G001	线性代数	3	48	考试
R003	数据结构	3	48	考试
R009	离散数学	3	48	考试
S023	嵌入式系统与编程实验	1	32	考察
G012	大学物理	4	64	考试

teaching（教师授课表）

cno（课程号）	tno（工号）	gno（班级号）	term（开课学期）	classroom（教室）
G001	0078	0211903	1	A101
G001	0078	0131901	1	A101
R003	0118	0211801	2	S001
R009	0213	0211903	2	S002
S023	0193	0211801	1	S001
G012	0030	0131901	2	B003

代码

---------- BEGIN ---------- 
UPDATE teaching SET classroom = 'B001' FROM course WHERE teaching.cno IN(SELECT course.cno FROM course WHERE course.cname = '离散数学');
---------- END ---------- 

实验收获：

1. 掌握了关系数据库标准语言的进阶内容。
2. 掌握了数据查询的进阶方法。
3. 掌握了相关子查询和不相关子查询的使用。

心得体会：

1. 通过学习第三章“数据查询”部分的内容，我完成了这次的实验报告，在这次报告中，我完成了数据查询的一些进阶操作，实现了内连接、外连接、自连接、不相关子查询、子查询、数据操纵中的子查询等查询方法，在完成题目的过程中，对数据表的连接和子查询的运用更加熟练。
2. 在进行这次实验报告时候，我感受到了数据库强大的查询能力，在多表之间进行査询、连接的操作，也学会了对多表进行操作。