17 Corrected exercises SQL queries - Complex systems and AI

These corrected SQL query exercises are for beginners to advanced users.

Flashback exercise on entity-association model

A real estate agency wants to manage its real estate portfolio. It wants to store all rental accommodation in its base.

To do this, it assigns an identifier to each accommodation, its address, its surface area and the rent. A home is located in a neighborhood which is assigned a number and a label. Each accommodation corresponds to a type of accommodation with fixed charges associated with it. In addition, the agency also stocks the tenants of its park. These are identified by a number, are characterized by their surnames, first names, date of birth and telephone number. Several tenants may be associated with a dwelling. On the other hand, an individual can only be referenced in one accommodation.

You will propose the entity-association model of these specifications

And you will deduce the logical model by identifying the primary and foreign keys.

Solution

association entity

Preliminary exercise to understand SQL

A small business created a relational database to store information about its employees and the departments to which they belong. The database contains the following tables (primary keys are underlined):

EMPLOYEE(employee_id, last name, first name, profession, date_hiring, salary, commission, id_department) contains the list of employees, defined by an internal system identifier, their last name (unique), their first name, their profession (executive, manager, engineer, salesperson, technician and intern), their date of hire, their salary (positive integer, an intern may not be paid), their commission (an employee may not have a commission) and the identifier of the department to which the employee belongs.

DEPARTMENT(department_id, name, director, city) contains the list of departments defined by a unique identifier, their name (sales, production, development and human resources), the identifier of the employee director of the department and the city, location of the department.

Here is the database schema in graphical form:

The database contains the following data:

Exercise 1

For each of the following queries, give its meaning in French (be careful not to paraphrase the SQL code) and indicate the result.

1) SELECT DEPARTMENT.department_id, DEPARTMENT.name

FROM DEPARTMENT

WHERE DEPARTMENT.Id_departement NOT IN (SELECT EMPLOYE.Id_departement FROM EMPLOYE);

2) SELECT E2.name AS “Director Name”, COUNT(E1.name) AS “Number of Employees”

FROM EMPLOYEE E1, EMPLOYEE E2, DEPARTMENT D

WHERE E1.Id_departement = D.Id_departement

AND E2.Id_employe = D.Director

GROUP BY E2.name

ORDER BY 1;

3) SELECT name, salary FROM EMPLOYEE

WHERE salary > ALL (SELECT salary

FROM EMPLOYEE

WHERE profession = 'Engineer');

4) SELECT E1.name AS “Employee Name”, E1.hire_date, E2.name AS “Director Name”, E2.hire_dateFROM EMPLOYEE E1, EMPLOYEE E2, DEPARTMENT DWHERE E2.Id_employe = D.DirectorAND E1.Id_departement = D.Id_departmentAND E1.Date_hiring < E2.Date_hiring;

Solution

1) Which departments have no employees?

No rows selected

2) What is the number of employees per director?

Name Director Number of Employees

—————————— ————————–

Jackson 3

Lavergne 1

McDonalds 4

Valiant 2

3) What is the salary and name of employees earning more than all engineers?

Name Salary

—————————- ————–

Furnon 6000

Babin 7000

McDonalds 10000

4) What is the name and date of hire of employees hired before their manager; also give the name and date of hiring of their director.

Name Employee Date_hired Name Director Date_hired

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

Sutton 01-04-1989 McDonald 06-05-2001

Furnon 06-11-1989 McDonald 05-06-2001

Babin 08-08-2000 McDonald 06-05-2001

Exercise 2

For each query, indicate whether (Yes or No) it gives the requested result (several queries can give the right result)

6) Give the salary and name of employees earning more than the lowest salary of engineers:.

6a SELECT DISTINCT E1.name, E1.salaryFROM EMPLOYEE E1, EMPLOYEE E2WHERE E2.Profession = 'Engineer'AND E1.salary > MIN(E2.salary);

6b SELECT name, salary FROM EMPLOYEWHERE salary > ALL (SELECT MIN(salary)FROM EMPLOYEWHERE profession = 'Engineer');

6c SELECT name, salary FROM EMPLOYEWHERE salary < ANY (SELECT MIN(salary)FROM EMPLOYE

WHERE profession = 'Engineer');

Give the names of employees with the same profession and the same director as 'Furnon':

7a SELECT name, professionFROM EMPLOYEEWHERE name <> 'Furnon'AND Id_departement IN (SELECT D2.Id_departementFROM EMPLOYEE E, DEPARTMENT D1, DEPARTMENT D2WHERE E.name = 'Furnon'AND D1.Id_departement = E.Id_departementAND D1.Directeur = D2.Directeur );

7b SELECT E.nomFROM EMPLOYEE E, DEPARTMENT DWHERE E.name <> 'Furnon' AND E.Id_departement = D.Id_departementAND (profession, director) = (SELECT profession, D1.directorFROM EMPLOYEE E1, DEPARTMENT D1, DEPARTMENT D2WHERE E1.name = 'Furnon'AND E1.Id_departement = D1.Id_departementAND D1.Directeur = D2.Directeur);

8) Give the names of employees of the 'Commercial' department hired on the same day as an employee of the 'Production' department:

8a SELECT DISTINCT E1.name FROM EMPLOYEE E1, DEPARTMENT D1, EMPLOYEE E2, DEPARTMENT D2WHERE E1.Id_departement = D1.Id_departementAND E2.Id_departement = D2. Id_departement AND D1.name = 'Commercial'AND D2.name = 'Production'AND E1.Date_hire = E2.Date_hire;

8b SELECT E.nameFROM EMPLOYEE E, DEPARTMENT DWHERE E.Id_departement = D.Id_departmentAND D.name = 'Commercial'AND E.Date_hire IN (SELECT E1.Date_hireFROM EMPLOYEE E1, DEPARTMENT D1WHERE E1. Id_departement = D1. Id_departementAND D1.name = 'Production');

8c SELECT E.nameFROM EMPLOYEE E, DEPARTMENT DWHERE E.Id_departement = D.Id_departementAND D.name = 'Commercial'AND E.Date_hire EXISTS (SELECT E1.Date_hireFROM EMPLOYEE E1, DEPARTMENT D1WHERE E1. Id_departement = D1. Id_departementAND D1.name = 'Production');

9) Give the names of employees with the maximum salary in each department:

9a SELECT nameFROM EMPLOYEWHERE (Id_department, salary) IN (SELECT Id_department, MAX(salary)FROM EMPLOYEGROUP BY Id_department);

9b SELECT nameFROM EMPLOYEE EWHERE salary = (SELECT MAX(salary)FROM EMPLOYEE E1WHERE E1. Id_departement = E. Id_departement);

9c SELECT E1.name, E1.department_id, MAX(E1.salary)

FROM EMPLOYEE E1, EMPLOYEE E2

WHERE E1. Id_department = E2. Id_department

GROUP BY E1.name, E1.id_department;

Solution

6a NO

6b YES

6c NO

7a NO

7b YES

8a YES

8b OUI

8c NO

9a YES

9b YES

9c NO

Exercise 3

11) Please give the SQL commands to display the list of all departments with the last name, first name, salary and identifier of the employees assigned to them. Please note: a department may not have any employee(s).

12) Please give the SQL commands to display the list of jobs with the lowest average salary; also give their average salary.

13) Please give the SQL commands to display the employees of the 'Production' department whose salary is less than 5000 $ and the employees of the 'Commercial' department whose salary exceeds the average salary.

Solution

11)

SELECT D.last name, E.employee_id, E.last name, E.first name, E.salary

FROM EMPLOYEE E, DEPARTMENT D

WHERE D.Id_departement = E.Id_departement(+)

ORDER BY D.surname, E.surname, E.firstname;

12)

SELECT profession, AVG(salary) FROM EMPLOYEGROUP BY professionHAVING AVG(salary) = (SELECT MIN(AVG(salary)) FROM EMPLOYE

GROUP BY profession);

13)

SELECT E1.EmployeeId, E1.Name, E1.Salary

FROM EMPLOYEE E1, DEPARTMENT D1

WHERE D1.Id_departement = E1.Id_departement

AND D1.Name = 'production'

AND E1.Salary < 5000

UNION

SELECT E2.EmployeeId, E2.Name, E2.Salary

FROM EMPLOYEE E2, DEPARTMENT D2

WHERE D2.Id_departement = E2.Id_departement

AND D2.Name = 'commercial'

AND E2.Salary > (SELECT AVG(E3.Salary) FROM EMPLOYEE E3)

ORDER BY 1, 2;

Exercise 4

You will give the SQL commands for creating the database tables, including all of the stated constraints that can be specified in the table creation command. Note that deleting a department must result in deleting all of its employees.

Solution

CREATE DEPARTMENT TABLE (

Id_department NUMERIC (3) PRIMARY KEY,

Name VARCHAR2 (30) CHECK (Name IN ('Commercial', 'Development', 'Production', 'Human Resources')),

Director NUMERIC (6) NOT NULL UNIQUE,

City VARCHAR2 (20) NOT NULL

);

CREATE EMPLOYEE TABLE (

Id_employee NUMERIC (6) PRIMARY KEY,

Name VARCHAR2 (30) NOT NULL UNIQUE,

First name VARCHAR2 (30) NOT NULL,

Profession VARCHAR2 (30) NOT NULL CHECK (Profession IN ('Executive', 'Manager', 'Engineer', 'Salesperson', 'Technician', 'Intern')),

Hire_date DATE NOT NULL,

Salary INT NOT NULL CHECK (Salary >= 0),

Commission INT DEFAULT 0,

Id_departement NUMERIC (3) CONSTRAINT fk_employe_departement REFERENCES DEPARTMENT(Id_department) ON DELETE CASCADE

);

BDD Cars.txt

The database used models a car rental activity between individuals: cars

The information kept in the database is information concerning the cars, the owners of these cars, the customers and the rentals made.

You take the following relational diagram:

CAR (immat, model, brand, category, color, places, purchaseA, counter, priceJ, #codeP )

OWNER (codeP, nickname, email, city, yearI)

CUSTOMER (codeC, last name, first name, age, license, address, city)

RENTAL (#CodeC, #immat, year, month,numLoc, km, duration, cityD, cityA, dateD, dateF)

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

Exercise 1

What brand is the T550 model vehicle?
What are the nicknames of the owners of Paris?
In which brands are vehicles in the `luxury' category found? What should I use to avoid duplicates?
What are the registrations for 4-seater convertibles?
How many different categories of cars are there?
What are the different car categories?
What Peugeot models are available for rental?
List (pseudo) of owners with a null email address
What is the mileage of vehicle `75AZ92′
Which customers (name only) live in `Nantes'?
Was using DISTINCT a good option? and why ?

Solution

select brand from car where model = 'T550'; —Ferrari
select nickname from owner where city = 'Paris'; — (4 rows returned)
select brand from car where category = 'Luxury'; — (3 rows returned, 1 with distinct)
select immat from car where category = 'convertible' and places = 4; — (1 row returned)
select count(distinct category) from car; — (7)
select distinct category from car; — (7 rows returned)
select model from car where brand = 'Peugeot'; — (5 rows returned)
select nickname from owner where email is null; — (2 rows returned)
select counter from car where immat =’75AZ92′; — 17560
select name from client where city ='Nantes';
Warning DISTINCT erases homonyms

Exercise 2

List of vehicles (immat) by year of purchase (from newest to oldest).
Check by adding the year of purchase to the list
List of brands and models ordered by brand and model.
List of cars with at least 4 seats (make, model, immat, seats) in order of decreasing number of seats.
List of owners' cities (without duplicates and in alphabetical order).
List of rentals (list of vehicle registrations) classified in descending order of km traveled

Solution

select immat from car order by purchaseA; — (20 rows returned)
select immat, purchaseA from car order by purchaseA; — (20 rows returned)

select distinct brand, model from car order by brand, model; — (15 rows returned)
select brand, model, immat, places from car where places >= 4 order by places DESC; — (10 rows returned)
select distinct city from owner order by city ASC; — (6 rows returned)
select immat, km from location order by km DESC; — (133 rows returned)

Exercise 3

1) List of names of customers in Lyon?

the same without the duplicates.

How many Lyon customers are there? ( count(*) , count(name) , count(distinct name))

note the results and explain the differences between the 3 results obtained?

2) Same questions but with first names

How many different couples (last name, first name) are there in the database (all cities combined?

Solution

— 1
select name from client where city ='Lyon';
select distinct name from client where city ='Lyon';
select count(name) from client where city ='Lyon';
select count(*) from client where city ='Lyon';
select count(distinct name) from client where city ='Lyon';
— 2
select last name, first name from client where city = 'Lyon';
select count(*) from client where city ='Lyon';
select count(first name) from client where city ='Lyon';
select count(distinct name) from client where city ='Lyon';
select last name, first name from client where first name is NULL and city ='Lyon';

Exercise 4

How many red or blue convertibles are there?
How many cars are there in the category (family with less than 50,000 km, or utility vehicle), black or white?
List (immat, brand, model) of Peugeot and Citroen brand models displayed by brand and model
List of clients from Paris, over 50 years old in alphabetical order
List (immat) of white vehicles except convertibles
How many vehicles in the luxury and premium categories are there before 2012 (2012 included)
List of rentals (rental number, with city, year, month) that started and ended in the same city in chronological order (most recent first)
List of cars dating between 2010 and 2012 (2010 and 2012 inclusive)
List of Renault, Peugeot or Citroen brand cars (use IN).
How many owners live neither in Paris, nor Lyon, nor Nantes?
List of clients who rented without a license

1 List of customers (codeC) who have made rentals.

2 List of customers (codeC) who have a permit number

3 List of customers (codeC) who do not have a license number

4 List of customers (codeC) who rented without a license number

12 List (first and last name) of customers living in the same city as an owner

13 List (immat) of cars that have been rented (at least once)

14 List (immat) of cars that have never been rented

Solution

— 1

select count(*) from car where category= “convertible” and (color = 'red' or color = 'blue'); — 1

— 2

select distinct category from car; — (7 rows returned)

select count(*) from car where ((category ='family' and counter <= 50000) or (category ='utility')) and (color = 'black' or color ='white'); — 4

— 3

select distinct brand from car order by brand;– (6 rows returned)

select brand, model, immat from car where brand = 'Peugeot' or 'Citroen' order by brand, model; — (8 rows returned)

— 4

select surname, first name, age from client where city ='Paris' and age > 50 order by surname; — (1 rows returned)

— 5

select immat from car where color = 'white' and category <> 'convertible'; — (8 rows returned)

— 6

select count(*) from car where (categorie = 'luxury' or category = 'premium') and purchaseA <= 2012; — (6 rows returned)

— 7

select numLoc, cityD, year, month from location where cityD = cityA order by year DESC , month DESC;– (67 rows returned)

— 8-9-10

select immat from car where purchaseA between 2010 and 2012; — (8 rows returned)

select immat from car where brand in ('Renault', 'Peugeot', 'Citroen'); — (16 rows returned)

select nickname from owner where city not in ('Paris', 'Lyon', 'Nantes'); — (5 rows returned)

— 11

select codeC from location;

select codeC from client where permit is not null;

select codeC from client where permit is null;

select distinct codeC , allowed from client

where codeC in (select codeC from location)

and permit is null ; — (2 rows returned)

— other version

select distinct codeC , allowed from client

where exists (select codeC from location)

and permit is null ;

— other version but without the license number

select distinct codeC from location

where codeC in (select codeC from client where permit is null);

— 12

select last name, first name, city from client

where city in (select city from owner); — (18 rows returned)

— 13

select distinct immat from location; — (20 rows returned)

— 14

select distinct immat from car

where immat not in (select distinct immat

from location); — (1 rows returned)

select distinct immat from car where not exists (

select * from location

where location.immat = car.immat); — (1 rows returned)

Exercise 5

What is the nickname of the owner of the car '56AA46′?
How many rentals for Peugeot 205?
List (make, model, immat, rental number) of cars rented in 4/2015.
List (make, model, immat, rental number) of cars rented in the year of their purchase.
List of Peugeot vehicle immats
List of Peugeot vehicle immats and corresponding rental information by year, month and immat
The list of customers who have rented a Ferrari vehicle?
Which Peugeot models have been rented?

Solution

— 1

select nickname

from owner p, car v — Jules

where immat = '56AA46′

and p.codeP = v.codeP ;

— 2

select count(*)

from location l, car v — 11

where l.immat = v.immat

and v.marque= 'Peugeot'

and v.model = '205';

— 3

select v.brand, v.model, v.immat, l.numLoc– (6 rows returned)

from car v, rental l

where v.immat=l.immat

and l.year = '2015'

and l.month = '4';

— 4

select v.brand, v.model, v.immat, l.numLoc– (11 rows returned)

from car v, rental l

where v.immat = l.immat

and l.year = v.purchaseA ;

— 5

select distinct immat — (5 rows returned)

from car

where brand ='Peugeot';

— 6

select l.* — (30 rows returned)

from car v, rental l

where v.immat = l.immat

and brand = 'Peugeot'

order by l.year, l.month, l.immat;

— 7

Select c.surname, c.first name, v.brand, v.model — (13 rows returned)

from location l, car v, customer c

where c.codeC = l.codeC

and l.immat = v.immat

and v.brand = 'Ferrari';

Exercise 6

List of brands with more than 3 cars available for rental
List of immat of vehicles having covered more than 800 km in rental
List of car models that have been rented at least 3 times

Solution

— 1: list of brands with more than 3 (>) cars available for rental
select mark — (2 rows returned)
from car
group by brand
having count(*) > 3;

— 2: list of immats of cars having covered more than 800km in rental
select immat — (15 rows returned)
from location
group by immat
having sum(km) > 800;

— 3: car models that have been rented at least 3 times (with number of rentals)
— displayed in alphabetical order by model
select v.model, count(l.numloc) — (15 rows returned)
from location l JOIN car v ON l.immat = v.immat
group by v.modele
having count(l.numloc) >= 3
order by v.model;

Exercise 7

Kms traveled in rental by Alain Delon, car (immat) per car
Brands and average km traveled in rental (by brand)
What is the oldest car? (with MIN)
Which car has more km on the odometer than all Peugeots? (with ALL)
Which car/s have a higher daily price than a Ferrari?
Display the list of cars with a km odometer higher than the general average km odometer.
List of cars with a km odometer higher than the average km odometer for their model?

Solution

— 1: KMs carried out for rental by Alain Delon car (immat) per car

select immat, sum(km) — (4 rows returned)

from client c JOIN location l on c.codeC = l.codeC

where name = 'Delon'

group by immat;

— 2a: brands and average km traveled in rental by brand

select v.marque, avg(l.km) — (6 rows returned)

from location l, car v

where l.immat = v.immat

group by v.marque;

— 2b: same in descending order of averages

— use of AS

select v.marque, avg(l.km) as average — (6 rows returned)

from location l, car v

where l.immat = v.immat

group by v.marque

order by average DESC;

— 3: what is the oldest car

select immat — (3 rows returned)

from car

where purchaseA = (select min(purchaseA)

from car);

— 4: Which car/s have more km on the odometer than all Peugeots (ALL)

select immat — (7 rows returned)

from car

where counter >= ALL (select counter

from car

where brand = 'Peugeot');

— 5: Which cars have a higher daily price than a Ferrari

select immat — (20 rows returned)

from car

where priceJ >= ANY (select priceJ

from car

where brand ='Ferrari');

— 6: list of cars with a km counter higher than the general average km counter

select immat– (6 rows returned)

from car

where counter > (select avg(counter)

from car);

— 7: list of cars with a km counter higher than the average km counter for their model

select v1.immat — (5 rows returned)

from car v1

where v1.counter > (select avg(v2.counter)

from car v2

where v2.model = v1.model);

Exercise 8

List of car models rented at least 3 times (with number of rentals)?
Which car has traveled the most km in rental?
Which customer(s) made the most rentals?
Which customer has rented the most different vehicles?
What is the total rental km carried out for the P75 owner?
What is the total rental km carried out for the owner P75 car per car?

the same but taking into account that rentals of more than 300 km.

the same but for vehicles having traveled a total of more than 300 km in rental.

For verification, list of rentals made by P75 vehicles.

Solution

— 1: car models that have been rented at least 3 times (with number of rentals)

— displayed in alphabetical order by model

select v.model , count(l.numloc) — (15 rows returned)

from location l, car v

where l.immat = v.immat

group by v.modele

having count(l.numloc) >= 3

order by v.model;

— 2: which car has traveled the most km in rental

select immat, sum(km) — 11RS75, 3697km

from location

group by immat

having sum(km) >= ALL(select sum(km)

from location

group by immat );

— 3: the customer who has made the most rentals

select C.surname, C.firstname — (1 rows returned)

from client c JOIN location l ON c.codeC = l.codeC

group by C.nom, C.prenom

having count(*)

>= ALL (select count(*)

from location lo

group by lo.codeC);

— 4: the customer who has rented the most different vehicles

select C.surname, C.firstname — (2 rows returned)

from client c, location l

where c.codeC = l.codeC

group by C.nom, C.prenom

having count(distinct immat)

>= ALL (select count(distinct immat)

from location l2

group by l2.codeC);

— ————————

— 5: total rental km carried out for owner's cars P75

select sum(l.km) — 7192

from location l, car v

where l.immat = v.immat and v.codeP =’P75′;

— 6a: total rental km carried out for the owner P75 car per car

select l.immat, sum(l.km) — (4 rows returned)

from location l, car v

where l.immat = v.immat and v.codeP =’P75′

group by l.immat;

— 6b: same as (b) but only taking into account rentals of more than 300 km

select l.immat, sum(l.km)– (3 rows returned)

from location l, car v

where l.immat = v.immat and v.codeP =’P75′

and l.km > 300

group by l.immat;

— 6c same as (b) but only for vehicles having traveled a total of more than 300 km in rental

select l.immat, sum(l.km) — (4 rows returned)

from location l, car v

where l.immat = v.immat and v.codeP =’P75′

group by l.immat

having sum(l.km) > 300;

— 6d: for verification, list of rentals made by the vehicles of P75

select l.immat, l.km — (25 rows returned)

from location l, car v

where l.immat = v.immat and v.codeP =’P75′

order by l.immat, l.km DESC;

Exercise 9

The query in question is: which customers (names) use the same license number?

To guide you a series of progressive queries.

For each permit number, display the number of times it is used.
List of license numbers that have been used more than once?
List of names of customers with a license number used more than once.
List of names of customers whose license number is equal to the license number of another customer (with IN)
Alternate writing for (4) using explicit join equalities (SQL1).
If there are duplicates left, how do I remove them?

Solution

— 1: number of times a permit number has been used

select c1.permis, count(*) — (8 rows returned)

from client c1

group by c1.permis

order by c1.permit;

— 2: number of permits used more than once

select c1.permis — (5 rows returned)

from client c1

group by c1.permis

having count(*)> 1

order by c1.permit;

— 3: customers with a license number (also display the license number) used more than once

select c2.name — (14 rows returned)

from client c2

where c2.permit IN ( select c1.permit

from client c1

group by c1.permis

having count(*)> 1 )

order by c2.name;

— 4: list of names of customers using the license number (show license number) of another customer

select c1.name, c1.permit — (14 rows returned)

from client c1

where c1.permit IN ( select c2.permit

from client c2

where c2.permit = c1.permit

and c2.name != c1.name)

order by c1.name;

— 5: other writing with explicit join equalities

— (self-join is now better visible)

select c1.name, c2.name, c1.permit — (38 rows returned)

from client c1, client c2

where c2.permit = c1.permit and c2.name != c1.name

order by c1.name;

— 6: other writing with explicit join equalities

— (self-join is now more visible)

select c1.name, c2.name, c1.permit — (19 rows returned)

from client c1, client c2

where c2.permit = c1.permit and c2.name > c1.name

order by c1.name;

Exercise 10

The query targeted is: which customers (the names) rented all the cars?

(a) List of cars that have not been rented; to write using NOT EXISTS. (rewording: cars for which there is no rental with this registration number).

(b) List of names of customers for whom there are vehicles that he (the customer) has not rented.

(rewording: list of names of customers for whom there is no vehicle that they have not rented.

Solution

— The query in question is: which customers (the names) rented all the cars?
— (a) List of cars which have not been rented; to write using NOT EXISTS.

SELECT car.immat — 1 row
from car
where not exists (select * from location where location.immat=car.immat);

— (b) List of names of customers for whom there are vehicles that he (the customer) has not rented.
SELECT DISTINCT last name, first name
from customer, car
where not exists (select codeC from location where location.immat=voiture.immat and client.codeC=location.codeC);

— (c) List of names of customers who have rented all vehicles in the fleet.
— (rewording: list of names of customers for whom there is no vehicle that they have not rented.

select customer.name
from location, customer
where location.codeC= client.codeC
group by client.codeC
having count(distinct immat)>= (select (count(distinct immat)) from car);

BDD Vins.txt

We want to use the following relational diagram to manage a wine cellar. The database representing this cellar has the following relational schema: wines

WINE (numV, vintage, year, degree)

PRODUCER ( numP, last name, first name, region)

HARVEST ( nprod#, nvin#, quantity)

And the following csvs: wine, harvest, producer.

Using the following statement, you translate each line of the .csv file into a tuple in the corresponding table

LOAD DATA INFILE 'absolute path/XXX.csv' INTO TABLE XXX FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n';

LOAD DATA INFILE 'C:/XXX.csv' INTO TABLE XXX FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n'; if the XXX.csv file is under the root

A wine is characterized by a whole number, a vintage, a year of production and a degree. All wines are represented by the VIN relationship. The key to the VIN relationship is the numV attribute.

A producer is characterized by an integer number, a last name, a first name and a region. All producers are represented by the PRODUCER relationship. The key to the PRODUCER relationship is the numP attribute. A producer produces one or more wines. Conversely, a wine is produced by one or more producers (possibly none!).

All production is represented by the HARVEST relationship. A tuple of the relation RECOLTE represents a particular production of a wine of number nvin by a producer of number nprod in a certain quantity. The key to the HARVEST relationship is the attribute group (nvin, nprod).

Exercise 1

1- Create the database from the files provided.

By default, files are only readable from the directory specified in the secure_file_priv variable.

SHOW VARIABLES LIKE “secure_file_priv”;

Drop your files in this directory and load them to populate your tables.

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/producer.csv'

INTO TABLE producer

FIELDS

TERMINATED BY ';'

LINES

STARTING BY »

TERMINATED BY '\r\n';

2- Display the information contained in the VIN relationship.

3- Give the list of producers who do not have a first name.

4- Give the list of distinct regions of wine production.

5- Give the list of wines from 1980.

6- Give the list of names and first names of wine producers not belonging to the following regions: Corsica, Beaujolais, Burgundy and Rhône.

7- What is the list of vintages harvested in 1979? Display the vintage, producer number and quantity produced.

8- What are the names of the Cru Etoile producers, their regions and the quantity of wines harvested?

9- What is the number of harvests?

10-How many wine producers are there in the Savoie and Jura region?

11-How many wine producers are there who have harvested at least one wine in the Savoie and Jura region?

12-What are the quantities of wine produced by region. Give the list ordered by decreasing quantity.

13- What is the quantity of wine produced of degree >12 per wine number?

14- What is the highest degree of vintage or vintages?

15- Give the ordered list of wines.

16- Give the ordered list of harvested wines.

17- So what is the unharvested raw material?

18- Give the ordered list of vintages and the quantity per vintage?

19- What is the average strength of the wines?

20- What are the vintages (ordered by degree and year) of higher degree than the average degree of the vintages?

Solution

select * from wine; # 101 rows
select * from producer; # 200 rows
select * from harvest; # 200 rows

#Question 2 – 7
select distinct name from producer where first name is null; # 0 rows
select distinct name from producer where first name = » or first name is null; # 12 rows

select distinct region from producer; # 11 rows
select degree, vintage from wine where year=1980 order by degree desc; # 18 rows
select name,first name,region from producer where region not in ('Corsica', 'Beaujolais', 'Burgundy', 'Rhone') order by name asc; # 164 rows
select nprod, vintage, quantity from wine v, harvest r where year=1979 and v.numV=r.nprod order by nprod; # 5 rows

#Question 8- 13
select cru,name,region,quantity FROM WINE v,PRODUCER p,HARVEST r WHERE cru='Star' AND p.numP=v.numV AND p.numP=r.nprod; #1 row
select COUNT(*) FROM HARVEST; # –> 665
select COUNT(*) FROM PRODUCER WHERE region in ('Savoie','Jura'); # –> 57
select COUNT(*), quantity FROM PRODUCER p,HARVEST r WHERE region in('Savoie','Jura') AND p.numP=r.nprod AND quantity>0 GROUP BY quantity; # 22 rows
select SUM(quantity) FROM HARVEST R, PRODUCER P WHERE R.nprod=P.numP GROUP BY region ORDER BY quantity DESC; # 10 rows
select SUM(quantity) from harvest r, wine v where v.numV=r.nvin and v.degre>12 group by nvin; # 14 rows

# Question 14-20
select raw FROM VIN WHERE degree in (select MAX(degree) FROM VIN); # –> Holy Love
select cru FROM WINE ORDER BY cru ASC; #101 rows
select distinct cru from vin v , harvest r where v.numV=r.nvin order by cru; # 61 rows
select cru from vin v where v.cru not in (select cru from vin v, harvest r where v.numV=r.nvin); # –> Saint Veran
select cru, SUM(quantity) from vin v ,harvest r where v.numV=r.nvin group by cru order by cru; # 61 rows
select avg(degree) from vin v; # –> 11.35
select cru,degree,year from vin where degree > (select avg(degree) from vin v) order by cru,degree,year; # 48 rows

Exercise 2

1 – Give all the wines whose vintage begins with the letter 'S'

2 – Give all distinct wines whose production date is between 1980 and 1981

3 – Select all productions from producer number 606

4 – Give by producer number, the total quantity of wine produced. Check with question 2

5 – Give by producer number, the total quantity of wine produced if it is greater than 400 and all ordered in ascending order of the quantities calculated using an alias on the sum

6 – Look for the oldest Pommard vintage. Post it with the comment: “the one I prefer”

7 – What are the numbers of the producers who harvested the most different wines?

8 – Add a column to the Producer table corresponding to his date of birth,

Modify the following existing producers in the database by assigning them their date of birth

birthdate = '1960-12-11′ name='Six';

birthdate = '1962-12-11′ name='Lasnier';

birthdate = '1970-12-11′ name='Moniot';

birthdate = '1970-12-11′ name='Boxler';

birthdate = '1980-12-11′ name='Jayer';

birthdate = '1961-05-10′ name='Grivot';

birthdate = '1980-12-11′ name='Tortochot';

9 – Select the producers for which the date of birth is not zero

10 – Calculate Grivot’s age

11 – Calculate the number of producers by age

Solution

1- select cru from vin where cru like 's%';

2- select distinct cru, annee from vin where annee between 1980 and 1981;

3- select * from harvest where nprod = 606;

4- select nprod, sum(qte) from harvest group by
nprod;

5- select nprod, sum(qte) as sum from harvest group by
nprod having sum(qte) > 400 order by sum;

6- select cru, annee from vin where cru='Pommard';

select concat (cru, 'the one I prefer'), year
from vin where cru = 'Pommard' and annee >= (select max(annee) from vin where cru='Pommard');

7- select nprod, name from harvest, producer where harvest.nprod = producer.num
group by nprod
having count(distinct nvin) >= All
(select count(distinct nvin) as nbvindifferent from harvest group by nprod
);

select nprod, name from harvest, producer where harvest.nprod = producer.num
group by nprod
having count(distinct nvin) >=
(select max(nbvindifferent) from
(select count(distinct nvin) as nbvindifferent from harvest group by nprod) P);

select max(nbvindifferent) from
(select count(distinct nvin) as nbvindifferent from harvest group by nprod) P;

select nprod, count(distinct nvin) as nbvindifferent
from harvest group by nprod order by nbvindifferent desc;

select * from harvest where nprod=355;

8- alter table producer add column birth datetime;

9- select * from producer where num < 5;
update producer set birth = '1990/10/10' where num = 1;

10- SELECT name, Year(current_date()) – YEAR(producer.birth) as AGE FROM producer
where birth is not null;

11- select count(*) as number, Year(current_date()) – year(producer.birth)as age
from producer where birth is not null group by age;

Exercise 3

Resume the VINs database and answer the following questions:

1 – What are the names of the producers of wine No. 5;

2 – Who are the producers who produced the 'Pommard' wine?

3 – What are the vintages harvested?

4 – What is the list of vintages harvested in 1979? Display the vintage, producer number and quantity produced.

5- What are the names of the Cru Etoile producers, their regions and the quantity of

wines harvested?

6 – What is the number of producers of vintages harvested in Savoie and the Jura?

7 – So what is the unharvested raw material?

8 – Give the ordered list of vintages and the quantity per vintage?

9 – Give the names of the producers who harvested the most different wines

10 – Give the list of names of producers who did not harvest

11 – What are the names of people with the same first name

Solution

1- select name from producer, harvest where producer.num = harvest.nprod and nvin=5;

2- select last name, first name from producer, harvest, wine where producer.num = harvest.nprod and harvest.nvin=vin.num and cru = 'Pommard';

3- select distinct cru from harvest, vin where harvest.nvin= vin.num;

4- select

p.num, p.nom, raw, qte

from

wine v,

to harvest,

producer p

where

date = 1979 and v.num = r.nvin

and p.num = r.nprod

order by nprod;

5-select

name, vintage, region, qty

from

to harvest,

producer p,

wine v

where

p.num = r.nprod and v.num = r.nvin

and v.cru = 'Star';

6- select

count(*)

from

producer p,

to harvest

where

region in ('Savoie', 'Jura')

and p.num = r.nprod;

select

count(*)

from

producer p,

to harvest

where

p.num = r.nprod and (p.region='Jura' or p.region='Savoie');

7- select

believed

from

wine

where

vin.num not in (select

harvest.nvin

from

harvest);

8- select

raw, sum(qty)

from

harvest,

wine

where

vin.num = harvest.nvin

group by nvin;

select

raw, sum(qty)

from

wine v

LEFT OUTER JOIN

harvest r ON v.num = r.nvin

group by cru

order by raw;

/*requests 9 and 10 see above*/

11- select P1.name, P2.name from Producer P1, Producer P2

where P1.firstname = P2.firstname;

Corrected SQL query exercises

Flashback exercise on entity-association model

Preliminary exercise to understand SQL

Exercise 1

Exercise 2

Exercise 3

Exercise 4

BDD Cars.txt

Exercise 1

Exercise 2

Exercise 3

Exercise 4

Exercise 5

Exercise 6

Exercise 7

Exercise 8

Exercise 9

Exercise 10

BDD Vins.txt

Exercise 1

Exercise 2

Exercise 3