Задачі з SQL

лютий 2015

Сontent

Оператор JOIN

Завдання

Є дві таблиці:
- users - користувачі (users_id, name)
- orders - замовлення (orders_id, users_id, status)
1) Вибрати Id замовлення та відповідних користувачів з таблиці users, в яких записи в таблиці orders мають status = 0;
2) Вибрати користувачів в яких є невиконані замовлення, status = 0;
3) Вибрати Id, ім'я, та кількість замовлень всіх користувачів з таблиці users, в яких 3 і більше записів поля 'status' = '1' в таблиці orders

Таблиця users:

users_idname
1Alex
2Max
3Helena
4Mark
5Nick
6Pete
7Lora

таблиця orders:

order_id users_idstatus
10010
10121
10210
10330
.........
12041
12111


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

Переглянути про синтаксис оператора JOIN

Завдання частина 1

Вибрати Id замовлення та відповідних користувачів з таблиці users, в яких записи в таблиці orders мають status = 0

SQL-запит:

SELECT o.orders_id, o.users_id, u.users_name AS Name
FROM orders o 
JOIN users u 
ON o.users_id=u.users_id 
WHERE o.status = 0


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

<?php   
$con = new mysqli("host","user","pass","db");
$result = $con->query(" 
  SELECT o.orders_id , o.users_id ,u.users_name 
  FROM orders o 
  JOIN users u 
  ON o.users_id=u.users_id 
  WHERE o.status = 0 ");
  
echo 'Id замовлення, Users_ Id,Users_Name в якої status = 0.<br>'; 
echo 'Order_Id : Users_ Id : Users_Name <br> '; 

while ($row = $result->fetch_assoc()){ 
  echo $row['orders_id'].' '.$row['users_id'].' '.$row['users_name'];
};

$con->close();
?>


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

Id замовлення, Users_ Id, Users_Name в якої status = 0.
Order_Id : Users_ Id : Users_Name
100      1      Alex
101      1      Alex
102      2      Max
103      3      Helena
104      1      Alex
106      3      Helena
107      1      Alex
108      2      Max
111      2      Max
112      2      Max
113      3      Helena


Завдання частина 2

Вибрати всіх користувачів з таблиці users, в яких ВСІ записи в таблиці orders мають status = 0;

SQL-запит:

SELECT u.users_id AS Id,u.users_name AS Name 
FROM orders o 
 JOIN users u      
 ON o.users_id=u.users_id 
WHERE o.status = 0 
GROUP BY o.users_id;


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

<?php   
$con = new mysqli("host","user","pass","db"); $result = $con->query(" SELECT u.users_id AS Id,u.users_name AS Name FROM orders o JOIN users u ON o.users_id=u.users_id WHERE o.status = 0 GROUP BY o.users_id ;"); echo 'Id, Name Людини в якої status = 0.<br>'; echo 'Id : Name <br>'; while ($row = $result->fetch_assoc()){ echo $row['Id'].' '. $row['Name']; }; $con->close(); ?>

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

Id, Name Людини в якої status = 0.
Id : Name
1 Alex
2 Max
3 Helena


Завдання частина 3

Вибрати Id, ім'я, та кількість замовлень всіх користувачів з таблиці users, в яких більше 3 записів поля 'status' = '1' в таблиці orders

SQL-запит:

SELECT u.users_id AS Id,
       u.users_name AS Name, 
       COUNT(o.status) AS Count 
FROM orders o 
JOIN users u 
ON o.users_id=u.users_id 
WHERE o.status = 1 
GROUP BY o.users_id 
HAVING COUNT(o.status) >= 3


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

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

$result = $con->query(" 
SELECT u.users_id AS Id,u.users_name AS Name, COUNT(o.status) AS Count
FROM orders o
JOIN users u
ON o.users_id=u.users_id
WHERE o.status = 1
GROUP BY o.users_id
HAVING COUNT(o.status) >= 3 ;"); echo 'Id, Name Людини в якої status = 1 та кількість замовлень 3 та більше'; echo 'Id : Name :Кількість <br>'; while ($row = $result->fetch_assoc()){ echo $row['Id'].' '. $row['Name'].' '. $row['Count']; }; $con->close(); ?>


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

Id, Name Людини в якої status = 1 та кількість замовлень 3 та більше. Id : Name : Кількість
2 Max 3
3 Helena 5


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

users_id : name
1   :   Alex
2   :   Max
3   :   Helena
4   :   Mark
5   :   Nick
6   :   Pete
7   :   Lora

таблиця "orders:"


orders_id : users_id : status
100   :   1   :   0
101   :   1   :   0
102   :   2   :   0
103   :   3   :   0
104   :   1   :   0
105   :   2   :   1
106   :   3   :   0
107   :   1   :   0
108   :   2   :   0
109   :   3   :   1
110   :   4   :   1
111   :   2   :   0
112   :   2   :   0
113   :   3   :   0
114   :   5   :   1
115   :   4   :   1
116   :   3   :   1
117   :   3   :   1
118   :   3   :   1
119   :   3   :   1
120   :   2   :   1
121   :   2   :   1