数据库实验三 Sql多表查询和视图

一、Sql表

www.db-book.com

二、在线练习

对所有表执行查询语句，查看有哪些数据。
select * from tableName;
一、执行以下查询语句，写出查询意图。
(1) select * from student,takes
where student.ID = takes.ID
根据学号连接stduent表和takes表进行查询

(2) select * from student natural join takes
stduent表和takes表进行自然连接后进行查询

(3) select * from student natural left outer join takes
stduent表自然左接takes表后进行查询

(4) select ID
from student natural left outer join takes
where course_id is null
stduent表自然左接takes表后查询课程id为空的学生id

（5）select name,instructor.dept_name,building
from instructor,department
where instructor.dept_name = department.dept_name
将instructor表和department表中instructor.dept_name和department.dept_name进行等值连接，然后查询表中name,instructor.dept_name,building等信息

（6）select name,course_id
from instructor,teaches
where instructor.ID=teaches.ID
将instructor表和teaches表中instructor.ID和teaches.ID进行等值连接，然后查询表中name,course_id等信息

（7）create view faculty as
select ID,name,dept_name
from instructor
创建一个包含instructor中select ID,name,dept_name数据的视图

（8）select * from faculty
查询faculty视图

（9）create view phy_fall_2017 as
select course.course_id,sec_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 = ‘2017’
创建一个列出Physics系在2017年秋季学期所开设的所有课程段，以及每个课程段在那栋建筑的那个房间授课的信息的视图

(10) select * from phy_fall_2017
查询from phy_fall_2017视图

(11) drop view phy_fall_2017
删除phy_fall_2017视图

(12)
create view dept_total_salary(dept_name,total_salary)
as
select dept_name,sum(salary)
from instructor
group by dept_name
创建一个dept_total_salary视图，并且以dept_name,total_salary作为表头，其中的数据从instructor的dept_name,sum(salary)获取，并且按dept_name分组

(13) select * from dept_total_salary
查询dept_total_salary视图

二、写出SQL查询语句
(14)找出名叫Einstein的教师所教授的所有学生的学号，保证结果中没有重复。
select ID
from student
where student.ID in (select s_ID
from advisor join instructor
on i_ID in(
select ID
from instructor
where name=‘Einstein’));

（15）找出2017年秋季开设的每个课程的选课人数。
select course_id, sec_id, count(ID)
from section natural join takes
where semester = ‘Fall’ and year = 2017
group by course_id, sec_id

（16）从2017年秋季开设的所有课程段中，找出最多的选课人数。
select Max(cnt)
from (
select Count(ID) as cnt
from section natural join takes
where semester = ‘Fall’ and year = 2017 group by course_id, sec_id
)

（17）找出2017年秋季选课人数最多的课程编号。
with Fall_2017 as
(
select course_id, sec_id, count(ID) as cnt
from section natural join takes
where semester = ‘Fall’ and year = 2017
group by course_id, sec_id
)
select course_id,sec_id
from Fall_2017
where cnt = (select max(cnt) from Fall_2017)