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