Übung 1: Gemischte SQL-Abfragen

Schema:

BUNDESLAND [ Name : VARCHAR(100) ; Landeshauptstadt : INT ; Einwohner : INT ; Fläche : INT ; Ministerpräsident : INT ; BIP: DECIMAL(10,2) ]
LANDKREIS [ Name : VARCHAR(100) ; Bevölkerungsdichte : INT ; Bundesland : VARCHAR(100) ]
STADT [ ID : INT ; Name : VARCHAR(100) ; Vorwahlen : VARCHAR(100) ; Webadresse : VARCHAR(100) ; Einwohner : INT ; Landkreis : VARCHAR (100) ]
POLITIKER [ID : INT ; Name : VARCHAR(50) ; Vorname : VARCHAR(50) ; GebDat : DATE ]
BERATER [ ID : INT ; Name : VARCHAR(50) ; Vorname : VARCHAR(50) ; Fachgebiet : VARCHAR(100) ]
berät [ B_ID : INT ; P_ID : INT]

Vereinfachtes Klassendiagramm:

Formuliere für nachfolgende Aufgaben die zugehörige SQL-Abfrage!

Aufgabe 1:
Gib alle Informationen über Städte aus und sortiere sie absteigend nach der Anzahl ihrer Einwohner. <popup name="Lösung">

SELECT *
FROM STADT
ORDER BY Einwohner DESC

</popup>

Aufgabe 2:
Gib den Namen und das Geburtsdatum des Politikers aus, der Ministerpräsident von Bayern ist. <popup name="Lösung">

SELECT P.Name, P.GebDat
FROM POLITIKER AS P, BUNDESLAND AS B
WHERE B.Name = "Bayern" AND
B.Ministerpräsident = P.ID

</popup>

Aufgabe 3:
Gib den Namen der Städte und deren Vorwahlen aus, die im Bundesland Hessen liegen. <popup name="Lösung">

SELECT S.Name, S.Vorwahlen
FROM STADT AS S, LANDKREIS AS L
WHERE L.Bundesland = "Hessen" AND
S.Landkreis = L.Name

</popup>

Aufgabe 4:
Die Augsburger Bürger möchten einen Brief an ihren Ministerpräsidenten schreiben. Gib den Vornamen und Namen ihres Ministerpräsidenten aus. <popup name="Lösung">

SELECT P.Vorname, P.Name
FROM STADT AS S, LANDKREIS AS L, BUNDESLAND AS B, POLITIKER AS P
WHERE S.Name = "Augsburg" AND
S.Landkreis = L.Name AND
L.Bundesland = B.Name AND
B.Ministerpräsident = P.ID

</popup>

Aufgabe 5:
Gib die Anzahl der Einwohner je Landkreis (Tipp: Tabelle STADT) aus. <popup name="Lösung">

SELECT Landkreis, SUM(Einwohner)
FROM STADT
GROUP BY Landkreis

</popup>

Aufgabe 6:
Wie viele Städte hat der Landkreis "Deggendorf"? <popup name="Lösung">

SELECT COUNT(ID)
FROM STADT
WHERE Landkreis = "Deggendorf"

</popup>

Aufgabe 7:
Wie viele Städte hat das Bundesland "Hessen"? <popup name="Lösung">

SELECT COUNT(S.ID)
FROM STADT AS S, LANDKREIS AS L
WHERE L.Bundesland = "Hessen" AND
S.Landkreis = L.Name

</popup>

Aufgabe 8:
Welche Ministerpräsidenten (Name) regieren ein Bundesland, das größer als 70 000[km²] ist? <popup name="Lösung">

SELECT P.Name
FROM POLITIKER AS P, BUNDESLAND AS B
WHERE B.Fläche > 70000 AND
P.ID = B.Ministerpräsident

</popup>

Aufgabe 9:
An welchem Wochentag wurde "Horst Seehofer" geboren? <popup name="Lösung">

SELECT DAYNAME(GebDat)
FROM POLITIKER
WHERE Name = "Seehofer" AND Vorname = "Horst"

</popup>

Aufgabe 10:
Gib eine Liste mit den Namen aller Ministerpräsidenten und deren Alter im Jahr 2015 aus! <popup name="Lösung">

SELECT Name, 2015 - YEAR(GebDat)
FROM POLITIKER

</popup>

Aufgabe 11:
Welche Berater (Name, Vorname) beraten "Horst Seehofer" im Fachgebiet "Bildung"? <popup name="Lösung">

SELECT B.Name, B.Vorname
FROM POLITIKER AS P, BERATER AS B, berät AS BT
WHERE P.Name = "Seehofer" AND P.Vorname = "Horst" AND
B.Fachgebiet = "Bildung" AND
P.ID = BT.P_ID AND
B.ID = BT.B_ID

</popup>

Aufgabe 12:
Wie viele Berater stehen "Horst Seehofer" je Fachgebiet zur Verfügung? <popup name="Lösung">

SELECT B.Fachgebiet, COUNT(B.ID)
FROM POLITIKER AS P, BERATER AS B, berät AS BT
WHERE P.Name = "Seehofer" AND P.Vorname = "Horst" AND
P.ID = BT.P_ID AND
B.ID = BT.B_ID
GROUP BY B.Fachgebiet

</popup>