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 beginner

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))
  • 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

  1. How many vehicles are there in the database?
  2. How many rentals are there in the bd
  3. Delete the vehicle registered `11TR62′
  4. What error code and why?
  5. 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

  1. Retry deletion of vehicle registered `11TR62′
  2. How many vehicles are there in the database (compare with the result previously noted)
  3. How many rentals are there in the database (compare with the result previously noted)
  4. Empty table (remove all tuples) from car table
  5. Drop the customer table (tuple and table structure) using a DROP TABLE
  6. Why doesn't this work?
  7. 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?
  8. Delete in the correct order by DROP TABLE, car, maintenance and owner tables
  9. 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

  1. Create a user with name 'superbozo' (password 'user'): CREATE USER 'superbozo'@'localhost' IDENTIFIED BY '123';
  2. Give him all the rights to the comic GRANT ALL ON renter.* TO 'superbozo'@'localhost';
  3. Create a user with name 'bozo' (password 'user')
  4. Give it only read access to the location table (SELECT)
  5. Reconnect under the name bozo and try to insert a new owner (pseudo bozo, code P01, email bozo@gmail.com, city Paris, year 2017).
  6. Log in again as root
  7. Revoke for superbozo the rights allowing it to delete tuples (DELETE): REVOKE.
  8. View the rights of `bozo', 'superbozo' and the current user
  9. Remove users bozo and superbozo: DROP USER …
  10. 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;