Задачі з SQL

лютий 2015

Сontent

Оператор HAVING

Завдання

1. Отримати кількість моделей ПК та середню ціну кожної моделі, середня ціна якої становить менше 800$.
2. Знайти максимальну, мінімальну та середню ціну на ПК.
3. Знайти максимальну, мінімальну та середню ціну на ПК за умови що середня ціна не перевищує 700$.


Таблиця Product_PC     переглянути всю таблицю Product_PC Бази Даних

product_id modelprice
11121600
21121650
31121750
.........
2011231100

Трохи теорії щодо оператора GROUP BY та HAVING


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

    Оператор HAVING призначений для використання спільно з оператором GROUP BY для завдання обмежень, які вказані з метою відбору тих груп, які будуть розміщені в підсумковій таблиці запиту. Хоча оператори HAVING та WHERE мають схожий синтаксис, їх призначення відрізняються. Оператор WHERE призначений для фільтрації окремих рядків, які використовуються для групування або які розміщуються в підсумковій таблиці запита, тоді як оператор HAVING використовується для фільтрації груп, які розміщуються в підсумковій таблиці запита. Стандарт ISO вимагає, щоб імена стовпців, які використовуються в операторі HAVING, обов’язково були присутніми в списку оператора GROUP BY або використовувались в агрегатних функціях.

    На практиці умови пошуку в операторі HAVING завжди містять хоча б одну агрегатну функцію, в протилежному випадку ці умови пошуку повинні бути розміщені в операторі WHERE та використовуватися для відбору окремих рядків. Треба пам’ятати, що агрегатні функції не можуть використовуватися в операторі WHERE. Оператор HAVING не є необхідною частиною SQL – будь-який запит, написаний з використанням оператору HAVING, може бути зображений в іншому вигляді без його використання.

   Варто відмітити порядок опрацювання виразу SELECT:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

    Цей порядок дещо не відповідає синтаксичному порядку загального виразу SELECT ;

SELECT
[FROM table_references]
[WHERE where_condition]
[GROUP BY]
[HAVING where_condition]
[ORDER BY]
[LIMIT ]

    Оператор HAVING може використовуватись і без опратора GROUP BY. В такому випадку агрегатні функції застосовуються до всього результуючого набору рядків запиту.



Рішення завдання

Завдання 1. Отримати кількість моделей ПК та середню ціну кожної моделі, середня ціна якої становить менше 800$.

SQL-запит:

SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price  
FROM product_PC  
GROUP BY model  
HAVING AVG(price) < 800;

Об'єктно-орієнтовний стиль кодування php-частини:

<?php 
$con = new mysqli("host","my_user","passd","db");  
$result = $con->query(" 
SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price 
FROM product_PC  
GROUP BY model 
HAVING AVG(price) < 800;");

echo 'model: Кількість : Середня ціна';
while ($row = $result->fetch_assoc()){ 
  echo  $row['model'].' '. $row['Qty_model'].' '. $row['Avg_price']; 
}; $con->close(); ?>


Результат виконання php-частини:

model : Кількість : Середня ціна
1121 5 760.0000
1123 4 700.0000
1127 4 612.5000


Завдання 2. Знайти максимальну, мінімальну та середню ціну на ПК

Поданий нижче SQL-запит визначить максимальну, мінімальну та середню ціну на ПК з усієї вибірки даних.

SQL-запит:

SELECT MIN(price) AS min_price,
 MAX(price) AS max_price, 
 AVG(price) avg_price
FROM product_PC;


Об'єктно-орієнтовний стиль кодування php-частини:

<?php   
$con = new mysqli("host","user","pass","db");

$result = $con->query(" 
  SELECT MIN(price) AS min_price,
  MAX(price) AS max_price, 
  AVG(price) avg_price
  FROM product_PC;");

echo  ' min_price :  max_price  : avg_price ';
$row = $result->fetch_assoc(); 
echo  $row['min_price'].' '.
      $row['max_price'].' '. 
      $row['avg_price']; 

$con->close();
?>

Результат виконання php-частини:

min_price : max_price : avg_price
500 1000 770.4545

Завдання 3. Знайти максимальну, мінімальну та середню ціну на ПК за умови що середня ціна не перевищує 700$. >

Поданий нижче SQL-запит визначить максимальну, мінімальну та середню ціну на ПК з усієї вибірки даних, якщо середня ціна на ПК менше 700$, але поскільки середня ціна по вибірці складає 770.4545$, то SQL-запит поверне пусту вибірку.

SQL-запит:

SELECT MIN(price) AS min_price, 
MAX(price) AS max_price, AVG(price) avg_price
FROM product_PC
HAVING AVG(price) <= 700;


Повна таблиця "product_PC" з Бази Даних:


product_id : model : price
1   :   1121   :   600
2   :   1121   :   700
3   :   1121   :   800
4   :   1121   :   900
5   :   1121   :   800
6   :   1122   :   700
7   :   1122   :   800
8   :   1122   :   900
9   :   1122   :   1000
10   :   1123   :   600
11   :   1123   :   700
12   :   1123   :   700
13   :   1123   :   800
14   :   1124   :   800
15   :   1124   :   900
16   :   1125   :   900
17   :   1126   :   1000
18   :   1126   :   900
19   :   1127   :   600
20   :   1127   :   700
21   :   1127   :   500
22   :   1127   :   650