TD 2-Requêttes SQL

Exercice 1
On Considère la base de données suivante :

— Départements :( DNO, DNOM, DIR, VILLE)

— Employés : ( ENO, ENOM, PROF, DATEEMB, SAL, COMM, #DNO)

Exprimez en SQL les requêtes suivantes :

1. Donnez la liste des employés ayant une commission

2. Donnez les noms, emplois et salaires des employés par emploi croissant, et pour chaque emploi,
par salaire décroissant

3. Donnez le salaire moyen des employés

4. Donnes les numéros de département et leur salaire maximum

5. Donnez les différentes professions et leur salaire moyen

6. Donnez le salaire moyen par profession (ne sélectionner que le salaire moyen le plus bas)

7. Donnez le ou les emplois ayant le salaire moyen le plus bas, ainsi que ce salaire moyen
 Solution
1. Donnez la liste des employés ayant une commission
SELECT *
FROM Employes
WHERE COMM NOT NULL
2. Donnez les noms, emplois et salaires des employés par emploi croissant, et pour chaque emploi, par
salaire décroissant
SELECT ENOM,PROF, SAL
FROM Employes
ORDER BY PROF ASC, SAL DESC
3. Donnez le salaire moyen des employés
SELECT AVG(SAL)
FROM Employes
4. Donnez les numéros de département et leur salaire maximum
SELECT DNO, MAX(SAL)
FROM Employes
GROUP BY DNO
 Solution
5. Donnez les différentes professions et leur salaire moyen
SELECT PROF, AVG(SAL)
FROM Employes
GROUP BY PROF
6. Donnez le salaire moyen par profession (ne sélectionner que le salaire moyen le plus bas)
SELECT AVG(SAL) as moy
FROM Employes
GROUP BY PROF
ORDER BY AVG(SAL) ASC
LIMIT 1
7. Donnez le ou les emplois ayant le salaire moyen le plus bas, ainsi que ce salaire moyen
SELECT PROF, AVG(SAL) as moy2
FROM Employes
GROUP BY PROF
HAVING AVG(SAL) = (SELECT AVG(SAL) as moy
FROM Employes
GROUP BY PROF
ORDER BY moy ASC
LIMIT 1)
 Exercice 2

 Objectifs : - Créer une base de données.

- Relier les tables.
- Créer des requêtes SQL.

Soit le modèle relationnel suivant relatif à la gestion des notes annuelles d’une
promotion d’étudiants :

ETUDIANT (N°Etudiant, Nom, Prénom)

MATIERE (CodeMat, LibelléMat, CoeffMat)

EVALUER (N°Etudiant#, CodeMat#, Date_Evaluation, Note)

Remarques : Les clés primaires sont soulignées et les clés étrangères sont marquées par #
 Exercice 2 - Suite

Questions :

Créer les requêtes SQL suivantes :

1. Quel est le nombre total d’étudiants ?

2. Quelle est, parmi l’ensemble des notes, la note la plus haute et la note la plus basse ?

3. Quelles sont les notes de chaque étudiant dans chacune des matières ?

4. Quelles sont les moyennes par matière ?

On utilisera la requête de la question 3 comme table source

5. Quelle est la moyenne générale de chaque étudiant ?

On utilisera la requête de la question 3 comme table source

6. Quelle est la moyenne générale de la promotion ?

On utilisera la requête de la question 5 comme table source

7. Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la
promotion ?
On utilisera la requête de la question 5 comme table source
 Solution Exercice 2

Les Requêtes:
1 - Quel est le nombre total d'étudiants ?

SELECT COUNT(*) FROM ETUDIANT ;

2 - Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la
plus basse ?

SELECT MIN(Note) AS Note_Min, MAX(Note) AS Note_Max FROM EVALUER ;

3 - Quelles sont les notes de chaque étudiant dans chacune des matières?

--CREATE VIEW NoteEtuMat AS

SELECT ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat, Note AS NoteEtuMat

FROM EVALUER, MATIERE, ETUDIANT
WHERE [Link] = [Link]
AND EVALUER.N°Etudiant = ETUDIANT.N°Etudiant
 Solution Exercice 2

Les Requêtes:
4 - Quelles sont les moyennes par matière ? On utilisera la requête de la question 3 comme table source

SELECT LibelléMat, AVG(NoteEtuMat)

FROM NoteEtuMat
GROUP BY LibelléMat ;

5 - Quelle est la moyenne générale de chaque étudiant ? On utilisera la requête de la question 3 comme table
source
--CREATE VIEW MGETU AS
SELECT N°Etudiant, Nom, Prénom, SUM(NoteEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu
FROM NoteEtuMat
GROUP BY N°Etudiant

6 - Quelle est la moyenne générale de la promotion ? On utilisera la requête de la question 5 comme table
source (MGETU)

SELECT AVG(MgEtu)
FROM MGETU ;
 Solution Exercice 2

Les Requêtes:
7 - Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la
promotion ? On utilisera la requête de la question 5 comme table source (MGETU)

SELECT N°Etudiant, Nom, Prénom, MgEtu

FROM MGETU
WHERE MgEtu >= (SELECT AVG(MgEtu) FROM MGETU) ;
 Exercice 3

 Objectifs : - Créer une base de données.

- Relier les tables.
- Créer des requêtes SQL.

Soit la base de données « cinéma » dont le schéma relationnel est donné ci-dessous :

VILLE (CodePostal, NomVille )

CINEMA (NumCine, NomCine, Adresse, #CodePostal )
SALLE (NumSalle, Capacité, #NumCine )
FILM (NumFilm, Titre, Durée)
PROJECTION (#NumFilm, #NumSalle, NumSemaine, Nbentrees)

Remarques : Les clés primaires sont soulignées et les clés étrangères sont marquées par #
 Exercice 3 - Suite

Questions :

Créer les requêtes SQL suivantes :

1. Afficher les Titres des films dont la durée est supérieure ou égale à deux heures.

2. Donner les Noms des villes abritant un cinéma nommé « RIF »

3. Donner les Noms des cinémas situés à Tanger ou contenant au moins une salle de plus 100 places.

4. Afficher le Nom, adresse et ville des cinémas dans lesquels on joue le film « Hypnose » la semaine 18.

5. Lister les Numéro des films projetés dans toutes les salles

6. Quels sont les Titres des films qui n’ont pas été projetés ?
 Solution Exercice 3
Les Requêtes:
1. Afficher les Titres des films dont la durée est supérieure ou égale à deux heures.
SELECT NumFilm , Titre FROM FILM WHERE Duree >=2 ;

2. Donner les Noms des villes abritant un cinéma nommé « RIF » .

SELECT NomVille FROM VILLE, CINEMA
WHERE [Link] = [Link] AND NomCine=’RIF’) ;
OU
SELECT NomVille FROM VILLE
WHERE CodePostal IN (SELECT CodePostal FROM CINEMA WHERE NomCine=’RIF’) ;

3. Donner les Noms des cinémas situés à Tanger ou contenant au moins une salle de plus 100 places.
SELECT NomCine
FROM CINEMA AS C, SALLE AS S, VILLE AS V
WHERE [Link] = [Link] AND [Link]=[Link]
AND ([Link] = ‘Tanger’ OR [Link] >=100)
OU
SELECT NomCine
FROM CINEMA
WHERE CodePostal = (SELECT CodePostal FROM VILLE WHERE NomVille=’Tanger’)
OR NumCine IN (SELECT NumCine FROM SALLE WHERE Capacite >=100)
 Solution Exercice 3
Les Requêtes:

4 - Afficher le Nom, adresse et ville des cinémas dans lesquels on joue le film « Hypnose » la semaine 18.
SELECT [Link] , [Link] , [Link]
FROM CINEMA as C, VILLE as V, SALLE as S, PROJECTION as P, FILM as F
WHERE [Link]=[Link] AND [Link] = [Link] AND [Link] = [Link]
AND [Link]=[Link] AND [Link]=’Hypnose’ AND [Link]=18

OU

SELECT [Link] , [Link] , [Link]

FROM CINEMA as C, VILLE as V
WHERE [Link]=[Link]
AND [Link] IN (SELECT [Link]
FROM SALLE as S, FILM as F, PROJECTION as P
WHERE [Link]=[Link] AND [Link]=[Link]
AND [Link]=’Hypnose’ AND [Link]=18)
 Solution Exercice 3

Les Requêtes:

5 - Lister les films (Numéro des films) qui ont été projetés dans toutes les salles

//Au niveau de la table PROJECTION, la clé primaire est composée de #NumFilm et #NumSalle, et
alors on ne pourra pas avoir des doublons, cela veut dire qu’au niveau d’une salle un film n’est
projetée qu’une seule fois (pendant une seule semaine), et donc le nombre de fois que #NumFilm se
répète dans la table PROJECTION reflète le nombre de salles dans lesquelles le film a été projeté

SELECT NumFilm, //count(*)

FROM PROJECTION
GROUP BY NumFilm
HAVING count(*) = (SELECT count(*) FROM SALLE) // Nombre totale de salles

6 - Quels sont les Titres des films qui n’ont pas été projetés ?

SELECT Titre
FROM FILM
WHERE NumFilm NOT IN (SELECT NumFilm FROM PROJECTION)