Raumfahrtgesellschaft - Die Datenbank

Beschreibung der Datenbank

Mitarbeiter haben eine eindeutige Nummer, einen Vornamen, Nachnamen, ein Geburtsdatum und ein Einstelldatum. Einige Mitarbeiter sind Astronauten, wobei bei diesen die Anzahl der virtuellen und realen Flugstunden vermerkt wird und, falls sie verschollen sind, das Datum, an dem sie verloren gingen. Mitarbeiter werden eingeteilt in Teams, die jeweils durch einen Nummer eindeutig identifiziert werden und zusätzlich einen Namen gespeichert haben. Mitarbeiter können gleichzeitig Mitglied in mehreren Teams sein und es wird vermerkt, seit wann sie im Team sind. Außerdem werden die Raumfahrzeuge der Raumfahrtgesellschaft in der Datenbank abgelegt. Diese werden eindeutig identifiziert durch ihren Typ und Namen und es wird die Anzahl der Stunden an Flugzeit gespeichert. Raumfahrzeuge werden unterteilt in Raumfähren, Raumstationen und sonstige Raumfahrzeuge. Bei Raumfähren werden zusätzlich die Anzahl der absolvierten Flüge und die geflogenen Kilometer vermerkt, bei Raumstationen, falls gegeben, um welchen Himmelskörper sie kreisen und seit wann. In der Datenbank wird auch eine Liste der Himmelskörper vermerkt, die als mögliche Destinationen für Flugmissionen in Frage kommen. Diese haben einen eindeutigen Namen, einen Typ und es werden der Durchmesser und die mittlere Entfernung zur Erde in Kilometern gespeichert. Im Rahmen einer Mission können nun Raumfähren zu einem dieser Himmelskörper fliegen. Optional kann die Raumfähre auch ein Team an Bord haben. Himmelskörper, Raumfähre und der Startzeitpunkt identifizieren eine Mission eindeutig. Weiters hat eine Mission noch eine Dauer in Tagen. Astronauten haben gewisse Qualifikationen. Zu diesen werden eine eindeutige Bezeichnung und eine Beschreibung vermerkt. Für jede Qualifikation eines Astronauten wird auf einer Skala von 1 bis 10 (1-ausbaufähig, 10-sehr gut) bewertet, wie gut er diese beherrscht. Um auf Raumfahrzeugen eingesetzt zu werden, sind ebenfalls gewisse Qualifikationen notwendig. Weiters wird bei Astronauten, falls sie verschollen sind, das Datum gespeichert, an dem sie verloren gingen. In den Raumfahrzeugen werden gewisse selbst entwickelte Technologien verwendet. Diese Technologien werden mit einer eindeutigen Nummer und einer genauen Beschreibung in der Datenbank gespeichert.

1)

Geben Sie die Nummer und den Nachnamen aller Mitarbeiter aus und sortieren Sie die Liste alphabethisch.

		
			select nummer, nachname
			from mitarbeiter
			order by nachname	
	
		

2)

Geben Sie Nummer, Vorname und Nachname aller Astronauten aus.

		
			select nummer, vorname, nachname
			from mitarbeiter natural join astronaut	
	
		

3)

Geben Sie Nummer, Vorname und Nachname jener Astronauten aus, die nach dem 15-05-2006 verschollen sind. (Tipp: Verwenden Sie dazu TO_DATE).

		
			select nummer, vorname, nachname
			from mitarbeiter natural join verschollen
			where datum>TO_DATE('15-05-2006', 'DD-MM-YYYY');	
	
		

		
			select nummer, vorname, nachname
			from mitarbeiter natural join verschollen
			where datum>TO_DATE('15-05-2006', 'dd-mm-yyyy');	
	
		

		
			select nummer, vorname, nachname
			from mitarbeiter natural join verschollen
			where datum>TO_DATE('15-MAY-2006');	
	
		

4)

Gesucht wird der Vorname und Nachname jener Astronauten, die die Qualifikation Landung mit einer guten Bewertung (kleiner als 5) beherrschen.

		
			select vorname, nachname
			from mitarbeiter natural join beherrscht
			where bezeichnung='Landung'
			and bewertung <5;	
	
		

5.1)

Geben Sie für die Raumstationen (Name, Typ) aus, welche Technologien (Beschreibung) genutzt werden.

		
			select name, typ, beschreibung
			from raumstation join nutzt using(name,typ) natural join technologie	
	
		

5.2)

Geben Sie jene Raumstationen (Name und Typ) aus, die die Technologie WARP-Antrieb nicht nutzen.

		
			select name, typ
			from raumstation
			where (name, typ) not in
			                (select name, typ
			                 from nutzt natural join technologie
			                 where beschreibung='WARP-Antrieb');	
	
		

		
			(select name, typ
			from raumstation)
			minus
			(select name, typ
			from nutzt natural join technologie
			where beschreibung='WARP-Antrieb');	
	
		

6.1)

Geben Sie eine Liste ALLER Astronauten (Nummer) aus und die Bezeichnung der Qualifikationen, die sie beherrschen. Falls ein Astronaut keine Qualifiktion in der Datenbank verzeichnet hat, soll stattdessen 'keine besondere QL' ausgegeben werden. (Stichwort: COALESCE)

		
			select nummer, coalesce(bezeichnung, 'keine besondere QL') as bezeichnung
			from astronaut left outer join beherrscht using(nummer);	
	
		

6.2)

Geben Sie eine Liste ALLER Astronauten (Nummer, Vorname, Nachame) aus und die Bezeichung der Qualifikationen, die sie beherrschen. Falls ein Astronaut keine Qualifiktion in der Datenbank verzeichnet hat, soll stattdessen 'keine besondere QL' ausgegeben werden.

		
			select nummer, vorname, nachname, coalesce(bezeichnung, 'keine besondere QL') as bezeichnung
			from (astronaut natural join mitarbeiter) left outer join beherrscht using(nummer);	
	
		

6.3)

Geben Sie eine Liste ALLER Astronauten (Nummer, Vorname, Nachame) aus und die Bezeichung der beherrschten Qualifikationen, die für die Raumsonde Cassini benötigt wird. Falls ein Astronaut keine derartigen Qualifiktionen in der Datenbank verzeichnet hat, soll stattdessen 'keine Cassini-QL' ausgegeben werden.

		
			select nummer, vorname, nachname, coalesce(bezeichnung, 'keine Cassini-QL') as bezeichnung
			from astronaut natural join mitarbeiter left outer join (select * from beherrscht natural join benoetigt where name='Cassini' and typ='Raumsonde') using(nummer);	
	
		

		
			select astronaut.nummer, nachname, vorname, coalesce(bezeichnung, 'keine Cassini-QL') as bezeichnung
			from astronaut join mitarbeiter on(astronaut.nummer=mitarbeiter.nummer) left outer join (beherrscht join benoetigt using (bezeichnung)) on (astronaut.nummer=beherrscht.nummer and name='Cassini' and typ='Raumsonde')	
	
		

7)

Geben Sie jenes Datum aus, an dem die erste Mission des Teams mit dem Namen Flying Angels begann.

		
			select min(beginn) as beginn
			from mission
			where tnummer=(select tnummer
			from team
			where name='Flying Angels');	
	
		

		
			select min(beginn) as beginn
			from mission  natural join team
			where name='Flying Angels'	
	
		

		
			select beginn
			from team natural join mission
			where name='Flying Angels'
			and beginn<=all (select beginn
			                 from team natural join mission
			                 where name='Flying Angels')	
	
		

8)

Geben Sie Name (des Himmelskörper) sowie Beginn und Dauer der längsten Missionen aus.

		
			select hname, beginn, dauer
			from mission
			where dauer=(select max(dauer)
			                from mission);	
	
		

		
			select hname, beginn, dauer
			 from mission
			 where dauer>= all(select dauer
			                    from mission)	
	
		

9.1)

Wieviele Teammitglieder sind im Team mit dem Namen Mission 1?

		
			select count(*) as anzahl
			from team natural join mitglied
			where name='Mission 1';	
	
		

9.2)

Geben Sie den Namen der Teams aus und die Anzahl der dazugehörigen Teammitglieder.

		
			select name, count(*) as anzahl
			from team natural join mitglied
			group by tnummer, name;	
	
		

9.3)

Geben Sie die Teams (Name) mit den wenigsten Teammitgliedern aus, zusammen mit der Anzahl der Mitglieder.

		
			select name, count(*) as anzahl
			from team natural join mitglied
			group by tnummer, name
			having count(*) <= ALL (select count (*)
			                        from team natural join mitglied
			                        group by tnummer, name);	
	
		

		
			select name, count(tnummer)
			from team natural join mitglied
			group by tnummer, name
			having count(tnummer) = (select min(anzahl)
							from (select count(tnummer) as anzahl
								from team natural join mitglied
								group by tnummer))	
	
		

10)

Geben Sie jene Raumfahrzeuge (Name und Typ) aus, die alle Technologien nutzen.

		
			select name, typ
			from raumfahrzeug
			where not exists (select * from technologie
			                        where nummer not in (select nummer
			                                                from nutzt
			                                                where nutzt.name=raumfahrzeug.name
			                                                and nutzt.typ=raumfahrzeug.typ));	
	
		

		
			select name, typ
			 from nutzt
			 group by name, typ
			 having count(nummer)=(select count(*)
			                 from technologie)	
	
		

		
			select name, typ FROM (select name, typ, count(*) as count from NUTZT GROUP BY name, typ) s where s.count=(SELECT count(*) from technologie)	
	
		

		
			select r.name,r.typ
			from raumfahrzeug r
			where (select count(*)
				from nutzt n
				where n.name=r.name
				and n.typ=r.typ)= (select count (*)
							from technologie)	
	
		

11)

Geben Sie jene Raumfaehren (Name, Typ und Flugstunden) aus, die keine Technologie nutzen.

		
			select name, typ, flugstunden
			from raumfahrzeug join raumfaehre using(name,typ)
			where (name,typ) not in (select name, typ from nutzt);	
	
		

		
			select r.name, r.typ, r.flugstunden
			from raumfahrzeug r join raumfaehre rf on (r.name=rf.name and r.typ=rf.typ)
			where not exists (select * from nutzt where nutzt.name=r.name);	
	
		

		
			select r.name, r.typ, r.flugstunden
			from raumfahrzeug r join raumfaehre r2 on (r.typ=r2.typ and r.name=r2.name) left outer join nutzt n on (r.name=n.name and r.typ=n.typ)
			group by r.name, r.typ, r.flugstunden
			having count(n.typ)=0;	
	
		

		
			select r.name, r.typ, r.flugstunden
			from raumfahrzeug r join raumfaehre r2 on (r.typ=r2.typ and r.name=r2.name) left outer join nutzt n on (r.name=n.name and r.typ=n.typ)
			where n.typ is null	
	
		

12)

Geben Sie die durchschnittlichen Flugstunden von verschollenen Astronauten an. Es soll dabei keine Rolle spielen, ob es sich dabei um reale oder virtuelle Flugstunden handelt.

		
			select avg(realeFlugstunden+virtuelleFlugstunden) as durchschnitt
			from astronaut natural join verschollen;	
	
		

13)

Geben Sie Nummer und Name der Teams aus, zusammen mit Nummer, Vorname und Nachname der Teammitglieder.

		
			select tnummer, name, nummer, vorname, nachname
			from (team natural join mitglied) join mitarbeiter on (mitarbeiternummer=nummer);	
	
		

14)

Gesucht wird jenes Team das alle Erdmission durchführte, allerdings an keiner Mondmission beteiligt war.

		
			select tnummer, name
			from team natural join mission
			where hname='Erde'
			group by tnummer, name
			having count(*)>= ALL (select count(*)
			                        from team natural join mission
			                        where hname='Erde')
			and tnummer not in (select tnummer
			                        from mission
			                        where hname='Mond');	
	
		

		
			select *
			from team
			where not exists (select * from mission where hname='Erde' and tnummer<>team.tnummer)
			and not exists (select * from mission where hname='Mond' and tnummer=team.tnummer);	
	
		

15)

Geben Sie jene Astronauten (Vorname, Nachname) aus, die die meisten Qualifikationen haben.

		
			select vorname, nachname
			from mitarbeiter natural join beherrscht
			group by nummer, vorname, nachname
			having count(*) >= ALL (select count (*)
			                        from mitarbeiter natural join beherrscht
			                        group by nummer, vorname, nachname);	
	
		

16)

Geben Sie eine Liste ALLER Himmelskörper (Name und Typ) aus und den Namen der Raumstationen, die herumkreisen. Falls keine Raumstation um den Himmelskörper kreist, geben Sie 'keine Raumstation' aus.

		
			select h.name, h.typ, coalesce(r.name, 'keine Raumstation') as raumstation
			from himmelskoerper h left outer join raumstation r on(h.name=r.himmelskoerpername);	
	
		

17)

Gesucht sind Name und Typ der Raumfähren die mehr als 50 Flügen absolviert haben, sowie Name und Typ aller Raumstationen, die schon länger als 9 Jahre (vor 2000) oder gar nicht um einen Himmelskörper kreisen.

		
			(select name, typ
			from raumfaehre
			where fluege>50)
			union
			(select name, typ
			from raumstation
			where seit<TO_DATE(2000, 'YYYY') or seit is null);	
	
		

		
			(select name, typ
			from raumfaehre
			where fluege>50)
			union
			(select name, typ
			from raumstation
			where seit<TO_DATE('01-Jan-2000') or seit is null);	
	
		

		
			(select name, typ
			 from raumfaehre
			 where fluege>50)
			 union
			 (select name, typ
			 from raumstation
			 where seit<'01-JAN-2000' or seit is null);	
	
		

18)

Gesucht sind jene Raumfähren (Name und Typ), die nicht als Raumstation eingesetzt sind.

		
			select r.name, r.typ
			from raumfaehre r
			where not exists (select *
			                from raumstation
			                where name=r.name
			                and typ=r.typ);	
	
		

		
			select name, typ
			from raumfaehre
			where (name, typ) not in (select name, typ
			                        from raumstation);	
	
		

		
			select r1.name, r1.typ
			from raumfaehre r1 left outer join raumstation r2 on (r1.name=r2.name and r1.typ=r2.typ)
			where r2.name is null;	
	
		

19)

Gesucht sind jene Mitarbeiter (Vorname, Nachname), die weder Astronauten sind noch in einem Team.

		
			select vorname, nachname
			from mitarbeiter
			where nummer not in (select nummer from astronaut)
			and nummer not in  (select mitarbeiternummer from mitglied);	
	
		

		
			select vorname, nachname
			from mitarbeiter m1 left outer join mitglied m2 on (m1.nummer=m2.mitarbeiternummer) left outer join astronaut a on (m1.nummer=a.nummer)
			where m2.mitarbeiternummer is null
			and a.nummer is null;	
	
		

20)

Geben Sie für ALLE Astronauten folgende Informationen aus: Vorname und Nachname, die durchschnittliche Bewertung seiner Qualifikationen und die Anzahl an Teams in denen er Mitglied ist.

		
			select vorname, nachname, avg(bewertung), count(distinct m2.tnummer)
			 from mitarbeiter m join astronaut a on (m.nummer=a.nummer) left outer join mitglied m2 on (m.nummer=m2.mitarbeiternummer) left outer join beherrscht b on (a.nummer=b.nummer)
			group by m.nummer, vorname, nachname	
	
		

		
			SELECT vorname, nachname, count(seit), m.bewertung FROM mitarbeiter natural join astronaut LEFT JOIN mitglied ON nummer=mitarbeiternummer LEFT JOIN (SELECT avg(bewertung) as bewertung, nummer as nr FROM beherrscht GROUP BY nummer) m ON m.nr=nummer GROUP BY vorname, nachname, nummer, m.bewertung	
	
		

		
			Select m.vorname, m.nachname, (select avg(bewertung) from beherrscht where nummer=m.nummer ), (select count(*) from mitglied where mitarbeiternummer=m.nummer)
			From mitarbeiter m join astronaut a on (m.nummer=a.nummer)	
	
		

21)

Geben Sie eine Liste ALLER Astronauten aus: Neben dem Vornamen und dem Nachname auch die Namen der Teams in denen sie sich befinden und seit wann. Wenn es Astronauten gibt ohne Team soll statt dem Teamnamen 'no team' ausgegeben werden.

		
			select vorname, nachname, coalesce(name, 'no team') as team, seit
			from mitarbeiter natural join astronaut left outer join (select mitarbeiternummer, name, seit from mitglied m join team t on (m.tnummer=t.tnummer)) on (nummer=mitarbeiternummer);	
	
		

		
			select vorname, nachname, coalesce(name, 'no team') as team, seit
			 from mitarbeiter natural join astronaut left outer join mitglied s join team t on (s.tnummer=t.tnummer) on (nummer=mitarbeiternummer);	
	
		

22)

Geben Sie zu den Mitarbeitern (Vorname und Nachname), den Namen jenes Teams aus, in dem Sie am längsten Mitglied sind.

		
			select vorname, nachname, name
			from mitarbeiter join (select mitarbeiternummer, name, seit from mitglied natural join team) on (nummer=mitarbeiternummer)
			where seit <= ALL (select seit from mitglied
			                        where nummer=mitarbeiternummer);	
	
		

		
			select vorname, nachname, name
			from mitarbeiter join ((select * from mitglied
			                         where (mitarbeiternummer,seit) in (select mitarbeiternummer, min(seit)
			                                                            from mitglied
			                                                            group by mitarbeiternummer)) natural join team) on (nummer=mitarbeiternummer)	
	
		

		
			select vorname, nachname, t.name
			from mitarbeiter m join mitglied m2 on (m.nummer=m2.mitarbeiternummer)
			join team t on (m2.tnummer=t.tnummer)
			where m2.seit=(select min(seit)
			               from mitglied m3
			               where m3.mitarbeiternummer=m.nummer)	
	
		

23)

Ermitteln Sie die am laengsten erforschten Himmelskörper (Name, Typ und Entfernung). Das sind jene Himmelskörper, zu denen mehr als 20% der Zeit aller Missionen aufgewendet wurde. Nehmen Sie davon Missionen auf der Erde aus.

		
			select name, typ, entfernung
			from himmelskoerper join mission on (name=hname)
			where hname!='Erde'
			group by name, typ, entfernung
			having sum(dauer)>=(select sum(dauer)
			                        from mission
			                        where hname!='Erde')/5;	
	
		

		
			select name, typ, entfernung
			from himmelskoerper join mission on (name=hname)
			where hname!='Erde'
			group by name, typ, entfernung
			having sum(dauer)>=(select sum(dauer)
			                        from mission
			                        where hname!='Erde')*0.2;	
	
		

		
			select h.name, h.typ, h.entfernung
			from himmelskoerper h join mission m on (h.name=m.hname)
			where hname<>'Erde'
			group by h.name, h.typ, h.entfernung
			having sum(dauer)>=.2*(select sum(dauer)
			                         from mission
			                         where hname!='Erde');	
	
		

		
			SELECT hname, typ, entfernung from (SELECT hname, typ, entfernung, sum(dauer) as dau FROM mission JOIN himmelskoerper ON name=hname  GROUP BY hname, typ, entfernung) WHERE dau > (SELECT sum(dauer)*0.2 as gesamt FROM mission WHERE hname!='Erde')	
	
		

24)

Wieviele Missionen haben das Team Flying Angels und das Team Mission 1 insgesamt geflogen?

		
			select count(*) as anzahl
			from team natural join mission
			where name='Flying Angels' or name='Mission 1';	
	
		

		
			select (select count(*)
			        from team t1 join mission f1 on (t1.tnummer=f1.tnummer and t1.name='Flying Angels'))
			+      (select count(*)
			        from team t2 join mission f2 on (t2.tnummer=f2.tnummer and t2.name='Mission 1'))
			 as anzahl from dual	
	
		

		
			select count(*) from mission where tnummer in
			(select tnummer from team where name in ('Flying Angels', 'Mission 1'))