Vorlesung

22.11

Anzahl aller Artikel per Aggregatfunktion bestimmen
```
SELECT COUNT(*) 
FROM artikel;
```
Anzahl aller Artikel per Aggregatfunktion bestimmen und Aliasnamen vergeben
```
SELECT COUNT(*) as "Anzahl Artikel" 
FROM artikel;
```

Anzahl aller Kunden und Email-Adressen bestimmen

SELECT count(*) as Anzahl, 
    count(email) as Email 
FROM kunden;

Aggregatfunktionen für alphabetisch kleinsten und größten Ort
```
SELECT max(Ort), 
    min(Ort) 
FROM kunden;
```
Anzeige nur von bestimmten Spalten und Bedingungen (Nur Kunden aus Frankreich)
```
SELECT Firma, Ort, Land 
FROM kunden 
WHERE Land = "frankreich";
```

Verknüpfung von Bedingungen (Alle Kunden aus Deutschland und Frankreich)

SELECT Firma, Ort, Land 
FROM kunden 
WHERE Land = "frankreich" 
    OR Land = "deutschland";

Verknüpfung von Bedingungen (Alle Kunden aus Münster und Berlin)

SELECT Firma, Ort, Land 
FROM kunden 
WHERE Ort IN ("münster", "berlin");

Sortieren nach 1. Land absteigend und 2. nach Firma aufsteigend (default)

SELECT Firma, Ort, Land
FROM kunden 
WHERE Land IN ("Frankreich", "deutschland", "usa") 
ORDER BY Land DESC, Firma;

alternativ mit Angabe der Spaltennummer statt Spaltennamen

SELECT Firma, Ort, Land
FROM kunden 
WHERE Land IN ("Frankreich", "deutschland", "usa") 
ORDER BY 3 DESC, 1;

23.11

Alle Spalten anzeigen, sortieren nach Einzelpreis

SELECT * 
FROM artikel 
ORDER BY Einzelpreis;

absteigende Sortierung:

SELECT * 
FROM artikel 
ORDER BY Einzelpreis DESC;

Die 5 teuersten Artikel anzeigen

SELECT * 
FROM artikel 
ORDER BY Einzelpreis DESC 
LIMIT 5;

Alle Artikel, deren Einzelpreis über 20€ liegt, absteigend sortieren

SELECT * FROM artikel 
WHERE Einzelpreis > 20
ORDER BY Einzelpreis DESC;

Alle Artikel, deren Einzelpreis zwischen 20€ und 50€ liegt, absteigend sortieren

SELECT Artikelname, Einzelpreis
FROM artikel 
WHERE Einzelpreis >= 20
    AND Einzelpreis <= 50
ORDER by Einzelpreis DESC;

alternativ:

SELECT Artikelname, Einzelpreis
FROM artikel 
WHERE Einzelpreis BETWEEN 20 AND 50
ORDER by Einzelpreis DESC;

Summe aller Kunden aus USA, für die auch eine Email hinterlegt ist

SELECT COUNT(*) "Kunden aus USA", 
    COUNT(email) "mit Mail" 
FROM kunden 
WHERE Land = "USA";

Alle Artikel (Tabelle artikel) mit den Lieferern (Tabelle lieferanten) mit Equi-JOIN anzeigen

SELECT Artikelname, Einzelpreis, Lagerbestand, Firma
FROM artikel, lieferanten
WHERE LieferantenID = lieferantenNr;

Ausgabe aller Firmen (kunden), die “Milchprodukte” (artikelkategorien) verkaufen

SELECT Firma, Artikelname, Kategoriename
FROM artikelkategorien, artikel, bestellungendetails, bestellungen, kunden
WHERE KategorieId = KategorieNr
    AND ArtikelId = ArtikelNr
    AND Bestellnr = BestellId
    AND KundenNr = KundenId
    AND Kategoriename LIKE "%milch%"
ORDER BY 1;

Ausgabe aller verkauften Artikel für "Blauer See Delikatessen" (kunden)

SELECT Artikelname, Firma
FROM artikel, bestellungendetails, bestellungen, kunden
WHERE ArtikelId = ArtikelNr
    AND BestellNr = BestellId
    AND KundenNr = KundenId
    AND Firma LIKE "%blau%";

24.11

Anzeige aller Bestellungen für Kunden in Berlin mit EQUI_JOIN

SELECT KundenId, Firma, Ort, BestellId 
FROM kunden, bestellungen
WHERE KundenId = KundenNr
    AND Ort = "Berlin";

alternativ mit INNER JOIN

SELECT KundenId, Firma, Ort, BestellId 
FROM kunden INNER JOIN bestellungen ON KundenId = KundenNr
    AND Ort = "Berlin";

Anzeige aller Datenfelder mit LEFT JOIN (Nicht nur die Schnittmenge)

SELECT KundenId, Firma, Ort, BestellId 
FROM Kunden LEFT JOIN Bestellungen ON KundenId = KundenNr
    AND Ort = "Berlin";

Anzeige von einigen Kunden, Bedingungen nach KundenId

SELECT KundenId, Firma, Ort, BestellId 
FROM Kunden LEFT JOIN Bestellungen ON KundenId = KundenNr
WHERE KundenID IN (1,22,23,57,58);

Anzeige aller Bestellnummern (bestellungendetails) zu allen Artikeln (artikel)

SELECT ArtikelId, Artikelname, BestellNr
FROM artikel INNER JOIN bestellungendetails ON ArtikelId = ArtikelNr;

Anzeige aller Datenfelder mit LEFT JOIN (Nicht nur die Schnittmenge) nach Anlegen eines neuen Artikels

SELECT ArtikelId, Artikelname, BestellNr
FROM artikel LEFT JOIN bestellungendetails ON ArtikelId = ArtikelNr
ORDER BY 3;

Verknüpfung von mehreren Tabellen in Kombination mit LEFT JOIN und INNER JOIN

SELECT ArtikelID, Artikelname, Bestellnr 
FROM (((artikel 
        LEFT JOIN bestellungendetails ON ArtikelId = ArtikelNr)
        Inner JOIN bestellungen ON BestellId = BestellNr)
        INNER JOIN kunden on KundenID = KundenNr)
WHERE Artikelname = "Gravad lax";

In welche Orte (kunden) wurde "Gravad Lax" verkauft

SELECT ArtikelID, Artikelname, Firma, Ort 
FROM artikel 
    INNER JOIN bestellungendetails on ArtikelId = ArtikelNr
    INNER JOIN bestellungen on BestellId = BestellNr
    INNER JOIN kunden on KundenId = KundenNr
WHERE Artikelname = "Gravad lax";

Auflistung aller Gewürze und Kunden

SELECT ArtikelID, Artikelname, Firma, Ort, Kategoriename 
FROM artikel 
    INNER JOIN artikelkategorien on KategorieNr = kategorieId
    INNER JOIN bestellungendetails on ArtikelId = ArtikelNr
    INNER JOIN bestellungen on BestellId = BestellNr
    INNER JOIN kunden on KundenId = KundenNr
WHERE Kategoriename LIKE "%rze%";

Liste verkürzen und sortiert nach Firma

  SELECT DISTINCT ArtikelID, Artikelname, Firma, Ort, Kategoriename 
  FROM artikel 
      INNER JOIN artikelkategorien on KategorieNr = kategorieId
      INNER JOIN bestellungendetails on ArtikelId = ArtikelNr
      INNER JOIN bestellungen on BestellId = BestellNr
      INNER JOIN kunden on KundenId = KundenNr
  WHERE Kategoriename LIKE "%rze%"
  ORDER by 1;

Gesamtsumme aller verkauften Artikel

SELECT sum(Anzahl * Einzelpreis) 
FROM artikel, bestellungendetails
WHERE ArtikelNr = ArtikelId;

Gesamtsumme aller Umsätze pro Artikel mit Aggregatfunktion

SELECT Artikelname, 
    sum(Anzahl * Einzelpreis) 
FROM bestellungendetails INNER JOIN artikel
    ON ArtikelNr = ArtikelId
GROUP BY Artikelname
ORDER BY 2 DESC;

Ausgabe der verkauften Mengen und des jeweiligen Umsatzes je Artikel durch Kombination von Aggregatfunktionen

SELECT Artikelname, Einzelpreis, 
    sum(Anzahl) "Verkaufsmenge",
    sum(Anzahl*einzelpreis) "Umsatz"
FROM bestellungendetails INNER JOIN artikel
WHERE ArtikelNr = ArtikelId
GROUP BY Artikelname, Einzelpreis
ORDER BY 3 DESC;

Gesamtumsatz je Kategorie

SELECT Kategoriename,
    sum(Anzahl * Einzelpreis) "Umsatz je Kategorie"
FROM bestellungendetails
    INNER JOIN Artikel ON ArtikelNr = ArtikelId
    INNER JOIN artikelkategorien ON KategorieId = KategorieNr
GROUP BY  Kategoriename
ORDER BY 2;

Gesamtumsatz je Bestellung

SELECT Bestellnr,
    sum(Anzahl * Einzelpreis) "Umsatz je Bestellung"
FROM bestellungendetails
    INNER JOIN artikel on ArtikelNr = ArtikelId
GROUP BY  BestellNr
ORDER BY 2 DESC;

Einzelne Umsätze für alle Artikel mit tofu

SELECT BestellNr, Artikelname,
    sum(Anzahl * Einzelpreis) "Umsatz für Tofu je Bestellung"
FROM bestellungendetails
    INNER JOIN artikel ON ArtikelNr = ArtikelId
WHERE Artikelname LIKE "%tofu%"
GROUP BY  BestellNr, Artikelname
ORDER By 1;

27.11

Umsätze pro Artikel anzeigen

SELECT Artikelname, 
    sum(Anzahl * Einzelpreis) "Umsatz je Artikel"
FROM artikel, bestellungendetails 
WHERE ArtikelID = ArtikelNR
GROUP BY Artikelname
ORDER BY 2 DESC;

Bedingungen für Aggregatfunktion mit HAVING

Nur Umsätze für Artikel anzeigen, die über 10000 liegen

SELECT Artikelname, 
    sum(Anzahl * Einzelpreis) "Umsätze über 10000"
FROM artikel, bestellungendetails 
WHERE ArtikelID = ArtikelNR
GROUP BY Artikelname
HAVING sum(Anzahl * Einzelpreis) > 10000
ORDER BY 2 DESC;

Umsätze für alle Kunden, die mindestens 10000€ Umsatz haben

SELECT  Firma,
  sum(Anzahl * Einzelpreis) Umsatz  
FROM artikel, bestellungendetails, bestellungen, kunden 
WHERE ArtikelID = ArtikelNR
    AND BestellNr = BestellId
    AND KundenNr = KundenId
GROUP BY Firma
HAVING sum(Anzahl * Einzelpreis) > 10000
ORDER BY Umsatz;

Anzeige billigster Artikel

SELECT Artikelname, Einzelpreis
FROM artikel
ORDER BY 2
LIMIT 1;

Anzeige teuerster Artikel

SELECT Artikelname, Einzelpreis
FROM artikel
ORDER BY 2 DESC
LIMIT 1;

Anzeige teuerster und billigster Artikel (ohne Artikelname)

SELECT min(Einzelpreis) Billigster,
    max(Einzelpreis) Teuerster
FROM artikel

teuerster und billigster Artikel mit SUB-SELECT

SELECT Artikelname, Einzelpreis
FROM artikel
WHERE Einzelpreis = 
        (SELECT min(Einzelpreis) FROM artikel)
    OR 
      Einzelpreis =
        (SELECT max(Einzelpreis) FROM artikel);

Anzeige billigste und teuerste Gewürz

SELECT Artikelname, KategorieNr, Einzelpreis
FROM artikel
WHERE KategorieNr = 
    (SELECT KategorieId FROM artikelkategorien WHERE Kategoriename = "gewürze")
AND 
    (
      Einzelpreis = (
      SELECT min(Einzelpreis) 
      FROM artikel 
      WHERE KategorieNr = (
          SELECT KategorieId 
          FROM artikelkategorien 
          WHERE Kategoriename ="gewürze"))
OR 
    Einzelpreis = (
      SELECT max(Einzelpreis) 
      FROM artikel 
      WHERE KategorieNr = (
          SELECT KategorieId 
          FROM artikelkategorien 
          WHERE Kategoriename ="gewürze"))
    )
ORDER BY Einzelpreis;

alternativ

SELECT Artikelname, KategorieNr, Einzelpreis
FROM artikel
  INNER JOIN artikelkategorien ON KategorieNr = KategorieID
WHERE Kategoriename = "Gewürze"
  AND 
    Einzelpreis IN 
    (
      (SELECT min(Einzelpreis) 
        FROM artikel 
        WHERE KategorieNr = 
          (SELECT KategorieID 
            FROM artikelkategorien 
            WHERE Kategoriename ="gewürze")), 
      (SELECT max(Einzelpreis) 
        FROM artikel 
        WHERE KategorieNr = 
          (SELECT KategorieID 
            FROM artikelkategorien 
            WHERE Kategoriename ="gewürze"))
    )
ORDER BY Einzelpreis;

Vorlesung

22.11​

23.11​

24.11​

27.11​

22.11

23.11

24.11

27.11