%load_ext sql

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

"""
Explanation: Sommer 2017
HS 5
End of explanation
"""


%%sql

select G.*, R.*
from `gebaeude` G
left join Raum R on G.`GebID` = R.`GebID`
order by G.`Bezeichnung` asc, R.`Typ`


"""
Explanation: Die Bioscan GmbH plant ein System zur Zugangskontrolle. Dazu wurde bereits folgende Datenbank entwickelt und mit Testdaten gefüllt.

a) Liste aller Gebäude mit deren Räumen jeweils aufsteigend sortiert nach Gebäudebezeichnung und Raumtyp
End of explanation
"""


%%sql

-- b
-- Ausgabe in der Kammerprüfung kann laut Datenbestand nicht ausgegeben werden
select p.*, Z.*
from Zugang Z
 left join Person P on P.`PersID` = Z.`PersID`


"""
Explanation: b) Liste aller Daten, die in der Tabelle Zugang gespeichert sind 
und die dazugehörigen Personendaten

Hinweis: Die Darstellung entspricht nicht dem erwarteten Ergebnis (redaktioneller Fehler in der Kammerprüfung (Dilettanten :-))
End of explanation
"""


%%sql

 Select M.Merkmal, COUNT(R.RaumID) as AnzahlRaueme
 from Raum R left join Merkmal m on M.`MerkID` = R.`MerkID`
 group by M.`Merkmal`


"""
Explanation: c) Anzahl der Räume, die bei derZugangskontrolle das Merkmal "Fingerabdruck prüfen" bzw. das Merkmal Iris prüfen
End of explanation
"""


%%sql
select P.`Nachname`, P.`Vorname`, Z.RaumID, Z.`ZeitVon`, Z.`ZeitBis`from Zugang Z
 	left join Person P
    	on P.`PersID` = Z.`PersID`
        where P.`Nachname` = 'Müller' and P.`Vorname` = 'Max'

"""
Explanation: d) Liste der Zugangsdaten von Max Müller. 
Hinweis: Es ist nur der Name, nicht die PersID bekannt
End of explanation
"""


%%sql

Select P.*
 from Person P
 where P.`PLZ` like '5%'

"""
Explanation: e) Liste aller Personen aus dem PLZ-Gebiet 5000 bis 5999
End of explanation
"""


