17 Corrected exercises SQL queries

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

requêtes SQL

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.

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:

association entity

The database contains the following data:

association entity

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;

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;

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.

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.

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, annee, mois,numLoc, km, duree, villeD, villeA, dateD, dateF)

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

Exercise 1

  1. De quelle marque est le véhicule de modèle T550 ?
  2. Quels sont les pseudos des propriétaires de Paris ?
  3. Dans quelles marques trouve-t-on des véhicules en catégorie `luxe’ ? Que faut-il utiliser pour éviter les doublons ?
  4. Quelles sont les immatriculations des cabriolets 4 places ?
  5. Combien y-a-t-il de catégories de voitures différentes ?
  6. Quelles sont les catégories de voitures différentes ?
  7. Quels sont les modèles de Peugeot proposés en location ?
  8. Liste (pseudo) des propriétaires ayant une adresse mail null 
  9. Quel est le kilométrage du véhicule `75AZ92′
  10. Quels clients (le nom seulement) habitent à `Nantes’ ?
  11. L’utilisation de DISTINCT était-elle une bonne option ? et pourquoi ?

select marque from voiture where modele = ‘T550’; — Ferrari
select pseudo from proprietaire where ville = ‘Paris’; — (4 rows returned)
select marque from voiture where categorie = ‘Luxe’; — (3 rows returned, 1 avec distinct)
select immat from voiture where categorie = ‘cabriolet’ and places = 4; — (1 row returned)
select count(distinct categorie) from voiture; — (7)
select distinct categorie from voiture; — (7 rows returned)
select modele from voiture where marque = ‘Peugeot’; — (5 rows returned)
select pseudo from proprietaire where email is null; — (2 rows returned)
select compteur from voiture where immat =’75AZ92′; — 17560
select nom from client where ville =’Nantes’;
Attention DISTINCT efface les homonymes

Exercise 2

  1. Liste des véhicules (immat) par année d’achat (du plus récent au plus ancien).
  2. Vérifiez en ajoutant à la liste l’année d’achat
  3. Liste des marques et modèles ordonnée par marque et modèle.
  4. Liste des voitures d’au moins 4 places (marque,modele,immat,places) par ordre de nombre de places décroissant.
  5. Liste des villes des propriétaires (sans doublons et dans l’ordre alphabétique).
  6. Liste des locations (liste des immatriculations des véhicules) classées par ordre décroissant de km effectués

select immat from voiture order by achatA; — (20 rows returned)
select immat, achatA from voiture order by achatA; — (20 rows returned)

select distinct marque, modele from voiture order by marque, modele; — (15 rows returned)
select marque, modele, immat, places from voiture where places >= 4 order by places DESC; — (10 rows returned)
select distinct ville from proprietaire order by ville ASC; — (6 rows returned)
select immat, km from location order by km DESC; — (133 rows returned)

Exercise 3

1) Liste des noms des clients à Lyon ?

idem sans les doublons.

Combien y-a-t-il de clients lyonnais ? ( count(*) , count(nom) , count(distinct nom))

notez les résultats et expliquez les différences entre les 3 résultats obtenus ?

2) Mêmes questions mais avec les prénoms

Combien y-a-t-il de couples différents (nom,prenom) dans la base de données (toutes villes confondues ?

— 1
select nom from client where ville =’Lyon’;
select distinct nom from client where ville =’Lyon’;
select count(nom) from client where ville =’Lyon’;
select count(*) from client where ville =’Lyon’;
select count(distinct nom) from client where ville =’Lyon’;
— 2
select nom, prenom from client where ville = ‘Lyon’;
select count(*) from client where ville =’Lyon’;
select count(prenom) from client where ville =’Lyon’;
select count(distinct nom) from client where ville =’Lyon’;
select nom, prenom from client where prenom is NULL and ville =’Lyon’ ;

Exercise 4

  1. Combien y-a-t-il de cabriolets rouges ou bleus ?
  2. Combien y-a-t-il de voitures dans la catégorie (familiale de moins de 50 000 km, ou utilitaire), de couleur noir ou blanc ?
  3. Liste (immat, marque, modèle) des modèles des marques Peugeot et Citroen affichés par marque et modèle
  4. Liste des clients de Paris, de plus de 50 ans par ordre alphabétique
  5. Liste (immat) des véhicules de couleur blanche sauf les cabriolets 
  6. Combien y-a-t-il de véhicules des catégories luxe et premium datant d’avant 2012 (2012 compris)
  7. Liste des locations (numéro de location, avec ville, année, mois) ayant débuté et terminé dans la même ville par ordre chronologique (la plus récente en premier)
  8. Liste des voitures datant d’entre 2010 et 2012 (2010 et 2012 inclus)
  9. Liste des voitures de marque Renault, Peugeot ou Citroen (utilisez IN).
  10. Combien de propriétaires n’habitent ni Paris, ni Lyon, ni Nantes ? 
  11. Liste des clients qui ont fait des locations sans permis

                1 Liste des clients (codeC) qui ont fait des locations.

                2 Liste des clients (codeC) qui ont un n° de permis 

                3 Liste des clients (codeC) qui n’ont pas de n° de permis 

                4 Liste des clients (codeC) qui ont fait une location sans n° de permis

12 Liste (nom et prénom) des clients habitant dans la même ville qu’un propriétaire 

13 Liste (immat) des voitures qui ont été loués (au moins une fois)

14 Liste (immat) des voitures qui n’ont jamais été loués

— 1
select count(*) from voiture where categorie= « cabriolet » and (couleur = ‘rouge’ or couleur = ‘bleue’);  — 1
— 2
select distinct categorie from voiture; — (7 rows returned)
select count(*) from voiture where ( (categorie =’familiale’ and compteur <= 50000) or (categorie =’utilitaire’) ) and (couleur = ‘noir’ or couleur =’blanc’); — 4
— 3
select distinct marque from voiture order by marque;– (6 rows returned)
select marque, modele, immat from voiture where marque = ‘Peugeot’ or ‘Citroen’ order by marque, modele; — (8 rows returned)
— 4
select nom, prenom, age from client where ville =’Paris’ and age > 50 order by nom; — (1 rows returned)
— 5
select immat from voiture where couleur = ‘blanc’ and categorie <> ‘cabriolet’; — (8 rows returned)
— 6
select count(*) from voiture where (categorie = ‘luxe’ or categorie = ‘premium’) and achatA <= 2012; — (6 rows returned)
— 7
select numLoc, villeD, annee, mois from location  where villeD = villeA order by annee DESC , mois DESC;– (67 rows returned)
— 8-9-10
select immat from voiture where achatA between 2010 and 2012; — (8 rows returned)
select immat from voiture where marque in (‘Renault’, ‘Peugeot’ , ‘Citroen’); — (16 rows returned)
select pseudo from proprietaire where ville not in (‘Paris’, ‘Lyon’, ‘Nantes’); — (5 rows returned)
— 11
select codeC from location;
select codeC from client where permis is not null;
select codeC from client where permis is null;
select distinct codeC , permis from client 
where codeC in ( select codeC from location)
and permis is null ; — (2 rows returned)
— autre version
 
select distinct codeC , permis from client 
where exists (select codeC from location)
and permis is null ; 
— autre version mais sans le n° de permis
select distinct codeC from location 
where codeC in ( select codeC from client where permis is null) ; 
 
— 12
select nom, prenom , ville from client 
where ville in (select ville from proprietaire); — (18 rows returned)
— 13
select distinct immat from location; — (20 rows returned)
 
— 14
select distinct immat from voiture 
where immat not in (select distinct immat 
from location); — (1 rows returned)
 
 select distinct immat from voiture where not exists (
select * from location 
where location.immat = voiture.immat); — (1 rows returned)

Exercise 5

  1. Quel est le pseudo du propriétaire de la voiture ’56AA46′ ?
  2. Combien de locations pour les peugeot 205 ?
  3. Liste (marque,modèle, immat, n°location) des voitures louées en 4/2015.
  4. Liste (marque,modèle, immat, n°location) des voitures louées l’année de leur achat. 
  5. Liste des immat des véhicules Peugeot
  6. Liste des immat des véhicules Peugeot et des infos des locations correspondantes par année, mois et immat 
  7. La liste des clients qui ont loué un véhicule Ferrari ?
  8. Quels sont les modèles de Peugeot ayant été loués.
— 1
select pseudo 
from proprietaire p, voiture v — Jules
where immat = ’56AA46′ 
and p.codeP = v.codeP ;
— 2
select count(*) 
from location l , voiture v — 11
where l.immat = v.immat
and v.marque= ‘Peugeot’ 
and v.modele = ‘205’;
— 3
select v.marque, v.modele , v.immat, l.numLoc– (6 rows returned)
from voiture v, location l 
where v.immat=l.immat
and l.annee = ‘2015’ 
and l.mois = ‘4’;
— 4
select v.marque, v.modele , v.immat, l.numLoc– (11 rows returned)
from voiture v, location l 
where v.immat = l.immat
and l.annee = v.achatA ;
— 5
select distinct immat — (5 rows returned)
from voiture 
where marque =’Peugeot’;
— 6
select l.* — (30 rows returned)
from voiture v , location l
where   v.immat = l.immat
and marque = ‘Peugeot’
order by l.annee, l.mois,l.immat;
— 7
Select c.nom, c.prenom, v.marque, v.modele — (13 rows returned)
from location l , voiture v , client c  
where c.codeC = l.codeC
and l.immat = v.immat
and v.marque = ‘Ferrari’;

Exercise 6

  1. Liste des marques ayant plus de 3 voitures disponibles à la location
  2. Liste des immat des véhicules ayant effectué plus de 800 km en location
  3. Liste des modèles des voitures ayant été louées au moins 3 fois

— 1: liste des marques ayant plus de 3 (>) voitures disponibles à la location
select marque — (2 rows returned)
from voiture
group by marque
having count(*) > 3;

— 2: liste des immat des voitures ayant effectuées plus de 800km en location
select immat — (15 rows returned)
from location
group by immat
having sum(km) > 800;

— 3: les modèles de voitures ayant été louées au moins 3 fois (avec nombre de locations)
— affichés par ordre alphabétique de modele
select v.modele, count(l.numloc) — (15 rows returned)
from location l JOIN voiture v ON l.immat = v.immat
group by v.modele
having count(l.numloc) >= 3
order by v.modele ;

Exercise 7

  1. Kms effectués en location par Alain Delon, voiture (immat) par voiture
  2. Marques et moyennes des km effectués en location (par marques)
  3. Quelle est la voiture la plus ancienne ? (avec MIN)
  4. Quelle voiture a plus de km au compteur que toutes les Peugeot ? (avec ALL)
  5. Quelle/s voiture/s a/ont un prix de journée supérieur à une Ferrarri ?
  6. Afficher la liste des voitures ayant un km compteur supérieur à la moyenne générale des km compteur.
  7. Liste des voitures ayant un km compteur supérieur à la moyenne des km compteur de leur modèle ?
— 1: KMs effectué en location par Alain Delon voiture (immat) par voiture
select immat, sum(km) — (4 rows returned)
from client c JOIN location l on c.codeC = l.codeC 
where nom = ‘Delon’
group by immat; 
 
— 2a: marques et moyennes de Km effectués en location par marque
select v.marque, avg(l.km) — (6 rows returned)
from location l, voiture v 
where l.immat = v.immat 
group by v.marque;
 
— 2b: idem par ordre décroissant des moyennes
— utilisation du AS
select v.marque, avg(l.km) as moyenne — (6 rows returned)
from location l, voiture v 
where l.immat = v.immat 
group by v.marque 
order by moyenne DESC;
 
— 3: quelle est la voiture la plus ancienne
select immat — (3 rows returned)
from voiture 
where achatA = (select min(achatA) 
from voiture) ;
 
— 4: Quelle/s voiture/s a/ont plus de km au compteur que toutes les Peugeot (ALL)
select immat — (7 rows returned)
from voiture
where compteur >= ALL (select compteur
from voiture
where marque = ‘Peugeot’); 
 
— 5: Quelles voitures ont un prix de journée supérieur à une Ferrari
select immat — (20 rows returned)
from voiture
where prixJ >= ANY (select prixJ
from voiture 
where marque =’Ferrari’);
 
— 6: liste des voitures ayant un km compteur supérieur à la moyenne générale des km compteur
select immat– (6 rows returned)
from voiture
where compteur > (select avg(compteur) 
from voiture) ;
 
— 7: liste des voitures ayant un km compteur supérieur à la moyenne des km compteur de leur modèle
select v1.immat — (5 rows returned)
from voiture v1
where v1.compteur > (select avg(v2.compteur) 
from voiture v2 
where v2.modele = v1.modele) ;

Exercise 8

  1. Liste des modèles de voitures louées au moins 3 fois (avec nombre de locations) ?
  2. Quelle est la voiture ayant parcouru le plus de km en location ?
  3. Quel(s) client(s) a effectué le plus de locations ?
  4. Quel client a loué le plus de véhicules différents ?
  5. Quel est le total des km de location effectués pour le propriétaire P75 ?
  6. Quel est le total des km de location effectués pour le propriétaire P75 voiture par voiture ?

                idem mais pour en prenant en compte que les locations de plus de 300 Km. 

                idem mais pour les véhicules ayant effectué un total de plus de 300 Km en location. 

Pour vérification, liste des locations effectuées par les véhicules de P75. 

— 1: les modèles de voitures ayant été louées au moins 3 fois (avec nombre de locations)
— affichés par ordre alphabétique de modele
select v.modele , count(l.numloc) — (15 rows returned)
from location l , voiture v
where l.immat = v.immat
group by v.modele
having count(l.numloc) >=  3
order by v.modele ; 
 
— 2: quelle est la voiture ayant parcouru le plus de km en location
select immat , sum(km) — 11RS75, 3697km
from location 
group by immat 
having sum(km) >= ALL( select sum(km) 
from location 
group by immat );
 
— 3: le client ayant effectué le plus de location
select C.nom, C.prenom — (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: le client ayant loué le plus de véhicules différents
select C.nom, C.prenom — (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 des km de location effectués pour les voitures du propriétaire P75
select sum(l.km) — 7192
from location l, voiture v
where l.immat = v.immat and v.codeP =’P75′;
 
— 6a: total des km de location effectué pour le propriétaire P75 voiture par voiture
select l.immat, sum(l.km) — (4 rows returned)  
from location l, voiture v
where l.immat = v.immat and v.codeP =’P75′
group by l.immat;
 
— 6b: idem à (b) mais en ne prenant en compte uniquement les locations de plus de 300 km
select l.immat, sum(l.km)– (3 rows returned) 
from location l, voiture v
where l.immat = v.immat and v.codeP =’P75′
and l.km > 300
group by l.immat;
 
— 6c idem à(b) mais uniquement pour les véhicules ayant effectué un total plus de 300 km en location
select l.immat, sum(l.km) — (4 rows returned) 
from location l, voiture v
where l.immat = v.immat and v.codeP =’P75′
group by l.immat
having sum(l.km) > 300 ;
 
— 6d: pour vérification, liste de locations effectuées par le véhicules de P75 
select l.immat, l.km  — (25 rows returned)
from location l, voiture v
where l.immat = v.immat and v.codeP =’P75′
order by l.immat, l.km DESC ;

Exercise 9

 La requête visée est : quels clients (les noms) utilisent le même numéro de permis ?

Pour vous guider une série de requêtes progressives.

  1. Pour chaque n° de permis, afficher le nombre de fois où il est utilisé.
  2. Liste des numéros de permis qui ont été utilisés plus d’une fois ?
  3. Liste des noms des clients ayant un n° de permis utilisé plus d’une fois. 
  4. Liste des noms des clients dont le n° de permis est égal au n° de permis d’un autre client (avec IN)
  5. Autre écriture pour (4) en utilisant les égalités de jointure explicites (SQL1). 
  6. S’il reste des doublons, comment les supprimer ?
— 1: nombre de fois où un n° de permis a été utilisé
select c1.permis, count(*)  — (8 rows returned)
from client c1 
group by c1.permis
order by c1.permis ;
 
— 2: nombre de permis utilisés plus d’une fois 
select c1.permis  — (5 rows returned)
from client c1 
group by c1.permis
having count(*)> 1
order by c1.permis ;
 
— 3: clients ayant un n° de permis (afficher aussi le n° de permis) utilisé plus d’une fois
select c2.nom  — (14 rows returned)
from client c2
where c2.permis IN ( select c1.permis
from client c1 
group by c1.permis
having count(*)> 1 ) 
order by c2.nom ;
 
— 4: liste des noms des clients utilisant le numéro de permis (afficher le n° de permis) d’un autre client 
select c1.nom, c1.permis — (14 rows returned)
from client c1
where c1.permis IN ( select c2.permis
from client c2 
where  c2.permis = c1.permis
and c2.nom != c1.nom)
order by c1.nom ;
 
— 5: autre écriture avec les égalités de jointure explicites 
— (l’autojointure est maintenant mieux visible)
select c1.nom, c2.nom , c1.permis — (38 rows returned)
from client c1, client c2
where c2.permis = c1.permis and c2.nom != c1.nom 
order by c1.nom ;
 
— 6: autre écriture avec les égalités de jointure explicites 
— (l’autojointure est maintenant plus visible)
select c1.nom, c2.nom , c1.permis — (19 rows returned)
from client c1, client c2
where c2.permis = c1.permis and c2.nom > c1.nom 
order by c1.nom ;

Exercise 10

 La requête visée est : quel clients (les noms) ont loué toutes les voitures ?

(a) Liste des voitures qui n’ont pas été louées ; à écrire en utilisant NOT EXISTS. (reformulation : les voitures pour lesquelles il n’existe pas de location avec ce n° d’immat).

(b) Liste des noms de clients pour lesquels il existe des véhicules qu’il (le client) n’ait pas loué.

(c) Liste des noms des clients qui ont loué tous les véhicules du parc.

 (reformulation : liste des noms de clients pour lesquels il n’existe pas de véhicule qu’ils n’aient pas loué.

— La requête visée est : quel clients (les noms) ont loué toutes les voitures ?
— (a) Liste des voitures qui n’ont pas été louées ; à écrire en utilisant NOT EXISTS.

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

— (b) Liste des noms de clients pour lesquels il existe des véhicules qu’il (le client) n’ait pas loué.
SELECT DISTINCT nom, prenom
from client, voiture
where not exists (select codeC from location where location.immat=voiture.immat and client.codeC=location.codeC);

— (c) Liste des noms des clients qui ont loué tous les véhicules du parc.
— (reformulation : liste des noms de clients pour lesquels il n’existe pas de véhicule qu’ils n’aient pasloué.

select client.nom
from location, client
where location.codeC= client.codeC
group by client.codeC
having count(distinct immat)>= (select (count(distinct immat)) from voiture);

BDD Vins.txt

Nous souhaitons utiliser le schéma relationnel suivant  pour gérer une cave à vin. La base de données représentant cette cave possède le schéma relationnel suivant: vins

VIN (numV, cru, annee, degre)

PRODUCTEUR ( numP, nom, prenom, region)

RECOLTE ( nprod#, nvin#, quantite)

Et les csv suivants : vin, recolte, producteur.

A l’aide de l’instruction suivante, vous traduisez chaque ligne du fichier .csv en tuple dans la table correspondante

LOAD DATA INFILE ’chemin absolu/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’; si le fichier XXX.csv est sous la racine

Un vin est caractérisé par un numéro entier, un cru, une année de production et un degré. L’ensemble des vins est représenté par la relation VIN. La clé de la relation VIN est l’attribut numV.

Un producteur est caractérisé par un numéro entier, un nom, un prénom et une région. L’ensemble des producteurs est représenté par la relation PRODUCTEUR. La clé de la relation PRODUCTEUR est l’attribut numP. Un producteur produit un ou plusieurs vins. Réciproquement, un vin est produit par un ou plusieurs producteurs (éventuellement par aucun !).

L’ensemble des productions est représenté par la relation RECOLTE. Un tuple de la relation RECOLTE représente une production particulière d’un vin de numéro nvin par un producteur de numéro nprod en une certaine quantité. La clé de la relation RECOLTE est le groupe d’attributs (nvin, nprod).

Exercise 1

1- Créer la base de données à partir des fichiers fournis.

Par défaut les fichiers ne sont lisibles qu’à partir du répertoire précisé dans la variable secure_file_priv.

SHOW VARIABLES LIKE « secure_file_priv »;

Déposer vos fichiers dans ce répertoire et les charger pour peupler vos tables.

LOAD DATA INFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/producteur.csv’

    INTO TABLE producteur

    FIELDS

        TERMINATED BY ‘;’

    LINES

        STARTING BY  »  

        TERMINATED BY ‘\r\n’;

2- Afficher les informations contenues dans la relation VIN.

3- Donner la liste des producteurs qui n’ont pas de prénom.

4- Donner la liste des régions distinctes de production des vins.

5- Donner la liste des vins de 1980.

6- Donner la liste des noms et des prénoms des producteurs de vins n’appartenant pas aux régions suivantes : Corse, Beaujolais, Bourgogne et Rhône.

7- Quelle est la liste des crus récoltés en 1979? Afficher le cru, le numéro du producteur et la quantité produite.

8- Quels sont les noms des producteurs du cru Etoile, leurs régions et la quantité de vins récoltés ?

9- Quel est le nombre de récoltes ?

10-Combien y-a-t-il de producteurs de vin dans la région Savoie et Jura ?

11-Combien y-a-t-il de producteurs de vin ayant récoltés au moins un vin dans la région Savoie et Jura ?

12-Quelles sont les quantités de vin produites par région. Donner la liste ordonnée par quantité décroissante.

13- Quelle est la quantité de vin produite de degré >12 par numéro de vin ?

14- Quel est le cru ou les crus au plus fort degré ?

15- Donner la liste ordonnée des crus.

16- Donner la liste ordonnée des crus récoltés.

17- Quel est donc le cru non récolté ?

18- Donner la liste ordonnée des crus et la quantité par cru ?

19- Quel est le degré moyen des crus ?

20- Quel sont les crus (ordonnés par degré et année) de degré supérieur au degré moyen des crus ?

select * from vin; # 101 rows
select * from producteur; # 200 rows
select * from recolte; # 200 rows

#Question 2 – 7
select distinct nom from producteur where prenom is null; # 0 rows
select distinct nom from producteur where prenom =  » or prenom is null; # 12 rows

select distinct region from producteur; # 11 rows
select degre,cru from vin where annee=1980 order by degre desc; # 18 rows
select nom,prenom,region from producteur where region not in (‘corse’ , ‘beaujolais’ , ‘bourgogne’ , ‘rhone’) order by nom asc; # 164 rows
select nprod, cru,quantité from vin v,recolte r where annee=1979 and v.numV=r.nprod order by nprod; # 5 rows

#Question 8- 13
select cru,nom,region,quantité FROM VIN v,PRODUCTEUR p,RECOLTE r WHERE cru=’Etoile’ AND p.numP=v.numV AND p.numP=r.nprod; #1 row
select COUNT(*) FROM RECOLTE ; # –> 665
select COUNT(*) FROM PRODUCTEUR WHERE region in (‘Savoie’,’Jura’); # –> 57
select COUNT(*), quantité FROM PRODUCTEUR p,RECOLTE r WHERE region in(‘Savoie’,’Jura’) AND p.numP=r.nprod AND quantité>0 GROUP BY quantité; # 22 rows
select SUM(quantité) FROM RECOLTE R, PRODUCTEUR P WHERE R.nprod=P.numP GROUP BY region ORDER BY quantité DESC; # 10 rows
select SUM(quantité) from recolte r, vin v where v.numV=r.nvin and v.degre>12 group by nvin; # 14 rows

# Question 14-20
select cru FROM VIN WHERE degre in (select MAX(degre) FROM VIN); # –> Saint Amour
select cru FROM VIN ORDER BY cru ASC; #101 rows
select distinct cru from vin v ,recolte 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 ,recolte r where v.numV=r.nvin); # –> Saint Veran
select cru, SUM(quantité) from vin v ,recolte r where v.numV=r.nvin group by cru order by cru; # 61 rows
select avg(degre) from vin v ; # –> 11.35
select cru,degre,annee from vin where degre > (select avg(degre) from vin v) order by cru,degre,annee; # 48 rows

Exercise 2

1 – Donnez tous les vins dont le cru commence par la lettre ‘S’

2 – Donnez tous les vins distincts dont la date de production est entre 1980 et 1981

3 – Sélectionnez toutes les productions du producteur numéro 606

4 – Donnez par numéro de producteur, la quantité totale de vin produite. Vérifiez avec la question 2

5 – Donnez par numéro de producteur, la quantité totale de vin produite si celle-ci est supérieure à 400 et le tout ordonnancé par ordre croissant des quantités calculées en utilisant un alias sur la somme

6 – Recherchez le cru Pommard le plus ancien. Affiche le avec le commentaire :  « celui que je préfère »

7 –  Quels sont les numéros des producteurs ayant récolté le plus de vin différents ?

8 – Ajoutez une colonne à la table Producteur correspondant à sa date de naissance,

Modifier les producteurs suivants existants dans la base en leur assignant leur date de naissance

datenaissance = ‘1960-12-11′  nom=’Six’;

datenaissance = ‘1962-12-11′  nom=’Lasnier’;

datenaissance = ‘1970-12-11′  nom=’Moniot’;

datenaissance = ‘1970-12-11′  nom=’Boxler’;

datenaissance = ‘1980-12-11′  nom=’Jayer’;

datenaissance = ‘1961-05-10′  nom=’Grivot’;

datenaissance = ‘1980-12-11′  nom=’Tortochot’;

9 – Sélectionnez les producteurs pour lesquels la date de naissance n’est pas nulle

10 – Calculez l’âge de Grivot

11 – Calculez le nombre de producteurs par âge

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 recolte where nprod = 606;

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

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

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

select concat (cru, ‘ celui que je préfère ‘), annee
from vin where cru = ‘Pommard’ and annee >= (select max(annee) from vin where cru=’Pommard’);

7- select nprod, nom from recolte, producteur where recolte.nprod = producteur.num
group by nprod
having count(distinct nvin) >= All
(select count(distinct nvin) as nbvindifferent from recolte group by nprod
);


select nprod, nom from recolte, producteur where recolte.nprod = producteur.num
group by nprod
having count(distinct nvin) >=
(select max(nbvindifferent) from
(select count(distinct nvin) as nbvindifferent from recolte group by nprod) P);


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

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

select * from recolte where nprod=355;


8- alter table producteur add column naissance datetime;

9- select * from producteur where num < 5;
update producteur set naissance = ‘1990/10/10’ where num = 1;

10- SELECT nom , Year(current_date()) – YEAR(producteur.naissance) as AGE FROM producteur
where naissance is not null;

11- select count(*) as nombre , Year(current_date()) – year(producteur.naissance)as age
from producteur where naissance is not null group by age;

Exercise 3

Reprendre la base de données VINs et répondre aux questions suivantes :

1 – Quels sont les noms des producteurs du vin N° 5 ;

2 – Quels sont les producteurs qui ont produit le vin ‘Pommard’ ?

3 – Quels sont les crus récoltés ?

4 – Quelle est la liste des crus récoltés en 1979? Afficher le cru, le numéro du producteur et la quantité produite.

5- Quels sont les noms des producteurs du cru Etoile, leurs régions et la quantité de

vins récoltés?

6 – Quel est le nombre producteurs de crus récoltés en Savoie et dans le Jura ?

7 – Quel est donc le cru non récolté?

8 – Donner la liste ordonnée des crus et la quantité par cru?

9 – Donnez les noms des producteurs qui ont récolté le plus de vins différents

10 – Donnez la liste des noms des producteurs qui n’ont pas récolté

11 – Quels sont les noms des personnes portant le même prénom

1- select nom from producteur , recolte where producteur.num = recolte.nprod and nvin=5;
 
2- select nom, prenom from producteur, recolte, vin where producteur.num = recolte.nprod and recolte.nvin=vin.num and cru = ‘Pommard’;
 
3- select distinct cru from recolte, vin where recolte.nvin= vin.num;
 
4- select 
    p.num, p.nom, cru, qte
from
    vin v,
    recolte r,
    producteur p
where
    date = 1979 and v.num = r.nvin
        and p.num = r.nprod
order by nprod;
 
5- select 
    nom, cru, region, qte
from
    recolte r,
    producteur p,
    vin v
where
    p.num = r.nprod and v.num = r.nvin
        and v.cru = ‘Etoile’;
 
6- select 
    count(*)
from
    producteur p,
    recolte r
where
    region in (‘Savoie’ , ‘Jura’)
        and p.num = r.nprod;
 
select 
    count(*)
from
    producteur p,
    recolte r
where
     p.num = r.nprod and (p.region=’Jura’ or p.region=’Savoie’);
 
7- select 
    cru
from
    vin
where
    vin.num not in (select 
            recolte.nvin
        from
            recolte);
 
8- select 
     cru, sum(qte) 
from
    recolte,
    vin
where
   vin.num = recolte.nvin
group by nvin;
 
 
select 
    cru, sum(qte)
from
    vin v
        LEFT OUTER JOIN
    recolte r ON v.num = r.nvin
group by cru
order by cru;
 
/*requete 9 et 10 voir plus haut*/
 
11- select P1.nom, P2.nom from Producteur P1, Producteur P2
where P1.prenom = P2.prenom;
To share