Langage SQL - Notions avancées

Clé primaire

Reprenons l'exemple du client. Lors de la création de la table client en SQL, nous voulons spécifier que no_c est une clé primaire. Pour cela, nous ajoutons l'attribut primary key en plus de not null. Ainsi, il sera impossible d'insérer une ligne dans la table si la valeur de no_c existe déjà dans celle-ci ou si elle est null.

create table client ( no_c int not null primary key, -- Ajouter aussi not null nom text not null, prenom text not null, adresse text, telephone text, mail text ); select * from client;

Si une clé primaire est définie au moyen de deux colonnes (par exemple, le nom et le mail), nous utilisons la notation suivante:

create table client (
  nom text not null,                -- Ajouter not null, car clé primaire
  prenom text not null,
  adresse text,
  telephone text,
  mail text not null,               -- Ajouter not null, car clé primaire
  primary key (nom, mail)           -- Définit la clé primaire
);

Exercice 10

Recréer la table produit des exercices 1 et 2 en y ajoutant la clé primaire.

select * from produit;

Solution

create table produit (
    no_p int primary key not null,
    nom text not null,
    description text,
    prix int
);

insert into produit values
  (1, 'Ektorp', 'canapé 2 places', 599),
  (2, 'Brimnes', 'structure de lit', 129),
  (3, 'Jaren', 'matelas à ressorts', 59);

Exercice 11

Que se passe-t-il si on ajoute une ligne qui ne contient pas de valeur pour la clé primaire? Tester avec l'exemple ci-dessous.

insert into produit (nom, description, prix) values ('Fado', 'Lampe de table', 20);

Exercice 12

Créer et compléter la table client ci-dessous, sachant que no_c est un entier et la clé primaire, titre, prenom et nom sont des chaînes de caractères.

Solution

create table client (
  no_c int primary key not null,
  titre text,
  prenom text not null,
  nom text not null
);

insert into client values
  (1, 'M', 'Albert', 'Einstein'),    -- Evite d'écrire insert into à chaque ligne
  (2, 'Mme', 'Ada', 'Lovelace'),     -- Ne pas oublier la virgule à chaque ligne
  (3, 'M', 'Alan', 'Turing'),
  (4, 'M', 'Stephen', 'Kleene');     -- Ne pas oublier le point-virgule à la fin

Exercice 13

Créer la table achat et la compléter avec les achats effectués par les clients. La clé primaire est composée des deux colonnes no_p et no_c.

1. Alan Turing a acheté le canapé 2 places Ektrop.

2. Ada Lovelace a également acheté le canapé Ektrop.

3. Albert Einstein a acheté la structure de lit et le canapé.

4. Stephen Kleene n'a rien acheté.

select * from achat;

Solution

create table achat (
  no_c int not null,
  no_p int not null,
  primary key(no_c, no_p)
);

insert into achat values (3, 1), (2, 1), (1, 2), (1, 1);

Requête sur plusieurs tables

En SQL, il est souvent utile de fusionner toute ou une partie de deux ou plusieurs tables. Par exemple, la table de l'exercice précédent n'est pas très lisible pour un humain:

Il serait préférable que la table contienne aussi le prénom et le nom du client, ainsi que le nom du produit acheté.
Cela se fait au moyen d'une jointure. Celle-ci va créer une nouvelle table avec les informations souhaitées.

select client.no_c, client.prenom, client.nom, produit.no_p, produit.nom
  from client
  join achat on client.no_c = achat.no_c
  join produit on achat.no_p=produit.no_p

Pour joindre deux tables, il faut utiliser l'instruction join ... on ....

La requête suivante retourne le(s) nom(s) du (des) produits acheté(s) par le client n°3.

select nom from produit        -- sélectionne la colonne nom de la table produit

  join achat                   -- joint la table précédente avec la table achat
  on produit.no_p=achat.no_p   -- condition de jointure

  where no_c=3;                -- critère de sélection

Exercice 14

Écrire la requête SQL qui retourne le(s) nom(s) du (des) produits acheté(s) par le client n°1.

Solution

select nom from produit
  join achat on produit.no_p=achat.no_p
  where no_c=1;

Exercice 15

Écrire la requête SQL qui retourne le titre, le prénom et le nom des clients ayant acheté le produit Ektorp.
Trier les valeurs dans l'ordre alphabétique des prénoms.

Solution

select client.titre, client.prenom, client.nom from client
  join achat on client.no_c = achat.no_c
  where achat.no_p = 1
  order by prenom asc;

Exercice 16

Utiliser des jointures pour retourner la table ci-dessous (triée selon les prénoms):

Solution

select client.no_c, client.prenom, client.nom, produit.no_p, produit.nom, prix
  from client

  join achat on client.no_c = achat.no_c    -- joint les tables client et achat
  join produit on achat.no_p=produit.no_p   -- joint les tables achat et produit

  order by prenom asc;