Systmes dInformation et Application Anne Universitaire 2018 2019

Systèmes d’Information et Application Année Universitaire 2018 -2019 SIA TD 3 SQL – Le langage de manipulation des données (LMD) – partie 1 Emmanuel Fruchart

Contact Web: http: //happymaths. pagesperso-orange. fr Email: happy. maths@orange. fr

Descriptif du TD Objectif : Savoir interroger et modifier les données d’une base de données déjà implémentée. Partie 1 : travail sur une seule table. Supports : SQL : le Langage de Manipulation des Données.

Contenu du TD Rappel de cours LMD SQL sur une seule table 0 h 30 Etude de cas Insertion, interrogation, modification, suppression de données sur une base existante 1 h 30 Exercice 2 h TNE 3

Présentation Langage de Manipulation des Données (LMD)

LMD : Langage de Manipulation des Données LMD (DML en anglais) : Le LMD est l'ensemble des commandes permettant de : Ø insérer des données. Ø interroger des données. Ø modifier des données. Ø supprimer des données. d’une base de données relationnelle.

Présentation LMD : Insertion des données

LMD : Insertion de données dans une table Instruction standard SQL INSERT INTO nom_table VALUES (val_col 1, val_col 2, …); Les valeurs de toutes les colonnes doivent être renseignées, éventuellement avec la valeur NULL. Instruction standard SQL INSERT INTO nom_table(col 1, col 2, …) VALUES (val_col 1, val_col 2, …); Seules colonnes 1, 2 sont renseignées, autres valeurs positionnées à NULL, ou à valeur par défaut de la colonne.

Présentation LMD : Interrogation des données d’une seule table

LMD : Interrogation d’une seule table Instruction standard SQL SELECT [ALL | DISTINCT] clause_de_selection FROM nom_table [WHERE critere_de_selection] [GROUP BY clause_de_regroupement] [HAVING critere_de_regroupement] [ORDER BY critere_de_tri];

LMD : Interrogation d’une seule table Ø Options de la clause de sélection ALL permet de garder tous les t-uplets même ceux en double (option par défaut) DISTINCT permet d'éliminer les doublons.

Présentation LMD : Mise à jour des données

LMD : Mise à jour de données Instruction standard SQL UPDATE nom_table SET col 1 = expr 1, col 2 = expr 2, … [WHERE critere_de_selection]; Avec cette syntaxe, les colonnes 1, 2, … des lignes vérifiant éventuellement un critère de sélection seront mises à jour.

Présentation LMD : Suppression des données

LMD : Suppression de données Instruction standard SQL DELETE FROM nom_table [WHERE critere_de_selection]; Avec cette syntaxe, les lignes vérifiant éventuellement un critère de sélection seront supprimées. Si aucun critère de type WHERE n'est spécifié, toutes les données de la table sont effacées.

Présentation Etude de cas : utilisation du LMD

Etude de cas : schéma relationnel BDCours

Présentation Insertion de données (jeu de données)

Etude de cas : insertions 1 insert into annee values (2014, 'Année Scolaire 2014 -2015', 150); insert into periode values ('1 T', 'Premier trimestre'); insert into classe values ('2 ND 1', 'Seconde 1'); insert into matiere values ('FRAN', 'Français');

Etude de cas : insertions 2 insert into professeur values ('MOREAUPA', 'Moreau', 'Patrick', 'H', '1986 -07 -01', 'FRAN'); insert into professeur_rem values ('CASTELAI', 'Castelain', 'Alice', 'F', NULL, 'FRAN'); insert into enseignement values (2014, '1 T', ‘TSES', ‘FRAN', ‘MOREAUPA', 11. 5);

Présentation Interrogation de données (une seule table) : Clauses de sélection

Etude de cas : interrogation SELECT Ou SELECT nom FROM professeur; ALL nom FROM professeur; On garde tous les noms, même ceux en double. SELECT FROM DISTINCT nom professeur; On élimine les éventuels doublons.

Etude de cas : interrogation nom Dubois Leroy Dubois Moreau Durand Morel Fournier Petit Garcia Thomas Girard Lambert Lefebvre Martinez Leroy Roussel ALL / DISTINCT Dubois Leroy Moreau Durand Morel Fournier Petit Garcia Thomas Girard Lambert Lefebvre Martinez Roussel

Etude de cas : interrogation SELECT Ou SELECT FROM * FROM professeur; id_professeur, nom, prenom, sexe, date_naissance, id_matiere professeur; Le symbole * représente l'ensemble des colonnes de la table professeur. SELECT FROM nom, prenom, date_naissance professeur; On n’affiche que le contenu des colonnes spécifiées.

Etude de cas : interrogation id_professeur nom prenom sexe date_naissance id_matiere DUBOISAM Dubois Amélie F 1980 -01 -30 ANGL LEROYTHO DUBOISGE MOREAUPA DURANDST MORELSTE FOURNIER Leroy Dubois Moreau Durand Morel Fournier Thomas Gérard Patrick Stéphane Stéphanie Nicolas H H F H 1985 -06 -05 1981 -02 -25 1986 -07 -01 1983 -04 -15 1990 -11 -16 1989 -10 -21 ANGL FRAN HGEO MATH PETITCHR Petit Christophe H 1982 -03 -20 MATH GARCIATH THOMASVI GIRARDAN LAMBERTA LEFEBVRE MARTINEZ LEROYPHI ROUSSELJ Garcia Thomas Girard Lambert Lefebvre Martinez Leroy Roussel Thierry Vincent Antoine Alice Géraldine Nathalie Philippe Jeanne H H H F F F H F 1988 -09 -26 1991 -12 -11 1992 -01 -21 NULL 1987 -08 -31 1993 -02 -14 1984 -05 -10 NULL PHCH PHIL SCVT SECS

Etude de cas : interrogation Incorporation de colonnes constantes Ø chaînes SELECT FROM nom, prenom, sexe, 'DATE DE NAISSANCE', date_naissance professeur; Ø numériques SELECT FROM 2015, nom, prenom, sexe, date_naissance professeur;

Etude de cas : interrogation nom Dubois Leroy Dubois Moreau Durand Morel Fournier Petit Garcia Thomas Girard Lambert Lefebvre Martinez Leroy Roussel prenom Amélie Thomas Gérard Patrick Stéphane Stéphanie Nicolas Christophe Thierry Vincent Antoine Alice Géraldine Nathalie Philippe Jeanne sexe F H H H H H F F F H F DATE DE NAISSANCE DATE DE NAISSANCE DATE DE NAISSANCE DATE DE NAISSANCE DATE DE NAISSANCE date_naissance 1980 -01 -30 1985 -06 -05 1981 -02 -25 1986 -07 -01 1983 -04 -15 1990 -11 -16 1989 -10 -21 1982 -03 -20 1988 -09 -26 1991 -12 -11 1992 -01 -21 NULL 1987 -08 -31 1993 -02 -14 1984 -05 -10 NULL

Etude de cas : interrogation Utilisation de fonctions arithmétiques SELECT FROM id_annee - 2000, desc_annee; SELECT FROM id_annee, nb_filles + nb_garcons annee; SELECT id_annee, 'Pourcentage de filles : ', nb_filles * 100 / (nb_filles + nb_garcons) annee; FROM

Etude de cas : interrogation id_annee - 2000 14 15 desc_annee Année Scolaire 2014 -2015 Année Scolaire 2015 -2016 id_annee nb_filles + nb_garcons 2014 300 2015 299 id_annee 2014 2015 Pourcentage de filles : nb_filles * 100 / (nb_filles + nb_garcons) 50. 0000 52. 5084

Etude de cas : interrogation Utilisation de fonctions arithmétiques ensemblistes SELECT COUNT(*) FROM professeur; SELECT FROM COUNT(DISTINCT nom) professeur; SELECT FROM MAX(nb_filles), MIN(nb_filles) annee; SELECT FROM SUM(nb_filles), AVG(nb_garcons) annee;

Etude de cas : interrogation COUNT(prenom) 16 MAX(nb_filles) 157 SUM(nb_filles) 307 COUNT(DISTINCT nom) 14 MIN(nb_filles) 150 AVG(nb_garcons) 146. 0000

Etude de cas : interrogation Utilisation de fonctions littérales Majuscules, minuscules SELECT UPPER(nom), LOWER(prenom), date_naissance FROM professeur; Concaténation SELECT CONCAT(UPPER(nom), ' ', LOWER(prenom)), date_naissance FROM professeur; Ou SELECT UPPER(nom) || ' ' || LOWER(prenom), date_naissance FROM professeur;

Etude de cas : interrogation CONCAT(UPPER(nom), ' ', LOWER(prenom)) DUBOIS amélie LEROY thomas DUBOIS gérard MOREAU patrick DURAND stéphane MOREL stéphanie FOURNIER nicolas PETIT christophe GARCIA thierry THOMAS vincent GIRARD antoine LAMBERT alice LEFEBVRE géraldine MARTINEZ nathalie LEROY philippe ROUSSEL jeanne date_naissance 1980 -01 -30 1985 -06 -05 1981 -02 -25 1986 -07 -01 1983 -04 -15 1990 -11 -16 1989 -10 -21 1982 -03 -20 1988 -09 -26 1991 -12 -11 1992 -01 -21 NULL 1987 -08 -31 1993 -02 -14 1984 -05 -10 NULL

Etude de cas : interrogation Sous-chaîne SELECT DISTINCT SUBSTRING(prenom FROM 1 FOR 3) FROM professeur; Longueur de la chaîne SELECT ALL prenom, CHARACTER_LENGTH(prenom) FROM professeur;

Etude de cas : interrogation CHARACTER_LENGTH( prenom) Amélie 6 Thomas 6 Gérard 6 Patrick 7 Stéphane 8 Stéphanie 9 Nicolas 7 Christophe 10 Thierry 7 Vincent 7 Antoine 7 Alice 5 Géraldine 9 Nathalie 8 Philippe 8 Jeanne 6 prenom SUBSTRING(prenom FROM 1 FOR 3) Amé Tho Gér Pat Sté Nic Chr Thi Vin Ant Ali Nat Phi Jea

Présentation Interrogation de données (une seule table) : Critères de sélection

Etude de cas : interrogation Mots clés =, <>, <, >, <=, >= SELECT WHERE * FROM nom = 'Leroy'; professeur SELECT WHERE * FROM professeur nom > 'Lombart'; SELECT WHERE * FROM nb_filles > 150; annee

Etude de cas : interrogation Mots clés AND et OR SELECT WHERE * FROM professeur (nom = 'Leroy') OR (prenom = 'Géraldine'); SELECT WHERE * FROM professeur (nom > 'Lombart') AND (prenom < 'Marc'); SELECT WHERE * FROM enseignement (id_periode = '1 T') AND ( (moyenne_classe >= 12) OR (moyenne_classe < 8. 5) );

Etude de cas : interrogation Mots clés IN et BETWEEN SELECT WHERE * FROM enseignement moyenne_classe BETWEEN 11. 0 AND 11. 5; SELECT WHERE * FROM professeur prenom IN ('Jacques', 'Thomas', 'Pierre'); SELECT WHERE * FROM professeur prenom IN ('Géraldine', 'Thomas', 'Antoine') AND nom < 'Latour';

Etude de cas : interrogation Mot clé LIKE On cherche des valeurs chaînes de caractères correspondant à un modèle donné. Le caractère '%' modélise une chaine quelconque de caractères (incluant une chaine vide), et le caractère '_' modélise 1 seul caractère. SELECT WHERE * FROM professeur nom LIKE '_e_o_'; SELECT WHERE * FROM professeur prenom LIKE '___r%';

Etude de cas : interrogation Mot clé NOT SELECT WHERE * FROM enseignement moyenne_classe NOT BETWEEN 8. 0 AND 14. 0; SELECT * FROM professeur WHERE prenom NOT IN ('Amélie', 'Thomas', 'Gérard', 'Stéphane', 'Patrick'); SELECT WHERE * FROM professeur prenom NOT LIKE '___r%';

Etude de cas : interrogation Mots clés IS NULL et IS NOT NULL SELECT WHERE nom, prenom FROM date_naissance IS NULL; professeur SELECT FROM WHERE nom, prenom professeur date_naissance IS NOT NULL and prenom NOT LIKE '_____t%';

Etude de cas : interrogation Utilisation de fonctions littérales SELECT WHERE UPPER(nom), LOWER(prenom), date_naissance FROM professeur CHARACTER_LENGTH( CONCAT(nom, prenom)) BETWEEN 12 AND 15; prenom FROM professeur SUBSTRING(prenom FROM 1 FOR 3) IN ('Pie', 'Mar', 'Dan', 'Pat') AND date_naissance IS NOT NULL;

Présentation Interrogation de données (une seule table) : Clauses de regroupement

Etude de cas : interrogation Les colonnes dans la clause GROUP BY sont obligatoirement présentes dans la clause de sélection, accompagnées de fonctions de groupe ! SELECT nom, COUNT(*) FROM professeur WHERE SUBSTRING(prenom FROM 1 FOR 1) <> 'J' GROUP BY nom; SELECT id_annee, SUBSTRING(id_professeur FROM 1 FOR 1), AVG(moyenne_classe) FROM enseignement GROUP BY id_annee, SUBSTRING(id_professeur FROM 1 FOR 1);

Etude de cas : interrogation nom Dubois Durand Fournier Garcia Girard Lambert Lefebvre Leroy Martinez Moreau Morel Petit Thomas COUNT(*) 2 1 1 1 id_annee 2014 SUBSTRING(id_professe AVG(moyenne_classe) ur FROM 1 FOR 1) D 11. 755555576748318 F 10. 633333206176758 G 12. 766667048136393 L 12. 833333334

Présentation Interrogation de données (une seule table) : Critères de regroupement

Etude de cas : interrogation On élimine certains groupes selon un critère sur le groupe. SELECT WHERE GROUP BY HAVING SELECT nom, COUNT(*) FROM professeur SUBSTRING(prenom FROM 1 FOR 1) <> 'J' nom COUNT(*) > 1; id_annee, SUBSTRING(id_professeur FROM 1 FOR 1), AVG(moyenne_classe) FROM enseignement GROUP BY id_annee, SUBSTRING(id_professeur FROM 1 FOR 1) HAVING AVG(moyenne_classe) > 12. 0;

Etude de cas : interrogation nom Dubois Leroy 2 2 id_annee 2014 COUNT(*) SUBSTRING(id_professeur AVG(moyenne_classe) FROM 1 FOR 1) G 12. 766667048136393 L 12. 833333334

Présentation Interrogation de données (une seule table) : Critères de tri

Etude de cas : interrogation Tri sur une seule colonne : ASC tri croissant DESC tri décroissant. Tri par défaut : ASC. SELECT DISTINCT prenom FROM professeur ORDER BY prenom; Ou … ORDER BY prenom ASC; Ou … ORDER BY 1;

Etude de cas : interrogation prenom Alice Amélie Antoine Christophe Géraldine Gérard Jeanne Nathalie Nicolas Patrick Philippe Stéphanie Thierry Thomas Vincent

Etude de cas : interrogation Tri sur plusieurs colonnes : SELECT FROM WHERE nom, prenom professeur SUBSTRING(prenom FROM 1 FOR 3) IN ('Ant', 'Mar', 'Dan', 'Pat') ORDER BY prenom DESC, nom ASC; Ou … ORDER BY 2 DESC, 1 ASC;

Etude de cas : interrogation nom Moreau Girard prenom Patrick Antoine