Разобрали ответы и решения урока 2.2(Запросы на выборку, соединение таблиц) под курс «Интерактивный тренажер по SQL».
Предыдущий разбор модуля 2.1 следующий разбор модуль 2.3
Вывести название, жанр и цену тех книг, количество которых больше 8, в отсортированном по убыванию цены виде.
SELECT title, name_genre, price
FROM book AS b
INNER JOIN genre AS g
ON b.genre_id = g.genre_id
WHERE b.amount > 8
ORDER BY 3 DESC;
Вывести все жанры, которые не представлены в книгах на складе.
SELECT name_genre
FROM genre AS g
LEFT JOIN book AS b
ON g.genre_id = b.genre_id
WHERE title IS NULL;
Есть список городов, хранящийся в таблице city.
Необходимо в каждом городе провести выставку книг каждого автора в течение 2020 года. Дату проведения выставки выбрать случайным образом. Создать запрос, который выведет город, автора и дату проведения выставки. Последний столбец назвать Дата. Информацию вывести, отсортировав сначала в алфавитном порядке по названиям городов, а потом по убыванию дат проведения выставок.
SELECT
name_city,
name_author,
DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND()*365) DAY) AS Дата
FROM city,author
ORDER BY
name_city ASC,
Дата DESC;
Вывести информацию о книгах (жанр, книга, автор), относящихся к жанру, включающему слово «роман» в отсортированном по названиям книг виде.
SELECT name_genre, title, name_author
FROM
author
INNER JOIN book ON author.author_id = book.author_id
INNER JOIN genre ON genre.genre_id = book.genre_id
WHERE name_genre = 'Роман'
ORDER BY title;
Посчитать количество экземпляров книг каждого автора из таблицы
author
. Вывести тех авторов, количество книг которых меньше 10, в отсортированном по возрастанию количества виде. Последний столбец назватьКоличество
.
SELECT name_author, sum(amount) AS Количество
FROM
author LEFT JOIN book
on author.author_id = book.author_id
GROUP BY name_author
HAVING Количество<10 or Количество is NULL
ORDER BY Количество ASC;
Вывести в алфавитном порядке всех авторов, которые пишут только в одном жанре. Поскольку у нас в таблицах так занесены данные, что у каждого автора книги только в одном жанре, для этого запроса внесем изменения в таблицу
book
. Пусть у нас книга Есенина «Черный человек» относится к жанру «Роман», а книга Булгакова «Белая гвардия» к «Приключениям» (эти изменения в таблицы уже внесены).
SELECT
name_author
FROM
author
INNER JOIN book USING(author_id)
GROUP BY
name_author
HAVING
COUNT(distinct(genre_id)) = 1
ORDER BY
name_author;
Вывести информацию о книгах (название книги, фамилию и инициалы автора, название жанра, цену и количество экземпляров книги), написанных в самых популярных жанрах, в отсортированном в алфавитном порядке по названию книг виде. Самым популярным считать жанр, общее количество экземпляров книг которого на складе максимально.
SELECT title, name_author, name_genre, price, amount FROM
author INNER JOIN book ON author.author_id = book.author_id
INNER JOIN (
SELECT genre_id FROM book
GROUP BY genre_id
HAVING SUM(amount) >= ALL (
SELECT SUM(amount)
FROM book
GROUP BY genre_id)) query_in
ON book.genre_id = query_in.genre_id
INNER JOIN genre ON query_in.genre_id = genre.genre_id
ORDER BY 1;
Если в таблицах
supply
иbook
есть одинаковые книги, которые имеют равную цену, вывести их название и автора, а также посчитать общее количество экземпляров книг в таблицахsupply
иbook
, столбцы назватьНазвание
,Автор
иКоличество
.
SELECT book.title AS Название, name_author AS Автор, sum(book.amount + supply.amount) AS Количество
FROM
author
INNER JOIN book USING (author_id)
INNER JOIN supply ON book.title = supply.title
AND author.name_author = supply.author
WHERE book.price = supply.price
GROUP BY book.title, name_author;