SQL

Ви будете використовувати базу даних publications. 

publications (1).sql Завантажити публікації (1).sql 

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

Посилання на зовнішній сайт.Завдання 1 - Найприбутковіші автори

У цьому завданні ви дізнаєтеся, хто є трьома найприбутковішими авторами у базі даних 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 автора
• Прибутки кожного автора, підсумовуючи аванси та загальні роялті кожної назви

Відсортуйте результат за загальним прибутком від найбільшого до найменшого та обмежте кількість рядків до 3.

Посилання на зовнішній сайт.Завдання 2 - Альтернативне рішення

У попередньому завданні ви розробили рішення наступним чином:

• Витягнуті таблиці (підзапити).(див. посилання на зовнішній сайт)

Ми пропонуємо вам спробувати інший спосіб:

• Створення тимчасових таблиць MySQL та запит до них у наступних кроках.

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

Посилання на зовнішній сайт.Додаткове навчання

У контексті цієї задачі ви можете використовувати або витягнуті таблиці, або тимчасові таблиці для розробки рішення. Перший спосіб здається більш зручним, але обидва мають свої переваги. Витягнуті таблиці зберігаються у пам’яті MySQL і зникають після завершення запиту. Тимчасові таблиці фізично зберігаються у MySQL і залишаються доступними, доки не закінчиться сесія. Якщо дані у базі часто оновлюються, використання витягнутих таблиць може призвести до різних результатів. Тоді як тимчасові таблиці зберігають дані до їх оновлення. Якщо важлива актуальність, використовуйте витягнуті таблиці. Якщо ж потрібно обробляти великі обсяги даних або складні запити, краще використовувати тимчасові таблиці для підвищення продуктивності.

Посилання на зовнішній сайт.Завдання 3

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

• au_id - ID автора
• profits - прибутки автора, підсумовуючи аванси та роялті

Включіть ваше рішення у файл solutions.sql.

Посилання на зовнішній сайт.Додаткове навчання

Щоб збалансувати продуктивність транзакцій бази даних і актуальність даних, інженери часто налаштовують автоматичні скрипти для періодичного запиту даних і збереження результатів у постійних таблицях. Це дозволяє швидко отримувати потрібну інформацію, хоча вона може бути трохи застарілою. Якщо дані часто оновлюються, краще використовувати витягнуті таблиці для актуальності. Якщо ж потрібно обробляти великі обсяги даних або складні запити, краще використовувати тимчасові таблиці для підвищення швидкості.