数据库上机实验报告
Ships表 Classes表
Outcomes表 Battles表
a)找出至少有10门炮的军舰类别名和制造国家
SELECT class, country FROM Classes WHERE numGuns >= 10
b)找出所有在1918年以前下水的舰船的名字，并且把结果列名改为ShipName.
SELECT name AS ShipName FROM Ships WHERE YEAR(launched)<1918
c)找出所有在战斗中被击沉的船只和那次战斗的名字。
SELECT ship AS ShipName, battle FROM Outcomes WHERE result='sunk'
d)找出所有和它的类别名同名的船只。
SELECT name AS ShipName FROM Ships WHERE name = class
e)找出所有以“R”字符打头的船只的名字。
SELECT name AS ShipName FROM Ships WHERE name LIKE'R%'
a)找出重量超过35000吨的船只。
SELECT
FROM Ships , Classes
WHERE Ships.class = Classes.class AND Classes.displacement>35000
b)找出参加Guadalcanal战斗的船只的名字、排水量和火炮数量。
SELECT , C.displacement, C.numGuns
FROM Ships S, Outcomes O, Classes C
WHERE =O.ship AND S.class =C.class AND O.battle ='Guadalcanal'
c)列出数据库在抗洪提到的所有船只。
SELECT name ShipName FROM Ships
UNION
SELECT ship ShipName FROM Outcomes
d)找出同时拥有战列舰和巡洋舰的国家。
SELECT C1.country
FROM Classes C1, Classes C2
WHERE C1.country = C2.country AND C1.type='bb'AND C2.type='bc';
a)找出火炮数量最多的船只所属的国家。
<1> SELECT C.country FROM Classes C
WHERE numGuns IN(SELECT MAX(numGuns)FROM Classes )
<2> SELECT C.country FROM Classes C
WHERE numGuns >=ALL(SELECT numGuns FROM Classes)
b)找出至少有一艘船在战斗中被击沉的船只种类。
<1> SELECT DISTINCT C.class FROM Classes C, Ships S
WHERE C.class = S.class
AND EXISTS(SELECT ship FROM Outcomes O WHERE O.ship= AND
O.result='sunk')
<2> SELECT DISTINCT C.class FROM Classes C, Ships S
WHERE C.class = S.class
AND IN(SELECT ship FROM Outcomes O WHERE O.result='sunk')
c)找出具有16英寸口径火炮的船只的名字。
<1> SELECT FROM Ships S
WHERE S.class IN(SELECT class FROM Classes C WHERE bore=16 )
<2> SELECT FROM Ships S
WHERE EXISTS(SELECT class FROM Classes C WHERE bore =16 AND C.class
= S.class )
d)找出有Kongo类型的船只参加的战斗。
<1> SELECT O.battle FROM Outcomes O
WHERE O.ship IN(SELECT name FROM Ships S WHERE S.Class ='Kongo') <2> SELECT O.battle FROM Outcomes O
WHERE EXISTS(SELECT name FROM Ships S WHERE S.Class ='Kongo'AND = O.ship)
a)查询军舰类型的数目。
SELECT COUNT(type)AS Classes_num FROM Classes WHERE type='bb'
b)查询军舰的所有类型的平均火炮数量。
SELECT AVG(numGuns)AS Avg_Guns FROM Classes WHERE type='bb'
c)查询军舰的平均火炮数量。
SELECT AVG(C.numGuns)AS Avg_Guns
FROM Classes C INNER JOIN Ships S ON(C.class =S.class)WHERE C.type='bb'
d)查询每个类型(class)的第一舰船下水的年份。
SELECT C.class,MIN(YEAR(unched))AS First_Launched
FROM Classes C, Ships S
WHERE C.class = S.class
GROUP BY C.class
e)查询每个类型在战斗中被击沉的船的数目。
SELECT C.class,COUNT(O.ship)AS Sunk_num
FROM Classes C, Outcomes O, Ships S
WHERE C.class = S.class AND = O.ship AND O.result ='sunk' GROUP BY C.Class。