SQL Uebung Loesungen
Beschreibung
Mitarbeiter sind eindeutig identifiziert durch die Sozialversicherungsnummer (SVNR). Es wird auch noch der Name (NAME), die Adresse (ADRESSE) und das Geburtsdatum (GEBDAT) gespeichert. Optional kann für gekündigte Mitarbeiter ein Kündigungsdatum (DATUM) gespeichert werden. Kassadienst und Bademeister sind Mitarbeiter. Für Bademeister ist bekannt, welche Ausbildung sie absolviert haben (AUSBILDUNG). Es wird auch gespeichert, welche Bademeister von welchen anderen Bademeistern vertreten werden können.
Ein Schwimmbecken wird identifiziert durch eine Bezeichnung (BEZ), außerdem ist die Wassertiefe (TIEFE) gespeichert. Messwerte sind identifiziert durch das Schwimmbecken, in dem sie gemessen wurden und dem Zeitpunkt (ZEITPKT). Dann wird noch die Bezeichnung des Wertes (BEZ) z.B. pH-Wert und der Wert (WERT) selbst gespeichert. Sollte ein Werte nicht der Norm entsprechen, so wird gespeichert, welche Maßnahmen in Bezug auf diesen einen Wert eingeleitet wurden. Jede Maßnahme wird identifiziert durch einen Zeitpunkt (ZEITPKT) und ein Mittel (MITTEL) das angewendet wurde. Außderdem wird die Menge (MENGE) des Mittels gespeichert. Es wird auch gespeichert welcher Bademeister welche Maßnahmen getroffen hat.
Bademeister haben auch noch eine andere Aufgabe: die Aufsicht. Daher muss vermerkt werden, welche Bademeister in welchem Zeitraum (VON, BIS) welche Becken als Rettungsschwimmer beaufsichtigen.
Eine Eintrittskarte wird identifiziert durch eine laufende Tagesnummer (LFDNR) und durch ein Datum (VON) von dem an sie gültig ist. Außerdem wird das Ende der Gültigkeit (BIS) und der Preis (PREIS) gespeichert. Es wird auch gespeichert, welche Mitarbeiter vom Kassadienst welche Karte verkauft haben. Eine Karte kann dabei nur von einem Kassadienstmitarbeiter verkauft werden. Kategorien werden identifiziert durch eine Bezeichnung (BEZ) z.B. Student, halbtags... Dazu wird noch eine genaue Beschreibung (BESCHREIBUNG) gespeichert. Karten können mehrere Kategorien zugeordnet sein. Eine Jahreskarte ist eine besondere Form der Karte. Es wird hierfür gespeichert welche Kunden welche Jahreskarten besitzen. Eine Jahreskarte ist hingegen einem einzigen Kunden zugeordnet. Ein Kunde ist eindeutig identifiziert duch eine Kundennummer (KNR). Zu jedem Kunden wird auch der Name (NAME), die Adresse (ADRESSE) sowie ein Foto (FOTO) gespeichert.
EER
1) Geben Sie jene Kassadienst-Mitarbeiter aus, die ab dem 1. Januar 1970 geboren wurden. Verwenden Sie dazu die Funktion TO_DATE und folgendes Datumsformat: DD-MM-YYYY. Ordnen Sie die Ergebnisse absteigend nach der Sozialversicherungsnummer.
select *
from kassadienst natural join mitarbeiter
where gebdat >= to_date ('01-01-1970' , 'DD-MM-YYYY')
order by svnr desc
2.1) Geben Sie das Geburtsdatum des aeltesten Bademeisters aus.
select min(gebdat)
from mitarbeiter natural join bademeister
2.2) Geben Sie die Sozialversicherungsnummer, den Namen und das Geburtsdatum des aeltesten Bademeisters aus.
select svnr, name, gebdat
from bademeister natural join mitarbeiter
where gebdat= (select min (gebdat)
from bademeister natural join mitarbeiter)
2.3) Geben Sie die Sozialversicherungsnummer, den Namen und das Geburtsdatum des aeltesten Bademeisters mit der Ausbildung 'Rettungsschwimmer' aus.
select svnr, name, gebdat
from bademeister natural join mitarbeiter
where gebdat = (select min(gebdat)
from bademeister natural join mitarbeiter
where Ausbildung = 'Rettungsschwimmer')
3.1) Geben Sie eine Liste aller Jahreskartenbesitzer aus, auf der die Kundennummer und der Name des Kunden zusammen mit der laufenden Tagesnummer der Jahreskarte(n) verzeichnet sind.
select name, knr, lfdnr from JAHRESKARTE left outer join kunde on (kunde=knr)
3.2) Geben Sie eine Liste ALLER Kunden (knr, name) aus zusammen mit der laufenden Tagesnummer der Jahreskarte(n), die sie besitzen. Fuer Kunden, die keine Jahreskarte haben, soll stattdessen 'keine Karte' ausgegeben werden.(Stichwort: COALESCE)
select k.name, k.knr, coalesce (j.lfdnr, 'keine Karte') as Tagesnummer
from jahreskarte j right outer join kunde k on (j.kunde=k.knr)
3.3) Geben Sie eine Liste ALLER Kunden (knr, name) aus zusammen mit der laufenden Tagesnummer der Jahreskarte(n), die sie besitzen, sowie den Namen des Mitarbeiters, der sie ihnen verkauft hat. Fuer Kunden, die keine Jahreskarte haben, soll anstelle der Tagesnummer 'keine Karte' und anstelle des Namens des Mitarbeiters 'kein Verkaeufer' ausgegeben werden. (Stichwort: COALESCE)
select k.knr, k.name, coalesce(j.lfdnr,'keine Karte'), coalesce(m.name,'kein Verkaeufer')
from kunde k left join jahreskarte j on k.knr=j.kunde left join karte ka on j.lfdnr=ka.lfdnr and j.von=ka.von left join mitarbeiter m on ka.verkaeufer=m.svnr
4.1) Mit Aussnahme von PH-Wert Bestimmungen (bez = 'pH-Wert'), soll die Bezeichnung, der Wert und der Zeitpunkt aller Messwerte ausgegeben werden, die fuer Nichtschwimmer-Becken (Tiefe maximal 135 cm) erhoben wurden.
select m.bez, m.wert, m.zeitpkt
from becken b join messwert m on b.bez=m.becken
where tiefe <=135 and m.bez!='pH-Wert'
4.2) Gesucht ist die Bezeichnung jener Becken, fuer die noch nie ein pH-Wert (Messwert bez = 'pH-Wert') erhoben wurde und die keine Nichtschwimmer-Becken sind. (Die Tiefe betraegt fuer Nichtschwimmerbecken maximal 135 cm.)
select b.bez
from becken b left join messwert m on b.bez=m.becken
where b.tiefe > 135 and b.bez not in (select becken from messwert where bez='pH-Wert' )
5.1) Wieviele verschiedene Kategorien sind den Eintrittskarten mit der laufenden Tagesnummer '123123' zugeordnet?
select count(distinct kategorie)
from hat
where lfdnr = 123123
5.2) Geben Sie fuer ALLE Eintrittskarten die laufende Tagesnummer sowie den Preis aus und dazu die Anzahl der zugeordneten Kategorien.
select k.lfdnr, k.preis, count(h.kategorie)
from karte k left join hat h on k.lfdnr=h.lfdnr and k.von=h.von
group by k.lfdnr, k.von, k.preis
5.3) Geben Sie die laufende Tagesnummer sowie den Preis jener Eintrittskarten aus, denen am meisten Kategorien zugeordnet sind.
select lfdnr, preis
from karte natural left join hat
group by lfdnr, von, preis
having count(kategorie) >= all(select count(kategorie) from karte natural left join hat group by lfdnr, von, preis)
6) Geben Sie zu jeder Beckentiefe, die Bezeichnung jenes Beckens aus, fuer das die meisten Messwerte vorliegen. Ordnen Sie das Ergebnis aufsteigend nach der Beckentiefe. (Beckentiefen, zu denen es keine Messwerte gibt, brauchen Sie dabei nicht beruecksichtigen.)
select tiefe, becken
from (select tiefe, becken, count(zeitpkt) as anzahl
from messwert m join becken b on b.bez=m.becken
group by becken, tiefe) x
natural join
(select tiefe, max(anzahl) as anzahl
from (select tiefe, becken, count(zeitpkt) as anzahl
from messwert m join becken b on b.bez=m.becken
group by tiefe, becken) a
group by tiefe) y
order by tiefe asc
(Das Ergebnis ist zwar korrekt, aber der Lösungsweg ist eigenwillig und stimmt nicht mit der erwarteten Lösung überein.
Das Ergebnis ist zwar korrekt, aber der Lösungsweg ist möglichweise eine ungültige Abkürzung. Formulieren Sie die Abfrage so, dass die Lösung auch mit anderen Daten korrekt wäre!)
7) Gesucht ist der Name und das Geburtsdatum jenes Bademeisters der bereits Rettungsdienste in ALLEN Becken mit der Tiefe von 260cm uebernommen hat, aber erst weniger als drei Massnahmen durchgefuehrt hat.
select name, gebdat
from mitarbeiter natural join (select svnr
from MASSNAHME m join mitarbeiter m2 on m.bademeister=m2.svnr
group by svnr having count)
where svnr in (select distinct bademeister as svnr
from BECKEN b join rdienst r on b.bez = r.becken
where tiefe = 260)
(Die Eingabe ist fehlerhaft)
8) Wieviele Messwerte werden pro Becken durchschnittlich erhoben?
select avg(anzahl)
from (select b.bez, count(zeitpkt) as anzahl
from becken b left join messwert m on b.bez=m.becken
group by b.bez) a
9) Geben Sie die Bezeichnung und Tiefe jener Becken aus, fuer die bereits alle verschiedenen Messwerte (z.B. Clor, pH-Wert, etc.) erhoben wurden.
select distinct tiefe, b.bez
from messwert m join becken b on b.bez=m.becken
where b.bez in (select becken
from messwert
group by becken having count(bez) >= all(select count(bez)
from messwert
group by becken))
10) Geben Sie eine Liste ALLER Kategorien (Bezeichnung und Beschreibung) aus und dazu die Anzahl der seit dem '01.01.2011' verkauften Eintrittskarten und wieviel diese in Summe gekostet haben. Verwenden Sie dazu die Funktion TO_DATE und folgendes Datumsformat: DD-MM-YYYY.
select bez, beschreibung, count(lfdnr) as Anzahl, coalesce(sum(preis),0)
from kategorie k1 left join hat h on k1.bez=h.kategorie left join (select * from karte where von >= to_date('01-01-2011','DD-MM-YYYY')) k2 on h.lfdnr=k2.lfdnr and h.von=k2.von
group by bez, beschreibung
Mitarbeiter sind eindeutig identifiziert durch die Sozialversicherungsnummer (SVNR). Es wird auch noch der Name (NAME), die Adresse (ADRESSE) und das Geburtsdatum (GEBDAT) gespeichert. Optional kann für gekündigte Mitarbeiter ein Kündigungsdatum (DATUM) gespeichert werden. Kassadienst und Bademeister sind Mitarbeiter. Für Bademeister ist bekannt, welche Ausbildung sie absolviert haben (AUSBILDUNG). Es wird auch gespeichert, welche Bademeister von welchen anderen Bademeistern vertreten werden können.
Ein Schwimmbecken wird identifiziert durch eine Bezeichnung (BEZ), außerdem ist die Wassertiefe (TIEFE) gespeichert. Messwerte sind identifiziert durch das Schwimmbecken, in dem sie gemessen wurden und dem Zeitpunkt (ZEITPKT). Dann wird noch die Bezeichnung des Wertes (BEZ) z.B. pH-Wert und der Wert (WERT) selbst gespeichert. Sollte ein Werte nicht der Norm entsprechen, so wird gespeichert, welche Maßnahmen in Bezug auf diesen einen Wert eingeleitet wurden. Jede Maßnahme wird identifiziert durch einen Zeitpunkt (ZEITPKT) und ein Mittel (MITTEL) das angewendet wurde. Außderdem wird die Menge (MENGE) des Mittels gespeichert. Es wird auch gespeichert welcher Bademeister welche Maßnahmen getroffen hat.
Bademeister haben auch noch eine andere Aufgabe: die Aufsicht. Daher muss vermerkt werden, welche Bademeister in welchem Zeitraum (VON, BIS) welche Becken als Rettungsschwimmer beaufsichtigen.
Eine Eintrittskarte wird identifiziert durch eine laufende Tagesnummer (LFDNR) und durch ein Datum (VON) von dem an sie gültig ist. Außerdem wird das Ende der Gültigkeit (BIS) und der Preis (PREIS) gespeichert. Es wird auch gespeichert, welche Mitarbeiter vom Kassadienst welche Karte verkauft haben. Eine Karte kann dabei nur von einem Kassadienstmitarbeiter verkauft werden. Kategorien werden identifiziert durch eine Bezeichnung (BEZ) z.B. Student, halbtags... Dazu wird noch eine genaue Beschreibung (BESCHREIBUNG) gespeichert. Karten können mehrere Kategorien zugeordnet sein. Eine Jahreskarte ist eine besondere Form der Karte. Es wird hierfür gespeichert welche Kunden welche Jahreskarten besitzen. Eine Jahreskarte ist hingegen einem einzigen Kunden zugeordnet. Ein Kunde ist eindeutig identifiziert duch eine Kundennummer (KNR). Zu jedem Kunden wird auch der Name (NAME), die Adresse (ADRESSE) sowie ein Foto (FOTO) gespeichert.
EER
1) Geben Sie jene Kassadienst-Mitarbeiter aus, die ab dem 1. Januar 1970 geboren wurden. Verwenden Sie dazu die Funktion TO_DATE und folgendes Datumsformat: DD-MM-YYYY. Ordnen Sie die Ergebnisse absteigend nach der Sozialversicherungsnummer.
select *
from kassadienst natural join mitarbeiter
where gebdat >= to_date ('01-01-1970' , 'DD-MM-YYYY')
order by svnr desc
2.1) Geben Sie das Geburtsdatum des aeltesten Bademeisters aus.
select min(gebdat)
from mitarbeiter natural join bademeister
2.2) Geben Sie die Sozialversicherungsnummer, den Namen und das Geburtsdatum des aeltesten Bademeisters aus.
select svnr, name, gebdat
from bademeister natural join mitarbeiter
where gebdat= (select min (gebdat)
from bademeister natural join mitarbeiter)
2.3) Geben Sie die Sozialversicherungsnummer, den Namen und das Geburtsdatum des aeltesten Bademeisters mit der Ausbildung 'Rettungsschwimmer' aus.
select svnr, name, gebdat
from bademeister natural join mitarbeiter
where gebdat = (select min(gebdat)
from bademeister natural join mitarbeiter
where Ausbildung = 'Rettungsschwimmer')
3.1) Geben Sie eine Liste aller Jahreskartenbesitzer aus, auf der die Kundennummer und der Name des Kunden zusammen mit der laufenden Tagesnummer der Jahreskarte(n) verzeichnet sind.
select name, knr, lfdnr from JAHRESKARTE left outer join kunde on (kunde=knr)
3.2) Geben Sie eine Liste ALLER Kunden (knr, name) aus zusammen mit der laufenden Tagesnummer der Jahreskarte(n), die sie besitzen. Fuer Kunden, die keine Jahreskarte haben, soll stattdessen 'keine Karte' ausgegeben werden.(Stichwort: COALESCE)
select k.name, k.knr, coalesce (j.lfdnr, 'keine Karte') as Tagesnummer
from jahreskarte j right outer join kunde k on (j.kunde=k.knr)
3.3) Geben Sie eine Liste ALLER Kunden (knr, name) aus zusammen mit der laufenden Tagesnummer der Jahreskarte(n), die sie besitzen, sowie den Namen des Mitarbeiters, der sie ihnen verkauft hat. Fuer Kunden, die keine Jahreskarte haben, soll anstelle der Tagesnummer 'keine Karte' und anstelle des Namens des Mitarbeiters 'kein Verkaeufer' ausgegeben werden. (Stichwort: COALESCE)
select k.knr, k.name, coalesce(j.lfdnr,'keine Karte'), coalesce(m.name,'kein Verkaeufer')
from kunde k left join jahreskarte j on k.knr=j.kunde left join karte ka on j.lfdnr=ka.lfdnr and j.von=ka.von left join mitarbeiter m on ka.verkaeufer=m.svnr
4.1) Mit Aussnahme von PH-Wert Bestimmungen (bez = 'pH-Wert'), soll die Bezeichnung, der Wert und der Zeitpunkt aller Messwerte ausgegeben werden, die fuer Nichtschwimmer-Becken (Tiefe maximal 135 cm) erhoben wurden.
select m.bez, m.wert, m.zeitpkt
from becken b join messwert m on b.bez=m.becken
where tiefe <=135 and m.bez!='pH-Wert'
4.2) Gesucht ist die Bezeichnung jener Becken, fuer die noch nie ein pH-Wert (Messwert bez = 'pH-Wert') erhoben wurde und die keine Nichtschwimmer-Becken sind. (Die Tiefe betraegt fuer Nichtschwimmerbecken maximal 135 cm.)
select b.bez
from becken b left join messwert m on b.bez=m.becken
where b.tiefe > 135 and b.bez not in (select becken from messwert where bez='pH-Wert' )
5.1) Wieviele verschiedene Kategorien sind den Eintrittskarten mit der laufenden Tagesnummer '123123' zugeordnet?
select count(distinct kategorie)
from hat
where lfdnr = 123123
5.2) Geben Sie fuer ALLE Eintrittskarten die laufende Tagesnummer sowie den Preis aus und dazu die Anzahl der zugeordneten Kategorien.
select k.lfdnr, k.preis, count(h.kategorie)
from karte k left join hat h on k.lfdnr=h.lfdnr and k.von=h.von
group by k.lfdnr, k.von, k.preis
5.3) Geben Sie die laufende Tagesnummer sowie den Preis jener Eintrittskarten aus, denen am meisten Kategorien zugeordnet sind.
select lfdnr, preis
from karte natural left join hat
group by lfdnr, von, preis
having count(kategorie) >= all(select count(kategorie) from karte natural left join hat group by lfdnr, von, preis)
6) Geben Sie zu jeder Beckentiefe, die Bezeichnung jenes Beckens aus, fuer das die meisten Messwerte vorliegen. Ordnen Sie das Ergebnis aufsteigend nach der Beckentiefe. (Beckentiefen, zu denen es keine Messwerte gibt, brauchen Sie dabei nicht beruecksichtigen.)
select tiefe, becken
from (select tiefe, becken, count(zeitpkt) as anzahl
from messwert m join becken b on b.bez=m.becken
group by becken, tiefe) x
natural join
(select tiefe, max(anzahl) as anzahl
from (select tiefe, becken, count(zeitpkt) as anzahl
from messwert m join becken b on b.bez=m.becken
group by tiefe, becken) a
group by tiefe) y
order by tiefe asc
(Das Ergebnis ist zwar korrekt, aber der Lösungsweg ist eigenwillig und stimmt nicht mit der erwarteten Lösung überein.
Das Ergebnis ist zwar korrekt, aber der Lösungsweg ist möglichweise eine ungültige Abkürzung. Formulieren Sie die Abfrage so, dass die Lösung auch mit anderen Daten korrekt wäre!)
7) Gesucht ist der Name und das Geburtsdatum jenes Bademeisters der bereits Rettungsdienste in ALLEN Becken mit der Tiefe von 260cm uebernommen hat, aber erst weniger als drei Massnahmen durchgefuehrt hat.
select name, gebdat
from mitarbeiter natural join (select svnr
from MASSNAHME m join mitarbeiter m2 on m.bademeister=m2.svnr
group by svnr having count)
where svnr in (select distinct bademeister as svnr
from BECKEN b join rdienst r on b.bez = r.becken
where tiefe = 260)
(Die Eingabe ist fehlerhaft)
8) Wieviele Messwerte werden pro Becken durchschnittlich erhoben?
select avg(anzahl)
from (select b.bez, count(zeitpkt) as anzahl
from becken b left join messwert m on b.bez=m.becken
group by b.bez) a
9) Geben Sie die Bezeichnung und Tiefe jener Becken aus, fuer die bereits alle verschiedenen Messwerte (z.B. Clor, pH-Wert, etc.) erhoben wurden.
select distinct tiefe, b.bez
from messwert m join becken b on b.bez=m.becken
where b.bez in (select becken
from messwert
group by becken having count(bez) >= all(select count(bez)
from messwert
group by becken))
10) Geben Sie eine Liste ALLER Kategorien (Bezeichnung und Beschreibung) aus und dazu die Anzahl der seit dem '01.01.2011' verkauften Eintrittskarten und wieviel diese in Summe gekostet haben. Verwenden Sie dazu die Funktion TO_DATE und folgendes Datumsformat: DD-MM-YYYY.
select bez, beschreibung, count(lfdnr) as Anzahl, coalesce(sum(preis),0)
from kategorie k1 left join hat h on k1.bez=h.kategorie left join (select * from karte where von >= to_date('01-01-2011','DD-MM-YYYY')) k2 on h.lfdnr=k2.lfdnr and h.von=k2.von
group by bez, beschreibung
Leave a Comment