Bases de données

BdD - TP 2 - M1 IM

Université Paris-Sud

  1. Requêtes SQL (base "commerce")

    Questions/réponses proposées par Didier Boulle sur son site.

    Résumé des tables présentes dans la base :

    ARTICLES (NOART, LIBELLE, STOCK, PRIXINVENT)
FOURNISSEURS (NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)
ACHETER (NOFOUR#, NOART#, PRIXACHAT, DELAI)

    Connexion : psql -d commerce

    1. Écrivez les requêtes suivantes en SQL (bonus : en algèbre relationnelle également)
      1. Numéros et libellés des articles dont le stock est inférieur à 10 ? 
        SELECT NOART, LIBELLE
FROM ARTICLES
WHERE STOCK<10;
        Algèbre relationnelle (bonus) :
        R1=SELECTION(ARTICLES, STOCK<10)
        R2=PROJECTION(R1, NOART, LIBELLE)
      2. Liste des articles dont le prix d'inventaire est compris entre 100 et 300 ? 
        SELECT *
FROM ARTICLES
WHERE PRIXINVENT BETWEEN 100 AND 300;
        AR :
        R1=SELECTION(ARTICLES, PRIXINVENT>=100 et PRIXINVENT<=300)
        R2=PROJECTION(R1, NOART, LIBELLE, STOCK, PRIXINVENT)
      3. Liste des fournisseurs dont on ne connaît pas l'adresse ? 
        SELECT *
FROM FOURNISSEURS
WHERE ADRFOUR IS NULL;
        AR :
        R1=SELECTION(FOURNISSEURS, ADRFOUR est "non renseigné")
        R2=PROJECTION(R1, NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)
      4. Liste des fournisseurs dont le nom commence par "STE" ? 
        SELECT *
FROM FOURNISSEURS
WHERE NOMFOUR LIKE 'STE%';
        AR :
        R1=SELECTION(FOURNISSEURS, NOMFOUR comme "STE%")
        R2=PROJECTION(R1, NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)
      5. Noms et adresses des fournisseurs qui proposent des articles pour lesquels le délai d'approvisionnement est supérieur à 20 jours ? 
        SELECT NOMFOUR, ADRFOUR, VILLEFOUR
FROM FOURNISSEURS F 
JOIN ACHETER A 
  ON F.NOFOUR=A.NOFOUR
WHERE DELAI>20;
        AR :
        R1=SELECTION(ACHETER, DELAI>20)
        R2=JOINTURE(R1, FOURNISSEURS, NOFOUR)
        R2=PROJECTION(R1, NOMFOUR, ADRFOUR, VILLEFOUR)
      6. Nombre d'articles référencés ? 
        SELECT COUNT(*) AS NbArticles
FROM ARTICLES;
        AR :
        NbArticles=CALCULER(ARTICLES, Comptage())
      7. Valeur du stock ? 
        SELECT SUM(STOCK*PRIXINVENT) AS ValeurStock
FROM ARTICLES;
        AR :
        ValeurStock=CALCULER(ARTICLES, Somme(STOCK*PRIXINVENT))
      8. Numéros et libellés des articles triés dans l'ordre décroissant des stocks ? 
        SELECT NOART, LIBELLE, STOCK
FROM ARTICLES
ORDER BY STOCK DESC;
        AR :
        R1=PROJECTION(ARTICLES, NOART, LIBELLE, STOCK)
        R2=TRI(R1, STOCK décroissant)
      9. Liste pour chaque article (numéro et libellé) du prix d'achat maximum, minimum et moyen ? 
        SELECT A.NOART, LIBELLE, MAX(PRIXACHAT) AS PMAX, 
       MIN(PRIXACHAT) AS PMIN, AVG(PRIXACHAT) AS PMOY
FROM ACHETER A 
JOIN ARTICLES B 
  ON A.NOART = B.NOART
GROUP BY A.NOART, LIBELLE;
        AR :
        R1=REGROUPER_ET_CALCULER(ACHETER, NOART, PAMAX : MAX(PRIXACHAT), PAMIN : MIN(PRIXACHAT), PAMOY : MOYENNE(PRIXACHAT))
        R2=JOINTURE(R1, ARTICLES, NOART)
        R3=PROJECTION(R2, NOART, LIBELLE, PAMAX, PAMIN, PAMOY)
      10. Délai moyen pour chaque fournisseur proposant au moins 2 articles ? 
        SELECT A.NOFOUR, NOMFOUR, AVG(DELAI) AS DelaiMoyen
FROM ACHETER A 
JOIN FOURNISSEURS F 
  ON A.NOFOUR = F.NOFOUR
GROUP BY A.NOFOUR, NOMFOUR
HAVING COUNT(*) >=2;
        AR :
        R1=REGROUPER_ET_CALCULER(ACHETER, NOFOUR, Nb : Comptage(), DelaiMoyen : MOYENNE(DELAI))
        R2=SELECTION(R1, Nb>=2)
        R3=JOINTURE(R2, FOURNISSEURS, NOFOUR)
        R4=PROJECTION(R3, NOFOUR, NOMFOUR, DelaiMoyen)
      11. Prix minimum de chaque article avec le fournisseur correspondant 
        SELECT a.noart, libelle, nomfour, minprix
FROM acheter a 
JOIN
  (select noart, min(prixachat) as minprix
  FROM acheter 
  group by noart) PMIN
    ON a.noart = PMIN.noart AND a.prixachat = PMIN.minprix
JOIN fournisseurs f 
    ON a.nofour = f.nofour
JOIN articles l 
    ON a.noart = l.noart;
        AR :
        R1=REGROUPER_ET_CALCULER(ACHETER, NOART, minprix : MIN(prixachat))
        R2=JOINTURE(R1, ACHETER, R1.NOART=ACHETER.NOART et R1.minprix=ACHETER.prixachat)
        R3=JOINTURE(R2, FOURNISSEURS, NOFOUR)
        R4=JOINTURE(R3, ARTICLES, NOART)
        R5=PROJECTION(R3, NOART, LIBELLE, NOMFOUR, minprix)
      12. Fournisseurs auprès desquels on ne s'approvisionne plus et qu'il faudrait donc peut-être supprimer de la table Fournisseurs 
        SELECT f.nofour, nomfour, adrfour, villefour
FROM fournisseurs f 
LEFT JOIN acheter a 
  on f.nofour = a.nofour
WHERE a.noart IS NULL;
        AR :
        R1=PROJECTION(FOURNISSEURS, NOFOUR)
        R2=PROJECTION(ACHETER, NOFOUR)
        R3=DIFFERENCE(R1, R2)
        R4=JOINTURE(R3, FOURNISSEURS, NOFOUR)
        R5=PROJECTION(R4, NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)
        ou
        R1=JOINTURE_GAUCHE(FOURNISSEURS, ACHETER, NOFOUR)
        R2=SELECTION(R1, NOART est NULL)
        R3=PROJECTION(R2, NOFOUR, NOMFOUR, ADRFOUR, VILLEFOUR)
      13. Quels sont les fournisseurs qui fournissent les 2 articles 100 et 106, et à quel prix ? 
        SELECT nomfour, a.prixachat as prix_100, 
       b.prixachat as prix_106
FROM acheter a 
join acheter b 
  using(nofour)
JOIN fournisseurs 
  using(nofour)
WHERE a.noart=100 AND b.noart=106;
        AR :
        R1=SELECTION(ACHETER, NOART=100)
        R2=SELECTION(ACHETER, NOART=106)
        R3=JOINTURE(R1, R2, NOFOUR)
        R4=JOINTURE(R3, FOURNISSEURS, NOFOUR)
        R5=PROJECTION(R4, NOMFOUR, R1.prixachat, R2.prixachat)
      14. Listes des articles pouvant être achetés chez plusieurs fournisseurs, avec le prix d'achat et le délai correspondants 
        SELECT a.noart, libelle, nomfour, a.prixachat, a.delai
FROM acheter a 
JOIN acheter b 
  ON a.noart = b.noart
JOIN fournisseurs f 
  ON a.nofour = f.nofour
JOIN articles l 
  ON a.noart = l.noart
WHERE a.nofour <> b.nofour
order by a.noart;
        AR :
        R1=ACHETER
        R2=JOINTURE(R1, ACHETER, NOART)
        R3=SELECTION(R2, R1.NOFOUR<>ACHETER.NOFOUR)
        R4=JOINTURE(R3, FOURNISSEURS, R1.NOFOUR=FOURNISSEURS.NOFOUR)
        R5=JOINTURE(R4, ARTICLES, R1.NOART=ACHETER.NOART)
        R6=PROJECTION(R5, R1.NOART, LIBELLE, NOMFOUR, R1.PRIXACHAT, R1.DELAI )
      15. Quels sont le ou les fournisseurs qui fournissent le plus de produits ? 
        SELECT nofour, nomfour, count(*) as nombre_produits
FROM fournisseurs 
join acheter 
  using(nofour)
GROUP BY nofour, nomfour 
HAVING count(*) = (SELECT MAX(nb_prod) FROM
  (SELECT count(*) as nb_prod
  FROM acheter
  GROUP BY nofour) A);
        AR :
        R1=REGROUPER_ET_CALCULER(ACHETER, NOFOUR, Nb_prod : Comptage())
        Max_prod=CALCULER(R1, MAX(Nb_prod))
        R2=REGROUPER_ET_CALCULER(ACHETER, NOFOUR, Nombre_produits : Comptage())
        R3=SELECTION(R2, Nombre_produits=Max_prod)
        R4=JOINTURE(R3, FOURNISSEURS, NOFOUR)
        R5=PROJECTION(R4, NOFOUR, NOMFOUR, Nombre_produits)
  2. Mise à jour de tables et requêtes SQL

    Exercices proposés par Christine Keribin dans un TP antérieur.

    1. Gestion d’une agence immobilière

      Vous travaillez dans une agence immobilière qui a mis en place un modèle relationnel afin de gérer son portefeuille client. Le modèle relationnel est le suivant :

      CLIENT (codeclt, nomclt, prenomclt, adresseclt, CPclt, villeclt)
APPARTEMENT (ref, superficie, pxvente, secteur, #coderep, #codeclt)
REPRESENTANT (coderep, nomrep, prenomrep)

      L'agent immobilier souhaite avoir un certain nombre d'informations. Écrivez les requêtes SQL correspondant aux demandes suivantes.

      1. la liste des clients classés par ordre alphabétique 
        SELECT *
FROM CLIENT
ORDER BY nomclt;
      2. la liste des appartements situés à Villefranche et gérés par Carole Palege 
        SELECT * FROM APPARTEMENT APT
JOIN CLIENT CL 
  ON CL.codeclt = APT.codeclt
JOIN REPRESENTANT REP 
  ON REP.coderep = APT.coderep
WHERE CL.villeclt = 'Villefranche' 
  AND REP.prenomrep = 'Carole' 
  AND REP.nomrep = 'Palege';
      3. la moyenne par secteur des prix des appartements 
        SELECT secteur, AVG(pxvente) AS prixMoyen
FROM APPARTEMENT
GROUP BY secteur;
      4. le nombre d'appartements dont la superficie est supérieure à 80 m² 
        SELECT COUNT(*)
FROM APPARTEMENT
WHERE superficie > 80;
      5. Par ailleurs, afin de mettre à jour sa base de données, l'agent immobilier vous demande :
        a] de supprimer l'appartement référencé 2006A
        b] de modifier le prix de l'appartement 2014G : il passe à 230 000 €.
        a] 
        DELETE FROM APPARTEMENT
WHERE ref = '2006A';
        b] 
        UPDATE APPARTEMENT 
SET pxvente = 230000
WHERE ref = '2014G';
    2. Gestion d'un service après vente

      Le responsable du SAV d'une entreprise d'électroménager a mis en place une petite base de données afin de gérer les interventions de ces techniciens. Le modèle relationnel à la source de cette base de données est le suivant :

      CLIENT (codeclt, nomclt, prenomclt, adresse, cp, ville)
PRODUIT (référence, désignation, prix)
TECHNICIEN (codetec, nomtec, prenomtec, tauxhoraire)
INTERVENTION (numero, date, raison, #codeclt, #référence, #codetec)

      Le responsable vous demande d’écrire en langage SQL les requêtes suivantes :

      1. la liste des produits (référence et désignation), classés du moins cher au plus cher 
        SELECT reference,designation
FROM PRODUIT
ORDER BY prix;
      2. le nombre d’interventions par technicien 
        SELECT T.codetec, T.nomtec, T.prenomtec, I.nbInterv
FROM TECHNICIEN T
JOIN
  (SELECT codetec, COUNT(*) AS nbInterv
  FROM INTERVENTION
  GROUP BY codetec) AS I
    ON T.codetec = I.codetec;
      3. les désignations de produits pour lesquelles la moyenne des prix est supérieure à 300 € 
        SELECT designation, AVG(prix) AS prixMoyen
FROM PRODUIT
GROUP BY designation
HAVING AVG(prix) > 300;
        Note: deux fois le calcul AVG(prix) : on devrait pouvoir l'éviter...
      4. la liste des clients ayant demandé une intervention pour des produits d’un prix supérieur à 300 € 
        SELECT *
FROM CLIENT
WHERE codeclt IN
  (SELECT codeclt
  FROM INTERVENTION
  WHERE reference IN
    (SELECT reference
    FROM PRODUIT
    WHERE prix > 300));
      5. les interventions effectuées par le technicien ayant le code 2381 entre le 1er juillet et le 31 août 2006 
        SELECT *
FROM INTERVENTION
WHERE codetec = 2381 AND 
  date BETWEEN '2006-07-01' AND '2006-08-31';
      6. Par ailleurs, il vous informe que le produit référencé 548G a vu son prix augmenter (nouveau prix : 320€). Vous apprenez également par le directeur des ressources humaines qu’un nouveau technicien a été recruté : son code est le 3294, il s’appelle Denis Cavacho et est rémunéré à un taux horaire de 15€. Comment traduire ceci en langage SQL ?
        a] 
        UPDATE PRODUIT
SET prix = 320
WHERE reference = '548G';
        b] 
        INSERT INTO TECHNICIEN
VALUES(3294, 'Cavacho', 'Denis', 15);
  3. Requêtes SQL (base "cinema")

    Questions trouvée dans un TP de Christine Keribin, utilisant une base construite par Philippe Rigaux

    CREATE TABLE Internaute (
  email VARCHAR (40) NOT NULL,
  nom VARCHAR (30) NOT NULL ,
  prenom VARCHAR (30) NOT NULL,
  region VARCHAR (30),
  PRIMARY KEY (email));

CREATE TABLE Pays (
  code VARCHAR(4) NOT NULL,
  nom VARCHAR (30) DEFAULT ’Inconnu’ NOT NULL,
  langue VARCHAR (30) NOT NULL,
  PRIMARY KEY (code));

CREATE TABLE Artiste (
  idArtiste INTEGER NOT NULL,
  nom VARCHAR (30) NOT NULL,
  prenom VARCHAR (30) NOT NULL,
  anneeNaiss INTEGER,
  PRIMARY KEY (idArtiste),
  UNIQUE (nom, prenom));

CREATE TABLE Film (
  idFilm INTEGER NOT NULL,
  titre VARCHAR (50) NOT NULL,
  annee INTEGER NOT NULL,
  idMES INTEGER,
  genre VARCHAR (22) NOT NULL,
  resume TEXT,
  codePays VARCHAR (4),
  PRIMARY KEY (idFilm),
  FOREIGN KEY (idMES) REFERENCES Artiste,
  FOREIGN KEY (codePays) REFERENCES Pays);

CREATE TABLE Notation (
  idFilm INTEGER NOT NULL,
  email VARCHAR (40) NOT NULL,
  note INTEGER NOT NULL,
  PRIMARY KEY (idFilm, email));

CREATE TABLE Role (
  idFilm INTEGER NOT NULL,
  idActeur INTEGER NOT NULL,
  nomRole VARCHAR(32),
  PRIMARY KEY (idActeur,idFilm),
  FOREIGN KEY (idFilm) REFERENCES Film,
  FOREIGN KEY (idActeur) REFERENCES Artiste);

CREATE TABLE Genre (
  code VARCHAR (22) NOT NULL,
  PRIMARY KEY (code));

    Connexion : psql -d cinema

    Remarque : la table "Artiste" contient les acteurs, mais aussi entre autres des metteurs en scène, qui n’ont pas forcément joué dans un film. C’est pourquoi on lit trois noms différents (idMES, idArtiste, idActeur) au fil des tables, mais ils référencent tous la table "Artiste".

    1. Requêtes sur plusieurs tables
      1. Qui a joué le rôle de Morpheus (nom et prénom)? 
        SELECT nom, prenom
FROM Artiste
WHERE idArtiste IN
  (SELECT idActeur
  FROM Role
  WHERE nomRole = 'Morpheus');
      2. Qui est le réalisateur de Alien? 
        SELECT nom, prenom
FROM Artiste
WHERE idArtiste IN
  (SELECT idMES
  FROM Film
  WHERE titre = 'Alien');
      3. Prénom et nom des internautes qui ont donné une note 4 à un film (donner aussi le titre du film) 
        WITH NoteEqual4 AS 
  (SELECT email, idFilm 
  FROM Notation 
  WHERE note = 4)
SELECT F.titre, I.prenom, I.nom
FROM NoteEqual4 N
JOIN Internaute I
  ON N.email = I.email
JOIN Film F
  ON N.idFilm = F.idFilm
      4. Quels acteurs ont joué quel rôle dans le film Vertigo? 
        WITH ActeurRoleVertigo AS
  (SELECT idActeur, nomRole
  FROM Role
  WHERE idFilm IN
    (SELECT idFilm
    FROM Film
    WHERE titre = 'Vertigo'))
SELECT A.nom, A.prenom, ARV.nomRole
FROM Artiste A 
JOIN  ActeurRoleVertigo ARV
  ON A.idArtiste = ARV.idActeur
      5. Films dont le réalisateur est Tim Burton et un des acteurs Johnny Depp. 
        SELECT *
FROM Film
WHERE idRES IN
  (SELECT idArtiste
  FROM Artiste
  WHERE prenom = 'Tim' AND nom = 'Burton')
AND idFilm IN
  (SELECT idFilm
  FROM Role
  WHERE idActeur IN
    (SELECT idArtiste
    FROM Artiste
    WHERE prenom = 'Johnny' AND nom = 'Depp'));
      6. Titre des films dans lesquels a joué Bruce Willis. Donner le nom, le rôle et le titre des films. 
        SELECT F.titre, R.nomRole
FROM Role R
JOIN Artiste A
  ON A.idArtiste = R.idActeur
JOIN Film F
  ON F.idFilm = R.idFilm
WHERE A.prenom = 'Bruce' AND A.nom = 'Willis';
-- Note : serait plus efficace avec des requêtes en plusieurs temps, comme ci-dessus.
      7. Quel metteur en scène a joué dans ses propres films ? Donner le rôle et le titre des films. 
        SELECT DISTINCT A.prenom, A.nom
FROM Artiste A
WHERE EXISTS
  (SELECT 1
  FROM Role R
  WHERE R.idActeur = A.idArtiste AND R.idActeur IN 
    (SELECT idMES FROM Film WHERE idFilm = R.idFilm));
      8. Dans quels films le metteur en scène a-t-il le même prénom que l’un de ses interprètes ? (titre, nom du metteur en scène, nom de l’interprète). 
        WITH MemePrenom AS 
  (SELECT A1.idArtiste AS idArtiste1, A2.idArtiste AS idArtiste2, 
          A1.prenom, A1.nom AS nom1, A2.nom AS nom2
  FROM Artiste A1
  JOIN Artiste A2
    ON A1.prenom = A2.prenom AND A1.idArtiste != A2.idArtiste)
SELECT F.titre, MP.prenom, MP.nom1 AS nomMES, MP.nom2 AS nomActeur
FROM Film F
JOIN MemePrenom MP
  ON MP.idArtiste1 = F.idMES
WHERE MP.idArtiste2 IN
  (SELECT R.idActeur
  FROM Role R
  WHERE R.idFilm = F.idFilm);
-- TODO: sans doute pas optimal...
    2. Agrégats
      1. Nombre de films notés par l’internaute rigaux@cnam.fr, moyenne des notes données, note minimale et note maximale. 
        WITH NotesRigaux AS
  (SELECT note
  FROM Notation
  WHERE email =  'rigaux@cnam.fr')
SELECT COUNT(*) AS nombreDeFilms, AVG(note) AS noteMoyenne, 
                MIN(note) AS noteMin, MAX(note) AS noteMax
FROM NotesRigaux;
      2. Combien de fois Bruce Willis a-t-il joué le rôle de McCane? 
        SELECT COUNT(*)
FROM Role
WHERE nomRole = 'McCane' AND idActeur IN
  (SELECT idArtiste
  FROM Artiste
  WHERE prenom = 'Bruce' AND nom = 'Willis');
      3. id, nom et prénom des réalisateurs, et nombre de films qu’ils ont tournés 
        SELECT A.idArtiste, A.nom, A.prenom, IdNb.nombreDeFilms
FROM Artiste A
JOIN
  (SELECT idMES, COUNT(*) AS nombreDeFilms
  FROM Film
  GROUP BY idMES) AS IdNb
    ON A.idArtiste = IdNb.idMES;
    3. Négation
      1. Films sans rôle. 
        SELECT *
FROM Film F
WHERE NOT EXISTS
  (SELECT 1
  FROM Role R
  WHERE F.idFilm = R.idFilm);
      2. Nom et prénom des acteurs qui n’ont jamais mis en scène de film. 
        SELECT nom, prenom
FROM Artiste
WHERE idArtiste NOT IN
  (SELECT idMES
  FROM Film);
      3. Internautes qui n’ont pas noté de film paru en 1999. 
        SELECT *
FROM Internaute
WHERE email NOT IN
  (SELECT email
  FROM Notation
  WHERE idFilm IN
    (SELECT idFilm
    FROM Film
    WHERE annee = 1999));
    4. Requêtes imbriquées ou jointure ?
      1. Nom et prénom des artistes qui ont mis en scène un film 
        SELECT nom, prenom
FROM Artiste
WHERE idArtiste IN
  (SELECT idMES
  FROM Film);
      2. Titre et l’année des films qui ont le même genre que Matrix 
        SELECT titre, annee
FROM Film
WHERE genre IN
  (SELECT genre
  FROM Film
  WHERE titre = 'Matrix');
      3. Nom des internautes qui ont noté le film Alien. Donner également la note. 
        SELECT I.nom, N.note
FROM Notation N
JOIN Internaute I
  ON N.email = I.email
WHERE N.idFilm IN
  (SELECT idFilm
  FROM Film
  WHERE titre = 'Alien');
      4. Quel metteur en scène a joué en tant qu’acteur (mais pas dans son propre film) ? Nom, le rôle et titre des films où le metteur en scène a joué. 
        SELECT A.nom, R.nomRole, F1.titre
FROM Role R
JOIN Artiste A
  ON R.idActeur = A.idArtiste
JOIN Film F1
  ON F1.idFilm = R.idFilm
WHERE A.idArtiste <> F1.idMES AND A.idArtiste IN
  (SELECT F2.idMES
  FROM Film F2);
  4. Requêtes SQL (base "dvdrental")

    Se connecter à la base après connexion SSH : psql -d dvdrental

    Rappel :
    • \h pour l'aide-mémoire des commandes SQL
    • \? pour l'aide-mémoire des commandes psql
    • \q pour quitter
    • ; en fin d'instruction pour exécuter.
    Résumé des tables :
    • actor – stores actors data including first name and last name.
    • film – stores films data such as title, release year, length, rating, etc.
    • film_actor – stores the relationships between films and actors.
    • category – stores film’s categories data.
    • film_category- stores the relationships between films and categories.
    • store – contains the stores data including manager staff and address.
    • inventory – stores inventory data.
    • rental – stores rental data.
    • payment – stores customer’s payments.
    • staff – stores staff data.
    • customer – stores customers data.
    • address – stores address data for staff and customers
    • city – stores the city names.
    • country – stores the country names.

    Pour connaître le schéma d'une table : \d <nom_table>

    Voir aussi le tutoriel correspondant, et le schéma des tables

    1. Requêtes sur plusieurs tables
      1. Titres des DVD dans lesquels joue Henry Berry 
        SELECT title 
FROM film_actor 
JOIN film 
  ON film.film_id = film_actor.film_id 
JOIN actor 
  ON actor.actor_id = film_actor.actor_id 
WHERE actor.first_name='Henry' AND actor.last_name='Berry';
      2. Identifiants des boutiques disposant de plus de 100 films non loués. 
        SELECT sto.store_id, COUNT(*) AS filmsDispo
FROM store sto 
JOIN inventory i 
  ON sto.store_id=i.store_id 
JOIN staff sta
  ON sto.store_id=sta.store_id
JOIN rental r 
  ON r.staff_id=sta.staff_id
WHERE r.return_date IS NULL OR r.return_date < NOW()
GROUP BY sto.store_id
HAVING COUNT(*) > 100;
-- Note : sur la base dvdrental tous les DVD sont retournés car toutes les dates sont avant NOW()
--            sur une vraie base actualisée en temps réel, ce serait plus intéressant.
      3. Nom et adresse complète des clients ayant loué (et pas encore rendu) 'Dynamite Tarzan'
        £\Rightarrow£ Combien de tables sont impliquées ?
        £\Rightarrow£ Ne l'écrivez pas forcément en entier si vous avez compris le mécanisme, c'est un peu long ! 
        SELECT first_name, last_name, address, address2, district, city, country
FROM customer
-- ...
        Je ne l'écris pas car c'est linéaire, long et peu intéressant : implique les tables address, city, country, rental, film (JOIN, JOIN, ...), puis WHERE rental.return_date > NOW() AND film.title = 'blabla'. En fait, face à une telle requête à rallonge on peut se demander s'il est très efficace d'autant normaliser la base : cf. NoSQL, voir séance 3 "conception des BdD".
    2. Agrégats
      1. Nombre de films dans la catégorie 'Horror' 
        SELECT COUNT(*)
FROM film_category fg
WHERE fg.category_id IN
  (SELECT ca.category_id
  FROM category ca
  WHERE ca.name = 'Horror');
      2. Total des paiements effectués par la cliente Donna Thompson 
        SELECT SUM(p.amount)
FROM payment p
WHERE p.customer_id IN
  (SELECT cu.customer_id
  FROM customer cu
  WHERE cu.first_name='Donna' AND cu.last_name='Thompson');
    3. Négations
      1. Acteurs n'ayant joué dans aucun film allemand. 
        SELECT ac.first_name, ac.last_name 
FROM actor ac
WHERE ac.actor_id NOT IN
  (SELECT fa.actor_id
  FROM film_actor fa
  WHERE fa.film_id IN
    (SELECT fi.film_id
    FROM film fi
    JOIN language l
      ON fi.language_id = l.language_id
    WHERE l.name = 'German'));
-- On peut aussi le faire avec IN au lieu de NOT IN, et <> 'German' dans la dernière clause WHERE...
-- Note : sur la base dvdrental tous les films sont libellés 'English'... 
--            donc la requête renvoie tous les acteurs
      2. Films n'ayant jamais été loués. 
        SELECT fi.film_id, fi.title
FROM film fi
WHERE fi.film_id NOT IN
  (SELECT i.film_id
  FROM inventory i
  JOIN rental r
    ON i.inventory_id = r.inventory_id);
-- Que se passerait-il avec un OUTER JOIN ?
-- (On renverrait probablement tous les films)