Головна    Статті    Карта    Зв'язок   

Задачі з SQL

лютий 2015

Сontent

MySQL-підзапити. Складні SQL-запити. SubQuery.

Завдання

1. Вибрати всі дані клієнта з таблиці, в якого максимальна сума замовлення.
2. Знайти споживача в котрого платежі за замовлення більші ніж середнєстатистичні платежі.
3. Вибрати дані споживачів, які не зробили ніяких замовлень.
4. Вибрати список споживачів, що мають щонайменше одне замовлення з продажами більше ніж 10К.
5. Знайти максимум, мінімум та середнє значення кількості проданих елементів замовлень.
6. Вибрати дані продукту, ціна котрого більша ніж середня ціна всіх продуктів.



дещо теорії про субзапити

Підсумок: В цьому розділі розглянемо складні SQL-запити, яким чином застосовуються підзапити MySQL та концепцію взаємозв'язків підзапитів.

MySQL subquery. MySQL підзапит це SQL-запит, що розташований всередині іншого SQL-запиту, таких як SELECT, INSERT, UPDATE чи DELETE.
MySQL підзапит також називається внутрішній запит, так як SQL-запит, що утримує в собі підзапит, називається зовнішній SQL-запит.

Давайте подивимось на слідуючий SQL-запит, що складається з двух частин. SQL-запит повертає значення співробітників (employees), що працюють в офісах, які в свою чергу розташовані в країні - USA.

  • SQL-підзапит повертає код всіх офісних приміщень (officeCode), що розташовані в USA
  • Зовнішній SQL-запит вибирає імя та прізвище співробітників, офіси яких повернув SQL-підзапит.
MySQL підзапит

Підзапит має бути заключений в круглі дужки.



MySQL підзапит з оператором WHERE.

1. MySQL підзапит з операторами порівняння.

Якщо SQL-підзапит повертає одне значення, Ви можете застосовувати оператор порівняння застосовуючи оператор WHERE.
Так наприклад, слідуючий SQL-запит повертає всі дані клієнта, в якого максимальна сума замовлення.

SQL-запит:

SELECT customerNumber,
       checkNumber,
       amount
FROM payments
WHERE amount = (
   SELECT MAX(amount)
   FROM payments)

MySQL підзапит

2. MySQL підзапит з операторами порівняння.

Також можна застосовувати інші оператори порівняння, такі як більше чим >, менше чим <, та інші.
Наприклад, ми можемо знайти споживача в котрого платежі за замовлення більші ніж середнєстатистичні платежі.
SQL-підзапит виконує розрахунок середньостатистичного значення виплат, використовуючи агрегатну функцію AVG.
А зовнішній SQL-запит вибирає виплати, що більші за значення середньостатистичного розрахунку.

SQL-запит:

SELECT customerNumber,
       checkNumber,
      amount
FROM payments
WHERE amount > (
    SELECT AVG(amount)
    FROM payments)

MySQL підзапит

3. MySQL підзапит з операторами IN та NOT IN.

Якщо підзапит повертає більше ніж одне значення, Ви можете застосовувати такі оператори як IN та NOT IN в зовнішньому SQL-запиті.
Так, наприклад, можна вибрати дані споживачів, які не зробили ніяких замовлень.

SQL-запит:

SELECT customername
FROM customers
WHERE customerNumber NOT IN (
   SELECT DISTINCT customernumber
   FROM orders )

MySQL підзапит

4. MySQL підзапит з операторами EXISTS та NOT EXISTS.

Якщо SQL-підзапит застосовує оператори EXISTS чи NOT EXISTS, то він повертає значення TRUE чи FALSE. Тобто підзапит використовується для перевірки на наявність даних.

В поданому нижче прикладі, ми вибираємо список споживачів, що мають щонайменше одне замовлення з продажами більше ніж 10К.

Спочатку, ми перевіряємо чи є замовлення з сумою продажу товару більше ніж 10К.

SQL-запит:

SELECT priceEach * quantityOrdered
FROM orderdetails
WHERE priceEach * quantityOrdered > 10000
GROUP BY orderNumber;

MySQL підзапит

Запит повернув 6 записів, тому коли ми застосуємо слідуючий SQL-запит, він поверне результат - TRUE для відповідних записів.

SQL-запит:

SELECT customerName
FROM customers
WHERE EXISTS (
    SELECT priceEach * quantityOrdered
    FROM orderdetails
    WHERE priceEach * quantityOrdered > 10000
    GROUP BY orderNumber)

MySQL підзапит

Якщо змінити EXISTS на NOT EXISTS SQL-запит не поверне взагалі ніяких записів.



5. MySQL підзапит з операторами FROM

Якщо Ви застосуєте SQL-підзапит з оператором FROM, результуюча вибірка даних буде представлена у вигляді таблиці.
Ця таблиця називається похідною таблиці або матеріалізований підзапит.
Слідуючий підзапит знаходить максимум, мінімум та середнє значення кількості проданих елементів замовлень.

SQL-запит:

SELECT max(items),
       min(items),
      floor(avg(items))
FROM (
    SELECT orderNumber, 
           count(orderNumber) AS items
    FROM orderdetails
    GROUP BY orderNumber
) AS lineitems

Відмітьте собі, підзапит повертає слідуючу результуючу вибірку як похідну таблицю для зовнішнього SQL-запиту.

MySQL підзапит

6. MySQL взаємозв'язані підзапити

В попередніх прикладах, SQL-підзапит був незалежний, тобто як звичайний SQL-запит. Якщо ж SQL-підзапит використовує дані зовнішнього SQL-запиту, то такий підзапит буде зв'язаним, тобто розрахунок значень підзапиту залежить від даних зовнішнього SQL-запиту.

Зв'язаний підзапит виконується один раз для кожного рядка в зовнішньому запиті.

В поданому взаємозв'язаному SQL-підзапиті, ми вибираємо дані продукту, ціна котрого більша ніж середня ціна всіх продуктів.

SQL-запит:

SELECT productname,
       buyprice
FROM products AS p1
WHERE buyprice > ( 
    SELECT AVG(buyprice)
    FROM products
    WHERE productline = p1.productline
)

MySQL підзапит