Transcript EXERCICES REQUETES SQL

Terminale STMG-GSI - Janvier 2014
EXERCICES REQUETES SQL
Classe
Enseignement
1
n
Professeur
n
1
1
n
Eleve
Eleve (num, nom,prenom, dateNaiss, sexe,codeClasse)
Clé primaire : num
Clé étrangère : codeClasse en référence à code de la relation classe
Professeur (num, nom, prenom, matiere)
Clé primaire : num
Classe (code, libelle)
Clé primaire : code
Enseignement (numProf, codeClasse, nbHeures)
Clé primaire : numProf, codeClasse
Clés étrangères : numProf en référence à num de la relation Professeur
codeClasse en référence à code de la relation classe
Page 1 sur 3
1.
PS :
Ecrire la requête permettant de créer la table « Professeur »
Les différents types de données à utiliser sont :
« varchar » pour les données de type hexadécimal
« integer » pour les données de type numérique
« date » pour les données de type date
Préciser « auto_increment » pour les champs de type clé primaire
Préciser « NOT NULL » pour chaque champ qui doit forcément comporter une valeur
Create table professeur (num integer auto_increment, nom varchar(30) NOT NULL, prenom
varchar(30) NOT NULL, matiere varchar(40) NOT NULL, PRIMARY KEY(num))
2.
A l’aide du Memento en annexe, écrire la requête permettant d’ajouter le champ Ville de Naissance
« villeNaiss » de type varchar à la table « eleve » (ce champ pouvant ne pas être renseigné)
ALTER TABLE eleve ADD villeNaiss varchar
3.
Ecrire la requête d’insertion de l’élève : DEUF John (homme, né le 12/02/1992) en TleSTMG
INSERT INTO eleve (‘DEUF’, ‘John’, ‘12/02/1992’, ‘H’, ‘TleSTMG’)
4.
Ecrire la requête de mise à jour ; l’éleve «TI Jean» (num 236) a changé de classe et est passé en 1ere STMG
UPDATE eleve SET codeClasse= ‘1ereSTMG’
WHERE num=236
Ecrire les requêtes d’interrogation permettant d’obtenir :
5.
La liste de tous les élèves de l’école (avec toutes leurs données)
SELECT * ou SELECT ALL
FROM eleve
6.
La liste de toutes les matières enseignées dans l’école
(attention : une même matière ne doit apparaître qu’une seule fois dans la liste)
SELECT DISTINCT matiere
FROM professeur
7.
La liste de tous les professeurs de Français (Nom et Prenom) classée par ordre alphabétique Décroissant
SELECT *
FROM professeur
WHERE matiere=”français”
ORDER BY nom, prenom DESC
8.
La matière enseignée par le professeur Nadia Felmin (num 4)
SELECT matiere
FROM professeur
WHERE num=4
Page 2 sur 3
9.
La liste des élèves portant le prénom Jean, Jacques, Pierre (requête la plus simple possible)
SELECT nom, prenom, codeClass
FROM eleve
WHERE prenom IN (‘Jean, ‘Jacques, ‘Pierre)
10. La liste des élèves dont le prénom commence par N, avec leur classe
SELECT nom, prenom, codeClass
FROM eleve
WHERE nom LIKE ‘N%’
11. Le nombre d’élèves de terminale STMG (code TleSTMG’)
SELECT COUNT(num)
FROM eleve
WHERE codeClasse= ‘TleSTMG’
12. La liste des classes de chaque professeur (nom, prénom, classe) triée par ordre alphabétique des
professeurs.
SELECT nom, prenom, codeClasse
FROM professeur, enseignement
WHERE professeur.num = enseignement.numProf
ORDER BY nom, prenom
13. La liste des classes ainsi que le nombre d’élèves de chaque classe, triée par classe
SELECT codeClasse, COUNT(num)
FROM eleve, classe
WHERE eleve.codeClasse=classe.code
GROUP BY codeClass
ORDER BY codeClasse
14. La liste des élèves nés en 1997 (requête le plus simple possible) triée par nom
SELECT nom, prenom
FROM eleve
WHERE dateNaiss BETWEEN ’01-01-1997’ AND ’31-12-1997’
15. L’âge de l’élève le plus âgé de l’école
SELECT MAX(dateNaiss)
FROM eleve
Page 3 sur 3