Zur Durchführung einer Abfrage werden Informationen benötigt, die erst durch eine eigene Abfrage geholt werden müssen.
Sie können stehen
In [ ]:
%load_ext sql
In [3]:
%sql mysql://steinam:steinam@localhost/versicherung_complete
Out[3]:
In [1]:
% load_ext sql
In [ ]:
%sql
### Lösung:
-- Lösung Teil 1: Hole die ID dieser Abteilung anhand des Namens.
-- Lösung Teil 2: Hole die Mitarbeiter der Abteilung unter Benutzung der gefundenen ID.
select Personalnummer, Name, Vorname
from Mitarbeiter
where Abteilung_ID =
( select ID from Abteilung
where Kuerzel = ’ScAb’ );
In [ ]:
%%sql
select Personalnummer, Name, Vorname
from Mitarbeiter
where Abteilung_ID =
( select ID from Abteilung
where Kuerzel = 'ScAb' );
Die Ergebnisse von Aggregatfunktionen werden häufig in der WHERE-Klausel benötigt
Beispiel:
Hole die Schadensfälle mit unterdurchschnittlicher Schadenshöhe.
In [13]:
%%sql
SELECT ID, Datum, Ort, Schadenshoehe
from Schadensfall
where Schadenshoehe < (
select AVG(Schadenshoehe) from Schadensfall
);
Out[13]:
In [14]:
%%sql
select sf.ID, sf.Datum, sf.Schadenshoehe, EXTRACT(YEAR from
sf.Datum) AS Jahr
from Schadensfall sf
where ABS(Schadenshoehe - (
select AVG(sf2.Schadenshoehe)
from Schadensfall sf2
where YEAR(sf2.Datum) = YEAR(sf.Datum)
)
) <= 300;
Out[14]:
Weitere Lösungsmöglichkeiten (Lutz (13/14)
select beschreibung, schadenshoehe
from schadensfall where
schadenshoehe <= (
select avg(schadenshoehe)
from schadensfall) + 300
and schadenshoehe >= (select avg(schadenshoehe)
from schadensfall) - 300
select beschreibung, schadenshoehe
from schadensfall where
schadenshoehe between (
select avg(schadenshoehe)
from schadensfall) - 300
and (select avg(schadenshoehe)
from schadensfall) + 300
select @average:=avg(schadenshoehe) from schadensfall;
select id from schadensfall where abs(schadenshoehe -
@average) <= 300;
In [16]:
%%sql
select ID, Kennzeichen, Fahrzeugtyp_ID as TypID
from Fahrzeug
where Fahrzeugtyp_ID in(
select ID
from Fahrzeugtyp
where Hersteller_ID = (
select ID
from Fahrzeughersteller
where Name = 'Volkswagen' ) );
Out[16]:
Aufgabe
Gib alle Informationen zu den Schadensfällen des Jahres 2008, die von der durchschnittlichen Schadenshöhe 2008 maximal 300 € abweichen.
Lösung
In [17]:
%%sql
select *
from Schadensfall
where ID in ( SELECT ID
from Schadensfall
where ( ABS(Schadenshoehe - (
select AVG(sf2.Schadenshoehe)
from Schadensfall sf2
where YEAR(sf2.Datum) = 2008
)
) <= 300 )
and ( YEAR(Datum) = 2008 )
);
Out[17]:
SELECT <spaltenliste>
FROM <haupttabelle>,
(SELECT <spaltenliste>
FROM <zusatztabellen>
<weitere Bestandteile der Unterabfrage>
) <name>
<weitere Bestandteile der Hauptabfrage>
Aufgabe
Bestimme alle Schadensfälle, die von der durchschnittlichen Schadenshöhe eines Jahres maximal 300 € abweichen.
In [ ]:
%sql
SELECT sf.ID, sf.Datum, sf.Schadenshoehe, temp.Jahr,
temp.Durchschnitt
FROM Schadensfall sf,
( SELECT AVG(sf2.Schadenshoehe) AS Durchschnitt,
EXTRACT(YEAR FROM sf2.Datum) as Jahr
FROM Schadensfall sf2
group by EXTRACT(YEAR FROM sf2.Datum)
) temp
WHERE temp.Jahr = EXTRACT(YEAR FROM sf.Datum)
and ABS(Schadenshoehe - temp.Durchschnitt) <= 300;
Das ist der wesentliche Unterschied und entscheidende Vorteil zu anderen Lösungen: Die Durchschnittswerte werden einmalig zusammengestellt und nur noch abgerufen; sie müs- sen nicht bei jedem Datensatz neu (und ständig wiederholt) berechnet werden.
Aufgabe
Bestimme alle Fahrzeuge eines bestimmten Herstellers mit Angabe des Typs.
In [19]:
%%sql
SELECT Fahrzeug.ID, Kennzeichen, Typen.ID As TYP, Typen.Bezeichnung
FROM Fahrzeug,
(SELECT ID, Bezeichnung
FROM Fahrzeugtyp
WHERE Hersteller_ID =
(SELECT ID
FROM Fahrzeughersteller
WHERE Name = 'Volkswagen' )
) Typen
WHERE Fahrzeugtyp_ID = Typen.ID;
Out[19]:
Welche der folgenden Feststellungen sind richtig, welche sind falsch?
Welche Verträge (mit einigen Angaben) hat der Mitarbeiter „Braun, Christian“ abgeschlossen? Ignorieren Sie die Möglichkeit, dass es mehrere Mitarbeiter dieses Namens geben könnte.
csv
ID PLZalt Ortalt PLZneu Ortneu
1 45658 Recklinghausen 45659 Recklinghausen
2 45721 Hamm-Bossendorf 45721 Haltern OT Hamm
3 45772 Marl 45770 Marl
4 45701 Herten 45699 HertenÄndern Sie die Tabelle Versicherungsnehmer so, dass bei allen Adressen, bei denen PLZ/Ort mit PLZalt/Ortalt
übereinstimmen, diese Angaben durch PLZneu/Ortneu geändert werden.
Hinweise: Beschränken Sie sich auf die Änderung mit der ID=3. (Die vollständige Lösung ist erst mit
SQL-Programmierung möglich.) Bei dieser Änderungsdatei handelt es sich nur um fiktive Daten, keine echten Änderungen.
In [ ]:
%sql mysql://steinam:steinam@localhost/versicherung_complete