SQL Abfragen mit Mini Datenbank

ocsme

Top Contributor
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:
SQL:
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:
SQL:
--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

Top Contributor
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.

SQL:
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:

SQL:
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:

SQL:
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;
 

ocsme

Top Contributor
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

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

SQL:
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.
SQL:
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

Top Contributor
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

Top Contributor
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:

SQL:
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

Top Contributor
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

Top Contributor
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:
SQL:
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

Top Contributor
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.
SQL:
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

Top Contributor
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:

SQL:
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

Top Contributor
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.
 
Ähnliche Java Themen
  Titel Forum Antworten Datum
N Datenbank abfragen nach bestimmten Wort Datenbankprogrammierung 7
J Datenbank abfragen Datenbankprogrammierung 6
D Daten aus der Firebase-Echtzeitdatenbank abfragen und in Variablen speichern (JAVA) Datenbankprogrammierung 0
M Sql Abfragen Datenbankprogrammierung 1
D 2 Abfragen zusammenfassen Datenbankprogrammierung 7
D MySQL Geburtsdatum per KW abfragen Datenbankprogrammierung 1
W Daten in Java intern abfragen Datenbankprogrammierung 1
Thallius MySQL Tabelle splitten aber wie eine abfragen? Datenbankprogrammierung 10
D geänderte SELECT Abfragen Datenbankprogrammierung 15
D foreignkey abfragen und speichern Datenbankprogrammierung 74
K Viele verschiedenen SQL Abfragen Datenbankprogrammierung 2
J SQLite ResultSet closed -- Wiederholende DB-Abfragen Datenbankprogrammierung 6
R JPA dynamisch auf NULL abfragen Datenbankprogrammierung 2
A MySQL Tabelle ID abfragen und Spalte ausgeben Datenbankprogrammierung 4
K HSQLDB Einzelne Tabellen abfragen? Datenbankprogrammierung 4
F myBatis und komplexe Abfragen Datenbankprogrammierung 3
M MySQL Spaltennamen abfragen Datenbankprogrammierung 2
B Abfragen fehlgeschlagen Datenbankprogrammierung 6
D MySQL Applet MYSQL Abfragen Sicherheit Datenbankprogrammierung 2
E Datenbank Abfragen in eine GUI schreiben Datenbankprogrammierung 8
I Tabelle mit "|" getrennten Werten -> einzelnen Wert davon abfragen Datenbankprogrammierung 15
N Daten aus Datenbank abfragen und anzeigen in der Praxis?? Datenbankprogrammierung 2
S MySQL Server Informationen abfragen Datenbankprogrammierung 6
turmaline [Hibernate] @OneToMany - Eine referenzierte Tabelle abfragen Datenbankprogrammierung 11
Burny91 SQLite ResultSetMetaData abfragen Datenbankprogrammierung 6
T Kleine Tabellen für schnellere abfragen? Datenbankprogrammierung 3
H Derby SQL-Abfragen in Datei abspeichern Datenbankprogrammierung 2
trash SQL, ganze Tabelle abfragen Datenbankprogrammierung 3
E MySQL 2 Fragen zur Syntax von SQL-Abfragen Datenbankprogrammierung 8
brainray Bei MySQL alle Datenbanken eines Servers abfragen Datenbankprogrammierung 3
N mehrere Abfragen nacheinander wie mit MS Access mit JAVA Datenbankprogrammierung 3
MQue größte id in einer Tabelle abfragen Datenbankprogrammierung 4
A Leere Zelle von einer DB abfragen Datenbankprogrammierung 3
S Abfragen auf Vorhanden Datenbankprogrammierung 23
G Mehrere Tabellen abfragen Datenbankprogrammierung 7
G SQL Abfragen hardcoded im Quelltext? Datenbankprogrammierung 15
G Prolog abfragen mit Java Datenbankprogrammierung 3
M H2 DAtenbank in .sql datei schreiben/abfragen ? Datenbankprogrammierung 3
A Abfragen, wer zur Zeit angemeldet ist Datenbankprogrammierung 3
S Datentypen einer Tabelle Abfragen? Datenbankprogrammierung 2
G Rechte abfragen Datenbankprogrammierung 8
L MySQL : Funktionsweise von '%' in Abfragen Datenbankprogrammierung 2
G INSERT abfragen ob erfolgreich? Datenbankprogrammierung 5
B Eingefügter Datensatz erneut abfragen. Datenbankprogrammierung 10
E Datentyp numeric abfragen? Datenbankprogrammierung 2
P Daten aus 2 Tabellen mit java abfragen Datenbankprogrammierung 9
F die DN eines LDAP verzeichnisses abfragen Datenbankprogrammierung 2
M Abfragen auf ResultSets Datenbankprogrammierung 3
E Einzelne SQL Abfragen, oder alles zusammen? (veraendert!!!) Datenbankprogrammierung 11
A Viele Abfragen auf einmal: Performance Datenbankprogrammierung 2
Z Zeilen-Anzahl abfragen bei Datenbank Datenbankprogrammierung 4
W RsultSet auf null abfragen funktioniert nicht Datenbankprogrammierung 4
isowiz SELECT SUM() Ergebnis in Java abfragen Datenbankprogrammierung 6
S MySQL Abfragen blockiern den Arbeitsspeicher Datenbankprogrammierung 6
G Anzahl der Datensaetze abfragen Datenbankprogrammierung 11
V Anzahl der Datensätze abfragen Datenbankprogrammierung 9
S Bereiche einer Datenbank abfragen Datenbankprogrammierung 3
N Mehrere Abfragen gleichzeitig - was passiert da? Datenbankprogrammierung 6
D Reihenfolge der Abfragen Datenbankprogrammierung 7
L mehrere Abfragen mit einem Statement!? Datenbankprogrammierung 5
G Select-Abfragen sind sehr langsam Datenbankprogrammierung 7
torresbig MySQL hibernate - could not resolve entity class ... (Datenbank Anfänger) Datenbankprogrammierung 19
M Meine Datenbank lässt sich mit meiner Methode nicht ändern Datenbankprogrammierung 1
I SaaS Applikation: pro Kunde eine Datenbank / Schema oder eine DB für alle Kunden? Datenbankprogrammierung 76
T SQL-Statement Datenbank nach SQL Statement schließen? Datenbankprogrammierung 7
M Datenbank Zugraff nach Umwandlung in .jar-Datei nicht mehr möglich Datenbankprogrammierung 4
Auf MySql Datenbank zugreifen funktioniert nicht Datenbankprogrammierung 8
MongoDB-Datenbank in Androidstudio einbinden Datenbankprogrammierung 1
thor_norsk Datenbank: Apache Derby Datenbankprogrammierung 6
B SQlite Datenbank, trotz Statements wurden nicht alle Zeilen erzeugt? Datenbankprogrammierung 35
M Datenbank mit REST API Datenbankprogrammierung 66
M Entfernte Datenbank Datenbankprogrammierung 11
T Datenbank: Tabelle erstellen bei Web-Applikation Datenbankprogrammierung 4
D SQLite Datenbank in Android Studio (Java) durchsuchen Datenbankprogrammierung 3
M MySQL Datenbank in Array Datenbankprogrammierung 2
S Den letzten Eintrag aus Datenbank ziehen (Oracle SQL Dev.) Datenbankprogrammierung 14
Beginner22 Zugriff auf Datenbank aus Java über mariadb Datenbankprogrammierung 3
ma095 value NULL- Datenbank Postgresql Spring - intellij community Datenbankprogrammierung 0
raptorrs Plötzlich keine Verbindung zur Datenbank mehr möglich Datenbankprogrammierung 14
Davee SQLite SQLite Datenbank lässt sich nicht auf anderen PCs öffnen Datenbankprogrammierung 8
P Datenbank Tool - besser als oracle SQL Developer gesucht mit effizinte Verbindungsverwaltung Datenbankprogrammierung 2
X JPA (EclipseLink) und Oracle Datenbank Datenbankprogrammierung 2
T Datenbank auf einer Webseite aus einer Exceltabelle erstellen Datenbankprogrammierung 5
L SQL Datenbank Datenbankprogrammierung 7
L SQL Datenbank INSERT INTO Datenbankprogrammierung 6
L SQL Datenbank Tabelle insert Datenbankprogrammierung 7
L SQL Datenbank Tabelle erstellen Datenbankprogrammierung 6
nonickatall MySQL Auf neue Datensätze in einer Datenbank reagieren Datenbankprogrammierung 5
W MYSQL Datenbank Login Android Datenbankprogrammierung 3
anton1 Online Datenbank Datenbankprogrammierung 7
krgewb H2-Datenbank öffnen Datenbankprogrammierung 8
P Verbindung zu einer Access Datenbank per Eclipse oder Intellij Datenbankprogrammierung 0
Z Datenbank Choicebox wird nicht befüllt Datenbankprogrammierung 15
S Suche In SQL Datenbank mit array Datenbankprogrammierung 6
P Enum in der Datenbank Datenbankprogrammierung 1
Z SQL- Datenbank 1.PK zu 2.FK Datenbankprogrammierung 3
E netbeans - jsp Daten in Datenbank hinzufügen Datenbankprogrammierung 2
bueseb84 Spring Boot : Update Mysql Datenbank Datenbankprogrammierung 1
Avalon Attribute werden mit Unterstrich in eine MySQL Datenbank eingetragen Datenbankprogrammierung 10
MiMa wo Datenbank verbinden/trennen? Datenbankprogrammierung 1

Ähnliche Java Themen

Neue Themen


Oben