SQL Abfragen mit Mini Datenbank

O

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

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;
 
O

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.
 
O

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

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.
 
O

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

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.
 
O

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 :(
 
O

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

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 :)
 
Ähnliche Java Themen
  Titel Forum Antworten Datum
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
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
A Attribute werden mit Unterstrich in eine MySQL Datenbank eingetragen Datenbankprogrammierung 10
M wo Datenbank verbinden/trennen? Datenbankprogrammierung 1
H MySQL Verbindung Datenbank und Eclipse (Java) Datenbankprogrammierung 5
DeltaPilot12 Datenbank connect Funktion Datenbankprogrammierung 7
J Oracle Datenbank-Tabelle per Combobox (oder ähnliches) auswählen Datenbankprogrammierung 3
N SQL Datenbank Spalte in Java Array Datenbankprogrammierung 2
J JAR-Datei und Datenbank Datenbankprogrammierung 8
Bluedaishi Datenbank Abfrage Datenbankprogrammierung 36
Kirby.exe Zeile aus der Datenbank holen wenn ein match besteht Datenbankprogrammierung 7
Kirby.exe Es werden keine Einträge in der Datenbank gemacht Datenbankprogrammierung 23
C Datenbank anlegen und über eine Website mit Daten füllen? Datenbankprogrammierung 19
J Welche Kriterien haben Einfluss auf die Geschwindigkeit einer Datenbank ? Datenbankprogrammierung 4
B Frage zu Datenbank Design bei Events (ZenDesk) Datenbankprogrammierung 1
M SQLite Datenbank mit SQLite Datenbankprogrammierung 7
C String in Datenbank einfügen Datenbankprogrammierung 11
C Keinen Zugrift auf Datenbank Datenbankprogrammierung 2
C Datenbank zugreifen Datenbankprogrammierung 10
L Auf Strato Datenbank zugreifen Datenbankprogrammierung 5
H Fehler bei getConnection zu MySQL Datenbank Datenbankprogrammierung 18
G Datenbank Statement Datenbankprogrammierung 22
M Datenbank editierbach machen in JTable Datenbankprogrammierung 13
S Datenbank MySQL und Java Datenbankprogrammierung 8
M H2 Verbindung zur Datenbank komplett schließen Datenbankprogrammierung 11
J Aufbau meiner Datenbank/Tabelle - Verbessern? So lassen? Datenbankprogrammierung 39
J Nur CRUD über Datenbank Klasse, oder auch mehr ? Datenbankprogrammierung 2
Dimax MySQL Maximale Datenlänge für eine Spalte einer Datenbank in java ermitteln Datenbankprogrammierung 15
L Appabsturz mit Datenbank Datenbankprogrammierung 4
J Zahlungseingänge von mehreren Kunden wie am besten abbilden in der Datenbank ? Datenbankprogrammierung 8
L Datenbank sichern Datenbankprogrammierung 8
S Daten von SQLite Datenbank nutzen Datenbankprogrammierung 5
J Datenbank VPS Server Datenbankprogrammierung 3
H MySQL MySQL - Keine Verbindung zur Datenbank?! Datenbankprogrammierung 4
NIckbrick MySQL Befehle aus Datenbank auslesen Datenbankprogrammierung 21
S Datenbank/Java true/false Datenbankprogrammierung 8
J JUNIT und CRUD-Datenbank Datenbankprogrammierung 4

Ähnliche Java Themen

Anzeige


Oben