实训三:多表查询 - 大学数据库创建与查询实战

第1关：数据库表设计

任务描述

本关任务：创建大学数据库的教师信息表和开课信息表。

相关知识

大学数据库的整体设计

一个大学里面会有很多教学楼、学生、老师，他们之间的都有着密不可分的关系，因此将大学数据库整体设计如下图所示，总共涉及到八张表：

1. 学生表（student）：包含学生学号 ID，学生姓名 name，学生所在系 dept_name，总学分 tot_cred 属性。

2. 课程表（course）：包含课程号 course_id，课程名称 title，课程所属的系名 dept_name，学分 credits 属性。

3. 系表（department）：包含系名 dept_name，建筑 building，预算 budget 属性。

4. 教室表（classroom）：包含建筑 building，房间号 room_number，容量 capacity 属性。

5. 教学信息表（teaches）：包含教室号 ID，课程号 course_id，课程信息序号 sec_id，学期 semester，年份 year 属性。

6. 选课信息表（takes）：包含学生学号 ID，课程标识 course_id，课程段标识 sec_id，学期 semester，年份 year，成绩 grade 属性。

7. 教师信息表（instructor）。

8. 开课信息表（section）。

图1 大学数据库模型

教师信息表（instructor）

教师信息表的具体属性及约束如下：

字段名称	类型	备注	约束
ID	varchar(5)	教师号	主键
name	varchar(20)	教师名字	非空
dept_name	varchar(20)	教师所在的系	外键
salary	numeric(8,2)	薪资	底薪大于 29000

开课信息表（section）

开课信息表的具体属性及约束如下：

字段名称	类型	备注	约束
course_id	varchar(8)	课程号	外键
sec_id	varchar(8)	课程段标识	无
semester	varchar(6)	学期	只能插入 Fall，Winter，Spring，Summer四个值。
year	numeric(4,0)	年份	1701 < year < 2100
building	varchar(15)	课程所在建筑	外键
room_number	varchar(7)	课程所在教室号	外键
time_slot_id	varchar(4)	时间档序号	无

section 表的主键由 course_id，sec_id，semester，year 四个属性共同组成。

编程要求

在右侧编辑器中的Begin-End之间补充代码，创建教师信息表和开课信息表（其他六张表系统已为你创建完成），使用 universityDB 数据库，创建的表命名依次为 instructor 和 section。

测试说明

平台会对你编写的代码进行测试，比对你输出的数值与实际正确数值，只有所有数据全部计算正确才能通过测试：

每次点击评测后台都会将数据库环境重置，数据库 universityDB 会自动创建好。

预期输出：

+------------------------+
| Tables_in_universityDB |
+------------------------+
| classroom              |
| course                 |
| department             |
| instructor             |
| section                |
| student                |
| takes                  |
| teaches                |
+------------------------+

---

开始你的任务吧，祝你成功！

参考代码

#请在此添加创建开课信息表的SQL语句
########## Begin ##########
#请在此添加创建开课信息表的SQL语句
########## Begin ##########
use universityDB;
create table instructor(
    ID varchar(5) COMMENT'教师号',primary key(ID),
    name varchar(20) COMMENT'教师名字'NOT NULL,
    dept_name varchar(20) DEFAULT NULL COMMENT'教师所在的系',
    salary numeric(8,2) COMMENT'薪资' check(salary>29000),
    foreign key (dept_name) references department(dept_name)
        on delete set null
);
create table section(
    course_id varchar(8)  COMMENT '课程号' ,
    sec_id varchar(8) COMMENT'课程段标识',
    semester varchar(6) COMMENT'学期' check(semester in('Fall','Winter','Spring','Summer')),
    year numeric(4,0)  COMMENT'年份',
    building varchar(15)  COMMENT '课程所在建筑' ,
    room_number varchar(7)  COMMENT '课程所在教室号' ,
    time_slot_id varchar(4) COMMENT'时间档序号',
    primary key (course_id, sec_id, semester, year),
     foreign key (course_id) references course(course_id)
        on delete cascade,
     foreign key (building, room_number) references classroom(building, room_number)
        on delete set null
);
 
########## End ##########

########## End ##########

第2关：查询（一）

任务描述

本关任务：编写 sql 语句，输出符合条件的查询结果。

相关知识

为了完成本关任务，下面列出你可能需要使用到的表及数据。

学生表（student）

其中 dept_name 是系表（department）的外键。

ID	name	dept_name	tot_cred
00128	Zhang	Comp. Sci.	102
12345	Shankar	Comp. Sci.	32
19991	Brandt	History	80
23121	Chavez	Finance	110
44553	Peltier	Physics	56
45678	Levy	Physics	46
54321	Williams	Comp. Sci.	54
55739	Sanchez	Music	38
70557	Snow	Physics	0
76543	Brown	Comp. Sci.	58
76653	Aoi	Elec. Eng.	60
98765	Bourikas	Elec. Eng.	98
98988	Tanaka	Biology	120

选课信息表（takes）

其中 course_id，sec_id，semester，year 为开课信息表（section）的外键，ID 为学生表（student）的外键。

ID	course_id	sec_id	semester	year	grade
00128	CS-101	1	Fall	2009	A
00128	CS-347	1	Fall	2009	A-
12345	CS-101	1	Fall	2009	C
12345	CS-190	2	Spring	2009	A
12345	CS-315	1	Spring	2010	A
12345	CS-347	1	Fall	2009	A
19991	HIS-351	1	Spring	2010	B
23121	FIN-201	1	Spring	2010	C+
44553	PHY-101	1	Fall	2009	B-
45678	CS-101	1	Fall	2009	F
45678	CS-101	1	Spring	2010	B+
45678	CS-319	1	Spring	2010	B
54321	CS-101	1	Fall	2009	A-
54321	CS-190	2	Spring	2009	B+
55739	MU-199	1	Spring	2010	A-
76543	CS-101	1	Fall	2009	A
76543	CS-319	2	Spring	2010	A
76653	EE-181	1	Spring	2009	C
98765	CS-101	1	Fall	2009	C-
98765	CS-315	1	Spring	2010	B
98988	BIO-101	1	Summer	2009	A
98988	BIO-301	1	Summer	2010	null

课程表（course）

course_id	title	dept_name	credits
BIO-101	Intro. to Biology	Biology	4
BIO-301	Genetics	Biology	4
BIO-399	Computational Biology	Biology	3
CS-101	Intro. to Computer Science	Comp. Sci.	4
CS-190	Game Design	Comp. Sci.	4
CS-315	Robotics	Comp. Sci.	3
CS-319	Image Processing	Comp. Sci.	3
CS-347	Database System Concepts	Comp. Sci.	3
EE-181	Intro. to Digital Systems	Elec. Eng.	3
FIN-201	Investment Banking	Finance	3
HIS-351	World History	History	3
MU-199	Music Video Production	Music	3
PHY-101	Physical Principles	Physics	4

教师信息表（instructor）

其中 dept_name 字段为系表（department）的外键。

ID	name	dept_name	salary
10101	Srinivasan	Comp. Sci.	68250
12121	Wu	Finance	94500
15151	Mozart	Music	42000
22222	Einstein	Physics	99750
32343	El Said	History	63000
33456	Gold	Physics	91350
45565	Katz	Comp. Sci.	78750
58583	Califieri	History	65100
76543	Singh	Finance	84000
76766	Crick	Biology	75600
83821	Brandt	Comp. Sci.	96600
98345	Kim Elec.	Eng.	84000

系表（department）

dept_name	building	budget
Biology	Watson	90000
Comp. Sci.	Taylor	100000
Elec. Eng.	Taylor	85000
Finance	Painter	120000
History	Painter	50000
Music	Packard	80000
Physics	Watson	70000

编程要求

在右侧编辑器中的Begin-End之间补充代码，完成下列查询：

1. 查询修了‘ Biology ’所有课程的学生姓名；

2. 查询满足工资至少比‘ Biology ’系某一个教师的工资高的所有教师的姓名；

3. 查询所有教师的姓名，以及他们所在系的名称和系所在建筑的名称；

4. 查询在包含‘ Watson ’字段的大楼开课的所有系名。

测试说明

补充完代码后，点击测评，平台会对你编写的代码进行测试，当你的结果与预期输出一致时，即为通过。

---

开始你的任务吧，祝你成功！

代码参考

#********* Begin *********#
echo "
select distinct name from student,takes,course
where student.ID=takes.ID and takes.course_id=course.course_id
and course.dept_name='Biology';

select name from instructor where salary>any
(select salary from instructor where dept_name='Biology');

select name,department.dept_name,building from instructor,department
where instructor.dept_name=department.dept_name;

select distinct department.dept_name from course,department
where course.dept_name=department.dept_name
and building like '%Watson%';
"
#********* End *********#