Blatt 5 - DBS (2024-25)

Möbel-Verkauf-Datenbank

Gegeben seien die Relationen einer Möbel-Verkauf-Datenbank:

$$\begin{array}{rl}\text{Kunde}& (\underline{\text{kund\_nr}},\text{kund\_name},\text{adresse},\text{ort},\text{plz})\\ \text{Personal}& (\underline{\text{pers\_nr}},\text{nachname},\text{vorname},\text{einsatz},\text{vorgesetzt},\text{gehalt})\\ \text{Verkauf}& (\underline{\text{auftr\_nr}},\text{bestelldatum},\text{pers\_nr},\text{kund\_nr})\\ \text{Inventar}& (\underline{\text{art\_nr}},\text{art\_bez},\text{lagerbest},\underline{\text{lagerort}},\text{preis})\\ \text{Auftragsposten}& (\underline{\text{auftr\_nr}},\underline{\text{art\_nr}},\text{menge})\end{array}$$

---

Aufgabe 5-1: Anfragen in SQL

Formulieren Sie die folgenden Anfragen in der Datenbanksprache SQL. Beachten Sie, dass die Teilaufgaben a) und b) jeweils einmal mit Hilfe von Join-Operationen und einmal nur mittels Unterabfragen ohne Join oder Kreuzprodukt formuliert werden sollen. Duplikate sind zu vermeiden.

a) Finden Sie die Nummern und Bezeichnungen aller Artikel, deren Preis entweder dem Gehalt von Roswita Hartinger oder Margot Winter entspricht.

Join-Version

SELECT DISTINCT Inventar.art_nr, Inventar.art_bez
FROM Inventar
JOIN Personal ON Inventar.preis = Personal.gehalt
WHERE (Personal.vorname = 'Margot' AND Personal.nachname = 'Winter')
   OR (Personal.vorname = 'Roswita' AND Personal.nachname = 'Hartinger');

Erklärung:

• JOIN wird verwendet, um die Inventar-Tabelle direkt mit der Personal-Tabelle zu verbinden, basierend auf der Bedingung, dass der preis des Artikels dem gehalt des Mitarbeiters entspricht.
• WHERE filtert die Mitarbeiter auf Margot Winter und Roswita Hartinger.
• DISTINCT stellt sicher, dass keine Duplikate in den Ergebnissen enthalten sind.

Unterabfrage-Version

SELECT DISTINCT art_bez, art_nr
FROM Inventar
WHERE preis IN (
    SELECT gehalt
    FROM Personal
    WHERE (vorname = 'Margot' AND nachname = 'Winter')
       OR (vorname = 'Roswita' AND nachname = 'Hartinger')
);

Erklärung:

• Die Hauptabfrage selektiert die art_bez und art_nr aus der Inventar-Tabelle.
• Die Unterabfrage wählt die gehalt-Werte der spezifischen Mitarbeiter.
• IN überprüft, ob der preis eines Artikels in den Gehältern der ausgewählten Mitarbeiter enthalten ist.
• DISTINCT vermeidet doppelte Einträge.

b) Geben Sie alle Kundennamen an, die am 24.07.2023 etwas von einem Mitarbeiter mit dem Einsatzort Hamburg gekauft haben.

Join-Version

SELECT DISTINCT Kunde.kund_name
FROM Kunde
JOIN Verkauf ON Kunde.kund_nr = Verkauf.kund_nr
JOIN Personal ON Verkauf.pers_nr = Personal.pers_nr
WHERE Personal.einsatz = 'Hamburg'
  AND Verkauf.bestelldatum = '2023-07-24';

Erklärung:

• JOIN verbindet Kunde mit Verkauf über kund_nr und anschließend mit Personal über pers_nr.
• WHERE filtert die Ergebnisse auf Verkäufe, die am 24.07.2023 von Mitarbeitern mit dem Einsatzort Hamburg getätigt wurden.
• DISTINCT sorgt dafür, dass jeder Kundenname nur einmal angezeigt wird.

Unterabfrage-Version

SELECT DISTINCT kund_name
FROM Kunde
WHERE kund_nr IN (
    SELECT kund_nr
    FROM Verkauf
    WHERE bestelldatum = '2023-07-24'
      AND pers_nr IN (
          SELECT pers_nr
          FROM Personal
          WHERE einsatz = 'Hamburg'
      )
);

Erklärung:

• Die Hauptabfrage wählt die kund_name aus der Kunde-Tabelle.
• Die erste Unterabfrage selektiert kund_nr aus Verkauf, die am 24.07.2023 getätigt wurden.
• Die zweite Unterabfrage innerhalb der ersten Unterabfrage filtert pers_nr basierend auf dem Einsatzort Hamburg.
• DISTINCT vermeidet doppelte Kundennamen in den Ergebnissen.

c) Erstellen Sie eine Liste aller Mitarbeiter (Vorname, Nachname, Gehalt), sortiert absteigend nach Gehalt. Bei gleichem Gehalt wird alphabetisch aufsteigend nach Nachname und dann nach Vorname sortiert.

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

Erklärung:

• SELECT wählt die gewünschten Spalten aus der Personal-Tabelle.
• ORDER BY sortiert die Ergebnisse zuerst nach gehalt in absteigender Reihenfolge.
• Bei gleichen Gehältern werden die Ergebnisse nach nachname und dann nach vorname in aufsteigender Reihenfolge sortiert.

d) Bestimmen Sie die Artikelnummern, Bezeichnungen und Preise des Inventars, die den niedrigsten Preis aufweisen (es gibt keinen Artikel mit einem niedrigeren Preis).

SELECT art_nr, art_bez, preis
FROM Inventar
WHERE preis = (SELECT MIN(preis) FROM Inventar);

Erklärung:

• SELECT wählt die art_nr, art_bez und preis aus der Inventar-Tabelle.
• Die Unterabfrage (SELECT MIN(preis) FROM Inventar) ermittelt den niedrigsten Preis in der Tabelle.
• WHERE stellt sicher, dass nur Artikel mit diesem minimalen Preis ausgewählt werden.

e) Finden Sie die Artikelnummern, die von mindestens zwei unterschiedlichen Kunden mit Wohnsitz in Stuttgart gekauft wurden.

Join-Version

SELECT ap.art_nr
FROM Verkauf v
JOIN Auftragsposten ap ON v.auftr_nr = ap.auftr_nr
JOIN Kunde k ON v.kund_nr = k.kund_nr
WHERE k.ort = 'Stuttgart'
GROUP BY ap.art_nr
HAVING COUNT(DISTINCT v.kund_nr) >= 2;

Erklärung:

• JOIN verbindet Verkauf mit Auftragsposten über auftr_nr und anschließend mit Kunde über kund_nr.
• WHERE filtert die Kunden auf den Wohnort Stuttgart.
• GROUP BY gruppiert die Ergebnisse nach art_nr.
• HAVING stellt sicher, dass mindestens zwei unterschiedliche Kunden (COUNT(DISTINCT v.kund_nr) >= 2) die Artikel gekauft haben.

Unterabfrage-Version

SELECT art_nr
FROM Auftragsposten
WHERE auftr_nr IN (
    SELECT auftr_nr
    FROM Verkauf
    WHERE kund_nr IN (
        SELECT kund_nr
        FROM Kunde
        WHERE ort = 'Stuttgart'
    )
)
GROUP BY art_nr
HAVING COUNT(DISTINCT kund_nr) >= 2;

Erklärung:

• Die Hauptabfrage wählt art_nr aus Auftragsposten.
• Die erste Unterabfrage selektiert auftr_nr aus Verkauf, die mit Kunden aus Stuttgart verbunden sind.
• Die zweite Unterabfrage innerhalb der ersten Unterabfrage filtert kund_nr basierend auf dem Wohnort Stuttgart.
• GROUP BY gruppiert die Ergebnisse nach art_nr.
• HAVING stellt sicher, dass mindestens zwei unterschiedliche Kunden die Artikel gekauft haben.

---

Aufgabe 5-2: Weitere SQL-Anfragen

Formulieren Sie die folgenden Anfragen in der Datenbanksprache SQL.

a) Finden Sie die Nummern und Namen aller Kunden, die noch nie etwas gekauft haben.

SELECT kund_nr, kund_name
FROM Kunde
WHERE NOT EXISTS (
    SELECT 1
    FROM Verkauf
    WHERE Verkauf.kund_nr = Kunde.kund_nr
);

Erklärung:

• SELECT wählt die kund_nr und kund_name aus der Kunde-Tabelle.
• WHERE NOT EXISTS überprüft, ob es keine Einträge in der Verkauf-Tabelle gibt, die mit dem aktuellen kund_nr übereinstimmen.
• Dies stellt sicher, dass nur Kunden angezeigt werden, die noch keine Käufe getätigt haben.

b) Finden Sie die Nummern und Nachnamen aller Angestellten, die allen Kunden mit Wohnsitz Landshut bereits etwas verkauft haben.

SELECT P.pers_nr, P.nachname
FROM Personal P
WHERE NOT EXISTS (
    SELECT 1
    FROM Kunde K
    WHERE K.ort = 'Landshut'
      AND NOT EXISTS (
          SELECT 1
          FROM Verkauf V
          WHERE V.pers_nr = P.pers_nr
            AND V.kund_nr = K.kund_nr
      )
);

Erklärung:

• SELECT wählt die pers_nr und nachname aus der Personal-Tabelle.
• WHERE NOT EXISTS stellt sicher, dass es keinen Kunden aus Landshut gibt, für den der Mitarbeiter keinen Verkauf getätigt hat.
• Die erste Unterabfrage selektiert alle Kunden aus Landshut.
• Die zweite Unterabfrage innerhalb der ersten prüft, ob der aktuelle Mitarbeiter (P.pers_nr) einen Verkauf an diesen Kunden (K.kund_nr) getätigt hat.
• Wenn für alle Kunden aus Landshut ein Verkauf existiert, wird der Mitarbeiter in die Ergebnisse aufgenommen.

---