Meisterhaftigkeit in SQL-Abfragen für die Studentenkursanalyse

Einführung

In diesem Projekt lernst du, wie du verschiedene SQL-Abfragen auf einer Studentenkurs-Datenbank ausführen kannst. Die Datenbank besteht aus drei Tabellen: der Studententabelle (S), der Kurs-Tabelle (C) und der Studenten-Kurs-Tabelle (SC). Du wirst lernen, wie du Daten aus diesen Tabellen abrufen und manipulieren kannst, um verschiedene Aufgaben zu erfüllen.

👀 Vorschau

SQL-Datenbank-Vorschau-Bild

🎯 Aufgaben

In diesem Projekt wirst du lernen:

Wie du die Namen aller Studenten aufzählst, die keine Kurse von Lehrer "Daniel" besucht haben
Wie du die Studentennummern und Namen von Studenten aufzählst, die in zwei oder mehr Kursen durchgefallen sind (Note < 60)
Wie du die Namen aller Studenten aufzählst, die sowohl Kurs "11" als auch Kurs "12" besucht haben
Wie du die Studentennummern aller Studenten aufzählst, deren Noten in Kurs "11" höher sind als in Kurs "12", und diese aufsteigend sortierst
Wie du die Namen aller Studenten aufzählst, die älter als "John Davis" sind
Wie du die Studentennummer des Studenten mit der höchsten Note unter allen Studenten aufzählst, die Kurs "12" besucht haben
Wie du die Namen aller Studenten zusammen mit ihren ausgewählten Kurs-IDs und Noten aufzählst
Wie du die Studentennummern und die Gesamtnoten (Alias: SG) von Studenten aufzählst, die vier oder mehr Kurse besucht haben, und diese absteigend nach Gesamtnoten sortierst

🏆 Errungenschaften

Nach Abschluss dieses Projekts wirst du in der Lage sein:

Zu verstehen, wie komplexe SQL-Abfragen geschrieben werden, um Daten aus einer relationalen Datenbank abzurufen und zu manipulieren
Verschiedene SQL-Techniken wie Subabfragen, Verknüpfungen, Aggregationen und Sortierungen anzuwenden, um reale Datenanalyseprobleme zu lösen
SQL-Fähigkeiten zu entwickeln, die für datengetriebene Entscheidungen und Problemlösungen unerlässlich sind

Starte MySQL und importiere Daten

In diesem Schritt lernst du, wie du den MySQL-Dienst startest und die erforderlichen Daten in die Datenbank importierst.

Starte den MySQL-Dienst mit dem sudo-Befehl ohne Passwort:

sudo service mysql start

Greife auf den MySQL-Client mit dem sudo-Befehl ohne Passwort zu:

sudo mysql

Importiere die Daten aus dem bereitgestellten personnel.sql-Skript in die MySQL-Datenbank:

SOURCE /home/labex/project/initdb.sql

Füge Abfragecode hinzu

In diesem Schritt lernst du, wie du die Namen aller Studenten aufzählst, die keine Kurse von Lehrer "Daniel" besucht haben. Folge den Schritten unten, um diesen Schritt abzuschließen:

Öffne die Datei answer.sql im Verzeichnis /home/labex/project.
Füge den SQL-Befehl unter dem Kommentar hinzu:
Locate the comment -- List the names of all students who have not taken courses taught by the "Daniel" teacher.
```
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
);
```
Dieser SQL-Befehl wählt zunächst die sname-Spalte aus der S-Tabelle. Anschließend verwendet er eine NOT EXISTS-Klausel, um zu überprüfen, ob es in der SC-Tabelle Datensätze gibt, deren Kurs-ID (cno) mit einem Kurs übereinstimmt, der von Lehrer "Daniel" unterrichtet wird, und die Studenten-ID (sno) mit dem aktuellen Studenten übereinstimmt. Wenn keine solchen Datensätze existieren, wird der Name des Studenten im Ergebnis enthalten sein.
Locate the comment -- List the student numbers and names of students who have failed (grade < 60) in two or more courses.
```
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;
```
Dieser SQL-Befehl verbindet zunächst die S- und SC-Tabellen, um die Studenteninformationen und ihre Kursnoten zu erhalten. Anschließend filtriert er die Datensätze, bei denen die Note kleiner als 60 ist, gruppiert die Ergebnisse nach Studentennummer und Name und wählt schließlich nur die Studenten aus, die in zwei oder mehr Kursen durchgefallen sind, mithilfe der HAVING-Klausel.
Locate the comment -- List the names of all students who have taken both course "11" and course "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;
```
Dieser SQL-Befehl verbindet zunächst die S- und SC-Tabellen, um die Studenteninformationen und ihre Kurs-IDs zu erhalten. Anschließend filtriert er die Datensätze, bei denen die Kurs-ID entweder 11 oder 12 ist, gruppiert die Ergebnisse nach Studentennummer und wählt schließlich nur die Studenten aus, die beide Kurse besucht haben, mithilfe der HAVING-Klausel. Schließlich sortiert er die Ergebnisse nach Studentennummer.
Locate the comment -- List the student numbers of all students whose grades in course "11" are higher than those in course "12", and sort them in ascending order.
```
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;
```
Dieser SQL-Befehl verbindet zunächst die SC-Tabelle mit sich selbst, um die Noteninformationen für sowohl Kurs 11 als auch Kurs 12 zu erhalten. Anschließend filtriert er die Datensätze, bei denen die Kurs-ID 11 ist und die Note höher ist als die Note für Kurs 12, und wählt die Studentennummer aus. Schließlich sortiert er die Ergebnisse nach Studentennummer.
Locate the comment -- List the names of all students who are older than "John Davis".
```
SELECT s.sname
FROM S s, (SELECT sage FROM S WHERE sname = 'John Davis') AS zs
WHERE s.sage > zs.sage;
```
Dieser SQL-Befehl wählt zunächst den sage-Wert für den Studenten mit dem Namen "John Davis" und speichert ihn in einer Unterabfrage zs. Anschließend wählt er den sname aus der S-Tabelle, bei dem das Alter des Studenten (sage) größer ist als das Alter von "John Davis".
Locate the comment -- List the student number of the student with the highest grade among all students who have taken course "12".
```
SELECT sc.sno
FROM SC sc
WHERE sc.cno = 12 AND sc.grade = (
  SELECT MAX(grade)
  FROM SC
  WHERE cno = 12
);
```
Dieser SQL-Befehl wählt zunächst die sno aus der SC-Tabelle, bei der die Kurs-ID 12 ist und die Note gleich der höchsten Note unter allen Studenten ist, die Kurs 12 besucht haben. Dies gibt uns die Studentennummer des Studenten mit der höchsten Note in Kurs 12.
Locate the comment -- List the names of all students, along with their selected course IDs and grades.
```
SELECT s.sname, sc.cno, sc.grade
FROM S s
LEFT JOIN SC sc ON s.sno = sc.sno;
```
Dieser SQL-Befehl führt eine linke Verknüpfung zwischen den S- und SC-Tabellen durch, was alle Studenten aus der S-Tabelle einschließt, auch wenn sie keine Datensätze in der SC-Tabelle haben. Dies gewährleistet, dass alle Studentennamen im Ergebnis enthalten sind, zusammen mit ihren Kurs-IDs und Noten (sofern vorhanden).
Locate the comment -- List the student numbers and total grades (alias as: SG) of students who have taken four or more courses, sorted in descending order of total grades.
```
SELECT sc.sno, SUM(sc.grade) AS SG
FROM SC sc
GROUP BY sc.sno
HAVING COUNT(sc.cno) >= 4
ORDER BY SG DESC;
```
Dieser SQL-Befehl gruppiert zunächst die SC-Tabelle nach Studentennummer (sno) und berechnet die Summe der Noten für jeden Studenten (alias SG). Anschließend filtert er die Ergebnisse, um nur die Studenten zu enthalten, die vier oder mehr Kurse besucht haben, mithilfe der HAVING-Klausel. Schließlich sortiert er die Ergebnisse nach den Gesamtnoten in absteigender Reihenfolge.

Führe das SQL-Skript aus

In diesem letzten Schritt wirst du das SQL-Skript ausführen, das du in den vorherigen Schritten erstellt hast.

Im MySQL-Client führe den folgenden Befehl aus, um das answer.sql-Skript auszuführen:

SOURCE /home/labex/project/answer.sql

Dies führt die SQL-Abfragen in der answer.sql-Datei aus und zeigt die Ergebnisse an.

Herzlichen Glückwunsch! Du hast das Projekt abgeschlossen. Du solltest die folgende Ausgabe sehen:

MariaDB [student]> SOURCE /home/labex/project/answer.sql
+----------------+
| sname          |
+----------------+
| Michael Brown  |
| William Wilson |
| Richard Taylor |
| Charles Thomas |
+----------------+
4 Zeilen in set (0.013 sec)

+-----+---------------+
| sno | sname         |
+-----+---------------+
|   1 | James Johnson |
|   6 | David Moore   |
+-----+---------------+
2 Zeilen in set (0.000 sec)

+----------------+
| sname          |
+----------------+
| James Johnson  |
| Michael Brown  |
| John Davis     |
| Robert Miller  |
| William Wilson |
| David Moore    |
| Richard Taylor |
+----------------+
7 Zeilen in set (0.000 sec)

+-----+
| sno |
+-----+
|   2 |
|   3 |
|   4 |
|   7 |
+-----+
4 Zeilen in set (0.001 sec)

+----------------+
| sname          |
+----------------+
| William Wilson |
| Richard Taylor |
+----------------+
2 Zeilen in set (0.013 sec)

+-----+
| sno |
+-----+
|   5 |
|   8 |
+-----+
2 Zeilen in set (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 Zeilen in set (0.001 sec)

+-----+------+
| sno | SG   |
+-----+------+
|   4 |  342 |
|   7 |  330 |
|   3 |  330 |
|   1 |  280 |
|   5 |  251 |
|   6 |  185 |
+-----+------+
6 Zeilen in set (0.000 sec)

Zusammenfassung

Herzlichen Glückwunsch! Du hast dieses Projekt abgeschlossen. Du kannst in LabEx weitere Übungen absolvieren, um deine Fähigkeiten zu verbessern.

SQL-Abfragen zur Studentenkursanalyse