COURS 3

1
COURS 3

• Introduction à SQL

2
Motivation Améliorer la lisibilité des requêtes

• SQL (Structured Query Language)
• Langage de Manipulation de Bases de Données
  développé par IBM (San José, 1981)
• Interrogation de bases de données Manipulation
  de bases de données dans des programmes.
• q Un standard industriel
• NB. Il existe des dialectes  constructeurs 
• e.g., le dialecte Access

3
Plan de la présentation

• 1) Interrogation de la base de données
• 2) Mise à jour de la base de données
• 3) Gestion des tables
• 4) Un exemple de construction de requête

4
Modèles représentatifs dune BD
Modèle Conceptuel de Données Modèle Logique de
données Schéma
Relationnel EQUIPES (NEquipe, NomEquipe, Ville,
Entraineur) JOUEURS (NJoueur, NEquipe,
NomJoueur, PrénomJoueur) MATCHS (NMatch,
NLocaux, NVisiteurs, Date, NbSpectateurs,
NbPtsLocaux, NbPtsVisiteurs) A_JOUE (NJoueur,
NMatch, NbPoints Marqués, NbFautes)
5
Une extension

• Table EQUIPES
• Table JOUEURS
• Table MATCHS

6
Une extension

• Table A_JOUE

7
-1- Linterrogation

• Forme générale
• SELECT liste-de-noms-de-colonnes
• FROM liste-de-tables
• SELECT
• FROM JOUEURS
• Algèbre relationnelle
• JOUEURS

8
-1.1- La projection

• La forme générale réalise une PROJECTION sans
  élimination des doubles (sélection de colonnes
  d'une table)
• SELECT PrénomJoueur
• FROM JOUEURS
• Algèbre relationnelle
• pPrénomJoueur (JOUEURS)
• Projection avec élimination des doubles
• SELECT DISTINCT liste-de-noms-de-colonnes
• FROM liste-de-tables
• SELECT DISTINCT JOUEURS.PrénomJoueur
• FROM JOUEURS

9
-1.2- La sélection

• SELECT liste-de-noms-de-colonnes
• FROM liste-de-tables
• WHERE qualification
• Une sélection est une restriction selon la
  qualification suivie d'une projection pour ne
  garder que les colonnes désignées.
• Liste des joueurs de l'équipe 1
• SELECT NomJoueur, NEquipe
• FROM JOUEURS
• WHERE NEquipe 1
• Algèbre relationnelle
• p NomJoueur, NEquipe (s NEquipe 1 (JOUEURS))

10
-1.2.1- Présentation du résultat

• Tri des lignes de la table résultat.
• ORDER BY nom-de-colonne ordre , nom-de-colonne
  ordre ...
• ordre ASC ou DESC ascendant ou descendant
• SELECT NomJoueur, NEquipe
• FROM JOUEURS
• WHERE NEquipe 1
• ORDER BY NomJoueur ASC

11
-1.2.1- Qualification composée

• La qualification peut être composée de plusieurs
  conditions combinées à laide des opérateurs
  booléens AND (et), OR (ou) et NOT (négation).
• Les opérateurs de comparaisons sont , ltgt ou !,
  lt, lt, gt et enfin gt.
• Liste des matchs ayant eu lieu chez l'équipe 1
  avec plus de 6000 spectateurs
• SELECT NMatch, NLocaux, NVisiteurs, Date,
  NbSpectateurs
• FROM MATCHS
• WHERE NLocaux 1 AND NbSpectateurs gt 6000
• Algèbre relationnelle 
• pNMatch, NLocaux, NVisiteurs, Date,
  NbSpectateurs
• (sNLocaux 1 AND NbSpectateurs gt 6000
  (MATCHS))

12
-1.3- Le produit cartésien

• Produit cartésien entre EQUIPES et MATCHS avec
  une projection pour ne pas avoir toutes les
  colonnes des deux tables 
• SELECT EQUIPES.NEquipe, EQUIPES.NomEquipe,
• MATCHS.NMatch , MATCHS.NLocaux,
• MATCHS.Date, MATCHS.NbSpectateur,
• FROM EQUIPES, MATCHS
• pEQUIPES.NEquipe,EQUIPES.NomEquipe,MATCHS.NMatch
  ,MATCHS.NLocaux,MATCHS.Date, MATCHS.NbSpectateur(
  EQUIPESÄMATCHS )

13
-1.4- La jointure

• La jointure est le produit cartésien de deux
  relations avec une sélection.
• Liste des matchs où les équipes reçoivent
• SELECT EQUIPES.NEquipe, EQUIPES.NomEquipe,
• MATCHS.NMatch , MATCHS.NLocaux,
• MATCHS.Date, MATCHS.NbSpectateur,
• FROM EQUIPES, MATCHS
• WHERE EQUIPES.NEquipe MATCHS.NLocaux
• p EQUIPES.NEquipe,EQUIPES.NomEquipe,
  MATCHS.NMatch
• MATCHS.NLocaux, MATCHS.Date, MATCHS.NbSpectateur
  s
• (EQUIPES EQUIPES.NEquipeMATCHS.NLocaux
  MATCHS)

14
-1.4.1- Jointure de 3 tables

• Liste des points marqués par les joueurs à
  domicile contre l'équipe 3.
• SELECT JOUEURS.NomJoueur, MATCHS.NMatch,
• MATCHS.Date, A_JOUE.NbPointsMarqués,
• A_JOUE.NbFautes, MATCHS.NVisiteur
• FROM MATCHS ,JOUEURS, A_JOUE
• WHERE JOUEURS.NJoueur A_JOUE.NJoueur
• and MATCHS.NMatch A_JOUE.NMatch
• and MATCHS.NVisiteur3
• p JOUEURS.NomJoueur, MATCHS.NMatch, MATCHS.Date,
  
• A_JOUE.NbPointsMarqués, A_JOUE.NbFautes,
  MATCHS.NVisiteur
• (sJOUEURS.NJoueur A_JOUE.NJoueur
• and MATCHS.NMatch A_JOUE.NMatch
• and MATCHS.NVisiteur3
• (MATCHS Ä JOUEURS Ä A_JOUE) )

15
-1.4.2- Imbrication de requêtes

• par utilisation d'opérateurs ensemblistes
• IN appartenance à un ensemble
• pour Access, lensemble peut aussi être une
  base
• de données externe, dBase ou Paradox par
  exemple
• NOT IN non appartenance
• Même requête que la précédente mais avec
  projection sur
• NomJoueur seulement.
• SELECT JOUEURS.NomJoueur
• FROM JOUEURS
• WHERE JOUEURS.NJoueur IN
• (SELECT A_JOUE.NJoueur
• FROM MATCHS, A_JOUE
• WHERE MATCHS.NMatch A_JOUE NMatch
• and MATCHS.NVisiteur3 )

16
-1.4.2- Imbrication de requêtes

• Remarque pour Access, IN est aussi un
  opérateur qui détermine si la valeur dune
  expression est égale à une ou plusieurs valeurs
  dune liste.
• Algèbre relationnelle 
• p JOUEURS.NomJoueur
• (sJOUEURS.NJoueur A_JOUE.NJoueur
• and MATCHS.NMatch A_JOUE.NMatch
• and MATCHS.NVisiteur3
• (MATCHS Ä JOUEURS Ä A_JOUE) )
• ANY Un ou plusieurs éléments de l'ensemble
  satisfont
• la condition.
• ALL Tous les éléments de l'ensemble satisfont
• la condition.

17
-1.4.2- Imbrication de requêtes

• Liste des matchs où le tiers des points marqués
  par les locaux est inférieur à au moins un nombre
  de points marqués par un joueur
• SELECT MATCHS.NMatch, MATCHS.NLocaux,
• MATCHS.NbPtsLocaux , A_JOUE.NJoueur,
• A_JOUE.NbPointsMarqués
• FROM MATCHS, A_JOUE
• WHERE MATCHS.NMatch A_JOUE.NMatch
• and MATCHS.NbPtsLocaux / 3 lt ANY
• (SELECT NbPointsMarqués
• FROM A_JOUE)

18
-1.5- Autres possibilités d interrogation

• UNION sélection de lignes appartenant à l'une
  ou à l'autre
• de 2 relations (avec suppression des doublons).
• Liste des noms des entraineurs et des joueurs
• SELECT JOUEURS.NomJoueur
• FROM JOUEURS
• UNION
• SELECT EQUIPES.Entraineur
• FROM EQUIPE
• Dautres opérateurs SQL existent ...
• INTERSECT sélection de lignes appartenant aux 2
  relations.
• MINUS sélection de lignes d'une relation
  n'appartenant pas
• à l'autre relation.

19
-1.6- Opérateurs dagrégats

• AVG Moyenne d'un ensemble de valeurs
• SUM Somme d'un ensemble de valeurs
• COUNT Nombre de valeurs d'un ensemble
• MAX Valeur Maximum d'un ensemble de valeurs
• MIN Valeur Minimum d'un ensemble de valeurs
• Moyenne de spectateurs par match
• SELECT AVG(MATCHS.NbSpectateurs)
• FROM MATCHS
• ou
• SELECT SUM(MATCHS.NbSpectateurs) / COUNT()
• FROM MATCHS

20
-1.6- Opérateurs dagrégats

• Maximum de spectateurs dans un match
• SELECT MAX(MATCHS.NbSpectateurs)
• FROM MATCHS
• Nombre total de joueurs
• SELECT COUNT()
• FROM JOUEURS

21
-1.7- Partition de relations

• GROUP BY application des opérateurs d'agrégat
  sur des
• sous-relations obtenues.
• Liste des moyennes de points marqués par les
  joueurs et leur moyenne de fautes
• SELECT A_JOUE.NJoueur,
• AVG(A_JOUE.NbPointsMarqués),
• AVG(A_JOUE.NbFautes)
• FROM A_JOUE
• GROUP BY A_JOUE.NJoueur

22
-1.7- Partition de relations

• HAVING possibilité d'appliquer des conditions
  sur
• les sous-relations.
• Liste des moyennes de points marqués par les
  joueurs et leur moyenne de fautes pour ceux dont
  la moyenne est gt à 2.5
• SELECT A_JOUE.NJoueur,
• AVG(A_JOUE.NbPointsMarqués),
• AVG(A_JOUE.NbFautes)
• FROM A_JOUE
• GROUP BY A_JOUE.NJoueur
• HAVING AVG(A_JOUE.NbFautes) gt 2.5

23
-1.7- Partition de relations

• WHERE et GROUP BY
• Liste des moyennes de points marqués par les
  joueurs et leur moyenne de fautes, pour les
  matchs 2 et 3
• SELECT A_JOUE.NJoueur, AVG(A_JOUE.NbPointsMarqué
  s),
• AVG(A_JOUE.NbFautes)
• FROM A_JOUE
• WHERE A_JOUE.NMatch2 OR A_JOUE.NMatch3
• GROUP BY A_JOUE.NJoueur

24
-1.7- Partition de relations

• WHERE, GROUP BY et HAVING
• Sélection selon la condition du WHERE, puis
  réalisation du GROUP BY et enfin sélection selon
  la condition du HAVING.
• Pour les matchs 2 et 3, donner la liste des
  moyennes de points marqués par les joueurs et
  leur moyenne de fautes pour ceux dont la moyenne
  est gt à 2.5
• SELECT A_JOUE.NJoueur, AVG(A_JOUE.NbPointsMarqué
  s),
• AVG(A_JOUE.NbFautes)
• FROM A_JOUE
• WHERE A_JOUE.NMatch2 OR A_JOUE.NMatch3
• GROUP BY A_JOUE.NJoueur
• HAVING AVG(A_JOUE.NbFautes) gt 2.5

25
-1.8- Interrogation avec ACCESS

• Liste des points marqués par les joueurs à
  domicile contre l'équipe 3.
• SELECT JOUEURS.NomJoueur, MATCHS.NMatch,
• MATCHS.Date, A_JOUE.NbPointsMarqués,
• A_JOUE.NbFautes, MATCHS.NVisiteur
• FROM MATCHS ,JOUEURS, A_JOUE
• WHERE JOUEURS.NJoueur A_JOUE.NJoueur
• and MATCHS.NMatch A_JOUE.NMatch and
  MATCHS.NVisiteur3
• devient en SQL Access 
• SELECT DISTINCTROW JOUEURS.NomJoueur,
  MATCHS.NMatch,
• MATCHS.Date, A_JOUE.NbPointsMarqués,
  A_JOUE.NbFautes,
• MATCHS.NVisiteur
• FROM MATCHS INNER JOIN
• (JOUEURS INNER JOIN A_JOUE ON
• JOUEURS.NJoueur A_JOUE.NJoueur) ON
• MATCHS.NMatch A_JOUE.NMatch
• WHERE ((MATCHS.NVisiteur3))

26
Requêtes avec l aide graphique ACCESS

• Remarques
• MATCHS.NMatch les crochets entourent des noms
  comportant des caractères spéciaux ou des
  espaces.
• INNER JOIN ... ON jointure "interne"
• DISTINCT/DISTINCTROW
• DISTINCTROW
• élimination des doublons avant projection.
• DISTINCT
• élimination des doublons après projection.

27
Requêtes avec l aide graphique ACCESS
SELECT JOUEURS.NEquipe, JOUEURS.NomJoueur, JO
UEURS.PrénomJoueur FROM JOUEURS INNER JOIN
A_JOUE ON JOUEURS.NJoueur
A_JOUE.NJoueur
28
Requêtes avec l aide graphique ACCESS

• Requête avec DISTINCTROW
• SELECT DISTINCTROW JOUEURS.NEquipe,
  JOUEURS.PrénomJoueur
• FROM JOUEURS INNER JOIN A_JOUE
• ON
• JOUEURS.NJoueur A_JOUE.NJoueur
• Requête avec DISTINCT
• SELECT DISTINCT JOUEURS.NEquipe,
  JOUEURS.PrénomJoueur
• FROM JOUEURS INNER JOIN A_JOUE
• ON JOUEURS.NJoueur A_JOUE.NJoueur

29
-2- Mise à jour dune base

• Insertion d'une ligne
• INSERT INTO nom-relation ( Champ1, Champ2, ...
  )
• VALUES (Valeur1, Valeur2, ... )
• INSERT INTO JOUEURS ( NJoueur, NEquipe,
  NomJoueur,
• PrénomJoueur )
• VALUES (17, 1, "Joyeux", "Michel")
• Modification des valeurs de champs d'une ligne
• UPDATE nom-relation
• SET Champ1 Valeur1, Champ2 Valeur2, ...
• WHERE condition
• UPDATE JOUEURS
• SET PrénomJoueur "Alain"
• WHERE NJoueur 17

30
-2- Mise à jour dune base

• Suppression d'une ligne
• DELETE FROM nom-relation
• WHERE condition
• DELETE FROM JOUEURS
• WHERE NJoueur 17

31
-3- Gestion des tables

• Création de table.
• CREATE TABLE nom-table
• (nom-d'attribut description, ... )
• Créer une table de nom Nouvelle Table avec deux
  champs de type Texte, un champ de type Entier et
  un champ Date/Heure. Le champ NSS est contraint à
  être la clé primaire.
• CREATE TABLE NouvelleTable
• (Nom TEXT, Prénom TEXT,
• NuméroSS INTEGER
• CONSTRAINT IndexPrim PRIMARY KEY ,
• DateNaissance DATETIME)

32
-3- Gestion des tables

• Suppression de table.
• DROP nom-table
• Création d'index.
• CREATE UNIQUE INDEX nom-index
• ON nom-relation ( nom-d'attribut, ... )
• Créer un index de nom IndNomJoueur
• CREATE INDEX IndNomJoueur
• ON JOUEURS (NomJoueur)
• Suppression d'index.
• DROP INDEX nom-index

33
-4- Exemple de création de requête

• Calculer la somme des points marqués par les
  équipes
• dans tous ses matchs à domicile.
• 1) Choisir les champs que l'on veut voir
  apparaitre dans
• le résultat de la requête.
• NomEquipe, somme des points marqués
• SELECT EQUIPES.NomEquipe, SUM(MATCHS.NbPtsLocaux)
• 2) En déduire les tables utiles pour obtenir ces
  champs.
• EQUIPES, MATCHS
• FROM EQUIPES, MATCHS

34
-4- Exemple de création de requête
3) Ajouter éventuellement les tables participant
à la requête mais dont les champs ne sont pas
visualisés. 4) Déterminer les jointures entre
ces tables. jointure EQUIPES.NEquipe avec
MATCHS.NLocaux WHERE EQUIPES.NEquipe
MATCHS.NLocaux 5) Déterminer les regroupements
nécessaires ? regroupement par NomEquipe GROUP
BY EQUIPES.NomEquipe
35
-4- Exemple de création de requête
SELECT EQUIPES.NomEquipe, SUM
(MATCHS.NbPtsLocaux) FROM EQUIPES, MATCHS WHERE
EQUIPES.NEquipeMATCHS.NLocaux GROUP
BY EQUIPES.NomEquipe
36
Supplément imbrication de clauses WHERE
Soit la requête SELECT EQUIPES.NEquipe,
EQUIPES.NomEquipe, MATCHS.NMatch ,
MATCHS.NLocaux, MATCHS.Date, MATCHS.NbSpectateur,
FROM EQUIPES, MATCHS WHERE EQUIPES.NEquipe
MATCHS.NLocaux AND MATCHS.NbSpectateurs gt 10000
37
Supplément imbrication de clauses WHERE
Cette requête peut aussi sécrire par
imbrication de clauses WHERE en utilisant les
opérateurs ensemblistes suivants IN
appartenance à un ensemble NOT IN non
appartenance SELECT EQUIPES.NEquipe,
EQUIPES.NomEquipe FROM EQUIPES WHERE
EQUIPES.NEquipe IN ( SELECT
MATCHS.NLocaux FROM MATCHS
WHERE (MATCHS.NbSpectateurs gt 10000))
38
Supplément ANY, SOME et ALL
ANY Un ou plusieurs éléments de l'ensemble
satisfont la condition. ALL Tous les éléments
de l'ensemble satisfont la condition. Utilisez
ANY ou SOME, qui sont synonymes, pour extraire de
la requête principale des enregistrements qui
répondent à la comparaison avec au moins un
enregistrement extrait de la sous-requête.
Lexemple suivant retourne tous les produits dont
le prix unitaire est supérieur à celui dau moins
un produit vendu avec une remise de 25 pour cent
ou plus SELECT FROM Produits WHERE Prix
unitaire gt ANY (SELECT Prix unitaire FROM
Détails commandes WHERE Remise gt .25)
39
Supplément ANY, SOME et ALL
Utilisez ALL pour nextraire de la requête
principale que les enregistrements qui répondent
à la comparaison avec tous les enregistrements
extraits de la sous-requête. Si vous
remplaciez ANY par ALL dans lexemple ci-dessus,
la requête retournerait seulement les produits
dont le prix unitaire est supérieur à celui de
tous les produits vendus avec une remise de 25
pour cent ou plus. Ceci est beaucoup plus
restrictif.
40
Supplément DISTINCTROW

• DISTINCTROW permet domettre les données
  relatives à des enregistrements entièrement
  dupliqués, et pas seulement des champs dupliqués.
  Par exemple, vous pourriez créer une requête qui
  joint les tables Clients et Commandes par
  lintermédiaire du champ Code client. La table
  Clients ne contient pas de doublon du champ Code
  client, contrairement à la table Commandes, étant
  donné quà chaque client peuvent correspondre de
  nombreuses commandes. Linstruction SQL suivante
  vous montre comment utiliser DISTINCTROW pour
  produire la liste des sociétés qui ont passé au
  moins une commande, mais sans aucun détail
  concernant ces commandes
• SELECT DISTINCTROW Société
• FROM Clients INNER JOIN Commandes
• ON Clients.Code client Commandes.Code
  client
• ORDER BY Société

41
Supplément DISTINCTROW
Sans DISTINCTROW, cette requête produit
plusieurs lignes pour chaque société qui a passé
plus dune commande. DISTINCTROW na deffet
que si vous sélectionnez des champs dans une
partie seulement des tables utilisées dans la
requête. DISTINCTROW est ignoré si vous
choisissez des champs dans toutes les tables ou
si votre requête ninclut quune table.