Skip to content

1 4 3 4 requete sql

SQL, langage déclaratif. 4/4 : conception d'une requête SQL

Supports complémentaires.

Vous devriez à ce stade connaître et comprendre l´interprétation d´une requête SQL. Redonnons-la encore une fois sous une forme un peu différente :

  • Le résultat d´une requête est une relation constituée de nuplets.
  • Chaque nuplet du résultat est construit à partir d´un ensemble de \(n\) nuplets \(t_1, t_2, \cdots, t_n\) provenant de la base de données.
  • Ces \(n\) nuplets doivent satisfaire un ensemble de conditions (exprimé par une formule).

La construction d´une requête consiste

  • à indiquer de quels nuplets \(t_1, t_2, \cdots, t_n\) nous avons besoin, et d´où chacun provient (c´est la clause from) ;
  • à exprimer les conditions avec la clause where ;
  • à indiquer comment on construit un nuplet du résultat avec la clause select.

C´est tout. Le système pour sa part se charge de trouver toutes les combinaisons possibles des \(t_1, t_2, \cdots, t_n\), de tester les conditions, de construire le résultat. Le tout en choisissant la méthode la plus efficace.

Nous sommes maintenant en mesure de tenter de décrire le processus mental qui nous permet de construire une requête SQL pour répondre à un besoin donné. Le processus que nous décrivons s´appuie sur une vision de la structure de la base qui comprend, au minimum, la liste des tables, leurs clés primaires et les clés étrangères. On établit cette vision à partir du schéma, comme le montre par exemple la figure 7 pour trois tables de la base des films. La bonne connaissance du schéma, et sa compréhension, sont des pré-requis pour exprimer des requêtes SQL correctes.

figure 7

Fig. 7 La base des films "vue" comme un graphe dont les arêtes sont les liens clé étrangère - clé primaire.

Commençons par les requêtes conjonctives, dans lesquelles la principale difficulté est de construire les jointures.

Important
La méthode décrite ci-dessus repose sur la forme déclarative de SQL que nous avons étudiée dans ce chapitre. Le chapitre prochain présentera une approche alternative, basée sur des opérations, qui est à mon avis beaucoup moins adéquate pour apprendre à maîtriser SQL.

Conception d´une jointure

Le mécanisme de base consiste donc à se représenter les nuplets qui permettront de construire un des nuplets du résultat. Dans les cas les plus simples, un seul suffit. Pour la requête "Quelle est l´année de naissance de G. Depardieu" par exemple, on construit un nuplet du résulat à partir d´un nuplet de la table Artiste, dont l´attribut "nom" est "Depardieu", et dont l´attribut "âge" est l´information qui nous intéresse. On désigne ce nuplet par un nom, par exemple a. L´image mentale à construire est celle de la figure 8

figure 8

Fig.8 Interrogation avec un seul nuplet

C´est très élémentaire (pour l´instant) mais toute la requête SQL est déjà codée dans cette représentation.

  • Chaque nuplet désigné doit être défini dans le from.
  • Les contraintes satisfaites par ce nuplet constituent le where (nom=´Depardieu´).
  • La clause select est toujours triviale (on choisit les attributs à conserver).

Ce qui donne sur ce premier exemple :

select annéeNaissance
from Artiste as a
where a.nom='Depardieu'

Entrons dans le vif du sujet avec la requête "Titre des films avec pour acteur Depardieu". Cette fois l´image mentale à construire est celle de la figure 9 Nous avons besoin, pour construire chaque nuplet du résultat, de trois nuplets de la base : un film, un artiste, un rôle. Dès que nous avons plusieurs nuplets, il faut indiquer de quelle manière ils sont liés : ici les liens sont (comme à peu près toujours) définis par le critère d´égalité des clés primaires et clés étrangères.

figure 9

Fig 9. Les nuplets impliqués dans la recherche des films avec Depardieu

On a donné un nom à chaque nuplet, soit f, r et a. La construction de la requête s´ensuit quasiment automatiquement.

select f.titre
from Artiste as a, Rôle as r, Film as f
where a.nom='Depardieu'
and a.idArtiste = r.idActeur
and r.idFilm = f.idFilm

Notez que les contraintes sur les nuplets sont soit des égalités entre attributs, soit l´égalité entre un attribut et une constante. Quand nous ajouterons la négation, un troisième type de contrainte apparaîtra, celui de l´existence ou non d´un résultat pour une sous-requête.

Remarquez également comment on se repose sur l´interpéteur SQL pour faire l´essentiel du travail : trouver les nuplets satisfaisant les constraintes, énumérer toutes les combinaisons valides à partir de la base, et construire le résultat.

Voici un exemple un peu plus compliqué qui ne change rien au raisonnement : on veut les titres de film avec Depardieu et Deneuve. L´image à construire est celle de la figure 10. Ici il faut concevoir qu´il nous faut deux nuplets de la table Artiste, l´un avec pour nom Depardieu (a1), et l´autre avec pour nom Deneuve (a2). Ces deux nuplets sont liés à deux nuplets distincts de la table Rôle, nommons-les r1 et r2. Ces deux derniers nuplets sont liés au même film f .

figure 10

Fig. 10. Les nuplets impliqué dans la recherche des films avec Depardieu et Deneuve

À partir de la construireSQLDepardieuDeneuveFilm{.interpreted-text role="numref"}, la construction syntaxique de la requête SQL est encore une fois directe : énumération des variables-nuplets dans le from, contraintes dans le where, clause select selon les besoins.

select * 
from Artiste as a1, Artiste as a2, Rôle as r1, Rôle as r2, Film as f
where a1.nom='Depardieu'
and a2.nom='Deneuve'
and a1.idArtiste = r1.idActeur
and a2.idArtiste = r2.idActeur
and r1.idFilm = f.idFilm
and r2.idFilm = f.idFilm

Voici deux exemples complémentaires. Le premier recherche les films réalisés par Q. Tarantino en 1994. L´image mentale est celle de la construireSQLTarantinoFilm.

Recherche les films réalisés par Q. Tarantino en 1994

La requête correspondante est bien entendu celle-ci.

select * 
from Artiste as a, Film as f
where a.nom='Tarantino'
and  f.année = 1994
and a.idArtiste = f.idRéalisateur

Le second exemple recherche les films réalisés par Q. Tarantino en 1994 dans lesquels il joue lui-même dans tant qu´acteur. Je vous laisse étudier et interpréter la construireSQLTarantinoFilmArtiste et exprimer vous-même la requête SQL.

Recherche les films réalisés par Q. Tarentino en 1994 dans lequels il joue

Conception des requêtes imbriquées

Que se passe-t-il en cas de requête imbriquée, et surtout en cas de nécessité d´exprimer une négation ? Les principes précédents restent valables : on identifie les nuplets de la base qui permettent de produire un nuplet du résultat, on construit la requête comme précédemment, et la requête imbriquée n´est qu´une contrainte supplémentaire sur ces nuplets. La seule particularité des requêtes imbriquées est que la contrainte porte sur un ensemble, et pas sur une valeur atomique.

Prenons un exemple : je veux les titres de film avec Catherine Deneuve mais sans Gérard Depardieu. On commence par la solution partielle qui consiste à trouver les films avec Deneuve.

select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and a.nom='Deneuve'

Maintenant on ajoute la contrainte suivante sur le film f : dans l'ensemble des acteurs du film *f*, on ne doit pas trouver Gérard Depardieu. L´ensemble des acteurs du film f qui se nomment Depardieu est obtenu par une requête fonction de f, cette requête est ajoutée dans le where et on obtient la requête complète

select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and a.nom='Deneuve'
and not exists (select * from Rôle as r2, Artiste as a2 
                 where f.idFilm=r2.idFilm and r2.idActeur=a2.idActeur
                 and a2.nom='Depardieu')

Il faut bien être conscient que cette condition supplémentaire porte sur le film f, et que f doit impérativement intervenir dans la requête imbriquée. La requête suivante par exemple est fausse :

select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and a.nom='Deneuve'     
and not exists (select * from Rôle as r2, Artiste as a2 
                 where r2.idActeur=a2.idActeur
                 and a2.nom='Depardieu')

La requête imbriquée est ici indépendante des nuplets de la variable principale, et on peut donc évaluer son résultat dès le début : soit il existe un acteur nommé Depardieu (quel que soit le film), le not exists est toujours faux et le résultat est toujours vide ; soit il n´en existe pas, le not exists est toujours vrai et ne sert donc à rien.

La disjonction

Reste à discuter de la disjonction. Il existe une propriété assez utile des formules logiques : on peut toujours les mettre sous une forme dite "normale disjonctive", autrement dit comme la disjonction de conjonctions (voir les exercices). En pratique cela implique que toute requête comprenant un "ou" peut s´écrire comme l´union de requêtes écrites sans "ou". Cherchons les films avec Deneuve ou Depardieu.

select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and a.nom='Deneuve'
  union
select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and a.nom='Deneuve'

Ce n´est pas très concis. Il est à peu près toujours possible de trouver une formulation plus condensée avec le "or". Ici ce serait :

select f.titre
from Film as f, Rôle as r, Artiste as a
where f.idFilm=r.idFilm
and r.idActeur = a.idArtiste
and (a.nom='Deneuve' or nom='Depardieu')

Il n´existe pas de règle générale permettant de trouver la bonne formulation sans réfléchir. La bonne maîtrise des principes de logique, d´équivalence de formule et d´interprétation sont les connaissances clés.

Les principes exposés ici sont très importants. Même s´ils peuvent vous sembler parfois éloignés de vos objectifs pratiques, tout ce qui précède devrait j´espère vous convaincre que maîtriser SQL, c´est d´abord être capable d´aborder la formulation des requêtes de manière rigoureuse, pas de produire une syntaxe finalement relativement simple. À vous de jouer.