数据库上机作业答案
--10级《数据库系统》上机作业02—单号
--要求：实现对关系数据库product中相关数据的简单查询操作。
--product(maker,model,type)
--PC(model,speed,ram,hd,rd,price)
--Laptop(model,speed,ram,hd,screen,price)
--Printer(model,color,type,price)
--实验4：SQL的简单查询
--a)找出所有价格低于$1000的PC型号、速度和硬盘大小
select model,speed,hd
from pc
where price<1000
--b)要求同（a），但要将列speed重命名为gigahertz，并将列hd重命名为gigabytes
select model,speed as gigahertz,hd as gigabytes
from pc
where price<1000
--c)找出所有打印机制造厂商。
select maker
from product
where type='printer'
--d)找出价格高于$1500的笔记本电脑的型号、内存大小和屏幕尺寸
select model,ram,screen
from laptop
where price>1500
--e)找出关系Printer中所有彩色打印机元组。注意属性color是一个布尔类型。
select *
from printer
where color='true'
--f)找出速度为3.2且价格低于$2000的PC的型号和硬盘尺寸。
select model,hd
from pc
where speed=3.2 and price<2000
--10级《数据库系统》上机作业02—双号
--要求：实现对关系数据库ship中相关数据的简单查询操作。
--Classes(class,type,country,numGuns,bore,displacement)
--Ships(name,class,launched)
--Battle(name,date)
--Outcomes(ship,battle,result)
--实验4：SQL的简单查询
--a)找出至少有10门炮的军舰类别名和制造国家。
select class,country
from classes
where numguns>=10
--b)找出在1918年以前下水的舰船的名字，并且把结果列名改为ShipName
select name
from ships
where launched<1918
--c)找出所有在战斗中被击沉的船只和那次战斗的名字
select battle
from outcomes
where result='sunk'
--d)找出所有和它的类别名同名字的船只
select name
from ships
where name=class
--e)找出所有以“R”字符打头的船只的名字。
select name
from ships
where name like 'R%'
--!f)找出所有包括三个或三个以上单词的船只名字（如King George V）
select name
from ships
where name like '[A-Z]%[a-z][ ][A-Z]%[a-z][ ][A-Z]%'
--10级《数据库系统》上机作业03—单号
--要求：实现对关系数据库product中相关数据的连接查询操作。
--product(maker,model,type)
--PC(model,speed,ram,hd,rd,price)
--Laptop(model,speed,ram,hd,screen,price)
--Printer(model,color,type,price)
--实验5：SQL的连接查询
--a)查询硬盘容量至少30GB的笔记本电脑制造商及该电脑的速度。
select maker,speed
from product,laptop
where product.model=laptop.model
and hd>=30
select maker,speed
from product inner join laptop on product.model=laptop.model
where hd>=30
--b)查询制造商B生产的任意类型的所有产品的型号和价格。
select product.model,
price from product,pc where product.model=pc.model and maker='B'
un
ion
select product.model,price from product,laptop where product.model=laptop.model and maker='B'
union
select product.model,price from product,printer where product.model=printer.model and maker='B'
--c)查询卖笔记本电脑不卖PC的厂商。
select maker from product where type='laptop'
except
select maker from product where type='pc'
--!d)查询出现在两种或两种以上PC中硬盘的大小。
select pc1.model,pc1.hd,pc2.hd,pc2.model
from pc as pc1,pc as pc2
where pc1.hd=pc2.hd
and pc1.model<>pc2.model
--!e)查询每对具有相同速度和RAM容量的PC型号。每一对只能列出一次，例如若（i,j）已被列出，则（j,i）就不能再被列出。
select pc1.model,pc1.speed,pc1.ram,pc2.ram,pc2.speed,pc2.model
from pc as pc1,pc as pc2
where pc1.speed=pc2.speed
and pc1.ram=pc2.ram
and pc1.model>pc2.model
--!!f)查询生产至少两种速度至少3.0的电脑（PC或笔记本电脑）的厂商。
--步1：找出速度至少3.0电脑（PC或笔记本电脑）的型号、速度和厂商
create view R as
select pc.model,speed,maker from pc,product where pc.model=product.model and speed>=3.0
union
select laptop.model,speed,maker from laptop,product where laptop.model=product.model and speed>=3.0
--步2：找出生产至少两种上述电脑的厂商
--select r1.model,r1.maker,r2.maker,r2.model
select R1.maker
from R as R1,R as R2
where R1.maker=R2.maker
and R1.model<R2.model
--实验6：SQL的复杂查询
--a)找出速度在3.0以上的PC制造商。
select distinct maker
from product
where model in(
select model
from pc
where speed>3.0
)
select distinct maker
from product
where exists(
select *
from pc
where speed>3.0
and model=product.model
)
--b)找出价格最高的打印机。
select *
from printer
where price>=all(
select price
from printer
)
--c)找出速度比任何一台PC都慢的笔记本电脑。
select *
from laptop
where speed<=all(
select speed
from pc
)
--!d)找出价格最高的产品（PC、笔记本电脑或打印机）的型号。
select model,price
from ((select model,price from pc) union (select model,price from laptop)union(select model,price from printer)) as modelprice
where price>=all(select price from
((select model,price from pc) union (select model,price from laptop)union(select model,price from printer))as modelprice)
--!e)找出价格最低的彩色打印机的制造商。
select maker from product where model in
(select model from printer where color='true'
and price<=all(
select price from printer
)
)
--!!f)找出在PC中具有最快速度同时且RAM容量最小的制造商。
select maker from product where model in(
select model from pc where ram<=all(select ram from pc)
and speed>=all
(select speed from pc
where ram<=all
(select ram from pc)))
--10级《数据库系统》上机作业03—
双号
--要求：实现对关系数据库ship中相关数据的简单查询操作。
--Classes(class,type,country,numGuns,bore,displacement)
--Ships(name,class,launched)
--Battles(name,date)
--Outcomes(ship,battle,result)
--实验5：SQL的连接查询
--a)找出重量超过35000吨的船只。
select name,displacement
from classes,ships
where classes.class=ships.class
and displacement>35000
--b)找出参加Guadacanal战斗船只的名字、排水量和火炮数量
select name,displacement,numGuns
from classes,ships,outcomes
where classes.class=ships.class
and https://www.wendangku.net/doc/ea3211591.html,=outcomes.ship
and battle='Guadalcanal'
--c)找出数据库中提到的所有船只（切记，并非所有船只都出现在ships关系中）。
(select name from ships)
union
(select ship from outcomes)
--!d)找出同时拥有战列舰和巡洋舰的国家。
select c1.type,c1.country,c2.country,c2.type
from classes as c1,classes as c2
where c1.country=c2.country
and c1.type='bb' and c2.type='bc'
--!e)找出曾在某次战斗中被击毁但后来又在其他战斗中出现的船只。
create view bo(ship,batttle,result,name,startdate,enddate)
as
select ship,batttle,result,name,startdate,enddate
from outcomes,battles
where outcomes.battle=https://www.wendangku.net/doc/ea3211591.html,
select ship
from bo as bo1,bo as bo2
where bo1.ship=bo2.ship
and bo1.battle<>bo2.battle
and bo1.enddate<bo2.startdate
and bo1.result=’damaged’
--!f)找出来自同一个国家的至少三艘船只参战的战斗。
create view cs
as
select battle,name,class
from ships,classes,outcomes
where ships.class=classes.class
and https://www.wendangku.net/doc/ea3211591.html,=outcomes.ship
select cs1.battle
from cs as cs1,cs as cs2,cs as cs3
where cs1.country=cs2.country
and cs1.country=cs3.country
and cs1.battel=cs2.battle
and cs1.battle=cs3.battle
and https://www.wendangku.net/doc/ea3211591.html,<>https://www.wendangku.net/doc/ea3211591.html,
and https://www.wendangku.net/doc/ea3211591.html,<>https://www.wendangku.net/doc/ea3211591.html,<>https://www.wendangku.net/doc/ea3211591.html,<>https://www.wendangku.net/doc/ea3211591.html,
--实验6：SQL的复杂查询
--a)找出火炮数量最多船只所属的国家。
select country
from classes
where numGuns>=all(
select numGuns
from classes)
--b)找出至少有一膄在战役中被击沉的船只种类。
select class
from ships
where name=any(
select ship
from outcomes
where result='sunk')
--c)找出具有16英寸口径火炮的船只名字。
select name
from ships
where class in(
select class
from classes
where bore=16)
--!d)找出Kongo类型船只参加的战役。
select battle
from outcomes
where ship in(
select name
from ships
where class='Kongo')
--!e)找出具有相同口径火炮船只中火炮数量最多的船只名字。
select name
from ships
where class in(
select class
from classes
where numGuns>=all(
select numGuns
from classes as
c
where bore=c.bore))
--10级《数据库系统》上机作业04—单号
--要求：实现对关系数据库product中相关数据的连接查询操作。
--product(maker,model,type)
--PC(model,speed,ram,hd,rd,price)
--Laptop(model,speed,ram,hd,screen,price)
--Printer(model,color,type,price)
--实验7：表中数据的聚集操作
--a)查询PC的平均速度。
select avg(speed) as avgspeed
from pc
--b)查询价格在$1000以上笔记本电脑的平均速度。
select avg(speed) as avgspeed
from laptop
where price>1000
--c)查询厂商“A”生产PC的平均价格。
select avg(price) as avgprice
from product,pc
where product.model=pc.model
and maker='A'
--!d)查询厂商“D”生产的PC和笔记本电脑的平均价格。
select avg(price) as avgprice
from(
(select price from product,pc where product.model=pc.model and maker='D')
union
(select price from product,laptop where product.model=laptop.model and maker='D')
) as sprice
--e)查询不同速度PC的平均价格。
select speed,avg(price) as avgprice
from pc
group by speed
--!f)查询各个厂商生产笔记本电脑的平均屏幕尺寸。
select maker,avg(screen) as avgscreen
from product,laptop
where product.model=laptop.model
group by maker
--!g)查询至少生产3种不同型号PC的制造商。
select maker,count(pc.model) as modelcount
from product,pc
where product.model=pc.model
group by maker
having count(pc.model)>=3
--!h)查询每个厂商生产PC的最高价格。
select maker,max(price) as maxprice
from product,pc
where product.model=pc.model
group by maker
--!i)查询每种速度高于2.0PC的平均价格。
select speed,avg(price) as avgprice
from pc
where speed>2.0
group by speed
--!!j)查询所有生产打印机的厂商生产PC硬盘容量的平均大小
select maker,avg(hd) as avghd
from product,pc
where product.model=pc.model
and maker in(
select maker
from product
where type=’printer’)
group by maker
--实验8：数据表中的视图操作
--a)定义销售PC机或者是手提电脑的视图AAA，包括电脑的型号，电脑的速度，销售的厂商；
create view AAA(model,speed,maker)
as
select pc.model,speed,maker
from pc,product
where pc.model=product.model
union
select laptop.model,speed,maker
from laptop,product
where laptop.model=product.model
--b)利用a)定义的视图找出生产最高速度的计算机(PC或者是手提电脑)的厂商；
select maker
from AAA
where speed=(
select max(speed)
from AAA)
--10级《数据库系统》上机作业03—双号
--要求：实现对关系数据库ship中相关数据的简单查询操作。
--Classes(class,type,country,numGuns,bore,displacement)
--Ships(name,class,launched)
--Battles(name,date)
--Outcomes(ship,battle,result)
--实验7：表中数据的聚集操作
--a)查询战舰
类型的数量。
select type,count(class) as countclass
from classes
group by
type
--b)查询不同类型战舰拥有的平均火炮数量。
select type,avg(numGuns) as avg_numGuns
from classes
group by type
--!c)查询战舰的平均火炮数量。注意b)和c)的不同在于：在计算平均值时，是使用战舰的数目还是战舰类型的数目
select avg(numGuns)
from classes
where class in(
select class from ships)
group by name
--!d)查询每个类型（class）第一膄船下水的年份。
select class,name,launched
from ships
where name in(
select min(name)
from ships
group by class)
--!e)查询每个类型在战役中被击沉舰船的数目。
select class,count(name) as countname
from ships,outcomes
where https://www.wendangku.net/doc/ea3211591.html,=outcomes.ship
and result='sunk'
group by class
--!!f)查询至少有3艘舰船的类型在战斗中被击沉舰船的数目。
select class,count(name) as countname
from ships,outcomes
where https://www.wendangku.net/doc/ea3211591.html,=outcomes.ship
and result='sunk'
group by class
having count(name)>=3
--!!g)舰船火炮使用炮弹的重量（以磅为单位）大约是火炮的口径（以英寸为单位）的一半。查询各个国家战舰炮弹重量的平均值。
select country,sum(numGuns*countname*bore*bore*bore/2)/sum(numGuns*countname) as avg_Gunsweight
from classes,(select class,count(name) as countname from ships group by class) as s
where classes.class=s.class
group by country
order by country
--实验8：数据表中的视图操作
--a)定义一个视图，它包括所有英国船只的类（class）、类别（type）、火炮数量、口径、排水量和下水年份。
create view britainship
as
select class,type,numguns,bore,displacement,launched
from classes,ships
where classes.class=ships.class
and country='Gt.Britain'
--b)利用a)定义的视图写一个查询，找出在1919年下水的英国战舰火炮数量及排水量；
select numguns,displacement
from britainship
where launched=1919