SQL

Вы будете использовать базу данных publications. 

publications (1).sql Скачать publications (1).sql 

Создайте файл solutions.sql со всеми комментариями, необходимыми для понимания вашего кода.

Ссылка на внешний сайт.Задача 1 - Самые прибыльные авторы

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

Чтобы решить эту задачу, важно учитывать следующие моменты:

• В таблице sales название может встречаться несколько раз. Необходимо рассчитывать роялти для каждой продажи.

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

• В вашем окончательном решении нужно суммировать следующую прибыль для каждого отдельного автора:

  • Все авансы, которые рассчитываются ровно один раз для каждого названия.
  • Все роялти по каждой продаже.

Поэтому, для достижения цели, нельзя использовать только один запрос SELECT, потребуется использовать подзапросы. Вам нужно выполнить несколько шагов, чтобы получить решение. Ниже приведён обзор этих шагов:

1. Рассчитать роялти каждой продажи для каждого автора и аванс для каждого автора и публикации.

2. Используя результат из шага 1 как подзапрос, агрегировать общие роялти для каждого названия и автора.

3. Используя результат из шага 2 как подзапрос, вычислить общую прибыль каждого автора, суммируя авансы и роялти по каждому названию.

Ниже мы проведём вас через каждый шаг. В вашем файле solutions.sql включите SELECT-запросы каждого шага, чтобы ваш преподаватель мог проверить ваш процесс решения.

Шаг 1: Рассчитать роялти каждой продажи для каждого автора и аванс для каждого автора и публикации

Напишите SELECT-запрос для получения следующего результата:

• ID названия
• ID автора
• Аванс каждого названия и автора
  • Формула:

    advance = titles.advance * titleauthor.royaltyper / 100
    
    

• Роялти каждой продажи
  • Формула:

    sales_royalty = titles.price * sales.qty * titles.royalty / 100 * titleauthor.royaltyper / 100
    
    

• Обратите внимание, что titles.royalty и titleauthor.royaltyper делятся на 100, так как это проценты, а не числа с плавающей точкой.

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

Ссылка на внешний сайт.Шаг 2: Агрегировать общие роялти для каждого названия и автора

Используя результат из шага 1, напишите запрос с подзапросом, чтобы получить следующий результат:

• ID названия
• ID автора
• Объединённые роялти каждого названия для каждого автора
  • Подсказка: используйте SUM и группировку по au_id и title_id

В результате этого шага каждое название должно встречаться только один раз для каждого автора.

Ссылка на внешний сайт.Шаг 3: Рассчитать общую прибыль каждого автора

Теперь, когда у каждого названия есть ровно одна строка для каждого автора, где доступны аванс и роялти, мы готовы получить окончательный результат. Используя результат из шага 2, напишите запрос с двумя подзапросами, чтобы получить следующий результат:

• ID автора
• Прибыль каждого автора, суммируя авансы и роялти по каждому названию

Отсортируйте результат по общей прибыли от высокой к низкой и ограничьте количество строк тремя.

Ссылка на внешний сайт.Задача 2 - Альтернативное решение

В предыдущей задаче вы разработали решение следующим образом:

• Использование производных таблиц (подзапросов).(см. ссылки на внешние ресурсы)

Мы предлагаем попробовать другой способ:

• Создание временных таблиц MySQL и выполнение запросов к ним в последующих шагах.

Включите ваше альтернативное решение в файл solutions.sql.

Ссылки на внешние ресурсы.Дополнительное обучение

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

Если ваши данные часто меняются, использование производных таблиц может привести к разным результатам при каждом выполнении. Временные таблицы сохраняют данные, пока сессия активна, и даже при изменениях в базе, данные в них остаются неизменными, пока вы их не обновите. Поэтому, если важна актуальность данных, лучше использовать производные таблицы. Если же важна производительность при больших объёмах данных и сложных запросах, лучше использовать временные таблицы, так как они позволяют выполнять тяжёлые вычисления один раз и повторно обращаться к результатам без повторных затрат.

Ссылка на внешний сайт.Задача 3

Расширяя решение из задач 1 и 2, создайте постоянную таблицу с названием most_profiting_authors, которая будет содержать данные о самых прибыльных авторах. Таблица должна иметь 2 столбца:

• au_id - ID автора
• profits - Общая прибыль автора, суммируя авансы и роялти

Включите ваше решение в файл solutions.sql.

Ссылки на внешние ресурсы.Дополнительное обучение

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