CREATE DATABASE DZ1233;

--1) Создать таблицу #students с тремя столбцами (id, name, age) и заполнить ее данными. Используя цикл FOR, вывести всех студентов,
--возраст которых больше 20 лет.
USE DZ1233
CREATE TABLE students (
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50),
age INT
);

INSERT INTO students (name, age)
VALUES
('Alice', 22),
('Bob', 19),
('Charlie', 25),
('David', 20),
('Eve', 21);

DECLARE @counter INT = 1;
WHILE @counter <= (SELECT COUNT(*) FROM students)
BEGIN
IF (SELECT age FROM students WHERE id = @counter) > 20
BEGIN
SELECT * FROM students WHERE id = @counter;
END
SET @counter = @counter + 1;
END

--2) Создать три переменные @day, @month и @year и присвоить им текущую дату. Используя оператор CASE, вывести сообщение в формате
--"День-Месяц-Год".
DECLARE @DAY INT, @MONTH INT, @YEAR INT;
SET @DAY = DAY(GETDATE());
SET @MONTH = MONTH(GETDATE());
SET @YEAR = YEAR(GETDATE());
SELECT
CASE
WHEN @DAY < 10 THEN '0' + CAST(@DAY AS VARCHAR(2))
ELSE CAST(@DAY AS VARCHAR(2))
END + '-' +
CASE
WHEN @MONTH < 10 THEN '0' + CAST(@MONTH AS VARCHAR(2))
ELSE CAST(@MONTH AS VARCHAR(2))
END + '-' +
CAST(@YEAR AS VARCHAR(4)) AS [День-Месяц-Год];

--3) Создать таблицу #tempTable с одним столбцом и заполнить ее значениями от 1 до 5. Далее вывести значения из этой таблицы.
CREATE TABLE tempTable (
VALUE INT
);
INSERT INTO tempTable (VALUE)
VALUES (1), (2), (3), (4), (5);
SELECT VALUE FROM tempTable;

--4)  Укажите сражения, в которых участвовало, по меньшей мере, три корабля одной и той же страны.
-------------------------------------------------------------------------------
CREATE TABLE Classes (
    class VARCHAR(50) NOT NULL,
    TYPE VARCHAR(2) NOT NULL,
    country VARCHAR(20) NOT NULL,
    numGuns TINYINT NULL,
    bore REAL NULL,
    displacement INT NULL,
    PRIMARY KEY (class)
);

CREATE TABLE Battles (
    name VARCHAR(20) NOT NULL,
    DATE DATETIME NOT NULL,
    PRIMARY KEY (name)
);

CREATE TABLE Ships (
    name VARCHAR(50) NOT NULL,
    class VARCHAR(50) NOT NULL,
    launched SMALLINT NULL,
    PRIMARY KEY (name),
    FOREIGN KEY (class) REFERENCES Classes(class)
);

CREATE TABLE Outcomes (
    ship VARCHAR(50) NOT NULL,
    battle VARCHAR(20) NOT NULL,
    RESULT VARCHAR(10) NOT NULL,
    PRIMARY KEY (ship, battle),
    FOREIGN KEY (battle) REFERENCES Battles(name)
);
-------------------------------------------------------------------------------
USE DZ1233;

INSERT INTO Classes (class, TYPE, country, numGuns, bore, displacement) VALUES
    ('Bismarck', 'bb', 'Germany', 8, 15, 41700),
    ('Iowa', 'bb', 'USA', 9, 16, 45000),
    ('Kongo', 'bc', 'Japan', 8, 14, 27500),
    ('North Carolina', 'bb', 'USA', 9, 16, 35000),
    ('Renown', 'bc', 'Gt. Britain', 6, 15, 32000),
    ('Revenge', 'bb', 'Gt. Britain', 8, 15, 29150),
    ('Tennessee', 'bb', 'USA', 12, 14, 32000),
    ('Yamato', 'bb', 'Japan', 9, 18, 65000),
    ('King George V', 'bb', 'Gt. Britain', 10, 14, 35000),
    ('Nelson', 'bb', 'Gt. Britain', 9, 16, 33950),
    ('Fuso', 'bb', 'Japan', 12, 14, 35000),
    ('Essex', 'cv', 'USA', 12, 14, 30000),
    ('Colorado', 'bb', 'USA', 8, 16, 32600);

INSERT INTO Battles (name, DATE) VALUES
    ('North Atlantic', 1941-05-24),
    ('Guadalcanal', 1942-11-15),
    ('North Cape', 1943-12-26),
    ('Surigao Strait', 1944-10-25),
    ('Coral Sea', 1942-05-04),
    ('Midway', 1942-06-04);

INSERT INTO Ships (name, class, launched) VALUES
    ('Bismarck', 'Bismarck', 1939),
    ('California', 'Tennessee', 1921),
    ('Duke of York', 'King George V', 1941),
    ('Fuso', 'Fuso', 1915),
    ('Hood', 'Renown', 1920),
    ('Iowa', 'Iowa', 1943),
    ('King George V', 'King George V', 1940),
    ('Kirishima', 'Kongo', 1915),
    ('Missouri', 'Iowa', 1944),
    ('Musashi', 'Yamato', 1942),
    ('New Jersey', 'Iowa', 1943),
    ('North Carolina', 'North Carolina', 1941),
    ('Prince of Wales', 'King George V', 1941),
    ('Ramillies', 'Revenge', 1917),
    ('Rodney', 'Nelson', 1927),
    ('South Dakota', 'North Carolina', 1942),
    ('Tennessee', 'Tennessee', 1920),
    ('Washington', 'North Carolina', 1941),
    ('Wisconsin', 'Iowa', 1944),
    ('Yamashiro', 'Fuso', 1917),
    ('Yorktown', 'Essex', 1937),
    ('West Virginia', 'Colorado', 1923);


INSERT INTO Outcomes (ship, battle, RESULT) VALUES
    ('Bismarck', 'North Atlantic', 'sunk'),
    ('California', 'Surigao Strait', 'ok'),
    ('Duke of York', 'North Cape', 'ok'),
    ('Fuso', 'Surigao Strait', 'sunk'),
    ('Hood', 'North Atlantic', 'sunk'),
    ('King George V', 'North Atlantic', 'ok'),
    ('Kirishima', 'Guadalcanal', 'sunk'),
    ('Prince of Wales', 'North Atlantic', 'damaged'),
    ('South Dakota', 'Guadalcanal', 'damaged'),
    ('Tennessee', 'Surigao Strait', 'ok'),
    ('Washington', 'Guadalcanal', 'ok'),
    ('West Virginia', 'Surigao Strait', 'ok'),
    ('South Dakota', 'North Atlantic', 'damaged'),
    ('Yamashiro', 'Surigao Strait', 'sunk');
-------------------------------------------------------------------------------
USE DZ1233;

SELECT Battles.name
FROM Battles
JOIN Outcomes ON Battles.name = Outcomes.battle
JOIN Ships ON Outcomes.ship = Ships.name
JOIN Classes ON Ships.class = Classes.class
GROUP BY Battles.name, Classes.country
HAVING COUNT(DISTINCT Ships.name) >= 3;

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

SELECT Classes.class
FROM Classes
LEFT JOIN Ships ON Classes.class = Ships.class
GROUP BY Classes.class
HAVING COUNT(DISTINCT Ships.name) = 1;
--6)  Найдите страны, владевшие когда-либо как обычными кораблями, так и крейсерами.
USE DZ1233;

SELECT country
FROM Classes
WHERE TYPE IN ('bb', 'bc')
GROUP BY country
HAVING COUNT(DISTINCT TYPE) = 2;
--7)  Найдите корабли, «сохранившиеся для будущих сражений»; то есть выведенные из строя в одной битве (damaged), они участвовали в
--другой.
USE DZ1233;

SELECT DISTINCT Outcomes.ship
FROM Outcomes
JOIN Outcomes AS Outcomes2 ON Outcomes.ship = Outcomes2.ship AND Outcomes.battle <> Outcomes2.battle
WHERE Outcomes.RESULT = 'damaged';
--8)  Найдите названия всех тех кораблей из базы данных, о которых можно определенно сказать, что они были спущены на воду до 1941 г.
USE DZ1233;

SELECT name
FROM Ships
WHERE launched < 1941;
--9)  Укажите названия, водоизмещение и число орудий, кораблей участвовавших в сражении при Гвадалканале (Guadalcanal).
USE DZ1233;

SELECT Ships.name, Classes.displacement, Classes.numGuns
FROM Ships
JOIN Classes ON Ships.class = Classes.class
JOIN Outcomes ON Ships.name = Outcomes.ship
WHERE Outcomes.battle = 'Guadalcanal';
--10)  Найдите названия кораблей, имеющих наибольшее число орудий среди всех кораблей такого же водоизмещения (учесть корабли из --таблицы Outcomes).
USE DZ1233;

SELECT Ships.name
FROM Ships
INNER JOIN Classes ON Ships.class = Classes.class
WHERE
  Classes.numGuns = (
    SELECT
      MAX(Classes.numGuns)
    FROM Classes
    WHERE
      Classes.displacement = Classes.displacement
  );