On va dans un premier temps se familiariser avec les requêtes SQL sur des bases de données utilisant le logiciel SQLite. On travaillera sur trois bases :
-
eleves.db: base de données très basique, constituée d’une seule table, décrivant les élèves d’un établissement scolaire. -
clients.db: base de données décrivant des opérations d’une banque, et constituée de trois tables :client,compte,operation. -
chinook.db: base de donnée proposée par les concepteurs de SQLite à des fins de tests. Elle est plus complexe et volumineuse que les deux premières.
Ces trois bases se trouvent dans le répertoire /users/partage. Si vous travaillez sur les machines locales, vous n’avez pas besoin de recopier ces données dans votre répertoire.
Elles se trouvent aussi dans le répertoire data pour ceux qui travaillent sur leur propre machine.
On se connectera sur une base par l’utilitaire sqlite3 en ligne de commande en tapant une commande
sqlite3 chemin_vers_base.dbOn trouvera de l’aide sur sqlite3 sur la page https://www.sqlite.org/cli.html . Dans l’interface en ligne de commande, on peut entrer :
-
soit des commandes SQL
-
soit des commandes propres à sqlite3, qui sont toutes préfixées par un point :
.helpou.quitpar exemple.
Par moment, on utilisera aussi l’utilitaire graphique sqlitebrowser.
La base eleves
La base clients
La base chinook
Explorer la base chinook.db et essayez de comprendre son architecture.
On reviendra plus loin dessus quand on discutera de la conception des bases de données.
--1 les attributs des clients non aux USA
SELECT FirstName,
LastName,
CustomerId,
Country
FROM customers
WHERE country NOT IN ('USA');
--------------------------------------------------------
--2 les attributs des clients au Brésil
SELECT FirstName,
LastName,
CustomerId,
Country
FROM customers
WHERE country='Brazil';
---------------------------------------------------------
--3 les clients au Brésil et leurs factures
SELECT c.FirstName,
c.LastName,
i.InvoiceId,
i.InvoiceDate,
i.BillingCountry
FROM customers c
JOIN invoices i
ON i.CustomerId=c.CustomerId
AND c.Country='Brazil';
-- ou bien :
SELECT c.FirstName,
c.LastName,
i.InvoiceId,
i.InvoiceDate,
i.BillingCountry
FROM customers c
JOIN invoices i
ON i.CustomerId=c.CustomerId
WHERE c.Country='Brazil';
-- ou encore
SELECT c.FirstName,
c.LastName,
i.InvoiceId,
i.InvoiceDate,
i.BillingCountry
FROM customers c, invoices i
WHERE i.CustomerId=c.CustomerId
AND c.Country='Brazil';
---------------------------------------------------------
--4 les employés du département des ventes
SELECT *
FROM employees
WHERE title LIKE 'Sales%';
---------------------------------------------------------
--5 les différents pays où des factures ont été émises
SELECT distinct BillingCountry
FROM invoices;
---------------------------------------------------------
--6 les clients du Brésil et leurs factures
SELECT *
FROM customers c
JOIN invoices i
ON i.CustomerId=c.CustomerId
AND c.Country='Brazil';
---------------------------------------------------------
--7 Les factures associées à chaque agent commercial.
-- Malheureusement, on ne peut pas utiliser un GROUP BY
-- Tout ce qu'on peut faire est d'ordonner au mieux ...
SELECT e.firstname,
e.lastname,
i.*
FROM customer AS c
JOIN invoices as i
ON c.customerid = i.customerid
JOIN employees AS e
ON e.employeeid = c.supportrepid
ORDER BY e.employeeid;
---------------------------------------------------------
--8 total de facture, nom client, pays, agent commercial pour tous les (facture+client)
SELECT e.firstname AS 'prenom_employe',
e.lastname AS 'nom_employe',
c.firstname AS 'client_prenom',
c.lastname AS 'client_nom',
c.country,
i.total
FROM employees AS e
JOIN customers AS c
ON e.employeeid = c.supportrepid
JOIN invoices AS i
ON c.customerid = i.customerid
---------------------------------------------------------
--9 How many Invoices were there in 2009 and 2011?
-- What are the respective total sales for each of those years?
SELECT strftime('%Y',InvoiceDate) AS annee ,
COUNT(*),
SUM(Total)
FROM invoices
WHERE annee IN ('2009','2011')
GROUP BY annee;
---------------------------------------------------------
--10 Le nombre de lignes de facture pour la facture 37
SELECT COUNT(*)
FROM invoice_items
WHERE InvoiceId=37;
---------------------------------------------------------
--11 le nombre de lignes de factures pour chaque facture
SELECT InvoiceId,
COUNT(*)
FROM invoice_items
GROUP BY InvoiceId;
---------------------------------------------------------
--12 Rajouter à chaque ligne de commande, le nom de la piste
SELECT i.*,
t.Name
FROM invoice_items i
JOIN tracks t
ON i.TrackId=t.TrackId
ORDER BY i.InvoiceLineId;
---------------------------------------------------------
--13 Même chose, plus le nom de l'artiste
SELECT i.*,
t.Name,
ar.Name
FROM invoice_items i
JOIN tracks t
on i.TrackId=t.TrackId
JOIN albums a
ON t.AlbumId=a.albumId
JOIN artists ar
ON a.ArtistId=ar.ArtistId
ORDER BY i.InvoiceLineId;
---------------------------------------------------------
--14 Le nombre de factures par pays
SELECT BillingCountry ,
COUNT(*)
FROM invoices
GROUP BY BillingCountry;
---------------------------------------------------------
--15 pour chaque playlist, son nom et son nombre de pistes
-- on notera l'utilisation d'une jointure externe à gauche
-- pour tenir compte des playlists vides
SELECT p.Name,
COUNT(pt.PLaylistId)
FROM playlists p
LEFT JOIN playlist_track pt
ON p.PlaylistId=pt.PLaylistId
GROUP BY p.PlaylistId;
---------------------------------------------------------
--16 pour chaque piste, toutes ses info, plus le titre de l'album,
-- le genre, et le type de média
SELECT t.Name AS 'track',
t.Composer,
t.milliseconds,
t.Bytes,
t.UnitPrice,
a.title AS 'album',
g.Name AS 'genre',
m.Name AS 'media type'
FROM tracks AS t
JOIN albums AS a
ON a.albumid = t.albumid
JOIN genres AS g
ON g.genreid = t.genreid
JOIN media_types AS m
ON m.mediatypeid = t.mediatypeid
ORDER BY t.TrackId;
---------------------------------------------------------
--17 Toutes les factures détaillées, avec leur nb de lignes de commande
SELECT i.*,
COUNT(InvoiceLineId)
FROM invoices i
JOIN invoice_items it
ON i.InvoiceId=it.InvoiceId
GROUP BY i.InvoiceId;
---------------------------------------------------------
--18 le total des ventes effectuées par chaque agent commercial
SELECT e.EmployeeId,
e.FirstName,
e.LastName,
SUM(i.Total)
FROM employees e
JOIN customers c
ON e.EmployeeId=c.SupportRepId
JOIN invoices i
ON i.CustomerId=c.CustomerId
GROUP BY e.EmployeeId;
---------------------------------------------------------
--19 l'agent commercial qui a fait le plus gros chiffre d'affaire en 2009
SELECT e.EmployeeId,
e.FirstName,
e.LastName,
SUM(i.Total) AS totalsales
FROM employees e
JOIN customers c ON e.EmployeeId=c.SupportRepId
JOIN invoices i ON i.CustomerId=c.CustomerId
WHERE strftime('%Y',i.InvoiceDate)='2009'
-- ou bien
-- WHERE i.invoicedate between '2009-01-00' AND '2009-12-31'
GROUP BY e.EmployeeId
ORDER BY totalsales DESC
LIMIT 1;
---------------------------------------------------------
--20 idem en 2010 (intérêt ???)
SELECT e.EmployeeId,
e.FirstName,
e.LastName,
SUM(i.Total) AS totalsales
FROM employees e
LOIN customers c
ON e.EmployeeId=c.SupportRepId
JOIN invoices i
ON i.CustomerId=c.CustomerId
WHERE strftime('%Y',i.InvoiceDate)='2010'
GROUP BY e.EmployeeId
ORDER BY totalsales DESC
LIMIT 1;
---------------------------------------------------------
--21 l'agent commercial qui a fait globalement le plus gros chiffre d'affaire
SELECT e.EmployeeId,
e.FirstName,
e.LastName,
sum(i.Total) AS totalsales
FROM employees e
JOIN customers c
ON e.EmployeeId=c.SupportRepId
JOIN invoices i
ON i.CustomerId=c.CustomerId
GROUP BY e.EmployeeId
ORDER BY totalsales DESC
LIMIT 1;
---------------------------------------------------------
--22 nb de clients assignés à chaque agent
SELECT SupportRepId,
COUNT(*)
FROM customers
GROUP BY SupportRepId;
---------------------------------------------------------
--23 le pays qui a dépensé le plus
SELECT BillingCountry,
SUM(Total) AS somme
FROM invoices
GROUP BY BillingCountry
ORDER BY somme DESC
LIMIT 1;
---------------------------------------------------------
--24 le morceau le plus vendu en 2013
SELECT t.Name,
sum(InvoiceLineId) AS nombre
FROM invoice_items ii
JOIN tracks t
ON t.TrackId=ii.TrackId
JOIN invoices i
ON i.InvoiceId=ii.InvoiceId
WHERE strftime('%Y',i.InvoiceDate)='2013'
GROUP BY t.trackId
ORDER BY nombre DESC
LIMIT 1;
---------------------------------------------------------
--25 les 5 morceaux les plus vendus de l'histoire
SELECT t.Name,
SUM(InvoiceLineId) AS nombre
FROM invoice_items i
JOIN tracks t
ON t.TrackId=i.TrackId
GROUP BY t.trackId
ORDER BY nombre DESC
LIMIT 5;
---------------------------------------------------------
--26 Les 3 artistes les plus vendeurs
SELECT artists.name,
SUM(i.Quantity * i.UnitPrice) AS total
FROM artists,
albums,
tracks,
invoice_items i
WHERE artists.ArtistId = albums.ArtistId
AND albums.AlbumId = tracks.AlbumId
AND tracks.TrackId = invoice_items.TrackId
GROUP BY artists.ArtistId
ORDER BY total DESC
LIMIT 3;