哈工大数据库作业二--SQL

 作业内容

在关系代数运算器 RelaX 中（https://nireas.iee.ihu.gr/relax/calc.htm）选择数据集“Database Systems The Complete Book - Excercise 2.4.3”，根据该数据集在你使用的DBMS上创建关系数据库，并用SQL完成操作。

该数据库包含 4 个关系：

Classes(class, type, country, numGuns, bore, displacement)
关系 Classes 记录了舰船级别的信息，包含以下属性：

class：舰船级别(class)的名称，通常是这个级别的第一艘船的名字。
type：该级别舰船的类型，取值为 bb 或 bc，其中 bb 表示战列舰(battleship)，bc 表示巡洋舰(battle cruiser)。
country：该级别舰船的制造国家。
numGuns：该级别舰船的主炮(main gun)数量。
bore：该级别舰船的主炮口径。
displacement：该级别舰船的排水量。
Ships(name, class, launched)
关系 Ships 记录了舰船的信息，包含以下属性：

name：舰船的名字。
class：舰船的级别。
lauched：舰船的下水日期。
Battles(name, date)
关系 Battles 记录了舰船参与的海战，包含以下属性：

name：海战的名称。
date：海战的时间。
Outcomes(ship, battle, result)
关系 Outcomes 记录了舰船在海战中取得的结果，包含以下属性：

ship：舰船的名字。
battle：海战的名称。
result：舰船在海战中取得的结果，取值为 sunk(沉没)、damaged(负伤)或 ok(完好)。
在该数据库上用SQL完成下列操作：

1.（20 分）创建视图 ShipCount，该视图包含2个属性 class和 cnt，分别表示级别名称和该级别舰船数量。
2.（20 分）在基本表和视图 ShipCount上查询舰船数量最多的级别，列出级别的全部属性。（用外连接）
3.（20 分）在基本表和视图 ShipCount上查询舰船数量最多的级别，列出级别的全部属性。（用IN）
4.（20 分）在基本表和视图 ShipCount上查询舰船数量最多的级别，列出级别的全部属性。（用>）
5.（20 分）在基本表和视图 ShipCount上查询舰船数量最多的级别，列出级别的全部属性。（用EXISTS）

注意事项：

1.这个作业不是在关系代数运算器 RelaX上进行的，关系代数运算器 RelaX上无法创建视图

2.需要下载PostgreSQL来完成作业，附windows安装教程https://www.runoob.com/postgresql/windows-install-postgresql.html

下面是汉化教程

3.打开SQL Shell，导入作业所需要数据，直接复制粘贴即可

CREATE TABLE Classes (
class CHAR(20) ,
type char(2),
country CHAR(20),
numGuns INT CHECK (numGuns > 0),
bore INT,
displacement INT
);

INSERT INTO Classes
VALUES
('Bismarck', 'bb', 'Germany', 8, 15,42000),
('Iowa', 'bb', 'USA', 9, 16,46000),
('Kongo', 'bc', 'Japan', 8, 14,32000),
('North Carolina', 'bb', 'USA', 9, 16,37000),
('Renown', 'bc', '  Gt. Britan', 6, 15,23000),
('Revenge', 'bb', ' Gt. Britan', 8, 15,29000),
('Tennessee', 'bb', 'USA', 12, 14,32000),
('Yamato', 'bb', 'Japan', 9, 18,65000)
;


CREATE TABLE Battles (
name CHAR(20),
date DATE
);

INSERT INTO Battles
VALUES
('Denmark Strait','1941-05-24'),
('Guadalcanal','1942-11-15'),
('North Cape','1943-12-26'),
('Surigao Strait','1944-10-25')
;

CREATE TABLE Outcomes (
ship CHAR(20),
battle CHAR(20),
result CHAR(20)
);

INSERT INTO Outcomes
VALUES
('Arizona', 'Pearl Harbor', 'sunk'),
('Bismarck', 'Denmark Strait', 'sunk'),
('California', 'Surigao Strait', 'ok'),
('Duke of York', 'North Cape', 'ok'),
('Fuso', 'Surigao Strait', 'sunk'),
('Hood', 'Denmark Strait', 'sunk'),
('King George V', 'Denmark Strait', 'ok'),
('Kirishima', 'Guadalcanal', 'sunk'),
('Prince of Wales', 'Denmark Strait', 'damaged'),
('Rodney', 'Denmark Strait', 'ok'),
('Scharnhorst', 'North Cape', 'sunk'),
('South Dakota', 'Guadalcanal', 'damaged'),
('Tennessee', 'Surigao Strait', 'ok'),
('Washington', 'Guadalcanal', 'ok'),
('West Virginia', 'Surigao Strait', 'ok'),
('Yamashiro', 'Surigao Strait', 'sunk')
;

CREATE TABLE Ships (
name CHAR(20),
class CHAR(20),
launched INT
);

INSERT INTO Ships
VALUES
('California','Tennessee',1921),
('Haruna','Kongo',1915),
('Hiei','Kongo',1914),
('Iowa','Iowa',1943),
('Kirishima','Kongo',1915),
('Kongo','Kongo',1913),
('Missouri','Iowa',1944),
('Musashi','Yamato',1942),
('New Jersey','Iowa',1943),
('North Carolina','North Carolina',1943),
('Ramillies','Revenge',1917),
('Renown','Renown',1916),
('Repulse','Renown',1916),
('Resolution','Revenge',1916),
('Revenge','Revenge',1916),
('Royal Oak','Revenge',1916),
('Royal Sovereign','Revenge',1916),
('Tennessee','Tennessee',1920),
('Washington','North Carolina',1941),
('Wisconsin','Iowa',1944),
('Yamato','Yamato',1941)
;

可以在pgAdmin4上查看到刚刚创建到的表

作业过程：

1.创建视图 ShipCount，该视图包含2个属性 class和 cnt，分别表示级别名称和该级别舰船数量。

在此输入SQL语句，视图即可创建成功

CREATE VIEW ShipCount AS
SELECT class, COUNT(*) AS cnt
FROM Ships
GROUP BY class;

2.在基本表和视图 ShipCount上查询舰船数量最多的级别，列出级别的全部属性。（用外连接）

SELECT c.*
FROM Classes c
LEFT JOIN ShipCount s ON c.class = s.class
WHERE s.cnt = (SELECT MAX(cnt) FROM ShipCount);

点击小闪电进行调试

3.在基本表和视图 ShipCount上查询舰船数量最多的级别，列出级别的全部属性。（用IN）

SELECT c.*
FROM Classes c
WHERE class
IN (SELECT class FROM ShipCount WHERE cnt IN (SELECT MAX(cnt) FROM ShipCount));

4. 在基本表和视图 ShipCount上查询舰船数量最多的级别，列出级别的全部属性。（用>）

SELECT c.*
FROM Classes c
WHERE c.class = 
(SELECT class FROM ShipCount WHERE cnt >= ALL (SELECT cnt FROM ShipCount));

注：个人认为需要用>=，不知道是否符合题意

5.在基本表和视图 ShipCount上查询舰船数量最多的级别，列出级别的全部属性。（用EXISTS）

SELECT c.*
FROM Classes c
WHERE EXISTS(SELECT * FROM ShipCount WHERE class=c.class AND cnt=(SELECT MAX(cnt) FROM ShipCount));

注：非最佳答案，本人自己写的，仅用于备份