数据库系统概念第四章习题答案

4.1

a.显示所有教师列表，列出他们的ID、姓名以及所讲授课程段的编号。对于没有讲授任何课程的教师，确保将课程段编号显示为0.在你的查询之中应该使用外连接，不能使用标量子查询。

select ID, name, sec_id
set sec_id = case
			when sec_id is null then 0
			end
from instructor natural left outer join teaches
group by ID,name;

上面的set语句不知道对不对！oracle没有跑成功

标答

select ID, name,
	count(course_id, sec_id, year,semester) as 'Number of sections'
from instructor natural left outer join teaches
group by ID, name

这题有点不太理解题目意思，哭~~~~标答在oracle上显示count()参数错误，我也觉得神奇！

函数nvl(p,q)可以实现null值赋值：如果p是null，则赋予q。如下

select ID,name,nvl(sec_id,0)
from instructor natural left outer join teaches;

oracle结果正确，但是不能使用group by.

b.使用标量子查询，不使用外连接写出上述查询。

直接上标答

select ID, name,
	(select count(*) as 'Number of sections'
	 from teaches T 
	 where T.id = I.id)
from instructor I

c. 显示2010年春季开设的所有课程的列表，包括讲授课程段的教师姓名。如果一个课程段有不止一位教师讲授，那么有多少位教师，此课程段在结果中就出现多少次。如果一个课程段没有任何教师，它也要出现在结果中，相应教师名设置为“—”.

select course_id, sec_id, ID,
	decode(name, NULL, '-', name)
from (section natural left outer join teaches)
			natural left outer join instructor
where semester='Spring' and year= 2010

decode()函数，检测控制并赋值。
decode函数详细介绍
section和teaches左外连接给出了对应课程上老师的ID，再与instructor左外连接得到老师的name.

d. 显示所有系的列表，包括每个系中教师的总人数，不能使用标量子查询。确保正确处理没有教师的系。

select distinct dept_name, count(ID)
from department natural left outer join instructor
group by dept_name;

4.2 不使用外连接重写查询

a

select* from student natural left outer join takes

解答：

select * from student natural join takes
union
select ID, name, dept_name, tot_cred, NULL, NULL, NULL, NULL, NULL
from student S1 where not exists
(select ID from takes T1 where T1.id = S1.id)

b

select* from student natural full outer join takes

解答

select* from student natural join takes
union
select ID, name,dept_name,tot_cred, NULL,NULL,NULL,NULL,NULL
from student S where not exists
	(select ID from takes T where S.ID = T.ID)
union
select ID, NULL,NULL,NULL,course_id, sec_id,semester,yesr,grade
from takes T where not exists 
	(select ID from student S where T.ID =S.ID)

4.7 关系数据库如下

Answer:

create table employee
(person name char(20),
street char(30),
city char(30),
primary key (person_name))

create table works
(person name char(20),
company name char(15),
salary integer,
primary key (person_name),
foreign key (person_name) references employee,
foreign key (company_name) references company)

create table company
(company_name char(15),
city char(30),
primary key (company_name))

create table manages
(person_name char(20),
manager_name char(20),
primary key (person_name),
foreign key (person_name) references employee,
foreign key (manager_name) references employee)

4.12 查找那些没有经理的雇员。注意一个雇员可能只是没有列出其经理，或者有null经理。

使用外连接

select employee_name
from employee natural left outer join manages
where manager_name is null

不使用外连接

（1）not exists版本

select employee_name as d
from employee as E
where not exists
	(select employee_name
	from manages as M
	where E.employee_name = M.employee_name 
		and manager_name is not null

（2）not in版本

select employee_name as d
from employee as E
where not in
	(select employee_name
	from manages as M
	where E.employee_name = M.employee_name 
		and manager_name is not null

(3)except版本

select employee_name
from employee
except
select employee_name
from employee natural join manages
where manager_name is not null