Aufgabe 3 - SQL-Abfragen · Abitur NRW Informatik LK · Haupttermin 2022

Thema: SQL-Abfragen · Aus der Prüfung LK · Haupttermin (2022)

Aufgabenstellung

Aufgabenstellung:

Weil Gäste Restaurants meist subjektiv beurteilen, plant der Projektkurs „Gastro-Informatik“ ein Bewertungssystem, in dem eine professionelle Gutachterin oder ein professioneller Gutachter Speisequalität, Servicequalität und Hygienezustand auf einer Skala von 0 bis 10 einstuft, wobei 10 die beste Note ist.

Die anfallenden Daten sollen in einer relationalen Datenbank verwaltet werden, die auf der folgenden Teilmodellierung beruht:

Das Datenbankschema in Abbildung 2 überträgt die Teilmodellierung aus Abbildung 1 in eine relationale Datenbank und wird im Weiteren genauer untersucht. Die Anlage enthält Beispieldaten zu diesem Schema.

Anlage:

Ausschnitt aus den Beispieldaten zum Datenbankschema aus Abbildung 2

Gutachter

ID

Vorname

Nachname

1 · Ada · Lovelace

2 · John · Von Neumann

3 · Alan · Turing

4 · Konrad · Zuse

5 · Tim · Berners-Lee

bewertet

GutachterID

RestaurantID

Speisequalitaet

Servicequalitaet

Hygienezustand

1 · 2 · 7 · 8 · 6

4 · 3 · 1 · 8 · 10

4 · 2 · 8 · 5 · 5

3 · 3 · 10 · 7 · 8

2 · 2 · 5 · 8 · 4

1 · 1 · 8 · 9 · 7

Restaurant

ID

Name

PLZ

Ortsname

1 · Meisterpizza · 44269 · Dortmund

2 · Sushi4Friends · 52525 · Heinsberg

3 · Veggie-Haus · 31137 · Hildesheim

4 · Der Grieche · 44135 · Dortmund

5 · Burgerhaus · 52525 · Waldfeucht

Ort

PLZ

Name

31137 · Hildesheim

44135 · Dortmund

44269 · Dortmund

52525 · Heinsberg

52525 · Waldfeucht

Teilaufgabe b)

Mithilfe der im Anhang beschriebenen Datenbank sollen die folgenden Abfragen umgesetzt werden:

i.

Ermittelt werden sollen die Namen aller Restaurants, deren PLZ zwischen 44100 und 44300 liegt (Grenzen eingeschlossen). Die Ausgabe soll aufsteigend nach dem Restaurantnamen geordnet sein. [3 Punkte]

ii.

Ermittelt werden sollen alle Restaurants, für die bisher keine Bewertung vorliegt. Auszugeben sind jeweils der Restaurantname, die PLZ und der Ortsname. [4 Punkte]

iii.

Ermittelt werden soll, wie viele Restaurants es je PLZ gibt. Auch PLZ ohne Restaurant müssen im Ergebnis erscheinen. Die Ausgabe soll nach der Restaurantanzahl absteigend geordnet sein. [5 Punkte]

Teilaufgabe c)

Gegeben ist die folgende SQL-Anweisung:

1 SELECT Temp.Ortsname, MAX(Temp.d) AS Max, MIN(Temp.d) AS Min

2 FROM (

3 SELECT Restaurant.ID, Restaurant.Ortsname,

4 AVG(bewertet.Speisequalitaet) AS d

5 FROM bewertet

6 INNER JOIN Restaurant

7 ON bewertet.RestaurantID = Restaurant.ID

8 GROUP BY Restaurant.ID

9) AS Temp

10 GROUP BY Temp.Ortsname

1.

Analysieren und erläutern Sie erst die Unterabfrage (Zeilen 3 bis 8) und danach die vollständige SQL-Anweisung. [6 Punkte]

2.

Erläutern Sie, welche Information die vollständige SQL-Anweisung im Sachzusammenhang liefert. [2 Punkte]

Aufgabenstellung: Aufgabe 3 - SQL-Abfragen · Abitur NRW Informatik LK · Haupttermin 2022

Lösung

Nach den offiziellen Korrekturhinweisen

b) i. Ermittelt werden sollen die Namen aller Restaurants, deren PLZ zwischen 44100 und 44300 liegt (Grenzen eingeschlossen). Die Ausgabe soll aufsteigend nach dem Restaurantnamen geordnet sein.

Lösung: ```sql SELECT Name FROM Restaurant WHERE PLZ >= 44100 AND PLZ <= 44300 ORDER BY Name ASC ```

b) ii. Ermittelt werden sollen alle Restaurants, für die bisher keine Bewertung vorliegt. Auszugeben sind jeweils der Restaurantname, die PLZ und der Ortsname.

Lösung: ```sql SELECT Restaurant.Name, Restaurant.PLZ, Restaurant.Ortsname FROM Restaurant WHERE Restaurant.ID NOT IN ( SELECT DISTINCT bewertet.RestaurantID FROM bewertet ) ```

b) iii. Ermittelt werden soll, wie viele Restaurants es je PLZ gibt. Auch PLZ ohne Restaurant müssen im Ergebnis erscheinen. Die Ausgabe soll nach der Restaurantanzahl absteigend geordnet sein.

Lösung: ```sql SELECT Ort.PLZ, COUNT(Restaurant.ID) AS Anzahl FROM Ort LEFT JOIN Restaurant ON Ort.PLZ = Restaurant.PLZ AND Ort.Name = Restaurant.Ortsname GROUP BY Ort.PLZ ORDER BY Anzahl DESC ```

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

Lösung: Die Unterabfrage verknüpft mit einem INNER JOIN die Relationen bewertet und Restaurant über die ID der Restaurants (vgl. Zeilen 5 bis 7). Außerdem werden die Datensätze nach der ID der Restaurants gruppiert (vgl. Zeile 8), um für jedes Restaurant die durchschnittliche Bewertung der Speisequalität mit der Aggregatfunktion AVG zu ermitteln (vgl. Zeile 4). Diese wird mit d bezeichnet und zusammen mit der ID und dem zugehörigen Ortsnamen des Restaurants zurückgeliefert (vgl. Zeilen 3 bis 4). Die gesamte SQL-Anweisung gruppiert die mit Temp bezeichnete Ergebnisrelation der Unterabfrage nach den Ortsnamen (vgl. Zeilen 9 bis 10). Die Aggregatfunktionen MAX und MIN stellen sicher, dass für jede Gruppe (d. h. für jeden Ortsnamen) ein Datensatz mit der höchsten (als Max bezeichneten) und der niedrigsten (als Min bezeichneten) durchschnittlichen Bewertung der Speisequalität zurückgeliefert wird (vgl. Zeile 1). Zusätzlich wird der zugehörige Ortsname zurückgeliefert (vgl. Zeile 1).

c) 2. Erläutern Sie, welche Information die vollständige SQL-Anweisung im Sachzusammenhang liefert.

Lösung: Gesucht ist zu jedem Ortsnamen die beste und die schlechteste durchschnittliche Bewertung der Speisequalität (bzw. die Bandbreite dieses Durchschnittswerts).

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 · Nachtermin 2022 · Aufgabe 3 - SQL-Abfragen · LK · Nachtermin 2022 ›

Mehr Aufgaben zu SQL-Abfragen