Zeitschriftenverlag - Die Datenbank

Beschreibung der Datenbank

Der Zeitschriftenverlag bietet verschiedene Zeitschriften an. Diese werden identifiziert durch ihr Genre und ihren Namen und haben weiters einen Bekanntheitsgrad von 1 unbekannt bis 10 sehr bekannt, und ein Einführungsdatum. Weiters wird optional ein Enddatum gespeichert, an dem die Zeitschrift eingestellt wurde. Zeitschriften unterteilen sich in Zeitungen oder Magazine. Für Zeitungen wird die Anzahl der Leser und das jährliche Budget in Euro gespeichert. Magazine haben eine Erscheinungsperiode (entweder "täglich", "wöchentlich", "monatlich" oder "vierteljährlich") und einen Untertitel. Von den Mitarbeitern des Verlags ist die eindeutige Sozialversicherungsnummer, Vorname, Nachname, Geburtsdatum und Gehalt bekannt. Mitarbeiter können Redakteure sein, für die zusätzlich das Einstelldatum gespeichert wird und wieviele Dienstjahre sie insgesamt bereits als Redakteur gearbeitet haben (auch bei anderen Verlagen). Bei Zeitungen gibt es immer genau einen Redakteur, der als Chefredakteurs die Zeitung leitet. Weiters wird gespeichert, welche Redakteure für welche Zeitschriften arbeiten. Mitarbeiter können auch Austräger sein, die Zeitschriften an Kunden liefern. Für diese wird vermerkt, ob sie ein eigenes Fahrzeug haben oder nicht und für welche Zeitschriften sie tätig sind. Der Verlag deckt mit seinen Magazinen eine Reihe von Themengebieten ab. Diese sind eindeutig durch einen Namen identifiziert. Außerdem wird eine Beschreibung gespeichert. Für Redakteure wird nun - falls vorhanden - vermerkt, welches Themengebiet ihr Spezialgebiet ist und welche Prämie es dabei gibt. Außerdem wird vermerkt, welcher Redakteur bei welchem Magazin für welches Themengebiet zuständig ist. Kunden haben eine eindeutige ID, einen Namen, eine Adresse, eine Telefonnummer und einen Typ (entweder "Firma" oder "Privatperson"). Kunden können für Zeitschriften Abonnements abschließen. Diese werden durch eine eindeutige ID identifiziert und es ist die Zeitschrift, der Kunden, die anfallenden Gebühren, sowie der Anfangs- und Endzeitpunkt des Abonnements bekannt. Für jedes Abonnement gibt es entsprechende Lieferungen. Diese werden eindeutig identifiziert durch die ID des Abonnements, zu dem sie gehören und das Datum der Lieferung. Weiters wird gespeichert, wer der Austräger war und ob der Kunde persönlich angetroffen wurde oder nicht.

1)

Geben Sie Name, Genre und Bekanntheit aller Zeitschriften aus. Sortieren Sie die Liste absteigend nach der Bekanntheit, Name und Genre.

		
			select name, genre, bekanntheit
			from zeitschrift
			order by bekanntheit desc, name desc, genre desc	
	
		

2)

Geben Sie Name, Genre, Budget und Einführungsdatum aller Zeitungen aus.

		
			select name, genre, budget, einfuehrungsdatum
			 from zeitschrift natural join zeitung	
	
		

3)

Geben Sie Name, Genre, Budget und Einführungsdatum jener Zeitungen aus, die mehr als 250.000 Leser haben.

		
			select name, genre, budget, einfuehrungsdatum
			from zeitschrift natural join zeitung
			where leser>250000	
	
		

4)

Geben Sie Name und Genre jener monatlichen Magazine aus, die vor dem 30-06-2005 eingeführt wurden. Verwenden Sie dazu folgendes Datumsformat: DD-MM-YYYY

		
			select name, genre
			from zeitschrift natural join magazin
			where einfuehrungsdatum<to_date('30-06-2005', 'DD-MM-YYYY')
			and periode='monatlich'	
	
		

5)

Geben Sie das letzte Datum aus, an dem für das Abo mit der ID 2000004 die Lieferung mit einem Fahrzeug aber nicht persönlich erfolgte.

		
			select max(datum) as datum
			from lieferung join austraeger on(austraegersvnr=svnr)
			where id=2000004
			and fahrzeug=1
			and persoenlich=0	
	
		

6)

Geben Sie eine Liste ALLER Zeitschriften aus (Name und Genre) und falls sie bereits eingestellt wurden, das Datum ihrer Einstellung.

		
			select name, genre, enddatum
			from zeitschrift left outer join ende using (name, genre)	
	
		

		
			select z.name, z.genre, (select enddatum
			                        from ende
			                        where name=z.name
			                        and genre=z.genre) as enddatum
			from zeitschrift z	
	
		

		
			select z.name, z.genre, e.enddatum
			from zeitschrift z left outer join (select * from ende) e on (z.name=e.name and z.genre=e.genre)	
	
		

7.1)

Geben Sie eine Liste ALLER Redakteure (Sozialversicherungsnummer und deren Dienstjahre) aus, zusammen mit deren Spezialgebieten. Hat ein Redakteur kein Spezialgebiet soll stattdessen 'kein Spezialgebiet' ausgegeben werden. (Stichwort: coalesce)

		
			select svnr, dienstjahre, coalesce(name, 'kein Spezialgebiet') as spezialgebiet
			from redakteur left outer join spezialgebiet using (svnr)	
	
		

		
			select r.svnr, r.dienstjahre, coalesce((select name
			                                        from spezialgebiet
			                                        where svnr=r.svnr), 'kein Spezialgebiet') as spezialgebiet
			from redakteur r	
	
		

7.2)

Geben Sie eine Liste ALLER Redakteure (Sozialversicherungsnummer, Name und Dienstjahre) aus, zusammen mit deren Spezialgebieten. Hat ein Redakteur kein Spezialgebiet soll stattdessen 'kein Spezialgebiet' ausgegeben werden.

		
			select r.svnr, m.name, r.dienstjahre, coalesce(s.name, 'kein Spezialgebiet') as spezialgebiet
			from (mitarbeiter m join redakteur r on (m.svnr=r.svnr)) left outer join spezialgebiet s on (r.svnr=s.svnr)	
	
		

		
			select r.svnr, m.name, r.dienstjahre, coalesce((select name 
			                                         from spezialgebiet
			                                         where svnr=r.svnr), 'kein Spezialgebiet') as spezialgebiet
			 from mitarbeiter m join redakteur r on (m.svnr=r.svnr)	
	
		

7.3)

Geben Sie eine Liste ALLER Redakteure (Sozialversicherungsnummer und Dienstjahre) aus, zusammen mit deren teuren Spezialgebieten (praemie>250). Hat ein Redakteur kein teures Spezialgebiet, soll stattdessen 'kein teures Spezialgebiet' ausgegeben werden.

		
			select r.svnr, r.dienstjahre, coalesce(s.name, 'kein teures Spezialgebiet') as spezialgebiet
			from redakteur r left outer join spezialgebiet s  on (r.svnr=s.svnr and s.praemie>250)	
	
		

		
			select r.svnr, r.dienstjahre, coalesce((select name  
			                                          from spezialgebiet
			                                          where svnr=r.svnr
			                                          and praemie>250), 'kein teures Spezialgebiet') as spezialgebiet
			from redakteur r	
	
		

		
			select r.svnr, r.dienstjahre, coalesce(s.name, 'kein teures Spezialgebiet') as spezialgebiet
			 from redakteur r left outer join (select * from spezialgebiet where praemie>250) s on (r.svnr=s.svnr)	
	
		

8)

Geben Sie das durchschnittliche, das geringste und das höchste Gehalt aller Austräger ohne Fahrzeug aus.

		
			select avg(gehalt) as durchschnitt, min(gehalt) as geringste, max(gehalt) as hoechste
			from mitarbeiter natural join austraeger
			where fahrzeug=0	
	
		

9)

Geben Sie unter den Austrägern mit einem Fahrzeug jene aus (Namen und Sozialversicherungsnummer), die das geringste Gehalt beziehen.

		
			select name, svnr
			from mitarbeiter natural join austraeger
			where fahrzeug=1
			and gehalt= (select min(gehalt)
			                from mitarbeiter natural join austraeger
			                where fahrzeug=1)	
	
		

		
			select name, svnr
			 from mitarbeiter natural join austraeger
			 where fahrzeug=1
			 and gehalt <= ALL (select gehalt
			                         from mitarbeiter natural join austraeger
			                         where fahrzeug=1)	
	
		

		
			select m.name, m.svnr 
			  from mitarbeiter m join austraeger a on (m.svnr=a.svnr)
			  where a.fahrzeug=1
			 and not exists (select *
			                 from mitarbeiter natural join austraeger
			                 where fahrzeug=1
			                 and gehalt<m.gehalt)	
	
		

10.1)

Wieviele Abos hat der Kunde mit dem Namen 'Thomas Aigner OHG'?

		
			select count(kundenid) as anzahl
			from abonnement
			where kundenid=(select id
			                from kunde
			                where name='Thomas Aigner OHG')	
	
		

		
			select count(*)
			from kunde join abonnement on (kunde.id=abonnement.kundenid)
			where name='Thomas Aigner OHG'	
	
		

10.2)

Geben Sie die Namen der Kunden aus zusammen mit der Anzahl an Abos, die sie gebucht haben.

		
			select k.name, count(*) as anzahl
			from kunde k join abonnement a on (k.id=a.kundenid)
			group by k.id, k.name	
	
		

10.3)

Geben Sie die Kunden mit den meisten Abos aus, zusammen mit der Anzahl an Abos

		
			select k.name, count(*) as anzahl
			from kunde k join abonnement a on (k.id=a.kundenid)
			group by k.id, k.name
			having count(*)>= ALL(select count(*)
			                        from kunde k join abonnement a on (k.id=a.kundenid)
			                        group by k.id, k.name)	
	
		

		
			select k.name, count(*) as anzahl
			 from kunde k join abonnement a on (k.id=a.kundenid)
			 group by k.id, k.name
			 having count(*) = (select max(anzahl)
			                   from (select count(*) as anzahl
			                         from kunde k join abonnement a on (k.id=a.kundenid)
			                         group by k.id, k.name))	
	
		

11.1)

Geben Sie für alle Redakteure bis auf jenen mit der Sozialversicherungsnummer 1235220847 die Sozialversicherungsnummer und die Dienstjahre aus.

		
			select svnr, dienstjahre
			from redakteur
			where svnr!=1235220847	
	
		

11.2)

Geben Sie jene Redakteure (Sozialversicherungsnummer und Dienstjahre) aus, die bei einer Zeitung arbeiten, allerdings keine Zeitung leiten.

		
			select distinct svnr, dienstjahre
			from redakteur natural join arbeitet natural join zeitung
			where svnr not in (select chefredakteursvnr
			                        from zeitung)	
	
		

		
			(select distinct svnr, dienstjahre
			 from redakteur natural join arbeitet natural join zeitung)
			minus
			(select svnr, dienstjahre
			 from zeitung join redakteur on (chefredakteursvnr=svnr))	
	
		

12)

Geben Sie jene Redakteure (Name und Einstelldatum) aus, die für alle Zeitschriften des Genres Informatik arbeiten.

		
			select name, einstelldatum
			from mitarbeiter m join redakteur r on (m.svnr=r.svnr)
			where not exists (select *
			                        from zeitschrift
			                        where genre='Informatik'
			                        and (name,genre) not in (select name, genre
			                                                        from arbeitet
			                                                        where svnr=m.svnr))	
	
		

		
			select m.name, r.einstelldatum
			from mitarbeiter m join redakteur r on (m.svnr=r.svnr) join arbeitet a on (a.svnr=r.svnr)
			where a.genre='Informatik'
			group by m.svnr, m.name, r.einstelldatum
			having count(*)>= (select count(*)
			                        from zeitschrift
			                        where genre='Informatik')	
	
		

13)

Geben Sie eine Liste ALLER Austräger (Sozialversicherungsnummer und Fahrzeug) aus zusammen mit der Anzahl an eher unbekannten (bekanntheit<5) Magazinen, die sie austragen.

		
			select svnr, fahrzeug, count(name) as anzahl
			from austraeger left outer join (select svnr, name, genre from traegt_aus natural join zeitschrift natural join magazin where bekanntheit<5) using(svnr)
			group by svnr, fahrzeug	
	
		

		
			select a.svnr, a.fahrzeug, (select count(*)
			                        from magazin natural join zeitschrift natural join traegt_aus
			                        where bekanntheit<5
			                        and svnr=a.svnr) as anzahl
			from austraeger a;	
	
		

		
			select a.svnr, a.fahrzeug, count(z.name) as anzahl
			from austraeger a left outer join ((traegt_aus t join zeitschrift z on (t.name=z.name and t.genre=z.genre)) join magazin m on (z.name=m.name and z.genre=m.genre and z.bekanntheit<5)) on(t.svnr=a.svnr)
			group by a.svnr, a.fahrzeug	
	
		

14)

Geben Sie alle Zeitungen (Name, Genre und Bekanntheit) mit weniger als 250.000 Leser aus, zusammen mit allen täglich erscheinenden Magazinen.

		
			(select name, genre, bekanntheit
			from zeitschrift natural join zeitung
			where leser<250000)
			union
			(select name, genre, bekanntheit
			from zeitschrift natural join magazin
			where periode='taeglich')	
	
		

15)

Gesucht sind jene Abos (ID und Gebuehr) mit den meisten persoenlichen Lieferungen.

		
			select id, gebuehr
			from abonnement join lieferung using(id)
			where persoenlich=1
			group by id, gebuehr
			having count(*)>=ALL (select count(*)
			                        from abonnement join lieferung using(id)
			                        where persoenlich=1
			                        group by id, gebuehr)	
	
		

		
			select id, gebuehr
			 from abonnement join lieferung using(id)
			 where persoenlich=1
			 group by id, gebuehr
			 having count(*) = (select max(anzahl)
			                   from (select count(*) as anzahl
			                         from abonnement join lieferung using(id)
			                         where persoenlich=1
			                         group by id, gebuehr))	
	
		

16)

Gesucht ist Name, Genre und Bekanntheit jener Zeitungen mit den meisten Lesern, die aber noch nicht eingestellt wurde.

		
			select name, genre, bekanntheit
			from zeitschrift natural join zeitung
			where (name, genre) not in (select name, genre from ende)
			and leser=(select max(leser)
			                from zeitschrift natural join zeitung
			                where (name, genre) not in (select name, genre from ende))	
	
		

		
			select name, genre, bekanntheit
			 from zeitschrift natural join zeitung
			 where (name, genre) not in (select name, genre from ende)
			 and leser >= ALL(select leser
			                 from zeitschrift natural join zeitung
			                 where (name, genre) not in (select name, genre from ende))	
	
		

		
			select zs.name, zs.genre, zs.bekanntheit
			 from zeitschrift  zs join zeitung z on (zs.name=z.name and zs.genre=z.genre)
			 where (zs.name, zs.genre) not in (select name, genre from ende)
			 and not exists (select name, genre, bekanntheit
			                 from zeitschrift natural join zeitung
			                 where (name, genre) not in (select name, genre from ende)
			                 and leser>z.leser)	
	
		

17)

Gesucht sind jene Zeitschriften (Name und Genre), die weder Zeitungen noch Magazine sind und einen hohen Bekanntheitsgrad (>5) haben.

		
			select name, genre
			from zeitschrift
			where bekanntheit>5
			and (name,genre) not in (select name, genre from zeitung)
			and (name,genre) not in (select name, genre from magazin)	
	
		

		
			(select name, genre
			 from zeitschrift
			 where bekanntheit>5)
			minus
			(select name, genre from zeitung)
			minus
			(select name, genre from magazin)	
	
		

18)

Welche Zeitungen (Name, Genre und Bekanntheit) verfügen über mehr als 20% des gesamten gespeicherten Budgets?

		
			select name, genre, bekanntheit
			from zeitschrift natural join zeitung
			where budget>(select sum(budget)/5
			                from zeitung)	
	
		

		
			select name, genre, bekanntheit
			 from zeitschrift natural join zeitung
			 where budget>(select sum(budget)*.2
			                 from zeitung)	
	
		

		
			select name, genre, bekanntheit 
			  from zeitschrift natural join zeitung
			  where budget>(select sum(budget)*0.2
			                  from zeitung)	
	
		

19)

Geben Sie eine Liste ALLER Redakeure (Sozialversicherungsnummer und Name) aus zusammen mit den Namen der Zeitschrift, bei denen sie für Populistisches zuständig sind. Ersetzen Sie Nullwerte durch 'keine Zeitschrift'.

		
			select m.svnr, m.name, coalesce(z.name, 'keine Zeitschrift') as zeitschrift
			from (mitarbeiter m join redakteur r on(r.svnr=m.svnr)) left outer join zustaendig z on (m.svnr=z.svnr and z.thema='Populistisches')	
	
		

		
			select m.svnr, m.name, coalesce(z.name, 'keine Zeitschrift') as zeitschrift
			from (mitarbeiter m join redakteur r on(r.svnr=m.svnr)) left outer join (select * from zustaendig where thema='Populistisches') z on(m.svnr=z.svnr)	
	
		

20)

Geben Sie den Namen jener Redakeure aus, die für alle gespeicherten Themengebiete zuständig sind, ganz egal bei welcher Zeitschrift.

		
			select m.name
			from mitarbeiter m
			where not exists (select *
			                        from themengebiet
			                        where name not in (select thema
			                                                from zustaendig where
			                                                svnr=m.svnr))	
	
		

		
			select m.name
			from mitarbeiter m join zustaendig z on (m.svnr=z.svnr)
			group by m.svnr, m.name
			having count(distinct z.thema)=(select count(*)
			                                from themengebiet)	
	
		

21)

Wieviel Gebühren haben Firmenkunden insgesamt für Abonnements bezahlt?

		
			select sum(a.gebuehr) as summe
			from abonnement a join kunde k on (a.kundenid=k.id)
			where k.typ='Firma'	
	
		

22)

Geben Sie den Namen und das Fahrzeug jener Austräger aus, die mindestens ein Drittel aller Zeitschriften austragen und wieviele das sind.

		
			select m.name, a.fahrzeug, count(*) as anzahl
			from mitarbeiter m join austraeger a on (m.svnr=a.svnr) join traegt_aus t on(a.svnr=t.svnr)
			group by m.svnr, m.name, a.fahrzeug
			having count(*)>=(select count(*)/3
			                        from zeitschrift)	
	
		

23)

Geben Sie eine Liste ALLER Kunden (Name und Typ) aus und die Anzahl der Abonnements, die vor dem Jahr 2005 endeten und die Summe der angefallenen Gebühren. Berücksichtigen Sie dabei, dass die Gebühren auf jeden Fall einen Wert beinhalten müssen. Verwenden Sie dazu folgendes Datumsformat: YYYY

		
			select k.name, k.typ, count(a.id) as abos, coalesce(sum(a.gebuehr),0) as gebuehren
			from kunde k left outer join abonnement a on(k.id=a.kundenid and a.ende<to_date(2005, 'YYYY'))
			group by k.id, k.name, k.typ	
	
		

		
			select k.name, k.typ, (select count(*)
			                        from abonnement
			                        where kundenid=k.id
			                        and ende<to_date(2005, 'YYYY'))  as abos,
			                      coalesce ((select sum(gebuehr)
			                        from abonnement
			                        where kundenid=k.id
			                        and ende<to_date(2005, 'YYYY')), 0) as gebuehren
			from kunde k	
	
		

		
			select k.name, k.typ, count(a.id) as abos, coalesce(sum(a.gebuehr),0) as gebuehren
			 from kunde k left outer join (select * from abonnement where ende<to_date(2005, 'YYYY')) a on (k.id=a.kundenid)
			 group by k.id, k.name, k.typ	
	
		

24)

Geben Sie alle Zeitungen (Name, Genre und Bekanntheit) aus, die mehr als 250.000 Euro als Budget verfügen zusammen mit den monatlichen Magazinen (Name, Genre und Bekanntheit).

		
			(select name, genre, bekanntheit
			from zeitschrift natural join zeitung
			where budget>250000)
			union
			(select name, genre, bekanntheit
			from zeitschrift natural join magazin
			where periode='monatlich')