Aufgabe 3 - SQL-Abfragen · Abitur NRW Informatik GK · Nachtermin 2021

Thema: SQL-Abfragen · Aus der Prüfung GK · Nachtermin (2021)

Aufgabenstellung

Das Unternehmen GoBigOrGoHome24 will die Gesundheit seiner Beschäftigten fördern, indem es sie zur regelmäßigen Teilnahme an Sportangeboten motiviert.

Die Beschäftigten sollen sich aus einer breiten Auswahl an Angeboten zu unterschiedlichen Sportarten Angebote aussuchen und dafür anmelden. Ein Angebot umfasst eine zuvor festgelegte Zahl an Einheiten, die zu besuchen sind. Das Unternehmen trägt die Kosten eines Angebots komplett, sofern die Person an sämtlichen Sporteinheiten teilnimmt. Fehlt sie 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, der die folgende Teilmodellierung zugrunde liegt:

Die weiteren Aufgaben beziehen sich auf das Datenbankschema in Abbildung 2, das nur einen Teil der Gesamtdatenbank abbildet. Im Anhang finden sich Beispieldaten dazu.

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

b)

Aus der Datenbank sollen die nachstehenden Informationen abgerufen werden: Entwerfen Sie zu jeder der folgenden Anfragen eine passende SQL-Anweisung. [11 Punkte]

c)

Gegeben ist die folgende SQL-Anweisung:

1 SELECT Sportart, Trainername

2 FROM Sportangebot

3 WHERE Sportangebot.ID NOT IN (

4 SELECT Sportangebot.ID

5 FROM Teilnehmer

6 INNER JOIN angemeldet

7 ON Teilnehmer.ID = angemeldet.TeilnehmerID

8 INNER JOIN Sportangebot

9 ON angemeldet.SportangebotID = Sportangebot.ID

10 WHERE Teilnehmer.ID = 2

11)

12 ORDER BY Sportangebot.Sportart ASC

Aufgabenstellung: Aufgabe 3 - SQL-Abfragen · Abitur NRW Informatik GK · Nachtermin 2021

Lösung

Nach den offiziellen Korrekturhinweisen

b) i. Ermittelt werden sollen die IDs und die Sportarten aller Sportangebote, die der Trainer Joseph Weizenbaum leitet.

Lösung: ```sql SELECT ID, Sportart FROM Sportangebot WHERE Trainername = 'Joseph Weizenbaum' ```

b) ii. Ermittelt werden soll, wie viele Sporteinheiten unentschuldigt versäumt wurden.

Lösung: ```sql SELECT COUNT(*) FROM fehlt WHERE istEntschuldigt = 'nein' ```

b) iii. Zu jedem Sportangebot sollen ID, Sportart, Name der Trainerin bzw. des Trainers und die Anzahl der angemeldeten Teilnehmerinnen und Teilnehmer ausgegeben werden. Auch Sportangebote, für die sich niemand angemeldet hat, sollen erscheinen. Die Ausgabe soll absteigend nach der Anzahl der teilnehmenden Personen geordnet werden.

Lösung: ```sql SELECT Sportangebot.ID, Sportangebot.Sportart, Sportangebot.Trainername, COUNT(angemeldet.SportangebotID) FROM Sportangebot LEFT JOIN angemeldet ON Sportangebot.ID = angemeldet.SportangebotID GROUP BY Sportangebot.ID ORDER BY COUNT(angemeldet.SportangebotID) DESC ```

c) Analysieren und erläutern Sie erst die Unterabfrage (Zeilen 4 bis 10) und danach die vollständige SQL-Anweisung.

Lösung: Zeile 4 in der Unterabfrage stellt die Projektion auf die ID des Sportangebots dar. In den Zeilen 5 – 9 wird ein Inner Join über die Relationen Teilnehmer, angemeldet und Sportangebot durchgeführt. Die Zeile 10 filtert die Datensätze für die Person mit der ID 2. Zeile 1 stellt die Projektion auf die Spalten (Sportart und Trainername) der Tabelle Sportangebot dar. In Zeile 3 werden alle Datensätze ausgewählt, deren SportangebotsID nicht in der Menge der SportangebotsIDs der Unterabfrage enthalten sind. Die Zeile 12 sortiert die Ergebnismenge aufsteigend nach der Sportart.

c) Erläutern Sie im Sachzusammenhang, welche Information mit der gesamten SQL-Anweisung gewonnen wird.

Lösung: Die gesamte SQL-Anweisung ermittelt für die Teilnehmerin bzw. den Teilnehmer mit der ID 2 alle Sportangebote, für die die Person nicht angemeldet ist.

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 · GK · Haupttermin · Weiterbildungskolleg 2021 · Aufgabe 3 - SQL-Abfragen · LK · Haupttermin 2021 ›

Mehr Aufgaben zu SQL-Abfragen