某货运站的数据库,其中存放以下四个表:
货车(货车编号,车牌,出厂日期,司机,最大运输量)
司机(司机编号,姓名,联系方式,驾驶证日期)
货物(货物编号,货物名称,产地,单价,货物总量)
运输记录(运输单编号,货车编号,货物编号,运输量,交易类型,运输日期)
试用SQL语言完成下列功能:
1建表,在定义中要求声明:(5分)
(1)每个表的主外码;
(2) 时间日期的数据类型是datetime;
(3) 运输量,货物总量,各种数量大于等于0,驾车年龄在0和40年之间,外码不能为空。create table 司机(司机编号char(3) primary key,姓名char(8),
联系方式char(3),驾驶证日期datetime )
create table 货车(货车编号char(3) primary key, 车牌varchar(10),
出厂日期datetime, 司机char(3),
最大运输量int check(最大运输量>=0),
Foreign key(司机) references 司机(司机编号))
create table 货物(货物编号char(3)primary key,货物名称varchar(10),
产地varchar(10),单价numeric(8,2),
货物总量int)
create table 运输记录(运输单编号int primary key, 货车编号char(3),
货物编号char(3),运输量int check(运输量>=0),
交易类型varchar(10),运输日期datetime,
foreign key(货车编号) references 货车(货车编号),
foreign key(货物编号) references 货物(货物编号))
2往表中插入数据(5分)
货车(
001,东风,2008-7-2,101,50000
002,解放,2004-5-4,102,70000
003,五菱,2003-2-6,103,150000
004,五十铃,2009-9-10,105,75000
005,长安,2010-8-2,106,65000
006,东风,2011-1-26,104,80000
)
司机(
101,张三,139,2001-1-5
102,李四,152,1998-2-15
103,唐二,187,1999-6-23
104,江五,133,2002-4-14
105,牛六,189,2005-5-12
106,郭七,135,2009-7-6
)
货物(
201,苹果,山东,4,300000
202,香蕉,海南,4,400000
203,桔子,江西,5,550000
204,蜜柚,广东,4.5,450000
)
运输记录(
1,001,201,40000,运入,2009-1-9
2,002,201,60000,运出,2009-1-10
3,003,204,100000,运入,2009-2-1
4,001,204,45000,运出,2009-4-2
5,004,202,72000,运出,2009-4-2
6,003,202,120000,运出,2009-4-2
7,002,203,55000,运入,2009-4-5
8,004,202,65000,运出,2009-4-5
9,005,204,40000,运入,2009-4-8
10,006,203,70000,运出,2009-5-1
11,005,204,50000,运入,2009-5-1
12,006,203,75000,运出,2009-5-8
)
3.用SQL语句完成下列查询:(10分*3=30分)
(1)找出哪种车牌的汽车最多。
select top 1 车牌,count(*)
from 货车group by 车牌
order by 2 desc
(2)找出同一辆货车两次运输间隔时间第二长的货车编号、车主及车主联系方式。每个车两次运输间隔最大的时间
select X.货车编号,max(datediff(day,Y.运输日期,X.运输日期))
from 运输记录X,运输记录Y where X.货车编号=Y.货车编号
group by X.货车编号
两次运输间隔时间第一长的货车编号
select X.货车编号,max(datediff(day,Y.运输日期,X.运输日期))
from 运输记录X,运输记录Y where X.货车编号=Y.货车编号
group by X.货车编号
having max(datediff(day,Y.运输日期,X.运输日期)) >=all
( select max(datediff(day,Y.运输日期,X.运输日期))
from 运输记录X,运输记录Y where X.货车编号=Y.货车编号
group by X.货车编号)
两次运输间隔时间第二长的货车编号:
select top 1 X.货车编号
from 运输记录X,运输记录Y where X.货车编号=Y.货车编号
group by X.货车编号
having X.货车编号not in
( select X.货车编号
from 运输记录X,运输记录Y where X.货车编号=Y.货车编号
group by X.货车编号
having max(datediff(day,Y.运输日期,X.运输日期)) >=all
( select max(datediff(day,Y.运输日期,X.运输日期))
from 运输记录X,运输记录Y where X.货车编号=Y.货车编号
group by X.货车编号))
order by max(datediff(day,Y.运输日期,X.运输日期)) desc
最后答案:
select 货车编号,司机编号,姓名,联系方式
from 货车, 司机where 货车.司机=司机.司机编号and 货车编号in (select top 1 X.货车编号
from 运输记录X,运输记录Y where X.货车编号=Y.货车编号
group by X.货车编号
having X.货车编号not in
( select X.货车编号
from 运输记录X,运输记录Y where X.货车编号=Y.货车编号
group by X.货车编号
having max(datediff(day,Y.运输日期,X.运输日期)) >=all
( select max(datediff(day,Y.运输日期,X.运输日期))
from 运输记录X,运输记录Y where X.货车编号=Y.货车编号
group by X.货车编号))
order by max(datediff(day,Y.运输日期,X.运输日期)) desc)
(3)找出2009年货运站最忙的月份及该月里的货物运入总量及货物运出总量。select month(运输日期) 最忙月份,sum(运输量) 总运输量
from 运输记录where year(运输日期)=2009
group by month(运输日期)
having sum(运输量)>=all(select sum(运输量)
from 运输记录where year(运输日期)=2009
group by month(运输日期))
select month(运输日期) 最忙月份,交易类型,sum(运输量) 运输量
from 运输记录where year(运输日期)=2009 and month(运输日期)= (select month(运输日期)
from 运输记录where year(运输日期)=2009
group by month(运输日期)
having sum(运输量)>=all(select sum(运输量)
from 运输记录where year(运输日期)=2009
group by month(运输日期)))
group by month(运输日期),交易类型
4.列出2009年货运站最忙月份里进出量最多的货物、货物进出总量及货物单价。(15分) select top 1 货物.货物编号,单价,sum(运输量) 运输量
from 运输记录,货物
where 运输记录.货物编号=货物.货物编号and year(运输日期)=2009 and month(运输日期)=
(select month(运输日期)
from 运输记录where year(运输日期)=2009
group by month(运输日期)
having sum(运输量)>=all(select sum(运输量)
from 运输记录where year(运输日期)=2009
group by month(运输日期)))
group by 货物.货物编号,单价
order by 3 desc
5.使用游标完成以下操作:
(1)修改货物表,调整总量超过350000的货物单价为原来的90%。(10分)
declare xgdj cursor for
select 货物编号,单价from 货物where 货物总量>=350000
for update of 单价
open xgdj
fetch xgdj
while @@fetch_status=0
begin
update 货物set 单价=单价*0.9
where current of xgdj
fetch xgdj
end
close xgdj
deallocate xgdj
(2)找出运输过所有货物的司机。(15分)
declare @SJBH char(3)
declare @XM char(8)
declare @lxfs char(3)
declare @jszrq datetime
declare SJ cursor for
select * from 司机where not exists
(select * from 货物where not exists
(select * from 运输记录,货车
where 运输记录.货车编号=货车.货车编号
and 货物编号=货物.货物编号and 司机=司机.司机编号))
open SJ
fetch FROM SJ into @SJBH,@XM,@lxfs,@jszrq
WHILE @@FETCH_STATUS=0
begin
print @SJBH+@XM+@lxfs+@jszrq
fetch from SJ into @SJBH,@XM,@lxfs,@jszrq
end
close SJ
deallocate SJ
6.货运站来了一辆车牌货车,装载了40000的苹果,货车最大承载量为60000,司机名叫老九,驾驶证日期为2006-6-1,请根据上述情况更新数据库。(20分)
insert into 司机(司机编号,姓名,驾驶证日期) values('107','赵九','006-6-1')
insert into 货车(货车编号,司机,最大运输量) values ('007','107',60000)
insert into 运输记录(运输单编号,货车编号,货物编号,运输量,交易类型)
values (13,'007','201',40000,'运入')
909491009@qq。Com张毓庆