Verlag - Die Datenbank

Beschreibung der Datenbank

Die Geschäftsführung des Literaturverlags Leseratte möchte eine Datenbank anlegen, um die wichtigsten Informationen über die Veröffentlichungen des Verlags zu speichern. In der Datenbank soll gespeichert werden, welche Personen an den Veröffentlichungen mitarbeiten. Personen werden eindeutig durch ihren Namen (NAME) identifiziert. Ausserdem wird zu jeder Person das Geburtsdatum (GEBDAT) und eine Kurzbiographie für die Website des Verlags (KURZBIO) gespeichert. Bei den Personen kann es sich um Autor/innen, übersetzer/innen oder Lektor/innen handeln. Eine Person kann auch Autor/in und übersetzer/in zugleich sein. Bei Lektor/innen wird in der Datenbank erfasst, welche Sprachen sie beherrschen. Jede Sprache wird dabei durch einen eindeutigen Namen (NAME) identifiziert. Ein Lektor oder eine Lektorin kann beliebig viele Sprachen beherrschen. Bei übersetzer/innen muss gespeichert werden, in welche Sprachen sie übersetzen können und aus welchen Originalsprachen sie übersetzen können. Diese beiden Beziehungen sind voneinander unabhängig -- es kann sein, dass jemand z.B. englische Texte ins Deutsche übersetzen kann, aber keine deutschen Texte ins Englische. Für jedes Werk, das im Verlag Leseratte erscheint, wird gespeichert, welche Autor/innen es geschrieben haben. Dabei ist zu beachten, dass auch mehrere Autor/innen gemeinsam ein Werk schreiben können. Werke werden durch ihren Titel (TITEL) und das Jahr, in dem sie geschrieben wurden (JAHR) identifiziert. Zu jedem Werk soll ausserdem noch eine kurze Beschreibung für die Verlagswebsite (BESCHREIBUNG) gespeichert werden. Ein Werk kann eine übersetzung eines anderen Werks sein. In der Datenbank soll gespeichert werden, bei welchen Werken es sich um übersetzungen handelt und wer sie übersetzt hat. Der Einfachheit halber nehmen wir an, dass es nicht möglich ist, dass mehrere Personen gemeinsam eine übersetzung eines Werks erstellen. Von einem Werk kann es aber mehrere übersetzungen geben, die auch von unterschiedlichen übersetzer/innen sein können. Zu den Werken wird ausserdem noch gespeichert, zu welchen Genres (Science-Fiction-Roman, Gedichtband ...) sie gehören. Jedes Werk wird einem oder mehreren Genres zugeordnet. Jedes Genre wird durch eine ID (GID) eindeutig identifiziert und hat ausserdem noch einen Namen (NAME) und eine kurze Beschreibung (BESCHREIBUNG). Ein Genre kann (muss aber nicht) ein Subgenre von einem oder mehreren anderen Genres sein. Von jedem Werk, das im Verlag Leseratte erscheint, gibt es eine oder mehrere Auflagen. Eine Auflage wird durch das Werk und durch eine zusätzliche Auflagennummer (NUMMER) eindeutig identifiziert. Die Auflagennummer allein ist im Allgemeinen nicht eindeutig. Ausserdem soll das Jahr gespeichert werden, in dem die Auflage erscheint (JAHR), und die Lektor/innen, die an der Auflage mitgearbeitet haben. An jeder Auflage arbeitet mindestens ein Lektor/eine Lektorin mit. Zu einer Auflage eines Werks kann es mehrere Publikationen geben. Publikationen werden durch die Auflage und eine zusätzliche ID (PID) identifiziert. Ausserdem wird noch die Seitenanzahl (SEITEN) zu jeder Publikation gespeichert. Schliesslich sollen auch noch die Verträge, die der Verlag mit Personen abschliesst, in die Datenbank aufgenommen werden. Zu jedem Vertrag enthält die Datenbank eine eindeutige ID (VID), das Abschlussdatum (DATUM) sowie einen kurzen Beschreibungstext (BESCHREIBUNG). Ausserdem soll gespeichert werden, mit welcher Person der Vertrag abgeschlossen wird. Dabei kann man davon ausgehen, dass Verträge immer mit genau einer Person abgeschlossen werden.

1)

Geben Sie Titel und Beschreibung aller Werke aus, die im Jahr 2004 oder später geschrieben wurden. Die Ergebnisse sollen aufsteigend nach dem Titel sortiert sein. Tipp: Verwenden Sie die Funktion TO_DATE mit dem Datumsformat 'YYYY'.

		
			SELECT titel, beschreibung 
			FROM Werk
			WHERE jahr >= TO_DATE('2004', 'YYYY')
			ORDER BY titel ASC;	
	
		

2)

Gesucht sind Name und Geburtsdatum aller Uebersetzer/innen, die aus dem Russischen uebersetzen koennen und an mindestens zwei Uebersetzungen mitgearbeitet haben, absteigend sortiert nach dem Geburtsdatum. Verwenden Sie zur Ausgabe der Geburtsdaten die Funktion TO_CHAR und das Datumsformat 'DD-MM-YYYY'.

		
			SELECT name, TO_CHAR(gebdat, 'DD-MM-YYYY') as gebdat
			FROM Person NATURAL JOIN UebersetzerIn NATURAL JOIN uebersetzt_aus
			WHERE sprache = 'Russisch' AND
			(SELECT COUNT(*) FROM Uebersetzung WHERE uebersetzerin = name) >= 2
			ORDER BY gebdat DESC;	
	
		

		
			SELECT p.name, TO_CHAR(p.gebdat, 'DD-MM-YYYY') as gebdat
			FROM Person p JOIN UebersetzerIn u ON p.name = u.name JOIN uebersetzt_aus a ON u.name = a.name
			WHERE a.sprache = 'Russisch' AND
			(SELECT COUNT(*) FROM Uebersetzung WHERE uebersetzerin = p.name) >= 2
			ORDER BY p.gebdat DESC;	
	
		

		
			SELECT p.Name, TO_CHAR(p.Gebdat, 'DD-MM-YYYY') as gebdat
			FROM Person p, Uebersetzerin u, uebersetzt_aus a, uebersetzung w
			where p.name=u.name and p.name=a.name and a.sprache='Russisch' and
			w.uebersetzerin=u.name
			group by p.name, p.gebdat
			having count (*) >= 2
			order by p.Gebdat desc	
	
		

3.1)

Geben Sie die Titel und Beschreibungen aller Werke aus, die keine Uebersetzungen sind. Die Ergebnisse sollen aufsteigend nach dem Titel sortiert werden.

		
			SELECT titel, beschreibung 
			FROM Werk
			WHERE (titel, jahr) NOT IN
			    (SELECT uebersetzung_titel, uebersetzung_jahr FROM Uebersetzung)
			ORDER BY titel ASC;	
	
		

		
			SELECT titel, beschreibung 
			FROM Werk
			WHERE NOT EXISTS
			    (SELECT * FROM Uebersetzung WHERE uebersetzung_titel = titel AND uebersetzung_jahr = jahr)
			ORDER BY titel ASC;	
	
		

		
			select TITEL, BESCHREIBUNG from WERK
			left outer join UEBERSETZUNG on TITEL = UEBERSETZUNG_TITEL and JAHR = UEBERSETZUNG_JAHR
			where ORIGINAL_TITEL is null
			order by titel ASC	
	
		

3.2)

Geben Sie die Titel und Beschreibungen aller Werke aus, die keine Uebersetzungen sind und nicht vor 2004 geschrieben wurden. Die Ergebnisse sollen aufsteigend nach dem Titel sortiert werden.

		
			SELECT titel, beschreibung 
			FROM Werk
			WHERE (titel, jahr) NOT IN
			    (SELECT uebersetzung_titel, uebersetzung_jahr FROM Uebersetzung)
			AND jahr >= TO_DATE('2004', 'YYYY')
			ORDER BY titel ASC;	
	
		

		
			SELECT titel, beschreibung 
			FROM Werk
			WHERE NOT EXISTS
			    (SELECT * FROM Uebersetzung WHERE uebersetzung_titel = titel AND uebersetzung_jahr = jahr)
			AND jahr >= TO_DATE('2004', 'YYYY')
			ORDER BY titel ASC;	
	
		

3.3)

Geben Sie die Titel und Beschreibungen aller Werke aus, die keine Uebersetzungen sind, nicht zum Genre "Science Fiction" gehoeren und nicht vor 2004 geschrieben wurden. Die Ergebnisse sollen aufsteigend nach dem Titel sortiert werden.

		
			SELECT titel, beschreibung 
			FROM Werk
			WHERE (titel, jahr) NOT IN
			    (SELECT uebersetzung_titel, uebersetzung_jahr FROM Uebersetzung)
			AND jahr >= TO_DATE('2004', 'YYYY')
			AND (titel, jahr) NOT IN
			    (SELECT titel, jahr FROM gehoert_zu NATURAL JOIN Genre WHERE name = 'Science Fiction')
			ORDER BY titel ASC;	
	
		

		
			SELECT titel, beschreibung 
			FROM Werk
			WHERE NOT EXISTS
			    (SELECT * FROM Uebersetzung WHERE uebersetzung_titel = titel AND uebersetzung_jahr = jahr)
			AND jahr >= TO_DATE('2004', 'YYYY')
			AND NOT EXISTS
			    (SELECT * FROM Genre NATURAL JOIN gehoert_zu WHERE name = 'Science Fiction' AND titel = werk.titel AND jahr = werk.jahr)
			ORDER BY titel ASC;	
	
		

4.1)

Gesucht sind Auflagennummer und Jahreszahl aller Auflagen des Werks "Der Mann ohne Eigenschaften", aufsteigend sortiert nach der Auflagennummer. Verwenden Sie zur Ausgabe der Jahreszahl die Funktion TO_CHAR mit dem Datumsformat 'YYYY'.

		
			SELECT nummer, TO_CHAR(aufljahr, 'YYYY') as aufljahr
			FROM Auflage
			WHERE titel = 'Der Mann ohne Eigenschaften'
			ORDER BY nummer;	
	
		

4.2)

Gesucht sind alle Auflagen des Werks "Der Mann ohne Eigenschaften". Zu jeder Auflage sollen Auflagennummer, Jahreszahl und die durchschnittliche Seitenanzahl der Publikationen ausgegeben werden. Das Ergebnis soll aufsteigend nach der Auflagennummer sortiert sein. Verwenden Sie zur Ausgabe der Jahreszahl die Funktion TO_CHAR mit dem Datumsformat 'YYYY'.

		
			SELECT nummer, TO_CHAR(aufljahr, 'YYYY') AS aufljahr, AVG(seiten) AS seiten 
			FROM Auflage NATURAL JOIN Publikation
			WHERE titel = 'Der Mann ohne Eigenschaften'
			GROUP BY nummer, aufljahr
			ORDER BY nummer;	
	
		

4.3)

Gesucht sind alle Auflagen des Werks "Der Mann ohne Eigenschaften", zu denen es mehr als eine Publikation gegeben hat. Zu jeder Auflage sollen Auflagennummer, Jahreszahl und die durchschnittliche Seitenanzahl der Publikationen ausgegeben werden (letzteres mit dem Attributnamen "seiten"). Das Ergebnis soll aufsteigend nach der Auflagennummer sortiert sein. Verwenden Sie zur Ausgabe der Jahreszahl die Funktion TO_CHAR mit dem Datumsformat 'YYYY'.

		
			SELECT nummer, TO_CHAR(aufljahr, 'YYYY') as aufljahr, AVG(seiten) AS seiten 
			FROM Auflage NATURAL JOIN Publikation
			WHERE titel = 'Der Mann ohne Eigenschaften'
			GROUP BY nummer, aufljahr
			HAVING COUNT(*) > 1
			ORDER BY nummer;	
	
		

5)

Geben Sie fuer jedes Jahr die Jahreszahl und die Titel jener in diesem Jahr geschriebenen Werke aus, die die meisten Auflagen haben. Für Jahre, in denen keine Werke geschrieben wurden, muss nichts ausgegeben werden. Das Ergebnis soll aufsteigend nach dem Jahr und dem Werktitel sortiert werden.

		
			SELECT a.jahr, a.titel
			FROM Auflage a
			GROUP BY a.titel, a.jahr
			HAVING COUNT(*) >= ALL (SELECT COUNT(*)
			                        FROM Auflage b WHERE b.jahr = a.jahr
			                        GROUP BY b.titel, b.jahr)
			ORDER BY a.jahr, a.titel ASC;	
	
		

		
			SELECT a.jahr, a.titel
			FROM auflage a
			GROUP BY a.jahr, a.titel
			HAVING count(*) = (
			SELECT max(tmp.count) FROM (
			        SELECT count(*) as count, jahr FROM auflage group by titel, jahr
			    ) tmp WHERE tmp.jahr = a.jahr
			)
			ORDER BY a.jahr, a.titel	
	
		

6.1)

Geben Sie Namen und Kurzbiographie des aeltesten Uebersetzers/der aeltesten Uebersetzerin aus. .

		
			SELECT name, kurzbio 
			FROM UebersetzerIn NATURAL JOIN Person
			WHERE gebdat = (SELECT MIN(gebdat)
			                FROM UebersetzerIn
			                NATURAL JOIN Person)	
	
		

		
			SELECT name, kurzbio 
			FROM UebersetzerIn NATURAL JOIN Person
			WHERE gebdat = (SELECT MIN(gebdat)
			                FROM UebersetzerIn
			                NATURAL JOIN Person)	
	
		

		
			SELECT name, kurzbio
			FROM UebersetzerIn NATURAL JOIN Person
			WHERE gebdat = (SELECT MIN(gebdat)
			                FROM UebersetzerIn NATURAL JOIN Person);	
	
		

		
			select p.name, p.kurzbio
			from person p join uebersetzerin u on (p.name = u.name)
			where p.gebdat <= all (
			    select pp.gebdat from person pp join uebersetzerin uu on (pp.name = uu.name)
			)	
	
		

6.2)

Geben Sie Namen und Kurzbiographie des aeltesten Uebersetzers/der aeltesten Uebersetzerin aus, der/die sowohl ins Russische als auch aus dem Russischen uebersetzen kann. .

		
			SELECT name, kurzbio 
			FROM UebersetzerIn NATURAL JOIN Person NATURAL JOIN uebersetzt_in NATURAL JOIN uebersetzt_aus
			WHERE sprache = 'Russisch'
			AND gebdat = (SELECT MIN(gebdat)
			                FROM UebersetzerIn NATURAL JOIN Person NATURAL JOIN uebersetzt_in NATURAL JOIN uebersetzt_aus
			                WHERE sprache = 'Russisch');	
	
		

		
			SELECT p.name, p.kurzbio 
			FROM UebersetzerIn u JOIN Person p ON u.name = p.name JOIN uebersetzt_in i ON p.name = i.name JOIN uebersetzt_aus a ON p.name = a.name
			WHERE i.sprache = 'Russisch'
			AND a.sprache = 'Russisch'
			AND p.gebdat = (SELECT MIN(p1.gebdat)
			                FROM UebersetzerIn u1 JOIN Person p1 ON u1.name = p1.name JOIN uebersetzt_in i1 ON p1.name = i1.name JOIN uebersetzt_aus a1 ON p1.name = a1.name
			                WHERE i1.sprache = 'Russisch'
			                AND a1.sprache = 'Russisch');	
	
		

		
			select p.name, p.kurzbio
			from person p
			join uebersetzt_aus ua on (p.name = ua.name)
			join uebersetzt_in ui on (p.name = ui.name)
			where ua.sprache = 'Russisch' and ui.sprache = 'Russisch'
			and p.gebdat <= all (
			    select pp.gebdat
			    from person pp
			    join uebersetzt_aus uua on (pp.name = uua.name)
			    join uebersetzt_in uui on (pp.name = uui.name)
			    where uua.sprache = 'Russisch' and uui.sprache = 'Russisch'
			)	
	
		

7)

Geben Sie eine Liste aller AutorInnen (Name) aus und die Werke (Titel und Beschreibung), die Sie vor 2000 geschrieben haben. Hat ein Autor oder eine Autorin kein Werk vor 2000 geschrieben, soll statt Titel und Beschreibung „kein Werk vor 2000“ ausgegeben werden.

		
			SELECT a.name, COALESCE(w.titel, 'kein Werk vor 2000') as titel, COALESCE(w.beschreibung, 'kein Werk vor 2000') as beschreibung
			FROM autorin a LEFT OUTER JOIN (schreibt s JOIN werk w ON s.titel=w.titel and s.jahr=w.jahr AND s.jahr<to_date('2000', 'YYYY')) on s.person=a.name;	
	
		

		
			SELECT a.name, COALESCE(s.titel, 'kein Werk vor 2000') as titel, COALESCE(w.beschreibung, 'kein Werk vor 2000') as beschreibung
			FROM autorin a
			LEFT OUTER JOIN schreibt s on (s.person=a.name AND s.jahr < TO_DATE('2000', 'YYYY'))
			LEFT OUTER JOIN werk w on (w.titel=s.titel AND w.jahr=s.jahr)	
	
		

		
			SELECT name, coalesce(titel,'kein Werk vor 2000'), coalesce(beschreibung, 'kein Werk vor 2000')
			FROM autorin natural left outer join
			(
			SELECT person as name, titel, beschreibung
			FROM schreibt natural join werk
			WHERE jahr < to_date('2000','YYYY')
			) as tmp	
	
		

8)

Gesucht sind die Namen der Lektor/innen, die an allen Auflagen des Werks "Holzfaellen" mitgearbeitet haben, zu denen es mindestens eine Publikation gegeben hat. Sie koennen davon ausgehen, dass es nur ein Werk mit diesem Titel gibt. Die Ergebnisse sollen aufsteigend sortiert werden.

		
			SELECT name 
			FROM lektoriert
			WHERE titel = 'Holzfaellen'
			AND (titel, jahr, nummer) IN (SELECT titel, jahr, nummer
			                                FROM Publikation
			                                WHERE titel = 'Holzfaellen')
			GROUP BY name HAVING COUNT(*) = (SELECT COUNT(*)
			                                FROM Auflage
			                                WHERE titel = 'Holzfaellen'
			                                AND nummer IN (SELECT nummer
			                                                FROM Publikation
			                                                WHERE titel = 'Holzfaellen'))
			ORDER BY name ASC;	
	
		

		
			SELECT l.name 
			FROM LektorIn l
			WHERE NOT EXISTS (
			    SELECT * FROM Auflage WHERE titel = 'Holzfaellen' AND (titel, jahr,
			nummer) IN
			        (SELECT titel, jahr, nummer FROM Publikation WHERE titel =
			'Holzfaellen')
			        AND (titel, jahr, nummer) NOT IN
			        (SELECT titel, jahr, nummer FROM lektoriert WHERE name =
			l.name))
			ORDER BY name ASC;	
	
		

		
			SELECT DISTINCT l.name 
			FROM lektoriert l
			WHERE NOT EXISTS (
			    SELECT * FROM Auflage WHERE titel = 'Holzfaellen' AND (titel, jahr,
			nummer) IN
			        (SELECT titel, jahr, nummer FROM Publikation WHERE titel =
			'Holzfaellen')
			        AND (titel, jahr, nummer) NOT IN
			        (SELECT titel, jahr, nummer FROM lektoriert WHERE name =
			l.name))
			ORDER BY name ASC;	
	
		

		
			select distinct l.name
			from lektoriert l
			where
			(
			select count(*)
			from lektoriert lk2
			where lk2.name = l.name and lk2.titel = 'Holzfaellen' and (lk2.titel, lk2.jahr, lk2.nummer) in (select p.titel, p.jahr, p.nummer from publikation p)
			) = (
			select count(a.titel)
			from auflage a
			where a.titel = 'Holzfaellen' and (a.titel, a.jahr, a.nummer) in (select p.titel, p.jahr, p.nummer from publikation p)
			)
			order by l.name asc	
	
		

9.1)

Gesucht sind die Namen der Uebersetzer/innen, die aus jeder Sprache uebersetzen koennen. Das Ergebnis soll aufsteigend sortiert werden.

		
			SELECT name 
			FROM UebersetzerIn NATURAL JOIN uebersetzt_aus
			GROUP BY name
			HAVING COUNT(*) = (SELECT COUNT(*)
			                        FROM Sprache)
			ORDER BY name ASC;	
	
		

		
			SELECT name 
			FROM UebersetzerIn u
			WHERE NOT EXISTS (SELECT *
			                   FROM Sprache s
			                   WHERE s.name NOT IN (SELECT a.sprache
			                                        FROM uebersetzt_aus a
			                                        WHERE a.name = u.name))
			ORDER BY name ASC;	
	
		

		
			select u.name
			from uebersetzerin u
			where (select distinct count(*) from sprache) = (select distinct count(*) from uebersetzt_aus ue where ue.name = u.name)
			order by u.name asc	
	
		

9.2)

Gesucht sind die Namen der Uebersetzer/innen, die mindestens drei Vertraege abgeschlossen haben und aus jeder Sprache uebersetzen koennen. Das Ergebnis soll aufsteigend sortiert werden.

		
			SELECT name 
			FROM UebersetzerIn NATURAL JOIN uebersetzt_aus
			WHERE (SELECT COUNT(*)
			        FROM Vertrag
			        WHERE mit = name) >= 3
			GROUP BY name
			HAVING COUNT(*) = (SELECT COUNT(*)
			                        FROM Sprache)
			ORDER BY name ASC;	
	
		

		
			SELECT name 
			FROM UebersetzerIn u
			WHERE (SELECT COUNT(*)
			        FROM Vertrag v
			        WHERE v.mit = u.name) >= 3
			            AND NOT EXISTS (SELECT s.name
			                        FROM Sprache s
			                        WHERE s.name NOT IN (SELECT a.sprache
			                                                FROM uebersetzt_aus a
			                                                WHERE a.name = u.name))
			ORDER BY name ASC;	
	
		

10)

Es sollen Name und Geburtsdatum aller Uebersetzer/innen, die ins Englische uebersetzen, und aller Lektor/innen, die in der Sprache Englisch arbeiten koennen, ausgegeben werden. Das Ergebnis soll absteigend nach dem Namen sortiert werden. Verwenden Sie zur Ausgabe des Geburtsdatums die Funktion TO_CHAR mit dem Datumsformat 'DD-MM-YYYY'.

		
			SELECT name, TO_CHAR(gebdat, 'DD-MM-YYYY') as gebdat
			FROM Person NATURAL JOIN uebersetzt_in
			WHERE sprache = 'Englisch'
			UNION
			(SELECT name, TO_CHAR(gebdat, 'DD-MM-YYYY') as gebdat
			FROM Person NATURAL JOIN arbeitet_in
			WHERE sprache = 'Englisch')
			ORDER BY name DESC	
	
		

		
			SELECT p.name, TO_CHAR(p.gebdat, 'DD-MM-YYYY') as gebdat
			FROM Person p JOIN uebersetzt_in i ON p.name = i.name
			WHERE i.sprache = 'Englisch'
			UNION
			(SELECT p.name, TO_CHAR(p.gebdat, 'DD-MM-YYYY') as gebdat
			FROM Person p JOIN arbeitet_in a ON p.name = a.name
			WHERE a.sprache = 'Englisch')
			ORDER BY name DESC;	
	
		

		
			SELECT name, TO_CHAR(gebdat,'DD-MM-YYYY') as gebdat
			FROM person
			WHERE name IN (
			  (SELECT name from uebersetzt_in WHERE sprache = 'Englisch')
			  UNION
			  (SELECT name from arbeitet_in WHERE sprache = 'Englisch'))
			ORDER BY name DESC