SQL (1)

1. SQL (1) yves.pigneur@hec.unil.ch gabor.maksay@eivd.ch

2. Référence • Bases de données et modèles de calcul, Outils et méthodes pour l'utilisateur, Jean-Luc Hainault, Dunod, 3ème édition, 2002 • Chapitre 4, pp. 53-82 2

3. Agenda • définition d’une base de données • consultation et extraction simples de données 3

4. Organisation des données • tables (tables) • colonnes (columns) • identifiant primaire (primary keys) • clé étrangère (foreign keys) 4

5. Gestion des données • schéma: • ajouter et supprimer une table dans une base de données • ajouter une colonne dans une table • base de données: • ajouter et retirer des lignes dans une table • modifier les valeurs d’une colonne dans certaines lignes un langage unique 5

6. Accès aux données • décrire des ensembles de données à partir de critères • sans considération technique d’accès • le résultat apparaît sous la forme d’une table • extrait d’une ou plusieurs tables de la base • avec possibilités de valeurs calculées 6

7. LigneCommande NoCommande NoProduit QteCommandee Exemple de BD Produit No Libelle Prix QteStock Client No Nom Adresse Localite (Categorie) Compte Commande No NoClient Date 7

8. Création d ’une table create table CREATE TABLE Client ( No CHAR (4), Nom CHAR (12), Adresse CHAR (20), Localite CHAR (12), Categorie CHAR (2), Compte DECIMAL (9,2)) 8

9. Types de données domaines SMALLINT entier (16 bits) INTEGER entier (32 bits) DECIMAL (m,n) décimaux de m chiffres (dont n après la virgule) FLOAT réels flottants CHAR (n) chaîne de n caractères VARCHAR chaîne variable d’au plus n car. DATE dates (année, mois et jour) TIME instants (heure, minute, seconde) 9

10. Identifiant primary key CREATE TABLE Client ( No CHAR (4), ..., PRIMARY KEY (No)) CREATE TABLE LigneCommande ( NoCommande CHAR (4), NoProduit CHAR (5), QteCommandee DECIMAL (4), PRIMARY KEY (NoCommande,NoProduit)) CREATE TABLE Assure ( NoAffilie CHAR (4), NoIdentite CHAR (5), Nom CHAR (25), PRIMARY KEY (NoAffilie) UNIQUE (NoIdentite)) unique 10

11. Clé étrangère foreign key CREATE TABLE LigneCommande ( NoCommande CHAR (4), NoProduit CHAR (5), QteCommandee DECIMAL (4), PRIMARY KEY (NoCommande, NoProduit), FOREIGN KEY (NoCommande) REFERENCES Commande, FOREIGN KEY (NoProduit) REFERENCES Produit) 11

12. Colonne obligatoire (facultative) not null CREATE TABLE LigneCommande ( NoCommande CHAR (4) NOT NULL, NoProduit CHAR (5) NOT NULL, QteCommandee DECIMAL (4), PRIMARY KEY (NoCommande, NoProduit), FOREIGN KEY (NoCommande) REFERENCES Commande, FOREIGN KEY (NoProduit) REFERENCES Produit) 12

13. Modification d’un schéma drop • suppression d’une table DROP TABLE LigneCommande • ajout d’une colonne ALTER TABLE LigneCommande ADD Montant SMALLINT • retrait d’une colonne ALTER TABLE LigneCommande DROP Montant alter 13

14. Consultation d’une BD SELECT Nom, Localite FROM Client WHERE Localite = ‘POITIERS’ 14

15. Consultation d’une BD SELECT Nom, Localite précise les valeurs (colonnes, valeurs calculées) qui constituent chaque ligne du résultat FROM Client indique les tables desquelles le résultat tire ses valeurs WHERE Localite = ‘POITIERS’ donne la condition de sélection que doivent satisfaire les lignes qui fournissent le résultat 15

16. AVANT APRES Extraction SELECT No, Nom, Localite FROM Client 16

17. AVANT APRES Extraction SELECT * FROM Client 17

18. AVANT APRES Extraction SELECT No, Nom, Localite FROM ClientWHERE Localite = ‘Poitiers’ 18

19. AVANT APRES AVEC DISTINCT APRES SANS DISTINCT Duplication de ligne SELECT DISTINCT Localite FROM Client WHERE Categorie = ‘1’ 19

20. and or not () AVANT APRES Conditions de sélection SELECT Nom, Adresse, Compte FROM Client WHERE Localite = ‘Poitiers’ AND Compte <= 0 20

21. Conditions de sélection is null Categorie IS NULL Categorie IS NOT NULL Categorie IN (‘C1’, ‘C2’, ‘C3’) Localite NOT IN (‘Toulouse’, ‘Namur’) Compte BETWEEN 1000 AND 4000 Categorie LIKE ‘_1’ Categorie NOT LIKE ‘%Neuve%’ in like _ % 21

22. AVANT APRES Données calculées SELECT ‘TVA de ‘, No, ‘ = ‘, 0.075*Prix*QteStock FROM Produit WHERE QteStock > 500 22

23. Fonctions agrégatives COUNT(*) nombre de lignes trouvées AVG(colonne) moyenne des valeurs de la colonne SUM(colonne) somme des valeurs de la colonne MIN(colonne) minimum des valeurs de la colonne MAX(colonne) maximum des valeurs de la colonne AVG et SUM: ne peuvent être appliqués uniquement sur des expressions à valeur numérique 23

24. AVANT APRES Fonctions agrégatives et duplication de lignes distinct SELECT COUNT (NoClient) FROM Commande nombre de commandes !!! 24

25. AVANT APRES 7 Pas supporté par Access 2002! Données calculées et duplication de lignes distinct SELECT COUNT (DISTINCT NoClient) FROM Commande nombre de clients 25

26. AVANT APRES Sous-requêtes SELECT Commande.No, Date FROM Commande WHERE NoClient IN (SELECT Client.No FROM Client WHERE Localite = ‘POITIERS’) 26

27. AVANT APRES Sélection négative Les commandes qui ne spécifient pas le produit 7 SELECT Commande.No, Date, NoClient FROM Commande WHERE Commande.No NOT IN ( SELECT NoCommande FROM LigneCommande WHERE NoProduit = ‘7’) 27

28. AVANT APRES Sélection négative Les commandes qui spécifient au moins un produit qui n’est pas 7 SELECT Commande.No, Date, NoClient FROM Commande WHERE Commande.No IN ( SELECT NoCommande FROM LigneCommande WHERE NOT (NoProduit = ‘7’)) 28

29. AVANT APRES Deux fois la même table dans la même requête Les clients qui habitent dans la même localité que le client n°2 SELECT * FROM Client WHERE Localite IN ( SELECT Localite FROM Client WHERE Client.No = ‘2’) 29

30. AVANT APRES exists Quantificateur existentiel Les produits qui ne sont pascommandés pour l’instant (dans une ligne de commande) SELECT * FROM Produit P WHERE NOT EXISTS ( SELECT NoProduit FROM LigneCommande WHERE NoProduit = P.No) 30

31. any all AVANT APRES Quantificateur universel SELECT * FROM LigneCommande WHERE QteCommandee <= ALL ( SELECT QteCommandee FROM LigneCommandee WHERE NoProduit = ‘7’) AND NoProduit = ‘7’ Les lignes de commande spécifiant la quantité minimum de produit 7 31

32. Merci pour votre attention ! 32