A1. Welche Bücher (Ausgabe von Titel und Alter = 2011 - jahr) sind nach 1997 eingegangen? Ordnen Sie die Titel nach dem Alter und bei gleichem Alter alphabetisch. Achtung: der SQL-Trainer akzeptiert keine Anfragen, in denen ‘Alter’ vorkommt

SELECT  titel, 2011-Jahr AS alt, 'Jahre alt' AS Text
FROM    buch
WHERE   jahr > 1997
ORDER BY alt DESC, titel

A2. Welche Bücher der Datenbank sind im Springer-Verlag an den verschiedenen Verlagsorten seit 1990 erschienen. Ordnen Sie die Ergebnis nach dem Ort alphabetisch.

SELECT  B.buchid, B.titel, V.name, V.ort, B.jahr
FROM    buch B NATURAL JOIN verlag V
WHERE   V.name='Springer' AND B.jahr>=1990
ORDER   BY V.ort

A3. Welche Autoren haben mindestens ein Buch zum Schlagwort ‘Datenbank’ verfasst?

SELECT  DISTINCT A.nachname, A.vornamen, A.autorid
FROM    autor A NATURAL JOIN buch_aut BA
        NATURAL JOIN buch_sw BS NATURAL JOIN schlagwort SW
WHERE   SW.schlagwort = 'Datenbank'
ORDER BY A.nachname

A4. Geben Sie das älteste Buch / die ältesten Bücher (buchid, titel, jahr) der Datenbank aus. (d.h. die Bücher mit der kleinsten Jahreszahl als Erscheinungsjahr). Ihre Anfrage darf hierbei keine Annahme über das am weitesten zurückliegende Erscheinungsjahr machen, d.h. sie muss auch dann noch funktionieren, wenn ein noch älteres Buch aufgenommen wird.

SELECT buchid, titel, jahr
FROM buch
WHERE jahr=(SELECT MIN(jahr) FROM buch)

A5. Welche Autoren haben den gleichen Nachnamen wie ein Verlag? (17)

SELECT name
FROM verlag
INTERSECT
SELECT nachname
FROM autor

A6. Welche Autoren (name, vornamen) waren bei in (ausschliesslich in) Berlin herausgegebenen Büchern als Herausgeber beteiligt? Verhindern Sie Doppelausgaben, und ordnen Sie die Ergebnismenge nach den Autornamen (name).

SELECT  DISTINCT A.nachname, A.vornamen
FROM    autor A NATURAL JOIN buch_aut BA NATURAL JOIN buch B
        NATURAL JOIN verlag V
WHERE   V.ort='Berlin' AND BA.rolle='h'
ORDER BY A.nachname

A7. Wieviel Prozent der Autoren sind mit unvollständigem (d.h. mit einem mit “.” abgekürzten) oder gar keinem (d.h. NULL-wertigen) Vornamen in der Datenbank gespeichert? (Hinweis: Sie benötigen den datenbankspezifischen Befehl CAST(i AS DOUBLE PRECISION) um den Integerwert i nach DOUBLE zu konvertieren).

SELECT  100 * (COUNT(*)/
        CAST((SELECT COUNT(*) FROM autor) AS double precision))
FROM    autor
WHERE   vornamen LIKE '%.%' OR vornamen IS NULL

A8. Geben Sie für jeden Buchautor seine ID und seinen Namen sowie die Anzahl der von ihm verfassten Bücher aus. Ordnen Sie die Ergebnismenge absteigend nach der Anzahl der Bücher des Autors, bei gleicher Anzahl alphabetisch nach dem Namen.

SELECT  A.autorid, A.nachname, count(*) AS ANZAHL
FROM    autor A NATURAL JOIN buch_aut BA
WHERE   BA.rolle='v'
GROUP BY A.autorid, A.nachname
ORDER BY ANZAHL DESC, A.nachname

A9. Welche Autoren (Name, Vorname), deren Name mit einem ‘G’ beginnt, waren nie als Herausgeber an Büchern beteiligt? Ordnen Sie die duplikatfreie Ergebnismenge nach dem Autornamen und -vornamen. (240, nicht 273)

SELECT  A.nachname, A.vornamen
FROM    autor A
WHERE   A.nachname LIKE 'G%'
AND NOT EXISTS ( SELECT * FROM buch_aut BA
                  WHERE rolle='h' AND A.autorid= BA.autorid)
ORDER BY A.nachname, A.vornamen

A10. Erstellen Sie eine kombinierte Liste (einspaltig) aller Vor- und Zunamen von Autoren. Die Liste soll alphabetisch sortiert sein. (5045/6396)

SELECT vornamen || ' ' || nachname
FROM autor
ORDER BY nachname, vornamen

andere Lesart:

SELECT name
FROM (
        (SELECT vornamen FROM autor)
UNION
        (SELECT nachname FROM autor)
) AS nameneinspaltig(name)
ORDER BY name

A11. Erstellen Sie eine Liste der Verlage - absteigend sortiert nach der Anzahl der im Verlag erschienen Bücher. (403; Springer(983), AW (544), PH (223), …)

SELECT verlag.name, count(*) 
FROM verlag, buch 
WHERE verlag.verlagsid = buch.verlagsid 
GROUP BY verlag.name 
ORDER BY count(*) desc

A12. Wie heißen die Bücher mit den meisten Schlagworten?

SELECT titel, buchid
FROM buch NATURAL JOIN buch_sw
GROUP BY buchid, titel
HAVING COUNT(*)= (SELECT MAX(swc)
                FROM (  SELECT COUNT(swid) AS swc
                        FROM buch_sw
                        GROUP BY buchid ) AS cnt )

oder

SELECT titel, buchid
FROM buch
WHERE buchid IN
        (SELECT buchid
         FROM buch_sw
         GROUP BY buchid
         HAVING COUNT(*)= (SELECT MAX(swc)
                        FROM (  SELECT COUNT(swid) AS swc
                                FROM buch_sw
                                GROUP BY buchid ) AS cnt ) )

A13. Welche Buecher haben im Schlagwort den String “Unix”? (4) – Hinweis: Um Gross-/Kleinschreibung von UNIX/Unix/unix zu vernachlaessigen hilft die Funktion “lower(…)” bzw “upper(…)”.

select schlagwort, titel 
from buch natural join buch_sw natural join schlagwort
where lower(schlagwort) like '%unix%'

A14. Schlagworte sind unterschiedlich lang. Wieviele Schlagworte gibt es mit der haeufigsten Schlagwortlaenge? (57, Laenge=15) – Hinweis: Benutzen Sie die Funktion “char_length(…)”.

select max(anzahl) from (
  select char_length(schlagwort) as laenge, count(*) as anzahl
  from schlagwort 
  group by laenge
) as q

A15. Welche Verlage haben in allen Jahren von 1995 bis einschließlich 2001 wenigstens eines ihrer Bücher in die Datenbank eingebracht, vorausgesetzt, dass aus dem betreffenden Jahr überhaupt Bücher in der Datenbank sind?

SELECT  v.name
FROM    verlag v
WHERE   NOT EXISTS (
        (SELECT DISTINCT jahr
         FROM buch
         WHERE jahr BETWEEN 1995 AND 2001)
        EXCEPT
        (SELECT DISTINCT jahr
         FROM buch b
         WHERE b.verlagsid=v.verlagsid)
)

A16. Erstellen Sie eine alphabetisch sortierte Liste aller Schlagworte und zählen Sie, wie oft jedes Schlagwort bei Büchern vergeben wurde, die im Springer-Verlag erschienen sind. (Hinweis: Verwenden Sie dazu die OUTER-JOIN-Konstruktion)

SELECT  schlagwort, Anzahl
FROM    schlagwort sw LEFT OUTER JOIN
  (SELECT swid, COUNT(*) AS Anzahl
   FROM   buch_sw bs JOIN
      (SELECT buchid
                FROM buch b JOIN verlag v USING (verlagsid)
                WHERE v.name='Springer'
        ) AS bi USING (buchid)
   GROUP BY swid
  ) AS sws ON sw.swid=sws.swid
ORDER BY schlagwort

A17-0. Vergleichen Sie die Anzahl der Buecher insgesamt mit der Anzahl der Bücher ohne Preis, und mit dem Preis 79.90 sowie ungleich dem Preis 79.90.

select 'alle' as text, count(*) as p1 from buch
union
select 'null' as text, count(*) as p1 from buch 
where preis is null
union
select 'ungleich 79' as text, count(*) as p1 from buch 
where preis <> 79.90
union
select 'gleich 79' as text, count(*) as p1 from buch 
where preis = 79.90

A17. Von wievielen Autoren werden jeweils alle ihre Bücher für den Preis von 79,90 angeboten? (46/50)

SELECT count(*) FROM (
  ( SELECT autorid      // alle Autoren mit Bücher à 79,90
    FROM autor NATURAL JOIN buch_aut NATURAL JOIN buch
    WHERE preis=79.90 )
EXCEPT                                  // abzüglich derjenigen
  ( SELECT autorid      // Autoren mit Bücher ungleich 79,90
    FROM autor NATURAL JOIN buch_aut NATURAL JOIN buch
    WHERE preis<>79.90 or preis IS NULL )
) AS c

oder

SELECT count(*) FROM autor a
WHERE 79.9 = ALL (
  SELECT preis
  FROM buch_aut ba NATURAL JOIN buch b
  WHERE ba.autorid=a.autorid)
AND autorid IN
 (SELECT autorid FROM buch_aut) // Autoren ohne Bücher rausfiltern

oder

SELECT count(distinct autorid)
FROM autor A NATURAL JOIN buch_aut NATURAL JOIN buch
WHERE preis=79.90 
AND NOT EXISTS (
  SELECT buchid
  FROM buch b2 NATURAL JOIN buch_aut ba2
  WHERE ba2.autorid = A.autorid
  AND (b2.preis<>79.90 OR b2.preis IS NULL)
)

A18. Welche Bücher haben einen Autor mit Rang=2, aber keinen mit Rang=1? (3 Bücher: 4901, 4902, 4904)

SELECT DISTINCT b.*
FROM buch_aut b2, buch b
WHERE b.buchid = b2.buchid
AND b2.rang = 2
AND b.buchid not in 
( SELECT b1.buchid 
  FROM buch_aut b1
  WHERE b1.buchid = b2.buchid
  and b1.rang = 1
)

oder

SELECT buchid
FROM buch_aut 
GROUP BY buchid
HAVING MIN (rang) = 2

A19. Geben sie eine bzgl. des Nachnamens alphabetische Liste derjenigen Autoren aus, die jeweils mehr als 7 Bücher geschrieben haben und deren Bücher jeweils alle im selben Verlag erschienen sind. (5 Autoren: Date, … , Valentin)

SELECT *
FROM autor
WHERE autorid in (
  SELECT autorid
  FROM ( 
    SELECT buch_aut.autorid, buch.verlagsid, count(buch.buchid)
    FROM buch_aut, buch
    WHERE buch.buchid = buch_aut.buchid
    GROUP BY buch_aut.autorid, buch.verlagsid
    HAVING count(*) > 7
  ) temp
  GROUP BY autorid
  HAVING count(*) = 1
)
ORDER BY nachname, vornamen

A20. Listen Sie alphabetisch alle Titel derjenigen Bücher auf, für die es ein gleichnamiges Buch (gleicher Titel) gibt, was später zum gleichen Preis (ohne Beachtung der Währung) erschienen ist. (15: ATM-Netzwerke, …, Theoretische Informatik)

SELECT DISTINCT b1.titel
FROM buch b1, buch b2
WHERE b1.titel = b2.titel
AND b1.jahr < b2.jahr
AND b1.preis = b2.preis
ORDER BY b1.titel

A21. Ermitteln Sie eine Liste, die für jeden Verlag das teuerste bzw. (bei gleichem Preis) die teuersten Bücher (ohne Beachtung der Währung) erstellt, d.h. finden Sie alle Bücher, deren Preis >= dem Preis aller Bücher des gleichen Verlags ist. Verwenden Sie hierbei das ALL-Prädikat. (197; u.a. Bertz mit zwei Büchern zu 48,00)

SELECT distinct b1.titel, b1.preis, v.name
FROM buch b1, verlag v
WHERE b1.verlagsid = v.verlagsid
AND b1.preis >= ALL (
  SELECT b2.preis
  FROM buch b2
  WHERE b1.verlagsid = b2.verlagsid
)
ORDER BY v.name

A22. Erstellen Sie eine Liste derjenigen Bücher (Buchtitel, Preis sowie Verlagsname), für die es mindesten ein weiteres Buch des gleichen Verlages gibt, das mindestens 10 mal so teuer ist (ohne Beachtung der Währung). Verwenden Sie hierbei das ANY-Prädikat. (50; u.a. 5 Bücher von AW mit Preis <=29,80 wegen teuerstem AW-Buch für 298,00)

SELECT distinct b1.titel, b1.preis, v.name
FROM buch b1 natural join verlag v
WHERE b1.preis <= ANY (
  SELECT b2.preis/10
  FROM buch b2
  WHERE b1.verlagsid = b2.verlagsid
)
order by v.name