Restaurant - Die Datenbank

Beschreibung der Datenbank

Ein Restaurant wird eindeutig durch seine Restaurantnummer (RESTNR) und den Standort (STANDORT) identifiziert. Jedes Restaurant wird von genau einem Mitarbeiter geleitet, es können aber beliebig viele Mitarbeiter in einem oder mehreren Restaurants arbeiten. Ein Mitarbeiter wird eindeutig durch seine Personalnummer (PNR) identifiziert. Zusätzlich werden noch die Sozialversicherungsnummer (SVNR), sein Name (NAME), seine Anschrift (ANSCHRIFT) und das Eintrittsdatum (SEIT) gespeichert. Mitarbeiter können auch einen befristeten Arbeitsvertrag haben. Sollte dies der Fall sein wird zusätzlich noch das Ablaufdatum des befristeten Arbeitsvertrages (BIS) gespeichert. Neben allgemeinen Mitarbeitern gibt es auch noch Kellner. Jeder Kellner ist ein Mitarbeiter, es können aber nur Kellner für einen oder mehrere Tische zuständig sein. Aber für einen Tisch ist immer genau ein Kellner zuständig. Ein Tisch wird über seine Tischnummer (TISCHNR) eindeutig pro Restaurant identifiziert. Jedes Restaurant kann einen oder mehrere Tische enthalten. Auszerdem werden auch noch Bestellungen vom System erfasst. Eine Bestellung wird eindeutig über ihre Bestellnummer (BESTNR) identifiziert und ist immer genau einem Tisch zugeordnet. Pro Tisch kann es aber beliebig viele Bestellungen geben. Eine Bestellung besteht aus beliebig vielen Gerichten. Gerichte können in beliebig vielen Bestellungen zu einer bestimmten Stückzahl (STUECK) vorkommen. Ein Gericht wird eindeutig über seine Id (ID) identifiziert. Zusätzlich werden auch noch Name (NAME) und Preis (PREIS) des Gerichts vermerkt. Weiters werden auch Kunden in der Datenbank gespeichert. Ein Kunde wird über eine Id (ID) eindeutig identifiziert. Auszerdem werden noch der Name (NAME) und seine Telefonnummer (TELEFON) vermerkt. Ein Event wird eindeutig über sein Datum (DATUM) identifiziert. Auszerdem wird auch noch das Thema (THEMA) des jeweiligen Events gespeichert. An jedem Event können beliebig viele Kunden an beliebig vielen Tischen teilnehmen.

1)

Geben Sie alle KellnerInnen mit befristetem Dienstvertrag aus, deren Anschrift mit AUT endet. Sortieren Sie die Ergebnisse aufsteigend nach der SVNR.

		
			SELECT * FROM MitarbeiterIn
			WHERE pnr IN (SELECT pnr FROM Befristung)
			AND pnr in (SELECT pnr FROM KellnerIn)
			AND anschrift LIKE '%AUT'
			ORDER BY svnr ASC;	
	
		

		
			SELECT MitarbeiterIn.*
			FROM MitarbeiterIn JOIN Befristung ON MitarbeiterIn.pnr = Befristung.pnr
			WHERE MitarbeiterIn.pnr in (SELECT pnr FROM KellnerIn)
			AND anschrift LIKE '%AUT'
			ORDER BY svnr ASC;	
	
		

		
			SELECT MitarbeiterIn.*
			FROM MitarbeiterIn JOIN KellnerIn ON MitarbeiterIn.pnr = KellnerIn.pnr
			WHERE MitarbeiterIn.pnr IN (SELECT pnr FROM Befristung)
			AND anschrift LIKE '%AUT'
			ORDER BY svnr ASC;	
	
		

		
			SELECT MitarbeiterIn.*
			FROM MitarbeiterIn JOIN Befristung ON MitarbeiterIn.pnr = Befristung.pnr JOIN KellnerIn ON MitarbeiterIn.pnr = KellnerIn.pnr
			WHERE anschrift LIKE '%AUT'
			ORDER BY svnr ASC;	
	
		

2)

Geben Sie alle Kunden (Name, Telefon) aus, welche im vor dem 06-06-2013 noch an keinem Event teilgenommen haben. Verwenden Sie hierzu die Funktion TO_DATE mit dem Datumsformat 'DD-MM-YYYY'. Sortieren Sie die Ergebnisse absteigend nach dem Namen.

		
			SELECT name, telefon FROM Kunde
			WHERE id NOT IN (SELECT id FROM teilnehmen WHERE datum < to_date('06-06-2013', 'DD-MM-YYYY'))
			ORDER BY name DESC;	
	
		

		
			SELECT name, telefon FROM Kunde
			WHERE NOT EXISTS (SELECT id FROM teilnehmen WHERE datum < to_date('06-06-2013', 'DD-MM-YYYY') AND Kunde.id = teilnehmen.id)
			ORDER BY name DESC;	
	
		

3.1)

Geben Sie die Anzahl aller (unterschiedlichen) Events aus.

		
			SELECT count(*) As anzahl
			FROM Event;	
	
		

3.2)

Geben Sie Kunden (ID) aus, die an Events teilgenommen haben, zusammen mit der Anzahl an (unterschiedlichen) Events, an denen Sie teilgenommen haben.

		
			SELECT id, count(DISTINCT datum) AS anzahl
			FROM teilnehmen
			GROUP BY id;	
	
		

3.3)

Geben Sie Kunden (Name, Telefon) aus, die an Events teilgenommen haben, zusammen mit der Anzahl an (unterschiedlichen) Events, an denen Sie teilgenommen haben.

		
			SELECT Kunde.name, Kunde.telefon, count(DISTINCT datum) AS anzahl
			FROM Kunde JOIN teilnehmen ON Kunde.id = teilnehmen.id
			GROUP BY Kunde.id, Kunde.name, Kunde.telefon;	
	
		

3.4)

Geben Sie jene Kunden (Name, Telefon) aus, die an den meisten (unterschiedlichen) Events teilgenommen haben.

		
			SELECT Kunde.name, Kunde.telefon
			FROM Kunde JOIN teilnehmen ON Kunde.id = teilnehmen.id
			GROUP BY Kunde.id, Kunde.name, Kunde.telefon
			HAVING count(DISTINCT datum) >= ALL(SELECT count(DISTINCT datum)
			                                    FROM teilnehmen t
			                                    GROUP BY t.id);	
	
		

4.1)

Geben Sie das Eintrittsdatum der am laengsten beschaeftigten MitarbeiterInnen aus.

		
			SELECT min(seit) AS seit
			FROM MitarbeiterIn;	
	
		

		
			select seit
			from mitarbeiterin m
			group by seit
			having seit <= all(
			select seit from mitarbeiterin
			);	
	
		

		
			SELECT distinct Seit
			FROM Mitarbeiterin
			WHERE Seit <= ALL(SELECT Seit
			                     FROM Mitarbeiterin);	
	
		

4.2)

Geben Sie jene MitarbeiterInnen (SVNR, Anschrift) aus, welche am laengsten beschaeftigt sind.

		
			SELECT svnr, anschrift
			FROM MitarbeiterIn
			WHERE seit <= ALL(SELECT seit
			                  FROM MitarbeiterIn);	
	
		

		
			SELECT svnr, anschrift
			FROM MitarbeiterIn
			WHERE seit = (SELECT min(seit)
			              FROM MitarbeiterIn);	
	
		

4.3)

Geben Sie jene MitarbeiterInnen (SVNR, Anschrift) aus, welche am laengsten beschaeftigt sind und in mindestens einem Restaurant arbeiten.

		
			SELECT distinct svnr, anschrift
			FROM MitarbeiterIn JOIN arbeitet ON MitarbeiterIn.pnr = arbeitet.pnr
			WHERE seit <= ALL(SELECT seit
			                  FROM MitarbeiterIn JOIN arbeitet ON MitarbeiterIn.pnr = arbeitet.pnr);	
	
		

		
			SELECT distinct svnr, anschrift
			FROM MitarbeiterIn JOIN arbeitet ON MitarbeiterIn.pnr = arbeitet.pnr
			WHERE seit = (SELECT MIN(seit)
			              FROM MitarbeiterIn JOIN arbeitet ON MitarbeiterIn.pnr = arbeitet.pnr);	
	
		

		
			SELECT distinct svnr, anschrift
			FROM MitarbeiterIn
			WHERE pnr IN(SELECT pnr FROM arbeitet)
			AND seit = (SELECT MIN(seit)
			            FROM MitarbeiterIn
			            WHERE pnr IN(SELECT pnr FROM arbeitet));	
	
		

		
			SELECT distinct svnr, anschrift
			FROM MitarbeiterIn
			WHERE pnr IN(SELECT pnr FROM arbeitet)
			AND seit <= ALL(SELECT seit
			                FROM MitarbeiterIn
			                WHERE pnr IN(SELECT pnr FROM arbeitet));	
	
		

5)

Geben Sie alle MitarbeiterInnen (svnr, anschrift) aus. In einer zusaetzlichen Spalte soll vermerkt werden, um welche Art von MitarbeiterIn es sich handelt, also entweder um eine 'KellnerIn' oder eine 'LeiterIn'. Sie koennen davon ausgehen, dass eine MitarbeiterIn entweder KellnerIn, LeiterIn oder keines von beiden ist. Sollte sie weder KellnerIn noch LeiterIn sein, soll in der zusaetzlichen Spalte 'Sonstiges' stehen.

		
			SELECT svnr, anschrift, 'KellnerIn' FROM KellnerIn JOIN MitarbeiterIn ON KellnerIn.pnr = MitarbeiterIn.pnr
			UNION
			SELECT svnr, anschrift, 'LeiterIn' FROM MitarbeiterIn JOIN Restaurant ON pnr = leiter
			UNION
			SELECT svnr, anschrift, 'Sonstiges' From MitarbeiterIn WHERE pnr NOT IN (SELECT pnr
			                                                                         FROM KellnerIn
			                                                                         UNION
			                                                                         SELECT leiter FROM Restaurant);	
	
		

6)

Geben Sie alle MitarbeiterInnen aus, welche in jedem Restaurant arbeiten.

		
			SELECT pnr, name, svnr, anschrift, seit FROM MitarbeiterIn NATURAL JOIN arbeitet
			GROUP BY pnr, name, svnr, anschrift, seit
			HAVING count(*) >= ALL(SELECT count(*)
			                        FROM Restaurant);	
	
		

		
			SELECT m.*
			FROM MitarbeiterIn m
			WHERE NOT EXISTS(
			        SELECT *
			        FROM Restaurant r
			        WHERE NOT EXISTS(
			                SELECT *
			                FROM arbeitet a
			                WHERE a.restnr = r.restnr AND a.standort = r.standort AND a.pnr = m.pnr
			        )
			);	
	
		

7.1)

Geben Sie zu den Restaurants (Standort, RestNr) in denen Bestellungen existieren den Gesamtumsatz (Summe der Preise aller bestellten Gerichte mal ihrer Stueckzahl) aus.

		
			SELECT b.standort, b.restnr, sum(preis*stueck) AS tischsumme
			FROM  Bestellung b JOIN beinhaltet i ON b.bestnr = i.bestnr
			JOIN Gericht g ON i.id = g.id
			GROUP BY b.standort, b.restnr;	
	
		

7.2)

Geben Sie zu JEDEM Tisch (Standort, RestNr, TischNr) den Gesamtumsatz (Summe der Preise aller bestellten Gerichte mal ihrer Stueckzahl) aus. Tische ohne Bestellungen sollten also 0 Euro Umsatz gemacht haben (Verwenden Sie dazu die Funktion COALESCE).

		
			SELECT t.standort, t.restnr, t.tischnr, sum(COALESCE(preis*stueck, 0)) AS tischsumme
			FROM Tisch t LEFT OUTER JOIN Bestellung b
			ON t.tischnr = b.tischnr AND t.standort = b.standort AND t.restnr = b.restnr
			LEFT OUTER JOIN beinhaltet i ON b.bestnr = i.bestnr
			LEFT OUTER JOIN Gericht g ON i.id = g.id
			GROUP BY t.standort, t.restnr, t.tischnr;	
	
		

		
			SELECT t.standort, t.restnr, t.tischnr, COALESCE(
			            (SELECT sum(stueck * preis)
			             FROM bestellung best join beinhaltet bein on best.bestnr=bein.bestnr join gericht ger on bein.id=ger.id
			             WHERE best.standort=t.standort
			             AND t.restnr=best.restnr
			             AND t.tischnr=best.tischnr
			             GROUP BY best.standort, best.restnr, best.tischnr), 0) as tischsumme
			FROM tisch t;	
	
		

8.1)

Geben Sie jene KellnerInnen (name, anschrift) aus, die den wenigsten Umsatz gemacht haben.

		
			SELECT m.name, m.anschrift
			FROM KellnerIn k JOIN MitarbeiterIn m ON (k.pnr=m.pnr) JOIN Tisch t ON (k.pnr = t.zustaendig) JOIN Bestellung b ON (t.restnr=b.restnr and t.standort=b.standort and t.tischnr=b.tischnr) JOIN beinhaltet be ON (b.bestnr=be.bestnr) JOIN Gericht g ON (be.id=g.id)
			GROUP BY m.pnr, m.name, m.anschrift
			HAVING sum(preis*stueck) <= ALL( SELECT sum(preis*stueck)
			                                FROM  Tisch
			                                NATURAL JOIN Bestellung NATURAL JOIN beinhaltet NATURAL JOIN Gericht
			                                GROUP BY zustaendig);	
	
		

9)

Geben Sie eine Liste ALLER KellnerInnen (name) aus. Falls diese laenger als bis zum 06-06-2014 befristet sind, auch das Datum bis zu dem sie befristet sind. Ansonsten geben Sie das Defaultdatum 01-01-1999 aus. Verwenden Sie hierzu die Funktion TO_DATE mit dem Datumsformat 'DD-MM-YYYY'.

		
			SELECT m.name, COALESCE(b.bis, TO_DATE('01-01-1999', 'DD-MM-YYYY')) AS datum
			  FROM (kellnerin k join mitarbeiterin m on(k.pnr=m.pnr)) left outer join befristung b on (b.pnr=k.pnr and b.bis>TO_DATE('06-06-2014', 'DD-MM-YYYY'));	
	
		

10)

Geben Sie die LeiterIn jener Restaurants (name) aus, zu denen Events existieren, dazu noch wieviele Kunden durchschnittlich an diesen Events teilgenommen haben.

		
			SELECT m.name, avg(subt.teilnehmer) AS teilnehmer
			FROM (
			        SELECT standort, restnr, count(DISTINCT id) AS teilnehmer
			        FROM teilnehmen NATURAL JOIN Restaurant
			        GROUP BY standort, restnr, datum) subt
			JOIN restaurant r on (subt.standort=r.standort and subt.restnr=r.restnr) JOIN MitarbeiterIn m ON r.leiter = m.pnr
			GROUP BY r.leiter, m.name;