Aufgabe 3 - SQL-Abfragen · Abitur NRW Informatik LK · Nachtermin 2021
Thema: SQL-Abfragen · Aus der Prüfung LK · Nachtermin (2021)
Aufgabenstellung
Aufgabenstellung:
Das Unternehmen GoBigOrGoHome24 will die Gesundheit seiner Belegschaft fördern, indem es zur regelmäßigen Teilnahme an Sportangeboten anregt. Die Beschäftigten wählen aus einem breiten Angebot verschiedener Sportarten und melden sich an. Ein Sportangebot umfasst eine zuvor festgelegte Zahl an Einheiten, die zu absolvieren sind. Nimmt eine Mitarbeiterin oder ein Mitarbeiter an allen Einheiten teil, übernimmt die Firma die Kosten des Angebots in voller Höhe. Fehlt jemand unentschuldigt, verringert sich der Zuschuss der Firma. Die Länge einer Sporteinheit wird in Minuten angegeben.
Um die Daten zu verwalten, plant die Firma den Aufbau einer relationalen Datenbank, die auf der folgenden Teilmodellierung beruht:
Die folgenden Aufgaben beziehen sich auf das Datenbankschema aus Abbildung 2, das nur einen Teil der gesamten Datenbank abbildet. Beispieldaten dazu finden sich im Anhang.
Anlage: Ausschnitt aus den Beispieldaten zum Datenbankschema aus Abbildung 2
Teilnehmer
ID · Vorname · Nachname
1 · Ada · Lovelace
2 · John · von Neumann
3 · Alan · Turing
4 · Konrad · Zuse
5 · Tim · Berners-Lee
Sportangebot
ID · Sportart · Trainername
42 · Zumba · Joseph Weizenbaum
101 · Fitness · Gottfried Wilhelm Leibniz
2020 · Fitness · Gottfried Wilhelm Leibniz
2021 · Yoga · Joseph Weizenbaum
Sporteinheit
ID · Datum · Thema · Dauer · SportangebotID
1 · 2020-08-13 · Ausdauer Laufband · 90 · 101
2 · 2020-08-13 · Grundschritte · 45 · 42
3 · 2020-08-14 · Tanzausdauer · 45 · 42
4 · 2020-08-17 · Zirkeltraining · 90 · 101
5 · 2020-08-20 · Ausdauer Fahrrad · 90 · 101
6 · 2020-08-20 · Spezialschritte · 45 · 42
7 · 2020-08-21 · Intervalltanzen · 45 · 42
8 · 2020-08-24 · Kräftigung des Rumpfes · 90 · 101
9 · 2020-08-27 · Eigene Tanzchoreographie · 45 · 42
angemeldet
TeilnehmerID · SportangebotID
1 · 42
1 · 101
1 · 2020
2 · 42
3 · 42
3 · 101
4 · 101
5 · 101
fehlt
TeilnehmerID · SporteinheitID · istEntschuldigt
1 · 1 · ja
1 · 2 · ja
3 · 9 · ja
4 · 8 · nein
5 · 4 · ja
5 · 5 · nein
Teilaufgabe b) [11 Punkte]
Es sollen die folgenden Informationen aus der Datenbank abgerufen werden:
Entwerfen Sie zu jeder der oben genannten Anfragen eine passende SQL-Anweisung.
i.
Ermittelt werden sollen die Namen sämtlicher Trainerinnen und Trainer zusammen mit der Zahl der Sportangebote, die sie jeweils durchführen. Die Ausgabe soll aufsteigend nach dem Namen der Trainerinnen und Trainer geordnet sein. [3 Punkte]
ii.
Gesucht wird eine Übersicht über die Teilnehmerinnen und Teilnehmer, die Sporteinheiten versäumt haben. Für jede Person sollen Vorname, Nachname und die Anzahl ihrer versäumten Sporteinheiten ausgegeben werden. [4 Punkte]
iii.
Ermitteln sollen Sie Vor- und Nachname aller Personen, die zu mindestens einem Sportangebot angemeldet sind und bisher nie gefehlt haben. [4 Punkte]
Teilaufgabe c) [8 Punkte]
Gegeben ist die folgende SQL-Anweisung:
1 SELECT *
2 FROM (
3 SELECT Sportangebot.Sportart, Sportangebot.Trainername,
4 COUNT(angemeldet.SportangebotID) as X
5 FROM Sportangebot
6 LEFT JOIN angemeldet
7 ON Sportangebot.ID = angemeldet.SportangebotID
8 GROUP BY Sportangebot.ID
9) AS A
10 WHERE A.X >= 2
11 ORDER BY A.X DESC
1.
Analysieren und erläutern Sie erst die Unterabfrage (Zeilen 3 bis 9) und danach die vollständige SQL-Anweisung. [6 Punkte]
2.
Erläutern Sie, welche Information die gesamte SQL-Anweisung im Sachzusammenhang liefert. [2 Punkte]
Lösung
Nach den offiziellen Korrekturhinweisen
b) i. Ermittelt werden sollen die Namen sämtlicher Trainerinnen und Trainer zusammen mit der Zahl der Sportangebote, die sie jeweils durchführen. Die Ausgabe soll aufsteigend nach dem Namen der Trainerinnen und Trainer geordnet sein.
Lösung: ```sql SELECT Trainername, COUNT(Trainername) FROM Sportangebot GROUP BY Trainername ORDER BY Trainername ASC ```
b) ii. Gesucht wird eine Übersicht über die Teilnehmerinnen und Teilnehmer, die Sporteinheiten versäumt haben. Für jede Person sollen Vorname, Nachname und die Anzahl ihrer versäumten Sporteinheiten ausgegeben werden.
Lösung: ```sql SELECT Teilnehmer.Vorname, Teilnehmer.Nachname, COUNT(*) FROM Teilnehmer INNER JOIN fehlt ON Teilnehmer.ID = fehlt.TeilnehmerID GROUP BY Teilnehmer.ID ```
b) iii. Ermitteln sollen Sie Vor- und Nachname aller Personen, die zu mindestens einem Sportangebot angemeldet sind und bisher nie gefehlt haben.
Lösung: ```sql SELECT DISTINCT Teilnehmer.Vorname, Teilnehmer.Nachname FROM Teilnehmer INNER JOIN angemeldet ON Teilnehmer.ID = angemeldet.TeilnehmerID WHERE Teilnehmer.ID NOT IN ( SELECT fehlt.TeilnehmerID FROM fehlt ) ```
c) 1. Analysieren und erläutern Sie erst die Unterabfrage (Zeilen 3 bis 9) und danach die vollständige SQL-Anweisung.
Lösung: Unterabfrage (Zeilen 3 – 9): Die Unterabfrage ermittelt für jedes Sportangebot die Sportart, den Namen der Trainerin bzw. des Trainers und die Anzahl der angemeldeten Personen. Durch den Left Join wird sichergestellt, dass auch die Sportangebote ohne Anmeldungen aufgelistet würden. Gesamte SQL-Anweisung: Durch die Bedingung in Zeile 10 werden von der Unterabfrage nur die Datensätze berücksichtigt, in denen die Anzahl der Anmeldungen größer gleich 2 ist. Das Ergebnis wird absteigend nach der Zahl der Anmeldungen sortiert.
c) 2. Erläutern Sie, welche Information die gesamte SQL-Anweisung im Sachzusammenhang liefert.
Lösung: Die gesamte SQL-Anweisung ermittelt für alle Sportangebote mit zwei oder mehr Anmeldungen die Sportart, den Namen der Trainerin bzw. des Trainers und die Anzahl der angemeldeten Personen. Das Ergebnis ist absteigend nach der Zahl der Anmeldungen sortiert.
Die ausführliche Erklärung Schritt für Schritt (mit KI) gibt es mit Premium. Premium
Löse sie direkt hier: automatische Korrektur, Hinweise und Lösung Schritt für Schritt.
‹ Aufgabe 3 - SQL-Abfragen · LK · Haupttermin 2021