---
# System prepended metadata

title: SQL

---

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

Решение:
``` postgresql
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". 
 * Регистр букв не имеет значения.

Решение:
``` postgresql 
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 включительно

Решение:
```posgresql
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
 * на которых выполнял рейсы пилот 'Ийон Тихий'

Решение:
``` postgresql
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:
``` postgresql
SELECT C.name 
FROM Commander C 
WHERE C.id NOT IN (
        SELECT F.commander_id 
        FROM Flight F);
```
Решение 2:
``` postgresql
SELECT C.name 
FROM Commander C 
     LEFT JOIN Flight F ON F.commander_id = C.id 
WHERE F.id IS null;
```
### 2.4
Найдите: 
 * имена пилотов
 * не сделавших ни одного рейса 
 * в июне 2084 года

Решение:
``` postgresql
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:
``` postgresql
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:
``` postgresql
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
Найдите: 
 * звездолёт, 
 * выполнивший самый хронологически ранний полёт

Решение:
```postgresql
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
Найдите:
 * Для каждого корабля, 
 * выполнявшего полёты, 
 * количество сделанных им полётов.

Решение:
```postgresql
SELECT S.name, 
       COUNT(*) 
FROM Flight F 
     JOIN Spacecraft S 
          ON F.spacecraft_id = S.id
GROUP BY S.id;
```
### 3.3
Найдите:
 * Для каждого капитана 
 * количество выполненных им полётов. 
 * Даже если он полётов не выполнял.

Решение:
``` postgresql
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
Найдите:
 * название 
 * и вместимость корабля, 
 * сделавшего наибольшее количество полётов. 
 * Если таковых несколько, они все должны быть в результате.

Решение:
``` postgresql
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 года
 * летали капитаны, 
 * у которых был хотя бы один полет в июне того же года

Решение:
``` postgresql
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
``` postgresql
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
Найти:
 * для каждого политического строя
 * количество планет
 * расстояние до ближайшей планеты
 * саму ближайшую планету
 * количество полетов на эту ближайшую планету

Решение:
``` postgresql
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;
```