TP SQL

Questions A :

1. le langage SQL est redondant, ce qui permet d'exprimer une même requête de multiples façons. Trouvez 8 expressions différentes (avec et sans sous requêtes) pour obtenir le nom des fournisseurs qui ont livrés la pièce P2
2. on veut connaître le nombre de lignes de livraisons dont le poids est supérieur à la moyenne des poids de toutes les livraisons existantes (solution avec sous requête)
3. on souhaite obtenir le nom des pièces dont le poids est strictement supérieur au poids de n'importe quelle pièce bleue (solution avec sous requête)
4. on souhaite connaître les numéros des pièces qui sont fournies par un fournisseur de Paris (solution avec sous requête)
5. on souhaite connaître les noms des fournisseurs qui ne fournissent pas la pièce P2 (solution avec exists et solution avec in)
6. on souhaite connaître les numéros des pièces fournies par un fournisseur de Londres à un projet de Londres (solution avec sous requête(s))
7. on souhaite connaître les fournisseurs qui fournissent à des projets dans une ville différente de la leur [comme tous les fournisseurs répondent à cette condition, supprimons virtuellement de la base de donnée les commandes (S4,P6,J3,300)] (solution avec sous requête(s))
8. on souhaite obtenir les numéros des projets auxquels la pièce P1 a été fournie, à condition que la quantité moyenne de toutes les pièces fournies à ce projet (pas uniquement P1) soit supérieure à la plus grande quantité livrée (QTY) d'une pièce fournie au projet J1 "par un même fournisseur" (solution avec sous requête(s))
9. on souhaite connaître les numéros des pièces qui sont d'une couleur qu'aucune autre pièce n'a (solution avec count et solution avec singular)
10. on souhaite connaître les numéros de projets pour lesquels on a fourni au total plus de 1000 pièces (les 1000 pièces ne doivent pas être les mêmes) (solution sans sous requête, solution avec sous requête et in, solution avec sous requête et exists)
11. on souhaite obtenir les noms de projets auxquels la moyenne des pièces fournies tout fournisseur confondu est supérieur à la moyenne des pièces fournies (tout fournisseur confondus) aux projets de Oslo (solution avec sous requête, solution avec sous requête qui possède elle même une sous requête)
12. idem que 11, en affichant en plus le numéro du projet (une seule version suffit)
13. quels sont les numéros de mois des dernières livraisons de la pièce la plus livrée à l'ensemble des projets (la pièce la plus livrée est P3… mais cela on ne le sait pas lorsqu'on écrit la requête) ?
14. quelles sont les pièces qui ont été livrées (la dernière fois) à la même date ? affichez le numéro de la pièce et la date

Réponses A :

1. #       select distinct sname from SPJ join S on SPJ.id_s=S.id_s where         SPJ.id_p='P2'

    #    select distinct sname from S where exists(select * from spj where                            SPJ.id_s=S.id_s and SPJ.id_p='P2')

# select distinct sname from S where S.id_s in(select SPJ.id_s from SPJ where           SPJ.id_p='P2')

# select distinct S.sname from S where (select count(*) from SPJ where                   SPJ.id_s=S.id_s and id_p='P2') >= 1

# select distinct sname from S where S.id_s = any(select SPJ.id_s from SPJ                             where SPJ.id_p=’P2’)

# select distinct sname from S where ‘P2’ in(select SPJ.id_p from SPJ where              SPJ.id_s=S.id_s)

# select distinct sname from S, SPJ where SPJ.id_s=S.id_s and SPJ.id_p=’P2’

# select distinct sname from S where ‘P2’ = any(select SPJ.id_p from SPJ where        SPJ.id_s=S.id_s)

2. #       select count(*) from SPJ join P on SPJ.id_p=P.id_p where qty*weight > (select    avg(qty*weight) from SPJ join P on SPJ.id_p=P.id_p)

3. #       select pname from P where weight > all(select weight from p where color = 'Blue')

4. # select distinct id_p from SPJ where id_s in(select id_s from S where city = 'Paris')

    # select distinct id_p from SPJ where (select city from S where SPJ.id_s=S.id_s)                    = 'Paris'

5. # select sname from S where id_s not in(select id_s from SPJ where id_p='P2')

    # select sname from S where not exists(select * from SPJ where S.id_s=SPJ.id_s                  and id_p='P2')

6. #       select id_p from SPJ where (select city from S where S.id_s=SPJ.id_s) = 'London' and (select city from J where J.id_j=SPJ.id_j) = 'London'

7. # select distinct id_s from SPJ where (select city from S where S.id_s=SPJ.id_s)     <> (select city from J where J.id_j=SPJ.id_j)

    # select id_s from S where exists(select * from SPJ join J on SPJ.id_j=J.id_j                         where S.city <> J.city and SPJ.id_s=S.id_s)

8. # select distincte SPJA.id_j from SPJ SPJA where SPJA.id_p = 'P1' and (select        avg(SPJB.qty) from SPJ SPJB where SPJA.id_j=SPJB.id_j) > (select   max(SPJC.qty) from SPJ SPJC where SPJC.id_j = 'J1')

9. #       select id_p from SPJ where (select city from S where S.id_s=SPJ.id_s) =      'London' and (select city from J where J.id_j=SPJ.id_j) = 'London'

10. # select id_j from SPJ group by id_j having sum(qty) > 1000

    # select distinct id_j from J where id_j in(select id_j from SPJ group by id_j                     having sum(qty) > 1000)

# select distinct id_j from J where exists(select id_j from SPJ where                         SPJ.id_j=J.id_j group by id_j having sum(qty) > 1000)

11. # select jname from SPJ join J on J.id_j=SPJ.id_j group by jname having avg(qty) > (select avg(qty) from SPJ join J on J.id_j=SPJ.id_j where city = 'Oslo')

    # select jname from SPJ join J on J.id_j=SPJ.id_j group by jname having avg(qty)                  > (select avg(qty) from SPJ where id_j in(select id_j from J where city =                           'Oslo'))

12. # select id_j,jname from SPJ join J on J.id_j=SPJ.id_j group by id_j,jname having   avg(qty) > (select avg(qty) from SPJ join J on J.id_j=SPJ.id_j where city =         'Oslo')

13. # select distinct extract(month from date_derniere_livraison) from SPJ where        id_p = (select id_p from SPJ group by id_p having sum(qty) >= all(select   sum(qty) from SPJ group by id_p))

14. # select id_p,date_derniere_livraison from SPJ SPJ1 where (select count(*) from    SPJ SPJ2 where SPJ1.date_derniere_livraison=SPJ2.date_derniere_livraison)     >= 2

Questions B :

Ecrire, pour chacun des exercices proposés, une requête sans sous requête, avec sous requête utilisant le quantificateur EXISTS, l'opérateur ensembliste IN (2x) et liée naturellement.

1. on souhaite connaître les noms des pièces qui sont fournies, lors d'une livraison, en 500 unités                   
2. on souhaite connaître les numéros de pièces fournies par un fournisseur de Paris
3. on souhaite connaître les noms des pièces fournies par le fournisseur S2
4. on souhaite connaître les noms des fournisseurs de la pièce P2
5. on souhaite connaître les noms des fournisseurs qui ne fournissent pas la pièce P2
6. on souhaite connaître les noms des fournisseurs d'au moins une pièce de couleur rouge
7. on souhaite connaître les numéros des fournisseurs et les noms des fournisseurs qui fournissent des pièces au projet Console

Réponses B :

1. # select distinct pname from spj join p on p.id_p=spj.id_p where qty = 500

# select distinct pname from P where exists(select * from spj where spj.id_p =               p.id_p and qty = 500)

# select distinct pname from P where id_p in(select id_p from spj where qty =                500)

# select distinct pname from P where 500 in(select qty from spy where spj.id_p=p.id_p)

# select distinct pname from P where (select count(*) from spj where                     spj.id_p=p.id_p and qty=500) >= 1

2. # select distinct id_p from spj join s on s.id_s=spj.id_s where city='Paris'

# select distinct id_p from spj where exists(select * from s where s.id_s=spj.id_s            and city='Paris')

# select distinct id_p from spj where id_s in(select id_s from s where city='Paris')

# select distinctid_p from spj where 'Paris' in(select city from s where spj.id_s = s.id_s)

# select distinct id_p from spj where (select count(*) from s where                         s.id_s=spj.id_s and city = 'Paris') >= 1

|| select distinct id_p from spj where (select city from s where pj.id_s = s.id_s) = 'Paris'

3. # select distinct pname from spj join p on p.id_p=spj.id_p where id_s = 'S2'

# select distinct pname from p where exists(select * from spj where                       p.id_p=spj.id_p and id_s='S2')

# select distinct pname from p where id_p in(select id_p from spj where                         id_s='S2')

# select distinct pname from p where 'S2' in(select id_s from spj where spj.id_p = p.id_p)

# select distinct pname from p where (select count(*) from spj where                     spj.id_p=p.id_p and id_s='S2') >= 1

4. # select distinct sname from spj join s on s.id_s=spj.id_s where id_p='P2'

# select distinct sname from S where exists(select * from SPJ where                                s.id_s=spj.id_s and id_p='P2')

# select distinct sname from S where id_s in(select id_s from SPJ where                         id_p='P2')

# select distinct sname from s where 'P2' in(select id_p from spj where spj.id_s = s.id_s)

# select distinct sname from S where (select count(*) from SPJ where                     s.id_s=spj.id_s and id_p='P2') >= 1

5. # (besoin de la soustraction)

# select distinct sname from S where NOT exists(select * from SPJ where                s.id_s=spj.id_s and id_p='P2')

# select distinct sname from S where id_s NOT in(select id_s from SPJ where                  id_p='P2')

# select distinct sname from s where 'P2' NOT in(select id_p from spj where spj.id_s = s.id_s)

# select distinct sname from S where (select count(*) from SPJ where                     s.id_s=spj.id_s and id_p='P2') = 0

6. # select distinct sname from spj join s on s.id_S=spj.id_s join p on    p.id_p=spj.id_p where color='Red'

# select distinct sname from s where exists(select * from spj join p on                    p.id_p=spj.id_p where color = 'Red' and spj.id_s=s.id_s)

# select distinct sname from s where id_s in(select id_s from spj join p on                p.id_p=spj.id_p where color = 'Red')

# select sname from s where (select count(*) from spj join p on p.id_p=spj.id_p             where color = 'Red' and s.id_s=spj.id_s) >= 1

7. # select distinct id_s,sname from spj join s on s.id_s=spj.id_s join j on        j.id_j=spj.id_j where jname = 'Console'

# select distinct id_s, sname from s where exists(select * from spj join j on                       j.id_j=spj.id_j where jname = 'Console' and s.id_s=spj.id_s)

# select id_s,sname from s where id_s in(select id_s from spj join j on                    j.id_j=spj.id_j where jname = 'Console')

# select id_s,sname from s where (select count(*) from spj join j on                                j.id_j=spj.id_j where jname = 'Console' and s.id_s=spj.id_s) >= 1

• Tweet
• Share
• Share
• Share
• Share

Related Post