TD6 - Free

TD6

Exercice 1.


Schéma

Les exemples suivants sont tirés du livre A Guide to DB, Third Edition de
C.J. Date.
CREATE TABLE FOURNISSEUR
( F# CHAR(5) NOT NULL,
FNOM CHAR(20) NOT NULL WITH DEFAULT,
STATUS SMALLINT NOT NULL WITH DEFAULT,
VILLE CHAR(15) NOT NULL WITH DEFAULT,
PRIMARY KEY ( F# ) );

CREATE TABLE PRODUIT
( P# CHAR(6) NOT NULL,
PNOM CHAR(20) NOT NULL WITH DEFAULT,
COULEUR CHAR(6) NOT NULL WITH DEFAULT,
POIDS SMALLINT NOT NULL WITH DEFAULT,
PRIMARY KEY ( P# ) );

CREATE TABLE CLIENT
( C# CHAR(6) NOT NULL,
CNOM CHAR(20) NOT NULL WITH DEFAULT,
VILLE CHAR(15) NOT NULL WITH DEFAULT,
PRIMARY KEY ( C# ) );

CREATE TABLE COMMANDE
( F# CHAR(5) NOT NULL,
P# CHAR(6) NOT NULL,
C# CHAR(6) NOT NULL,
QTE SMALLINT,
PRIMARY KEY ( F#, P#, C# ) );

CREATE UNIQUE INDEX FX ON FOURNISSEUR ( F# );

Requêtes

Exprimer les requêtes suivantes en SQL.

Requête 1 :
Toutes les informations sur les clients.
SELECT C#, CNOM, VILLE
FROM CLIENT;
ou
SELECT *
FROM CLIENT;

Requête 2 :
Toutes les informations sur les clients à Paris.
SELECT *
FROM CLIENT
WHERE VILLE = 'Paris';

Requête 3 :
La liste triée des numéros des fournisseurs du client avec le numéro
C1.
SELECT DISTINCT F#
FROM COMMANDE
WHERE C# = 'C1'
ORDER BY F#;

Requête 4 :
Les commandes avec une quantité entre 300 et 750.
SELECT *
FROM COMMANDE
WHERE QTE >= 300 AND QTE B.P#;

Exercice 1.


Schéma

Les exemples suivants sont tirés des sources de la société Oracle.

Relation des Employés (EMP)


EMP(ENO, ENOM, PROF, DATEEMB, SAL, COMM, DNO)

ENO :
numéro d'employé, clé

ENOM :
nom de l'employé

PROF :
profession (directeur n'est pas une profession)

DATEEMB :
date d'embauche

SAL :
salaire

COMM :
commission (un employé peut ne pas avoir de commission)

DNO :
numéro de département auquel appartient l'employé

Relation des Départements (DEPT)


DEPT(DNO, DNOM, DIR, VILLE)

DNO :
numéro de département, clé

DNOM :
nom du département

DIR :
directeur du département

VILLE :
lieu du département (ville)

Requêtes

Requête 20 :
Donner la liste des employés ayant une commission.
SELECT *
FROM EMP
WHERE COMM IS NOT NULL;

Requête 21 :
Donner les noms, emplois et salaires des employés par emploi croissant
et, pour chaque emploi, par salaire décroissant.
SELECT ENOM, PROF, SAL
FROM EMP
ORDER BY PROF ASC, SAL DESC;

Requête 22 :
Donner le salaire moyen des employés.
SELECT AVG(SAL) AS 'SALAIRE MOYEN'
FROM EMP;

Requête 23 :
Donner le nombre d'employés du département PRODUCTION.
SELECT COUNT(EMP.*)
FROM EMP, DEPT
WHERE EMP.DNO = DEPT.DNO
AND DEPT.DNOM = 'PRODUCTION';

Requête 24 :
Les numéros de département et leur salaire maximum?
SELECT DNO, MAX(SAL)
FROM EMP
GROUP BY DNO;

Requête 25 :
Donner les noms des employés ayant le salaire maximum de chaque
département.
SELECT ENOM
FROM EMP
WHERE (DNO, SAL) IN (SELECT DNO, MAX(SAL)
FROM EMP
GROUP BY DNO);


ou


SELECT ENOM
FROM EMP E
WHERE SAL = (SELECT MAX(SAL)
FROM EMP F
WHERE F.DNO = E.DNO);

Requête 26 :
Les professions et leur salaire moyen?
SELECT PROF, AVG(SAL)
FROM EMP
GROUP BY PROF;



Requête 27 :
Le salaire moyen le plus bas (par profession)?
SELECT MIN(AVG(SAL))
FROM EMP
GROUP BY PROF;

Requête 28 :
Donner les emplois ayant le salaire moyen le plus bas; donnez aussi
leur salaire moyen.
SELECT PROF, AVG(SAL)
FROM EMP
GROUP BY PROF
HAVING AVG(SAL) = (SELECT MIN(AVG(SAL))
FROM EMP
GROUP BY PROF);