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.
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.
Exercise 3
- Display the list (model, color, registration) of Peugeot vehicles.
- View vehicle specifications 56AA46. (use *)
- Where does Depardieu live?
- Which customers have 'Jean' for first name?
- List of customers (last name, first name) with `jean' for first name, in alphabetical order.
- List of customers with a compound first name starting with jean (jean-pierre, jean-jacques etc.)
- Who (last name, first name) lives in a street whose name contains the word 'bear'?
- What are 4-seater convertibles?
- List (registration numbers) of blue colored cars.
- List (registration numbers) of white-colored cars.
- 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))
- Make the same queries adding distinct and explain the difference
4-2. Insert tuples into a table
car (10AK37, Peugeot, 205, convertible, purple, 2 seats, 1980, meter: 65,000, €40/day, owner: P99)
car (11BF37, Peugeot, 206, convertible, purple, 2 seats, 1980, meter: 21,000, €32/day, owner: P99)
car (15AK37, Peugeot, 205, convertible, purple, 2 seats, 1985, meter: 27,000, €30/day, owner: P99)
- Check the presence of these cars in the relevant table
car (63GH94, Megane, Renault, sedan, pink, 4 seats, 2012, meter: 750, 40€/day, owner: P99)
car (87AZ92, Clio, Renault, city car, green, 4 seats, 1999, meter: 61200, €40/day, owner: P99)
- If problem => identify the problem, can we correct and redo the insertion?
4-3. Miscellaneous
- How many vehicles are there in the comic?
- How many purple vehicles are there?
- Change color of vehicle registration 11BF37 to white
- Remove the purple vehicles, how many vehicles are left in the comic?
- Delete the car whose code P='P89', explain
4.4 Insert columns in a class
- Add two new columns to the rental table intended to receive users' opinions on the rental made:
- rating (from 0 to 5)
- appreciation (text limited to 120 characters) and
Check the location table with the DESC command
— 1
SELECT count(model) FROM car;
SELECT count(distinct model) FROM car;
SELECT count(immat) FROM car;
SELECT count(distinct immat) FROM car;
— 2
insert into car (immat, model, brand, category, color, seats, purchaseA, counter, priceJ, codeP)
values ('10AK37′, '205','Peugeot', 'cabriolet', 'purple',2,1980,65000,40,'P99′);
insert into car values ('11BF37′, '206', 'Peugeot', 'cabriolet', 'purple',2,1980,21000,27,'P99′);
insert into car values ('15AK37′, '205', 'Peugeot', 'cabriolet', 'purple',2,1980,27000,32,'P99′);
SELECT count(*) FROM car;
insert into car (immat, model, brand, category, color, seats, purchaseA, counter, priceJ, codeP)
values ('63GH94′, 'megane','Renault','sedan','rouge',4,'2012′,750,40,'P99′);
insert into car (immat, model, brand, category, color, seats, purchaseA, counter, priceJ, codeP)
values ('87AZ93′, 'Clio','Renault', 'citadine','vert',4,'1999′,61200,30,'P99′);
— ———————
— 3
SELECT count(*) FROM car;
SELECT * FROM car WHERE color = 'purple';
SELECT * FROM car WHERE immat = '11BF37′;
update car set color = 'white' WHERE immat = '11BF37′;
SELECT count(*) FROM car WHERE color = 'purple';
SELECT count(*) FROM car;
delete FROM car WHERE color = 'purple';
SELECT count(*) FROM car;
SELECT count(*) FROM car WHERE codeP = 'P89';
delete FROM car WHERE codeP = 'P89';
SELECT count(*) FROM car WHERE codeP = 'P89';
— cannot delete P89 is referenced as a foreign key in another tuple
— —————————————-
— to modify the safe mode parameter (if there is a problem)
— —————————————-
— 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
- How many vehicles are there in the database?
- How many rentals are there in the bd
- Delete the vehicle registered `11TR62′
- What error code and why?
- In which table should delete on cascade be activated?
Two ALTER TABLEs will be necessary:
one for DROP FOREIGN KEY
then one for ADD CONSTRAINT … FOREIGN KEY
Attention : the name chosen to designate the Foreign Key constraint must be unique, so use names like…VariableNameTableName
- Retry deletion of vehicle registered `11TR62′
- How many vehicles are there in the database (compare with the result previously noted)
- How many rentals are there in the database (compare with the result previously noted)
- Empty table (remove all tuples) from car table
- Drop the customer table (tuple and table structure) using a DROP TABLE
- Why doesn't this work?
- Delete the location table (tuple and table structure) with a DROP TABLE Then delete the customer table. Why is the deletion of rental possible then customer and not customer before rental?
- Delete in the correct order by DROP TABLE, car, maintenance and owner tables
- Finally regenerate the entire DB to the initial state for the rest of the tutorial.
Recreate the tables by running the table creation scripts;
Rerun the table population script
Then perform a bulk insert of a data file into the database (more precisely into the location table)
LOAD DATA LOCAL INFILE '/temp/locationComplements.csv' 2
INTO TABLE `location`
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n';
to modify the corresponding system variable:
SHOW GLOBAL VARIABLE LIKE 'local_infile';
SET GLOBAL LOCAL_INFILE = 'ON'
— 1,2,3
SELECT count(*) FROM car;
SELECT count(*) FROM location;
delete FROM car 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 `car` (`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 car (immat)
ON DELETE CASCADE ON UPDATE NO ACTION;
— 6,7,8
delete FROM car WHERE immat = '11TR62′;
SELECT count(*) FROM car;
SELECT count(*) FROM location;
— 9,10,11,12
delete FROM car;
drop customer table;
#– Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails
drop table rental;
drop customer table;
— 13
drop table maintenance;
drop table car;
drop table owner;
— ———————
# LOAD DATA FILE
load data local infile '/temp/locationComplements.csv'
into table location
fields terminated by ';'
lines terminated by '\n';
— ———————
— —————————————-
— to allow massive data loading
— —————————————-
— show global variables like 'local\infile';
— set global local\infile = 'ON';
— —————————————-
Exercise 6
Creating a user and assigning access rights to the database
- Create a user with name 'superbozo' (password 'user'): CREATE USER 'superbozo'@'localhost' IDENTIFIED BY '123';
- Give him all the rights to the comic GRANT ALL ON renter.* TO 'superbozo'@'localhost';
- Create a user with name 'bozo' (password 'user')
- Give it only read access to the location table (SELECT)
- Reconnect under the name bozo and try to insert a new owner (pseudo bozo, code P01, email bozo@gmail.com, city Paris, year 2017).
- Log in again as root
- Revoke for superbozo the rights allowing it to delete tuples (DELETE): REVOKE.
- View the rights of `bozo', 'superbozo' and the current user
- Remove users bozo and superbozo: DROP USER …
- View the list of users (from the mysql.user table)
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;
