16

tp2

medina5 edited this page 2025-11-05 12:59:36 +01:00

Table of Contents

• Travaux pratiques n°2
• Sélection de colonnes
• Tri
• Filtre
• Les dates
• strftime
• Top
• Null
• Jointures simples
• Compter
• 5. Nombre de pays des clients
• 5. Nombre de clients par pays
• 6. Nombre de pistes par genre
• 7. Nombre d'albums par artiste
• 8. Nombre de piste par artiste
• 9. Nombre de minute de musique par album
• 10. Recette par piste
• 9. Recette par album
• 10. Les 3 meilleurs clients
• 11. Les genres écoutés par Isabelle Mercier

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

Travaux pratiques n°2

Sélection de colonnes

• Afficher les titres et identifiants des albums.

select Title, AlbumId from Albums

• Afficher les colonnes prénom, nom et ville de la table clients.

SELECT FirstName, LastName, City from customers;

• Afficher le nom complet des clients. Le nom complet est la concaténation dans une seule colonne du prénom et du nom. Donner le titre NomComplet à cette colonne.

select FirstName || ' ' || LastName as nomComplet, City from customers;

L'opérateur || est l'opérateur de concaténation de chaine équivalent à + mais pour le texte.

Le mot clé as permet de spécifier ou renommer le titre d'une colonne

Tri

• Affichez les noms des artistes triés par ordre alphabétique.

SELECT Name from artists order by Name

Filtre

• Montrez toutes les pistes (Name) dont la durée (Milliseconds) est supérieure à 5 minutes.

select Name from tracks
  where Milliseconds > 300000 -- 5 * 60 * 1000

• Filtrer les clients dont l'age est inférieur à 25 ans

select firstName || ' ' || lastName as nomComplet, Age
  from customers
  where Age < 25

• Affichez tous les clients vivant au Brésil.

select firstName || ' ' || lastName as nomComplet
  from customers
  where Country = 'Brazil'

Warning

Le respect des majuscules n'est pas obligatoire pour le nom des tables et colonnes mais cela l'est pour les valeurs.
brazil est différent de Brazil.

• Filtrer les clients dont l'age est inférerieur à 25 ans et qui vivent aux USA

select firstName || ' ' || lastName as nomComplet
  from customers
  where Age < 25 and Country = 'USA'

L'opérateur and effectue un ET logique entre 2 conditions.

Ici aussi il faut respecter les majuscules pour la valeur USA.

• Filter les albums qui sont sortis entre 1984 et 1989

select Title, Year
  from albums
  where Year between 1984 and 1989

• Afficher les employés qui ne sont pas de la ville de Calgary

select firstName || ' ' || lastName as nomComplet, City
  from employees
  where City <> 'Calgary;

• Filtrer les clients de France, Belgique et Allemagne (France, Belgium, Germany)

select firstName || ' ' || lastName as nomComplet, Country
  from customers
  where country = 'France'
     or country = 'Belgium'
     or country = 'Germany';

select firstName || ' ' || lastName as nomComplet, Country
  from customers
  where Country in ('France', 'Belgium', 'Germany');

Les dates

• Récupérez toutes les factures (InvoiceId, InvoiceDate, Total) émises en 2010.

select InvoiceId,  InvoiceDate , Total
  from invoices
  where InvoiceDate >= '2010-01-01'
	  and InvoiceDate <= '2010-12-31'

select InvoiceId,  InvoiceDate , Total
  from invoices
  where InvoiceDate between '2010-01-01' and '2010-12-31'

L'opérateur between combine supérieur ou égal >= ET inférieur ou égal <= dans une seule commande

select InvoiceId,  InvoiceDate , Total
  from  invoices
  where STRFTIME('%Y', InvoiceDate) = '2010'

strftime string from time il faut les ''

--
select InvoiceId,  InvoiceDate , Total
  from invoices
  where cast(STRFTIME('%Y', InvoiceDate) as INT) = 2010

cast transforme la chaine en entier (nombre)

strftime

voir le détail de la fonction strftime

• Lister le total des factures en détaillant dans des colonnes séparées l'année, le mois, le n° de semaine et le n° de jour de la semaine

• Afficher les jours de la semaine avec leur nom (lundi, mardi, mercredi, ...) plutôt qu'avec un nombre.

select
  STRFTIME('%Y', invoiceDate) as Année,
  STRFTIME('%m', invoiceDate) as Mois,
  STRFTIME('%V', invoiceDate) as 'N° de semaine',
  case STRFTIME('%u', invoiceDate)
    WHEN '1' THEN 'Lundi'
    WHEN '2' THEN 'Mardi'
    WHEN '3' THEN 'Mercredi'
    WHEN '4' THEN 'Jeudi'
    WHEN '5' THEN 'Vendredi'
    WHEN '6' THEN 'Samedi'
    WHEN '7' THEN 'Dimanche'
  end
  as 'jour de la semaine'
from invoices

Top

Pour afficher un top il suffit de trier par ordre décroissant DESC et de limiter le nombre de lignes.

• Afficher les 10 pistes les plus longues en durée.

SELECT Name, Milliseconds FROM tracks
  order by Milliseconds DESC
  limit 10;

• Afficher les 5 factures les plus importantes par montant total.

select Total from invoices
  order by Total desc
  limit 5

Null

Warning

Null est égal à rien même pas à lui même. Il faut utiliser les opérateurs IS NULL ou IS NOT NULL

• Listez les employés (FirstName, LastName) qui reportent à un manager (ReportsTo non nul).

-- NE FONCTIONNE PAS !!
SELECT * FROM employees WHERE ReportsTo <> NULL;

SELECT * FROM employees WHERE ReportsTo IS NOT NULL;

• Le client qui n'a pas de numéro de téléphone

SELECT * FROM customers WHERE Phone IS NULL;

Jointures simples

• Affichez le titre des albums de l’artiste AC/DC.

-- Étape 1. Récupère artistId pour AC/DC
select ArtistId from  artists where Name = 'AC/DC'

-- Étape 2. Sélectionne les albums pour ArtistId = 1
select Title from albums WHERE ArtistId = 1

Utilisation d'une sous-requête. Une requête utilise le résultat d'une autre.

SELECT Title FROM albums WHERE ArtistId = (
  SELECT artistId FROM  artists WHERE Name = 'AC/DC'
)

Utilisation d'une jointure entre 2 tables

SELECT Title
  FROM albums
  JOIN artists ON albums.ArtistId = artists.ArtistId
  WHERE Name = 'AC/DC'

• Affichez le nom des pistes (Track.Name) et le nom de l’album (Album.Title) associé.

SELECT Name, Title
FROM tracks
JOIN albums on albums.AlbumId = tracks.AlbumId

La liaison ON sur ce qui est commun entre les 2 tables

• Listez les factures (InvoiceId, Total) avec le nom du client (Customer.FirstName, Customer.LastName).

SELECT i.InvoiceId, i.Total, c.firstName, c.lastName
FROM invoices i
JOIN customers c on c.CustomerId = i.CustomerIdx

• Listez tous les clients (FirstName, LastName) avec le pays (Country) et l’employé commercial (FirstName, LastName) qui leur est assigné.

SELECT c.FirstName, c.LastName, c.country,
       e.FirstName, e.lastName
FROM customers c
JOIN employees e ON c.SupportRepId = e.EmployeeId

FirstName et LastName sont présents dans les 2 tables. Il est OBLIGATOIRE d'utiliser l'alias de table pour savoir quelle colonne est affichée

• Affichez toutes les pistes (Name) avec leur genre (Genre.Name).

select t.Name, g.Name
from tracks t
join genres g on g.GenreId = t.GenreId

• Affichez toutes les pistes (Track.Name) avec leur artiste (Artist.Name).

SELECT t.Name, a.Name
FROM tracks t
JOIN albums b ON b.AlbumId = t.AlbumId
JOIN artists a ON a.ArtistId = b.ArtistId

Compter

• Nombre de clients

select count(*) from customers;

• Nombre d'albums

select count(*) from albums;

• Nombre d'artistes

select count(*) from artists;

• Liste des pays des clients

select distinct country from customers

Utilisation de distinct pour supprimer les doublons.

5. Nombre de pays des clients

5. Nombre de clients par pays

6. Nombre de pistes par genre

7. Nombre d'albums par artiste

8. Nombre de piste par artiste

9. Nombre de minute de musique par album

10. Recette par piste

9. Recette par album

10. Les 3 meilleurs clients

11. Les genres écoutés par Isabelle Mercier

Attention l'égalité est stricte sur FirstName et LastName (respect des majuscules / minuscules)