/*********************************************
EXERCICE SQL 
/*********************************************

/---------------------------------------------
Requete 1
/---------------------------------------------
Notions : ORDER BY
/* La liste des bureaux (adresse et ville) triés par pays décroissant puis par état */

SELECT addressLine1, addressLine2, city, country, state
FROM offices
ORDER BY country DESC, state

/* RESULTAT ==> 7 lignes / 100 Market Street */


/---------------------------------------------
Requete 2
/---------------------------------------------
Notions : WHERE -> https://sql.sh/cours/where
/* La liste des avions (code et nom) triés par vendeur et par quantité en stock décroissants */

SELECT `productName`,`productCode` 
FROM `products` 
WHERE `productLine` = 'Planes' 
ORDER BY `productVendor` DESC, `quantityInStock` DESC

/* RESULTAT ==> 12 lignes / 1900s Vintage Tri-Plane */

/---------------------------------------------
Requete 3
/---------------------------------------------
Notions : WHERE -> https://sql.sh/cours/where : Opérateurs de comparaisons
< : inferieur 
<= : inferieur ou egal 
> : superieur 
>= : superieur ou egal 
!= , <> : different 
/* La liste des produits (nom, vendeur et prix de vente) qui sont vendus au moins 132$ triés par nom du produit */

SELECT `productName`, `productVendor`, `MSRP` 
FROM `products` 
WHERE `MSRP` >= 132 
ORDER BY `productName` DESC

/* RESULTAT ==> 24 lignes / 1903 Ford Model A */

/---------------------------------------------
Requete 4
/---------------------------------------------
*/La liste des produits (code, nom, échelle et quantité) qui ont une échelle soit de 1:10, soit de 1:18 triés par quantité en stock décroissante */

SELECT `productCode`, `productName`, `productScale`, `quantityInStock`
FROM `products` 
WHERE `productScale` IN ( "1:10", "1:18" ) /* équivalent à WHERE productScale = '1:10' OR productScale = '1:18' */
ORDER BY `quantityInStock` DESC

/* RESULTAT ==> 48 lignes / 1995 Honda Civic */

/---------------------------------------------
Requete 5
/---------------------------------------------
*/La liste des produits (code, nom et prix d'achat) des produits achetés au moins 60$ au plus 90$ triés par prix d'achat */

SELECT `productCode`, `productName`, `buyPrice`
FROM `products` 
WHERE `buyPrice` BETWEEN '60' AND '90' /* équivalent à WHERE buyPrice >= 60 AND buyPrice <= 90 */
ORDER BY `buyPrice`

/* RESULTAT ==> 34 lignes / 1937 Lincoln Berline */

/---------------------------------------------
Requete 6
/---------------------------------------------
Notions : Alias AS -> https://sql.sh/cours/alias

SELECT productCode, (prix dachat - prix de vente ) AS marge 
AS il ne se mets pas dans le where 
mais il peut se mettre dans le ORDER BY 

*/La liste des motos (nom, vendeur, quantité et marge) triés par marge décroissante */

SELECT `productName`, `productVendor`, `quantityInStock`, (`MSRP`-`buyPrice`) AS `marge`
FROM `products` 
WHERE `productLine` = "Motorcycles"
ORDER BY `marge` DESC

/* RESULTAT ==> 13 lignes / 2003 Harley-Davidson Eagle Drag Bike */


/---------------------------------------------
Requete 7
/---------------------------------------------
Notions :
Alias AS -> https://sql.sh/cours/alias
OR & AND -> https://sql.sh/cours/where/and-or
/* La liste des commandes (numéro, date de commande, date d'expédition, écart en jours entre les deux dates et statut) qui sont en cours de traitement ou qui ont été expédiées et ont un écart de plus de 10j triés par écart décroissant puis par date de commande */

SELECT `orderNumber`, `orderDate`, `shippedDate`, (`shippedDate` - `orderDate`) AS `processTime`, `status`
FROM `orders` 
WHERE `status` = "In Process" 
OR (`shippedDate` - `orderDate`) > "10"
ORDER BY `processTime`DESC, `orderDate`


/* RESULTAT ==> 33 lignes / 10165 */


/---------------------------------------------
Requete 8
/---------------------------------------------
Notions : LIKE -> https://sql.sh/cours/where/like
/*La liste des produits (nom et valeur du stock à la vente) des années 1960 */

SELECT `productName`, (`quantityInStock` * `MSRP`) AS `stockValue`
FROM `products` 
WHERE `productName` LIKE '196%'

/* RESULTAT ==> 16 lignes / 1969 Harley Davidson Ultimate Chopper */


/---------------------------------------------
Requete 9
/---------------------------------------------
Notions : AVG -> https://sql.sh/fonctions/agregation/avg
La fonction d’agrégation AVG() dans le langage SQL permet de calculer une valeur moyenne
/* Le prix moyen d'un produit vendu par chaque vendeur triés par prix moyen décroissant */

SELECT AVG(`MSRP`) AS `averagePrice`, `productVendor`
FROM `products` 
GROUP BY `productVendor`
ORDER BY `averagePrice` DESC

/* RESULTAT ==> 13 lignes / Welly Diecast Productions / 113.9325 */



/---------------------------------------------
Requete 10
/---------------------------------------------
Notions : COUNT -> https://sql.sh/fonctions/agregation/count
la fonction d’agrégation COUNT() permet de compter le nombre d’enregistrement dans une table.
/* Le nombre de produits pour chaque ligne de produit */

SELECT productLine, COUNT(`*`)
FROM `products`
GROUP BY `productLine`

/* RESULTAT ==> 7 lignes / Classic Cars / 38 */


/---------------------------------------------
Requete 11
/---------------------------------------------
Notions : SUM -> https://sql.sh/fonctions/agregation/sum
la fonction d’agrégation SUM() permet de calculer la somme totale d’une colonne contenant des valeurs numériques.
/* Le total du stock et le total de la valeur du stock à la vente de chaque ligne de produit pour les produits vendus plus de 100$ trié par total de la valeur du stock à la vente */

SELECT `productLine`, SUM(`quantityInStock`) AS `totalStock`, SUM(`MSRP` * `quantityInStock`) AS `stockValue`
FROM `products` 
WHERE `MSRP` > "100"
GROUP BY `productLine`
ORDER BY `stockValue`


/* RESULTAT ==> 7 lignes / Ships / 429177.74 */


/---------------------------------------------
Requete 12
/---------------------------------------------
Notions : GROUP BY -> https://sql.sh/cours/group-by
La commande GROUP BY est utilisée en SQL pour grouper plusieurs résultats
A noter : cette commande doit toujours s’utiliser après la commande WHERE et avant la commande HAVING
Notions : MAX -> https://sql.sh/fonctions/agregation/max
la fonction d’agrégation MAX() permet de retourner la valeur maximale d’une colonne
/* La quantité du produit le plus en stock de chaque vendeur trié par vendeur */

SELECT `productVendor`, MAX(`quantityInStock`) 
FROM `products` 
GROUP BY `productVendor`
ORDER BY `productVendor`



/* RESULTAT ==> 13 lignes / Autoart Studio Design / 9354 */


/---------------------------------------------
Requete 13
/---------------------------------------------
Notions : GROUP BY -> https://sql.sh/cours/group-by
La commande GROUP BY est utilisée en SQL pour grouper plusieurs résultats
A noter : cette commande doit toujours s’utiliser après la commande WHERE et avant la commande HAVING
Notions : MIN -> https://sql.sh/fonctions/agregation/min
La fonction d’agrégation MIN() permet de retourner la plus petite valeur d’une colonne sélectionnée
/* Le prix de l'avion qui coûte le moins cher à l'achat */

SELECT MIN(`MSRP`)
FROM `products` 
WHERE `productLine` = "Planes"



/* RESULTAT ==> 1 ligne / 29.34$ */


/---------------------------------------------
Requete 14
/---------------------------------------------
Notions : HAVING -> https://sql.sh/cours/having
équivalent à WHERE à la différence que HAVING permet d'utiliser des fonctions telles que SUM(), COUNT(),...
A noter : une donnée avec un AS dans le select peut etre utilisé avec son alias dans un HAVING 
et le HAVING se met apres un GROUP BY (souvent utilisé ensemble )
/* Le crédit des clients qui ont payé plus de 20000$ durant l'année 2004 trié par crédit décroissant */


SELECT `customerNumber`, sum(`amount`) AS TotalCredit  
FROM `payments` 
WHERE `paymentDate` LIKE '2004%'
GROUP BY `customerNumber`
HAVING TotalCredit > 20000
ORDER BY TotalCredit DESC




/* RESULTAT ==> 69 lignes / 141 / 293 765.51 */

/---------------------------------------------
Requete 15
/---------------------------------------------
Notions : INNER JOIN-> https://sql.sh/cours/jointures/inner-join
La commande INNER JOIN permet lier plusieurs tables entre-elles. 
A noter : INNER JOIN s'utilise avec ON ou WHERE mais on preferera le ON, 
deplus la jointure se fait avant la clause WHERE
/* La liste des employés (nom, prénom et fonction) et des bureaux (adresse et ville) dans lequel ils travaillent */

SELECT `firstName`,`lastName`,`jobTitle`,employees.officeCode,offices.addressLine1,offices.addressLine2,offices.city
FROM employees
INNER JOIN offices
ON employees.officeCode = offices.officeCode



/* RESULTAT ==> 23 lignes / Diane Murphy */

/---------------------------------------------
Requete 16
/---------------------------------------------
Notions : INNER JOIN-> https://sql.sh/cours/jointures/inner-join
La commande INNER JOIN permet lier plusieurs tables entre-elles. 
/* La liste des clients français ou américains (nom du client, nom, prénom du contact et pays) 
et de leur commercial dédié (nom et prénom) triés par nom et prénom du contact */

SELECT `customerName`, `contactLastName`, `contactFirstName`, `country`, `LastName`, `FirstName`
FROM customers
INNER JOIN employees 
ON customers.salesRepEmployeeNumber = employees.employeeNumber
WHERE customers.country IN('France', 'USA')
ORDER BY `contactLastName`, `contactFirstName`



/* RESULTAT ==> 48 lignes / Miguel Barajas */

/---------------------------------------------
Requete 17
/---------------------------------------------
Notions : INNER JOIN-> https://sql.sh/cours/jointures/inner-join
La commande INNER JOIN permet lier plusieurs tables entre-elles. 
/* La liste des lignes de commande (numéro de commande, code, nom et ligne de produit) 
et la remise appliquée aux voitures ou motos commandées triées par numéro de commande puis par remise décroissante */


SELECT `orderNumber`, products.productCode, products.productName, products.productLine, (MSRP-`priceEach`) AS remise
FROM `orderdetails`
INNER JOIN products
ON orderdetails.`productCode`= products.productCode
WHERE productline IN('Classic Cars','MotorCycles', 'Vintage Cars')
ORDER BY `orderNumber`, remise DESC



/* RESULTAT ==> 2026 lignes / 34 */


/---------------------------------------------
Requete 18
/---------------------------------------------
A SAVOIR : l'ordre dans une requete de selection : 
-> SELECT / FROM / INNER JOIN ON / WHERE / GROUP BY / HAVING / ORDER BY

/* Le total des paiements effectués de chaque client (numéro, nom et pays) américain, allemand ou français 
de plus de 50000$ trié par pays puis par total des paiements décroissant */

SELECT customers.customerNumber, customerName, country, SUM(amount) AS totalPayment
FROM customers
INNER JOIN payments ON payments.customerNumber = customers.customerNumber
WHERE country IN ('France', 'Germany', 'USA')
GROUP BY customers.customerNumber, customerName, country
HAVING totalPayment > 50000
ORDER BY country, totalPayment DESC


/* RESULTAT ==> 38 lignes / 146 / 130305.35 */


/---------------------------------------------
Requete 19
/---------------------------------------------
A SAVOIR : l'ordre dans une requete de selection : 
-> SELECT / FROM / INNER JOIN ON / WHERE / GROUP BY / HAVING / ORDER BY
/* Le montant total de chaque commande (numéro et date) des clients New-Yorkais (nom) trié par nom du client 
puis par date de commande */

SELECT customerName, orders.orderNumber, orderDate, SUM(quantityOrdered * priceEach) AS totalAmount
FROM customers
INNER JOIN orders ON orders.customerNumber = customers.customerNumber
INNER JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber
WHERE city = 'NYC'
GROUP BY city, customerName, orderNumber, orderDate
ORDER BY customerName, orderDate



/* RESULTAT ==> 16 lignes / Classic Legends / 10115 / 21665.98 */