Initiation au langage SQL

I) Pour approfondir le sujet et s'entraîner
II) Instructions de base pour interroger une base de données
    1) Instruction SELECT
    2) Exemples
    3) Fonctions d'agrégation
    4) Exemples
III) Exercices corrigés
    1) Représentations musicales
    2) Notes d'étudiants
IV) Exercices non corrigés
    1) Vols
    2) Vidéoclub

  Le langage SQL (Structured Query Language) peut être considéré comme le langage d'accès standard et normalisé, destiné à interroger ou à manipuler une base de données relationnelle.
  Il permet de traduire les expressions de l'algèbre relationnelle, avec une syntaxe très simple et un jeu réduit de mots clefs.
  Il a fait l'objet de plusieurs normes ANSI/ISO dont la plus répandue aujourd'hui est la norme SQL2 qui a été définie en 1992.

I) Pour approfondir le sujet et s'entraîner

  On ne s'intéresse ici qu'aux instructions de base permettant d'interroger une base de données en rapport avec le cours sur l'algèbre relationnelle. Pour approfondir, savoir comment créer, supprimer, modifier une BDR, il est conseillé par exemple de visiter les sites suivants:

http://sql.sh/                                : formulaire SQL avec exemples
http://laurent-audibert.developpez.com/Cours-BD/      : cours complet sur BDR et SQL

  Pour écrire et tester des requêtes SQL sur une vraie base de données en ligne:
http://eric.univ-lyon2.fr/~jdarmont/tutoriel-sql/        : requêtes en ligne
http://webtic.free.fr/sql/exint/q1.htm                : requêtes en ligne

II) Instructions de base pour interroger une base de données

Les arguments entre crochets sont facultatifs.

Les exemples seront tirés des tables suivantes

  
Film
idFilm titre pays année réalisateur durée
1 Le bon,la brute et le truand Italie 1966 4 178
2 Pour quelques dollars de plus Italie 1965 4 126
3 Impitoyable USA 1991 1 131
4 Au delà USA 2010 1 129
5 Invictus USA 2003 1 134
6 Gran Torino USA 2009 1 116
  
Personne
idPersonne nom prénom
1 Eastwood Clint
2 Damon Matt
3 Van Cleef Lee
4 Leone Sergio
5 De France Cécile

Vedette
idVedette idFilm acteur
1 1 1
2 1 3
3 2 1
4 2 3
5 3 1
6 4 2
7 4 5
8 5 2
9 6 1
               
Relevé
idEleve nom filière numéro note
1 Durand TS 1 10
2 Dupond TL 1 5
3 Dupont TS 2 15
4 Pierre TL 1 10
5 Martin TS 2 20
6 Dubois TS 2 15
7 Petit TL 2 9
8 Bernard TS 1 5
9 Garcia TS 1 5
10 Dubois TS 2 14

1) Instruction SELECT

Cette instruction permet d'interroger une base de données (on dit faire une requête) et d'en extraire une table.

L'instruction de base est:     

SELECT [DISTINCT, *] attribut(s) [AS]
FROM table [JOIN table ON] [NATURAL JOIN table]
[WHERE condition(s)] [options]

l'option AS permet de renommer certains atttributs.
L'option DISTINCT permet de ne retourner que les n-uplets distincts, l'option * conserve tous les attributs
L'option WHERE permet de faire une sélection suivant une condition booléenne (on peut utiliser NOT, AND, OR pour écrire cette condition)
Les options JOIN .. ON ou NATURAL JOIN permettent de faire une jointure entre deux tables.
Les autre options peuvent être IN, NOT IN, UNION, INTERSECT pour travailler sur une réunion, intersection ou différence de tables
On peut utiliser l'option ORDER BY attribut(s) pour trier suivant la valeur des attributs.

2) Exemples

a) projection: Liste des titres et années des films

SELECT titre, année FROM Film  =
titre année
Le bon,la brute et le truand 1966
Pour quelques dollars de plus 1965
Impitoyable 1991
Au delà 2010
Invictus 2003
Gran Torino 2009
= Π titre , année ( Film )

b) projection: Liste des pays (sans doublons) où sont tournés les films

SELECT DISTINCT Pays FROM Film  =
Pays
Italie
USA
  = Π pays ( Film )

c) sélection et projection: Liste des titre des films réalisés avant 2000

SELECT titre FROM Film WHERE année < 2000 =
titre
Le bon,la brute et le truand
Pour quelques dollars de plus
Impitoyable
= titre σ année < 2000 ( Film )

d) sélection et projection: Liste des titre des films américains durant moins de deux heures

SELECT titre FROM Film WHERE durée < 120 AND pays = USA      
titre
Gran Torino
= titre σ durée < 120 et pays = USA ( Film )

e) projection avec tri: Liste des titre et durées des films classés par durée décroissante

SELECT titre, durée FROM Film ORDER BY durée DESC  =  
titre durée
Le bon,la brute et le truand 178
Invictus 134
Impitoyable 131
Au delà 129
Pour quelques dollars de plus 126
Gran Torino 116

e) jointure et projection: la liste des titres, nom et prénom du réalisateur

SELECT titre, nom, prénom
FROM Film  JOIN Personne ON Film.realisateur = Personne.idPersonne =
titre nom prénom
Le bon,la brute et le truand Leone Sergio
Pour quelques dollars de plus Leone Sergio
Impitoyable Eastwood Clint
Au delà Eastwood Clint
Invictus Eastwood Clint
Gran Torino Eastwood Clint
  = Π titre , nom , prénom ( Film [ Film . realisateur = Personne . idPersonne ] Personne )

f) jointure, sélection et projection: la liste des titres où le réalisateur est une vedette du film

SELECT titre
FROM Film NATURAL JOIN Vedette
# ou FROM Film JOIN Vedette ON Film.idFilm = Vedette.idFilm
WHERE Film.realisateur = Vedette.acteur

g) jointure double, sélection et projection: la liste des noms et prénoms des réalisateur qui sont une vedette du film

SELECT DISTINCT nom, prénom
FROM Film NATURAL JOIN Vedette
JOIN Personne ON Vedette.acteur = Personne.idPersonne
WHERE Film.realisateur = Vedette.acteur

3) Fonctions d'agrégation

a) Instructions

  Pour regrouper, dans une table T, les n-uplets dont certains des attributs A 1 , ... , A n ont la même valeur, afficher une partie A i 1 , ... , A i p d'entre eux et calculer pour chacun des sous ensembles de T obtenus par ce regroupement, des valeurs f i ( B i ) , où B 1 , ... B p sont d'autres attributs et f i une des cinq fonctions d'agrégation, on calcule en algèbre relationnelle la table T' dont les attributs sont A i 1 , ... , A i p , f 1 ( B 1 ) , ... , f p ( B p ) et qui est notée T ' = A 1 , ... , A n γ f 1 ( B 1 ) , ... , f p ( B p ) ( T )

L'équivalent SQL est: SELECT A i 1 , ... , A i p , f 1 ( B 1 ) , ... , f p ( B p ) FROM T GROUP BY A 1 , ... , A n .
Les attributs A i j doivent être parmi { A 1 , ... , A n }

GROUP BY : cette clause permet de partitionner une table et éventuellement d'appliquer une fonction d'agrégation sur les parties obtenues
COUNT(), MAX(), MIN(), SUM(), AVG() : fonctions d'agrégation
L'option HAVING () permet d'exprimer des conditions portant sur une fonction d'agrégation après une clause GROUP...BY

4) Exemples

a)  nombre total de films

SELECT COUNT(*) FROM Film

b) moyennes par classes

SELECT filière, numéro, AVG(note)
FROM Relevé
GROUP BY filière, numéro :

c)  nombre de films par réalisateur

SELECT nom, prénom, COUNT(*)
FROM Film  JOIN Personne ON Film.realisateur = Personne.idPersonne
GROUP BY nom, prénom

d)  durée moyenne des films par vedette

SELECT nom, prénom, AVG(durée)
FROM Film NATURAL JOIN Vedette
JOIN Personne ON Vedette.acteur = Personne.idPersonne
GROUP BY nom, prénom

III) Exercices corrigés

1) Représentations musicales

Soit le modèle relationnel suivant relatif à une base de données sur des représentations musicales :
Représentation (num-repr, titre, lieu)
Musicien (nom, num-repr)
Programmer (date, num-repr, tarif)
NB : les identifiants sont soulignées et les clés étrangères sont en gras

Pour commencer, que penser de ce schéma relationnel ?

a)  Donner la liste (sans doublons) des titres des représentations

SELECT DISTINCT titre
FROM Représentation ;

b) Donner la liste des titres des représentations ayant lieu à l'Opéra Bastille.

SELECT titre
FROM Représentation
WHERE lieu=Opéra Bastille ;

c) Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent

SELECT DISTINCT nom, titre
FROM Représentation NATURAL JOIN Musicien
# ou encore FROM Représentation JOIN Musicien ON Représentation.num-repr = Musicien.num-repr

ou (ce qui n'est pas recommandé)

SELECT nom, titre
FROM Musicien, Représentation
WHERE Musicien.num-repr = Représentation.num-repr ;

d) Donner la liste des dates, des titres et des tarifs des représentations

SELECT date, titre, tarif
FROM Représentation NATURAL JOIN Programmer
# ou FROM Représentation JOIN Programmer ON  Représentation.num-repr = Programmer.num-repr

e) Donner le nombre de musiciens par représentation

SELECT titre, COUNT(*) AS nombre de musiciens
FROM Représentation NATURAL JOIN Musicien
GROUP BY titre

f) Donner le tarif moyen des représentations par lieu

SELECT lieu, AVG(tarif)
FROM Représentation NATURAL JOIN Programmer
GROUP BY lieu

2) Notes d'étudiants

Soit le modèle relationnel suivant relatif à la gestion des notes pendant une année d'une promotion d'étudiants :
Etudiant(idEtudiant, nom, prénom)
Matière(CodeMat, libelléMat)
Evaluer(idEval, idEtudiant, codeMat, note)

a)  Quel est le nombre total d'étudiants ?

SELECT COUNT(*) AS nombre d'étudiants FROM Etudiant

b) Donner la liste des moyennes par matières

On fait la jointure de Matière et Evaluer, on regoupe parCodeMat et on fait la moyenne sur les Notes

SELECT LibelléMat, AVG(Note)
FROM Matière NATURAL JOIN Evaluer
# ou FROM Matière JOIN EValuer ON Matière.codeMat = Evaluer.codeMat
GROUP BY libelléMat

c) Quelles sont les moyennes de chaque étudiant dans chacune des matières ?

Il faut joindre ici les trois tables, regrouper puis calculer les moyennes et extraire les colonnes que l'on veut.

SELECT nom, prénom, libelléMat, AVG(note)
FROM Matière NATURAL JOIN Evaluer NATURAL JOIN Etudiant
GOUP BY nom, prénom, libelléMat

d) Quels sont les étudiants n'ayant pas la moyenne dans certaines matières (préciser moyenne et matière) ?

Il suffit de rajouter la clause HAVING au c).

SELECT nom, prénom, libelléMat, AVG(note)
FROM Matière NATURAL JOIN Evaluer NATURAL JOIN Etudiant
GOUP BY nom, prénom, libelléMat
HAVING AVG(note) < 10

IV) Exercices non corrigés

1) Vols

La base de données permettant de gérer des vols comporte les relations suivantes :

Ville(nomV, pays)
Liaison(numL, Départ, arrivée)  (départ et arrivée sont des nomV)
Vol(numVol, numL, numC, durée)
Compagnie(numC, nomC, nationalité)

Répondre aux questions suivantes en langage SQL . Trouvez :

1) les villes qui sont desservies au départ de Paris par la compagnie de nom "Air France".

2) les compagnies aériennes effectuant la liaison Paris-Lomé en moins de 7 heures.

3) les compagnies aériennes (numc) effectuant toutes les liaisons.

4) donner le nombre de vols par liaison toutes compagnies confondues.

5) donner le nombre de vols par liaison et par compagnie aérienne pour les compagnies de
nationalité française.

6) donner la durée moyenne d'un vol entre Paris et Lomé par compagnie aérienne.

2) Vidéoclub

La base de données du Vidéoclub comporte les relations suivantes :

Abonné( nAb, nom, prenom)
Emprunt (nEmprunt, nAb, nDvd, datedeb, datefin)
Dvd (nDvd, nFilm, dateachat, état)
Film (nFilm, titre, descriptif, annéeProduction, réalisateur)

Le domaine de valeur de l'attribut état de la relation Dvd est { « emprunté » ou « disponible »}

Répondre aux questions suivantes en utilisant le langage SQL. Trouver la:

1) Liste de toutes les dvd présents dans le magasin ( numéro de dvd et titre du film).

2) Liste des dvd qui ont été l'objet d'un emprunt compris entre le 1/05/09 et le 20/05/09 ( numéro de dvd et titre du film).

3) Liste des dvd présents dans le magasin le 15/05/09

4) Lister les films disponibles dans le magasin avec le nombre de dvd.

5) Donner le nombre d'emprunts et la durée moyenne d'emprunt par film.

© 2014 - Eric Obermeyer      Powered by