DEVOIR DE SQL :

Nom : DIALLO Prénom : Abdou

Classe : L3 électronique

Exo 01 :

1) SELECT code_ch FROM opere WHERE date_op=’09-09-01’

2) SELECT nom, prenom FROM malade WHERE code_mal IN (SELECT code_mal FROM occupe
WHERE date_deb=’05-03-01’)
3) SELECT code_ch, COUNT(*) FROM chirurg, opere WHERE chirurg.code_ch=opere.code_ch AND
date_op BETWEEN 09-05-01 AND 09-09-01 GROUP BY code_ch
4) SELECT nom FROM opere, chirurg WHERE chirurg.code_ch=opere.code_ch AND date_op IN
(SELECT date_op FROM chirurg, opere WHERE [Link]=’Med’ AND
chirurg.code_ch=opere.code_ch)

Exo 02 :

1- SELECT * FROM article WHERE prix_vente_art >= 2*prix_achat_art

2- SELECT * FROM article WHERE coul_art = ‘rouge’ AND poids_art > 100
3- SELECT * FROM article WHERE coul_art ≠ rouge AND pods_art ≤ 500
4- SELECT N°four FROM commande WHERE 09-05-01 ≤ date_cde ≤ 09-09-01 GROUP BY N°four
HAVING COUNT(*) > 10
5- SELECT coul_art, AVG(prix_vente_art) FROM article WHERE prix_achat_art ≥ 500 GROUP BY
coul_art
6- SELECT * FROM article WHERE poids_art < (SELECT poids_art FROM article WHERE N°art=’A02’)

7- SELECT N°art, nom_art FROM article WHERE coul_art = (SELECT coul_art FROM article WHERE
N°art=’A10’) AND poids_art ≥ (SELECT AVG(poids_art) FROM article)

8- SELECT nom_art FROM article WHERE prix_vente_art>(SELECT MIN(prix_vente_art) FROM article

WHERE coul_art=’blanche’

Exo 03 :
1- SELECT origine, N°bois FROM boisson WHERE date_fab=’09-09-2001’ AND concentration>10
ORDER BY origine ASC, N°bois DESC
2- SELECT nom FROM buveur, bus WHERE buveur.N°buv=bus.N°buv AND
bus.N°bois=boisson.N°bois AND [Link]=’Abricot’
3- SELECT N°bois FROM bus GROUP BY N°bois HAVING COUNT(*)>100
4- INSERT INTO boisson
5- DELETE boisson WHERE ‘Bendjima’ IN (SELECT nom FROM buveur, bus WHERE
boisson.N°bois=bus.N°bois AND bus.N°buv=buveur.N°buv)
6- UPDATE bus SET quantité=0 WHERE ‘Béchar’ IN (SELECT adresse FROM buveur WHERE
bus.N°buv=buveur.N°buv)

Exo 04 :
 1- SELECT N°wagon FROM wagon WHERE type_wagon=’frigo’ AND etat=’libre’ AND
gare=’tours’
2- SELECT type_wagon FROM wagon WHERE N°wagon IN (SELECT N°wagon FROM train
WHERE N°train=’4002’
3- SELECT N°wagon, poids_vide, poids_marchandise FROM train, wagon, transport WHERE
train.N°wagon = wagon.N°wagon AND train.N°wagon = transport.N°wagon AND
train.N°train = ‘4002’
4- SELECT N°ligne FROM trafic GROUP BY N°ligne HAVING SET(jour) CONTAIN (SELECT jour
FROM trafic)
5- SELECT N°ligne FROM trafic GROUP BY N°ligne HAVING SET(jour) CONTAIN (‘lundi’,
‘jeudi’)
6- SELECT N°train, COUNT(N°wagon) FROM train GROUP BY N°train
7- SELECT N°train FROM train GROUP BY N°train HAVING COUNT(N°wagon) ≥ 100
8- SELECT gare FROM ligne WHERE rang = (SELECT MAX(rang) FROM ligne WHERE
N°ligne=’35’) AND N°ligne = ‘35’

Exo 05 :

1- Clés primaires : Etudiant⇒nom_etud Enseignant⇒nom_ens Faculte⇒nom_fac

Affectation⇒nom_etud, groupe Emploi_temps⇒nom_ens, jour, heure Clés étrangères :
Enseignant⇒nom_fac % à faculte Affectation⇒nom_etud % à etudiant
Emploi_temps⇒nom_ens % à enseignant
2- Création des tables en SQL : CREATE TABLE faculte (nom_fac char(20) not null, Localite
char(20), recteur char(15)) PRIMARY KEY nom_fac ; CREATE TABLE emploi_temps (nom_ens
char(15) not null, Groupe integer, matière char(10), Jour char(8) not null, heure char(15) not
null, Activite_pedag char(15), PRIMARY KEY(nom_ens, jour, heure), FORCEGN KEY nom_ens,
IDENTIFIES enseignant ; 3- Exprimer en SQL :
R1 :SELECT A.nom_etud, B.nom_etud FROM etudiant A, etudiant B WHERE [Link] = [Link]
AND A.nom_etud <> B.nom_etud

R2 : SELECT * FROM etudiant WHERE ville not IN (SELECT ville FROM etudiant WHERE
ville=’Béchar’

R3 : SELECT nom_etud, age_etud FROM etudiant WHERE nom_etud LIKE ‘%B’ AND nom_etud
IN (SELECT nom_etud FROM affectation WHERE groupe=4)

R4 : SELECT nom_ens, salaire FROM enseignant WHERE age_ens BETWEEN 35 AND 50 AND
salaire>(SELECT AVG(salaire) FROM enseignant)

R5 : SELECT nom_ens FROM emploi_temps WHERE matiere=’BDD’ AND nom_ens IN (SELECT

nom_ens FROM enseignant WHERE grade = ‘charge de cours’ AND age_ens <35 AND
nom_fac IN (SELECT nom_fac FROM faculte WHERE localite=’ORAN’ AND recteur=’Med’

Exo 06 :

1- SELECT * FROM client

2- SELECT nom, tel FROM client
3- SELECT * FROM client WHERE ville=’Béchar’
 4- SELECT * FROM article WHERE prix_unit>150DA AND qte_stock ≤ 100 UNION SELECT *
FROM commande WHERE date_cde > 09-09-99
5- SELECT * FROM article WHERE prix_unit≥ 150 AND prix_unit≤ 200 UNION SELECT * FROM
commande WHERE date_cde BETWEEN 09-09-99 AND 30-01-00
6- SELECT * FROM client WHERE ville=’Béchar’ OR ville=’Oran’ OR ville=’Alger’ UNION SELECT *
FROM article WHERE prix_unit=150 OR prix_unit=200 OR prix_unit=300

Exo 07 :

1- SELECT N°voyage , A.places_disp , B.places_disp FROM vol A , vol B , voyages WHERE

A.N°vol = N°vol_all AND B.N°vol = N°vol_ret
2- SELECT COUNT(nom_hot) FROM hotels WHERE ville_hot = ‘Béchar’
3- SELECT SUM(lits_disp) FROM hotels WHERE ville_hot = ‘Béchar’ AND date = ’09-09-2002’
4- SELECT MIN(lits_disp) FROM hotels , voyages WHERE hotels.nom_hot = voyages.nom_hot
AND voyages.N°voyage = ‘113’ AND [Link] >= voyages.date_dep AND [Link] <=
[Link]
5- SELECT ville_hot , COUNT(nom_hot) FROM hotels GROUP BY ville_hot ;

Exo 08 :

1) SELECT * FROM client

2) SELECT * FROM client WHERE ville = ’Béchar’
3) SELECT DISTINCT N°four FROM command WHERE N°cl = ’C1’ ORDER BY N°four
4) SELECT * FROM commande WHERE Qte BETWEEN 300 AND 750
5) SELECT * FROM commande WHERE Qte = Qte
6) SELECT N°cl FROM client WHERE SUBSTR (ville, 1, 1) = ’B’
7) SELECT N°four, N°cl FROM fournisseur, client WHERE [Link] = [Link]
8) SELECT N°four, N°cl FROM fournisseur, client WHERE NOT [Link] = [Link]
9) SELECT DISTINCT N°prod FROM commande, fournisseur WHERE commande.N°four =
fournisseur.N°four AND ville = ’Béchar’
10) SELECT DISTINCT N°prod FROM commande, fournisseur, client WHERE
commande.N°four=fournisseur.N°four AND commande.N°cl = client.N°cl AND
[Link] = ’Béchar’ AND [Link] = ’Oran’
11) SELECT DISTINCT [Link], [Link] FROM commande, fournisseur, client WHERE
commande.N°four = fournisseur.N°four AND commande.N°cl = client.N°cl
12) SELECT DISTINCT N°prod FROM commande, fournisseur, client WHERE commande.N°four =
fournisseur.N°four AND commande.N°cl = client.N°cl AND [Link] = [Link] ;

Exo 09 :

1) SELECT count(N°etud) FROM etudiant

2) SELECT max(note),min(note) FROM evalue
3) SELECT libelle_mat, nom_etud, avg(coeff_mat*note) FROM etudiant, matiere,
evaluer WHERE etudiant.N°etud=evaluer.N°etud AND
matiere.code_mat=evaluer.code_mat GROUP BY libelle_mat, nom_etud
4) SELECT code_mat, avg(coeff_mat*note) FROM matiere, evaluer WHERE
matiere.code_mat=evaluer.code_mat GROUP BY code_mat
 5) SELECT N°etud , avg(coeff_mat*note) FROM etudiant , matiere , evaluer WHERE
etudiant.N°etud=evaluer.N°etud AND matiere.code_mat=evaluer.code_mat GROUP
BY N°etud
6) SELECT avg(coeff_mat*note) FROM matiere , evaluer WHERE matiere.code_mat =
evaluer.code_mat

7) SELECT nom_etud , avg(coeff_mat*note) FROM etudiant , matiere , evaluer WHERE

etudiant.N°etud=evaluer.N°etud AND matiere.code_mat=evaluer.code_mat GROUP
BY nom_etud HAVING avg(coeff_mat*note)>(SELECT avg(coeff_mat*note) FROM
matiere, evaluer WHERE matiere.code_mat=evaluer.code_mat)

Exo 10 :

1) SELECT count(N°pilote) FROM pilote WHERE adresse_pil=’Béchar’

2) SELECT SUM(nbre_places) FROM avion
3) SELECT N°vol FROM vol WHERE N°pilote IN (SELECT N°pilote FROM pilote WHERE
adresse_pil=’Béchar’)
4) SELECT nom_pil FROM pilote, vol WHERE pilote.N°pilote=vol.N°pilote AND
N°avion=all(SELECT N°avion FROM avion) Exercices supplémentaires :
Nb : des alias sont utilisés dans les exercices suivants.

Exercice 1 : Relation Transport

1. La relation transport est-elle en 3FN ? Sinon, décomposez-la.

La relation transport n’est pas en 3FN à cause des dépendances fonctionnelles transitives.

La décomposition :

1. WAGON(N°wagon, type_wagon, poids_vide, capacité, état, gare_destination)

2. LIGNE(N°ligne, gare, rang_ligne)

3. TRAIN(N°train, N°ligne, date)

4. TRANSPORT(N°train, N°wagon, rang_ligne) – relation résiduelle après suppression des autres

attributs

Requêtes SQl :

1. Liste des lignes qui partent de la gare d’Alger rangées par ordre croissant :

SELECT N°ligne FROM LIGNE WHERE gare = ‘Alger’ ORDER BY N°ligne ASC ;

2. Pour chaque wagon sa capacité et son poids vide

SELECT N°wagon, capacité, poids_vide FROM WAGON ;

3. Pour chaque wagon du train 2300, son numéro, sa capacité et son poids vide
SELECT w.N°wagon, [Link]é, w.poids_vide FROM WAGON w JOIN TRANSPORT t
ONw.N°wagon=t.N°wagon WHERE t.N°train = 2300 ;
4. Liste des numéros de wagon qui partent d’Oran

SELECT w.N°wagon, [Link]é FROM WAGON w JOIN TRANSPORT t ON w.N°wagon = t.N°wagon

JOIN TRAIN tr ON t.N°train = tr.N°train JOIN LIGNE l ON tr.N°ligne = l.N°ligne WHERE [Link] = ‘Oran’
AND [Link] = ’09-09-01’ ; le 09-09-01 et leurs capacités

5. Numéros des wagons chargés du train 4002 ainsi que leur type

SELECT w.N°wagon, w.type_wagon FROM WAGON w JOIN TRANSPORT t ON w.N°wagon = t.N°wagon

WHERE t.N°train = 4002 AND w.état = ‘chargé’ ;

Exercice 2 : Base de données Boissons Création des tables

CREATE TABLE boisson ( Code_boiss INT PRIMARY KEY, Nom_boiss VARCHAR(50), Couleur
VARCHAR(20), Degré DECIMAL(5,2), Année INT ) ;

CREATE TABLE bus ( Code_buv INT, Code_boiss INT, Quantité INT, PRIMARY KEY (code_buv,
code_boiss), FOREIGN KEY (code_boiss) REFERENCES boisson(code_boiss) ) ;

Requêtes SQL :

1. Noms des boissons sans doublons

SELECT DISTINCT nom_boiss FROM boisson ;

2. Buveurs ayant bu des boissons dont le nom commence par ‘B’, degré inconnu

SELECT DISTINCT b.nom_buv FROM bureau b JOIN bus bu ON b.code_buv = bu.code_buv JOIN
boisson bo ON bu.code_boiss = bo.code_boiss WHERE bo.nom_boiss LIKE ‘B%’ AND [Link]é IS NULL
;

3. Noms des boissons bues par un buveur nommé ‘An Indus’

SELECT DISTINCT bo.nom_boiss FROM boisson bo JOIN bus bu ON bo.code_boiss = bu.code_boiss

JOIN bureau b ON bu.code_buv = b.code_buv WHERE b.nom_buv = ‘An Indus’ ;

4. Moyenne des degrés pour chaque boisson

SELECT nom_boiss, AVG(degré) as moyenne_degre FROM boisson GROUP BY nom_boiss ;

5. Insérer les producteurs de la région de Béchar ayant produit des boissons de 2001 dans la relation
buveur

INSERT INTO bureau (code_buv, nom_buv, ville) SELECT DISTINCT p.code_prod, p.nom_prod,
p.région FROM producteur p JOIN produire pr ON p.code_prod = pr.code_prod JOIN boisson b ON
pr.code_boiss = b.code_boiss WHERE p.région = ‘Béchar’ AND [Link]ée = 2001 ;

Exercice 3 : Base de données Université

Requêtes SQl :

1. Trouver le nom de la fac à qui appartient l’étudiant ‘Omar’

SELECT f.nom_fac FROM fac f JOIN appart_fac af ON f.nom_fac = af.nom_fac JOIN affectation a ON
[Link] = [Link] WHERE a.nom_etud = ‘Omar’ ;

2. Trouver les salaires de tous les enseignants

SELECT sal FROM enseignant ;

3. Trouver le salaire et l’âge pour l’enseignant M*[4] âgé de 40 ans

SELECT sal, age FROM enseignant WHERE nom_cns LIKE ‘M*[4]’ AND age = 40 ;

Exercice 4 : Base de données Bibliothèque

Requêtes SQL :

1. Titres et catégories des films enregistrés sur la cassette numéro 3, triés par ordre alphabétique

SELECT f.titre_film, [Link]égorie FROM film f WHERE f.N°cassette = 3 ORDER BY f.titre_film ASC ;

2. Livres qui ont été empruntés et combien de fois, triés par nombre d’emprunts

SELECT l.titre_livre, COUNT(el.code_livre) as nombre_emprunts FROM livre l JOIN emprunter_livre el

ON l.code_livre = el.code_livre GROUP BY l.titre_livre ORDER BY nombre_emprunts DESC, l.titre_livre
ASC ;

3. Films empruntés plusieurs fois par une même personne

SELECT e.N°emp, e.nom_emp, [Link]énom_emp, f.titre_film FROM emprunteur e JOIN emprunter_film

ef ON e.N°emp = ef.N°emp JOIN film f ON ef.N°film = f.N°film GROUP BY e.N°emp, f.N°film HAVING
COUNT(*) > 1 ;

4. Prix moyen des cassettes préenregistrées

SELECT AVG(prix_achat) as prix_moyen FROM cassette ;

5. Cassettes disponibles avec leurs films

SELECT DISTINCT c.N°cassette, f.titre_film FROM cassette c LEFT JOIN film f ON c.N°cassette =
f.N°cassette WHERE c.état = ‘disponible’ ORDER BY c.N°cassette ASC, f.titre_film ASC ;

Exercice 5 : Base de données Département

1. Clés primaires et étrangères

- Département : Clé primaire – N°dep

- Employé : Clé primaire – N°emp, Clé étrangère – N°dep référence Département(N°dep)

- Projet : Clé primaire – N°proj, Clé étrangère – N°dep référence Département(N°dep)

- Historique : Clé primaire – (N°proj, N°emp, date), Clés étrangères – N°proj référence Projet(N°proj),
N°emp référence Employé(N°emp)

2. Création des tables

CREATE TABLE Projet ( N°proj INT PRIMARY KEY, Titre VARCHAR(100), Budget_proj DECIMAL(12,2),
N°dep INT, FOREIGN KEY (N°dep) REFERENCES Département(N°dep) ) ;

CREATE TABLE Historique ( N°proj INT, N°emp INT, Date DATE, Salaire DECIMAL(10,2), PRIMARY KEY
(N°proj, N°emp, date), FOREIGN KEY (N°proj) REFERENCES Projet(N°proj), FOREIGN KEY (N°emp)
REFERENCES Employé(N°emp) ) ;

Requêtes SQL

Employés habitant dans la même ville que l’employé N°113

SELECT e1.N°emp FROM Employé e1 JOIN Employé e2 ON [Link] = [Link] WHERE e2.N°emp = 113
AND e1.N°emp != 113

Villes des employés ayant participé au projet ‘Bases de données’

SELECT DISTINCT [Link] FROM Employé e JOIN Historique h ON e.N°emp = h.N°emp JOIN Projet p ON
h.N°proj = p.N°proj WHERE [Link] = ‘Bases de données’ ;

Budget le plus élevé des projets du département N°09

SELECT MAX(budget_proj) FROM Projet WHERE N°dep = 9 ; Supprimer le projet ‘intelligence

artificielle’ DELETE FROM Projet WHERE titre = ‘intelligence artificielle’ ;

Exercice 6 : Table Étudiant

Résultats des Requêtes

1. `SELECT nom FROM étudiant` :

Roda

Oma

r Méliani

2. `SELECT age FROM étudiant WHERE ville = ‘Béchar’` :

20

3. `SELECT nom, ville FROM stagiaire WHERE age>21` :

(En supposant que « stagiaire » est une erreur et devrait être « étudiant », et comme aucun étudiant
n’a plus de 21 ans, le résultat serait vide2

4. `SELECT nom FROM stagiaire INTERSECT SELECT nom FROM étudiant` :

(Cette requête retournerait les noms présents dans les deux tables, mais comme nous n’avons pas la
table stagiaire, on ne peut pas déterminer le résultat)

5. `SELECT count(*) FROM étudiant` :