49 - #SQL : Exercice d’application : Group by, Having & Fonctions SQL #SGBD #darija #database

CREATE DATABASE boutique; USE boutique; CREATE TABLE Orders ( order_id int NOT NULL AUTO_INCREMENT, customer_id int NOT NULL, order_date date NOT NULL, total_amount decimal(10,2) NOT NULL, country varchar(50) NOT NULL, PRIMARY KEY ( order_id ) ) ; INSERT INTO boutique.Orders (customer_id,order_date,total_amount,country) VALUES (101,'2023-12-01',200.50,'USA'), (102,'2023-12-02',1500.00,'USA'), (103,'2023-12-03',500.00,'UK'), (104,'2023-12-03',300.00,'UK'), (105,'2023-12-05',20000.00,'Germany'), (106,'2023-12-06',50.00,'France'), (107,'2023-12-07',800.00,'France'), (108,'2023-12-08',1200.00,'Canada'), (109,'2023-12-09',950.00,'Canada'), (110,'2023-12-10',220.00,'Australia'), (111,'2023-12-11',600.00,'Australia'), (112,'2023-12-12',400.00,'India'), (113,'2023-12-13',180.00,'India'), (114,'2023-12-14',1450.00,'USA'), (115,'2023-12-15',780.00,'Germany'), (116,'2023-12-16',900.00,'UK'), (117,'2023-12-17',350.00,'France'), (118,'2023-12-18',200.00,'Canada'), (119,'2023-12-19',275.00,'Australia'), (120,'2023-12-20',500.00,'India'), (121,'2023-12-21',100.00,'Germany'), (122,'2023-12-22',800.00,'USA'), (123,'2023-12-23',150.00,'France'), (124,'2023-12-24',750.00,'UK'), (125,'2023-12-25',3000.00,'Germany'), (126,'2023-12-26',50.00,'India'), (127,'2023-12-27',2750.00,'USA'), (128,'2023-12-28',600.00,'France'), (129,'2023-12-29',980.00,'Canada'), (130,'2023-12-30',150.00,'Australia'); 00:00 Introduction 00:45 Enoncé 03:00 Questions 05:13 Réponses 05:14 Question 1 10:14 Question 2 13:35 Question 3 17:07 Question 4 ------------------- RÉPONSES : SELECT country,COUNT(*) AS 'Count' FROM Orders o GROUP BY country; SELECT o.country,AVG(total_amount) as 'moyen',MAX(total_amount) as 'max',MIN(total_amount) as 'min' FROM Orders o GROUP BY country; SELECT country,SUM(total_amount) AS 'TOTAL' FROM Orders o GROUP BY country HAVING SUM(total_amount) SUP 10000; SELECT country,SUM(total_amount) AS 'TOTAL' FROM Orders o WHERE country !='Germany' GROUP BY country HAVING SUM(total_amount) SUP 3000;