DEV Community

Erlan Akbaraliev
Erlan Akbaraliev

Posted on • Edited on

База данных 2

  1. Реляционная база данных
  2. Основные типы связей
  3. Диаграмма
  4. Ключи
  5. Подзапросы
  6. IN
  7. Когда НУЖНА третья таблица?
  8. Когда НЕ НУЖНА третья таблица?
  9. JOIN
  10. GROUP BY
  11. Задача 1

Создание новых таблиц

Удалите таблицу "книги" если имеется, мы создадим новую таблицу, с новой структурой с таким же именем.
Выполните этот запрос чтобы удалить таблицу "книги":

DROP TABLE книги;
Enter fullscreen mode Exit fullscreen mode

Скопируйте и выполните все запросы которые имеются в этом файле.
Ссылка на файл

Изпульзуя этот файл мы создали 7 таблиц:

  1. книги
  2. авторы
  3. книги_авторы
  4. переводчики
  5. переводчики_книги
  6. издатели
  7. рейтинг

Если откроете в Pgadmin4 Servers/PostgreSQL 18/Базы Данных/моябазаданных/Схемы/public/Таблицы можете посмотреть на таблицу которые у вас имеются.

У всех этих таблиц есть какое-то взаимосвязь, поэтому эти таблицы называются Реляционными базами данных.

1. Реляционная база данных

Реляционная база данных = Связанные таблицы/база данные
Relation = Реляция = отношение
База данные/таблицы у которых есть отношение, связь с друг-другом.

         авторы                         книги
-----------------------          -----------------------
|     имя             |          |     название        |
| Адания Шибли        |          | Валун               |
| Ахмед Саадави       |          | Кит                 |
| Алия Трабукко Зеран |          | Евангелие           |
| Аманда Свенссон     |          | Стоящий тяжело      |
| Андрей Курков       |          | Убежище времени     |
-----------------------          -----------------------
Enter fullscreen mode Exit fullscreen mode

Просто смотря на эти таблицы как мы можем сказать кто написал какую книгу?

Первая строка в таблице авторов всегда будет соответствовать первой строке в таблице книг. Проблема этой системы в том, что можно ошибиться — добавить книгу, но забыть добавить её автора, или наоборот. Также один автор может написать несколько книг, или книгу могут написать несколько авторов вместе.

И это может привести к дублированию данных — например, если один автор написал несколько книг, его имя будет повторяться, или если книгу написали несколько авторов, данные книги будут повторяться.

            автор.                        книги
----------------------------     -------------------------
| id     имя               |     | id    название          |
|  1   Адания Шибли        |     |  1     Валун            |
|  2   Ахмед Саадави       |     |  2     Кит              |
|  3   Алия Трабукко Зеран |     |  3     Евангелие         |
|  4   Аманда Свенссон     |     |  4     Стоящий тяжело.   |
|  5   Андрей Курков       |     |  5     Первое Солнце     |
                                 |  6     Второе Солнцу     |
 -------------------------                        
                                 ----------------------------
Enter fullscreen mode Exit fullscreen mode

Что мы можем сделать чтобы у нас были две разные связанные таблицы и данные ни в какой таблице не повторялись?
Мы можем создать третью таблицу и назвать её авторы_книги.

         авторы_книги
---------------------------- 
| автор_id     книга_id    |
|  1              1        |
|  2              2        |
|  3              3        |
|  4              4        |
|  5              5        |
|  5              6        |
----------------------------
Enter fullscreen mode Exit fullscreen mode

Автор с id 5, написал книги с id 5 и 6.

Почему используем id, а не имя автора или название книги?
Потому что у разных авторов может быть одинаковое имя, а у разных книг — одинаковое название. id — это уникальный номер для каждого автора и каждой книги, который никогда не повторяется.

Зачем делить одну таблицу на несколько?
Когда таблица одна, хранить много столбцов и данных становится неудобно. Например, можно в таблицу «авторы» добавить столбцы страна (где родился) и год рождения автора.

Если все данные хранить в одной большой таблице с множеством столбцов, её будет трудно понимать и использовать. Чтобы работать с такими данными проще, лучше разделить информацию на несколько небольших таблиц.

  • Упрощается понимание структуры данных — маленькие таблицы легче читать и использовать.
  • Уменьшается дублирование данных.
  • Улучшается производительность запросов — запросы к маленьким таблицам выполняются быстрее.

2. Основные типы связей

  • один к одному
  • один ко многим
  • многие ко многим

Один к одному
У одного автора есть только одна книга.
One-to-one

Один ко многим
У одного автора имеются несколько книг
One-to-many

Многие ко многим
Не только один автор может написать несколько книг, но и одну книгу могут писать несколько авторов вместе.
Many-to-many

3. Диаграмма

Диаграмма

Связи между таблицами показываются глаголами:

  • Автор (author) пишет (writes) книгу (book)
  • Издатель (publisher) публикует (publishes) книгу
  • Переводчик (translator) переводит (translates) книгу
  • У книги (book) имеется (has) рейтинги

Диаг
Первая (0) означает что нет никаких связей
Вторая что есть только одна связь
Третья несколько связей

Д
У одного автора может быть только одна книга

Д2
Автор может написать одну книгу и у одной книги может быть один автор.

Д3
Один автор может иметь несколько книг, у одной книги могут быть несколько авторов.

4. Ключи

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

         авторы                         книги
----------------------------     -------------------------
| id     имя               |     | id    название        |
|  1   Адания Шибли        |     |  1     Валун          |
|  2   Ахмед Саадави       |     |  2     Кит            |
|  3   Алия Трабукко Зеран |     |  3     Евангелие      |
|  4   Аманда Свенссон     |     |  4     Стоящий тяжело |  
|  5   Андрей Курков       |     |  5     Убежище времени|
                                 |  6     Первое солнце  |
----------------------------     -------------------------
Enter fullscreen mode Exit fullscreen mode 
         авторы_книги
---------------------------- 
| автор_id     книга_id    |
|  1              2        |
|  2              1        |
|  3              5        |
|  4              4        |
|  5              3        |
|  5              6        |
----------------------------
Enter fullscreen mode Exit fullscreen mode

Первичный ключ (Primary Key) — уникальный столбец в таблице, который однозначно идентифицирует каждую строку.
Внешний ключ (Foreign Key) — столбец, который ссылается на первичный ключ другой таблицы и связывает таблицы между собой.

id в таблице "авторы" это уникальный столбец которая идентифицирует каждую строку и тоже самое в таблице "книги". id в обоих таблицах в этой связи явльяются Первычными ключами (Primary keys).

автор_id, книга_id ссылаются на первычные ключи id из таблицы авторы и книги, и связывают эти таблицы поэтому явльяются внешными ключами в этой связи.

5. Подзапросы

Подзапрос - запрос внутри другого запроса.

Выведите все книги которые были опубликованы издателем 'And Other Stories'.

Сперва посмотрите на структуру этих двух таблиц:

  • издатели
  • книги

Как вы видите в таблице издатели есть столбец издатель_id и в таблице издатели есть столбец id.
Эти две таблицы: книги, издатели соеденины по столбцам издатель_id и id

Связь между этими таблицами: один ко многим.
Один издатель может опубликовать несколько книги и одна книги может быть опубликовано только одним издателем.

Теперь к задачу:

  1. Выведите id издателя 'And Other Stories'
  2. Выведите все книги которые были опубликованы этим издателем

С иными словами:

  1. Выведите id из таблицы издатели где издатель='And Other Stories'
  2. Выведите все столбцы из таблицы книги где издатель_id="id издателя 'And Other Stories'"

1.

SELECT id
FROM издатели
WHERE издатель='And Other Stories';
Enter fullscreen mode Exit fullscreen mode

Результат: 1

2.

SELECT *
FROM книги
WHERE издатель_id=1;
Enter fullscreen mode Exit fullscreen mode

Можно получить тот же результат используя подзапрос и это считается хорошой практикой.

SELECT *
FROM книги
WHERE издатель_id=(
        SELECT id
        FROM издатели
        WHERE издатель='And Other Stories'
);
Enter fullscreen mode Exit fullscreen mode

Вот перевод простыми словами:

Обрати внимание:

  • Подзапрос находится в круглых скобках.
    Запрос, который находится глубже всего в скобках, выполняется первым, а затем уже выполняется внешний запрос.

  • Внутренний запрос сделан с отступом.
    Это делается по правилам оформления кода — так подзапрос выглядит аккуратнее и легче читается.

Выведите все рейтинги книги 'Кит'.

  1. Посмотрите на таблицы книги, рейтинг и постарайтесь понять как эти таблицы соединяются
  2. Выведите id книги с названием 'Кит'
  3. Выведите рейтинги где книгаid=_ 
SELECT * 
FROM рейтинг
WHERE книга_id=(
    SELECT id 
        FROM книги
    WHERE название='Кит'
);
Enter fullscreen mode Exit fullscreen mode

Выведите все автора книги 'Валун'

  1. Таблицы книги, авторы, авторы_книги. Тип связи: многое ко многим
  2. Выведите id книги 'Валун'
  3. Выведите авторid где книга_id=_
  4. Выведите имя автора где id=предыдущий запрос

Ответ:

SELECT id
FROM книги
WHERE название='Валун';
Enter fullscreen mode Exit fullscreen mode 
SELECT автор_id
FROM авторы_книги
WHERE книга_id= (
    SELECT id
    FROM книги
    WHERE название='Валун'
);
Enter fullscreen mode Exit fullscreen mode 
SELECT *
FROM авторы
WHERE id=(
    SELECT автор_id
    FROM авторы_книги
    WHERE книга_id= (
        SELECT id
        FROM книги
        WHERE название='Валун'
    )
);
Enter fullscreen mode Exit fullscreen mode

6. IN

IN - в
Это ключевое слово используется, чтобы проверить, есть ли нужное значение в данном списке или множестве значений.

SELECT *
FROM книги
WHERE название IN ('Валун', 'Кит');
Enter fullscreen mode Exit fullscreen mode 
SELECT *
FROM книги
WHERE название='Валун' OR название='Кит';
Enter fullscreen mode Exit fullscreen mode 
SELECT *
FROM книги
WHERE название NOT IN ('Валун', 'Кит');
Enter fullscreen mode Exit fullscreen mode

Выведите все книги 'Ольга Токарчук' используя ключевое слова

  1. Связь между книгами и авторами: многое ко многим. У одного автора могут быть несколько книг, поэтому будем использовать ключевое слово IN
  2. Выведите id автор 'Ольга Токарчук'
  3. Выведите все книгаid где автор_id=_
  4. Выведите все данные книг где id=__ 
SELECT *
FROM книги
WHERE id IN (
    SELECT книга_id
    FROM авторы_книги
    WHERE автор_id= (
        SELECT id
        FROM авторы
        WHERE имя='Ольга Токарчук'
    )
);
Enter fullscreen mode Exit fullscreen mode

Этот запрос даст нам все id книг автора 'Ольга Токарчук' (25,55,78).
Так как книг несколько, нам нужно использовать слово IN.
Выведи все данные книг которые в этом списке id книг.

SELECT книга_id
FROM авторы_книги
WHERE автор_id= (
    SELECT id
    FROM авторы
    WHERE имя='Ольга Токарчук'
);
Enter fullscreen mode Exit fullscreen mode

7. 📌 Когда НУЖНА третья таблица?

Когда связь многие-ко-многим
То есть одна книга может иметь много авторов,
и один автор может написать много книг.

В этом случае создаём третью таблицу:

         авторы                         книги
----------------------------     -------------------------
| id     имя               |     | id    название        |
|  1   Адания Шибли        |     |  1      Валун         |
|  2   Ахмед Саадави       |     |  2      Кит           |
|  3   Алия Трабукко Зеран |     |  3     Евангелие      |
|  4   Аманда Свенссон     |     |  4     Стоящий тяжело |  
|  5   Андрей Курков       |     |  5     Убежище времени|
                                 |  6     Первое солнце  |
----------------------------     -------------------------

         авторы_книги
---------------------------- 
| автор_id     книга_id    |
|  1              2        |
|  2              1        |
|  3              5        |
|  4              4        |
|  5              3        |
|  5              6        |
----------------------------
Enter fullscreen mode Exit fullscreen mode

➡️ Она соединяет авторов и книги.

  1. 📌 Когда НЕ НУЖНА третья таблица? Когда связь один-к-одному или один-ко-многим Один издатель выпускает много книг. Но каждая книга принадлежит только одному издателю.

Один ко многим

       издатели
--------------------------
| id   издатель          |
| 1    And Other Stories |
| 2    Charco Press      |
| 3    Europa Editions   |
| 4    Faber & Faber     |
--------------------------
                 книги       
----------------------------------------
| id     имя                издатель_id |
|  1   Валун                     1      |  
|  2   Кит                       1      |  
|  3   Костёр                    2      |  
|  4   Едва рождённый            2      |   
|  5   Небеса                    2      |  
----------------------------------------
Enter fullscreen mode Exit fullscreen mode

В связи один-ко-многим внешний ключ всегда ставится в ту таблицу, где находится “много”, чтобы не дублировать данные из стороны “один”.
Один издатель может выпустить много книг. Многое в этом случии таблица книги.

❌ Почему внешний ключ НЕ должен быть в таблице издатели?

              издатели
-----------------------------------------
| id   издатель            книга_id     |
| 1    And Other Stories   1            |
| 1    And Other Stories   2            |
| 1    And Other Stories   3            |
| 2    Charco Press        4            |
| 2    Charco Press        5            |
-----------------------------------------
Enter fullscreen mode Exit fullscreen mode

Если бы мы добавили поле книга_id в таблицу издатели, то из-за того, что один издатель выпускает несколько книг, нам пришлось бы дублировать одну и ту же запись издателя для каждой книги.

Один к одкому
Если бы по каким то правилом в наших таблицах один автор мог бы написать только одну книгу и одной книги мог бы быть только один автор.

              авторы                        
-------------------------------------
| id     имя                книга_id |     
|  1   Адания Шибли             2    |     
|  2   Ахмед Саадави            1    |     
|  3   Алия Трабукко Зеран      5    |     
|  4   Аманда Свенссон          6    |     
|  5   Андрей Курков            3    |     
-------------------------------------     
               книги
-------------------------------------
| id    название            автор_id |
|  1     Валун                  2    |
|  2     Кит                    1    |
|  3     Евангелие              5    |
|  4     Стоящий тяжело         4    |  
|  5     Убежище времени        3    |
-------------------------------------
Enter fullscreen mode Exit fullscreen mode

9. JOIN

JOIN - объединять
Это ключевое слово позволяет объединять две или больше таблицы в один результат.

Выведите название издателя книги 'Небеса'

  1. Тип связи между книги и издатели
  2. Выведите id книги 'Небеса'
  3. Выведите издатель книги с id=_ 
SELECT издатель
FROM издатели
WHERE id=(
    SELECT id
    FROM книги
    WHERE название='Небеса'
);
Enter fullscreen mode Exit fullscreen mode

Выведите название издателя каждой книги

SELECT *
FROM книги к
JOIN издатели и ON к.издатель_id=и.id;
Enter fullscreen mode Exit fullscreen mode

Выведи все столбцы
из таблицы книги, коротко назови таблицу 'к'
объедини таблицей издатели, коротко назови таблицу 'и', по издатель_id из таблицы k и id из таблицы к.

Создайте эти таблицы для следующих примеров:

-- Таблица Миграции
CREATE TABLE миграции (
    id INTEGER PRIMARY KEY,
    расстояние INTEGER,  -- distance
    дней INTEGER         -- days
);

INSERT INTO миграции (id, расстояние, дней) VALUES
(10484, 1000, 107),
(11728, 1531, 56),
(11729, 1370, 37),
(11732, 1622, 62),
(11734, 1491, 58),
(11735, 2723, 82),
(11736, 1571, 52),
(11737, 1957, 92);

-- Таблица животные
CREATE TABLE животные (
    id INTEGER PRIMARY KEY,
    имя TEXT,         -- name
    вид TEXT          -- species
);

INSERT INTO животные (id, имя, вид) VALUES
(10484, 'Ая', 'Залофус калифорнийский'),
(11728, 'Спот', 'Залофус калифорнийский'),
(11729, 'Тигр', 'Залофус калифорнийский'),
(11732, 'Мейбл', 'Залофус калифорнийский'),
(11734, 'Рик', 'Залофус калифорнийский'),
(11790, 'Джоли', 'Залофус калифорнийский');
Enter fullscreen mode Exit fullscreen mode

Выведите все данные животного и миграционные данные в одной таблице

  1. Тип связи и по какому общему столбцу эти две таблицы соединяются 
SELECT *
FROM животные ж
JOIN миграции м ON ж.id=м.id;
Enter fullscreen mode Exit fullscreen mode

Если в одной таблице есть такие id, которых нет в другой таблице, то эти строки не попадут в результат объединения.
Такой вид объединения называется INNER JOIN (внутреннее соединение).

LEFT JOIN отдаёт приоритет данным из левой (первой) таблицы.
То есть: всё из левой таблицы попадёт в результат, даже если совпадений справа нет.

SELECT *
FROM животные ж
LEFT JOIN миграции м ON ж.id=м.id;
Enter fullscreen mode Exit fullscreen mode

Некоторые строки в результирующей таблице могут быть частично пустыми.
Так бывает, если в правой таблице нет данных для какого-то конкретного ID.

Точно так же RIGHT JOIN сохраняет все строки из правой (второй) таблицы.

SELECT *
FROM животные ж
RIGHT JOIN миграции м ON ж.id=м.id;
Enter fullscreen mode Exit fullscreen mode

А FULL JOIN показывает все строки из обеих таблиц, даже если для каких-то ID нет совпадений.

SELECT *
FROM животные ж
FULL JOIN миграции м ON ж.id=м.id;
Enter fullscreen mode Exit fullscreen mode

Обе таблицы в базе данных морских львов имеют столбец id.
Поскольку мы соединяем таблицы по столбцу с одинаковым названием, мы можем не указывать часть ON в запросе при соединении — база сама поймёт, по какому столбцу соединять.

SELECT *
FROM животные ж
NATURAL JOIN миграции м;
Enter fullscreen mode Exit fullscreen mode

10. GROUP BY

GROUP BY = Группируй по

Рассмотрим таблицу рейтинг.
Для каждой книги мы хотим найти её средний рейтинг.

Чтобы это сделать, нам сначала нужно посчитать средний рейтинг внутри каждой группы, а затем сгруппировать все рейтинги по книгам.

Используя таблицу рейтинг, выведите id книги и средний рейтинг каждой книги (сгруппируй по книгам).

SELECT книга_id, AVG(рейтинг)
FROM рейтинг
GROUP BY книга_id;
Enter fullscreen mode Exit fullscreen mode

Теперь мы хотим увидеть только те книги, которые хорошо оценены — то есть имеют средний рейтинг выше 3.5.

SELECT книга_id, AVG(рейтинг)
FROM рейтинг
GROUP BY книга_id
HAVING AVG(рейтинг) > 3.5;
Enter fullscreen mode Exit fullscreen mode

Выведи только те группы книг, у которых средний рейтинг больше 3.5.
HAVING - чтобы добавить условие к нашему запросу. Тоже самое как WHERE, но когда используем GROUP BY, после этого нужно использовать слово HAVING чтобы добавить условие к группам.

SELECT книга_id, AVG(рейтинг)
FROM рейтинг
WHERE рейтинг >= 1          -- фильтрация строк
GROUP BY книга_id
HAVING AVG(рейтинг) > 3.5;  -- фильтрация групп
Enter fullscreen mode Exit fullscreen mode

👉 WHERE — фильтрует строки до группировки
👉 HAVING — фильтрует группы после группировки
Что происходит?

WHERE сначала убирает ненужные строки (например, оценки ниже 1).
GROUP BY собирает оставшиеся строки в группы по книге.
HAVING показывает только те группы (книги), которые подходят под условие.

Задача 1 

1. Выведите только имена всех авторов без повторений
2. Выведите количество всех авторов
3. Выведите все книги у которых название начинается с буквы 'Д' (LIKE)
4. Выведите все данные книг и отсортируйте по их названиям по возрастанию (ORDER BY)
5. Выведите все данные книг и отсортируйтеих по году публикации по убыванию (ORDER BY)
6. Выведите все книги, которые были опубликованы в период с 2018 по 2021 год

7. Выведите все миграционные данные животного с именем 'Ая' (таблицы животные, миграции, подзапрос)
8. Выведите все миграционные данные животного с именем 'Рик' (таблицы животные, миграции, подзапрос)
9. Объедините таблицы животные и миграции (JOIN)
10. Объедините таблицы животные и миграции и выведите столбцы id, имя из таблицы животные и расстояние из таблицы миграции(JOIN)
11. Объедините таблицы животные и миграции с приоритетом на таблицу животные (LEFT JOIN)
12. Объедините таблицы животные и миграции с приоритетом на таблицу миграции (RIGHT JOIN)
13. Выведите список всех животных и их миграционных данных, используя NATURAL JOIN

14. Выведите все данные авторов которые входят в список ('Бора Чхун', 'Хан Кан')
15. Выведите все авторы которые НЕ входят в список ('Бора Чхун', 'Хан Кан')

16. Выведите все название книг которые были опубликованы издателем 'Scribe UK'
17. Объедините таблицы книги и издатели по общему ключу (JOIN)
18. Объедините таблицы книги и издатели по общему ключу и выведите столбцы название, издатель_id из таблицы книги и id, издатель из таблицы издатели (JOIN)
19. Выведите все книги которые были опубликованы издателем 'Scribe UK' используя JOIN в формате название, издатель
20. Выведите все книги издателей ('Scribe UK', 'Pushkin Press', 'Europa Editions') используя JOIN в формате (название, издатель) (JOIN, IN)
21. Выведите все книги издателей ('Scribe UK', 'Pushkin Press', 'Europa Editions') используя JOIN в формате (название, издатель) и отсортируйте по издателям (JOIN, IN, ORDER BY)
22. Выведите все книги в формате (название, издатель) используя короткое название таблиц (JOIN, IN)

23. Выведите все рейтинги книги 'Небеса' (книги, рейтинг, подзапрос)
24. Выведите средний рейтинг книги 'Небеса' (AVG, подзапрос)
25. Объедините таблицы книги и рейтинг по общему ключу
26. Выведите название и средний рейтинг каждой книги(JOIN, AVG, GROUP BY)
27. Выведите название и средний рейтинг книги 'Кит' (JOIN, AVG, GROUP BY, HAVING)
28. Выведите название и средний рейтинг книг у которых рейтинг больше 3.5 (JOIN, AVG, GROUP BY, HAVING)
29. Выведите название и средний рейтинг книг у которых рейтинг больше 3 и средний рейтинг больше 3.5 (JOIN, AVG, WHERE, GROUP BY, HAVING)
Enter fullscreen mode Exit fullscreen mode

Ответы:

-- 1. Выведите только имена всех авторов без повторений
SELECT DISTINCT имя
FROM авторы;

-- 2. Выведите количество всех авторов
SELECT COUNT(имя)
FROM авторы;

-- 3. Выведите все книги у которых название начинается с буквы 'Д' (LIKE)
SELECT * FROM книги
WHERE название LIKE 'Д%';

-- 4. Выведите все данные книг и отсортируйте по их названиям по возрастанию (ORDER BY)
SELECT *
FROM книги
ORDER BY название ASC;

-- 5. Выведите все данные книг и отсортируйте по их по году публикации по убыванию (ORDER BY)
SELECT *
FROM книги
ORDER BY год DESC;

-- 6. Выведите все книги, которые были опубликованы в период с 2018 по 2021 год
SELECT *
FROM книги
WHERE год BETWEEN 2018 AND 2021;

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

-- 7. Выведите все миграционные данные животного с именем 'Ая'
SELECT *
FROM миграции
WHERE id=(
    SELECT id
    FROM животные
    WHERE имя='Ая'
);

-- 8. Выведите все миграционные данные животного с именем 'Рик' (таблицы животные, миграции, подзапрос)
SELECT *
FROM миграции
WHERE id=(
    SELECT id
    FROM животные
    WHERE имя='Рик'
);

-- 9.Объедините таблицы животные и миграции (JOIN)
SELECT *
FROM животные
JOIN миграции ON животные.id=миграции.id;

-- 10. Объедините таблицы животные и миграции и выведите столбцы id, имя, из таблицы животные и расстояние из таблицы миграции (JOIN)
SELECT животные.id, животные.имя, миграции.расстояние
FROM животные
JOIN миграции ON животные.id=миграции.id;

-- 11. Объедините таблицы животные и миграции с приоритетом на таблицу животные (LEFT JOIN)
SELECT *
FROM животные
LEFT JOIN миграции ON животные.id=миграции.id;

-- 12. Объедините таблицы животные и миграции с приоритетом на таблицу миграции (RIGHT JOIN)
SELECT *
FROM животные
RIGHT JOIN миграции ON животные.id=миграции.id;

-- 13. Выведите список всех животных и их миграционных данных, используя NATURAL JOIN
SELECT *
FROM животные
NATURAL JOIN миграции;

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

-- 14. Выведите все авторы которые входят в список ('Бора Чхун', 'Хан Кан')
SELECT *
FROM авторы
WHERE имя IN ('Бора Чхун', 'Хан Кан');

-- 15. Выведите все авторы которые НЕ входят в список ('Бора Чхун', 'Хан Кан')
SELECT *
FROM авторы
WHERE имя NOT IN ('Бора Чхун', 'Хан Кан');

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

-- 16. Выведите все книги которые были опубликованы издателем 'Scribe UK'
SELECT название
FROM книги
WHERE издатель_id=(
    SELECT id
    FROM издатели
    WHERE издатель='Scribe UK'
);

-- 17. Объедините таблицы книги и издатели по общему ключу (JOIN)
SELECT *
FROM книги
JOIN издатели ON книги.издатель_id=издатели.id;

-- 18. Объедините таблицы книги и издатели по общему ключу и выведите столбцы название, издатель_id из таблицы книги и id, издатель из таблицы издатели (JOIN)
SELECT книги.название, книги.издатель_id, издатели.id, издатели.издатель
FROM книги
JOIN издатели ON книги.издатель_id=издатели.id;

-- 19. Выведите все название книг которые были опубликованы издателем 'Scribe UK' и название издателя используя JOIN
SELECT книги.название, издатели.издатель
FROM книги
JOIN издатели ON книги.издатель_id=издатели.id
WHERE издатель='Scribe UK';

-- 20. Выведите все книги издателей ('Scribe UK', 'Pushkin Press', 'Europa Editions') используя JOIN в формате название, издатель
SELECT книги.название, издатели.издатель
FROM книги
JOIN издатели ON книги.издатель_id=издатели.id
WHERE издатель IN ('Scribe UK', 'Pushkin Press', 'Europa Editions');

-- 22. Выведите все книги в формате (название, издатель) используя короткое название таблиц (JOIN, IN)
SELECT к.название, и.издатель
FROM книги к
JOIN издатели и ON к.издатель_id=и.id;

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

-- 23. Выведите все рейтинги книги 'Небеса' (книги, рейтинг, подзапрос)
SELECT *
FROM рейтинг
WHERE книга_id=(
    SELECT id
    FROM книги
    WHERE название='Небеса'
);

-- 24. Выведите средний рейтинг книги 'Небеса' (AVG, подзапрос)
SELECT AVG(рейтинг)
FROM рейтинг
WHERE книга_id=(
    SELECT id
    FROM книги
    WHERE название='Небеса'
);

-- 25. Объедините таблицы книги и рейтинг по общему ключу
SELECT *
FROM книги к
JOIN рейтинг р ON к.id=р.книга_id

-- 26. Выведите название и средний рейтинг каждой (JOIN, AVG, GROUP BY)
SELECT к.название, ROUND(AVG(р.рейтинг),2)
FROM книги к
JOIN рейтинг р ON к.id=р.книга_id
GROUP BY к.название;

-- 27. Выведите название и средний рейтинг книги 'Кит' (JOIN, AVG, GROUP BY, HAVING)
SELECT к.название, ROUND(AVG(р.рейтинг),2)
FROM книги к
JOIN рейтинг р ON к.id=р.книга_id
GROUP BY к.название
HAVING к.название='Кит';

-- 28. Выведите название и средний рейтинг книг у которых рейтинг больше 3.5 (JOIN, AVG, GROUP BY, HAVING)
SELECT к.название, ROUND(AVG(р.рейтинг),2)
FROM книги к
JOIN рейтинг р ON к.id=р.книга_id
GROUP BY к.название
HAVING ROUND(AVG(р.рейтинг),2)>3.5;

-- 29. Выведите название и средний рейтинг книг у которых рейтинг больше 3 и средний рейтинг больше 3.5 (JOIN, AVG, WHERE, GROUP BY, HAVING)
SELECT к.название, ROUND(AVG(р.рейтинг),2)
FROM книги к
JOIN рейтинг р ON к.id=р.книга_id
WHERE р.рейтинг>3
GROUP BY к.название
HAVING ROUND(AVG(р.рейтинг),2)>3.5;
Enter fullscreen mode Exit fullscreen mode

После этой задачи можете еще постараться решить задача 2.

Top comments (0)