/****************************************************************************** ** Purpose : Aufgabensammlung SQL - SW4 ** Date : 2013-03-16 ** Author : Felix Rohrer ** URL : http://hslu.ximit.ch ******************************************************************************/ /* USE UNI-DB */ USE uni GO /* Aufgabe 1 */ SELECT Raum FROM Professoren WHERE Name = 'Curie' /* Aufgabe 2 */ SELECT VorlNr FROM hoeren WHERE MatrNr = 29120 /* Aufgabe 3 */ SELECT VorlNr, Titel FROM Vorlesungen ORDER BY Titel /* Aufgabe 4 */ SELECT Titel, PersNr FROM Vorlesungen AS v INNER JOIN Professoren AS p ON p.PersNr = v.gelesenVon ORDER BY PersNr, Titel DESC /* Aufgabe 5 */ SELECT a.Name AS AssistentenName, p.Name AS ProfessorName FROM Assistenten AS a INNER JOIN Professoren AS p ON p.PersNr = a.Boss ORDER BY a.Name /* Aufgabe 6 */ SELECT DISTINCT p.Name AS ProfessorName FROM Professoren AS p INNER JOIN Vorlesungen AS v ON v.gelesenVon = p.PersNr INNER JOIN hoeren AS h ON h.VorlNr = v.VorlNr WHERE h.MatrNr = (SELECT MatrNr FROM Studenten WHERE Name = 'Carnap') /* Aufgabe 7 */ SELECT DISTINCT MatrNr FROM hoeren /* Aufgabe 8 */ SELECT DISTINCT s.Name FROM Studenten AS s INNER JOIN hoeren as h ON h.MatrNr = s.MatrNr /* Aufgabe 9 */ SELECT DISTINCT s.Name FROM Studenten AS s INNER JOIN hoeren as h ON h.MatrNr = s.MatrNr INNER JOIN Vorlesungen as v ON v.VorlNr = h.VorlNr WHERE v.Titel = 'Bioethik' /* Aufgabe 10 */ SELECT DISTINCT s.Name FROM Studenten AS s INNER JOIN hoeren AS h ON h.MatrNr = s.MatrNr INNER JOIN Vorlesungen AS v ON v.VorlNr = h.VorlNr INNER JOIN Professoren AS p ON p.PersNr = v.gelesenVon WHERE p.Name = 'Russel' /* Aufgabe 11 */ SELECT p.Name AS ProfessorName, s.Name AS StudentName FROM Professoren AS p INNER JOIN Vorlesungen AS v ON v.gelesenVon = p.PersNr INNER JOIN hoeren AS h ON h.VorlNr = v.VorlNr INNER JOIN Studenten AS s ON s.MatrNr = h.MatrNr ORDER BY p.Name, s.Name /* ************************************************************************* */ /* 2. Weitere Aufgaben */ /* Aufgabe 12 */ USE zug SELECT b.Name FROM Bahnhof AS b INNER JOIN Stadt AS s ON s.Name = b.Name; /* Aufgabe 13 */ USE zug SELECT b.Name FROM Bahnhof AS b WHERE b.Name NOT IN (SELECT Name FROM Stadt) /* Aufgabe 14 */ USE uni SELECT p.Name FROM Professoren AS p WHERE NOT EXISTS (SELECT * FROM Assistenten AS a WHERE a.Boss = p.PersNr) /* Aufgabe 14b */ USE uni SELECT p.Name FROM Professoren AS p WHERE p.PersNr NOT IN (SELECT Boss FROM Assistenten) /* Aufgabe 15 */ USE uni SELECT s.Name FROM Studenten AS s INNER JOIN pruefen AS pr ON pr.MatrNr = s.MatrNr WHERE pr.Note = 1 /* Aufgabe 16 */ USE uni SELECT s.Name FROM Studenten AS s WHERE s.MatrNr IN (SELECT MatrNr FROM pruefen WHERE Note > (SELECT AVG(Note) FROM pruefen) ) /* Aufgabe 17 */ USE uni SELECT Name FROM Professoren WHERE PersNr NOT IN (SELECT PersNr FROM pruefen) /* Aufgabe 18 */ USE zug SELECT AVG(Gleise) AS AvgGleise FROM Bahnhof /* Aufgabe 19 */ USE zug SELECT Name FROM Bahnhof WHERE Gleise < (SELECT AVG(Gleise) FROM Bahnhof) /* Aufagbe 20 */ USE zug SELECT Zug, Abfahrt FROM Zughalt WHERE Halt = 9 -- 9 = Zürich HB AND Abfahrt > (SELECT Ankunft FROM Zughalt WHERE Halt = 9 -- 9 = Zürich HB AND Zug = 1) -- 1 = Zug 1 /* Aufgabe 21 */ USE zug SELECT Zug, Abfahrt FROM Zughalt WHERE Halt = 9 -- 9 = Zürich HB AND Abfahrt > (SELECT MAX(Ankunft) FROM Zughalt WHERE Halt = 9 AND (Ankunft IS NOT NULL)) -- 9 = Zürich HB /* Aufgabe 22 */ USE zug SELECT Zug FROM Zughalt GROUP BY Zug HAVING COUNT(Halt) <= 3 /* Aufgabe 23 */ USE zug SELECT z.Zug, b.Name FROM Zughalt AS z INNER JOIN Bahnhof AS b ON b.BahnhofId = z.Halt WHERE Zug NOT IN (SELECT Zug FROM Zughalt WHERE Halt = (SELECT BahnhofId FROM Bahnhof WHERE Name = 'Olten')) AND (Ankunft IS NULL OR Abfahrt IS NULL)