Accueil > Language de requête SQL > Découverte du SQL

Découverte du SQL

Objectifs

Question à laquelle cette activité va permettre de répondre :

Comment écrire une première requête SQL pour interroger une base de données ?

Objectifs pédagogiques visés :

Savoir écrire une requête simple en interrogation sur une seule table avec le mot-clef SELECT
Savoir écrire une requête permettant de récupérer les informations de plusieurs tables au moyen du mot-clef JOIN
Savoir écrire une requête pour regrouper des lignes et faire des calculs sur un de leurs champs au moyen du mot-clef GROUP BY

La base de données BIBLIOTHÈQUE

Les requêtes SQL à exprimer sont relatives à la base de données BIBLIOTHEQUE constituée des relations dont le schéma logique est décrit ci-dessous :

abonne (id, nom, prenom, telephone, code_postal)

livre (id, isbn, titre, auteur, annee_edition, prix)

emprunt (id_livre, id_abonne, date_emprunt) : où id_livre référence livre(id) et id_abonne référence abonne(id).

Aucun historique n’est géré dans cette base : La table ABONNE contient les informations sur les abonnés actuels de la bibliothèque, et la table LIVRE contient celles de tous les livres actuellement gérés par la bibliothèque. La table EMPRUNT contient les emprunts en cours. Lorsqu’un livre est retourné, le tuple correspondant dans la table EMPRUNT est effacé.

Schéma logique de la base BIBLIOTHEQUE (sous forme graphique)

Structure générale d’une requête

Les requêtes SQL respectent la structure générale présentée ci-dessous. Les paragraphes suivants vous guident pas à pas pour découvrir les concepts les plus importants et donnent des précisions sur la syntaxe.

SELECT colonnes
FROM table
[WHERE condition]
[GROUP BY colonnes [HAVING condition]]
[ORDER BY colonnes [ASC|DESC]];

Remarque : les expressions entre crochets [ … ] signifient que le mot-clef est optionnel !

Interrogation d’une table

SELECT [DISTINCT] colonne_1, ..., colonne_n
FROM nom_table
WHERE condition
[ORDER BY colonne_x [ASC|DESC], colonne_y [ASC|DESC]];

Dans la clause WHERE, la condition peut s’exprimer au moyen de différents opérateurs : =, !=, <=, <, >, >=. Si l’on veut comparer deux chaînes de caractères au moyen d’une expression régulière il ne faut pas utiliser l’opérateur = mais l’opérateur LIKE suivi d’une chaîne de caractères pouvant contenir des jokers :

% pour remplacer n’importe quelle suite de caractères
_ pour remplacer un seul caractère

Dans la clause ORDER, le mot-clef ASC permet de faire un tri ascendant (ordre croissant, alphabétique, alpha-numérique) et le mot-clef DESC un tri descendant. Par défaut, si l’indication de l’ordre de tri n’est pas indiquée il s’agira d’un tri ascendant.

Exemple

Affichez le nom et le prénom de tous les abonnés :
```
SELECT nom, prenom FROM abonne;
```

Affichez tous les livres écrits par Voltaire :

SELECT DISTINCT titre, auteur FROM livre WHERE auteur='Voltaire'

Le mot-clef DISTINCT permet de supprimer les doublons (les lignes identiques et multiples) dans la réponse. La table livre contient en fait chacun des exemplaires physiques des livres de la bibliothèques. En utilisant DISTINCT on ne garde qu’un seul des exemplaire qui ont le même titre et même auteur, ce que l’on va appeler ici un livre.

Affichez tous les exemplaires des livres écrits par Voltaire :
```
SELECT * FROM livre WHERE auteur='Voltaire'
```

Champ libre pour tester les requêtes précédentes :

Question 1 : Affichez le titre et l’auteur de tous les exemplaires de livre

Question 2 : Affichez le nom, le code postal et le numéro de téléphone des abonnés sur Paris (code postaux débutant par 75)

Question 3 : Affichez, sans doublon, l’isbn et l’auteur des livres selon l’ordre alphabétique des auteurs et des titres

Jointures entre plusieurs tables

La jointure est l’opération qui permet fusionner les lignes d’une table à celles d’une autre table en fonction d’une condition. Par exemple, dans notre schéma, la table emprunt permet de faire le lien entre les abonnés et les livres empruntés. Si l’on désire avoir pour chaque emprunt les informations détaillées sur les abonnés (plutôt qu’uniquement l’identifiant) il faut donc effectuer une jointure entre la table emprunt et la table abonne. Le critère de jointure à spécifier ici est que le numéro de l’abonné dans la table emprunt doit être le même que le numéro de l’abonné dans la table abonne. Souvent ces attributs sont des clés primaires et étrangères (ou référentielles), mais ce n’est pas obligatoire (à vous de faire attention au résultat obtenu !).

L’opérateur pour effectuer la jointure est le JOIN qui, implicitement, correspond à INNER JOIN pour une jointure interne. Sachez à ce niveau que pour ce type de jointure l’ordre des tables n’est pas important. Mais ce ne sera pas toujours le cas !

Cliquez ici pour afficher un exemple animé de la jointure entre `emprunt` et `abonne`

Voici la syntaxe détaillée à respecter pour effectuer une jointure :

SELECT [DISTINCT] colonne_1, ..., colonne_n

FROM nom_table_1 [[AS] alias_1] [INNER] JOIN nom_table_2 [[AS] alias_2]

ON nom_table_1.nom_colonne_x = nom_table_2.nom_colonne_y

L’expression [[AS] alias] permet de renommer une table au moyen d’un alias. Cette notation est utilisée pour faciliter la lecture d’une requête et pour éviter les confusions lorsque plusieurs tables ont des attributs se nommant de la même façon.

Exemple

Donnez le nom des abonnés ayant fait un emprunt :

SELECT DISTINCT A.nom FROM abonne A INNER JOIN emprunt E ON A.id=E.id_abonne

Affichez les dates d’emprunt des exemplaires du livre “Le Petit Prince” (attention aux majuscules dans le nom du titre, SQL est sensible à la casse dans les chaînes de caractères) :
```
SELECT E.date_emprunt FROM emprunt E INNER JOIN livre L ON E.id_livre=L.id
WHERE L.titre='Le Petit Prince'
```

Champ libre pour tester les requêtes précédentes :

Question 4 : Affichez le titre des livres empruntés par l’abonné n°2

Question 5 : Affichez la liste des emprunts sur la ville de Brest (code postal 29200)

Question 6 : Affichez le nom et le prénom des abonnés qui empruntent actuellement un exemplaire du livre “Le Petit Prince”

Calculs et regroupement

GROUP BY colonne_1, ..., colonne_n
HAVING condition

Le mot-clef GROUP BY permet de réaliser des regroupements selon un ou plusieurs attributs. Il est donc ainsi possible, au niveau du SELECT, de spécifier une fonction d’agrégation (count, min, max, sum, avg etc.) qui s’appliquera à l’ensemble des lignes regroupées.

Le mot-clef HAVING permet lui de filtrer les résultats du regroupement (c’est-à-dire le résultat de la fonction d’agrégation).

Ainsi, le mot-clef WHERE est une condition sur les valeurs présentes dans la table que vous interrogez, par opposition au mot-clef HAVING qui est une condition sur la valeur donnée par la fonction d’agrégation.

Exemple

Affichez le nombre de livres actuellement empruntés :
```
SELECT count(id_livre) FROM emprunt
```
Affichez, pour chaque abonné ayant un emprunt en cours, le nombre de livres actuellement empruntés :
```
SELECT id_abonne, count(id_livre) FROM emprunt GROUP BY id_abonne
```
Affichez, pour chaque abonné ayant plus de 2 emprunts en cours, le nombre de livres actuellement empruntés :
```
SELECT id_abonne, count(id_livre) FROM emprunt
GROUP BY id_abonne HAVING count(id_livre) > 2
```

Champ libre pour tester les requêtes précédentes :

Question 7 : Affichez le prix total de tous les livres possédés par la bibliothèque

Question 8 : Affichez le nombre d’abonnés par code postal

Question 9 : Affichez le titre des livres de “Voltaire” présents en plus de 3 exemplaires

Après avoir réalisé cette activité, ou en cas de blocage, il est conseillé de vérifier ses réponses.