2014数据库期末试卷船员租赁船只

有一个船员租赁船只的系统，表结构如下
Sailors(sid, sname, rating, age);
Boats(bid, bname, color);
Reaserves(sid, bid, day)
利用嵌套查询完成1）2）两个查询。

(1)找出年龄在35岁以上，rating>5的，一个月内，没有预定红色船只水手的名字；

select sname from Sailors where age > 35 and rating >5 and sid not in 
(
    select sid from Boats,Reaserves where datediff(date.today,day)<=30 and color = 'red' 
);

(2))找出年龄在35岁以上，rating>5的，一个月内，同时预定了红色船和绿色船的水手姓名；

select sname from Sailors where age > 35 and rating >5 and sid in 
(
    select sid from Boats,Reaserves where datediff(date.today,day)<=30 and (color = 'red' or color = 'green')
);

(3)找出预定了所有船的水手的名字；

select sname from Sailors s,Boats b,Reservers r
where s.sid = r.sid and count(distinct(r.bid)) = count(b.bid);