6 Corrected SQL exercises for beginners

These corrected beginner SQL exercises are aimed, as the name suggests, at people who are not initiated into the SQL language. Other exercises will cover databases in more depth.

sql débutant

States

The database used models a car rental activity between individuals. The information kept in the database is information concerning the cars, the owners of these cars, the customers and the rentals made. This is the database used as an example during the course (in its full version… some attributes have been added compared to the course version)

The relational diagram is as follows:

CAR (immat :VARCHAR, model: VARCHAR, brand: VARCHAR, category: VARCHAR, color: VARCHAR, places: INTEGER, purchaseA: INTEGER, counter: INTEGER, priceJ: DECIMAL, #codeP: INTEGER)

OWNER (codeP:VARCHAR(4), nickname: VARCHAR, email: VARCHAR, city: VARCHAR, year: INTEGER)

CUSTOMER (Ccode:VARCHAR(4), last name: VARCHAR, first name: VARCHAR, age: INTEGER, license: VARCHAR, address: VARCHAR, city: VARCHAR)

RENTAL (#codeC:VARCHAR(4), #immat:VARCHAR, year:INTEGER, month:INTEGER, locnum:INTEGER, km:INTEGER, duration:INTEGER, cityD:VARCHAR, cityA:VARCHAR, dateD:DATE, dateF:DATE)

Primary keys are bolded/underlined; foreign keys are preceded by un#

A customer is identified by his customer code (Ccode) and the information stored is his last name, first name, age, street name, city name and his license number.

An owner is identified by his (owner code) and the information stored is his name, city and email.

A car is identified by its (registration number) and the information stored is the make, model, color, category, number of seats, year of purchase, price per day and km odometer which is updated after each new rental.

The color can take its values from the following: white, red, green, black, purple and blue

The number of seats in cars cannot exceed 6 (6 included)

A rental is identified by the (customer code, vehicle registration and a date broken down into month and year).

In addition, for each rental, a rental number, the mileage traveled, the duration, the cities of departure and arrival, and the start and end dates are kept.

Exercise 1

Create the Entity-Association diagram corresponding to the specifications and which obviously suits the relational diagram provided.

It's all in the statement...

Exercise 2

From the relational diagram provided upstream, create your database on MySQL respecting the names.

For the color constraint: color ENUM('white','red','green','black','purple','blue'),

For the places constraint: places INT NULL CHECK (places <= 6),

Please note, the database schema is not updated automatically. You need to refresh the window.

The file stand tables  allows the installation of a dataset in tables.

Use the MySQL menu command: File / Open SQL script to insert the table tuples.

DROP DATABASE IF EXISTS lessor; 
CREATE DATABASE IF NOT EXISTS lessor; 
 
DROP TABLE IF EXISTS client;
CREATE TABLE IF NOT EXISTS client (
  codeC VARCHAR(4) ,
  name VARCHAR(20) NOT NULL,
  first name VARCHAR(20),
  age INT ,
  permit VARCHAR(10) ,
  address VARCHAR(50) ,
  city VARCHAR(20),
  PRIMARY KEY (Ccode) );
 
DROP TABLE IF EXISTS owner;
CREATE TABLE IF NOT EXISTS owner (
  codeP VARCHAR(4) NOT NULL,
  pseudo VARCHAR(20) NOT NULL,
  email VARCHAR(20) NULL,
  city VARCHAR(20) NULL,
  yearI INT NULL,
  PRIMARY KEY (Pcode) );
  
DROP TABLE IF EXISTS car;
CREATE TABLE IF NOT EXISTS car (
  immat VARCHAR(10) NOT NULL,
  model VARCHAR(20) NULL,
  mark VARCHAR(20) NULL,
  category VARCHAR(20) NULL,
  color ENUM('white','red','green','black','purple','blue'),
  places INT NULL CHECK (places <= 6),
  purchaseA VARCHAR(4) NULL,
  counter INT NOT NULL,
  price INT NULL,
  codeP VARCHAR(4) NULL,
   PRIMARY KEY (immat),
   
   CONSTRAINT codePCar FOREIGN KEY (codeP)
Owner REFERENCES (codeP)
ON DELETE NO ACTION
ON UPDATE NO ACTION );
 
DROP TABLE IF EXISTS location;
CREATE TABLE IF NOT EXISTS location (
  codeC VARCHAR(4) NOT NULL,
  immat VARCHAR(10) NOT NULL,
  year INT NOT NULL,
  month INT NOT NULL,
  numLoc VARCHAR(5) NULL,
  km INT NOT NULL,
  duration INT NULL,
  cityD VARCHAR(20) NULL,
  cityA VARCHAR(20) NULL,
  dateD DATE NULL,
  dateF DATE NULL,
  PRIMARY KEY (Ccode, immat, year, month),
 
CONSTRAINT codeClocation FOREIGN KEY (codeC)
Customer REFERENCES (codeC)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
  CONSTRAINT immatRental FOREIGN KEY (immat)
CAR REFERENCES (immat)
ON DELETE NO ACTION
ON UPDATE NO ACTION );

Exercise 3

  1. Display the list (model, color, registration) of Peugeot vehicles.
  2. View vehicle specifications 56AA46. (use *)
  3. Where does Depardieu live?
  4. Which customers have 'Jean' for first name?
  5. List of customers (last name, first name) with `jean' for first name, in alphabetical order.
  6. List of customers with a compound first name starting with jean (jean-pierre, jean-jacques etc.)
  7. Who (last name, first name) lives in a street whose name contains the word 'bear'?
  8. What are 4-seater convertibles?
  9. List (registration numbers) of blue colored cars.
  10. List (registration numbers) of white-colored cars.
  11. List (*) of vehicles registered in 62.

— 1 (5 rows returned)
SELECT model, color, immat FROM car WHERE brand = 'Peugeot';

— 2 (1 rows returned)
SELECT * FROM car WHERE immat = '56AA46′;

— 3 (3 rows returned)
SELECT first name, address, city FROM customer WHERE last name = 'Depardieu';

— 4 (2 rows returned)
SELECT name FROM customer WHERE first name = 'John';

— 5 (2 rows returned)
SELECT last name, first name FROM customer WHERE first name = 'Jean' order by last name;

— 6 (5 rows returned)
SELECT last name, first name FROM client WHERE first name like 'Jean%' order by last name;

— 7 (2 rows returned)
SELECT last name, first name, address FROM customer WHERE address like '%ours%' order by last name;

— 8 (3 rows returned)
SELECT * FROM car WHERE category = 'convertible'and seats = 2;

— 9 (0 rows returned)
SELECT immat, brand, model FROM car WHERE color = 'blue';

— 10 (8 rows returned)
SELECT immat, brand, model FROM car WHERE color = 'white';

— 11 (4 rows returned)
SELECT * FROM car WHERE immat like '';

Exercise 4

4-1 Study the Car table

  • How many vehicles are there in the comic?
  • How many brands? (SELECT count(brands))
  • How many registrations? (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

  • How many vehicles are there in the comic?
  • 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;

Exercise 5

  1. Combien y-a-t-il de véhicules dans la bd
  2. Combien y-a-t-il de locations dans la bd
  3. Supprimez le véhicule immatriculé `11TR62′
  4. Quel code erreur et pourquoi ?
  5. 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

  1. Réessayer la suppression du véhicule immatriculé `11TR62′
  2. Combien y-a-t-il de véhicules dans la bd (comparez avec le résultat précédemment noté)
  3. Combien y-a-t-il de locations dans la bd (comparez avec le résultat précédemment noté)
  4. Vider la table (supprimez tous les tuples) de la table voiture
  5. Supprimer la table client (tuple et structure de la table) par un DROP TABLE
  6. Pourquoi cela ne marche pas ?
  7. 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 ?
  8. Supprimer dans le bon ordre par des DROP TABLE, les tables voiture, maintenance et propriétaire
  9. 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’;

— —————————————-

Exercise 6

Création d’un utilisateur et attribution des droits d’accès à la BD

  1. Créez un utilisateur de nom ‘superbozo’ (mot de passe ‘user’) : CREATE USER ‘superbozo’@’localhost’ IDENTIFIED BY ‘123’;
  2. Attribuez-lui tous les droits sur la bd GRANT ALL ON loueur.* TO ‘superbozo’@’localhost’;
  3. Créez un utilisateur de nom ‘bozo’ (mot de passe ‘user’)
  4. Attribuez-lui seulement le droit d’accès en lecture de la table location (SELECT)
  5. 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).
  6. Reconnectez-vous sous le nom root
  7. Révoquez pour superbozo les droits lui permettant de supprimer des tuples (DELETE) : REVOKE.
  8. Visualisez les droits de `bozo’ , de ‘superbozo’ et de l’utilisateur courant
  9. Supprimez les utilisateurs bozo et superbozo : DROP USER …
  10. 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;

To share