sql_练习题

一、创建三张表
Sailors(sid char(10),sname char(20),rating int,age int),其中sid是主关键字，sid表示水手的编号，sname表示水手的姓名，rating表示水手的级别，age表示水手的年龄。
Boats(bid char(10),bname char(20),color char(10)),其中bid表示船的编号是主关键字，bname是船的名字，color是船的颜色
Reserves(sid char(10),bid char(10),rdate date),Reserves中记录水手在哪天定了那只船，其中sid是指向Sailors的外关键字，bid是指向Boats的外关键字,(sid,bid,rdate)合起来构成Reserves的主关键字。当sailors或Boats中的相关记录被删除时要求Reservers中的记录也被联带删除。

二、请将下面的数据插入到表中
sailors 表
"22","dustin",7,45
"29","brustus",1,33
"31","lubber",8,56
"32","andy",8,26
"58","rusty",10,35
"64","horatio",7,35
"71","zorba",10,35
"74","horatio",9,35
"85","art",3,26
"86","john",1,17
"95","bob",3,64
"96","frodo",3,26
"98","tom",3,17


Boats 表
"101","A","red"
"102","B","green"
"103","C","blue"
"104","D","white"
"105","E","red"
"106","F","blue"
"107","G","green"

Reserves 表
"22","101","2010-01-08"
"22","102","2010-01-09"
"29","103","2010-01-09"
"31","102","2010-02-11"
"22","104","2010-03-08"
"22","103","2010-03-10"
"32","105","2010-03-11"
"32","106","2010-03-18"
"32","102","2010-03-19"
"58","104","2010-03-20"
"64","105","2010-03-20"
"95","101","2010-04-02"
"85","102","2010-04-05"
"22","101","2010-04-07"
"22","105","2010-05-01"
"22","106","2010-06-18"
"22","107","2010-07-09"
"31","106","2010-08-06"
"32","105","2010-08-06"
"29","104","2010-08-07"
"64","103","2010-09-05"
"58","102","2010-09-09"
"64","104","2010-11-03"
"64","105","2010-11-04"
"31","106","2010-12-06"

三。完成下面的SQL操作
1.查找定了103号船的水手
2.查找定了红色船水手的姓名
3.将年龄小于30的水手级别+1
4.查找定了红色船而没有定绿色船的水手姓名
5.查找没有定过船的水手信息
6.查找定过船而没有定过红色船的水手信息
7.查找没有定过红色船的水手信息
8.查找定过所有船的水手姓名和编号
9.查找年龄最大的水手姓名和年龄
10.统计水手表中每个级别组的平均年龄和级别组
11.统计水手表中每个人数不少于2人的级别组中年满18岁水手的平均年龄和级别组
12.统计水手表中每个级别组的人数
13.统计水手表中人数最少的级别组及人数
14.查找定过船而没有定过相同的船的水手姓名
15.删除名字叫lubber的水手的定船信息.
16.请创建一个级别小于6的水手视图
17.请创建下面的表
students(sid char(3),sname char(8),age int, grade char(4)),要求sid为关键字，age>=15和age<=40.
请插入以下记录说明完成情况
"001","abc",18,"2001"
"002","bcd",19,"2002"
"003","def",13,"2004"
"004","kdz",19,"2007"
"005","ddd",47,"2008"