%load_ext sql

%sql mysql://steinam:steinam@localhost/versicherung_complete

"""
Explanation: Subselect / Unterabfragen)
Zur Durchführung einer Abfrage werden Informationen benötigt, die erst durch eine eigene Abfrage geholt werden müssen.
Sie können stehen

als Vertreter für einen Wert
als Vertreter für eine Liste
als Vertreter für eine Tabelle
als Vertreter für ein Feld
End of explanation
"""


% load_ext sql

"""
Explanation: 
End of explanation
"""


%%sql


select Personalnummer, Name, Vorname 
from Mitarbeiter 
where Abteilung_ID = 
( select ID from Abteilung 
where Kuerzel = 'Schadensabwicklung' );     


"""
Explanation: Vertreter für Wert
Nenne alle Mitarbeiter der Abteilung „Schadensabwicklung“.
End of explanation
"""


%%sql

select Personalnummer, Name, Vorname 
from Mitarbeiter 
where Abteilung_ID = 
( select ID from Abteilung 
where Kuerzel = 'ScAb' );

"""
Explanation: Lösung
End of explanation
"""


%%sql
SELECT ID, Datum, Ort, Schadenshoehe 
from Schadensfall 
where Schadenshoehe < ( 
        select AVG(Schadenshoehe) from Schadensfall 
); 

"""
Explanation: Vertreter für Spaltenfunktionen
Die Ergebnisse von Aggregatfunktionen werden häufig in der WHERE-Klausel benötigt
Beispiel:
Hole die Schadensfälle mit unterdurchschnittlicher Schadenshöhe. 
Lösung

Teil 1: Berechne die durchschnittliche Schadenshöhe aller Schadensfälle. 
Teil 2: Übernimm das Ergebnis als Vergleichswert in die eigentliche Abfrage.
End of explanation
"""


%%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; 

"""
Explanation: Aufgabe
Bestimme alle Schadensfälle, die von der durchschnittlichen Schadenshöhe eines Jahres 
maximal 300 € abweichen. 
Lösung

Teil 1: Bestimme den Durchschnitt aller Schadensfälle innerhalb eines Jahres. 
Teil 2: Hole alle Schadensfälle, deren Schadenshöhe im betreffenden Jahr innerhalb des Bereichs „Durchschnitt plus/minus 300“ liegen.
End of explanation
"""


%%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' ) ); 

"""
Explanation: Bemerkung
Dies ist ein Paradebeispiel dafür, wie Unterabfragen nicht benutzt werden sollen. Für jeden 
einzelnen Datensatz muss in der WHERE-Bedingung eine neue Unterabfrage gestartet werden − mit eigener WHERE-Klausel und Durchschnittsberechnung. Viel besser wäre eine der JOIN-Varianten. 
Weitere Lösungsmöglichkeiten (Lutz (13/14)
```mysql
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; 
```
Ergebnis als Liste mehrerer Werte
Das Ergebnis einer Abfrage kann als Filter für die eigentliche Abfrage benutzt werden. 
Aufgabe
Bestimme alle Fahrzeuge eines bestimmten Herstellers. 
Lösung

Teil 1: Hole die ID des gewünschten Herstellers. 
Teil 2: Hole alle IDs der Tabelle Fahrzeugtyp zu dieser Hersteller-ID. 
Teil 3: Hole alle Fahrzeuge, die zu dieser Liste von Fahrzeugtypen-IDs passen.
End of explanation
"""


%%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 ) 
); 

"""
Explanation: Aufgabe
Gib alle Informationen zu den Schadensfällen des Jahres 2008, die von der durchschnittlichen Schadenshöhe 2008 maximal 300 € abweichen.
Lösung

Teil 1: Bestimme den Durchschnitt aller Schadensfälle innerhalb von 2008. 
Teil 2: Hole alle IDs von Schadensfällen, deren Schadenshöhe innerhalb des  Bereichs „Durchschnitt plus/minus 300“ liegen. 
Teil 3: Hole alle anderen Informationen zu diesen IDs.
End of explanation
"""


%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; 

"""
Explanation: Vertreter für eine Tabelle
Das Ergebnis einer Abfrage kann in der Hauptabfrage überall dort eingesetzt werden, wo 
eine Tabelle vorgesehen ist. Die Struktur dieser Situation sieht so aus: 
```mysql
SELECT <spaltenliste> 
FROM <haupttabelle>, 
  (SELECT <spaltenliste> 
   FROM <zusatztabellen> 
<weitere Bestandteile der Unterabfrage> 
) <name> 
<weitere Bestandteile der Hauptabfrage> 
```

Die Unterabfrage kann grundsätzlich alle SELECT-Bestandteile enthalten.
ORDER BY kann nicht sinnvoll genutzt werden, weil das Ergebnis der Unterabfrage mit der Haupttabelle oder einer
  anderen Tabelle verknüpft wird wodurch eine Sortierung sowieso verlorenginge. 
Es muss ein Name als Tabellen-Alias angegeben werden, der als Ergebnistabelle in der Hauptabfrage verwendet wird.

Aufgabe
Bestimme alle Schadensfälle, die von der durchschnittlichen Schadenshöhe eines Jahres maximal 300 € abweichen. 
Lösung


Teil 1: Stelle alle Jahre zusammen und bestimme den Durchschnitt aller Schadensfälle innerhalb eines Jahres. 


Teil 2: Hole alle Schadensfälle, deren Schadenshöhe im jeweiligen Jahr  innerhalb des Bereichs „Durchschnitt plus/minus 300“ liegen.
End of explanation
"""


%%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; 

"""
Explanation: Durch eine Gruppierung werden alle Jahreszahlen und die durchschnittlichen Schadenshöhen zusammengestellt (Teil 1 der Lösung). 
Für Teil 2 der Lösung muss für jeden Schadensfall nur noch Jahr und Schadenshöhe mit dem betreffenden Eintrag in der Ergebnistabelle temp verglichen werden. 

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. 

Teil 1: Hole die ID des gewünschten Herstellers. 
Teil 2: Hole alle IDs und Bezeichnungen der Tabelle Fahrzeugtyp, die zu dieser  Hersteller-ID gehören. 
Teil 3: Hole alle Fahrzeuge, die zu dieser Liste von Fahrzeugtyp-IDs gehören.
End of explanation
"""


%sql mysql://steinam:steinam@localhost/so_2016

%%sql

-- Original Roth
Select 	Kurs.KursID, Kursart.Bezeichnung, 
		Kurs.DatumUhrzeitBeginn, 
        ((count(KundeKurs.KundenID)/Kursart.TeilnehmerMax) * 100) as Auslastung 
		from Kurs, Kursart, Kundekurs 
		where KundeKurs.KursID = Kurs.KursID and Kursart.KursartID = Kurs.KursartID  
		group by Kurs.KursID, Kurs.DatumUhrzeitBeginn, Kursart.Bezeichnung
        having Auslastung < 50;

%%sql

select kursid from kurs
where 
((select teilnehmerMax from kursart where kursart.kursartId = kurs.kursartId) * 0.5)
> 
(count(KundeKurs.kundenid) where KundeKurs.KursID = kurs.KursID);

%%sql

Select 	Kurs.KursID, Kursart.Bezeichnung, 
		Kurs.DatumUhrzeitBeginn, 
        ((count(KundeKurs.KundenID)/Kursart.TeilnehmerMax) * 100) as Auslastung 
		from Kurs, Kursart, Kundekurs 
		where KundeKurs.KursID = Kurs.KursID and Kursart.KursartID = Kurs.KursartID 
		group by Kurs.KursID, Kurs.DatumUhrzeitBeginn, Kursart.Bezeichnung
        having Auslastung < 50

%%sql

Select 	Kurs.KursID, Kursart.Bezeichnung, 
		Kurs.DatumUhrzeitBeginn, 
        ((count(KundeKurs.KundenID)/Kursart.TeilnehmerMax) * 100) as Auslastung 
		from kurs left join kundekurs
        	on kurs.`kursid` = kundekurs.`Kursid`
            inner join kursart
            on `kurs`.`kursartid` = `kursart`.`kursartid`

            
            group by Kurs.KursID, Kurs.DatumUhrzeitBeginn, Kursart.Bezeichnung
        having Auslastung < 50

"""
Explanation: Übungen


Welche der folgenden Feststellungen sind richtig, welche sind falsch? 

Das Ergebnis einer Unterabfrage kann verwendet werden, wenn es ein einzelner Wert oder eine Liste in Form einer Tabelle ist. Andere Ergebnisse sind nicht möglich. 
Ein einzelner Wert als Ergebnis kann durch eine direkte Abfrage oder durch eine Spaltenfunktion erhalten werden. 
Unterabfragen sollten nicht verwendet werden, wenn die WHERE-Bedingung  für jede Zeile der Hauptabfrage einen anderen Wert erhält und deshalb die Unterabfrage neu ausgeführt werden muss. 
Mehrere Unterabfragen können verschachtelt werden. 
Für die Arbeitsgeschwindigkeit ist es gleichgültig, ob mehrere Unterabfragen  oder JOINs verwendet werden. 
Eine Unterabfrage mit einer Tabelle als Ergebnis kann GROUP BY nicht sinnvoll nutzen. 
Eine Unterabfrage mit einer Tabelle als Ergebnis kann ORDER BY nicht sinnvoll nutzen. 
Bei einer Unterabfrage mit einer Tabelle als Ergebnis ist ein Alias-Name für die Tabelle sinnvoll, aber nicht notwendig. 
Bei einer Unterabfrage mit einer Tabelle als Ergebnis sind Alias-Namen für die Spalten sinnvoll, aber nicht notwendig. 



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. 

Zeigen Sie alle Verträge, die zum Kunden 'Heckel Obsthandel GmbH' gehören.  Ignorieren Sie die Möglichkeit, dass der Kunde mehrfach gespeichert sein könnte. 
Ändern Sie die Lösung von Übung 3, sodass auch mehrere Kunden mit diesem  Namen als Ergebnis denkbar sind. 
Zeigen Sie alle Fahrzeuge, die im Jahr 2008 an einem Schadensfall beteiligt waren. 
Zeigen Sie alle Fahrzeugtypen (mit ID, Bezeichnung und Name des Herstellers), die im Jahr 2008 an einem Schadensfall beteiligt waren. 
Bestimmen  Sie  alle  Fahrzeuge  eines  bestimmten  Herstellers  mit  Angabe  des Typs. 
Zeigen Sie zu jedem Mitarbeiter der Abteilung „Vertrieb“ den ersten Vertrag (mit einigen Angaben) an, den er abgeschlossen hat. Der Mitarbeiter soll mit ID und Name/Vorname angezeigt werden. 
Von der Deutschen Post AG wird eine Tabelle PLZ_Aenderung mit folgenden Inhalten geliefert: 

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 ﬁktive Daten, keine echten Änderungen.

Sommer 2016
End of explanation
"""


