boat-sailor 数据库SQL语言上机实验问题

【boat-sailor】

saliors(sid,sname,rating,age)，其中sid表示水手号，sname表示水手姓名，rating表示水手级别，age水

手年龄；

boats(bid,bname,color) 其中bid表示船号，bname表示船名，color表示船的颜色；

reserves(sid,bid,day) 其中sid表示水手号，bid表示船号，day表示水手预定船的日期

1. 找出预订了103船的水手的名字
SELECT distinct sname
FROM saliors
WHERE sid IN(select sid from reserves where bid='103')

Dustin
Lubber
Horatio

2. 找出预订了红色船的所有水手的名字
SELECT distinct sname
FROM saliors
WHERE sid IN(select sid from reserves where bid in
  (select bid from boats where color='red'))

Dustin
Lubber
Horatio

3. 找出Lubber预定的船的所有颜色
select distinct color
from boats
where bid in(select bid from reserves where sid in
  (select sid from saliors where sname='Lubber'))

Red
Green
Red

4. 找出至少预订了一艘船的水手的名字
select distinct sname
from saliors
where exists(select sid from reserves)

或者select sname from sailors where sid in( select sid from reserves);

Andy
Art
Bob
Brutus
Dustin
Horatio
Lubber
Rusty
Zorba

5. 找出预订了一艘红色船或者绿色船的水手名
select distinct sname
from saliors
where sid in(select sid from reserves where bid in
 (select bid from boats where color='red' or color='green'))

Dustin
Horatio
Lubber

6. 找出同时预订了红色船和绿色船的水手名
select distinct sname
from saliors
where sid in(select sid from reserves where bid in
 (select bid from boats where color='red'))
intersect
select distinct sname
from saliors
where sid in(select sid from reserves where bid in
 (select bid from boats where color='green'))

Dustin
Horatio
Lubber

7. 找出预定了所有船的水手们的名字
select distinct sname
from saliors
where not exists(select * from boats where not exists
 (select * from reserves where sid=saliors.sid and bid=boats.bid))

Dustin

8. 找出预定了所有名为Interlake船的水手的名字
select distinct sname
from saliors
where not exists(select * from boats where bname='Interlake'and not exists
 (select * from reserves where sid=saliors.sid and bid=boats.bid))

Dustin
Horatio

9. 找出最老的水手的名字和年龄
select distinct sname,age
from saliors
where age>=ALL(select age from saliors)

Bob 63.5

10. 找出比等级为10的最老的水手的年龄还要大的水手的名字
select distinct sname
from saliors
where age>ALL(select age from saliors where rating='10')

Bob
Dustin
Lubber

11. 对于每个等级级别找出最年轻的水手的年龄
select min(age)
from saliors
group by rating

33.0
25.5
35.0
25.5
35.0
16.0

12. 对于至少有两个水手的等级级别，求出水手们的平均年龄
select avg(age)
from saliors
group by rating
having count(*)>=2

25.5
40.0
40.5
44.5

13. 对于至少有两个水手的等级，找出到了投票年龄（>18岁）的所有水手的平均年龄
select avg(s.age)
from saliors s
where s.age>18
group by s.rating
having 1<(select count(*)
  from saliors s2
  where s.rating=s2.rating)

44.5
40
40.5
35

14. 找出在所有的等级中水手的平均年龄是最低的那些等级
select s.rating
from saliors s
group by s.rating
having avg(s.age)<=all(select avg(s2.age)
   from saliors s2
   group by s2.rating)

10