Ces exercices corrigés de SQL débutant s’adressent comme son nom l’indique à des non initiés au langage SQL. D’autres exercices traiteront plus en profondeur des bases de données.
Enoncé
La base de données utilisée modélise une activité de location de voitures entre particuliers. Les informations conservées dans la base sont les informations concernant les voitures, les propriétaires de ces voitures, les clients et les locations effectuées. Il s’agit de la base de données utilisée comme exemple durant le cours (dans sa version complète… certains attributs ont été ajoutés par rapport à la version du cours)
Le schéma relationnel est le suivant :
VOITURE (immat :VARCHAR, modele :VARCHAR, marque :VARCHAR, categorie :VARCHAR, couleur :VARCHAR, places :INTEGER, achatA :INTEGER, compteur :INTEGER, prixJ :DECIMAL, #codeP :INTEGER )
PROPRIETAIRE (codeP :VARCHAR(4), pseudo :VARCHAR, email :VARCHAR, ville :VARCHAR, anneeI :INTEGER)
CLIENT (codeC :VARCHAR(4), nom :VARCHAR, prenom :VARCHAR, age :INTEGER, permis :VARCHAR, adresse :VARCHAR, ville :VARCHAR)
LOCATION (#codeC :VARCHAR(4), #immat :VARCHAR, annee :INTEGER, mois :INTEGER, numLoc :INTEGER, km:INTEGER, duree:INTEGER, villeD:VARCHAR, villeA :VARCHAR, dateD :DATE, dateF :DATE)
Les clés primaires sont en gras/souligné ; les clés étrangères sont précédées par un#
Un client est identifié par son code client (codeC) et les informations conservées sont ses nom, prénom, âge, nom de rue, de ville ainsi que son n° de permis.
Un propriétaire est identifié par son (code propriétaire) et les informations conservées sont son nom, sa ville et son email.
Une voiture est identifiée par son (n° d’immatriculation) et les informations conservées sont la marque, le modèle, la couleur, la catégorie, le nombre de places, l’année d’achat, le prix par jour et le km compteur qui est mis à jour après chaque nouvelle location.
La couleur peut prendre ses valeurs parmi les suivantes : blanc, rouge, vert, noir, violet et bleu
Le nombre de place dans les voitures ne peut excéder 6 (6 compris)
Une location est identifiée par le (code du client, l’immatriculation du véhicule et une date décomposée en mois et année).
En complément pour chaque location, il est conservé un numéro de location, le kilométrage parcouru, la durée, les villes de départ et d’arrivée, et les dates de début et de fin.
Exercice 1
Créer le schéma Entité-Association correspondant au cahier des charges et qui convient évidemment au schéma relationnel fourni.
Tout est dans l’énoncé…
Exercice 2
A partir du schéma relationnel fourni en amont, créer sur MySQL votre BDD en respectant les noms.
Pour la contrainte des couleurs : couleur ENUM(‘blanc’,’rouge’,’vert’,’noir’,’violet’,’bleu’),
Pour la contrainte de places : places INT NULL CHECK (places <= 6),
Attention, le schéma de la base n’est pas mis à jour automatiquement. Il faut réactualiser la fenêtre.
Le fichier peuplement tables permet l’installation d’un jeu de données dans les tables.
Utilisez la commande du menu de MySQL : File / Open SQL script pour insérer les tuples des tables.
Exercice 3
- Afficher la liste (modèle,couleur,immatriculation) des véhicules Peugeot.
- Afficher les caractéristiques du véhicule 56AA46. (utilisez *)
- Où habite Depardieu ?
- Quels clients ont ‘Jean’ pour prénom ?
- Liste des clients (nom, prénom) ayant `jean’ pour prénom , par ordre alphabétique.
- Liste des clients ayant pour prénom composé commençant par jean ( jean-pierre, jean-jacques etc….)
- Qui (nom,prenom) habite dans une rue dont le nom contient le mot ‘ours’ ?
- Quels sont les cabriolets 4 places ?
- Liste (numéros d’immatriculation) des voitures de couleur bleu.
- Liste (numéros d’immatriculation) des voitures de couleur blanc.
- Liste (*) des véhicules immatriculés dans le 62.
— 1 (5 rows returned)
SELECT modele, couleur, immat FROM voiture WHERE marque = ‘Peugeot’;
— 2 (1 rows returned)
SELECT * FROM voiture WHERE immat = ’56AA46′;
— 3 (3 rows returned)
SELECT prenom, adresse, ville FROM client WHERE nom = ‘Depardieu’;
— 4 (2 rows returned)
SELECT nom FROM client WHERE prenom = ‘Jean’;
— 5 (2 rows returned)
SELECT nom , prenom FROM client WHERE prenom = ‘Jean’ order by nom;
— 6 (5 rows returned)
SELECT nom , prenom FROM client WHERE prenom like ‘Jean%’ order by nom;
— 7 (2 rows returned)
SELECT nom, prenom, adresse FROM client WHERE adresse like ‘%ours%’ order by nom;
— 8 (3 rows returned)
SELECT * FROM voiture WHERE categorie = ‘cabriolet’and places = 2;
— 9 (0 rows returned)
SELECT immat, marque , modele FROM voiture WHERE couleur = ‘bleu’;
— 10 (8 rows returned)
SELECT immat, marque , modele FROM voiture WHERE couleur = ‘blanc’;
— 11 (4 rows returned)
SELECT * FROM voiture WHERE immat like ‘%62’;
Exercice 4
4-1 Etude la table Voiture
- Combien y-a- t-il de véhicules dans la BD ?
- Combien de marques ? ( SELECT count(marques) )
- Combien d’immatriculation ? ( SELECT count(immat) )
- Faire les mêmes requêtes en ajoutant distinct et expliquez la différence
4-2. Insérer les tuples dans une table
voiture (10AK37, Peugeot, 205, cabriolet, violet, 2 places, 1980, compteur : 65 000, 40€/jour, propriétaire : P99)
voiture (11BF37, Peugeot, 206, cabriolet, violet, 2 places, 1980, compteur : 21 000, 32€/jour, propriétaire : P99)
voiture (15AK37, Peugeot, 205, cabriolet, violet, 2 places, 1985, compteur : 27 000, 30€/jour, propriétaire :P99)
- Vérifier la présence de ces voitures dans la table concernée
voiture (63GH94, Megane, Renault, berline, rose, 4 places, 2012, compteur : 750, 40€/jour, propriétaire : P99)
voiture (87AZ92, Clio, Renault, citadine, vert, 4 places, 1999, compteur : 61200, 40€/jour, propriétaire : P99)
- Si problème => identifiez le problème, peut-on corrigez et refaire l’insertion ?
4-3. Divers
- Combien y-a- t-il de véhicules dans la BD ?
- Combien y-a- t-il de véhicules de couleur violet ?
- Changer la couleur du véhicule d’immatriculation 11BF37 en blanc
- Supprimer les véhicules de couleur violet, combien reste-t-il de véhicules dans la BD ?
- Supprimer la voiture dont le codeP=’P89’, expliquez
4.4 Insérer des colonnes dans une classe
- Ajouter à la table location deux nouvelles colonnes destinées à recevoir l’avis des utilisateurs sur la location effectuée :
- note (de 0 à 5)
- appréciation (texte limitée à 120 caractères) et
Vérifier la table location avec la commande DESC
— 1
SELECT count(modele) FROM voiture;
SELECT count(distinct modele) FROM voiture;
SELECT count(immat) FROM voiture;
SELECT count(distinct immat) FROM voiture;
— 2
insert into voiture (immat,modele,marque,categorie,couleur,places,achatA,compteur,prixJ,codeP)
values (’10AK37′, ‘205’,’Peugeot’, ‘cabriolet’ , ‘violet’,2,1980,65000,40,’P99′);
insert into voiture values (’11BF37′, ‘206’,’Peugeot’, ‘cabriolet’ , ‘violet’,2,1980,21000,27,’P99′);
insert into voiture values (’15AK37′, ‘205’,’Peugeot’, ‘cabriolet’ , ‘violet’,2,1980,27000,32,’P99′);
SELECT count(*) FROM voiture;
insert into voiture (immat,modele,marque,categorie,couleur,places,achatA,compteur,prixJ,codeP)
values (’63GH94′, ‘megane’,’Renault’,’berline’,’rouge’,4,’2012′,750,40,’P99′);
insert into voiture (immat,modele,marque,categorie,couleur,places,achatA,compteur,prixJ,codeP)
values (’87AZ93′, ‘Clio’,’Renault’, ‘citadine’,’vert’,4,’1999′,61200,30,’P99′);
— ———————
— 3
SELECT count(*) FROM voiture;
SELECT * FROM voiture WHERE couleur = ‘violet’;
SELECT * FROM voiture WHERE immat = ’11BF37′;
update voiture set couleur = ‘blanc’ WHERE immat = ’11BF37′;
SELECT count(*) FROM voiture WHERE couleur = ‘violet’;
SELECT count(*) FROM voiture;
delete FROM voiture WHERE couleur = ‘violet’;
SELECT count(*) FROM voiture;
SELECT count(*) FROM voiture WHERE codeP = ‘P89’;
delete FROM voiture WHERE codeP = ‘P89’;
SELECT count(*) FROM voiture WHERE codeP = ‘P89’;
— impossible de supprimer P89 est référencé comme clé étrangère dans un autre tuple
— —————————————-
— pour modifier le paramère safe mode (si probleme)
— —————————————-
— show variables like ‘sql\_safe\_updates’ ;
— set sql\safe\updates = 0 ;
— —————————————-
— 4
alter table location add column note int;
alter table location add column appreciation varchar(20);
desc location;
Exercice 5
- Combien y-a-t-il de véhicules dans la bd
- Combien y-a-t-il de locations dans la bd
- Supprimez le véhicule immatriculé `11TR62′
- Quel code erreur et pourquoi ?
- Dans quelle table faut-il activer le delete on cascade ?
Deux ALTER TABLE seront nécessaires :
un pour DROP FOREIGN KEY
puis un pour ADD CONSTRAINT … FOREIGN KEY
Attention : le nom choisi pour désigner la contrainte de Foreign Key doit être unique , donc utiliser des noms du genre…nomVariableNomTable
- Réessayer la suppression du véhicule immatriculé `11TR62′
- Combien y-a-t-il de véhicules dans la bd (comparez avec le résultat précédemment noté)
- Combien y-a-t-il de locations dans la bd (comparez avec le résultat précédemment noté)
- Vider la table (supprimez tous les tuples) de la table voiture
- Supprimer la table client (tuple et structure de la table) par un DROP TABLE
- Pourquoi cela ne marche pas ?
- Supprimer la table location (tuple et structure de la table) par un DROP TABLE Puis supprimer la table client. Pourquoi la suppression de location est possible puis client et pas client avant location ?
- Supprimer dans le bon ordre par des DROP TABLE, les tables voiture, maintenance et propriétaire
- Enfin régénérer l’ensemble de la BD à l’état initial pour la suite du TD.
Recréez les tables par exécution des scripts de création des tables ;
Relancez le script du peuplement des tables
Puis procédez à une insertion groupée d’un fichier de données dans la base de données (plus précisément dans la table location )
LOAD DATA LOCAL INFILE ‘/temp/locationComplements.csv’ 2
INTO TABLE `location `
FIELDS TERMINATED BY ‘;’
LINES TERMINATED BY ‘\n’;
pour modifier la variable system correspondante :
SHOW GLOBAL VARIABLE LIKE ‘local_infile’;
SET GLOBAL LOCAL_INFILE = ‘ON’
— 1,2,3
SELECT count(*) FROM voiture;
SELECT count(*) FROM location;
delete FROM voiture WHERE immat = ’11TR62′;
— 4
#–Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`loueur`.`location`, CONSTRAINT `immat` FOREIGN KEY (`immat`) REFERENCES `voiture` (`immat`) ON DELETE NO ACTION ON UPDATE NO ACTION)
— 5
alter table location drop foreign key immatLocation;
alter table location add CONSTRAINT immatLocation
FOREIGN KEY (immat) REFERENCES voiture (immat)
ON DELETE CASCADE ON UPDATE NO ACTION;
— 6,7,8
delete FROM voiture WHERE immat = ’11TR62′;
SELECT count(*) FROM voiture;
SELECT count(*) FROM location;
— 9,10,11,12
delete FROM voiture ;
drop table client;
#– Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails
drop table location;
drop table client;
— 13
drop table maintenance;
drop table voiture;
drop table proprietaire;
— ———————
# LOAD DATA FILE
load data local infile ‘/temp/locationComplements.csv’
into table location
fields terminated by ‘;’
lines terminated by ‘\n’;
— ———————
— —————————————-
— pour permettre le chargement massif de données
— —————————————-
— show global variables like ‘local\infile’;
— set global local\infile = ‘ON’;
— —————————————-
Exercice 6
Création d’un utilisateur et attribution des droits d’accès à la BD
- Créez un utilisateur de nom ‘superbozo’ (mot de passe ‘user’) : CREATE USER ‘superbozo’@’localhost’ IDENTIFIED BY ‘123’;
- Attribuez-lui tous les droits sur la bd GRANT ALL ON loueur.* TO ‘superbozo’@’localhost’;
- Créez un utilisateur de nom ‘bozo’ (mot de passe ‘user’)
- Attribuez-lui seulement le droit d’accès en lecture de la table location (SELECT)
- Reconnectez-vous sous le nom bozo et essayez d’insérer un nouveau propriétaire (pseudo bozo, code P01, email bozo@gmail.com, ville paris, année 2017).
- Reconnectez-vous sous le nom root
- Révoquez pour superbozo les droits lui permettant de supprimer des tuples (DELETE) : REVOKE.
- Visualisez les droits de `bozo’ , de ‘superbozo’ et de l’utilisateur courant
- Supprimez les utilisateurs bozo et superbozo : DROP USER …
- Visualiser la liste des utilisateurs (depuis la table mysql.user)
create user ‘superbozo’@’localhost’ identified by ‘123’ ;
grant all on loueur.* to ‘superbozo’@’localhost’;
—
create user ‘bozo’@’localhost’ identified by ‘user’ ;
grant SELECT on loueur.location to ‘bozo’@’localhost’;
—
revoke delete on loueur.location FROM ‘superbozo’@’localhost’;
show grants for ‘superbozo’@’localhost’;
show grants for ‘bozo’@’localhost’;
show grants for current_user;
show grants;
—
drop user ‘bozo’@’localhost’;
drop user ‘superbozo’@’localhost’;
SELECT * FROM mysql.user;
SELECT user, host FROM mysql.user order by user;
