大学数据库创建与查询实战_在右侧编辑器中的begin-end之间补充代码,创建教师信息表和开课信息表(其他六张表

第1关：数据库表设计

任务描述

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

编程要求

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

测试说明

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

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

预期输出：

1. +------------------------+
2. | Tables_in_universityDB |
3. +------------------------+
4. | classroom |
5. | course |
6. | department |
7. | instructor |
8. | section |
9. | student |
10. | takes |
11. | teaches |
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 ##########

第2关：查询（一）

任务描述

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

编程要求

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

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

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

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

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

测试说明

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

---

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

#********* Begin *********#
echo "
select name
from student where 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 instructor.dept_name
from instructor,department where instructor.dept_name=department.dept_name and building='Watson';
 
 
"
#********* End *********#

第3关：查询（二）

任务描述

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

编程要求

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

1. 查询2010年春季学期教课的教师数；

2. 查询2009年秋季学期讲授2门课程的教师信息；

3. 查询每个系在2010年春季学期讲授至少一门课的教师人数。

测试说明

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

---

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

#********* Begin *********#
echo "
select count(distinct ID) from teaches where semester ='spring' and year='2010';
select instructor.ID,name,dept_name from teaches,instructor where semester='Fall'and year='2009' and teaches.ID=instructor.ID group by ID having count(distinct course_id)=2;
select dept_name, count(distinct instructor.ID) as instr_count from instructor,teaches where semester='Spring' and year='2010' and instructor.ID=teaches.ID group by dept_name having count(distinct course_id)>=1
"
#********* End *********#

第4关：查询（三）

任务描述

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

编程要求

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

1. 根据工资将教师信息表（instructor）关系按工资的降序、姓名的升序排列；

2. 查询大学里的最高工资；

3. 找出平均工资最高的系；

4. 查询教师平均工资超过50000的系，并按系名升序和平均工资降序排序。

注意：编写查询语句时，需要查询列表所有信息时，请使用表名.*（由于评测原因指定这样书写，实际自己使用可以直接*的）。

测试说明

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

---

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

#********* Begin *********#
echo "
select instructor.* from instructor order by salary desc,name asc ;
select max(salary) from instructor;
select dept_name from instructor group by dept_name having avg(salary)=(
select max(avgs) from(select avg(salary) avgs,dept_name  from instructor group by dept_name) a);
select dept_name, avg(salary) as avg_salary from instructor group by dept_name having avg(salary)>50000  order by dept_name asc,avg(salary) desc;
"
#********* End *********#

第5关：查询（四） 

任务描述

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

编程要求

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

1. 查询在2009年秋季学期开课，但不在2010年春季学期开课的课程名称；

2. 查询2009年秋季和2010年春季都开设的所有课程号；

3. 查询2009年秋季学期和2010年春季学期开设的所有课程；

4. 对于在2009年讲授的每个课程段，如果该课程段至少有两名学生选课，查询该课程段信息和选修该课程段的所有学生的总分数（ tot_cred ）的平均值。

注意：编写查询语句时，需要查询列表所有信息时，请使用表名.*（由于评测原因指定这样书写，实际自己使用可以直接*的）。

测试说明

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

---

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

#********* Begin *********#
echo "
select distinct course_id 
from section 
where semester='Fall' and year=2009 and
course_id not in (select course_id from section where semester='Spring' and
year=2010);
 
select course_id 
from section as S 
where year=2009 and semester='Fall' and exists
(select section.* from section as T where year=2010 and semester='Spring' and
S.course_id=T.course_id);
 
select course_id 
from section 
where year=2009 and semester='Fall' union all select
course_id from section where year=2010 and semester='Spring';
 
select course_id,semester,year,sec_id,avg(tot_cred) 
from takes join student join department on takes.ID = student.ID 
where year=2009 group by course_id,semester,year,sec_id having count(takes.ID)>=13;
 
 
"
#********* End *********#

 

第6关：查询（五）

任务描述

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

编程要求

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

1. 查询‘ Physics ’系的所有教师姓名及其课程号；

2. 查询所有在‘ Physics ’系并且工资大于80000的教师姓名；

3. 年度工资增长，请你更新各教室的工资，要求10万元以上的教师涨3%，10万元及以下的教师涨5%（注意要先更新工资大于10万的，再更新小于10万的，否则先更新小于10万的，可能更新后大于10万了）；

4. 创建视图，列出‘ Physics ’系在2009年秋季学期开设的所有课程段，以及课程段在哪栋建筑的哪个房间授课，视图名称为Physics_fall_2009。

注意：编写查询语句时，需要查询列表所有信息时，请使用表名.*（由于评测原因指定这样书写，实际自己使用可以直接*的）。

测试说明

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

---

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

#********* Begin *********#
echo "
select instructor.name,teaches.course_id 
from instructor,teaches 
where instructor.ID=teaches.ID and dept_name='Physics';
 
select name 
from instructor 
where dept_name='Physics' and salary>80000;
update instructor set salary = case when salary <= 100000 then salary*1.05 else salary*1.03 end;
create view Physics_fall_2009 as select course.course_id,building,room_number from course,section where course.course_id = section.course_id and course.dept_name='Physics' and section.semester='Fall' and section.year='2009';
 
 
"
#********* End *********#