Saurez-vous construire une requête évoluée ? [Résolu]

[sputnick]

Bonjour,

on considère le schema suivant :

VOILIER (Numerovoilier, TypeVoilier, PrixLocation, EtatVoilier, #Base)
CLIENT (NumeroClient NomClient, AdresseClient)
RESERVATION (NuméroVoilier, NumeroClient, PremierJourLocation, JourRetour)
SUCCURSALE (Base, Directeur)

Comment exprimer les 2 requetes suivantes ?

- quelles sont les bases qui ne louent que des voiliers dont l'état est < 2 ?
- quels sont les directeurs de base ayant loué, comme clients, un voilier dans une autre base que celle qu'ils dirigent ?

Le mieux serait en algèbre relationnel, mais comme c'est surement difficile à transcrire ici, l'idée de base suffira.

Je vois bien des pistes avec des SUMs et des comparaisons, mais peut-être je rate quelque chose ?!

++sputnick;

[punkoff]

bonjour,

lisez ceci : http://sqlpro.developpez.com/cours/sqlaz/sousrequetes/

En particulier le chapitre sur le prédicat EXISTS (et not exists pour votre 1ere requête)

[al1_24]

Présente-nous tes requêtes... Nous pourrons alors t'aider à les corriger.

[sputnick]

Je dirais pour la première question

Code :

SELECT Base FROM SUCCURSALE WHERE EXISTS (SELECT EtatVoilier FROM VOILIER WHERE EtatVoilier < 2);

Mais ça me sort toutes les bases au lieu de juste celles avec un EtatVoilier < 2

[sputnick]

Ok, j'ai trouvé :

Code :

SELECT Base FROM VOILIER WHERE EXISTS (SELECT Base FROM SUCCURSALE) AND EtatVoilier < 2;

[dehorter olivier]

bonjour

mais peut etre une jonction serait-elle plus appropriee ?

Code :

1
2
3
SELECT Base
  FROM Succursale
    INNER JOIN Voilier ON (Voilier.Base = Succursale.Base AND Voilier.Etatvoilier < 2)

et pour

- quels sont les directeurs de base ayant loué, comme clients, un voilier dans une autre base que celle qu'ils dirigent ?

une petite requete avec EXISTS

[dehorter olivier]

et pour la seconde,

je verais bien un truc comme cela (mais sans certitude )

Code :

1
2
3
4
5
6
7
8
9
10
11
      SELECT Directeur
            FROM Succursale AS S
              INNER JOIN Voilier ON (Voilier.Base = S.Base)
              INNER JOIN Reservation ON (Reservation.Numerovoilier = Voilier.Numerovoilier)
              INNER JOIN Client C ON (C.Numeroclient = Reservation.Numeroclient)
          WHERE EXISTS(SELECT 1
                         FROM Succursale
                           INNER JOIN Voilier ON (Voilier.Base = Succursale.Base)
                           INNER JOIN Reservation ON (Reservation.Numerovoilier = Voilier.Numerovoilier)
                           INNER JOIN Client ON (Client.Numeroclient = Reservation.Numeroclient AND Client.Numeroclient = C.Numeroclient)
                       WHERE Succursale.Base <> S.Base)

[sputnick]

Envoyé par dehorter olivier

et pour la seconde,

je verais bien un truc comme cela (mais sans certitude )

Cela ne passe pas sous Oracle 11.1.0.6.0 :

Code :

1
2
3
4
5
 
            FROM Succursale AS S
                            *
ERROR at line 2:
ORA-00933: SQL command NOT properly ended

[dehorter olivier]

et en elevant le "AS"

[sputnick]

Code :

1
2
3
4
              INNER JOIN Reservation ON (Reservation.Numerovoilier = Voilier.Numerovoilier)
                                         *
ERROR at line 4:
ORA-00904: "RESERVATION"."NUMEROVOILIER": invalid identifier

[punkoff]

Envoyé par sputnick

Ok, j'ai trouvé :

Code :

SELECT Base FROM VOILIER WHERE EXISTS (SELECT Base FROM SUCCURSALE) AND EtatVoilier < 2;

cette requete est fausse par rapport au besoin.
Vous devez vérifiez qu'une base n'a pas de voilier avec un état supérieur ou égale à 2 tout en ayant des voilier avec un état inférieur a 2.

Vous aurez donc besoin de 2 sous-requete en utilisant EXISTS et NOT EXISTS

[sputnick]

Youhou, pour la première j'ai trouvé un truc simple et propre :

Code :

1
2
3
4
5
 
SELECT SUCCURSALE.Base
FROM SUCCURSALE, VOILIER
WHERE VOILIER.Base = SUCCURSALE.Base
AND VOILIER.ETATVOILIER < 2;

[punkoff]

Encore faux.
Faites vous un jeu de test !

[sputnick]

Envoyé par punkoff

cette requete est fausse par rapport au besoin.
Vous devez vérifiez qu'une base n'a pas de voilier avec un état supérieur ou égale à 2 tout en ayant des voilier avec un état inférieur a 2.

Vous aurez donc besoin de 2 sous-requete en utilisant EXISTS et NOT EXISTS

Oui, merci je m'en suis rendu compte, ce pourquoi on trouves une proposition plus heureuse au dessus.

[dehorter olivier]

re bonjour

INNER JOIN Reservation ON (Reservation.Numerovoilier = Voilier.Numerovoilier)
*
ERROR at line 4:
ORA-00904: "RESERVATION"."NUMEROVOILIER": invalid identifier

STP, en regardant juste un peu ce que j'avais ecrit, on trouve la faute en 30sec

punkoff a ecrit :

Vous devez vérifiez qu'une base n'a pas de voilier avec un état supérieur ou égale à 2 tout en ayant des voilier avec un état inférieur a 2.

oups oui

- quelles sont les bases qui ne louent que des voiliers dont l'état est < 2 ?

le fondamental est lien est bien le QUE

[CinePhil]

Pour écrire une requête qui semble complexe, il faut décomposer le problème.

VOILIER (Numerovoilier, TypeVoilier, PrixLocation, EtatVoilier, #Base)
CLIENT (NumeroClient NomClient, AdresseClient)
RESERVATION (NuméroVoilier, NumeroClient, PremierJourLocation, JourRetour)
SUCCURSALE (Base, Directeur)

- quelles sont les bases qui ne louent que des voiliers dont l'état est < 2 ?

Les bases qui louent des voiliers dont l'état est inférieur à 2 :

Code :

1
2
3
SELECT DISTINCT v.Base
FROM VOILIER v
WHERE v.EtatVoilier < 2

Parmi celles-ci, les bases qui ne louent pas de voiliers dont l'état est >= 2 (ou plus simplement > 1) :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT DISTINCT v.Base
FROM VOILIER v
WHERE v.EtatVoilier < 2
    AND NOT EXISTS 
    (
        SELECT *
        FROM VOILIER v2
        WHERE v2.Base = v.Base
            AND v2.EtatVoilier > 1
    )

Traduction de la requête :
Sélectionner les bases de la table VOILIER où il y a des voiliers dont l'état est inférieur à 2 et pour lesquelles il n'existe pas de voiliers de la même base dont l'état est supérieur à 1.

quels sont les directeurs de base ayant loué, comme clients, un voilier dans une autre base que celle qu'ils dirigent ?

Quelques jointures et le tour est joué :

Code :

1
2
3
4
5
6
SELECT DISTINCT s.Directeur
FROM SUCCURSALE s 
INNER JOIN CLIENT c ON c.NomClient = s.Directeur
    INNER JOIN RESERVATION r ON r.NumeroClient = c.NumeroClient
        INNER JOIN VOILIER v ON v.NumeroVoilier = r.NumeroVoilier
WHERE s.Base <> v.Base

[Waldar]

Envoyé par CinePhil

Sélectionner les bases de la table VOILIER où il y a des voiliers dont l'état est inférieur à 2 et pour lesquelles il n'existe pas de voiliers de la même base dont l'état est supérieur à 1.

Ou alors une autre traduction : "sélectionner les bases de la table voilier dont le plus grand état est strictement inférieur à deux."
Requête :

Code sql :

1
2
3
4
  SELECT v.Base
    FROM Voilier v
GROUP BY v.Base
  HAVING max(v.EtatVoilier) < 2;

[CinePhil]

Bravo Waldar ! Tu as trouvé la meilleure requête je pense !

[sputnick]

Merci beaucoup, on voit les gars qui jouent avec de la requete de façon quotidienne, c'est beau à voir !

Voici ce que j'ai pondu en Latex et algèbre relationnel pour la question quelles sont les bases qui ne louent que des voiliers dont l'état est < 2 ?

<AUTO_CENSURE>faux</AUTO_CENSURE>

Et pour la question Quels sont les directeurs de base ayant loué, comme clients, un voilier dans une autre base que celle qu'ils dirigent :



Edit et PS : Spécial BIG UP à CinePhil pour avoir expliqué en détail sa démarche, au final c'est presque plus intéressant que la concision de Waldar