Licence Fondamentale d'Informatique

lundi 25 mars 2013

Exercice Langage SQL : BD Cinéma (Partie1)

Énoncé de l'Exercice (BD Cinéma):
Les attributs NUM, NUM, NUMA, NUMC, NUMS sont des identifiants uniques (clés primaires) pour respectivement : FILM, PERSONNE, ACTEUR, CINÉMA, SALLE.
Un de ces attributs utilisé comme attribut d’une autre relation est une clé étrangère qui renvoie à la clé primaire de la relation correspondante, par exemple dans GÉNÉRIQUE, NUMF renvoie au NUMF de FILM et est défini sur le même domaine.
De plus, les attributs RÉALISATEUR dans FILM et NUMA dans ACTEUR sont définis sur le domaine des NUMP, et renvoient au NUMP de la personne correspondante.

SQ1

Réaliser les Requêtes suivantes:

Requête 1 : Retrouver la liste de tous les films.
Requête 2 : Retrouver la liste des films dont la longueur dépasse 180 min.
Requête 3 : Donner la liste de tous les genres de film.
Requête 4 : Trouver le titre et l’année des films de science fiction dont le budget dépasse 5.000.000 $.
Requête 5 : Donner le nombre de films par genre.
Requête 6 : Donner le nombre de films de 1960 par genre.
Requête 7 : Trouver le titre des films réalisés par Roman Polanski.
Requête 8 : Quels sont les acteurs comiques (nom, prénom) qui ont joué dans un film de Spielberg.
Requête 9 : Trouver le titre et l’année du film le plus long.
Requête 10 : Nom et prénom des acteurs qui ont joué Gavroche dans les différentes versions des « Misérables » avec les dates correspondantes.
Requête 11 : Donner le nom et le prénom des réalisateurs qui ont joué dans au moins un de leurs propres films.

Requête 1 : Retrouver la liste de tous les films.
 
SELECT * FROM FILM
 
Requête 2 : Retrouver la liste des films dont la longueur dépasse 180 min.
 
SELECT * FROM FILM
WHERE LONGUEUR > 180
 
Requête 3 : Donner la liste de tous les genres de film.
 
SELECT DISTINCT GENRE
FROM FILM
 
Requête 4: Trouver le titre et l’’année des films de science fiction dont le budget dépasse 5.000.000 $.
 
SELECT TITRE, ANNÉE 
FROM FILM
WHERE GENRE = ‘SciFi’
ANDBUDGET > 5000000
 
Requête 5 : Donner le nombre de films par genre.
 
SELECT  GENRE, COUNT (*)
FROM FILM
GROUP BY GENRE
 
Requête 6 : Donner le nombre de films de 1960 par genre.
 
SELECT GENRE, COUNT (*)
FROM FILM
WHERE ANNÉE = 1960
GROUP BY GENRE
 
Requête 7  Trouver le titre des films réalisés par Roman Polanski
 
Forme plate :
 SELECT F.TITRE
 FROM FILM F, PERSONNE P
 WHERE F.RÉALISATEUR = P.NUMP
 AND P.PRÉNOM = ‘Roman’
 AND P.NOM = ‘Polanski’
 
Forme imbriquée :
 SELECT TITRE
 FROM FILM
 WHERE RÉALISATEUR IN (
      SELECT NUMP
      FROM PERSONNE
      WHERE PRÉNOM = ‘Roman’
      AND NOM = ‘Polanski’ )
 
Requête 8 : Quels sont les acteurs comiques (nom, prénom) qui ont joué dans un film de Spielberg.
 
Forme plate :
 SELECT PA.PRÉNOM, PA.NOM
 FROM PERSONNE PA, DISTRIBUTION D, FILM F, PERSONNE PR
 WHERE PA.NUMP = D.NUMA
AND D.SPÉCIALITÉ = ‘Comique’
AND D.NUMF = F.NUMF
AND F.RÉALISATEUR = PR.NUMP
AND PR.NOM = ‘Spielberg’
 
Forme imbriquée :
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN (
      SELECT NUMA
      FROM DISTRIBUTION
      WHERE SPÉCIALITÉ = ‘Comique’
      ANDNUMF IN (
           SELECT NUMF
           FROMFILM
           WHERE RÉALISATEUR IN (
              SELECT NUMP
              FROMPERSONNE
              WHERE NOM = ‘Spielberg’ ) ) )
 
Requête 9 : Trouver le titre et l’année du film le plus long.
 
Forme imbriquée :
 SELECT TITRE, ANNÉE
 FROM FILM
 WHERE LONGUEUR = (
      SELECT MAX (LONGUEUR)
      FROM FILM )
 
Requête 10 : Nom et prénom des acteurs qui ont joué Gavroche dans les différentes versions des « Misérables » avec les dates correspondantes.
 
Forme plate :
 SELECT P.PRÉNOM, P.NOM, F.DATE
 FROM PERSONNE P, DISTRIBUTION D, FILM F
 WHERE P.NUMP = D.NUMA
 AND D.RÔLE = ‘Gavroche’
 AND G.NUMF = F.NUMF
 AND F.TITRE = ‘Les misérables’
 
Forme imbriquée :
 SELECT P.PRÉNOM, P.NOM, F.DATE
 FROM PERSONNE P, FILM F
 WHERE F.TITRE = ‘Les misérables’
 AND (P.NUMP, F.NUMF) IN (
      SELECT NUMA, NUMF
      FROM DISTRIBUTION
      WHERE RÔLE = ‘Gavroche’ )
 
Requête 11 : Donner le nom et le prénom des réalisateurs qui ont joué dans au moins un de leurs propres films.
 
Forme plate :
 SELECT DISTINCT P.PRÉNOM, P.NOM
 FROM PERSONNE P, FILM F, DISTRIBUTION D
 WHERE P.NUMP = F.RÉALISATEUR
 AND F.NUMF = D.NUMF
 AND D.NUMA = F.RÉALISATEUR
 
Forme imbriquée SQL-92 :
 SELECT DISTINCT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN (
      SELECT RÉALISATEUR
      FROM FILM
      WHERE (RÉALISATEUR, NUMF) IN (
           SELECT NUMA, NUMF
           FROM DISTRIBUTION ) )

AhmedOumezzine

15:49

Exercice Langage SQL : Les livraisons (Partie 3)

Enoncé de l'exercice: (Les livraison Suite...)

Soit la base relationnelle de données PUF de schéma :
U (NumU, NomU, VilleU)
P (NumP, NomP, Couleur, Poids)
F (NumF, NomF, Statut, VilleF)
PUF (NumP, NumU, NumF, Quantité)
Décrivant le fait que (avec des DF évidentes) :
U : une usine est d’écrite par son numéro NumU, son nom NomU et la ville VilleU où elle est située
P : un produit est décrit par son numéro NumP, son nom NomP, sa couleur et son poids
F : un fournisseur est décrit par son numéro NumP, son nom NomF, son statut (sous-traitant, client…) et la ville VilleF où il est domicilié
PUF : le produit de numéro NumP a été délivré à l’usine de numéro NumU par le fournisseur de numéro NumF dans une quantité donnée.
Exprimer en SQL les requêtes suivantes :
17- Donnez les numéros des fournisseurs qui fournissent au moins un produit fourni par au moins un fournisseur qui fournit au moins un produit rouge
18- Donnez tous les triplets (VilleF, NumP, VilleU) tels qu’un fournisseur de la première ville VilleF approvisionne une usine de la deuxième ville VilleU avec un produit NumP
19- Même question que précédemment mais sans les triplets où les deux villes sont identiques
20- Donnez les numéros des produits qui sont livrés à toutes les usines de Paris
21- Donnez les numéros des fournisseurs qui approvisionnent toutes les usines avec un même produit
22- Donnez les numéros des usines qui achètent au fournisseur de numéro 3 tous les produits qu’il fournit
23- Donnez les numéros des usines qui s’approvisionnent uniquement chez le fournisseur de numéro 3

17) Donnez les numéros des fournisseurs qui fournissent au moins un produit fourni par au moins un fournisseur qui fournit au moins un produit rouge
 
    P WHERE Couleur=‘Rouge’ AND P.Np=PUF2.Np AND
    PUF2.Nf=PUF1.Nf AND PUF1.Np=PUF.Np
       Ou bien SELECT DISTINCT Nf FROM PUF WHERE Np IN
      (SELECT Np FROM PUF WHERE Nf IN (SELECT Nf FROM
      PUF WHERE Np IN (SELECT Np FROM P WHERE
      Couleur=‘Rouge’)))
 
18) Donnez tous les triplets (VilleF, NumP, VilleU) tels qu"’"un fournisseur de la première ville VilleF approvisionne une usine de la deuxième ville VilleU avec un produit NumP 
 
SELECT DISTINCT PUF.Nf FROM PUF, PUF PUF1, PUF PUF2,
SELECT DISTINCT F.Ville, Np, U.Ville FROM PUF, U, F
    WHERE PUF.Nf=F.Nf AND PUF.Nu=U.Nu
19) Même question que précédemment mais sans les triplets où les deux villes sont identiques
 
SELECT DISTINCT F.Ville, NP, U.Ville FROM PUF, U, F
    WHEREF.VilleU.VilleANDPUF.Nf=F.NfAND
    PUF.Nu=U.Nu
 
20) Donnez les numéros des produits qui sont livrés à toutes les usines de Paris
 
SELECT Np FROM PUF WHERE NOT EXISTS(SELECT Nu
    FROM U WHERE NOT EXISTS (SELECT * FROM PUF WHERE
    NOT (Ville=‘Paris’) OR (P.Np=PUF.Np AND U.Nu=PUF.Nu))
 
21) Donnez les numéros des fournisseurs qui approvisionnent toutes les usines avec un même produit
 
SELECT NF FROM PUF WHERE NOT EXISTS (SELECT Nu
    FROM U WHERE NOT EXISTS (SELECT * FROM PUF PUF1
    WHEREF.Nf=PUF1.NFANDU.Nu=PUF1.NuAND
    PUF.Np=PUF1.Np))
 
22) Donnez les numéros des usines qui achètent au fournisseur de numéro 3 tous les produits qu’il fournit
 
  SELECT Nf FROM F WHERE EXISTS (SELECT Np FROM P
      WHERE NOT EXISTS (SELECT Nu FROM U WHERE NOT
      EXISTS (SELECT * FROM PUF WHERE F.Nf=PUF.Nf AND
      U.Nu=PUF.Nu AND P.Np=PUF.Np)))
 
23) Donnez les numéros des usines qui s"’"approvisionnent uniquement chez le fournisseur de numéro 3
 
SELECT Nu FROM U WHERE Nu NOT IN (SELECT Nu FROM
    PUF WHERE Nf3)

AhmedOumezzine