开篇废话
查询练习题是在ss数据库内运行的，这篇博客包含了一些子查询语句，难度比前两篇高一点，只要多做题熟练之后相信你一定都能不在话下~
ss.sql数据库传送处
感觉上传到我的资源里对没有积分的小伙伴下载很不方便=.= 最少就是一个了，没有免费的选项
暂时没有想到什么好点子，有主意的可以跟我讲一下，万分感谢！
答案是我自己做的，如果有问题欢迎指正！

如何将数据库备份导入到MySQL里面在我的博客 查询练习题（1）的文末 （我是传送 点我点我！）

老规矩 ss数据库内的三张数据表，这次我们做的是水手订船的题~
sailors 水手信息表

boats 船信息表

reserves 水手订船信息表

下面是查询操作题，建议先自己做多动手哦
1.查找定了103号船的水手
SELECT s.*,r.bid
from sailors s,reserves r
where s.sid=r.sid and r.bid=103

2.查找定了红色船水手的姓名
SELECT s.sname
from sailors s,reserves r,boats b
where s.sid=r.sid and r.bid=b.bid and b.color=‘red’

3.将年龄小于30的水手级别+1
UPDATE sailors
SET rating=rating+1
where age<30;

4.查找定了红色船而没有定绿色船的水手姓名
SELECT DISTINCT sname
from sailors s
where s.sid in(SELECT sid from reserves where bid in (SELECT bid from boats where color=‘red’))
and sid not in(SELECT sid from reserves where bid in(SELECT bid from boats where color=‘green’));

5.查找没有定过船的水手信息
SELECT *
from sailors
where sid not in (SELECT sid from reserves)

6.查找定过船而没有定过红色船的水手信息
方法1
SELECT DISTINCT s.*
from sailors s,reserves r
WHERE s.sid=r.sid and s.sid not in
(SELECT s.sid from boats b,sailors s,reserves r where b.bid=r.bid and b.color=‘red’ and r.sid=s.sid);

方法2
SELECT *
from sailors s
where EXISTS(SELECT * from reserves r where r.sid=s.sid)
and not EXISTS (SELECT * from reserves r,boats b where b.bid=r.bid and r.sid=s.sid and b.color=‘red’);

7.查找没有定过红色船的水手信息
方法1
SELECT s.*
from sailors s
where not EXISTS (SELECT * from boats b,reserves r where r.bid=b.bid and b.color=‘red’ and s.sid=r.sid)
and not EXISTS (SELECT * from reserves r where s.sid=r.sid)
方法2
SELECT *
from sailors
where sid in(SELECT sid from reserves where bid not in(SELECT bid from boats where bid=‘red’))

8.查找定过所有船的水手姓名和编号
select sailors.sname,sailors.sid
from sailors
where not exists
(selectfrom boats where not exists
(selectfrom reserves where sid=sailors.sid and bid=boats.bid));

9.查找年龄最大的水手姓名和年龄
SELECT sname,age
from sailors
where age>=all(SELECT age from sailors )

10.统计水手表中每个级别组的平均年龄和级别组
SELECT rating,avg(age)
from sailors
GROUP BY rating;

11.统计水手表中每个人数不少于2人的级别组中年满18岁水手的平均年龄和级别组
方法1
SELECT avg(age),rating
from sailors
where age>18
GROUP BY rating
HAVING COUNT(sid)>=2
方法2
select rating,avg(age)
from sailors
where rating in
(select rating from sailors group by rating having count(*)>=2)
and age>=18
group by rating;

12.统计水手表中每个级别组的人数
SELECT rating,COUNT(*)
from sailors
GROUP BY rating

13.统计水手表中人数最少的级别组及人数
SELECT rating,COUNT()
from sailors
GROUP BY rating
HAVING COUNT()<=all(SELECT COUNT(*) from sailors GROUP BY rating)

14.查找定过船而没有定过相同的船的水手姓名
SELECT sname
from sailors
where sid in
(SELECT sid
from reserves) and sid not in
(SELECT sid from reserves
GROUP BY bid
HAVING COUNT(*)>=2);

15.删除名字叫lubber的水手的定船信息.
delete
from reserves
where sid=(SELECT sid from sailors where sname=‘lubber’)