Семинар 2. Соединения, ключи, запросы с условиями

Теоретическая справка

2.1 Соединения

Все операции по соединению таблиц деляться на 3 основных группы:

• CROSS JOIN - декартово произведение 2 таблиц
• INNER JOIN - соединение 2 таблиц по условию. В результирующую выборку попадут только те записи, которые удовлетворяют условию соединения
• OUTER JOIN - соединение 2 таблиц по условию. В результирующую выборку могут попасть записи, которые не удовлетворяют условию соединения:
  • LEFT (OUTER) JOIN - все строки “левой” таблицы попадают в итоговую выборку
  • RIGHT (OUTER) JOIN - все строки “правой” таблицы попадают в итоговую выборку
  • FULL (OUTER) JOIN - все строки обеих таблиц попадают в итоговую выборку

Рассмотрим каждую из групп отдельно:

CROSS JOIN

Объединение каждой строки левой таблицы со всеми строками правой таблицы. Этот вид соединения иногда называют декартовым произведением.

Пример:

INNER JOIN

Из строк левой таблицы и правой таблицы объединяются и возвращаются только те строки, по которым выполняются условия соединения. Если рассматривать алгоритм выполнения, то он имеет следующий вид:

• Каждая строка одной таблицы сопоставляется с каждой строкой второй таблицы
• Для полученной соединённой строки проверяется условие соединения
• Если условие истинно, в набор результатов добавляется соединённая строка

Пример:

OUTER JOIN

Выполнение зависит от специфики, например:

• LEFT JOIN: Возвращаются все строки левой_таблицы (ключевое слово LEFT). Данными правой таблицы дополняются только те строки левой таблицы, для которых выполняются условия соединения. Для недостающих данных вместо строк правой таблицы вставляются NULL-значения.
• RIGHT JOIN: Возвращаются все строки правой таблицы (ключевое слово RIGHT). Данными левой таблицы дополняются только те строки правой таблицы, для которых выполняются условия соединения. Для недостающих данных вместо строк левой таблицы вставляются NULL-значения.
• FULL JOIN: Возвращаются все строки левой таблицы и правой таблицы. Если для строк левой таблицы и правой таблицы выполняются условия соединения, то они объединяются в одну строку. Для строк, для которых не выполняются условия соединения, NULL-значения вставляются на место левой таблицы, либо на место правой таблицы, в зависимости от того данных какой таблицы в строке не имеется.

Примеры:

• LEFT JOIN:

• RIGHT JOIN:

• FULL JOIN:

В случае, если join_condition входят столбцы с одинаковыми именами, можно использовать сокращённый синтаксис с USING.

Пример аналогичных join_condition:

ON left_table.a = right_table.a AND left_table.b = right_table.b

USING (a, b)

Ещё более компактный вариант указания join_condition – указание NATURAL перед join_type.

SELECT select_list 
FROM T1 NATURAL JOIN T2;

Принцип работы NATURAL:

• Аналогичен USING с указанием всех одноимённых столбцов
• Если одноимённых столбцов нет, то аналогичен ON TRUE

2.2 Ключи

Потенциальный ключ – подмножество атрибутов отношения, удовлетворяющее требованиям уникальности и минимальности:

• Уникальность: нет и не может быть двух кортежей данного отношения, в которых значения этого подмножества атрибутов совпадают;
• Минимальность: в составе потенциального ключа отсутствует меньшее подмножество атрибутов, удовлетворяющее условию уникальности;
• Типы:
  • простой (состоит ровно из одного атрибута)
  • составной (состоит из двух и более атрибутов)

Пример нескольких потенциальных составных ключей:

CREATE TABLE flight_schedule (
  departure timestampt,
  gate text,
  pilot text

  UNIQUE(departure, gate),
  UNIQUE(departure, pilot)
);

Первичный ключ (Primary key, PK) — любой из потенциальных ключей, выбранный в качестве основного; выбирают в качестве первичного такой ключ, который занимает меньше места при хранении и который не утратит свою уникальность со временем. (потенциальный ключ существует всегда, даже если включает в себя все атрибуты отношения) Альтернативными ключами называются потенциальные ключи, не выбранные в качестве первичного.

Типы:

• естественный (основанный на уже существующем поле)
• интеллектуальный (основанный на естественном ключе путем добавления дополнительного поля)

Суррогатный ключ — это дополнительное служебное поле, которое добавляется к уже имеющимся информационным полям таблицы, единственное предназначение которого – служить первичным ключом. (значение генерируется искусственно).

Пусть R₁ и R₂ – две переменные отношения, не обязательно различные. Внешним ключом FK (Foreign key) в R₂ является подмножество атрибутов переменной R2 такое, что выполняются следующие требования:

• В переменной отношения R₁ имеется потенциальный ключ PK такой, что PK и FK совпадают с точностью до переименования атрибутов (FK из R₂ является PK из R₁)

• В любой момент времени каждое значение FK в текущем значении R₂ идентично значению PK в некотором кортеже в текущем значении R₁. Иными словами, в любой момент времени множество всех значений FK в R₂ является подмножеством значений PK в R₁.

• Родительское (главное/целевое) отношение — отношение R₁, содержащее потенциальный ключ.

• Дочернее (подчиненное) отношение — отношение R₂, содержащее в себе ссылку на сущность, в которой находятся нужные нам атрибуты. (содержащее в себе внешний ключ)

Создание ключей

PRIMARY KEY

CREATE TABLE PERSON (
    ID         INTEGER      PRIMARY KEY,
    LAST_NAME  VARCHAR(255) NOT NULL,
    FIRST_NAME VARCHAR(255) NOT NULL,
    AGE        INTEGER
);

ALTER TABLE PERSON ADD PRIMARY KEY (ID);

------------------------------------------

CREATE TABLE PERSON (
    ID         INTEGER,
    LAST_NAME  VARCHAR(255),
    FIRST_NAME VARCHAR(255) NOT NULL,
    AGE        INTEGER,
    CONSTRAINT PK_Person PRIMARY KEY (ID, LAST_NAME)
);

ALTER TABLE PERSON
ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LAST_NAME);

ALTER TABLE PERSON
DROP CONSTRAINT PK_Person;

FOREIGN KEY

CREATE TABLE ORDER (
    ORDER_ID     INTEGER,
    ORDER_NUMBER INTEGER NOT NULL,
    PERSON_ID    INTEGER,
    
    PRIMARY KEY (ORDER_ID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PERSON_ID) REFERENCES PERSON(PERSON_ID)
);

ALTER TABLE ORDER ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PERSON_ID) REFERENCES PERSON(PERSON_ID);

ALTER TABLE ORDER DROP CONSTRAINT FK_PersonOrder;

------------------------------------------

CREATE TABLE ORDER (
    ORDER_ID     INTEGER PRIMARY KEY,
    ORDER_NUMBER INTEGER NOT NULL,
    PERSON_ID    INTEGER REFERENCES PERSON(PERSON_ID)
);

ALTER TABLE ORDER
ADD FOREIGN KEY (PERSON_ID) REFERENCES PERSON(PERSON_ID);

2.3 Запросы с условиями

Напомним как выглядит синтаксис запроса:

SELECT
  [ALL | DISTINCT [ON (expression [, ...] )] ]
  [* | expression [AS output_name] [, ...] ]
[FROM from_item [, ...] ]
[WHERE condition]
[GROUP BY grouping_element [, ...]]
[HAVING condition]
[ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}]
     [, ...]]
[LIMIT {count | ALL}]
[OFFSET start [ROW | ROWS]]
[FETCH {FIRST | NEXT} [count] {ROW | ROWS} ONLY]

2.3.1 Функции принадлежности

• IN - принадлежность определенному набору значений: X IN (a1, a2, ..., an) ≣ X = a₁ or X = a₂ or … or X = a_n
• BETWEEN - принадлежность определенному интервалу значений: X BETWEEN A AND B ≣ (X >= A and X <= B) or (X <= A and X >= B)
• LIKE - удовлетворение текста паттерну: X LIKE '0%abc_0', где _ - ровно 1 символ, а % - любая последовательность символов (в том числе нулевой длины).

2.3.2 Функции ветвления

• IF ... THEN ... [ELSIF ... THEN ... ELSE ...] END IF - ветвления, пример:

SELECT
    IF number = 0 THEN
        'zero'
    ELSIF number > 0 THEN
        'positive'
    ELSIF number < 0 THEN
        'negative'
    ELSE
        'NULL'
    END IF AS number_class
FROM
    numbers

• CASE [...] WHEN ... THEN ... ELSE ... END CASE - еще один аналог ветвлений, пример:

SELECT
    CASE 
        WHEN number = 0 THEN
            'zero'
        WHEN number > 0 THEN
            'positive'
        WHEN number < 0 THEN
            'negative'
        ELSE
            'NULL'
    END CASE AS number_class
FROM
    numbers

2.3.3 Функция DISTINCT

• DISTINCT ON - исключает строки, совпадающие по всем указанным выражениям, пример:

-- вывести кол-во уникальных отделов
SELECT
    count(DISTINCT ON department_nm)
FROM
    salary;

Примеры:

• WHERE:

SELECT name, salary
FROM employees
WHERE department = 'IT' AND salary > 65000;

• Использование NULL в условиях:

SELECT name, end_date
FROM projects
WHERE end_date IS NULL;

• DISTINCT:

SELECT DISTINCT customer_id, product
FROM orders;

• IN:

SELECT department_name
FROM departments
WHERE department_id IN (1, 3);

• LIKE:

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'A%';

• CASE:

SELECT sale_id, 
       CASE 
           WHEN amount < 200 THEN 'Low'
           WHEN amount BETWEEN 200 AND 300 THEN 'Medium'
           ELSE 'High'
       END AS category
FROM sales;

• Сортировка с NULLS FIRST:

SELECT product_id, rating
FROM reviews
ORDER BY rating DESC NULLS FIRST;

Практическая часть

Справка по практической части

Часто для удобной работы с данными можно использовать готовые датасеты различных форматов. Чтобы загрузить в БД датасет из файлов расширения .csv можно воспользоваться внутренним функционалом PostgreSQL.

Пример:

COPY table_name (column1, column2, column3)
FROM '/path/to/your/file.csv'
DELIMITER ','
CSV HEADER;

Где:

• table_name: Имя таблицы, в которую загружаются данные.
• /path/to/your/file.csv: Полный путь к файлу на сервере PostgreSQL.
• DELIMITER: Разделитель (по умолчанию — запятая).
• CSV HEADER: Указывает, что первая строка содержит заголовки.

Задания

1. Создать схему sem_2:

2. Создать следующие таблицы. Какие первичные и внешние ключи нужны здесь? Создайте их. Колонку id заведите типа serial (документация). Как называется такой вид ключа?

movies
id (идентификатор фильма)
title (название фильма)
release_year (год выпуска)
duration_min (длительность в мин)
rating (рейтинг)
director (режиссёр)

actors
id (идентификатор актёра)
first_nm (имя актёра)
last_nm (фамилия актёра)

cast
movie_id (идентификатор фильма)
actor_id (идентификатор актёра)
character_nm (персонаж)

genres
movie_id (идентификатор фильма)
genre_nm (жанр)

3. Заполнить таблицу movies 3 тестовыми строками.

4. Добавить в таблицу movies новое поле comment.

5. Написать запрос для обновления поля с комментарием. Для каждой строки необходимо указать свой комментарий. Подумайте, как это сделать одной операций UPDATE, а не пятью разными запросами.

6. Удалить одну из строк таблицы на выбор.

7. Очистить таблицу, используя оператор группы DDL.

8. Снова заполните таблицу и обратите внимание на идентификаторы. Снова очистите полностью.

9. Удалить из таблицы столбец с комментарием.

10. Запустить операции вставки из отдельного файла

11. Найдите все фильмы жанра Crime. Вывести название фильма, год выпуска и рейтинг

12. Найдите ID актёров, по которым нет информации о фильмах, в которых они снимались

13. Как зовут актёра, игравшего ‘Harry Potter’?

14. Выведите все фильмы 90х жанров Drama и Romance

15. Для каждого жанра найдите кол-во фильмов и средний рейтинг Отсортировать по убыванию среднего рейтинга, при равенстве по убыванию кол-ва фильмов

16. Для каждого актёра выведите кол-во фильмов, в которых он сыграл (может быть 0). Отсортировать по убыванию кол-ва фильмов

17. Найдите все фильмы, в которых играл Jake Gyllenhaal. Выведите название фильма, год выпуска и длительность. Отсортируйте по увеличению длительности фильма

18. Выведите все фильмы с актёром, который играл ‘Captain Jack Sparrow’

19. Для каждого фильма выведите его жанры через запятую в виде строки (например, с помощью STRING_AGG) Если для фильма не указан жанр, вывести -.

20. Найдите всех актёров, которых играли вместе с Leonardo DiCaprio. Опционально: вывести фильмы, в которых они играли вместе.