Das Museum unterteilt sich in mehrere Räume, die eine eindeutige Raumnummer (RNR) besitzen. Zu jedem Raum sind eine Beschreibung (BESCHREIBUNG) und die Fläche (FLAECHE) abrufbar. Manche der Räume werden als Magazine genutzt. In diesem Fall wird eingetragen, welcher Klimaanlagentyp (KLIMAANLAGE) für die richtige Temperatur zur Lagerung der Kunstwerke sorgt. Die wichtigsten Räume sind die Ausstellungsräume, in denen vermerkt wird, welche Alarmanlage (ALARMANLAGE) jeweils zur Sicherung der wertvollen Ausstellungsstücke installiert ist. In der Datenbank werden weiters alle Exponate des Museums verzeichnet. Jedes Exponat kann über seine Inventarnummer (INVNR) eindeutig identifiziert werden. Zu jedem Exponat werden die Kategorie, die Bezeichnung, der Schätzwert sowie die Versicherungssumme (KATEGORIE, BEZEICHNUNG, SCHAETZWERT, VERSSUMME) abgespeichert. Dabei befinden sich nicht alle Exponate im Besitz des Museums. Manche sind Leihgaben, deren Besitzer (BESITZER) ebenfalls erfasst ist. Die Kunstwerke der Sammlung wurden von den verschiedenen Künstler unterschiedlicher Epochen geschaffen. In der Datenbank wird daher eine Liste aller Künstler angelegt. Jeder Künstler erhält dabei eine eindeutige, fortlaufende Nummer (KNR), außerdem werden der Name (NAME) sowie die Epoche (EPOCHE), in der der Künstler hauptsächlich gewirkt hat, abgespeichert. Sofern bekannt werden die an einem Exponat beteiligten Künstler (bei Gemeinschaftsarbeiten auch mehrere) erfasst. Bei manchen Exponaten ist jedoch der Künstler nicht mehr bekannt. Das Museum veranstaltet regelmäßig Ausstellungen, die gewissen Themenbereichen gewidmet sind. Jede Ausstellung trägt dabei einen eindeutigen Namen (NAME), dazu werden noch Beginn und Ende (VON, BIS) der Ausstellung erfasst. In der Datenbank wird für jede Ausstellung verzeichnet, welche Exponate in welchen Räumen gezeigt werden. Die Datenbank erfasst auch alle Angestellten des Museums. Diese können durch die Kombination aus Sozialversicherungsnummer (SVNR) und Geburtsdatum (GEBDATUM) eindeutig identifiziert werden, weiters werden der Name und das Gehalt (NAME, GEHALT) der Angestellten gespeichert. Manche der Angestellten werden als Aufseher eingesetzt, von denen der Ausbildungsgrad (AUSBILDUNG) bekannt ist. Von bereits gekündigten Mitarbeitern wird zusätzlich das Austrittsdatum (DATUM) gespeichert. Jede Ausstellung wird von genau einem Mitarbeiter geleitet, wobei es auch Angestellte geben kann, die mehrere Ausstellungen leiten. Zusätzlich kann jede Ausstellung von beliebig vielen Angestellten betreut werden, wobei auch hier Angestellte an mehreren Ausstellungen mitwirken können. Die Angestellten sind auch für die Restaurierung der Exponate zuständig. Exponate können von beliebig vielen Mitarbeitern restauriert werden, und jeder Mitarbeiter kann beliebig viele Exponate restaurieren, also durchaus auch keine. Für jede Ausstellung werden verschiedene Eintrittskartentypen aufgelegt. Jeder Eintrittskartentyp identifiziert sich über die Ausstellung, für die die Eintrittskarte gültig ist, sowie den Typ der Eintrittskarte (Erwachsene, Kinder, Gruppentickets etc.). Außerdem wird noch der Preis (PREIS) des Eintrittskartentyps vermerkt. Beim Kauf von Eintrittskarten erhalten die Kunden des Museums eine Rechnung, auf denen genau verzeichnet ist, welche Typen von Eintrittskarten und wie viele (ANZAHL) gekauft wurden. Jede Rechnung hat dabei eine eindeutige Rechnungsnummer (RECHNNR), dazu wird noch das Datum (DATUM) des Museumsbesuchs ersichtlich gemacht.
1)
Geben Sie Namen und Beginndatum aller Ausstellungen aus.
select name, von from ausstellungen
2)
Geben Sie Kategorie, Bezeichnung und Schätzwert aller Exponate aus. Sortieren Sie die Liste nach dem Schätzwert.
select kategorie, bezeichnung, schaetzwert from exponate order by schaetzwert
3)
Geben Sie die den Namen und das Gehalt jener Angestellten aus, die vor 01-06-1980 geboren wurden und mehr als 3000 Euro verdienen. Verwenden Sie dazu die Funktion TO_DATE und folgendes Datumsformat: DD-MM-YYYY
select name, gehalt
from angestellte
where gebdatum<TO_DATE('01-06-1980', 'DD-MM-YYYY')
and gehalt>3000;
4)
Geben Sie Bezeichnung, Kategorie und Besitzer aller Leihgaben aus.
select bezeichnung, kategorie, besitzer from exponate natural join leihgaben;
5.1)
Wieviele Angestellte betreuen die Ausstellung 'Die Renaissance - Wiedergeburt der Antike'?
select count(*) as anzahl from betreuen where name='Die Renaissance - Wiedergeburt der Antike';
5.2)
Geben Sie Namen und das Beginndatum der betreuten Ausstellungen zusammen mit der Anzahl der betreuenden Mitarbeiter aus.
select name, von, count(*) as anzahl from betreuen natural join ausstellungen group by name, von;
5.3)
Geben Sie Namen und das Beginndatum jener Ausstellungen aus, die von den meisten Mitarbeitern betreut werden, sowie die Anzahl der Mitarbeiter.
select name, von, count(*) as anzahl from ausstellungen natural join betreuen group by name, von having count(*)>= ALL (select count(*) from betreuen group by name);
select name, von, count(*) as anzahl from ausstellungen natural join betreuen group by name, von having count(*)>= (select max(anzahl) from (select count(*) as anzahl from betreuen group by name));
6)
Wieviel Geld wurde insgesamt vom Verkauf an Karten mit einem Preis unter 10 Euro eingenommen?
select sum(anzahl*preis) as einnahmen from verkaufen join eintrittskarten using (name, typ) where preis < 10;
7)
Geben Sie aus welche Angestellten (SVNR und Name) welche Exponate (Kategorie und Bezeichnung) restaurieren.
select a.svnr, a.name, e.kategorie, e.bezeichnung from exponate e, restaurieren r, angestellte a where e.invnr=r.invnr and r.svnr=a.svnr and r.gebdatum=a.gebdatum;
8.1)
Geben Sie eine Liste ALLER Exponate (Kategorie und Bezeichnung) aus und die Künstler (KNR), die es geschaffen haben. Falls zu einem Exponat kein Künstler gespeichert ist, geben Sie stattdessen die Nummer 999 aus. (Tipp: Verwenden Sie COALESCE)
select kategorie, bezeichnung, COALESCE(knr, 999) as knr from exponate left outer join schaffen using (invnr);
8.2)
Geben Sie eine Liste ALLER Exponate (Kategorie und Bezeichnung) aus und die Künstler (Name) aus der Renaissance, die es geschaffen haben. Falls zu einem Exponat keine Renaissance-Künstler gespeichert sind, geben Sie stattdessen 'keine Kuenstler aus der Renaissance' aus.
select e.kategorie, e.bezeichnung, COALESCE(k.name, 'keine Kuenstler aus der Renaissance') as name from exponate e left outer join (schaffen s join kuenstler k on(s.knr=k.knr and k.epoche='Renaissance')) on (e.invnr=s.invnr);
select e.kategorie, e.bezeichnung, COALESCE(s.name, 'keine Kuenstler aus der Renaissance') as name from exponate e left outer join (select * from join kuenstler using (knr) where epoche='Renaissance') s on (e.invnr=s.invnr);
9.1)
Geben Sie den Preis der billigsten Eintrittskarte aus.
select min(preis) as preis from eintrittskarten;
9.2)
Geben Sie eine Liste aller Ausstellungen (Name) aus, bei denen die billigste Eintrittskarte ausgegeben wurde.
select name from eintrittskarten where preis=(select min(preis) from eintrittskarten);
select name from eintrittskarten where preis <= ALL(select preis from eintrittskarten);
select e.name from eintrittskarten e where not exists(select * from eintrittskarten where preis<e.preis);
9.3)
Geben Sie eine Liste aller Ausstellungen (Name) aus, bei denen die billigste Eintrittskarte vom Typ Erwachsene ausgegeben wurde.
select name from eintrittskarten where typ='Erwachsene' and preis=(select min(preis) from eintrittskarten where typ='Erwachsene');
select name from eintrittskarten where typ='Erwachsene' and preis<= ALL(select preis from eintrittskarten where typ='Erwachsene');
select e.name from eintrittskarten e where e.typ='Erwachsene' and not exists (select * from eintrittskarten where typ='Erwachsene' and preis<e.preis);
10)
Geben Sie eine Liste jener Exponate (Kategorie und Bezeichnung) aus, die schon in allen Ausstellräumen ausgestellt waren.
select kategorie, bezeichnung from exponate join zeigen using (invnr) group by invnr, kategorie, bezeichnung having count(distinct rnr) >= (select count(*) from ausstellraeume);
select e.kategorie, e.bezeichnung from exponate e where not exists (select * from ausstellraeume where rnr not in (select rnr from zeigen where invnr=e.invnr));
11.1)
Geben Sie jene Exponate (Kategorie und Bezeichnung) aus, die nicht Skulpturen sind.
select kategorie, bezeichnung from exponate where kategorie != 'Skulptur';
11.2)
Geben Sie jene Exponate (Kategorie und Bezeichnung) aus, die nicht von einem Künstler der Renaissance geschaffen wurde.
select kategorie, bezeichnung
from exponate
where invnr not in (select invnr
from kuenstler natural join schaffen natural right outer join exponate
where epoche='Renaissance' or epoche is null);
12)
Geben Sie die grössten Räume (RNR und Beschreibung) aus, die nicht für Ausstellungen genutzt werden.
select rnr, beschreibung from raeume where rnr not in (select rnr from ausstellraeume) and flaeche=(select max(flaeche) from raeume where rnr not in (select rnr from ausstellraeume));
select rnr, beschreibung from raeume where rnr not in (select rnr from ausstellraeume) and flaeche>= ALL(select flaeche from raeume where rnr not in (select rnr from ausstellraeume));
select r.rnr, r.beschreibung from raeume r where r.rnr not in (select rnr from ausstellraeume) and not exists (select * from raeume where rnr not in (select rnr from ausstellraeume) and flaeche>r.flaeche);
13)
Gesucht sind jene Leihgaben (INVNR, Kategorie, Bezeichnung) mit einem Schätzwert von mehr als 50.000 Euro, die noch nie restauriert wurden, die aber bereits in einer Ausstellung gezeigt wurden.
select distinct invnr, kategorie, bezeichnung from exponate natural join leihgaben natural join zeigen where schaetzwert>50000 and invnr not in (select invnr from restaurieren);
select distinct e.invnr, e.kategorie, e.bezeichnung from exponate e join leihgaben l on(e.invnr=l.invnr) join zeigen z on (e.invnr=z.invnr) where e.schaetzwert>50000 and not exists (select * from restaurieren where invnr=e.invnr);
14)
Geben Sie den jüngsten Aufseher (Geburtsdatum und Name) aus, der ein Kunststudium absolviert hat.
select gebdatum, name from aufseher natural join angestellte where ausbildung='Kunststudium' and gebdatum= (select max(gebdatum) from aufseher where ausbildung='Kunststudium');
select gebdatum, name from aufseher natural join angestellte where ausbildung='Kunststudium' and gebdatum >= ALL(select gebdatum from aufseher where ausbildung='Kunststudium');
select a.gebdatum, a.name from aufseher a2 join angestellte a on (a.svnr=a2.svnr and a.gebdatum=a2.gebdatum) where ausbildung='Kunststudium' and not exists (select * from aufseher where ausbildung='Kunststudium' and gebdatum>a.gebdatum);
15)
Geben Sie jene Leihgaben (Kategorie und Bezeichnung) aus, die schon bei allen Ausstellungen gezeigt wurden.
select kategorie, bezeichnung from exponate natural join leihgaben natural join zeigen group by invnr, kategorie, bezeichnung having count(*) = (select count(*) from ausstellungen)
select kategorie, bezeichnung from exponate e join leihgaben l on (e.invnr=l.invnr) where not exists (select * from ausstellungen where name not in (select name from zeigen where invnr=e.invnr));
16)
Geben Sie jene Künstler (Nummer und Name) aus der Renaissance aus, die die meisten Exponate geschaffen haben.
select knr, name from schaffen natural join kuenstler where epoche='Renaissance' group by knr, name having count(*) >= ALL(select count(*) from schaffen natural join kuenstler where epoche='Renaissance' group by knr);
select knr, name from schaffen natural join kuenstler where epoche='Renaissance' group by knr, name having count(*) = (select max(anzahl) from (select count(*) as anzahl from schaffen natural join kuenstler where epoche='Renaissance' group by knr));
17)
Geben Sie eine Liste von Rechnungen (Rechnungsnummer und Datum) gemeinsam mit der Rechnungssumme aus.
select r.rechnnr, r.datum, sum(v.anzahl*e.preis) as rechnungssumme from rechnungen r join (verkaufen v join eintrittskarten e on (v.name=e.name and v.typ=e.typ)) on (v.rechnnr=r.rechnnr) group by r.rechnnr, r.datum;
select r.rechnnr, r.datum, sum(v.anzahl*v.preis) as rechnungssumme from rechnungen r join (select * from verkaufen join eintrittskarten using(name, typ)) v on (v.rechnnr=r.rechnnr) group by r.rechnnr, r.datum;
18)
Geben Sie jene Ausstellungen (Name)mit den meisten Einnahmen aus, und wieviel das ist.
select name, sum(anzahl*preis) as einnahmen from eintrittskarten join verkaufen using(name,typ) group by name having sum(anzahl*preis) >= ALL (select sum(anzahl*preis) from eintrittskarten join verkaufen using(name,typ) group by name);
select name, sum(anzahl*preis) as einnahmen from eintrittskarten join verkaufen using(name,typ) group by name having sum(anzahl*preis) = (select max(geld) from (select sum(anzahl*preis) as geld from eintrittskarten join verkaufen using(name,typ) group by name));
19)
Gesucht sind Multitalente unter den Angestellten (Name und Gehalt). Sie sind noch im Unternehmen beschäftigt, betreuen mindestens eine Ausstellung und haben schon einmal mindestens ein Exponat restauriert.
select name, gehalt from angestellte where (svnr, gebdatum) in (select svnr, gebdatum from betreuen) and (svnr, gebdatum) in (select svnr, gebdatum from restaurieren) and (svnr, gebdatum) not in (select svnr, gebdatum from kuendigung);
select distinct a.name, a.gehalt from angestellte a join betreuen b on (a.svnr=b.svnr and a.gebdatum=b.gebdatum) where exists (select * from restaurieren where svnr=a.svnr and gebdatum=a.gebdatum) and not exists (select * from kuendigung where svnr=a.svnr and gebdatum=a.gebdatum);
20)
Geben Sie eine Liste ALLER Räume (RNR und Beschreibung) aus und notieren Sie dazu, ob es sich um einen Ausstellraum (Ausstellraum) ein Magazin (Magazin) oder einem Sonstigen Raum (Sonstige) handelt.
select rnr, beschreibung, 'Ausstellraum' as art from ausstellraeume natural join raeume union select rnr, beschreibung, 'Magazin' as art from magazine natural join raeume union select rnr, beschreibung, 'Sonstige' as art from raeume where rnr not in (select rnr from magazine) and rnr not in (select rnr from ausstellraeume);
(select rnr, beschreibung, 'Ausstellraum' as art from ausstellraeume natural join raeume) union (select rnr, beschreibung, 'Magazin' as art from magazine natural join raeume) union (select rnr, beschreibung, 'Sonstige' as art from raeume where rnr not in (select rnr from magazine union select rnr from ausstellraeume));
21)
Geben Sie eine Liste ALLER Aufseher (SVNR, Name und Ausbildung) aus und wieviele Ausstellungen Sie leiten.
select a.svnr, a.name, a2.ausbildung, count(a3.name) as ausstellungen from (angestellte a join aufseher a2 on (a.svnr=a2.svnr and a.gebdatum=a2.gebdatum)) left outer join ausstellungen a3 on (a.svnr=a3.leitersvnr and a.gebdatum=a3.leitergebdat) group by a.svnr, a.gebdatum, a.name, a2.ausbildung;
select a.svnr, a2.name, a.ausbildung, (select count(*) from ausstellungen where leitersvnr=a.svnr and leitergebdat=a.gebdatum) as ausstellungen from angestellte a2 join aufseher a on (a.svnr=a2.svnr and a.gebdatum=a2.gebdatum)
select a.svnr, a.name, a.ausbildung, count(a3.name) as ausstellungen from (select * from angestellte natural join aufseher) a left outer join ausstellungen a3 on (a.svnr=a3.leitersvnr and a.gebdatum=a3.leitergebdat) group by a.svnr, a.gebdatum, a.name, a.ausbildung;
22)
Geben Sie ausgestellte Exponate (Kategorie und Bezeichnung) aus und wie oft sie schon in einer Ausstellung vor 2009 ausgestellt wurden. Verwenden Sie dzu folgendes Datumsformat: YYYY
select kategorie, bezeichnung, count(distinct name) as anzahl from exponate natural join zeigen natural join ausstellungen where von<TO_DATE(2009, 'YYYY') group by invnr, kategorie, bezeichnung
23)
Geben Sie eine Liste ALLER Epochen aus und die Anzahl der Exponate dazu.
select epoche, count(distinct invnr) as anzahl from kuenstler left outer join schaffen using(knr) group by epoche;
24)
Geben Sie den durchschnittlichen Schätzwert aller Exponate der Kategorie Oelmalerei aus.
select avg(schaetzwert) as durchschnitt from exponate where kategorie='Oelmalerei';