• Accueil|
• Feuille de route|
• Manuel du cours

Le langage SQL

Vérifier et pratiquer

Présentation

Dans cette activité, nous vous proposons une série de questions. Le questionnaire vous permettra de consolider vos connaissances et de revenir, au besoin, sur certains passages des trois textes que vous avez étudiés au cours de l’activité précédente.

Étape par étape…

1. Accédez au Questionnaire sur le langage SQL.
2. Répondez aux questions et analysez attentivement vos réponses en vous demandant : « Quels sont les arguments qui font que cette réponse pourrait être la bonne ? » et « Quels sont les arguments qui font que cette réponse pourrait être une mauvaise réponse ? »
3. Vérifiez votre réponse en cliquant sur le bouton Solution. Pour chaque question, la réponse et une explication sont fournies.
4. Terminez le travail avec l’activité d’autoévaluation suivante.

Autoévaluation

Pour vous préparez un travail noté, faites l’activité qui suit.

Soit le modèle relationnel suivant :

Etudiant (numetu_, nom, prenom, datenaiss, rue, cp, ville)
Matiere (codemat_, libelle, coeff)
Epreuve (numepreuve_, dateepreuve, lieu, codemat#) 
Notation (numetu#, numepreuve#, note) 

(Le symbole # indique une clef étrangère alors que le symbole _ indique une clef primaire.)

La valeur de coeff est un nombre de 0 à 1 indiquant
le poids de la matière correspondante dans le calcul
de la note globale.

Répondez aux questions suivantes avec la requête SQL correspondante.

Question 1 : Contenu de la table etudiant

Question 2 : Contenu de la table etudiant, classé par ordre alphabétique inverse des noms d’étudiants (Indice : on peut utiliser la syntaxe ORDER BY)

Question 3 : Libellé et coefficient (exprimé en pourcentage) de chaque matière

Question 4 : Nom et prénom de chaque étudiant

Question 5 : Nom et prénom des étudiants domiciliés à Lyon

Question 6 : Liste des notes supérieures ou égales à 10

Question 7 : Liste des épreuves dont la date se situe entre le 1er janvier et le 30 juin 2004 (on peut utiliser la syntaxe BETWEEN AND et représenter le 1er janvier sous la forme 2004-01-01)

Question 8 : Nom, prénom et ville des étudiants dont la ville contient la chaîne "ll" (Indice : on peut utiliser l’instruction LIKE)

Question 9 : Prénoms des étudiants de nom Dupont, Durand ou Martin (Indice : on peut utiliser l’instruction IN)

Question 10 : Somme des coefficients de toutes les matières (indice : la somme est calculée avec la fonction SUM)

Question 11 : Nombre total d’épreuves (indice : on dénombre les enregistrements avec la fonction COUNT)

Question 12 : Nombre de notes indéterminées (NULL) (indice : on vérifie qu’une valeur est nulle est l’expression IS NULL)

Question 13 : Liste des épreuves (numéro, date et lieu) incluant le libellé de la matière (Indice : il faut faire une jointure)

Question 14 : Liste des notes en précisant pour chacune le nom et le prénom de l’étudiant qui l’a obtenue

Question 15 : Liste des notes en précisant pour chacune le nom et le prénom de l’étudiant qui l’a obtenue et le libellé de la matière concernée
(Indice : il faut faire trois jointures)

Question 16 : Nom et prénom des étudiants qui ont obtenu au moins une note égale à 20

Question 17 : Moyennes des notes de chaque étudiant (indiquer le nom et le prénom)
(Indice : On peut utiliser la fonction AVG pour calculer la moyenne)

Question 18 : Moyennes des notes de chaque étudiant (indiquer le nom et le prénom), classées de la meilleure à la moins bonne (on peut utiliser la syntaxe AS)

Question 19 : Moyennes des notes pour les matières (indiquer le libellé) comportant plus d’une épreuve (Indice : il faut utiliser un GROUP BY ... HAVING ainsi que COUNT et DISTINCT)

Question 20 : Moyennes des notes obtenues aux épreuves (indiquer le numéro d’épreuve) où moins de 6 étudiants ont été notés

Question 21 : Étant donné les tuples suivants :

Quelle est la différence entre

SELECT nom, sum(salaire) FROM table WHERE salaire >3 GROUP BY nom ;

et

SELECT nom, sum(salaire) FROM table GROUP BY nom HAVING SUM(salaire) >3 ;

 ?

Question 22 : Étant donné les tuples suivants :

Trouvez le nom de tous les individus ayant un salaire supérieur à la moyenne.

Question 23 : Étant donné les tuples suivants :

Trouvez le nom de tous les individus ayant un salaire maximal.

Question 24 : (Question avancée) En supposant que l’instruction « except » retourne la différence entre deux tables en SQL (ce qui est le cas avec SQLite, Oracle préférant Minus), donnez une instruction SQL qui retourne « vide » si la table est vide (COUNT(*)=0) et « non vide » autrement. Le nom de la table est « test ».

Ce questionnaire est basé en partie sur un tutoriel mis au point par Jérôme Darmont de Lyon 2.

Autres exercices

On trouve sans mal d’autres activités d’apprentissage sur le Web. Par exemple, SQL Zoo est un site populaire pour approfondir sa compréhension du SQL.