
/* 1.La liste des bureaux (adresse et ville) triés par pays décroissant puis par état*/

SELECT addressLine1, addressLine2, city
FROM offices
ORDER BY country DESC, state
/* RESULTAT ==> 7 lignes / 100 Market Street */


/* 2.La liste des avions (code et nom) triés par vendeur et par quantité en stock décroissants*/

SELECT productName, productCode,productLine, productVendor, quantityInStock
FROM products 
WHERE productLine = "Planes"
ORDER BY productVendor, quantityInStock DESC
/* RESULTAT ==> 12 lignes */


/* 3.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
/* RESULTAT ==> 24 lignes */


/* 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')
ORDER BY quantityInStock DESC
/* RESULTAT ==> 48 lignes */


/* 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
ORDER BY buyPrice
/* RESULTAT ==> 34 lignes */


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

SELECT productName, productVendor, quantityInStock, (MSRP - buyPrice) as profit
FROM products
WHERE productLine = "MotorCycles"
ORDER BY profit DESC
/* RESULTAT ==> 13 lignes */


/* 7.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, DATEDIFF(shippedDate, orderDate) as delay, status
FROM orders
WHERE status = "In Process" OR (status ="Shipped" AND DATEDIFF(shippedDate, orderDate) > 10)
ORDER BY delay DESC, orderDate
/* RESULTAT ==> 7 lignes */


/* 8.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 BETWEEN 1960 AND 1969
/* WHERE productName LIKE "196%" */
ORDER BY stockValue
/* RESULTAT ==> 16 lignes */


/* 9.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 */


/* 10.Le nombre de produits pour chaque ligne de produit */

SELECT productLine, COUNT(productLine) AS totalProduct
FROM products
GROUP BY productLine
/* RESULTAT ==> 7 lignes */


/* 11.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 totalQuantity, SUM(quantityInStock * MSRP) AS totalValueInStock
FROM products
WHERE MSRP > 100
GROUP BY productLine
ORDER BY totalValueInStock
/* RESULTAT ==> 7 lignes */


/* 12 - La quantité du produit le plus en stock de chaque vendeur trié par vendeur */

SELECT productVendor, MAX(quantityInStock) AS maxInStock
FROM products
GROUP BY productVendor
ORDER BY productVendor
/* RESULTAT ==> 13 lignes */


/* 13 - Le prix de l'avion qui coûte le moins cher à l'achat */

SELECT MIN(buyPrice) AS cheapestPrice
FROM products
WHERE productLine = "Planes"
/* RESULTAT ==> 1 lignes */

/* 14 - 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 credit
FROM payments
WHERE paymentDate BETWEEN "2004-01-01" AND "2005-01-01"
GROUP BY customerNumber
HAVING credit > 20000
ORDER BY credit DESC
/* RESULTAT ==> 69 lignes */


/*OU (avec jointures)*/

SELECT customerName, addressLine2, country, city,  payments.customerNumber, SUM(amount) as credit
FROM payments
JOIN customers ON payments.customerNumber = customers.customerNumber
WHERE paymentDate BETWEEN '2004-01-01' AND '2005-01-01'
GROUP BY customerNumber
HAVING credit > 20000
ORDER BY credit DESC;
/* RESULTAT ==> 69 lignes */


/* 15 - La liste des employés (nom, prénom et fonction) et des bureaux (adresse et ville) dans lequel ils travaillent */

SELECT lastName, firstName, jobTitle, addressLine1, addressLine2, city
FROM employees
JOIN offices ON employees.officeCode = offices.officeCode;
/* RESULTAT ==> 23 lignes */


/* 16 - 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, contactFirstName, contactLastName, country, firstName, lastName
FROM customers
JOIN employees ON salesRepEmployeeNumber = employeeNumber
WHERE country IN ("USA", "France")
ORDER BY contactFirstName, contactLastName
/* RESULTAT ==> 48 lignes */


/* 17 - 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, productName, productLine, (MSRP - priceEach) AS discount
FROM orderdetails
JOIN products ON products.productCode = orderdetails.productCode
WHERE productLine IN ('Classic Cars', 'MotorCycles', 'Vintage Cars')
ORDER BY orderNumber, discount DESC
/* RESULTAT ==> 2026 lignes */


/* 18 - 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 payments.customerNumber, customerName, country, SUM(amount) AS totalPayement
FROM payments
JOIN customers ON payments.customerNumber = customers.customerNumber
WHERE country IN ('France', 'USA', 'Germany')
GROUP BY payments.customerNumber, customerName, country
HAVING totalPayement > 50000
ORDER BY country, totalPayement DESC
/* RESULTAT ==> 38 lignes */


/* 19 - 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 orders.orderNumber, orderDate, customerName, SUM(priceEach * quantityOrdered) AS totalAmount
FROM orderdetails
JOIN orders ON orders.orderNumber = orderdetails.orderNumber
JOIN customers ON orders.customerNumber = customers.customerNumber
WHERE city = "NYC"
GROUP BY orders.orderNumber
ORDER BY customerName, orderDate
/* RESULTAT ==> 16 lignes */