Licence Fondamentale d'Informatique: base

Affichage des articles dont le libellé est base_donnée. Afficher tous les articles

dimanche 21 avril 2013

Exercice : vues

Créez une vue V_EMP contenant : le matricule, le nom, le numéro de département, la somme de la commission et du salaire baptisée GAINS, le lieu du département.
Sélectionnez les lignes de V_EMP dont le salaire total est supérieur à 10.000 F
Essayez de mettre à jour le nom de l'employé MARTIN à travers la vue V_EMP.
Créez une vue VEMP10 qui ne contienne que les employés du département 10 de la table EMP (n'utilisez pas l'option CHECK pour cette création).

Détruisez cette vue VEMP10 et recréez-la avec l'option CHECK.
Essayez d'insérer un employé BALARD pour le département 30. Que se passe-t-il ?
Liste des salaires des employés avec le pourcentage par rapport au total des salaires de leur département (utilisez une vue qui fournira le total des salaires).

---------------------------------

corrogier

create view v_emp (matr, nom, dept, GAINS, lieu) as
select matr, nome, emp.dept, sal + nvl(comm,0), lieu
from emp,dept
where emp.dept = dept.dept;select * from v_emp where SALTOT > 10000;
update v_emp set nome = 'TOTO' where nome = 'MARTIN';
-----
create view vemp10 as select * from emp where dept=10;
insert into vemp10 values
(1117, 'SOULIER', 'RANGER', 7839, '18/05/81', 10000, 300, 20);
drop view vemp10;
create view VEMP10 as select * from emp
where dept = 10 with check option;
insert into vemp10 values
(0007, 'BALARD', 'OUVRIER', 7839, '18-MAY-81', 10000, 300, 20);
ORA-01402: view WITH CHECK OPTION where-clause violation
update vemp10 set dept=20 where nome='LESAGE';
On ne peut pas non plus utiliser update pour la meme raison.
-----
create view vt (dept, total) as
select dept, sum(sal+nvl(comm,0)) from emp group by dept;
select nome, sal+nvl(comm,0) salaire, (sal+nvl(comm,0))/total pourcentage
from vt,emp
where vt.dept=emp.dept;
Avec les nouvelles possibilités offertes par SQL (select emboîté dans la clause from) on peut se passer de la création d'une vue:
select nome, sal+nvl(comm,0) salaire, round((sal+nvl(comm,0))/total*100) pourcentage, emp.dept
from emp, (select dept, sum(sal+nvl(comm,0)) total from emp group bydept) e1
where e1.dept = emp.dept;
Si on veut bien utiliser les facilités offertes par SQL*PLUS(pas dans SQL) :
break on dept skip 1
compute sum of salaire on dept
column pourcentage format a7
select nome, sal+nvl(comm,0) salaire,
to_char(round((sal+nvl(comm,0))/total*100), '999')||' %' pourcentage, emp.dept
from emp, (select dept, sum(sal+nvl(comm,0)) total from emp group bydept) e1
where e1.dept = emp.dept
order by 4;

AhmedOumezzine

13:42

Les vues peuvent être considérées comme des tables virtuelles. Généralement, une table contient un jeu de définitions et elle est destinée à stocker physiquement les données. Une vue a également un jeu de définitions, créé au-dessus des tables ou d’autres vues, et elle ne stocke pas physiquement les données.
La syntaxe pour la création d’une vue est comme suit :
CREATE VIEW "nom de vue" AS "instruction SQL"
"instruction SQL" peut être n’importe quelle instruction SQL que nous avons vue dans ce didacticiel.
À titre d’illustration, utilisons un exemple simple. Supposons que nous avons la table suivante :
TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date date)

et pour créer une vue appelée V_Customer contenant seulement les colonnes First_Name, Last_Name et Country de cette table, il faut saisir :
CREATE VIEW V_Customer
AS SELECT First_Name, Last_Name, Country
FROM Customer
Nous avons à présent une vue appelée V_Customer avec la structure suivante :
View V_Customer
(First_Name char(50),
Last_Name char(50),
Country char(25))
Il est aussi possible d’utiliser une vue pour appliquer des jointures à deux tables. Dans ce cas, les utilisateurs ne verront qu’une vue au lieu de deux tables, disposant ainsi d’instructions SQL beaucoup plus simples. Supposons que nous avons les deux tables suivantes :
Table Store_Information

store_name	Sales	Date
Los Angeles	1500 €	05-Jan-1999
San Diego	250 €	07-Jan-1999
Los Angeles	300 €	08-Jan-1999
Boston	700 €	08-Jan-1999

Table Geography

region_name	store_name
East	Boston
East	New York
West	Los Angeles
West	San Diego

et pour créer une vue contenant des ventes par données de région, il faudra définir l’instruction SQL suivante :
CREATE VIEW V_REGION_SALES
AS SELECT A1.region_name REGION, SUM(A2.Sales) SALES
FROM Geography A1, Store_Information A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
Nous avons la vue V_REGION_SALES, définie pour stocker les ventes par enregistrements de région. Pour connaître le contenu de cette vue, il faut saisir :
SELECT * FROM V_REGION_SALES
Résultat :

REGION	SALES
East	700 €
West	2050 €

AhmedOumezzine

13:11

lundi 25 mars 2013

Exercice Langage SQL : BD Cinéma (Partie 4)

Enoncé de l'Exercice (BD Cinéma Suite):
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

Schéma complémentaire
RÉCOMPENSE (NUMR, CATÉGORIE, FESTIVAL)
RÉCOMPENSE_FILM (NUMF, ANNÉE, NUMR)
RÉCOMPENSE_ACTEUR (NUMA, NUMF, ANNÉE, NUMR)
Pour répondre aux questions suivantes, il faut noter que lorsqu'un acteur reçoit une récompense, le film en reçoit une indirectement.
Ce schéma complémentaire conduit à utiliser une union dans les requêtes.

Réaliser les Requêtes suivantes:
Requête 24 : Donner le titre des films qui ont été primés au moins une fois (y compris les récompenses des acteurs jouant dans le film).
Requête 25 : Lister les cinémas qui ont exclusivement passé des films primés.
Requête 26 : Donner le titre des films qui ont reçu au moins trois récompenses.
Requête 27 : Noms et prénoms des acteurs qui ont reçu plus de récompenses qu'aucun acteur qui a joué dans "Casablanca" n'en a eu.

Requête 24 : Donner le titre des films qui ont été primés au moins une fois (y compris les récompenses des acteurs jouant dans le film).
 
Forme plate:
 SELECT DISTINCT F.TITRE, F.ANNÉE
 FROM FILM F, RÉCOMPENSE_FILM RF
 WHERE F.NUMF = RF.NUMF
 UNION
 SELECT DISTINCT F.TITRE, F.ANNÉE
 FROM FILM F, RÉCOMPENSE_ACTEUR RA
WHERE F.NUMF = RA.NUMF
 
Forme imbriquée:
 SELECT TITRE, ANNÉE
 FROM FILM
 WHERE NUMF IN (
      SELECT NUMF
      FROM RÉCOMPENSE_FILM
      UNION
      SELECT NUMF FROM RÉCOMPENSE_ACTEUR )
 
Requête 25 : Lister les cinémas qui ont exclusivement passé des films primés.
 
Forme imbriquée:
 SELECT NOM, VILLE
 FROM CINÉMA C
 WHERE NOT EXISTS (
      SELECT * FROM PASSE P
      WHERE P.NUMC = C.NUMC
      AND NOT EXISTS (SELECT * FROM RÉCOMPENSE_FILM RF
           WHERE RF.NUMF = P.NUMF )
      ANDNOT EXISTS (
           SELECT * FROM RÉCOMPENSE_ACTEUR RA
WHERE RA.NUMF = P.NUMF ) )
 
Forme imbriquée– prédicat NOT EXISTS :
 SELECT NOM, VILLE
 FROM CINÉMA C
 WHERE NOT EXISTS (
      SELECT * FROM PASSE P
      WHERE P.NUMC = C.NUMC
      AND NOT EXISTS ( SELECT * FROM (
                SELECT NUMF
                FROM RÉCOMPENSE_FILM
                UNION
                SELECT NUMF
                FROM RÉCOMPENSE_ACTEUR ) AS R
WHERE R.NUMF = P.NUMF ) )
 
 
Forme imbriquée – prédicat NOT IN :
 SELECT NOM, VILLE
 FROM CINÉMA
 WHERE NUMC NOT IN (
      SELECT NUMC
      FROM PASSE
      WHERE NUMF NOT IN (
           SELECT R.NUMF FROM (
                SELECT NUMF
                FROM RÉCOMPENSE_FILM
                UNION
                SELECT NUMF
                FROM RÉCOMPENSE_ACTEUR ) AS R ) ) )
 
Requête 26 : Donner le titre des films qui ont reçu au moins trois récompenses.
 
Forme imbriquée:
 SELECT TITRE, ANNÉE
 FROM FILM
 WHERE NUMF IN (
      SELECT R.NUMF
      FROM (
           SELECT NUMF
           FROM RÉCOMPENSE_FILM
           UNION
           SELECT NUMF
           FROM RÉCOMPENSE_ACTEUR ) AS R
      GROUP BY R.NUMF
      HAVING COUNT (*) >= 3 ) 
Requête 27 : Noms et prénoms des acteurs qui ont reçu plus de récompenses qu”'”aucun acteur qui a joué dans "Casablanca" n”'”en a eu.
 
Forme imbriquée:
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN (
      SELECT NUMA
      FROM RÉCOMPENSE_ACTEUR
      GROUP BY NUMA
      HAVING COUNT (*) > (
           SELECT MAX (
                SELECT COUNT (*)
                FROM RÉCOMPENSE_ACTEUR
                WHERE NUMA IN (
                    SELECT NUMA
                    FROM DISTRIBUTION
                    WHERE NUMF IN (
                         SELECT NUMF
                         FROM FILM
                         WHERE TITRE = ‘Casablanca’))
                GROUP BY NUMA)))

AhmedOumezzine

15:51

Exercice langage SQL : BD Cinéma (Partie 3)

Réaliser les Requêtes suivantes:
Requête 20 : Trouver les couples acteur-réalisateur (noms et prénoms) tels que l’un a dirigé l’autre sur un film et vice-versa sur un autre.
Requête 21 : Trouver le nom, le prénom, le numéro des acteurs qui ont joué dans tous les films de Lelouch, s'il y en a.
Requête 22 : Pour chaque film de Bergman, trouver le nom et le prénom de l'acteur qui a eu le plus gros salaire.
Requête 23 : Donner le nom et le prénom des réalisateurs qui ont eu le plus gros salaire sur un de leurs films (par comparaison avec ceux des acteurs).

Requête 20 : Trouver les couples acteur-réalisateur (noms et prénoms) tels que l“’“un a dirigé l’autre sur un film et vice-versa sur un autre.
 
Forme plate :
 
SELECT DISTINCT P1.PRENOM, P1.NOM, P2.PRENOM, P2.NOM
FROM PERSONNE P1, PERSONNE P2, FILM F1, FILM F2,
DISTRIBUTION D1, DISTRIBUTION D2
WHERE P1.NUMP > P2.NUMP
AND P1.NUMP = F1.REALISATEUR
AND P2.NUMP = F2.REALISATEUR
AND F1.NUMF = D1.NUMF
AND D1.NUMA = F2.RÉALISATEUR
AND F2.NUMF = D2.NUMF
AND D2.NUMA = F1.REALISATEUR
 
Forme imbriquée:
 
 SELECT DISTINCT P1.PRÉNOM, P1.NOM, P2.PRÉNOM, P2.NOM
 FROM PERSONNE P1, PERSONNE P2
 WHERE (P1.NUMP, P2.NUMP) IN (
      SELECT F1.RÉALISATEUR, F2.RÉALISATEUR
      FROM FILM F1, FILM F2, DISTRIBUTION D1,
                DISTRIBUTION D2
      WHERE F1.RÉALISATEUR > F2.RÉALISATEUR
AND F1.NUMF = D1.NUMF
AND D1.NUMA = F2.RÉALISATEUR
AND F2.NUMF = D2.NUMF
AND D2.NUMA = F1.RÉALISATEUR)
 
Requête 21 : Trouver le nom, le prénom, le numéro des acteurs qui ont joué dans tous les films de Lelouch, s“’“il y en a.
 
Forme imbriquée – prédicat EXISTS : « dans un des films »
 
 SELECT NOM, PRÉNOM FROM PERSONNE P
 WHERE EXISTS (SELECT * FROM FILM F
      WHERE RÉALISATEUR IN (SELECT NUMP FROM PERSONNE
           WHERE NOM = ‘Lelouch’)
      ANDEXISTS (SELECT * FROM DISTRIBUTION D
WHERE D.NUMF = F.NUMF
AND D.NUMA = P.NUMP)
                                                        )
 
Requête 22 : Pour chaque film de Bergman, trouver le nom et le prénom de L“’ “acteur qui a eu le plus gros salaire.
 
Forme imbriquée – prédicat NOT EXISTS : un seul rôle par acteur
 
 SELECT F.TITRE, PA.PRÉNOM, PA.NOM
 FROM FILM F, DISTRIBUTION D1, PERSONNE PA
 WHERE F.NUMF = D1.NUMF
 ANDD1.NUMA = PA.NUMP
 ANDRÉALISATEUR IN (
      SELECT NUMP
      FROM PERSONNE
      WHERE NOM = “Bergman”)
 ANDNOT EXISTS (
      SELECT * FROM DISTRIBUTION D2
      WHERE D2.NUMF = D1.NUMF
      AND D2.SALAIRE > D1.SALAIRE )
 
Forme imbriquée + – prédicat > ALL : possibilité de plusieurs rôles pour un même acteur
 
 SELECT F.TITRE, PA.PRÉNOM, PA.NOM
 FROM FILM F, DISTRIBUTION D1, PERSONNE PA
 WHERE F.NUMF = D1.NUMF
 ANDD1.NUMA = PA.NUMP
 ANDRÉALISATEUR IN (
      SELECT NUMP
      FROM PERSONNE
      WHERE NOM = ‘Bergman’)
 GROUP BY D1.NUMF, D1.NUMA, F.TITRE, PA.PRÉNOM, PA.NOM
 HAVING SUM (SALAIRE) > ALL (
      SELECT SUM (SALAIRE)
      FROM DISTRIBUTION D2
      WHERE D2.NUMF = D1.NUMF
AND D2.NUMA   D1.NUMA
GROUP BY D2.NUMA)
 
Forme imbriquée: possibilité de plusieurs rôles pour un même acteur
 
 SELECT F.TITRE, PA.PRÉNOM, PA.NOM
 FROM FILM F, PERSONNE PA
WHERE (F.NUMF, PA.NUMP) IN (SELECT D1.NUMF, D1.NUMA
   FROM DISTRIBUTION D1 WHERE D1.NUMF IN (
       SELECT NUMF FROM FILM
       WHERE RÉALISATEUR IN (SELECT NUMP FROM PERSONNE
           WHERE NOM = ‘Bergman’ ) )
   GROUP BY D1.NUMF, D1.NUMA
   HAVING SUM (D1.SALAIRE) = (
       SELECT MAX (
           SELECT SUM (D2.SALAIRE)
           FROM DISTRIBUTION D2
           WHERE D2.NUMF = D1.NUMF
           GROUP BY D2.NUMA))
 
Utilisation d’une vue GROUPée:
 
 CREATE VIEW SALAIRE_TOTAL_ACTEUR_FILM
        (NUMA, NUMF, SALAIRE_TOTAL)
 AS SELECT NUMA, NUMF, SUM (SALAIRE)
        FROM FILM GROUP BY NUMA, NUMF
SELECT F.TITRE, PA.PRÉNOM, PA.NOM
FROM FILM F, SALAIRE_TOTAL_ACTEUR_FILM D1, PERSONNE PA
WHERE F.NUMF = D1.NUMF
AND D1.NUMA = PA.NUMP
AND RÉALISATEUR IN (SELECT NUMP FROM PERSONNE
    WHERE NOM = ‘Bergman’)
ANDNOT EXISTS ( SELECT * FROM SALAIRE_TOTAL_ACTEUR_FILM D2
    WHERE D2.NUMF = D1.NUMF
    AND D2.SALAIRE_TOTAL > D1.SALAIRE_TOTAL )
 
Requête 23 : Donner le nom et le prénom des réalisateurs qui ont eu le plus gros salaire sur un de leurs films (par comparaison avec ceux des acteurs).
 
Forme imbriquée :
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN (
      SELECT RÉALISATEUR
      FROM FILM F
      WHERE SALAIRE_RÉAL > (
           SELECT MAX (SALAIRE)
           FROM DISTRIBUTION D
           WHERE D.NUMF = F.NUMF ) )
 
Forme imbriquée 2:
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN ( SELECT RÉALISATEUR
FROM FILM F
WHERE SALAIRE_RÉAL > ALL (SELECT SUM (SALAIRE)
   FROM DISTRIBUTION D
   WHERE D.NUMF = F.NUMF
   GROUP BY NUMA ) )
 
Forme imbriquée 3 :
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN (
      SELECT RÉALISATEUR
      FROM FILM F
      WHERE SALAIRE_RÉAL + (SELECT SUM (SALAIRE)
           FROM DISTRIBUTION D1
           WHERE D1.NUMF = F.NUMF
           AND D1.NUMA = F.RÉALISATEUR ) >(SELECT MAX (
           SELECT SUM (SALAIRE)
           FROM DISTRIBUTION D2
           WHERE D2.NUMF = F.NUMF
           GROUP BY D2.NUMA ) ) )

AhmedOumezzine

15:51

Exercice Langage SQL : BD Cinéma (Partie 2)

Réaliser les Requêtes suivantes:
Requête 12 : Quel est le total des salaires des acteurs du film « Nuits blanches à Seattle ».
Requête 13 : Donner la moyenne des salaires des acteurs par film, avec le titre et l’année correspondants.
Requête 14 : Trouver le genre des films des années 80 dont le budget moyen dépasse 200.000 $.
Requête 15 : Pour chaque film de Spielberg (titre, année), donner le total des salaires des acteurs.
Requête 16 : Lister les cinémas dont la taille moyenne d'écran est supérieure à 40 mètres carrés.
Requête 17 : Quels sont les cinémas Parisiens de la Fox, avec le film correspondant, qui passent un film d'Elia Kazan avant 22 heures dans une salle d'au moins 200 places et d'écran de taille supérieure à 30 m carrés.
Requête 18 : Trouver le titre des films qui ne passent à aucun cinéma de la compagnie FOX.
Requête 19 : Trouver le nom et le prénom des acteurs qui ont eu un salaire plus important dans un film particulier que le salaire du réalisateur du même film.

Requête 12 : Quel est le total des salaires des acteurs du film « Nuits blanches à Seattle ».
 
Forme plate:
 SELECT SUM (D.SALAIRE)
 FROM DISTRIBUTION D, FILM F
 WHERE DISTRIBUTION.NUMF = F.NUMF
 AND F.TITRE = ‘Nuits blanches à Seattle’
 
Forme imbriquée :
 SELECT SUM (SALAIRE)
FROM DISTRIBUTION
WHERE NUMF IN (SELECT NUMF
FROM FILM WHERE TITRE = ‘Nuits blanches à Seattle’ )
 
Requête 13 : Donner la moyenne des salaires des acteurs par film, avec le titre et l’année correspondants.
 
SELECT F.TITRE, F.ANNÉE, AVG (D.SALAIRE)
 FROM FILM F, DISTRIBUTION D
 WHERE F.NUMF = D.NUMF
 GROUP BY F.TITRE, F.ANNÉE
 
Requête 14 : Trouver le genre des films des années 80 dont le budget moyen dépasse 200.000 $.
 
SELECT GENRE FROM FILM
   WHERE ANNÉE BETWEEN 1980 AND 1989
   GROUP BY GENRE
   HAVING AVG (BUDGET) > 200000
 
Requête 15 : Pour chaque film de Spielberg (titre, année), donner le total des salaires des acteurs.
 
Forme plate :
 SELECT F.TITRE, F.ANNÉE, SUM (D.SALAIRE)
 FROM FILM F, DISTRIBUTION D, PERSONNE P
 WHERE F.NUMF = D.NUMF
 AND F.RÉALISATEUR = P.NUMP
 AND P.NOM = ‘Spielberg’
 GROUP BY F.TITRE, F.ANNÉE
 
Forme imbriquée :
 SELECT F.TITRE, F.ANNÉE, SUM (D.SALAIRE)
 FROM FILM F, DISTRIBUTION D
 WHERE F.NUMF = D.NUMF
 AND F.RÉALISATEUR IN (SELECT NUMP FROM PERSONNE
      WHERE NOM = ‘Spielberg’ )
 GROUP BY F.TITRE, F.ANNÉE
Forme imbriquée SQL-92 :
 SELECT F.TITRE, F.ANNÉE, X.SUMSAL
 FROM FILM F, (SELECT NUMF, SUM (SALAIRE) AS SUMSAL
      FROM DISTRIBUTION
    GROUP BY NUMF ) AS X
WHERE F.NUMF = X.NUMF
AND F.RÉALISATEUR IN (SELECT NUMP FROM PERSONNE
    WHERE NOM = ‘Spielberg’ )
 
Requête 16 : Lister les cinémas dont la taille moyenne d"'"écran est supérieure à 40 mètres carrés.
 
Forme plate :
 SELECT C.NOM, C.VILLE
 FROM CINÉMA C, SALLE S
 WHERE C.NUMC = S.NUMC
 GROUP BY C.NUMC, C.NOM, C.VILLE
 HAVING AVG (S.TAILLE_ÉCRAN) > 40 )
 
Forme imbriquée SQL-92 :
 SELECT NOM, VILLE FROM CINÉMA
 WHERE NUMC IN (SELECT NUMC FROM SALLE
      GROUP BY NUMC
      HAVING AVG (TAILLE_ÉCRAN) > 40 )
 
Requête 17 : Quels sont les cinémas Parisiens de la Fox, avec le film correspondant, qui passent un film d"'"Elia Kazan avant 22 heures dans une salle d'au moins 200 places et d'écran de taille supérieure à 30 m carrés.
 
Forme plate :
 SELECT DISTINCT C.NOM, F.TITRE
 FROM CINÉMA C, SALLE S, PASSE P, FILM F, PERSONNE P
 WHERE C.COMPAGNIE = ‘Fox’
 ANDC.VILLE = ‘Paris’ AND C.NUMC = S.NUMC
AND S.NBPLACES >= 200 AND S.TAILLE_ÉCRAN > 30
AND S.NUMC = P.NUMC AND S.NUMS = P.NUMS
AND P.HORAIRE < ’22 :00’ AND P.NUMF = F.NUMF
AND F.RÉALISATEUR = P.NUMP AND P.PRÉNOM = ‘Elia’
AND P.NOM = ‘Kazan’
 
Forme imbriquée:
 SELECT DISTINCT C.NOM, F.TITRE
 FROM CINÉMA C, FILM F
 WHERE C.COMPAGNIE = ‘Fox’  ANDC.VILLE = ‘Paris’
 AND (C.NUMC, F.NUMF) IN (
      SELECT S.NUMC, P.NUMF FROM SALLE S, PASSE P
      WHERE S.NBPLACES >= 200
      AND S.TAILLE_ÉCRAN > 30 AND S.NUMC = P.NUMC
      AND S.NUMS = P.NUMS
      AND P.HORAIRE < ’22 :00’ )
 ANDF.RÉALISATEUR IN (
      SELECT NUMP FROM PERSONNE
      WHERE PRÉNOM = ‘Elia’ AND NOM = ‘Kazan’ )
 
Requête 18 : Trouver le titre des films qui ne passent à aucun cinéma de la Compagnie FOX.
 
Forme plate : pour trouver ceux qui passent dans un cinéma de la Fox
 SELECT DISTINCT F.NUMF, F.TITRE
 FROM FILM F, PASSE P, CINÉMA C
 WHERE F.NUMF = P.NUMF
 AND P.NUMC = C.NUMC
 AND C.COMPAGNIE = ‘Fox’
 
Forme imbriquée 1 – prédicat IN : pour trouver ceux qui passent dans un cinéma de la Fox
 SELECT DISTINCT NUMF, TITRE
 FROM FILM WHERE NUMF IN (
      SELECT NUMF FROM PASSE
           WHERE NUMC IN (SELECT NUMC FROM CINÉMA
           WHERE COMPAGNIE = ‘Fox’ ) )
 
Forme imbriquée 2 – prédicat EXISTS : toujours pour trouver ceux qui passent dans un cinéma de la Fox
 SELECT DISTINCT NUMF, TITRE
 FROM FILM F WHERE EXISTS (
      SELECT * FROM PASSE P
      WHERE P.NUMF = F.NUMF
AND EXISTS ( SELECT * FROM
CINÉMA C WHERE C.NUMC = P.NUMC
AND COMPAGNIE = ‘Fox’ ) )
 
La négation de ces deux dernières formes permet d’exprimer la requête initiale : les films qui ne passent à aucun des cinémas de la Fox.
 
Forme imbriquée 1 – prédicat NOT IN :
SELECT DISTINCT NUMF, TITRE
FROM FILM WHERE NUMF NOT IN (
    SELECT NUMF FROM PASSE WHERE NUMC IN (
        SELECT NUMC FROMCINÉMA
        WHERE COMPAGNIE = ‘Fox’ ) )
 
Forme imbriquée 2 – prédicat NOT EXISTS : pour trouver ceux qui ne passent dans aucun cinéma de la Fox
 SELECT DISTINCT NUMF, TITRE FROM FILM F
 WHERE NOT EXISTS (
      SELECT * FROM PASSE P
      WHERE P.NUMF = F.NUMF
      ANDEXISTS (SELECT * FROM CINÉMA C
           WHERE C.NUMC = P.NUMC
           AND COMPAGNIE = ‘Fox’ ) )
 
Pour finalement arriver à la forme la plus simple, où seul le prédicat NOT EXISTS provoque un niveau d’imbrication.
 
Forme 3 – prédicat NOT EXISTS uniquement :
 SELECT DISTINCT NUMF, TITRE FROM FILM F
 WHERE NOT EXISTS (
      SELECT * FROM PASSE P, CINÉMA C
      WHERE F.NUMF = P.NUMF
      AND P.NUMC = C.NUMC
      AND COMPAGNIE = ‘Fox’ )
 
Forme complète :
 SELECT DISTINCT NUMF, TITRE
 FROM FILM F WHERE NUMF IN (
      SELECT NUMF FROM PASSE )
 AND NOT EXISTS (
      SELECT * FROM PASSE P, CINÉMA C
      WHERE F.NUMF = P.NUMF
      AND P.NUMC = C.NUMC
      AND COMPAGNIE = ‘Fox’ )
 
Requête 19 : Trouver le nom et le prénom des acteurs qui ont eu un salaire plus important dans un film particulier que le salaire du réalisateur du même film.
 
Forme plate :
 SELECT PA.PRÉNOM, PA.NOM
 FROM PERSONNE PA, DISTRIBUTION D, FILM F
 WHERE PA.NUMP = D.NUMA
 AND D.NUMF = F.NUMF
 AND D.SALAIRE > F.SALAIRE_RÉAL
 
Forme imbriquée 1 :
 SELECT PRÉNOM, NOM
 FROM PERSONNE
 WHERE NUMP IN ( SELECT D.NUMA
      FROM DISTRIBUTION D, FILM F
      WHERE D.NUMF = F.NUMF
      AND D.SALAIRE > F.SALAIRE_RÉAL )
 
Forme imbriquée 2 :
 SELECT PRÉNOM, NOM FROM PERSONNE
 WHERE NUMP IN (SELECT NUMA
      FROM DISTRIBUTION D
      WHERE D.SALAIRE > (SELECT F.SALAIRE_RÉAL FROMFILM F
 
Forme imbriquée :
 SELECT DISTINCT PA.PRÉNOM, PA.NOM
 FROM PERSONNE PA, DISTRIBUTION D
 WHERE PA.NUMP = D.NUMA
 GROUP BY D.NUMA, D.NUMF, PA.PRÉNOM, PA.NOM
 HAVING SUM (SALAIRE) > (
      SELECT SALAIRE_RÉAL
      FROM FILM F
      WHERE D.NUMF = F.NUMF

AhmedOumezzine

15:50

Exercice : vues

Create View

Exercice Langage SQL : BD Cinéma (Partie 4)

Exercice langage SQL : BD Cinéma (Partie 3)

Exercice Langage SQL : BD Cinéma (Partie 2)