---

База данных "Корабли".

Рассматривается БД кораблей, участвовавших во второй мировой войне. Имеются следующие отношения:

Название таблицы	Поля таблицы
Classes	(class, type, country, numGuns, bore, displacement)
Ships	(name, class, launched)
Battles	(name, date)
Outcomes	(ship, battle, result)

Корабли в «классах» построены по одному и тому же проекту, и классу присваивается либо имя первого корабля, построенного по данному проекту, либо названию класса дается имя проекта, которое не совпадает ни с одним из кораблей в БД. Корабль, давший название классу, называется головным.

Отношение Classes содержит
имя класса,
тип (bb для боевого (линейного) корабля или bc для боевого крейсера),
страну, в которой построен корабль,
число главных орудий,
калибр орудий (диаметр ствола орудия в дюймах) и
водоизмещение (вес в тоннах).

В отношении Ships записаны
название корабля,
имя его класса и
год спуска на воду.

В отношение Battles включены
название и
дата битвы, в которой участвовали корабли,

а в отношении Outcomes – результат участия данного корабля в битве (потоплен - sunk, поврежден - damaged или невредим - OK).

Замечания.
1) В отношение Outcomes могут входить корабли, отсутствующие в отношении Ships.
2) Потопленный корабль в последующих битвах участия не принимает.

Задание 14:

Найдите класс, имя и страну для кораблей из таблицы Ships, имеющих не менее 10 орудий.

SQL-запрос 14:

SELECT DISTINCT S.class, S.name, C.country FROM Ships AS S,
(SELECT class, country, numGuns
 FROM Classes WHERE numGuns>=10) AS C # 2-ая таблица, сделанная 'на лету'
WHERE S.class=C.class

Задание 31:

Для классов кораблей, калибр орудий которых не менее 16 дюймов, укажите класс и страну.

SQL-запрос 31:

SELECT class, country FROM Classes
WHERE bore >= 16

Задание 33:

Укажите корабли, потопленные в сражениях в Северной Атлантике (North Atlantic).
Вывод: ship.

SQL-запрос 33:

SELECT DISTINCT ship FROM outcomes
WHERE battle='North Atlantic' AND result='sunk'

Задание 34:

По Вашингтонскому международному договору от начала 1922 г. запрещалось строить линейные корабли водоизмещением более 35 тыс.тонн. Укажите корабли, нарушившие этот договор (учитывать только корабли c известным годом спуска на воду).
Вывести названия кораблей.

SQL-запрос 34:

SELECT DISTINCT name FROM classes,
    (SELECT class, name, launched FROM ships
    WHERE launched >= 1922) AS T
WHERE classes.class = T.class AND displacement > 35000

Задание 36:

Перечислите названия головных кораблей, имеющихся в базе данных (учесть корабли в Outcomes).

SQL-запрос 36:

SELECT name FROM classes, ships
WHERE classes.class = name
UNION
SELECT ship FROM classes, outcomes
WHERE classes.class = ship

Задание 37:

Найдите классы, в которые входит только один корабль из базы данных (учесть также корабли в Outcomes).

SQL-запрос 37:

SELECT CL.class FROM classes CL
LEFT JOIN (
   SELECT class, name
   FROM ships
   UNION
   SELECT ship, ship
   FROM outcomes
) AS s
ON s.class = CL.class
GROUP BY CL.class
HAVING COUNT(s.name) = 1

Задание 38:

Найдите страны, имевшие когда-либо классы обычных боевых кораблей ('bb') и имевшие когда-либо классы крейсеров ('bc').

SQL-запрос 38:

WITH T AS (
    SELECT DISTINCT country, type
    FROM classes
    WHERE type = 'bb' OR type = 'bc'
)
SELECT country FROM T
GROUP BY country
HAVING COUNT(country) = 2

Задание 39:

Найдите корабли, `сохранившиеся для будущих сражений`; т.е. выведенные из строя в одной битве (damaged), они участвовали в другой, произошедшей позже.

SQL-запрос 39:

SELECT DISTINCT ship FROM outcomes o, battles b
WHERE result='damaged'
AND
EXISTS (
    SELECT DISTINCT ship, date FROM outcomes, battles
    WHERE o.ship = outcomes.ship
    AND b.date < battles.date
    AND outcomes.result = 'OK'
)

WITH T AS (
    SELECT DISTINCT ship, result, date FROM outcomes, battles
    WHERE battle = name
)
SELECT DISTINCT T1.ship FROM T T1, T T2
WHERE T1.ship = T2.ship
AND T1.date < T2.date
AND T2.result = 'OK'

WITH T AS (
    SELECT DISTINCT ship, result, date FROM outcomes, battles
    WHERE battle = name
)
SELECT T1.ship FROM T T1, T T2
WHERE T1.ship = T2.ship
AND T1.date < T2.date
AND T1.result = 'damaged'
AND T2.result != 'damaged'

WITH T AS (
    SELECT DISTINCT ship, result, date FROM outcomes, battles
    WHERE battle = name
)
SELECT DISTINCT T2.ship FROM T T2
WHERE T2.ship IN (
    SELECT ship FROM T T1
    WHERE T1.date < T2.date AND T1.result = 'damaged'
)

WITH BT AS (
    SELECT O.ship, B.name, B.date, O.result
    FROM outcomes O
    LEFT JOIN battles B ON O.battle = B.name
)
SELECT DISTINCT A.ship FROM BT A
WHERE A.ship IN (
    SELECT ship FROM BT B
    WHERE B.date < A.date AND B.result = 'damaged'
)

Задание 42:

Найдите названия кораблей, потопленных в сражениях, и название сражения, в котором они были потоплены.

SQL-запрос 42:

SELECT ship, battle FROM outcomes
WHERE result='sunk'

Задание 43:

Укажите сражения, которые произошли в годы, не совпадающие ни с одним из годов спуска кораблей на воду.

SQL-запрос 43:

SELECT DISTINCT name FROM battles
WHERE NOT EXISTS (
    SELECT DISTINCT launched FROM ships
    WHERE YEAR(date) = launched
)

Задание 44:

Найдите названия всех кораблей в базе данных, начинающихся с буквы R.

SQL-запрос 44:

SELECT DISTINCT name FROM ships
WHERE name LIKE 'R%'
UNION
SELECT DISTINCT ship FROM outcomes
WHERE ship LIKE 'R%'

Задание 45:

Найдите названия всех кораблей в базе данных, состоящие из трех и более слов (например, King George V). Считать, что слова в названиях разделяются единичными пробелами, и нет концевых пробелов.

SQL-запрос 45:

SELECT name FROM ships
WHERE name LIKE '% % %'
UNION
SELECT ship FROM outcomes
WHERE ship LIKE '% % %'

Задание 46:

Для каждого корабля, участвовавшего в сражении при Гвадалканале (Guadalcanal), вывести название, водоизмещение и число орудий.

SQL-запрос 46:

SELECT DISTINCT S.name, displacement, numGuns
FROM classes C, ships S, outcomes O
WHERE C.class = S.class AND S.name = O.ship AND O.battle = 'Guadalcanal'

SELECT DISTINCT S.name, displacement, numGuns FROM classes C, ships S, outcomes O
WHERE C.class = S.class AND S.name = O.ship AND
    EXISTS (
        SELECT ship FROM outcomes, battles
        WHERE name = 'Guadalcanal'
    )

SELECT DISTINCT S.name, displacement, numGuns
FROM classes C, ships S, outcomes O
WHERE C.class = S.class AND S.name = O.ship
  AND O.battle = 'Guadalcanal'
UNION
SELECT DISTINCT O.ship, displacement, numGuns
FROM classes C, outcomes O
WHERE C.class = O.ship
  AND O.battle = 'Guadalcanal'

Задание 48:

Найдите классы кораблей, в которых хотя бы один корабль был потоплен в сражении.

SQL-запрос 48:

SELECT C.class FROM Classes C
LEFT JOIN Ships S ON S.class = C.class
WHERE C.class IN (
    SELECT ship FROM Outcomes
    WHERE result = 'sunk'
)
OR S.name IN (
    SELECT ship FROM Outcomes
    WHERE result = 'sunk'
)
GROUP BY C.class

WITH T AS ( 
    SELECT ship FROM Outcomes
    WHERE result = 'sunk'
)
SELECT C.class FROM Classes C
LEFT JOIN Ships S ON S.class = C.class
WHERE C.class IN (SELECT * FROM T)
OR S.name IN (SELECT * FROM T)
GROUP BY C.class

Задание 49:

Найдите названия кораблей с орудиями калибра 16 дюймов (учесть корабли из таблицы Outcomes).

SQL-запрос 49:

SELECT DISTINCT name FROM Classes C, Ships S
WHERE bore = 16
AND C.class = S.class
UNION
SELECT DISTINCT ship FROM Classes C, outcomes O
WHERE bore = 16
AND O.ship = C.class

Задание 52:

Определить названия всех кораблей из таблицы Ships, которые могут быть линейным японским кораблем, имеющим число главных орудий не менее девяти, калибр орудий менее 19 дюймов и водоизмещение не более 65 тыс.тонн.

SQL-запрос 52:

SELECT S.name FROM ships S, classes C
WHERE S.class = C.class
  AND (numGuns >= 9 OR numGuns is NULL)
  AND (bore < 19 OR bore is NULL)
  AND (displacement <= 65000 OR displacement is NULL)
  AND UPPER(country) = 'JAPAN'
  AND type = 'bb'

Задание 53:

Определите среднее число орудий для классов линейных кораблей. Получить результат с точностью до 2-х десятичных знаков.

SQL-запрос 53:

SELECT CAST(AVG(CAST(numGuns AS REAL)) AS NUMERIC(4,2))
FROM classes
WHERE type='bb'

Задание 54:

С точностью до 2-х десятичных знаков определите среднее число орудий всех линейных кораблей (учесть корабли из таблицы Outcomes).

SQL-запрос 54:

WITH T AS (
    SELECT name, numGuns, type FROM classes C, ships S
    WHERE C.class = S.class
    UNION
    SELECT ship, numGuns, type FROM classes C, outcomes O
    WHERE C.class = O.ship  
)
SELECT CAST(AVG(CAST(numGuns AS REAL)) AS NUMERIC(4,2))
FROM T
WHERE type='bb'

Задание 55:

Для каждого класса определите год, когда был спущен на воду первый корабль этого класса. Если год спуска на воду головного корабля неизвестен, определите минимальный год спуска на воду кораблей этого класса.
Вывести: класс, год.

SQL-запрос 55:

WITH T AS (
    SELECT C.class, name, launched FROM classes C, ships S
    WHERE C.class = S.class
    UNION
    SELECT class, class, NULL FROM classes C
)
SELECT class, MIN(launched) FROM T
GROUP BY class

Задание 56:

Для каждого класса определите число кораблей этого класса, потопленных в сражениях.
Вывести: класс и число потопленных кораблей.

SQL-запрос 56:

WITH T AS (
    SELECT C.class, name, result FROM classes C, ships S, outcomes O
    WHERE C.class = S.class AND S.name = O.ship
    UNION
    SELECT class, ship, result FROM classes, outcomes
    WHERE class = ship
)
SELECT T.class,
       (CASE WHEN COUNT(result) > 0
       THEN COUNT(result)
       ELSE 0 END)
FROM T
GROUP BY T.class

Задание 57:

SQL-запрос 57:

Задание 70:

Укажите сражения, в которых участвовало по меньшей мере три корабля одной и той же страны.

SQL-запрос 70:

WITH T AS ( 
    SELECT country, name FROM classes C, ships S
    WHERE C.class = S.class
)
SELECT battle FROM outcomes
INNER JOIN T
ON name = ship
GROUP BY battle
HAVING COUNT(battle) >= 3

SELECT DISTINCT O.battle FROM outcomes O
LEFT JOIN ships S ON S.name = O.ship
LEFT JOIN classes C ON O.ship = C.class OR S.class = C.class
WHERE C.country IS NOT NULL
GROUP BY C.country, O.battle
HAVING COUNT(O.ship) >= 3

Получить таблицу страна и количество строк с этими странами:

WITH T AS (
SELECT country, C.class, name FROM classes C, ships S
WHERE C.class = S.class
)
SELECT country, COUNT(country) AS CNT FROM T
GROUP BY country

WITH T AS (
    SELECT country, COUNT(country) AS CNT FROM (
        SELECT country, name FROM classes C, ships S
        WHERE C.class = S.class
    ) AS A
    GROUP BY country
)
SELECT * FROM T

Получить по конкретной стране количество с этой страной:

WITH T AS (
SELECT country, C.class, name FROM classes C, ships S
WHERE C.class = S.class
)
SELECT country, COUNT(country) AS CNT FROM T
WHERE UPPER(country) = 'JAPAN'
GROUP BY country