Exercices gestion de bases de données relationnelles avec SQL

Rédigé par GC Team, Publié le 26 Mars 2011, Mise à jour le Jeudi, 12 Août 2021 19:39

Participez au vote ☆☆☆☆☆★★★★★

Exercice algèbre relationnelle SQL dépendeances fonctionnelles normalisation

Exercice 1 :

A l'aide d'un exemple, expliquer comment remplacer une association ternaire par un modèle équivalent sans relation ternaire. Donner la traduction relationnelles des 2 modélisation.

Exercice 2

Expliquer la notion de contrainte d'intégrité du modèle relationnel. Citer et expliquer brièvement les différents types de contraintes dans le modèle relationnel : celles présentes dans le modèle et celles annexées à celui-ci.

Exercice 3

Déf inir l'opération de produit cartésien en algèbre relationnelle. Expliquer le lien entre cette opération et la jointure, la jointure naturelle et la jointure externe.

Exercice 4

Expliquer la séquence intuitive d'exécution (sémantique opérationnelle) d'une requête de type :

SELECT(...)FROM(...)WHERE(...)GROUPBY(...)HAVING(...)

Exercice 5

Défi nir la notion de dépendance multi-valuée. Défi nir également la quatrième forme normale.
Expliquer le lien entre les dépendances multi-valuées et la première forme normale.

Exercice sur les Requêtes SQL Corrigé ( presentation musicales )

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.

Solution :

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' ;

Exercice sur les Requêtes SQL Corrigé ( presentation musicales )

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) ;

Exercice Langage SQL : Procédure Stockée

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 :


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

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 :

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 :

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:

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.