Schokoladefabrik - Die Datenbank

Beschreibung der Datenbank

Personen haben eine eindeutige Nummer, einen Vornamen und einen Nachnamen. Sie werden in Künstler und Mitarbeiter eingeteilt. Bei Mitarbeitern ist zusätzlich das Einstellungsdatum, und falls bereits wieder entlassen, das Kündigungsdatum bekannt. Zu Künstlern wird der Bekanntheitsgrad vermerkt. Der Bekanntheitsgrad ist ein Integerwert von 0 bis 10, wobei 0 Unbekanntheit bedeutet, 10 Weltberühmtheit. In der Schokoladefabrik werden verschiedene Produkte hergestellt, die über eine Nummer eindeutig identifiziert werden und von denen eine Bezeichnung sowie das Gewicht (in Gramm) bekannt sind. Zu den Produkten gehören das Standardsortiment, für welches ein Verkaufspreis und die Verpackungsart gespeichert werden, sowie Kunstwerke, deren Schätzwerte in der Datenbank vermerkt werden (Kunstwerke sind spezielle Schokolade- oder sonstige Skulpturen, die jeweils als Sonderanfertigung produziert werden). Die Schokoladefabrik besitzt verschiedene Maschinen, von denen eine eindeutige Nummer sowie eine Beschreibung gespeichert werden. Mitarbeiter bedienen die Maschinen und jede Maschine kann für die Produktion von betimmten Produkten eingeteilt sein. Weiters existieren Lager, welche von mindestens einem Mitarbeiter betreut werden. Ein Mitarbeiter betreut allerdings maximal ein Lager. Ein Lager hat eine es identifizierende Bezeichnung und eine bestimmte Fläche (in m²). Produkte werden eingelagert, wobei für jedes Produkt bekannt ist, welche Menge (Stückzahl) sich davon in welchem Lager befindet. Die Fabrik bekommt von verschiedenen Firmen Produktionsaufträge. Jede Firma wird durch ihren Namen identifiziert und es sind weiters eine Kontaktadresse und Telefonnummer bekannt. Pro Firma werden verschiedene Aufträge vergeben, über welche beliebig viele Produkte bei der Fabrik bestellt werden. Ein Auftrag wird durch eine pro Firma eindeutige Nummer identifiziert. Weiters sind das Auftragsdatum und der aktuelle Status bekannt. Die produzierten Kunstwerke werden von den dafür verantwortlichen Künstlern bei verschiedenen Kunstschauen hergezeigt. Hier wird vermerkt, welchen Platz ein Künstler mit seinem Kunstwerk bei der Schau erreicht. Die Kunstschauen selbst werden durch einen Namen und ein Datum identifiziert und es ist bekannt, in welchem Land und welchem Ort die Kunstschau stattfindet.

1)

Geben Sie die Bezeichnung und das Gewicht aller Produkte aus.

		
			select bezeichnung, gewicht from produkt	
	
		

2)

Geben Sie die Bezeichnung und den Schätzwert aller Kunstwerke aus und sortieren Sie die Liste nach Gewicht.

		
			select bezeichnung, schaetzwert from produkt natural join kunstwerk order by gewicht	
	
		

3)

Geben Sie die Bezeichnung und das Gewicht aller Kunstwerke aus, für die der Schätzwert kleiner als 5000 Euro ist und sortieren Sie das Ergebnis nach dem Schätzwert.

		
			select bezeichnung, gewicht from produkt natural join kunstwerk where
			schaetzwert<5000 order by schaetzwert	
	
		

4)

Geben Sie für alle gekündigten Mitarbeiter den Namen (Vorname und Nachname) und das Kündigungsdatum aus.

		
			select vorname, nachname, kuendigungsdatum
			from person natural join mitarbeiter natural join kuendigung	
	
		

		
			select vorname, nachname, kuendigungsdatum
			from person natural join kuendigung	
	
		

5.1)

Wieviele Maschinen werden benötigt um "Mika Fender" herzustellen?

		
			select count(*)
			from erzeugt
			where pnummer=(select nummer
			        from produkt
			        where bezeichnung='Mika Fender');	
	
		

		
			select count(*)
			from erzeugt join produkt on pnummer=nummer
			where bezeichnung='Mika Fender';	
	
		

5.2)

Geben Sie die Namen der Produkte aus, die von Maschinen erzeugt wurden und geben Sie die Anzahl der benötigten Maschinen aus.

		
			select bezeichnung, count(*)
			from erzeugt join produkt on pnummer=nummer
			group by pnummer, bezeichnung;	
	
		

5.3)

Geben Sie an, welche Produkte die meisten Maschinen für deren Herstellung benötigen. Geben Sie dafür die Bezeichnung und die Anzahl aus.

		
			select bezeichnung, count(*)
			from erzeugt join produkt on pnummer=nummer
			group by pnummer, bezeichnung
			having count(*)>=ALL(select count(*)
			                     from erzeugt
			                     group by pnummer);	
	
		

		
			select bezeichnung, count(*)
			from produkt p join erzeugt e on (p.nummer = e.pnummer)
			group by nummer, bezeichnung
			having count(*) = (select max(anzahl)
			from (select count (*) as anzahl from erzeugt group by pnummer));	
	
		

6)

Geben Sie den durchschnittlichen Preis aller Produte des Standardsortiments aus, die in Plastik ('Plastiksackerl' oder 'Plastikfolie bedruckt') verpackt sind.

		
			select avg(preis)
			from standardsortiment
			where verpackung='Plastiksackerl' OR verpackung='Plastikfolie bedruckt'	
	
		

		
			select avg(preis)
			from produkt natural join standardsortiment
			where verpackung='Plastiksackerl' OR verpackung='Plastikfolie bedruckt'	
	
		

7)

Geben Sie für alle Standardprodukte (Nummer und Bezeichnung) aus, mit welchen Maschinen (Nummer und Beschreibung) sie erzeugt wurden.

		
			select pnummer, bezeichnung, maschnummer, beschreibung
			from produkt, standardsortiment, erzeugt, maschine
			where produkt.nummer=standardsortiment.nummer and
			erzeugt.pnummer=produkt.nummer and
			erzeugt.maschnummer=maschine.nummer	
	
		

		
			select pnummer, bezeichnung, maschnummer, beschreibung
			from produkt natural join standardsortiment
			     join erzeugt on nummer=pnummer
			     join maschine on maschnummer=maschine.nummer;	
	
		

8.1)

Geben Sie eine Liste ALLER Kunstwerke (Bezeichnung) aus und bei welchen Kunstschauen (Name und Datum) sie ausgestellt wurden. Wenn ein Kunstwerk nicht ausgestellt wurde geben Sie an Stelle des Namens der Ausstellung "unter Verschluss" und statt dem Datum das aktuelle Datum aus (Stichwort COALESCE),

		
			select bezeichnung, COALESCE(datum,current_date), COALESCE(name,'unter Verschluss')
			from kunstwerk natural join produkt left outer join zeigt on nummer=kunstwerknummer;	
	
		

8.2)

Geben Sie eine Liste ALLER Kunstwerke aus und wenn das Kunstwerk in Österreich ausgestellt wurde, bei welchen Kunstschauen (Name und Datum) es ausgestellt wurden. Wenn ein Kunstwerk nicht oder nicht in Österreich ausgestellt wurde geben Sie anstelle des Datums das aktuelle Datum und anstelle des Namens 'keine Ausstellung in AUT'.

		
			select bezeichnung, COALESCE(datum,current_date),
			COALESCE(name,'keine Ausstellung in AUT')
			from kunstwerk natural join produkt left outer join (zeigt natural
			join kunstschau) on (nummer=kunstwerknummer and land='Oesterreich')	
	
		

9.1)

Geben Sie das erste Kündigungsdatum aus.

		
			select min(kuendigungsdatum) from kuendigung;	
	
		

9.2)

Geben Sie die Namen und Vornamen jener Mitarbeiter aus, die als erstes gekündigt wurden.

		
			select vorname, nachname
			from person natural join mitarbeiter natural join kuendigung
			where kuendigungsdatum=(select min(kuendigungsdatum)
			                from kuendigung);	
	
		

		
			select nachname, vorname from person natural join kuendigung where
			kuendigungsdatum = (select min(kuendigungsdatum) from kuendigung)	
	
		

10)

Geben Sie jene Künstler aus (Name und Vorname), die an allen Kunstschauen in Graz teilgenommen haben.

		
			select vorname, nachname
			from kuenstler natural join person join zeigt on knummer=nummer join kunstschau using (name,datum)
			where ort='Graz'
			group by nummer, vorname, nachname
			having count(DISTINCT datum)=(select count(*)
			                from kunstschau
			                where ort='Graz');	
	
		

		
			select vorname, nachname
			from kuenstler natural join person
			where not exists (select * from kunstschau
			                  where ort='Graz' and
			                        (datum, name) not in (select datum, name
			                                              from zeigt
			                                              where knummer=nummer))	
	
		

11)

Geben Sie Produktnummer und Bezeichnung aller Produkte aus, die in der Produktion nicht durch die Füllanlage 1 gehen.

		
			select nummer, bezeichnung
			from produkt
			where  nummer not in (select pnummer
			                      from erzeugt join maschine on maschnummer=nummer
			                      where Beschreibung='Fuellanlage 1')	
	
		

		
			(select nummer, bezeichnung
			from produkt)
			minus
			(select produkt.nummer, bezeichnung
			from produkt join erzeugt on produkt.nummer=pnummer join maschine on maschnummer=maschine.nummer
			where Beschreibung='Fuellanlage 1')	
	
		

12.1)

Geben Sie für jeden Kunden (Name und Adresse) die Anzahl der jeweiligen Aufträge aus, die noch nicht abgeschlossen wurden.

		
			select firmenname, adresse, count(*)
			from kunde natural join (select * from auftrag where status!='abgeschlossen') a
			group by firmenname, adresse;	
	
		

		
			select firmenname, adresse, count(*)
			from kunde natural join auftrag
			where status!='abgeschlossen'
			group by firmenname, adresse;	
	
		

12.2)

Geben Sie aus, wieviele Kunden mit nicht abgeschlossen Aufträgen es gibt.

		
			select count(*)
			from kunde
			where firmenname in (select firmenname from auftrag where status!='abgeschlossen');	
	
		

		
			select count(distinct firmenname)
			from kunde natural join auftrag
			where status!='abgeschlossen'	
	
		

		
			select count(distinct firmenname) from auftrag where status !=
			'abgeschlossen'	
	
		

13)

Geben Sie Namen und Vornamen des bekanntesten Künstlers aus, der nicht auch Mitarbeiter im Unternehmen ist.

		
			select vorname, nachname
			from kuenstler natural join person
			where nummer not in (select nummer from mitarbeiter)
			and bekanntheit=(select max(bekanntheit)
			                               from kuenstler
			                               where nummer not in (select nummer from mitarbeiter));	
	
		

14)

Geben Sie die Namen und Vornamen jener Künstler aus, die auch Mitarbeiter des Unternehmens sind und einen Bekanntheitsgrad >6 haben.

		
			select vorname, nachname
			from person natural join kuenstler
			where nummer in (select nummer from mitarbeiter)
			and bekanntheit>6;	
	
		

		
			select vorname, nachname
			from person natural join kuenstler natural join mitarbeiter
			where bekanntheit>6;	
	
		

		
			select vorname, nachname
			from person natural join kuenstler
			where bekanntheit>6
			intersect
			select vorname, nachname
			from person natural join mitarbeiter;	
	
		

15)

Geben Sie Name, Vorname und Nummer jener Künstler aus, die bei Kunstschauen die meisten Werke ausgestellt haben.

		
			select nummer, vorname, nachname
			from (kuenstler natural join person) join zeigt on (nummer=knummer)
			group by nummer, vorname, nachname
			having count(distinct kunstwerknummer)>= ALL (select count(distinct kunstwerknummer)
			                from (kuenstler natural join person) join zeigt on (nummer=knummer)
			                group by nummer);	
	
		

16)

Gesucht sind Nummer, Vorname und Nachname jener Mitarbeiter, die alle Maschinen bedienen können.

		
			select nummer, vorname, nachname
			from (person natural join mitarbeiter), bedient
			where nummer=mitnummer
			group by nummer, vorname, nachname
			having count(*)=(select count(*)
			                from maschine);	
	
		

17)

Geben Sie eine Liste ALLER Lager (Bezeichnung und Fläche) aus mit folgenden Infos: die Fläche des Lagers, die Anzahl der verschiedenen Produkte die darin gelagert werden, sowie die Gesamtmenge aller Produkte.

		
			select bezeichnung, flaeche, count(pnummer), sum(menge)
			from lager left outer join lagert using (bezeichnung)
			group by bezeichnung, flaeche;	
	
		

18)

Bei wievielen Kunstschauen in Österreich konnten Künstler einen Platz unter den ersten drei machen?

		
			select count(distinct(concat(name,datum)))
			from zeigt join kunstschau using (name, datum)
			where land='Oesterreich'
			and platz<4;	
	
select count(name) from (select distinct k.datum, k.name 
from zeigt z join kunstschau k on k.name=z.name and k.datum=z.datum and
k.land ='Oesterreich' and z.platz<4)


		

		
			select count(distinct(concat(name,datum)))
			from zeigt natural join kunstschau
			where land='Oesterreich'
			and platz<=3;	
	
		

19)

Geben Sie eine nach Plätzen sortierte Liste der Plätze aus, die je mit Kunstwerken in Österreich gemacht wurden, und dazu die Anzahl der Plätze.

		
			SELECT platz, count(*)
			FROM zeigt natural join kunstschau
			where land='Oesterreich'
			GROUP BY platz
			ORDER BY platz;	
	
		

20)

Gesucht sind Vorname und Nachname jener Personen, die keine Künstler sind, nicht gekündigt wurden und mindestens einen Mischer bedienen können.

		
			select distinct vorname, nachname
			from person natural join mitarbeiter join bedient on (nummer=mitnummer)
			where nummer not in (select nummer from kuenstler)
			and nummer not in (select nummer from kuendigung)
			and maschnummer in (select nummer from maschine where beschreibung='Mischer')	
	
		

		
			select vorname, nachname from person where nummer not in (select nummer from
			kuenstler) and nummer not in (select nummer from kuendigung) and nummer in
			(select mitnummer from maschine join bedient on nummer = maschnummer and
			beschreibung = 'Mischer')	
	
		

21)

Schnäppchen sind entweder Kunstwerke mit einem Schätzwert unter 2000 Euro oder Süßigkeiten unter 3 Euro. Geben Sie eine Liste von Schnäppchen (bezeichnung und gewicht) aus. Vermerken Sie dabei auch in einer eigenen Spalte, die Sie 'ist' nennen, ob es sich um ein Kunstwerk oder ein Produkt aus dem Standardsortiment handelt. (Hinweis: verwenden Sie das keyword union).

		
			select bezeichnung, gewicht, 'Kunstwerk' as ist
			from produkt natural join kunstwerk
			where schaetzwert<2000
			union
			select bezeichnung, gewicht, 'Standard' as ist
			from produkt natural join standardsortiment
			where preis<3;	
	
		

22)

Erstellen Sie eine Liste ALLER Kunden (Firmennamen) und geben Sie zu jedem Kunden den Gesamtpreis der in sämtlichen Aufträgen bestellten Produkten aus dem Standardsortiment. Nennen Sie diese Spalte Gesamtpreis und sortieren Sie die Liste danach.

		
			select k.firmenname, (select sum(s.preis*e.menge)) 
			                      from enthaelt e join produkt p on (e.pnummer=p.nummer) join standardsortiment s on (p.nummer=s.nummer)
			                                           where e.firmenname=k.firmenname) as Gesamtpreis
			from kunde k order by Gesamtpreis;	
	
		

		
			select k.firmenname, sum(s.preis*e.menge)  as Gesamtpreis
			from kunde k left outer join (enthaelt e join produkt p on
			(e.pnummer=p.nummer) join standardsortiment s on (p.nummer=s.nummer))
			on (k.firmenname=e.firmenname)
			group by k.firmenname
			order by Gesamtpreis;	
	
		

23)

Geben Sie eine Liste ALLER Künstler (Vor- und Nachname) aus und das Datum des letzten Kunstschau, an der dieser Künstler teilgenommen hat.

		
			SELECT p.vorname, p.nachname, max(z.datum)
			FROM (kuenstler k natural join person p) LEFT OUTER JOIN zeigt z on (nummer = z.knummer)
			GROUP BY nummer, p.vorname, p.nachname;	
	
		

24)

Geben Sie die Bezeichung und die Fläche jener Lager aus in denen mehr als 20% der Produkte lagern.

		
			select bezeichnung, flaeche
			from lager natural join lagert
			group by bezeichnung, flaeche
			having sum(menge)>=(select sum(menge) from lagert)/5;	
	
		

		
			select bezeichnung, flaeche
			from lager natural join lagert
			group by bezeichnung, flaeche
			having sum(menge)>=(select sum(menge) from lagert)*0.2;