create view v1 as(with delay1 as (select Tail_Number from airline where DepDelay > 0) 
 select *, count(*) as cnt1 from delay1 group by Tail_Number); 
 create view v2 as(with delay2 as (select Tail_Number from airline where ArrDelay > 0) 
 select *, count(*) as cnt2 from delay2 group by Tail_Number); 
 create view v0 as (select Tail_Number from v1) union (select Tail_Number from v2);

create view con1 as 
 select * from v2 where not exists 
 (select * from v1 where v1.Tail_Number = v2.Tail_Number); 

create view con2 as 
 select * from v1 where not exists 
 (select * from v2 where v1.Tail_Number = v2.Tail_Number); 
 create view con3 as select * from v1 natural join v2; 


 select * from v0 natural left outer join con2 
 natural left outer join con1 where cnt1 is not null or cnt2 is not null
 union all 
 select * from con3;

各机场与航班延误的关系
#

列出各机场始发、到达及总的延误次数，组合为一张表的查询形式。

利用view和natural join：

create view v1 as(with delay1 as (select Origin as City from airline where DepDelay > 0) 
 select *, count(*) as cnt1 from delay1 group by City); 
 create view v2 as(with delay2 as (select Dest as City from airline where ArrDelay > 0) 
 select *, count(*) as cnt2 from delay2 group by City); 
 select *, (cnt1+cnt2) as total from 
 v1 natural join v2 
 order by total desc;

航班目的地与始发地对延误的影响
#

列出各城市始发、到达及总的延误次数，组合为一张表的查询形式。

利用view和natural join：

create view v1 as(with delay1 as (select OriginCityName as City from airline where DepDelay > 0) 
 select *, count(*) as cnt1 from delay1 group by City); 
 create view v2 as(with delay2 as (select DestCityName as City from airline where ArrDelay > 0) 
 select *, count(*) as cnt2 from delay2 group by City); 
 select *, (cnt1+cnt2) as total from 
 v1 natural join v2 
 order by total desc;

题目2
#

假设我们有三个关系 R(A, B)、S(B, C) 和 T(B, D)，所有属性都被声明为非空。 a. 给出关系 R、S 和 T 的实例，使得在 (R NATURAL LEFT OUTER JOIN S) NATURAL LEFT OUTER JOIN T 的结果中，属性 C 具有空值，但属性 D 具有非空值。 b. 是否存在关系 R、S 和 T 的实例，使得 R NATURAL LEFT OUTER JOIN (S NATURAL LEFT OUTER JOIN T) 的结果对于 C 具有空值，但对于 D 具有非空值？解释为什么或为什么不。

不妨令R、S、T分别为：