Musikproduzent - Die Datenbank

Beschreibung der Datenbank

Mitarbeiter besitzen eine Personalnummer (PNR), über die sie eindeutig identifiziert werden können, eine eindeutige Sozialversicherungsnummer (SVNR), und es werden zusätzlich Vorname (VNAME), Nachname (NNAME), Eintrittsdatum (EINTRITTSDAT) und Gehalt (GEHALT) gespeichert. Es gibt zwei Arten von Mitarbeitern: Organisatoren und Aufnahmeleiter. Bei den Aufnahmeleitern wird auch die Berufserfahrung (ERFAHRUNG) in Jahren gespeichert. In jedem Studio arbeiten mehrere Mitarbeiter. Ein Mitarbeiter arbeitet in einem oder mehreren Studios. Jedes Studio wird von einem der Mitarbeiter geleitet. Ein Studio wird eindeutig identifiziert durch die Postleitzahl (PLZ) und der Straße (STRASSE). Innerhalb eines Studios gibt es mehrere Räume. Ein Raum ist identifiziert durch das Studio und die Raumnummer (RAUMNR). Ein Raum wird von einem Aufnahmeleiter gewartet, ein Aufnahmeleiter kann mehrere Räume warten. Bei einem Künstler sind Vorname (VNAME), Nachname (NNAME) und Adresse bekannt. Ein Künstler ist eindeutig bestimmbar durch seinen Künstlernamen (KNAME). Von einem Künstler gibt es Alben, die über den Künstler und eine AlbumID (ALBUMID) gefunden werden können und haben einen Albumnamen (ANAMEN) und das Erscheinungsjahr, in welchem es produziert worden ist, gespeichert. Ein Album besteht aus mehreren Liedern, die durch das Album und eine LiedID (LIEDID) identifiziert sind und einen Liednamen (LNAME) haben. Ein Aufnahmeleiter ist zuständig für die Aufnahme von Liedern. Diese finden in einem oder mehreren Räumen statt. Ein Aufnahmeleiter kann auch Künstler beraten. Organisatoren können Events veranstalten (wobei für ein Event immer genau ein Organisator verantwortlich ist), an denen mehrere Künstler teilnehemen. Events werden durch einen Eventnamen (ENAME) und das Datum (DATUM) identifiziert und spielen einen Umsatz (UMSATZ)ein.

1)

Geben Sie alle Mitarbeiter (alle Attribute der Relation "Mitarbeiter") aus, die Aufnahmeleiter sind, nach dem 01.01.2010 eingestellt wurden und schon mehr als 3 Jahre Berufserfahrung haben. Sortieren Sie die Ausgabe aufsteigend nach deren Gehalt. Verwenden Sie die Funktion TO_DATE und das Datumsformat DD-MM-YYYY.

		
			SELECT m.*
			FROM Mitarbeiter m JOIN Aufnahmeleiter a ON m.pnr=a.pnr
			WHERE a.erfahrung>3
			AND eintrittsdat > TO_DATE('01-01-2010','DD-MM-YYYY')
			ORDER BY m.gehalt;	
	
		

		
			select m.*
			from Mitarbeiter m
			where exists (select * from Aufnahmeleiter a where a.pnr = m.pnr and erfahrung>3)
			and eintrittsdat>to_date('01-01-2010','DD-MM-YYYY')
			order by gehalt;	
	
		

2)

Geben Sie Vor- und Nachname jener Aufnahmeleiter aus, die bereits mehr als 2 Künstler beraten haben.

		
			SELECT m.vname, m.nname
			FROM Mitarbeiter m JOIN beraet b ON m.pnr=b.pnr
			GROUP BY m.pnr, vname, nname
			HAVING count(*)>2;	
	
		

		
			select m.vname, m.nname
			from mitarbeiter m
			where m.pnr in (select pnr from beraet group by pnr having count(*) > 2)	
	
		

3.1)

Geben Sie die Anzahl aller Organisatoren aus, die schon ein Event veranstaltet haben, das mit dem Buchstaben S beginnt.

		
			SELECT count(distinct e.organisator) as anzahl
			FROM Event e
			WHERE e.ename LIKE 'S%';	
	
		

		
			select count(*)
			from (select distinct organisator from event
			where ename like 'S%')	
	
		

3.2)

Geben Sie Vorname und Nachname aller Organisatoren aus zusammen mit der Anzahl der Events, die sie veranstaltet haben.

		
			SELECT m.vname, m.nname, count(e.organisator) as anzahl
			FROM Mitarbeiter m JOIN Organisator o ON m.pnr=o.pnr LEFT OUTER JOIN Event e ON m.pnr=e.organisator
			GROUP BY m.pnr, m.vname, m.nname	
	
		

		
			select m.VNAME, m.NNAME, (select count(e.ENAME) 
			                          from EVENT e 
			                          where m.PNR = e.ORGANISATOR)
			from MITARBEITER m, ORGANISATOR o
			where m.PNR = o.PNR	
	
		

3.3)

Geben Sie Vor- und Nachnahme der Organisatoren aus, die die Events mit dem meisten Umsatz veranstaltet haben, zusammen mit dem Umsatz.

		
			SELECT m.vname, m.nname, e.umsatz
			FROM Mitarbeiter m JOIN Event e ON m.pnr=e.organisator
			WHERE e.umsatz >= (SELECT max(umsatz) FROM Event);	
	
		

		
			SELECT m.vname, m.nname, e.umsatz
			FROM Mitarbeiter m JOIN Event e ON m.pnr=e.organisator
			WHERE e.umsatz >= all (SELECT umsatz FROM Event);	
	
		

4)

Gesucht sind Personalnummer, Vor- und Nachname jener Organisatoren, die die meisten Events veranstaltet haben. Sie dürfen annehmen, dass mindestens ein Event in der Datenbank gespeichert ist.

		
			SELECT e.organisator, m.vname, m.nname
			FROM Mitarbeiter m JOIN Event e ON m.pnr=e.organisator
			GROUP BY organisator, vname, nname
			HAVING count(organisator) >= ALL(
			        SELECT count(organisator)
			        FROM Event
			        GROUP BY organisator
			        );	
	
		

5.1)

Geben Sie alle Organisatoren (alle Attribute der Relation "Mitarbeiter") aus, die kein Studio leiten.

		
			SELECT m.*
			FROM Mitarbeiter m JOIN Organisator o ON m.pnr=o.pnr
			WHERE m.pnr NOT IN (
			        SELECT leiter
			        FROM Studio
			        );	
	
		

		
			SELECT m.*
			FROM Mitarbeiter m JOIN Organisator o ON m.pnr=o.pnr
			WHERE m.pnr <> all (
			        SELECT leiter
			        FROM Studio
			        );	
	
		

5.2)

Geben Sie alle Mitarbeiter aus, die in jedem Studio arbeiten aber keine Organisatoren sind.

		
			SELECT m.*
			FROM Mitarbeiter m
			WHERE m.pnr NOT IN (SELECT pnr FROM Organisator)
			AND NOT EXISTS (SELECT s.* FROM Studio s
			        WHERE NOT EXISTS (SELECT * FROM arbeitet_in a
			                WHERE s.plz=a.plz and s.strasse=a.strasse and m.pnr=a.pnr));	
	
		

		
			(SELECT m.*
			FROM Mitarbeiter m
			WHERE NOT EXISTS (SELECT s.* FROM Studio s
			        WHERE NOT EXISTS (SELECT * FROM arbeitet_in a
			                WHERE s.plz=a.plz and s.strasse=a.strasse and m.pnr=a.pnr)))
			MINUS
			(Select m.* from mitarbeiter m join organisator on m.pnr = organisator.pnr);	
	
		

		
			SELECT m.*
			FROM Mitarbeiter m join arbeitet_in a on a.pnr=m.pnr
			WHERE
			NOT EXISTS (SELECT * FROM Organisator where Organisator.pnr = m.pnr)
			group by m.pnr, vname, nname,  gehalt, eintrittsdat, svnr
			having count(*) = (select count(*) from studio);	
	
		

		
			(SELECT m.*
			FROM Mitarbeiter m join arbeitet_in a on a.pnr=m.pnr
			group by m.pnr, vname, nname,  gehalt, eintrittsdat, svnr
			having count(*) = (select count(*) from studio))
			MINUS
			(Select m.* from mitarbeiter m join organisator on m.pnr = organisator.pnr);	
	
		

5.3)

Geben Sie alle Mitarbeiter aus, die schon in jedem Raum eine Aufnahme geleitet haben und mehr als 3 Jahre Berufserfahrung haben.

		
			SELECT m.*
			FROM Mitarbeiter m JOIN Aufnahmeleiter al ON m.pnr=al.pnr AND al.erfahrung >3
			WHERE NOT EXISTS (SELECT r.* FROM Raum r
			WHERE NOT EXISTS (SELECT * FROM aufnahme a
			WHERE r.plz=a.raumplz AND r.strasse=a.raumstr AND r.raumnr=a.raumnr AND m.pnr=a.aufnahmeleiter));	
	
		

		
			SELECT m.* 
			FROM Mitarbeiter m join Aufnahmeleiter a on m.pnr=a.pnr
			WHERE (SELECT count(*) FROM (SELECT distinct raumplz, raumstr, raumnr, aufnahmeleiter from aufnahme) where aufnahmeleiter=a.pnr) = (SELECT count(*) from Raum)
			AND erfahrung>3	
	
		

6.1)

Geben Sie Vor- und Nachname ALLER Künstler aus, zusammen mit den Namen des Events, an denen sie beteiligt waren. Für die Künstler, die noch nicht aufgetreten sind, soll 'kein Auftritt' ausgegeben werden.

		
			SELECT k.vname, k.nname, coalesce(n.ename, 'kein Auftritt') as Event
			FROM Kuenstler k LEFT OUTER JOIN nimmt_teil n ON k.kname=n.kuenstler;	
	
		

		
			(SELECT k.vname, k.nname, n.ename as Event
			FROM Kuenstler k JOIN nimmt_teil n ON k.kname=n.kuenstler)
			UNION
			(SELECT k.vname, k.nname, 'kein Auftritt' as Event
			FROM Kuenstler k
			where k.kname not in (SELECT kuenstler from nimmt_teil));	
	
		

6.2)

Geben Sie Vor- und Nachname ALLER Künstler aus zusammen mit den Events, auf den sie aufgetreten sind. Für alle Künstler, die noch an keinem Event, welches mehr als 5000 Umsatz gemacht hat, beteiligt waren, soll 'kein Event mit hohem Umsatz' ausgegeben werden.

		
			SELECT k.kname, k.vname, k.nname, coalesce(a.ename, 'kein Event mit hohem Umsatz') as Event
			FROM Kuenstler k LEFT OUTER JOIN
			        (SELECT n.ename, n.edatum, e.umsatz, n.kuenstler
			         FROM nimmt_teil n JOIN Event e ON n.ename=e.ename AND n.edatum=e.datum
			         WHERE umsatz > 5000) a ON k.kname=a.kuenstler;	
	
		

7)

Geben Sie Vor- und Nachname aller Künstler aus zusammen mit der Anzahl an Alben, die sie vor 2008 produziert haben und der Gesamtanzahl der Lieder, die sich auf diesen Alben befinden.

		
			SELECT k.vname, k.nname,
			(SELECT count(*)
			 FROM Album a where a.kname=k.kname AND a.jahr<2008) as Alben,
			(SELECT count(*)
			 FROM Lied l JOIN Album a1 ON l.kname=a1.kname and l.albumid=a1.albumid
			 WHERE l.kname=k.kname AND a1.jahr<2008) as Lieder
			FROM Kuenstler k
			GROUP BY k.kname, k.vname, k.nname;	
	
		

8)

Geben Sie alle Mitarbeiter (PNR, Vorname, Nachname) aus zusammen mit deren Funktion (Organisator oder Aufnahmeleiter - Sie können davon ausgehen, dass jeder Mitarbeiter entweder Organisator oder Aufnahmeleiter ist)

		
			(SELECT m.pnr, m.vname, m.nname, 'Organisator' as Funktion
			 FROM Mitarbeiter m JOIN Organisator o ON m.pnr=o.pnr)
			UNION
			(SELECT m1.pnr, m1.vname, m1.nname, 'Aufnahmeleiter' as Funktion
			 FROM Mitarbeiter m1 JOIN Aufnahmeleiter a ON m1.pnr=a.pnr);	
	
		

		
			select tab.pnr,tab.vname,tab.nname,tab.funktion
			from
			( (select m.*, 'Organisator' as funktion
			from mitarbeiter m, organisator o
			where m.pnr = o.pnr)
			
			union
			
			(select m.*, 'Aufnahmeleiter' as funktion
			from mitarbeiter m, aufnahmeleiter auf
			where m.pnr = auf.pnr) ) tab	
	
		

9)

Geben Sie unter jenen Mitarbeitern, die kein Studio leiten, die meistverdienensten aus.

		
			SELECT a.*
			FROM (SELECT m.* FROM Mitarbeiter m
			          WHERE m.pnr NOT IN
			                  (SELECT leiter FROM Studio)) a
			WHERE gehalt >= (SELECT max(gehalt) FROM Mitarbeiter m1 WHERE m1.pnr NOT IN (SELECT leiter FROM Studio));	
	
		

		
			SELECT m.*
			FROM Mitarbeiter m join
			 ((SELECT pnr FROM Mitarbeiter)
			  MINUS
			  (SELECT leiter as pnr FROM Studio)) tmp on m.pnr=tmp.pnr
			WHERE gehalt >= all (SELECT gehalt from Mitarbeiter natural join
			     ((SELECT pnr FROM Mitarbeiter)
			      MINUS
			      (SELECT leiter as pnr FROM Studio)));