# SQL
### 2020-09-15
### 1.7
Найдите:
* все населённые планеты
* находящиеся на расстоянии более чем 500
* впервые посещённые после 2070 года.
* В результат выведите строку, построенную по шаблону: [название планеты]: [дата первого посещения]. Пример строки результата: Ra: 2072-05-14

Решение:

SELECT format('%s: %s', P.name, P.first_visit_date) 
AS "value" 
FROM Planet P 
WHERE P.distance > 500 
      AND first_visit_date >= '2071-01-01'::DATE 
      AND P.is_inhabited;

Reference: [String functions](https://www.postgresql.org/docs/9.3/functions-string.html)

### 1.8.
Найдите:
* все планеты
* в названии которых есть буква "z"
* за которой следует (возможно, через несколько букв, но возможно и сразу) буква "e" или буква "a".
* Регистр букв не имеет значения.

Решение:

SELECT P.name 
FROM Planet P 
WHERE P.name SIMILAR TO '_*(Z|z)_*(A|a|E|e)_*';

Reference: [Pattern matching](https://www.postgresql.org/docs/9.3/functions-matching.html)
## 2020-09-24
### 2.1
Найдите:
* названия всех населённых планет
* на которые выполнялись рейсы с 2085-04-12 по 2085-10-10 включительно

Решение:

SELECT DISTINCT P.name 
FROM Planet P JOIN Flight F ON F.planet_id = P.id
WHERE P.is_inhabited 
      AND F.date 
      BETWEEN '2085-04-12'::DATE AND '2085-10-10'::DATE;

### 2.2
Найдите:
* названия всех кораблей
* класса 2
* на которых выполнял рейсы пилот 'Ийон Тихий'

Решение:

SELECT DISTINCT S.name
FROM Spacecraft S 
     JOIN Flight F ON S.id = F.spacecraft_id
     JOIN Commander C ON F.commander_id = C.id
WHERE S.class = 2 
      AND C.name = 'Ийон Тихий';

### 2.3
Найдите:
* имена пилотов
* не сделавших ни одного рейса.

Решение 1:

SELECT C.name 
FROM Commander C 
WHERE C.id NOT IN (
        SELECT F.commander_id 
        FROM Flight F);

Решение 2:

SELECT C.name 
FROM Commander C 
     LEFT JOIN Flight F ON F.commander_id = C.id 
WHERE F.id IS null;

### 2.4
Найдите:
* имена пилотов
* не сделавших ни одного рейса
* в июне 2084 года

Решение:

SELECT C.name 
FROM Commander C 
WHERE C.id NOT IN (
    SELECT F.commander_id 
    FROM Flight F 
    WHERE F.date::TEXT SIMILAR TO '2084-06-_%');

### 2.5
Найдите:
* Среди всех рейсов,
* которые совершал капитан Ийон Тихий
* рейсы на такие планеты, на которых не бывал капитан Форд Префект

Выведите:
* название планеты
* название космического корабля
* и дату рейса

Решение 1:

SELECT P.name, S.name, F.date::TEXT 
FROM Flight F 
     JOIN Commander C ON F.commander_id = C.id 
     JOIN Planet P ON F.planet_id = P.id
     JOIN Spacecraft S ON F.spacecraft_id = S.id
WHERE C.name = 'Ийон Тихий'
      AND P.id NOT IN (SELECT P.id 
                         FROM Flight F 
                              JOIN Commander C ON F.commander_id = C.id 
                              JOIN Planet P ON F.planet_id = P.id 
                           WHERE C.name = 'Форд Префект');

Решение 2:

SELECT P.name,
       S.name,
       F.date::TEXT
FROM Flight AS F
LEFT JOIN Commander C ON F.commander_id = C.id
LEFT JOIN Spacecraft S ON F.spacecraft_id = S.id
LEFT JOIN Planet P ON F.planet_id = P.id
WHERE C.name = 'Ийон Тихий'
  AND F.planet_id NOT IN
    (SELECT planet_id
     FROM Flight
     WHERE commander_id IN
         (SELECT id
          FROM Commander
          WHERE name = 'Форд Префект' ))

## 2020-10-01
### 3.1
Найдите:
* звездолёт,
* выполнивший самый хронологически ранний полёт

Решение:

SELECT S.name 
FROM Spacecraft S 
     JOIN Flight F 
          ON S.id = F.spacecraft_id 
WHERE F.date = (SELECT MIN(F.date) 
                FROM Flight F);

### 3.2
Найдите:
* Для каждого корабля,
* выполнявшего полёты,
* количество сделанных им полётов.

Решение:

SELECT S.name, 
       COUNT(*) 
FROM Flight F 
     JOIN Spacecraft S 
          ON F.spacecraft_id = S.id
GROUP BY S.id;

### 3.3
Найдите:
* Для каждого капитана
* количество выполненных им полётов.
* Даже если он полётов не выполнял.

Решение:

SELECT C.name
       COUNT(F.id) 
FROM Flight F 
     RIGHT JOIN Commander C 
     ON C.id = F.commander_id 
GROUP BY C.name;

### 3.4
Найдите:
* название
* и вместимость корабля,
* сделавшего наибольшее количество полётов.
* Если таковых несколько, они все должны быть в результате.

Решение:

WITH T AS (SELECT F.spacecraft_id,
           COUNT(F.id)
           FROM Flight F GROUP BY F.spacecraft_id)
SELECT S.name,
       S.capacity 
FROM T JOIN Spacecraft S ON S.id = T.spacecraft_id 
WHERE T.count = (SELECT MAX(T.count) FROM T);

## warmup
### 4.1
Найдите:
* названия планет, на которые в июле 2084 года
* летали капитаны,
* у которых был хотя бы один полет в июне того же года

Решение:

WITH t AS
  (SELECT DISTINCT f.commander_id
   FROM flight f
   WHERE f.date::text SIMILAR TO '2084-06-_%')
SELECT f.planet_id
FROM flight f
WHERE f.commander_id in
    (SELECT *
     FROM t)
  AND f.date::text SIMILAR TO '2084-07-_%';

## [Seminar 3](https://my.compscicenter.ru/courses/2020-autumn/1.528-data-bases/classes/6136/#video)
### At 15:00
Как отфильтровать результаты GROUP BY: HAVING

SELECT government_id,
          G.value,
          COUNT(*) AS planet_count,
          MIN (Distance) AS distance
   FROM Planet P
   JOIN Government G ON P.government_id = G.id
   GROUP BY government_id,
            G.value
   HAVING COUNT(*) >= 15;

### At 18:45
Найти:
* для каждого политического строя
* количество планет
* расстояние до ближайшей планеты
* саму ближайшую планету
* количество полетов на эту ближайшую планету

Решение:

SELECT P.Name,
       T.*,
       S.count       
FROM
  (SELECT government_id,
          G.value,
          COUNT(*) AS planet_count,
          MIN (Distance) AS distance
   FROM Planet P
   JOIN Government G ON P.government_id = G.id
   GROUP BY government_id,
            G.value) T
JOIN Planet P USING (government_id,
                     distance)
JOIN
  (SELECT planet_id,
          COUNT(*)
   FROM Flight
   GROUP BY planet_id) S ON S.planet_id = P.id;