chinook-queries.sql

/*************************************************************************************************
   Chinook Database - Version 1.4.5
   Script: Chinook_MySql.sql
   DB Server: MySql
   Author: Grace TSOUALLA
   Description: Answers to exercises of Project << Automatiser les rapports de ventes avec SQL >>
**************************************************************************************************/

/*************************************************************************************************
Requêtes de base

 1.Clients non américains : Fournissez une requête affichant les
 Clients (leurs noms complets, ID client et pays) qui ne sont pas aux
 États-Unis.

 2.  Clients brésiliens : Fournissez une requête affichant uniquement
 les Clients provenant du Brésil.

 3. Factures des clients brésiliens : Fournissez une requête affichant
 les factures des clients qui sont du Brésil.
 Le tableau résultant doit inclure le nom complet du client, l'ID de la
 facture, la date de la facture et le pays de facturation.

 4. Agents de vente : Fournissez une requête affichant uniquement
 les employés qui sont des Agents de Vente
**************************************************************************************************/

SELECT `customer`.`CustomerId`, `customer`.`FirstName`, `customer`.`LastName`, `customer`.`Country`
FROM `chinook`.`customer`
WHERE `customer`.`Country` <> 'USA';


SELECT `customer`.`CustomerId`, `customer`.`FirstName`, `customer`.`LastName`, `customer`.`Country`
FROM `chinook`.`customer`
WHERE `customer`.`Country` = 'Brazil';


SELECT `FirstName`, `LastName`, `InvoiceId`, `InvoiceDate`, `BillingCountry`
FROM `chinook`.`customer` C
	INNER JOIN `chinook`.`invoice` I 
    ON C.`CustomerId` = I.`CustomerId`
WHERE C.`Country` = 'Brazil';


SELECT `employee`.`EmployeeId`, `employee`.`LastName`, `employee`.`FirstName`, `employee`.`Title`
FROM `chinook`.`employee`
WHERE `employee`.`Title` = 'Sales Support Agent';


/*************************************************************************************************
Agrégations et relations
 5. Pays uniques dans les factures : Fournissez une requête affichant
 une liste unique des pays de facturation présents dans la table Invoice.

 6. Factures par agent de vente : Fournissez une requête affichant les
 factures associées à chaque agent de vente.
 Le tableau résultant doit inclure le nom complet de l'agent de
 vente.

 7. Détails des factures : Fournissez une requête affichant le total de
 chaque facture, le nom du client, le pays et le nom de l'agent de vente.
**************************************************************************************************/

SELECT DISTINCT `invoice`.`BillingCountry`
FROM `chinook`.`invoice`;


SELECT E.`LastName`, E.`FirstName`, I.`InvoiceId`
FROM `chinook`.`employee` E
	LEFT JOIN `chinook`.`customer` C
    ON E.`EmployeeId` = C.`SupportRepId`
    LEFT JOIN `chinook`.`invoice` I
    ON I.`CustomerId` = C.`CustomerId`
WHERE E.`Title` = 'Sales Support Agent';


SELECT I.`InvoiceId`, I.`Total`, C.`LastName` CustomerName, E.`LastName` EmployeeName, E.`Country` EmployeeCountry
FROM `chinook`.`invoice` I
	INNER JOIN `chinook`.`customer` C
    ON I.`CustomerId` = C.`CustomerId`
    INNER JOIN `chinook`.`employee` E 
    ON E.`EmployeeId` = C.`SupportRepId`
WHERE E.`Title` = 'Sales Support Agent';



/*************************************************************************************************
Analyse par année et lignes de facture

 8. Ventes par année : Combien de factures y a-t-il eu en 2009 et
 2011 ? Quels sont les montants totaux des ventes pour chacune de
 ces années ?

 9. Articles pour une facture donnée : Fournissez une requête
 comptant le nombre d'articles (line items) pour l'ID de facture 37.

 10. Articles par facture : Fournissez une requête comptant le nombre
 d'articles (line items) pour chaque facture.
 Astuce : utilisez GROUP BY.
**************************************************************************************************/

SELECT YEAR(`InvoiceDate`) Invoice_Year, COUNT(`InvoiceId`) Number_of_Bills, SUM(`Total`) Total_Sales_Amount
FROM `chinook`.`invoice`
WHERE YEAR(`InvoiceDate`) IN (2009, 2011)
GROUP BY YEAR(`InvoiceDate`);


SELECT I.`InvoiceId`, COUNT(IL.`InvoiceId`) Number_of_Items
FROM `chinook`.`invoice` I
	INNER JOIN `chinook`.`invoiceline` IL
    ON I.`InvoiceId` = IL.`InvoiceId`
WHERE I.`InvoiceId` = 37;


SELECT I.`InvoiceId`, COUNT(IL.`InvoiceId`) Number_of_Items
FROM `chinook`.`invoice` I
	INNER JOIN `chinook`.`invoiceline` IL
    ON I.`InvoiceId` = IL.`InvoiceId`
GROUP BY I.`InvoiceId`;


/*************************************************************************************************
Détails des morceaux
 11. Nom des morceaux : Fournissez une requête incluant le nom du
 morceau pour chaque ligne de facture.

 12. Morceaux et artistes : Fournissez une requête incluant le nom du
 morceau acheté ET le nom de l'artiste pour chaque ligne de facture.
**************************************************************************************************/

SELECT `InvoiceLineId`, `InvoiceId`, I.`TrackId`, I.`UnitPrice`, `Quantity`, `Name`
FROM `chinook`.`invoiceline` I
	INNER JOIN `chinook`.`track` T
    ON I.`TrackId` = T.`TrackId`;


SELECT `InvoiceLineId`, `InvoiceId`, I.`TrackId`, I.`UnitPrice`, `Quantity`, T.`Name` Track_Title, Art.`Name` Artist_Name
FROM `chinook`.`invoiceline` I
	INNER JOIN `chinook`.`track` T
    ON I.`TrackId` = T.`TrackId`
    INNER JOIN `chinook`.`album` A
    ON A.`AlbumId` = T.`AlbumId`
    INNER JOIN `chinook`.`artist` Art
    ON Art.`ArtistId` = A.`ArtistId`
ORDER BY `InvoiceLineId`;



/*************************************************************************************************
 Comptages et regroupements
 13. Nombre de factures par pays : Fournissez une requête affichant
 le nombre de factures par pays.
 Astuce : utilisez GROUP BY.

 14. Nombre de morceaux par playlist : Fournissez une requête
 affichant le nombre total de morceaux dans chaque playlist.
 Le nom de la playlist doit être inclus dans le tableau résultant.

 15. Liste des morceaux : Fournissez une requête affichant tous les
 morceaux (Tracks), mais sans afficher les IDs.
 Le tableau résultant doit inclure le nom de l'album, le type de média
 et le genre.
**************************************************************************************************/

SELECT `BillingCountry`, COUNT(`InvoiceId`) Number_of_Invoices
FROM `chinook`.`invoice`
GROUP BY `BillingCountry`;


SELECT P.`Name` Playlist_Name, COUNT(PT.`TrackId`) Number_of_Tracks
FROM `chinook`.`playlist` P 
	LEFT JOIN `chinook`.`playlisttrack` PT
    ON PT.`PlaylistId` = P.`PlaylistId`
GROUP BY P.`Name`;


SELECT T.`Name` Track , A.`Title` Album_Title, M.`Name` Mediatype, G.`Name` Genre
FROM `chinook`.`track` T
	LEFT JOIN `chinook`.`album` A    /* NB : Il faudra peut etre remplacer LEFT par INNER */
    ON T.`AlbumId` = A.`AlbumId`
    LEFT JOIN `chinook`.`mediatype` M
    ON T.`MediaTypeId` = M.`MediaTypeId`
    LEFT JOIN `chinook`.`genre` G
    ON T.`GenreId` = G.`GenreId`;


/*************************************************************************************************
Analyse des ventes
 16. Factures et articles : Fournissez une requête affichant toutes les
 factures, avec le nombre d'articles par facture.

 17. Ventes par agent de vente : Fournissez une requête affichant les
 ventes totales réalisées par chaque agent de vente.

 18. Meilleur agent de 2009 : Quel agent de vente a réalisé le plus de
 ventes en 2009 ?

 19. Meilleur agent de 2010 : Quel agent de vente a réalisé le plus de
 ventes en 2010 ?

 20. Meilleur agent global : Quel agent de vente a réalisé le plus de
 ventes en tout ?
**************************************************************************************************/

SELECT `InvoiceId`, COUNT(`TrackId`) Number_of_articles
FROM `chinook`.`invoiceline`
GROUP BY `InvoiceId`;


SELECT E.`LastName` EmployeeName, SUM(I.`Total`) Total_Amount_of_Sales
FROM `chinook`.`invoice` I
	INNER JOIN `chinook`.`customer` C
    ON I.`CustomerId` = C.`CustomerId`
    INNER JOIN `chinook`.`employee` E 
    ON E.`EmployeeId` = C.`SupportRepId`
WHERE E.`Title` = 'Sales Support Agent'
GROUP BY E.`LastName`;


SELECT I.`InvoiceDate` Invoice_Year, E.`LastName` Employee_Last_Name, E.`FirstName` Employee_First_Name, COUNT(I.`InvoiceId`) Number_of_Sales
FROM `chinook`.`invoice` I
	INNER JOIN `chinook`.`customer` C
    ON I.`CustomerId` = C.`CustomerId`
    INNER JOIN `chinook`.`employee` E 
    ON E.`EmployeeId` = C.`SupportRepId`
WHERE E.`Title` = 'Sales Support Agent' AND YEAR(I.`InvoiceDate`) = 2009
GROUP BY I.`InvoiceDate`, E.`LastName`, E.`FirstName`
ORDER BY Number_of_Sales DESC
LIMIT 1;


SELECT I.`InvoiceDate` Invoice_Year, E.`LastName` Employee_Last_Name, E.`FirstName` Employee_First_Name, COUNT(I.`InvoiceId`) Number_of_Sales
FROM `chinook`.`invoice` I
	INNER JOIN `chinook`.`customer` C
    ON I.`CustomerId` = C.`CustomerId`
    INNER JOIN `chinook`.`employee` E 
    ON E.`EmployeeId` = C.`SupportRepId`
WHERE E.`Title` = 'Sales Support Agent' AND YEAR(I.`InvoiceDate`) = 2010
GROUP BY I.`InvoiceDate`, E.`LastName`, E.`FirstName`
ORDER BY Number_of_Sales DESC
LIMIT 1;


SELECT E.`LastName` Employee_Last_Name, E.`FirstName` Employee_First_Name, COUNT(I.`InvoiceId`) Number_of_Sales
FROM `chinook`.`invoice` I
	INNER JOIN `chinook`.`customer` C
    ON I.`CustomerId` = C.`CustomerId`
    INNER JOIN `chinook`.`employee` E 
    ON E.`EmployeeId` = C.`SupportRepId`
WHERE E.`Title` = 'Sales Support Agent'
GROUP BY E.`LastName`, E.`FirstName`
ORDER BY Number_of_Sales DESC
LIMIT 1;


/*************************************************************************************************
Analyse des clients et des pays
 21. Clients par agent de vente : Fournissez une requête affichant le
 nombre de clients attribués à chaque agent de vente.

 22. Ventes totales par pays : Fournissez une requête affichant les
 ventes totales par pays.
 Quel pays a dépensé le plus ?
**************************************************************************************************/

SELECT E.`FirstName` Employee_First_Name, E.`LastName` Employee_Last_Name, COUNT(`CustomerId`) Number_of_Customer
FROM `chinook`.`customer` C
	RIGHT JOIN `chinook`.`employee` E
    ON E.`EmployeeId` = C.`SupportRepId`
WHERE E.`Title` = 'Sales Support Agent'
GROUP BY E.`FirstName`, E.`LastName`;


SELECT `BillingCountry`, SUM(`Total`) Amount_of_Sales
FROM `chinook`.`invoice`
GROUP BY `BillingCountry`
ORDER BY Amount_of_Sales DESC;


/*************************************************************************************************
Analyse des morceaux et des artistes
 23. Morceau le plus acheté en 2013 : Fournissez une requête
 affichant le morceau le plus acheté en 2013.
 24. Top 5 des morceaux les plus achetés : Fournissez une requête
 affichant les 5 morceaux les plus achetés en tout.
 25. Top 3 des artistes les plus vendus : Fournissez une requête
 affichant les 3 artistes les plus vendus.
 26. Type de média le plus acheté : Fournissez une requête affichant
 le type de média le plus acheté.
**************************************************************************************************/

SELECT IL.`TrackId`, T.`Name`, COUNT(IL.`TrackId`) Number_of_Orders
FROM `chinook`.`invoiceline` IL
	INNER JOIN `chinook`.`Invoice` I
    ON I.`InvoiceId` = IL.`InvoiceId`
    INNER JOIN `chinook`.`track` T
    ON T.`TrackId` = IL.`TrackId`
WHERE YEAR(I.`InvoiceDate`) = 2013
GROUP BY IL.`TrackId`, T.`Name`
ORDER BY Number_of_Orders
LIMIT 1;


SELECT IL.`TrackId`, T.`Name`, COUNT(IL.`TrackId`) Number_of_Orders
FROM `chinook`.`invoiceline` IL
    INNER JOIN `chinook`.`track` T
    ON T.`TrackId` = IL.`TrackId`
GROUP BY IL.`TrackId`, T.`Name`
ORDER BY Number_of_Orders DESC, T.`Name`
LIMIT 5;


SELECT Art.`Name`, COUNT(IL.`TrackId`) Number_of_Orders
FROM `chinook`.`invoiceline` IL
    INNER JOIN `chinook`.`track` T
    ON T.`TrackId` = IL.`TrackId`
    INNER JOIN `chinook`.`album` A
    ON A.`AlbumId` = T.`AlbumId`
    INNER JOIN `chinook`.`artist` Art
    ON Art.`ArtistId` = A.`ArtistId`
GROUP BY Art.`Name`
ORDER BY Number_of_Orders DESC, Art.`Name`
LIMIT 3;


SELECT M.`Name`, COUNT(T.`MediaTypeId`) Number_of_Orders
FROM `chinook`.`invoiceline` IL
    INNER JOIN `chinook`.`track` T
    ON T.`TrackId` = IL.`TrackId`
    INNER JOIN `chinook`.`mediatype` M
    ON M.`MediaTypeId` = T.`MediaTypeId`
GROUP BY M.`Name`
ORDER BY Number_of_Orders DESC, M.`Name`
LIMIT 1;