- Team
- Forschung
- Studium
- Klausurtermine
- M.Sc. Data Science
- SS 2022
- SS 2023
- WS 2022/23
- WS 2023/24
- LOTS-Nutzung
- Vergangene Semester
- Module der Abteilung
- Abschlussarbeiten
- Top-Studenten
- Erasmus
- Service
LOTS of SQL
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