Решение модуля 2.2 из курса «Интерактивный тренажер по SQL»

Разобрали ответы и решения урока 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;

Если у вас не отображается решение последних задач, значит у вас включен блокировщик рекламы который вырезает эти ответы

3 комментариев

Новые

Старые Популярные

Межтекстовые Отзывы

Посмотреть все комментарии

Серафим

2 лет назад

ЗаданиеПридумайте один или несколько запросов для таблиц book, author, genre и city. Проверьте, правильно ли они работают.
При желании можно формулировку запросов разместить в комментариях.
Размещенные задания можно использовать для закрепления материала урока.
Оценивайте понравившиеся Вам запросы.
В последнем модуле создан отдельный урок, в котором мы разместим запросы, набравшие наибольшее количество лайков.

CREATE TEMPORARY TABLE total_revenue
SELECT
  amount * price AS rev, — AS Общая стоимость книг,
  title, — AS Наименование,
  name_author — AS Автор
FROM (
SELECT
  SUM(amount) AS amount,
  price,
  title,
  name_author
FROM book
  INNER JOIN author ON book.author_id = author.author_id
GROUP BY
  price,
  name_author,
  title) AS query_1;

select * from total_revenue;

SET @avg_rev = (SELECT AVG(rev) FROM total_revenue);

CREATE TEMPORARY TABLE city_dest
SELECT
  title,
  name_author,
  rev,
  CASE WHEN rev > @avg_rev THEN ‘Cankt_fetep6ypr’
  ELSE ‘Mockea’ END AS city_book
FROM total_revenue
ORDER BY rev DESC;

select * from city_dest;

-15

Ответить

Алексей

3 лет назад

select book.title as Название, supply.author as Автор, (book.amount + supply.amount) as Количество
from supply
left join book on supply.title = book.title
and supply.amount = book.amount
left join author on author.author_id = book.author_id
where book.title = supply.title and book.price = supply.price

SELECT title, name_author, name_genre, price, amount
FROM
  author
  INNER JOIN book ON author.author_id = book.author_id
  INNER JOIN genre ON book.genre_id = genre.genre_id
WHERE genre.genre_id IN
     (
     SELECT query_in_1.genre_id
     FROM
       (
        SELECT genre_id, SUM(amount) AS sum_amount
        FROM book
        GROUP BY genre_id
        )query_in_1
     INNER JOIN
       (
        SELECT genre_id, SUM(amount) AS sum_amount
        FROM book
        GROUP BY genre_id
        ORDER BY sum_amount DESC
        LIMIT 1
        ) query_in_2
     ON query_in_1.sum_amount= query_in_2.sum_amount
     )
     ORDER BY title ASC;