Blatt 6 - DBS (2024-25)

Aufgabe 6-1 Anfragen in SQL Aggregatfunktionen in SQL

Info

(!!!) steht für Teilaufgaben mit hohem Schwierigkeitsgrad

Gegeben seien die Relationen Professoren, Personal, Studenten, Vorlesungen, Voraussetzungen, hoeren und Pruefungen also Datenmodell für eine Universitätsdatenbank:

$$\begin{array}{rl}\text{Professoren}& (\underline{\text{persNr}},\text{name, raum})\\ \text{Studenten}& (\underline{\text{matrNr}},\text{name, semester})\\ \text{Vorlesungen}& (\underline{\text{vorlNr}},\text{titel, ects, gelesenVon[Professoren]})\\ \text{Voraussetzungen}& (\underline{\text{vorausgesetzt}[Vorlesungen]},\underline{\text{vorlesung}[Vorlesungen]})\\ \text{hoeren}& (\underline{\text{matrNr}[Studenten]},\underline{\text{vorlNr}[Vorlesungen]})\\ \text{Pruefungen}& (\underline{\text{matrNr}[Studenten]},\underline{\text{vorlNr}[Vorlesungen]},\text{note})\end{array}$$

Formulieren Sie folgende Anfragen für das obige Datenbankschema in der Datenbanksprache SQL.

Hinweis: Am Ende des Übungsblattes finden Sie zum Verständnis eine passende Beispielausprägung des Relationenschemas. Achten Sie darauf, dass Ihre Anfrage für jede mögliche Ausprägung der Datenbank das richtige Ergebnis liefert; nicht nur für die gegebene Beispielausprägung. Darüber hinaus finden Sie eine Beispielausprägung auch in der SQL-Schnittstelle.

a) Bestimmen Sie für jede Vorlesung (Anzuzeigen: Nummer und Titel ) die Durchschnittsnote (gerundet auf drei Stellen) über alle in dem Fach stattgefundenen Prüfungen.

Runden: Zum Runden können Sie die Funktion ROUND benutzen. Beispielsweise wird das Ergebnis der Berechnung in folgender Anfrage auf drei Nachkommastellen gerundet:

SELECT ROUND(menge*gewicht / 1000,3) FROM LTP NATURAL JOIN T

SELECT V.vorlNr, V.titel, ROUND(AVG(note)) AS "average_grade"
FROM Vorlesungen V
JOIN Pruefungen P ON V.vorlNr = P.vorlNr
GROUP BY V.vorlNr, V.titel;

b) Bestimmen Sie Personalnummer und Namen eines jeden Professors, zusammen mit der Anzahl der unterschiedlichen Vorlesungen, die er hält.

Professoren ohne Vorlesung sollen mit einer Vorlesungsanzahl von 0 angegeben werden. Sortieren Sie das Ergebnis absteigend nach der Anzahl der Vorlesungen.

SELECT  persNr, name, COUNT(vorlNr) as "VorlCount" FROM Professoren P
LEFT JOIN Vorlesungen V ON P.persNr=V.gelesenVon
GROUP BY (persNr)
ORDER BY (VorlCount) DESC

!!! c) Bestimmen Sie für jeden Studenten (Anzuzeigen: Matrikelnummer und Name), wieviele andere Studenten aus einem höheren Semester dieser kennt.

(d.h. beide besuchen mindestens eine Vorlesung gemeinsam). Zeigen Sie nur Studenten an, die mehr als zwei andere Studenten aus einem höheren Semester kennen.

SELECT S1.matrNr, S1.name, COUNT(DISTINCT S2.matrNr) AS anzahl_hoehere_studenten
FROM Studenten S1
JOIN hoeren H1 ON S1.matrNr = H1.matrNr
JOIN hoeren H2 ON H1.vorlNr = H2.vorlNr
JOIN Studenten S2 ON H2.matrNr = S2.matrNr
WHERE S1.semester < S2.semester
GROUP BY S1.matrNr, S1.name
HAVING COUNT(DISTINCT S2.matrNr) > 2;

Erklärung der SQL-Abfrage

Diese Abfrage ermittelt für jeden Studenten (S1), wie viele andere Studenten aus höheren Semestern (S2) er kennt. Ein Student wird als “bekannt” betrachtet, wenn beide mindestens eine Vorlesung gemeinsam besuchen.

• Tabellenverknüpfung: Die Tabelle hoeren verknüpft Studenten mit den von ihnen belegten Vorlesungen. Mit JOIN wird geprüft, welche Vorlesungen zwei Studenten (S1 und S2) gemeinsam hören.
• Semesterbedingung: Die Bedingung S1.semester < S2.semester filtert die Ergebnisse, sodass nur Studenten gezählt werden, die sich in einem höheren Semester befinden als S1. Dies stellt sicher, dass ausschließlich Beziehungen zu älteren Semestern betrachtet werden.
• Gruppierung und Zählung: Mithilfe von GROUP BY S1.matrNr, S1.name werden die Ergebnisse nach den Matrikelnummern und Namen der Studenten (S1) gruppiert. Anschließend wird mit COUNT(DISTINCT S2.matrNr) ermittelt, wie viele eindeutige Matrikelnummern der Studenten aus höheren Semestern (S2) mit jedem Studenten verknüpft sind.
• Filterung: Die Bedingung HAVING COUNT(DISTINCT S2.matrNr) > 2 sorgt dafür, dass nur Studenten berücksichtigt werden, die mehr als zwei andere aus höheren Semestern kennen.

Ergebnis: Die Abfrage liefert eine Liste mit den Matrikelnummern und Namen der Studenten, sowie der Anzahl der Studenten aus höheren Semestern, die sie kennen. Nur diejenigen Studenten werden angezeigt, die mehr als zwei solche Verbindungen haben.

d) Bestimmen Sie die Professoren (Anzuzeigen: Personalnummer und Name), die für die mindestens zwei Studenten mindestens drei Vorlesungen des jeweiligen Professors hören.

Tipp: Erstellen Sie passende Views, um die Anfrage übersichtlicher zu gestalten. Benutzen Sie hierzu die Syntax aus der Vorlesung!

CREATE VIEW AZ AS
(
SELECT matrNr, gelesenVon, COUNT(*) as anz
FROM Vorlesungen NATURAL JOIN hoeren
GROUP BY matrNr, gelesenVon
HAVING COUNT(*) >= 3
)

SELECT P.persNr, P.name
FROM Professoren P
JOIN AZ ON P.persNr = AZ.gelesenVon
GROUP BY P.persNr, P.name
HAVING COUNT(*) >= 2;

Erklärung

• CREATE VIEW AZ: Erstellt eine temporäre Tabelle zur Vereinfachung komplexer Abfragen.

• SELECT matrNr, gelesenVon, COUNT(*) as anz: Wählt Studenten-ID, Professor-ID und zählt Vorlesungen.

• FROM Vorlesungen NATURAL JOIN hoeren: Verbindet Vorlesungen mit gehörten Vorlesungen auf Basis gleicher Spalten.

• GROUP BY matrNr, gelesenVon: Gruppiert Ergebnisse nach Student und Professor.

• HAVING COUNT(*) >= 3: Berücksichtigt nur Gruppen mit mindestens drei Vorlesungen.

• SELECT persNr, name: Wählt Professoren-ID und -Namen.

• FROM Professoren JOIN AZ ON persNr = gelesenVon: Verknüpft Professoren mit dem View, basierend auf ID.

• GROUP BY persNr, name: Gruppiert nach Professor.

• HAVING COUNT(*) >= 2: Filtert Professoren mit mindestens zwei zugehörigen Studenten.

e) Bestimmen Sie für jede Vorlesung (Anzuzeigen: Nummer und Titel), welche mindestens eine andere Vorlesung voraussetzt, die prozentualen Anteile (gerundet auf zwei Nachkommastellen) der teilnehmenden Studenten pro Semester.

Dabei interessieren uns nur Vorlesungen, die von Studenten aus unterschiedlichen Semestern gehört werden. Andere Vorlesungen sollen nicht in der Ergebnistabelle auftauchen.

Ein Beispiel: Die Vorlesung Softwaretechnik setzt eine andere Vorlesung voraus und wird von insgesamt 6 Studenten gehört. Davon stammt ein Student aus dem fünften Semester. Also macht dieser Fünftsemester ein sechstel aller Hörer aus. Das entsprechende Ergebnisbeispiel ist somit $(6,\text{’Softwaretechnik’},5,16.67)$.

Das Ergebnis soll sortiert werden:

• Alphabetisch nach dem Vorlesungstitel
• Absteigend nach dem prozentualen Anteil
• Bei gleichem Anteil aufsteigend nach dem Semester.

Tipp: Erstellen Sie passende Views.

CREATE VIEW Vorlesung_mit_Voraussetzung AS
(
SELECT vorlNr, titel
FROM Vorlesungen
WHERE vorlNr IN (
SELECT vorlesung
FROM Voraussetzungen)
)
 

CREATE VIEW Anzahl_Gesamt AS
(
SELECT vorlNr, COUNT(*) AS anz_ges
FROM hoeren
GROUP BY vorlNr
)

CREATE VIEW Anzahl_Semester AS
(
SELECT vorlNr, semester, COUNT(*) AS anz_sem
FROM hoeren NATURAL JOIN Studenten
GROUP BY vorlNr, semester
)

SELECT V.vorlNr, V.titel, A.semester, ROUND((A.anz_sem*100.0/ G.anz_ges)) AS Prozent
FROM Anzahl_Semester A
NATURAL JOIN Anzahl_Gesamt G
NATURAL JOIN Vorlesung_mit_Voraussetzung V
WHERE A.anz_sem < G.anz_ges
ORDER BY V.titel ASC, Prozent DESC, A.semester ASC;

Erklärung der SQL-Abfrage Die Aufgabe wird durch einen schrittweisen Ansatz mit Views (virtuellen Tabellen) gelöst, um die SQL-Abfrage zu vereinfachen:

• View Vorlesung_mit_Voraussetzung: Enthält alle Vorlesungen mit mindestens einer Voraussetzung, basierend auf den Tabellen Vorlesungen und Voraussetzungen.
• View Anzahl_Gesamt: Berechnet für jede Vorlesung die Gesamtanzahl der teilnehmenden Studenten durch Gruppierung der Tabelle hoeren.
• View Anzahl_Semester: Zählt die Studenten pro Semester je Vorlesung mithilfe eines NATURAL JOIN zwischen hoeren und Studenten.
• Finale Anfrage: Verknüpft die Views, um prozentuale Anteile der Teilnehmer je Semester zu berechnen, sortiert die Ergebnisse nach Titel, Anteil (absteigend) und Semester (aufsteigend), und filtert mit anz_sem < anz_ges nur Vorlesungen, die von Studenten aus unterschiedlichen Semestern besucht werden.

Ergebnis: Durch diese Schritte wird die Komplexität reduziert, und das gewünschte Ergebnis effizient ermittelt.

Aufgabe 6-2 Anfragen in SQL

Diese Aufgabe nimmt Bezug auf das aus früheren Übungsblättern bekannte Möbel-Datenbankschema. Formulieren Sie die folgenden Anfragen in SQL. Auf der Vorlesungswebseite steht auch eine SQL-Schnittstelle für dieses Schema zur Verfügung.

a) Finden Sie die Artikelnummer, die Artikelbezeichnung, den Lagerort und den Lagerbestand aller in Hamburg oder München gelagerten Artikel.

SELECT art_nr, art_bez, lagerort, lagerbest FROM Inventar
WHERE lagerort = 'Hamburg' OR lagerort = 'Muenchen'

ODER

SELECT art_nr, art_bez, lagerort, lagerbest FROM Inventar
WHERE lagerort IN (“Hamburg”, “Muenchen”)

b) Finden Sie für alle in der Tabelle Auftragsposten gespeicherten Bestellungen des Artikels mit der Nummer 203333 die Auftragsnummer, die Artikelnummer, die bestellte Menge und alle Lagerbestände und Lagerorte, an denen eine ausreichende Stückzahl des Artikels 203333 vorhanden ist.

SELECT auftr_nr,art_nr,menge,lagerbest,lagerort FROM Auftragsposten
NATURAL JOIN Inventar
WHERE art_nr = 203333 AND lagerbest >= menge

c) Finden Sie die Nummern derjenigen Kunden, die keinen Auftrag erteilt haben.

SELECT K.kund_nr FROM Kunde K
LEFT JOIN Verkauf V ON K.kund_nr = V.kund_nr
WHERE auftr_nr IS NULL

ODER

SELECT kund_nr FROM Kunde
WHERE kund_nr NOT IN (SELECT kund_nr FROM Verkauf)

d) Gesucht sind die verschiedenen Lagerorte von Artikeln in der Tabelle Inventar, die einen Lagerbestand von mindestens 8 Exemplaren haben.

SELECT DISTINCT lagerort FROM Inventar
WHERE lagerbest >= 8

e) Finden Sie den Vornamen und den Nachnamen derjenigen Angestellten, die einen oder mehrere Verkäufe an Kunden aus Stuttgart bearbeitet haben.

SELECT vorname,nachname FROM Personal P
JOIN Verkauf V ON V.pers_nr = P.pers_nr
JOIN Kunde K ON K.kund_nr = V.kund_nr
WHERE K.ort = 'Stuttgart'

f) Finden Sie den Nachnamen, Vornamen, Einsatzort und das Gehalt aller in der Tabelle Personal gespeicherten Angestellten.

Das Ergebnis soll lexikographisch nach dem Einsatzort sortiert werden. Bei gleichem Einsatzort soll in absteigender Reihenfolge nach dem Gehalt sortiert werden.

SELECT nachname,vorname,einsatz ,gehalt FROM Personal
ORDER BY einsatz ASC, gehalt DESC

g) Finden Sie die Personalnummern und das Gehalt der Angestellten mit minimalem oder maximalem Gehalt.

SELECT pers_nr,gehalt FROM Personal
WHERE gehalt = (SELECT MIN(gehalt) FROM Personal) OR gehalt = (SELECT MAX(gehalt) FROM Personal);

h) Finden Sie für jeden Einsatzort die Anzahl der dort eingesetzten Angestellten.

SELECT einsatz, COUNT(pers_nr) FROM Personal
GROUP BY einsatz

i) Wie hoch ist die durchschnittliche Anzahl Angestellter über alle Einsatzorte?

SELECT AVG(anzahl) FROM (
  SELECT COUNT(*) AS anzahl FROM Personal
  GROUP BY einsatz
) AS temp

j) Finden Sie für jeden Artikel die Artikelnummer und die Summe der Lagerbestände in den Lagerorten, die den Artikel führen.

Es sollen nur solche Artikel ausgegeben werden, die einen Gesamtbestand von mehr als 10 Stück haben.

SELECT art_nr,art_bez,SUM(lagerbest) as bestand FROM Inventar
GROUP BY art_nr,art_bez
HAVING bestand >10

---