Licence Fondamentale d'Informatique

vendredi 12 avril 2013

Pour aller plus loin, les VLANS

Pour aller plus loin, les VLANs

Au-delà de la commutation (le fait d'aiguiller une trame vers un port) les switchs ont acquis de nouvelles capacités au cours du temps pour améliorer le fonctionnement des réseaux. Une de ces fonctionnalités est très répandue et intéressante, il s'agit des VLANs.

Qu'est-ce qu'un VLAN ?

Un VLAN est un LAN virtuel (ou virtual LAN en anglais).
Sachant qu'un LAN est un réseau local (ou Local Area Network en anglais) un VLAN sera donc unréseau local virtuel.

Cela ne nous aide malheureusement pas beaucoup à mieux comprendre de quoi il s'agit... mais la réalité est beaucoup plus simple.

Cela correspond à séparer certains ports d'un switch. Ils ne pourront donc plus communiquer ensemble. Plus du tout. Du tout du tout.
Prenons l'exemple suivant :

Nous avons un switch de 10 ports sur lequel sont branchées six machines.
Nous souhaitons que ces groupes de machines ne puissent pas parler entre eux. Les trois premières parlent ensemble, les trois autres aussi, mais pas d'un groupe à l'autre. Les VLANs peuvent nous aider à faire cela !

L'idée du VLAN est de couper notre switch en plusieurs morceaux. Comme si l'on avait en fait plusieurs switchs.

Dans notre cas, nous allons créer deux VLANs. Un VLAN pour les trois machines de gauche, et un autre pour les trois machines de droites.
Ainsi, nous aurons fait en sorte qu'elles ne puissent plus parler entre elles d'un groupe à l'autre.

Voici ce que cela donne :

Nous voyons ici en vert et en rouge les deux VLANs.
Ainsi, les machines connectées aux ports appartenant au VLAN vert ne peuvent communiquer qu'avec le VLAN vert. Et de même pour les machines connectées aux ports appartenant au VLAN rouge. Par contre, il est impossible pour une machine connectée au VLAN vert de communiquer avec une machine connectée au VLAN rouge. C'est comme si on avait séparé le switch en deux petits switchs, avec chacun leur propre table CAM, comme sur le schéma suivant :

Quel est l'intérêt des VLANs ?

Dans l'exemple que nous avons choisi, l'intérêt n'est pas flagrant.
Mais imaginons que nous avons à gérer une école. Avec une administration, 100 enseignants et 1000 élèves.
Nous avons alors plusieurs switchs répartis dans l'école. Des gros switchs de 256 ports ! (on appelle cela souvent des châssis.)
Il est intéressant sur ces switchs de pouvoir les segmenter pour séparer les trois populations. Pour que les élèves n'aient pas accès au réseau administratif ou à celui des enseignants, et que les enseignants n'aient pas accès au réseau administratif (pour changer leur fiche de paye par exemple Smiley

). Plutôt que d'acheter 25 petits switchs de 48 ports, on en achète 5 gros de 256 ports.

En plus de la sécurité offerte par la séparation des réseaux, cela apporte de la facilité de configuration. Si je veux qu'un port passe d'un VLAN à un autre, il me suffit de le configurer sur le switch.
Je peux faire tout cela sans bouger de mon bureau d'administrateur réseau à travers une interface web d'administration du switch :

On voit ici que chaque port peut être positionné dans un VLAN donné.
Ici le port 1 est dans le VLAN 1 alors que le port 5 est dans le VLAN 2. Les machines connectées sur ces ports ne pourront pas communiquer ensemble./

Mais c'est vraiment impossible de passer d'un VLAN à un autre ?

Non. Ce n'est pas impossible, mais presque. Déjà, rien n'est impossible en réseau. Si c'est impossible, c'est juste que personne ne l'a encore fait Smiley

D'ailleurs dans le cas des VLANs, cela a déjà été fait. Cela s'appelle du VLAN hopping.
Mais malheureusement pour nous, les failles de conception qui le permettaient ont été corrigées aujourd'hui et le VLAN hopping n'est plus d'actualité (jusqu'à ce que quelqu'un trouve une nouvelle faille...).

Le matériel utilisé pour connecter les machines entre elles en couche 2 est le switch qui sait aiguiller les trames grâce à l'adresse MAC contenue dans l'en-tête Ethernet de la trame
On peut découper le switch en plusieurs VLANs

Vous savez maintenant :

Connecter des machines ensemble
Créer un réseau local
Faire communiquer les machines ensemble sur un réseau local
Comprendre comment elles communiquent et comment sont aiguillées les informations

Mais il serait sympa maintenant de faire communiquer notre réseau avec celui du voisin ! Eh bien en route, c'est ce que nous allons maintenant voir avec la couche 3 !

AhmedOumezzine

12:02

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