TP SQL - Vendeur de logiciels.


1
Présentation des tables
et de leur contenu.
Client A Acheté Produit Catégorie
NomPrénomVille-NumCNumP-NomCatégorie-CodeLibellé
1SPERRYBrettNew York111Office 95BUBUBureautique
2MIELEDonnySan Fransisco182Office 97BUSYSystème d'exploitation
3WETHERILLSteveDetroit1123Office 2000BUDEOutil de développement
4WANGEricLos Angeles234Word 2000BUINOutil Internet
5BOSTICJoeNew York275Excel 2000BUBABase de données
6TALLSteveNew York396Unix Red HatSY
7AMBROSEBretSan Fransisco4107Windows NT 4 wksSY
8KETTLERNealDetroit4118Paradox 5BA
9LONGDenzilNew York4129Sybase 4BA
10LANIERJonathanDetroit5610WinDev 5DE
11ISGREENAdamSan Fransisco8111Visual Basic 6DE
12YEOEricNew York9212Borland C++DE
13ARCHERJohnDetroit10413MemoWeb 5IN
14LIGHTNERMichaelLos Angeles12514MemoWeb 98IN
15PANNULLOPatrickNew York12715GetRight 3IN
16CONNELYPatrickSan Fransisco137
147

2
Requêtes à rédiger en SQL
et réponses renvoyées par ces requêtes.

Rédigez, en SQL, les requêtes suivantes.

Rappels sur... Les requêtes.

  • Lorsque le nom d'un champ ou d'une table comporte des caractères spéciaux, vous devez l'entourer de crochet. Par exemple : SELECT [N°] FROM Client ;

  • Posez-vous toujours la question : "est-ce que la requête que je suis en train de taper est susceptible de renvoyer plusieurs fois la même réponse", et déduisez-en si la clause DISTINCT est nécessaire. Par exemple, pour la requête 1, elle sera nécessaire, mais pas pour la 2.

  • Les clauses SQL, les tables et les champs peuvent s'écrire indifféremment en minuscule ou en majuscule (du moins sous Access).

  • Vous pouvez trouver des rappels et des explications sur la commande SELECT ici : http://sql.developpez.com/sqlaz/select/, ou utilisez le Memento SQL.
  1. SELECT : Dans quelle ville trouve-t-on des clients ?
    Ville
    New York
    San Fransisco
    Detroit
    Los Angeles

  2. SELECT, WHERE : Quels sont les clients qui habitent New-York ?
    NomPrénom
    1SPERRYBrett
    5BOSTICJoe
    6TALLSteve
    9LONGDenzil
    12YEOEric
    15PANNULLOPatrick

  3. SELECT, WHERE : Quels sont les différents produits de catégorie "SY" et "BA" vendus par la société ?
    NomCatégorie
    6Unix Red HatSY
    7Windows NT 4 wksSY
    8Paradox 5BA
    9Sybase 4BA

  4. Jointure, WHERE : Quels sont les clients qui ont acheté des produits de bureautique ?
    NomPrénomVille
    1SPERRYBrettNew York
    2MIELEDonnySan Fransisco
    8KETTLERNealDetroit
    9LONGDenzilNew York
    10LANIERJonathanDetroit
    12YEOEricNew York

  5. Jointure, WHERE : Quels sont les produits achetés par le client "YEO Eric" ?
    NomCatégorie
    5Excel 2000BU
    7Windows NT 4 wksSY

  6. LIKE : Quels sont les clients dont le nom commence par "W" ?
    NomPrénomVille
    3WETHERILLSteveDetroit
    4WANGEricLos Angeles

  7. LIKE : Quels sont les différentes versions d'Office disponibles ?
    NomCatégorie
    1Office 95BU
    2Office 97BU
    3Office 2000BU

  8. NOT IN avec les tables Client et [A Acheté] : Quels sont les clients qui n'ont jamais acheté de produit ?
    NomPrénomVille
    6TALLSteveNew York
    7AMBROSEBretSan Fransisco
    11ISGREENAdamSan Fransisco
    15PANNULLOPatrickNew York
    16CONNELYPatrickSan Fransisco

  9. NOT IN avec les tables Produit et [A Acheté] : Quels sont les produits qui n'ont jamais été vendus ?
    NomCatégorie
    13MemoWeb 5IN
    14MemoWeb 98IN
    15GetRight 3IN

  10. NOT IN avec les tables Produit et [A Acheté], Catégorie : Quelles sont les catégories qui n'ont jamais été vendues ?
    CodeLibellé
    INOutil Internet

  11. NOT IN avec toutes les tables : Quels sont les clients qui n'ont jamais acheté de produits de bureautique ?
    NomPrénomVille
    3WETHERILLSteveDetroit
    4WANGEricLos Angeles
    5BOSTICJoeNew York
    6TALLSteveNew York
    7AMBROSEBretSan Fransisco
    11ISGREENAdamSan Fransisco
    13ARCHERJohnDetroit
    14LIGHTNERMichaelLos Angeles
    15PANNULLOPatrickNew York
    16CONNELYPatrickSan Fransisco

  12. Autojointure (avec deux fois la table Client) : Quels sont les clients qui habitent dans la même ville que Steve TALL ?
    NomPrénomVille
    1SPERRYBrettNew York
    5BOSTICJoeNew York
    9LONGDenzilNew York
    12YEOEricNew York
    15PANNULLOPatrickNew York

  13. Autojointure (plus difficile, avec les tables Produit, [A Acheté], Produit) : on suppose que le magasin a un site Web. Pour conseiller ses clients, il veut faire la requête suivante : produits achetés par les clients qui ont acheté le produit consulté. Le numéro du "produit consulté" sera ici saisi à chaque exécution de la requête, grâce à une clause WHERE [N°] = [Saisissez le n° du produit consulté]. Par exemple, quand le produit consulté est "Office 95" (n°1), la base conseillera les produits suivants :
    NomCatégorie
    8Paradox 5BA
    12Borland C++DE

  14. Autojointure (difficile) : Le magasin, pour conseiller ses clients, veut faire la requête suivante : produits achetés par les clients qui ont achetés les mêmes produits que le client connecté. Ainsi, lorsque SPERRY Brett se connecte, elle pourra lui conseiller les logiciels suivants :
    NomCatégorie
    10WinDev 5DE
    11Visual Basic 6DE

Sébastien PASTORE.