Maîtriser les requêtes SQL pour l'analyse des cours des étudiants

Introduction

Dans ce projet, vous allez apprendre à effectuer diverses requêtes SQL sur une base de données d'étudiants et de cours. La base de données est composée de trois tables : la table Étudiant (S), la table Cours (C) et la table Étudiant - Cours (SC). Vous allez apprendre à récupérer et à manipuler les données de ces tables pour accomplir différentes tâches.

👀 Aperçu

Image d'aperçu de la base de données SQL

🎯 Tâches

Dans ce projet, vous allez apprendre :

Comment lister les noms de tous les étudiants qui n'ont pas suivi de cours enseignés par le professeur "Daniel"
Comment lister les numéros d'étudiant et les noms d'étudiants qui ont échoué (note < 60) dans deux cours ou plus
Comment lister les noms de tous les étudiants qui ont suivi à la fois le cours "11" et le cours "12"
Comment lister les numéros d'étudiant de tous les étudiants dont la note dans le cours "11" est supérieure à celle dans le cours "12", et les trier par ordre croissant
Comment lister les noms de tous les étudiants qui sont âgés de plus de "John Davis"
Comment lister le numéro d'étudiant de l'étudiant ayant la meilleure note parmi tous ceux qui ont suivi le cours "12"
Comment lister les noms de tous les étudiants, ainsi que leurs identifiants de cours sélectionnés et leurs notes
Comment lister les numéros d'étudiant et les notes totales (alias : SG) d'étudiants qui ont suivi quatre cours ou plus, triés par ordre décroissant de notes totales

🏆 Réalisations

Après avoir terminé ce projet, vous serez capable de :

Comprendre comment écrire des requêtes SQL complexes pour récupérer et manipuler des données dans une base de données relationnelle
Appliquer diverses techniques SQL, telles que les sous - requêtes, les jointures, les agrégations et le tri, pour résoudre des problèmes d'analyse de données du monde réel
Développer des compétences SQL essentielles pour la prise de décision et la résolution de problèmes axées sur les données

Démarrer MySQL et importer des données

Dans cette étape, vous allez apprendre à démarrer le service MySQL et à importer les données nécessaires dans la base de données.

Démarrez le service MySQL en utilisant la commande sudo sans mot de passe :

sudo service mysql start

Accédez au client MySQL en utilisant la commande sudo sans mot de passe :

sudo mysql

Importez les données à partir du script personnel.sql fourni dans la base de données MySQL :

SOURCE /home/labex/project/initdb.sql

Ajouter le code de requête

Dans cette étape, vous allez apprendre à lister les noms de tous les étudiants qui n'ont pas suivi de cours enseignés par le professeur "Daniel". Suivez les étapes ci - dessous pour terminer cette étape :

Ouvrez le fichier answer.sql dans le répertoire /home/labex/project.
Ajoutez l'instruction SQL sous le commentaire :
Localisez le commentaire -- Lister les noms de tous les étudiants qui n'ont pas suivi de cours enseignés par le professeur "Daniel".
```
SELECT s.sname
FROM S s
WHERE NOT EXISTS (
  SELECT 1
  FROM SC sc
  JOIN C c ON sc.cno = c.cno
  WHERE c.tname = 'Daniel' AND sc.sno = s.sno
);
```
Cette instruction SQL sélectionne d'abord la colonne sname de la table S. Elle utilise ensuite une clause NOT EXISTS pour vérifier s'il existe des enregistrements dans la table SC dont l'identifiant de cours (cno) correspond à un cours enseigné par le professeur "Daniel" et l'identifiant d'étudiant (sno) correspond à l'étudiant actuel. Si aucun tel enregistrement n'existe, le nom de l'étudiant sera inclus dans le résultat.
Localisez le commentaire -- Lister les numéros d'étudiant et les noms d'étudiants qui ont échoué (note < 60) dans deux cours ou plus.
```
SELECT s.sno, s.sname
FROM S s
JOIN SC sc ON s.sno = sc.sno
WHERE sc.grade < 60
GROUP BY s.sno, s.sname
HAVING COUNT(sc.sno) >= 2;
```
Cette instruction SQL joint d'abord les tables S et SC pour obtenir les informations sur les étudiants et leurs notes de cours. Elle filtre ensuite les enregistrements où la note est inférieure à 60, regroupe les résultats par numéro d'étudiant et nom, et sélectionne finalement seulement les étudiants qui ont échoué dans deux cours ou plus en utilisant la clause HAVING.
Localisez le commentaire -- Lister les noms de tous les étudiants qui ont suivi à la fois le cours "11" et le cours "12".
```
 SELECT s.sname
 FROM S s
 JOIN SC sc ON s.sno = sc.sno
 WHERE sc.cno IN (11, 12)
 GROUP BY s.sno
 HAVING COUNT(DISTINCT sc.cno) = 2
 ORDER BY s.sno;
```
Cette instruction SQL joint d'abord les tables S et SC pour obtenir les informations sur les étudiants et leurs identifiants de cours. Elle filtre ensuite les enregistrements où l'identifiant de cours est soit 11 soit 12, regroupe les résultats par numéro d'étudiant, et sélectionne seulement les étudiants qui ont suivi les deux cours en utilisant la clause HAVING. Enfin, elle trie les résultats par numéro d'étudiant.
Localisez le commentaire -- Lister les numéros d'étudiant de tous les étudiants dont les notes dans le cours "11" sont supérieures à celles dans le cours "12", et les trier par ordre croissant.
```
SELECT sc11.sno
FROM SC sc11
JOIN SC sc12 ON sc11.sno = sc12.sno AND sc12.cno = 12
WHERE sc11.cno = 11 AND sc11.grade > sc12.grade
ORDER BY sc11.sno;
```
Cette instruction SQL joint d'abord la table SC avec elle - même pour obtenir les informations sur les notes des cours 11 et 12. Elle filtre ensuite les enregistrements où l'identifiant de cours est 11 et la note est supérieure à la note du cours 12, et sélectionne le numéro d'étudiant. Enfin, elle trie les résultats par numéro d'étudiant.
Localisez le commentaire -- Lister les noms de tous les étudiants qui sont plus âgés que "John Davis".
```
SELECT s.sname
FROM S s, (SELECT sage FROM S WHERE sname = 'John Davis') AS zs
WHERE s.sage > zs.sage;
```
Cette instruction SQL sélectionne d'abord la valeur de sage pour l'étudiant nommé "John Davis" et la stocke dans une sous - requête zs. Elle sélectionne ensuite le sname de la table S où l'âge de l'étudiant (sage) est supérieur à l'âge de "John Davis".
Localisez le commentaire -- Lister le numéro d'étudiant de l'étudiant ayant la meilleure note parmi tous ceux qui ont suivi le cours "12".
```
SELECT sc.sno
FROM SC sc
WHERE sc.cno = 12 AND sc.grade = (
  SELECT MAX(grade)
  FROM SC
  WHERE cno = 12
);
```
Cette instruction SQL sélectionne d'abord le sno de la table SC où l'identifiant de cours est 12 et la note est égale à la note maximale parmi tous les étudiants qui ont suivi le cours 12. Cela nous donnera le numéro d'étudiant de l'étudiant ayant la meilleure note dans le cours 12.
Localisez le commentaire -- Lister les noms de tous les étudiants, ainsi que leurs identifiants de cours sélectionnés et leurs notes.
```
SELECT s.sname, sc.cno, sc.grade
FROM S s
LEFT JOIN SC sc ON s.sno = sc.sno;
```
Cette instruction SQL effectue un jointure gauche entre les tables S et SC, ce qui inclura tous les étudiants de la table S, même s'ils n'ont aucun enregistrement dans la table SC. Cela garantira que tous les noms d'étudiants sont inclus dans le résultat, ainsi que leurs identifiants de cours et leurs notes (s'ils en ont).
Localisez le commentaire -- Lister les numéros d'étudiant et les notes totales (alias : SG) d'étudiants qui ont suivi quatre cours ou plus, triés par ordre décroissant de notes totales.
```
SELECT sc.sno, SUM(sc.grade) AS SG
FROM SC sc
GROUP BY sc.sno
HAVING COUNT(sc.cno) >= 4
ORDER BY SG DESC;
```
Cette instruction SQL regroupe d'abord la table SC par numéro d'étudiant (sno) et calcule la somme des notes pour chaque étudiant (aliassée en SG). Elle filtre ensuite les résultats pour n'inclure que les étudiants qui ont suivi quatre cours ou plus en utilisant la clause HAVING. Enfin, elle trie les résultats par les notes totales par ordre décroissant.

Exécuter le script SQL

Dans cette dernière étape, vous allez exécuter le script SQL que vous avez créé dans les étapes précédentes.

Dans le client MySQL, exécutez la commande suivante pour exécuter le script answer.sql :

SOURCE /home/labex/project/answer.sql

Cela exécutera les requêtes SQL dans le fichier answer.sql et affichera les résultats.

Félicitations! Vous avez terminé le projet. Vous devriez voir la sortie suivante :

MariaDB [student]> SOURCE /home/labex/project/answer.sql
+----------------+
| sname          |
+----------------+
| Michael Brown  |
| William Wilson |
| Richard Taylor |
| Charles Thomas |
+----------------+
4 lignes sélectionnées (0,013 sec)

+-----+---------------+
| sno | sname         |
+-----+---------------+
|   1 | James Johnson |
|   6 | David Moore   |
+-----+---------------+
2 lignes sélectionnées (0,000 sec)

+----------------+
| sname          |
+----------------+
| James Johnson  |
| Michael Brown  |
| John Davis     |
| Robert Miller  |
| William Wilson |
| David Moore    |
| Richard Taylor |
+----------------+
7 lignes sélectionnées (0,000 sec)

+-----+
| sno |
+-----+
|   2 |
|   3 |
|   4 |
|   7 |
+-----+
4 lignes sélectionnées (0,001 sec)

+----------------+
| sname          |
+----------------+
| William Wilson |
| Richard Taylor |
+----------------+
2 lignes sélectionnées (0,013 sec)

+-----+
| sno |
+-----+
|   5 |
|   8 |
+-----+
2 lignes sélectionnées (0,000 sec)

+-----------------+------+-------+
| sname           | cno  | grade |
+-----------------+------+-------+
| James Johnson   |   11 |    50 |
| James Johnson   |   12 |    80 |
| James Johnson   |   13 |    50 |
| James Johnson   |   14 |    50 |
| James Johnson   |   15 |    50 |
| Michael Brown   |   11 |    70 |
| Michael Brown   |   12 |    65 |
| Michael Brown   |   15 |    70 |
| John Davis      |   11 |    90 |
| John Davis      |   12 |    70 |
| John Davis      |   14 |    80 |
| John Davis      |   15 |    90 |
| Robert Miller   |   11 |    80 |
| Robert Miller   |   12 |    50 |
| Robert Miller   |   13 |    70 |
| Robert Miller   |   14 |    62 |
| Robert Miller   |   15 |    80 |
| William Wilson  |   11 |    40 |
| William Wilson  |   12 |    90 |
| William Wilson  |   13 |    60 |
| William Wilson  |   15 |    61 |
| David Moore     |   11 |    30 |
| David Moore     |   12 |    50 |
| David Moore     |   13 |    40 |
| David Moore     |   14 |    30 |
| David Moore     |   15 |    35 |
| Richard Taylor  |   11 |    90 |
| Richard Taylor  |   12 |    80 |
| Richard Taylor  |   13 |    80 |
| Richard Taylor  |   15 |    80 |
| Joseph Anderson |   12 |    90 |
| Joseph Anderson |   14 |    70 |
| Joseph Anderson |   15 |    95 |
| Charles Thomas  | NULL |  NULL |
+-----------------+------+-------+
34 lignes sélectionnées (0,001 sec)

+-----+------+
| sno | SG   |
+-----+------+
|   4 |  342 |
|   7 |  330 |
|   3 |  330 |
|   1 |  280 |
|   5 |  251 |
|   6 |  185 |
+-----+------+
6 lignes sélectionnées (0,000 sec)

Résumé

Félicitations! Vous avez terminé ce projet. Vous pouvez pratiquer plus de laboratoires sur LabEx pour améliorer vos compétences.

Requêtes SQL pour l'analyse des cours des étudiants