Exercices gestion de bases de données relationnelles avec SQL

Soit le modèle relationnel suivant relatif à une base de données sur des représentations musicales :

REPRESENTATION (n°représentation, titre_représentation, lieu)

MUSICIEN (nom, n°représentation*)

PROGRAMMER (date, n°représentation*, tarif)

Remarque : les clés primaires sont soulignées et les clés étrangères sont marquées par *

Questions :

Requête 1 - Donner la liste des titres des représentations.

Requête 2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.

Requête 3 - Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent.

Requête 4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la journée du 14/09/96.

1 - Donner la liste des titres des représentations.

SELECT titre_représentation FROM REPRESENTATION ;

2 - Donner la liste des titres des représentations ayant lieu à l'opéra Bastille.

SELECT titre_représentation FROM REPRESENTATION

WHERE lieu="Opéra Bastille" ;

3 - Donner la liste des noms des musiciens et des titres des représentations auxquelles ils participent.

SELECT nom, titre_représentation

FROM MUSICIEN, REPRESENTATION

WHERE MUSICIEN.n°représentation = REPRESENTATION.n°représentation ;

4 - Donner la liste des titres des représentations, les lieux et les tarifs pour la journée du 14/09/96.

SELECT titre_représentation, lieu, tarif

FROM REPRESENTATION, PROGRAMMER

WHERE PROGRAMMER.n°représentation = REPRESENTATION.n°représentation

AND date='14/06/96' ;

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, Note)

Remarque : les clés primaires sont soulignées et les clés étrangères sont marquées par *

Questions :

Requête 1 - Quel est le nombre total d'étudiants ?

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

Requête 3 - Quelles sont les moyennes de chaque étudiant dans chacune des matières ?

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

Requête 5 - Quelle est la moyenne générale de chaque étudiant ?

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

Requête 6 - Quelle est la moyenne générale de la promotion ?

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

Requête 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 :

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), MAX(Note) FROM EVALUER ;

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

SELECT ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat, AVG(Note) AS MoyEtuMat

FROM EVALUER, MATIERE, ETUDIANT

WHERE EVALUER.CodeMat = MATIERE.CodeMat

AND EVALUER.N°Etudiant = ETUDIANT.N°Etudiant

GROUP BY ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat;

4 - Quelles sont les moyennes par matière ?

 SELECT LibelléMat, AVG(MoyEtuMat)

FROM MOYETUMAT

GROUP BY LibelléMat ;

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

SELECT N°Etudiant, Nom, Prénom, SUM(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu

FROM MOYETUMAT

GROUP BY N°Etudiant, Nom, Prénom ;

6 - Quelle est la moyenne générale de la promotion ? Avec la vue MGETU de la question 5 :

SELECT AVG(MgEtu)

FROM MGETU ;

7 - Quels sont les étudiants qui ont une moyenne générale supérieure ou égale à la moyenne générale de la promotion ?

Avec la vue MGETU de la question 5 :

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

FROM MGETU

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

Suite à l’exercice de Procédures Stockées

Objectif :

Créer des Fonctions  en SQL

Travail à Faire :

Créer une Procédure Stockée permettant d’effacer un fichier dont le nom du fichier est stocké dans la base de données

Solution :

1
2
3
4
5
6
7
8
9
10
11

--Créer une Procédure Stockée permettant d’effacer un fichier dont le nom du fichier est stocké dans la base de données

DECLARE @NomFichierImage AS NVarChar(250)

SELECT @ID = Pers.ID, @NomFichierImage= ISNULL(Pers.NomImage, '') 
FROM TABLEPersonne Pers 
WHERE Pers.Login LIKE '%' + @LoginCollab + '%'
IF (@NomFichierImage'')
BEGIN
 SET @NomFichierImage = 'DEL '\\NomDuServeur\CheminDuRepertoireDesPhotos\'+ @NomFichierImage +'"'
 PRINT @NomFichierImage
 EXEC master.dbo.xp_cmdshell @NomFichierImage;

END

Exercice création des requêtes SQL

Objectifs :

- 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 #


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 ?

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

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 :

Les requêtes SQL

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), MAX(Note) FROM EVALUER ; 

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

SELECT ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat, 
AVG(Note) AS MoyEtuMat 
FROM EVALUER, MATIERE, ETUDIANT 
WHERE EVALUER.CodeMat = MATIERE.CodeMat 
AND EVALUER.N°Etudiant = ETUDIANT.N°Etudiant 
GROUP BY ETUDIANT.N°Etudiant, Nom, Prénom, LibelléMat, CoeffMat; 

4 - Quelles sont les moyennes par matière ?

SELECT LibelléMat, AVG(MoyEtuMat) 
FROM MOYETUMAT 
GROUP BY LibelléMat ; 

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

SELECT N°Etudiant, Nom, Prénom, 
SUM(MoyEtuMat*CoeffMat)/SUM(CoeffMat) AS MgEtu 
FROM MOYETUMAT 
GROUP BY N°Etudiant, Nom, Prénom ; 

6 - Quelle est la moyenne générale de la promotion ? 
Avec la vue MGETU de la question 5 :

SELECT AVG(MgEtu) 
FROM MGETU ; 

7 - Quels sont les étudiants qui ont une moyenne générale supérieure ou
égale à la moyenne générale de la promotion ?
Avec la vue MGETU de la question 5 :

SELECT N°Etudiant, Nom, Prénom, MgEtu 
FROM MGETU 
WHERE MgEtu >= (SELECT AVG(MgEtu) FROM MGETU) ;

Exercice requete SQL corrigé (Tour de France)

Soit le modèle relationnel suivant relatif à la gestion simplifiée des étapes du Tour de France 97, dont une des étapes de type "contre la montre individuel" se déroula à Saint-Etienne :

EQUIPE(CodeEquipe, NomEquipe, DirecteurSportif)

COUREUR(NuméroCoureur, NomCoureur, CodeEquipe*, CodePays*)

PAYS(CodePays, NomPays)

TYPE_ETAPE(CodeType, LibelléType)

ETAPE(NuméroEtape, DateEtape, VilleDép, VilleArr, NbKm, CodeType*)

PARTICIPER(NuméroCoureur*, NuméroEtape*, TempsRéalisé)

ATTRIBUER_BONIFICATION(NuméroEtape*, km, Rang, NbSecondes, NuméroCoureur*)

Remarque : les clés primaires sont soulignées et les clés étrangères sont marquées par *

Questions :

Requête 1 - Quelle est la composition de l'équipe Festina (Numéro, nom et pays des coureurs) ?

Requête 2 - Quel est le nombre de kilomètres total du Tour de France 97 ?

Requête 3 - Quel est le nombre de kilomètres total des étapes de type "Haute Montagne"?

Requête 4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?

Requête 5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?

Requête 6 - Quel est le classement général des coureurs (nom, code équipe, code pays et temps des coureurs) à l'issue des 13 premières étapes sachant que les bonifications ont été intégrées dans les temps réalisés à chaque étape ?

Requête 7 - Quel est le classement par équipe à l'issue des 13 premières étapes (nom et temps des équipes) ?

Solution :

1 - Quelle est la composition de l'équipe FESTINA (Numéro, nom et pays des coureurs) ?

SELECT NuméroCoureur, NomCoureur, NomPays

FROM EQUIPE A, COUREUR B, PAYS C

WHERE A.CodeEquipe=B.CodeEquipe And B.CodePays=C.CodePays And NomEquipe="FESTINA" ;

2 - Quel est le nombre de kilomètres total du Tour de France 97 ?

SELECT SUM(Nbkm)

FROM ETAPE ;

3 - Quel est le nombre de kilomètres total des étapes de type HAUTE MONTAGNE ?

SELECT SUM(Nbkm)

FROM ETAPE A, TYPE_ETAPE B

WHERE A.CodeType=B.CodeType And LibelléType="HAUTE MONTAGNE" ;

4 - Quels sont les noms des coureurs qui n'ont pas obtenu de bonifications ?

SELECT NomCoureur FROM COUREUR

WHERE NuméroCoureur NOT IN (SELECT NuméroCoureur

FROM ATTRIBUER_BONIFICATION) ;

5 - Quels sont les noms des coureurs qui ont participé à toutes les étapes ?

SELECT NomCoureur

FROM PARTICIPER A, COUREUR B

WHERE A.NuméroCoureur=B.NuméroCoureur

GROUP BY NuméroCoureur, NomCoureur

HAVING COUNT(*)=(SELECT COUNT(*) FROM ETAPE) ;

6 - Quel est le classement général des coureurs (nom, code équipe, code pays et temps des coureurs) à l'issue des 13 premières étapes sachant que les bonifications ont été intégrées dans les temps réalisés à chaque étape ? SELECT NomCoureur, CodeEquipe, CodePays, SUM(TempsRéalisé) AS Total

FROM PARTICIPER A, COUREUR B

WHERE A.NuméroCoureur=B.NuméroCoureur and NuméroEtape<=13

GROUP BY A.NuméroCoureur, NomCoureur, CodeEquipe, CodePays

ORDER BY Total;

7 - Quel est le classement par équipe à l'issue des 13 premières étapes (nom et temps des équipes) ?

SELECT NomEquipe, SUM(TempsRéalisé) AS Total

FROM PARTICIPER A, COUREUR B, EQUIPE C

WHERE A.NuméroCoureur=B.NuméroCoureur And B.CodeEquipe=C.CodeEquipe

And NuméroEtape<=13

GROUP BY B.CodeEquipe, NomEquipe

ORDER BY Total;

Exercice Langage SQL : Les prédicats NULL, IN, LIKE, BETWEEN

Suite 2 de l'Exercice récapitulatif en Langage SQL

Objectif :

Créer des requêtes enSQL

Travail à faire:

À partir de la Base de données ETUDIANTS cité dans l’exercice précédent (Exercice récapitulatif en Langage SQL), écrire les requêtes SQL permettant de répondre aux questions suivantes.:

Requête 1 :

Numéro, nom et sexe des étudiants dont la date de naissance n'est pas connue

Requête 2 :

Nom et date de naissance des étudiants dont la 2° lettre du nom est 'a'.

Requête 3 :

Nom des matières qui ont un coefficient compris entre 1 et 2.

Solution :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

Requête 1 : 

SELECT Numetu, Nometu
FROM ETUDIANT
WHERE Dtnaiss IS NULL;

Requête 2 : 

SELECT Nometu, Dtnaiss
FROM ETUDIANT
WHERE Nometu LIKE '_a%';

Requête 3 : 

SELECT Nomat
FROM MATIERE
WHERE Coeff BETWEEN 1 AND 2;
Ce prédicat, est une simplication d"'"écriture et peut être remplacé par :
SELECT Nomat
FROM MATIERE
WHERE (Coeff >=1) AND (Coeff<=2);

Exercice Langage SQL : Les prédicats NULL, IN, LIKE, BETWEEN

Suite 2 de l'Exercice récapitulatif en Langage SQL

Objectif :

Créer des requêtes enSQL

Travail à faire:

À partir de la Base de données ETUDIANTS cité dans l’exercice précédent (Exercice récapitulatif en Langage SQL), écrire les requêtes SQL permettant de répondre aux questions suivantes.:

Requête 1 :

Numéro, nom et sexe des étudiants dont la date de naissance n'est pas connue

Requête 2 :

Nom et date de naissance des étudiants dont la 2° lettre du nom est 'a'.

Requête 3 :

Nom des matières qui ont un coefficient compris entre 1 et 2.