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

第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）。

教师信息表（instructor）

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

开课信息表（section）

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

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

编程要求

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

测试说明

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

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

预期输出：

+------------------------+
| Tables_in_universityDB |
+------------------------+
| classroom              |
| course                 |
| department             |
| instructor             |
| section                |
| student                |
| takes                  |
| teaches                |
+------------------------+
1
2
3
4
5
6
7
8
9
10
11
12

---

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

参考代码

#请在此添加创建开课信息表的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 ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

第2关：查询（一）

任务描述

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

相关知识

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

学生表（student）

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

选课信息表（takes）

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

课程表（course）

教师信息表（instructor）

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

系表（department）

编程要求

在右侧编辑器中的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 *********#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17