第二次数据库课后习题（第四五章）

各位大佬，看过记得留言，毕竟我也不晓得答案正确性（老师不改的，写这个博客纯粹是为了记录生活），答案错误就会修改，同时可以给后人指明前路，你的留言是我不断更新的动力。
- 4.1

a)

select teaches.id,name,decode(name,null,0,course_id) course_id
from teaches left outer join instructor
on teaches.id=instructor

b)

select teaches.id,name,(select decode(name,null,0,teaches.course_id)
                         from teaches
                         where teaches.id=instructor.id)course_id
from instructor,teaches;

c)

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

d)

select dept_name,count(id)
from instructor natural left outer join department
group by dept_name

• 4.2

a)

 select *
from student natural join takes union
select id,name,dept_name,tot_cred,null,null,null,null,null
from student 
where not exists(select id from takes where student.id=takes.id);

b)

select *
from student natural join takes union
select id，null,null,null,null,null,null,null,null
from student 
where not exists(select id from takes where student.id=takes.id)
union (
select id，null,null,null,course_id,sec_id,semester,year,grade
from takes 
where not exists(select id from student where student.id=takes.id));

• 4.3

a)

r(A,B),s(E,F),t(B,D)

b)

因为s左外联t，里面有相同的元素，所以只会出现c和d同时为空所以不会出现C为空且D非空。

• 4.5

a)

 create view student_grades(ID,GPA) as
select id, GPA
  from (select sum(credits*pointers)/sum(credits) as GPA
           from (takes left outer join course) left outer join grade_points

• 4.8

a)

select id,name,course_id,sec_id,year,semester,time_slot_id, count (*) as times
    from instructor I natural join teaches t natural join section S
group by id,name,course_id,sec_id,year,semester,time_slot_id
  HAVING COUNT (*) > 1;

• 4.9

由于manager_name是manager的外键，所以删除会导致触发管理者与员工所对应的元组，级联删除manager_name 

• 4.12

select  m.employee_name
from employee e right outer join managers m
on e.employee_name=m.employee_name and m.manager_name=null;

select employee_name
from employee e natural join manages m
where manager_name not in (select manager_name
from managers)

• 4.15

case when(expression 1 is not null) then expression 1
 when(expression 2 is not null) then expression 2
…………………………..
When(expression n is not null) then expression n
Else null 
End

• 5.9

group by (a),rollup(b),rollup(c),rollup(d)

• 5.10

Create  table s (student varchar(20)
Subject varchar(20)
Marks  INT);
Select * 
From (student,sum(marks) grades,rank() over(order by(grades) desc) Rank
from S
group by student)
where Rank<n;

• 5.11

(  SELECT color, clothes_size, SUM (num)
     FROM sales
 GROUP BY color, clothes_size)
UNION
(  SELECT color, 'all', SUM (num)
     FROM sales
 GROUP BY color)
UNION
(  SELECT 'all', clothes_size, SUM (ber)
     FROM sales
 GROUP BY clothes_size)
UNION
(  SELECT 'all', clothes_size, SUM (num)
     FROM sales
 GROUP BY clothes_size)
UNION
(SELECT 'all', 'all', SUM (num) FROM sales)

• 5.25

Select *
From ( id,GPA,rank() over(order by(GPA) desc) Rank
   From student_grades)
Where Rank<=10;

• 5.27

SELECT area20, SUM (c)
    FROM (SELECT c, NTILE (20) OVER (ORDER BY (a)) AS area20 FROM r) 
GROUP BY area20

• 5.28

(SELECT 1, COUNT (*)
   FROM account
  WHERE 3 * balance <= (SELECT MAX (balance) FROM account))
UNION
(SELECT 2, COUNT (*)
   FROM account
  WHERE     3 * balance > (SELECT MAX (balance) FROM account)
        AND 1.5 * balance <= (SELECT MAX (balance) FROM account))
UNION
(SELECT 3, COUNT (*)
   FROM account
  WHERE 1.5 * balance > (SELECT MAX (balance) FROM account))