База Данных 2 Практика

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)

---

Ответы:

-- 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;