Grundlagen Datenbanken

 Grundlagen Datenbanken Sommersemester 2012 Prof. Dr. Gregor Engels Jan Bals, Markus Luckey, Maria Gerges, Robert Mittendorf, Thomas Sommer Hausübung Nr. 6 Kalenderwoche 27 und 28 Beispiellösungen für die Rechnerübung SQL Mögliche Lösungen: Anfrage 1: SELECT f.Titel,f.Dauer FROM
Film f WHERE NOT EXISTS (SELECT *
FROM Film g WHERE g.Dauer > f.Dauer)
SELECT f.Titel,f.Dauer FROM Film f WHERE f.Dauer = (SELECT
MAX(g.Dauer) FROM Film g) Anfrage 2: SELECT COUNT(*) FROM (SELECT DISTINCT PID FROM Regie); Anfrage 3: SELECT Name FROM Person NATURAL JOIN Darsteller GROUP BY PID HAVING
COUNT(FID)>10; SELECT Name FROM Person NATURAL JOIN (SELECT PID FROM Darsteller
GROUP BY PID HAVING COUNT(FID) > 10);
Anfrage 4: SELECT Titel FROM Film NATURAL JOIN (SELECT * FROM Darsteller GROUP
BY FID HAVING COUNT(PID) > 25);
Anfrage 5: SELECT p.Name FROM Person p WHERE NOT EXISTS (SELECT * FROM Film f
WHERE f.Dauer>130 AND f.Wertung=5.9 AND NOT EXISTS (SELECT * FROM
Darsteller d WHERE d.FID = f.FID AND d.PID=p.PID));
(Für welchen Darsteller existieren keine Filme, an denen er nicht mitwirkt?) Anfrage 6: SELECT DISTINCT Name FROM Person NATURAL JOIN(SELECT PID FROM(
SELECT * FROM Darsteller INTERSECT SELECT * FROM Regie INTERSECT
SELECT * FROM Produzent));
Anfrage 7: SELECT DISTINCT Name FROM Person NATURAL JOIN (SELECT PID
FROM(SELECT * FROM Darsteller UNION SELECT * FROM Regie UNION SELECT
* FROM Produzent) GROUP BY PID HAVING COUNT(FID) >=7);
Grundlagen Datenbanken Sommersemester 2012 Prof. Dr. Gregor Engels Jan Bals, Markus Luckey, Maria Gerges, Robert Mittendorf, Thomas Sommer Hausübung Nr. 6 Kalenderwoche 27 und 28 Anfrage 8: SELECT Titel FROM Film NATURAL JOIN Inhalt WHERE Inhalt LIKE
"%Golf%";
Anfrage 9: SELECT p1.name, p2.name FROM Produzent pr1 NATURAL JOIN Person p1,
Produzent pr2 NATURAL JOIN Person p2 WHERE pr1.fid=pr2.fid AND
p1.pid<p2.pid GROUP BY p1.name,p2.name HAVING COUNT(pr1.fid) > 2;
Anfrage 10: SELECT name FROM (SELECT pid FROM Produzent INTERSECT SELECT pid
FROM Darsteller) NATURAL JOIN Person; Anfrage 11: SELECT f.Titel FROM Film f WHERE f.Jahr=2001 AND f.dauer = (SELECT
MIN(g.Dauer) FROM Film g WHERE g.Jahr=2001); SELECT f.Titel FROM Film f WHERE f.Jahr=2001 AND NOT EXISTS ( SELECT
g.Titel, g.Dauer FROM film g WHERE g.Jahr=2001 AND NOT
g.dauer>=f.dauer);
SELECT f.Titel FROM Film f WHERE f.Jahr=2001 AND NOT EXISTS ( SELECT
g.Titel, g.Dauer FROM film g WHERE g.Jahr=2001 AND g.dauer<f.dauer);