SQL Abfragen mit Mini Datenbank

[ocsme]

Hallo zusammen,

ich beschäftige mich gerade mit dem Buch Datenbanksysteme von Kemper. Dort gibt es eine recht simple Datenbank die wie folgt aufgebaut ist:

Studenten: {[Matrnr, Name, Semester]}
hören: {[Matrnr, Vorlnr]}
Vorlesungen: {[Vorlnr, SWS, Titel, gelesenvon (PersNr von einem Professor]}
voraussetzen: {[vorgänger (ist eine Vorlnr), nachfolger (ist eine Vorlnr)]}
prüfen: {[Matrnr, Vorlnr, Persnr, Note]}
Professoren: {[PersNr, Name, Rang, Raum]}
Assistenten: {[PersNr, Name, Fachgebiet, arbeitet_für (PersNr von einem Professor)]}

Nun habe ich 3 Aufgaben zu dieser Datenbank die ich nicht gelöst bekomme.
Die Fragen sind folgende:

1. Die SQL Abfrage soll die verschiedenen Namen aller Studenten ausgeben, die irgendeine Vorlesung hören, für welche die Vorlesung „Mathematik“ eine direkte Voraussetzung ist.
2. Die SQL Abfrage soll die Titel aller Vorlesungen ausweisen, die eine Voraussetzung für mehr als eine andere Vorlesung sind. (ohne Duplikate)
3. Die SQL Abfrage soll die Namen der Studenten, die eine Vorlesung hören, zusammen mit der Note ausweisen, mit der sie die Prüfung zur jeweiligen Vorlesung ggf. abgeschlossen haben. Die Studenten sollen auch dann ausgewiesen werden, wenn sie die jeweilige Vorlesung nur gehört haben, auch ohne dass eine Prüfungsdatensatz vorliegt.

Aufgabe 3 habe ich versucht zu Lösen:

select tmp.name, tmp.titel, note
from (select name, s.matrnr, titel
      from studenten s
      join hoeren h on s.matrnr = h.matrnr
      join vorlesungen v on h.vorlnr = v.vorlnr) tmp
left join pruefen p on tmp.matrnr = p.matrnr

Doch das stimmt überhaupt nicht. So wie ich das sehe sind alle 3 Aufgabenstellung ähnlich Aufgebaut. Bitte verbessert mich wenn das quatsch ist ;-)
Denn ich benötige ja immer 2 Mengen die ich dann "vereine". Bei der 1ten Aufgabenstellung könnte man ja auch 2 Mengen konstruieren wie folgt:

--1te Menge:

select name
from studenten s
join hoehren h on s.matrnr = h.matrnr

--2te Menge:
select vorlnr
from vorlesungen v
join voraussetzen vv on v.vorlnr = vv.vorgaenger

Weiß irgendjemand weiter?
Ich vermute es müssen Korrelierte Abfragen sein doch die bekomme ich auch nicht aufgebaut =(

Danke im voraus 🙂

 

[mihe7]

Zerlege das Problem in Teilprobleme.

Hinweis: natürlich führen mehrere Wege nach Rom.

Beispiel:

Die SQL Abfrage soll die verschiedenen Namen aller Studenten ausgeben, die irgendeine Vorlesung hören, für welche die Vorlesung „Mathematik“ eine direkte Voraussetzung ist.

Der hintere Teil: "Vorlesungen, für die die Vorlesung "Mathemtik" eine direkte Voraussetzung ist". Wie wird das in den Tabellen abgebildet? Offensichtlich über die Tabelle voraussetzen. Dabei muss vorgänger die ID der Mathe-Vorlesung sein und nachfolger ist dann die ID der gewünschten Vorlesung.

SELECT
  nachfolger 
FROM voraussetzen INNER JOIN Vorlesungen 
    ON voraussetzen.vorgaenger=Vorlesungen.Vorlnr 
 WHERE Vorlesungen.Titel = 'Mathematik';

Nächster Part: Studenten, die irgendeine solche Vorlesungen hören. Dazu wird Tabelle hören benötigt. Hier kann man nun verschiedene Möglichkeiten andenken: so kann man einen Verbund bilden, den könnte man direkt in die vorstehende Abfrage einbauen:

SELECT
    Matrnr
FROM
    voraussetzen
    INNER JOIN Vorlesungen ON voraussetzen.vorgaenger = Vorlesungen.Vorlnr
    INNER JOIN hören ON voraussetzungen.nachfolger = hören.Vorlnr
WHERE
    Vorlesungen.Titel = 'Mathematik';

Nachteil: die Matrnr tauchen ggf. mehrfach auf. Das kann man mit einem DISTINCT verhindern.

Zum Schluss brauchen wir noch die Namen also eine Verquickung mit Tabelle Stundenten. Hier muss man jetzt ein wenig aufpassen, denn es kann gleiche Namen mit unterschiedliche Matrikelnummern geben. Man kann also nicht einfach einen weiteren JOIN in die obenstehende Abfrage einbauen und dann DISTINCT auf den Namen anwenden. Hier können JOINs, IN, ANY, EXISTS helfen. Mal ein Beispiel mit einem JOIN:

SELECT
    Name
FROM
    Studenten
    INNER JOIN (
        SELECT
            DISTINCT Matrnr
        FROM
            voraussetzen
            INNER JOIN Vorlesungen ON voraussetzen.vorgaenger = Vorlesungen.Vorlnr
            INNER JOIN hören ON voraussetzungen.nachfolger = hören.Vorlnr
        WHERE
            Vorlesungen.Titel = 'Mathematik'
    ) hoerer ON Studenten.Matrnr = hoerer.Matrnr;

 

[M.L.]

"Auf Vorrat" gibt es die versch. SQL Joins auch visuell erklärt: https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
(auch wenn es lt. div. Kommentaren (z.B. https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/ ) mit Rechtecken zielführender wäre und es auch farbenblinde Leute gibt)

 

[ocsme]

Vielen Lieben Dank an euch zwei =)
Dann werde ich mal versuchen die weiteren Aufgaben auch noch zu lösen ;-)
Zur Not frage ich hier erneut nach.

 

[ocsme]

Die SQL Abfrage soll die Titel aller Vorlesungen ausweisen, die eine Voraussetzung für mehr als eine andere Vorlesung sind. (ohne Duplikate)

select distinct titel
from vorlesungen v
join voraussetzen vv on v.vorlnr = vv.vorgaenger
where v.vorlnr in (
    select vorgaenger
    from voraussetzen
    group by vorgaenger
    having count(vorgaenger) > 1);

Die SQL Abfrage soll die Namen der Studenten, die eine Vorlesung hören, zusammen mit der Note ausweisen, mit der sie die Prüfung zur jeweiligen Vorlesung ggf. abgeschlossen haben. Die Studenten sollen auch dann ausgewiesen werden, wenn sie die jeweilige Vorlesung nur gehört haben, auch ohne dass eine Prüfungsdatensatz vorliegt.

select s.name, v.titel, p.note
from (select h.matrnr MATRNR, h.vorlnr VORLNR from hoeren h
      union
      select p.matrnr MATRNR, p.vorlnr VORLNR from pruefen p) a
join studenten s on a.matrnr = s.matrnr
join vorlesungen v on v.vorlnr = a.vorlnr
left join pruefen p on (p.vorlnr = a.vorlnr and p.matrnr = a.matrnr);

Könnte das jemand überfliegen und mir sagen wenn ich etwas Falsch gemacht habe?

 

[mihe7]

Die erste Abfrage sieht in Ordnung aus, bei der zweiten hast Du m. E. zuviel gewollt, den Sub-Select brauchst Du nicht, einfach mit hoeren joinen reicht.

 

[ocsme]

Wenn ich nur mit hoeren joine hätte ich aber doch das Problem das wenn die Vorlesung nicht gehört wird aber eine Prüfung statt gefunden hat, dass solch ein Eintrag nicht existieren würde in dieser Abfrage:

select s.name, v.titel, p.note
from uni.hoeren h
join uni.studenten s on h.matrnr = s.matrnr
join uni.vorlesungen v on v.vorlnr = h.vorlnr
left join uni.pruefen p on (p.vorlnr = h.vorlnr and p.matrnr = s.matrnr);

Oder stehe ich schon auf dem Schlauch =D

 

[mihe7]

hätte ich aber doch das Problem das wenn die Vorlesung nicht gehört wird aber eine Prüfung statt gefunden hat

Das wäre ein Problem, wenn danach gefragt worden wäre. Lies Dir mal die Aufgabenstellung genau durch:

"Die SQL Abfrage soll die Namen der Studenten, die eine Vorlesung hören, zusammen mit der Note ausweisen, mit der sie die Prüfung zur jeweiligen Vorlesung ggf. abgeschlossen haben."

Der Hauptsatz ist: "Die SQL Abfrage soll die Namen der Studenten zusammen mit der Note ausweisen". Die Relativsätze enthalten zusätzliche Informationen. Die Namen welcher Studenten sollen ausgewiesen werden? Die Namen der Studenten, die eine Vorlesung hören. Mit welcher Note sollen die Namen ausgewiesen werden? Mit der sie die Prüfung zur jeweiligen Vorlesung ggf. abgeschlossen haben. Das "ggf." impliziert, dass auch solche Studenten ausgewiesen werden sollen, für die keine Prüfungsnote zu der Vorlesung existiert.

Man möchte also die Namen der Studenten, die eine Vorlesung hören und zusätzlich möchte man von diesen die ggf. existierende Prüfungsnote wissen. Wer also aufgrund von Vorerfahrungen (z. B. aus anderen Vorlesungen) die Prüfung ablegt, ohne die betreffende Vorlesung besucht zu haben, soll gerade nicht auftauchen.

 

[ocsme]

Danke für die Ausführliche Erklärung =)
Doch leider bekomme ich immer noch nicht den SQL Befehl dafür zusammen gebaut.

Meine Idee ist das ich die Studenten, hören und Vorlesungen zu einer Menge mache und dann mit dieser Menge left join auf Prüfen so also:

select a.name, a.titel, p.note
from    (select s.name, v.titel, s.matrnr
        from studenten s
        join hoeren h on s.matrnr = h.matrnr
        join vorlesungen v on v.vorlnr = h.vorlnr) a
left join pruefen p on p.matrnr = a.matrnr;

Doch das gibt wieder nur misst 🙁

 

[ocsme]

Wer also aufgrund von Vorerfahrungen (z. B. aus anderen Vorlesungen) die Prüfung ablegt, ohne die betreffende Vorlesung besucht zu haben, soll gerade nicht auftauchen.

select a.name, v.titel, p.note
from    (select s.name, h.vorlnr, s.matrnr
        from studenten s join hoeren h on s.matrnr = h.matrnr) a
join vorlesungen v on v.vorlnr = a.vorlnr
left join pruefen p on  a.matrnr = p.matrnr and a.vorlnr = p.vorlnr;

Es sollen also nur Studenten ausgegeben werden die, die Vorlesung gehört haben und eine Note zu dieser Vorlesung bekommen haben. Wenn der Student die Vorlesung nicht gehört hat, soll er nicht ausgegeben werden.
Stimmt das nun?

 

[mihe7]

Doch das gibt wieder nur misst

Für die Verknüpfung mit Tabelle pruefen wird ein zusammengesetzter Schlüssel benötigt: Matrikelnummer und natürlich auch die Vorlesung 🙂

Abgesehen davon brauchst Du dafür auch keinen Subselect. So sollte es reichen:

select s.name, v.titel, p.note
        from studenten s
        join hoeren h on s.matrnr = h.matrnr
        join vorlesungen v on v.vorlnr = h.vorlnr
        left join pruefen p on p.matrnr = h.matrnr and p.vorlnr = h.vorlnr;

Es sollen also nur Studenten ausgegeben werden die, die Vorlesung gehört haben und eine Note zu dieser Vorlesung bekommen haben. Wenn der Student die Vorlesung nicht gehört hat, soll er nicht ausgegeben werden.
Stimmt das nun?

Ja.

EDIT: x-mal korrigiert 🙂

 

[ocsme]

Vielen Lieben Dank @mihe7.

Leider fehlt es mir immer noch am Verständnis, sprich Aufgaben zu verstehen und diese dann in SQL Ausdrücken zu formulieren 🙁
Da hilft nur am Ball bleiben.

 

[mihe7]

Vielen Lieben Dank @mihe7.

Leider fehlt es mir immer noch am Verständnis, sprich Aufgaben zu verstehen und diese dann in SQL Ausdrücken zu formulieren 🙁
Da hilft nur am Ball bleiben.

Ja, einfach rantasten, irgendwann machts klick - was nicht bedeutet, dass man jede Abfrage mal eben aus dem Ärmel schüttelt.