SQL Anfänger 75 min

Projekt: Online-Shop DB

Baue eine vollständige Online-Shop Datenbank: Produkte, Kunden, Warenkorb, Bestellungen, Bewertungen und Zahlungen.

Aktualisiert:

In diesem Projekt baust du eine professionelle Datenbank fuer einen Online-Shop. Du wirst ein komplexes Schema entwerfen, Testdaten generieren und reale Business-Abfragen schreiben, wie sie in echten E-Commerce-Unternehmen verwendet werden.

Projektueberblick

Der Online-Shop “TechMart” verkauft Elektronik und Zubehoer. Die Datenbank soll verwalten:

  • Kunden mit Adressen und Kontodaten
  • Produkte in Kategorien mit Bildern und Bewertungen
  • Warenkorb fuer aktuelle Einkaufsvorgaenge
  • Bestellungen mit Positionen und Status-Tracking
  • Zahlungen und Versandinfos
  • Produktbewertungen von Kunden

Schritt 1: Schema erstellen

-- Kategorien (hierarchisch)
CREATE TABLE kategorien (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    eltern_id INTEGER REFERENCES kategorien(id),
    beschreibung TEXT
);

-- Kunden
CREATE TABLE kunden (
    id INTEGER PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    vorname VARCHAR(100) NOT NULL,
    nachname VARCHAR(100) NOT NULL,
    passwort_hash TEXT NOT NULL,
    telefon VARCHAR(20),
    registriert_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    letzter_login TIMESTAMP,
    aktiv BOOLEAN DEFAULT TRUE
);

-- Adressen (ein Kunde kann mehrere haben)
CREATE TABLE adressen (
    id INTEGER PRIMARY KEY,
    kunden_id INTEGER NOT NULL REFERENCES kunden(id) ON DELETE CASCADE,
    typ VARCHAR(20) DEFAULT 'lieferung' CHECK (typ IN ('lieferung', 'rechnung')),
    strasse VARCHAR(200) NOT NULL,
    hausnummer VARCHAR(10) NOT NULL,
    plz VARCHAR(10) NOT NULL,
    stadt VARCHAR(100) NOT NULL,
    land VARCHAR(2) DEFAULT 'DE',
    ist_standard BOOLEAN DEFAULT FALSE
);

-- Produkte
CREATE TABLE produkte (
    id INTEGER PRIMARY KEY,
    name VARCHAR(300) NOT NULL,
    beschreibung TEXT,
    preis DECIMAL(10,2) NOT NULL CHECK (preis > 0),
    streichpreis DECIMAL(10,2),
    kategorie_id INTEGER REFERENCES kategorien(id),
    sku VARCHAR(50) UNIQUE NOT NULL,
    lagerbestand INTEGER DEFAULT 0 CHECK (lagerbestand >= 0),
    gewicht_gramm INTEGER,
    aktiv BOOLEAN DEFAULT TRUE,
    erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Produktbilder
CREATE TABLE produktbilder (
    id INTEGER PRIMARY KEY,
    produkt_id INTEGER NOT NULL REFERENCES produkte(id) ON DELETE CASCADE,
    url TEXT NOT NULL,
    alt_text VARCHAR(200),
    position INTEGER DEFAULT 0
);

-- Warenkorb
CREATE TABLE warenkorb (
    id INTEGER PRIMARY KEY,
    kunden_id INTEGER NOT NULL REFERENCES kunden(id) ON DELETE CASCADE,
    produkt_id INTEGER NOT NULL REFERENCES produkte(id),
    menge INTEGER NOT NULL CHECK (menge > 0),
    hinzugefuegt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (kunden_id, produkt_id)
);

-- Bestellungen
CREATE TABLE bestellungen (
    id INTEGER PRIMARY KEY,
    kunden_id INTEGER NOT NULL REFERENCES kunden(id),
    bestellnummer VARCHAR(20) UNIQUE NOT NULL,
    status VARCHAR(30) DEFAULT 'eingegangen'
        CHECK (status IN ('eingegangen', 'bezahlt', 'in_bearbeitung', 'versendet', 'zugestellt', 'storniert')),
    liefer_adresse_id INTEGER REFERENCES adressen(id),
    rechnungs_adresse_id INTEGER REFERENCES adressen(id),
    versandart VARCHAR(50) DEFAULT 'standard',
    versandkosten DECIMAL(10,2) DEFAULT 0,
    zwischensumme DECIMAL(10,2) NOT NULL,
    mwst_betrag DECIMAL(10,2) NOT NULL,
    gesamtbetrag DECIMAL(10,2) NOT NULL,
    bestellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notizen TEXT
);

-- Bestellpositionen
CREATE TABLE bestellpositionen (
    id INTEGER PRIMARY KEY,
    bestell_id INTEGER NOT NULL REFERENCES bestellungen(id) ON DELETE CASCADE,
    produkt_id INTEGER NOT NULL REFERENCES produkte(id),
    produktname VARCHAR(300) NOT NULL,
    einzelpreis DECIMAL(10,2) NOT NULL,
    menge INTEGER NOT NULL CHECK (menge > 0),
    gesamt DECIMAL(10,2) NOT NULL
);

-- Zahlungen
CREATE TABLE zahlungen (
    id INTEGER PRIMARY KEY,
    bestell_id INTEGER NOT NULL REFERENCES bestellungen(id),
    zahlungsart VARCHAR(30) NOT NULL
        CHECK (zahlungsart IN ('kreditkarte', 'paypal', 'ueberweisung', 'lastschrift')),
    betrag DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'ausstehend'
        CHECK (status IN ('ausstehend', 'abgeschlossen', 'fehlgeschlagen', 'erstattet')),
    transaktions_id VARCHAR(100),
    gezahlt_am TIMESTAMP
);

-- Bewertungen
CREATE TABLE bewertungen (
    id INTEGER PRIMARY KEY,
    produkt_id INTEGER NOT NULL REFERENCES produkte(id) ON DELETE CASCADE,
    kunden_id INTEGER NOT NULL REFERENCES kunden(id),
    sterne INTEGER NOT NULL CHECK (sterne BETWEEN 1 AND 5),
    titel VARCHAR(200),
    kommentar TEXT,
    erstellt_am TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (produkt_id, kunden_id)
);

Schritt 2: Indizes

CREATE INDEX idx_produkte_kategorie ON produkte(kategorie_id);
CREATE INDEX idx_produkte_preis ON produkte(preis);
CREATE INDEX idx_bestellungen_kunden ON bestellungen(kunden_id);
CREATE INDEX idx_bestellungen_status ON bestellungen(status);
CREATE INDEX idx_bestellungen_datum ON bestellungen(bestellt_am);
CREATE INDEX idx_bestellpos_bestell ON bestellpositionen(bestell_id);
CREATE INDEX idx_bewertungen_produkt ON bewertungen(produkt_id);
CREATE INDEX idx_adressen_kunden ON adressen(kunden_id);
CREATE INDEX idx_warenkorb_kunden ON warenkorb(kunden_id);

Schritt 3: Testdaten

-- Kategorien (mit Hierarchie)
INSERT INTO kategorien (id, name, eltern_id, beschreibung) VALUES
(1, 'Elektronik', NULL, 'Alle elektronischen Geraete'),
(2, 'Computer', 1, 'Laptops, Desktops und Zubehoer'),
(3, 'Smartphones', 1, 'Handys und Zubehoer'),
(4, 'Audio', 1, 'Kopfhoerer, Lautsprecher'),
(5, 'Zubehoer', NULL, 'Kabel, Adapter, Huellen'),
(6, 'Laptop-Zubehoer', 5, 'Taschen, Maeuse, Tastaturen'),
(7, 'Handy-Zubehoer', 5, 'Huellen, Ladegeraete');

-- Kunden
INSERT INTO kunden (id, email, vorname, nachname, passwort_hash, registriert_am) VALUES
(1, 'laura@example.com', 'Laura', 'Hoffmann', 'hash_1', '2025-06-15 10:00:00'),
(2, 'stefan@example.com', 'Stefan', 'Wagner', 'hash_2', '2025-08-20 14:30:00'),
(3, 'maria@example.com', 'Maria', 'Fischer', 'hash_3', '2025-10-01 09:15:00'),
(4, 'thomas@example.com', 'Thomas', 'Schroeder', 'hash_4', '2026-01-10 16:45:00'),
(5, 'julia@example.com', 'Julia', 'Neumann', 'hash_5', '2026-02-01 11:20:00');

-- Adressen
INSERT INTO adressen (id, kunden_id, typ, strasse, hausnummer, plz, stadt, ist_standard) VALUES
(1, 1, 'lieferung', 'Berliner Str.', '42', '10115', 'Berlin', TRUE),
(2, 1, 'rechnung', 'Berliner Str.', '42', '10115', 'Berlin', FALSE),
(3, 2, 'lieferung', 'Hamburger Weg', '7', '20095', 'Hamburg', TRUE),
(4, 3, 'lieferung', 'Muenchner Platz', '15', '80331', 'Muenchen', TRUE),
(5, 4, 'lieferung', 'Koelner Ring', '3', '50667', 'Koeln', TRUE),
(6, 5, 'lieferung', 'Frankfurter Allee', '99', '60311', 'Frankfurt', TRUE);

-- Produkte
INSERT INTO produkte (id, name, beschreibung, preis, streichpreis, kategorie_id, sku, lagerbestand, gewicht_gramm) VALUES
(1, 'UltraBook Pro 15', 'Leistungsstarker Laptop mit 15 Zoll Display', 1299.99, 1499.99, 2, 'LAP-001', 25, 1800),
(2, 'SmartPhone X12', 'Flaggschiff-Smartphone mit 6.5 Zoll OLED', 899.99, NULL, 3, 'PHN-001', 50, 195),
(3, 'Wireless Kopfhoerer BT500', 'Noise-Cancelling Over-Ear Kopfhoerer', 149.99, 179.99, 4, 'AUD-001', 100, 280),
(4, 'USB-C Hub 7-in-1', 'Multiport Adapter fuer Laptops', 49.99, NULL, 6, 'ACC-001', 200, 120),
(5, 'Laptop-Rucksack Urban', 'Wasserabweisender Rucksack fuer 15 Zoll', 69.99, 89.99, 6, 'ACC-002', 75, 850),
(6, 'Handyhuelle Premium', 'Schutzhuelle aus echtem Leder', 29.99, NULL, 7, 'ACC-003', 150, 45),
(7, 'Bluetooth Lautsprecher Mini', 'Tragbarer Lautsprecher, wasserfest', 59.99, NULL, 4, 'AUD-002', 80, 350),
(8, 'Schnellladegeraet 65W', 'USB-C Ladegeraet mit GaN Technologie', 39.99, 44.99, 7, 'ACC-004', 120, 95);

-- Bestellungen
INSERT INTO bestellungen (id, kunden_id, bestellnummer, status, liefer_adresse_id, versandart, versandkosten, zwischensumme, mwst_betrag, gesamtbetrag, bestellt_am) VALUES
(1, 1, 'TM-2026-0001', 'zugestellt', 1, 'express', 9.99, 1349.98, 256.50, 1616.47, '2026-01-20 14:00:00'),
(2, 2, 'TM-2026-0002', 'zugestellt', 3, 'standard', 0, 199.98, 38.00, 237.98, '2026-02-05 09:30:00'),
(3, 1, 'TM-2026-0003', 'versendet', 1, 'standard', 0, 69.99, 13.30, 83.29, '2026-03-01 16:20:00'),
(4, 3, 'TM-2026-0004', 'bezahlt', 4, 'express', 9.99, 929.98, 176.70, 1116.67, '2026-03-08 11:45:00'),
(5, 4, 'TM-2026-0005', 'eingegangen', 5, 'standard', 0, 49.99, 9.50, 59.49, '2026-03-10 08:15:00'),
(6, 5, 'TM-2026-0006', 'storniert', 6, 'standard', 0, 149.99, 28.50, 178.49, '2026-03-09 20:00:00');

-- Bestellpositionen
INSERT INTO bestellpositionen (id, bestell_id, produkt_id, produktname, einzelpreis, menge, gesamt) VALUES
(1, 1, 1, 'UltraBook Pro 15', 1299.99, 1, 1299.99),
(2, 1, 4, 'USB-C Hub 7-in-1', 49.99, 1, 49.99),
(3, 2, 3, 'Wireless Kopfhoerer BT500', 149.99, 1, 149.99),
(4, 2, 4, 'USB-C Hub 7-in-1', 49.99, 1, 49.99),
(5, 3, 5, 'Laptop-Rucksack Urban', 69.99, 1, 69.99),
(6, 4, 2, 'SmartPhone X12', 899.99, 1, 899.99),
(7, 4, 6, 'Handyhuelle Premium', 29.99, 1, 29.99),
(8, 5, 4, 'USB-C Hub 7-in-1', 49.99, 1, 49.99),
(9, 6, 3, 'Wireless Kopfhoerer BT500', 149.99, 1, 149.99);

-- Zahlungen
INSERT INTO zahlungen (id, bestell_id, zahlungsart, betrag, status, transaktions_id, gezahlt_am) VALUES
(1, 1, 'kreditkarte', 1616.47, 'abgeschlossen', 'TXN-001', '2026-01-20 14:05:00'),
(2, 2, 'paypal', 237.98, 'abgeschlossen', 'TXN-002', '2026-02-05 09:32:00'),
(3, 3, 'lastschrift', 83.29, 'abgeschlossen', 'TXN-003', '2026-03-01 16:25:00'),
(4, 4, 'kreditkarte', 1116.67, 'abgeschlossen', 'TXN-004', '2026-03-08 11:50:00'),
(5, 5, 'ueberweisung', 59.49, 'ausstehend', NULL, NULL),
(6, 6, 'paypal', 178.49, 'erstattet', 'TXN-006', '2026-03-10 10:00:00');

-- Bewertungen
INSERT INTO bewertungen (id, produkt_id, kunden_id, sterne, titel, kommentar, erstellt_am) VALUES
(1, 1, 1, 5, 'Perfekter Laptop', 'Schnell, leicht, tolles Display. Kann ich nur empfehlen!', '2026-02-15 10:00:00'),
(2, 3, 2, 4, 'Guter Sound', 'Noise Cancelling funktioniert super, etwas klobig.', '2026-02-20 14:30:00'),
(3, 4, 1, 5, 'Unverzichtbar', 'Endlich genug Anschluesse am Laptop!', '2026-02-01 09:00:00'),
(4, 4, 2, 4, 'Solide', 'Funktioniert einwandfrei, gutes Preis-Leistungsverhaeltnis.', '2026-02-10 16:00:00'),
(5, 2, 3, 5, 'Bestes Handy', 'Kamera ist unglaublich gut, Akku haelt ewig.', '2026-03-10 11:00:00');

Schritt 4: Business-Abfragen

Umsatz-Dashboard

SELECT
    COUNT(DISTINCT b.id) AS bestellungen_gesamt,
    COUNT(DISTINCT CASE WHEN b.status != 'storniert' THEN b.id END) AS aktive_bestellungen,
    ROUND(SUM(CASE WHEN b.status != 'storniert' THEN b.gesamtbetrag ELSE 0 END), 2) AS umsatz_netto,
    ROUND(AVG(CASE WHEN b.status != 'storniert' THEN b.gesamtbetrag END), 2) AS avg_bestellwert,
    COUNT(DISTINCT b.kunden_id) AS aktive_kunden
FROM bestellungen b;

Bestseller-Liste

SELECT
    p.name,
    p.preis,
    SUM(bp.menge) AS verkauft,
    SUM(bp.gesamt) AS umsatz,
    ROUND(AVG(bw.sterne), 1) AS avg_bewertung
FROM produkte p
JOIN bestellpositionen bp ON p.id = bp.produkt_id
JOIN bestellungen b ON bp.bestell_id = b.id AND b.status != 'storniert'
LEFT JOIN bewertungen bw ON p.id = bw.produkt_id
GROUP BY p.id, p.name, p.preis
ORDER BY verkauft DESC;

Kundenanalyse mit Window Functions

SELECT
    k.vorname || ' ' || k.nachname AS kunde,
    COUNT(b.id) AS bestellungen,
    SUM(b.gesamtbetrag) AS gesamtumsatz,
    RANK() OVER (ORDER BY SUM(b.gesamtbetrag) DESC) AS umsatz_rang,
    ROUND(SUM(b.gesamtbetrag) * 100.0 /
        SUM(SUM(b.gesamtbetrag)) OVER (), 1) AS anteil_pct
FROM kunden k
JOIN bestellungen b ON k.id = b.kunden_id
WHERE b.status != 'storniert'
GROUP BY k.id, k.vorname, k.nachname
ORDER BY gesamtumsatz DESC;

Kategorie-Performance

SELECT
    kat.name AS kategorie,
    COUNT(DISTINCT p.id) AS anzahl_produkte,
    SUM(bp.menge) AS verkaufte_stueck,
    ROUND(SUM(bp.gesamt), 2) AS umsatz,
    ROUND(AVG(bw.sterne), 1) AS avg_bewertung
FROM kategorien kat
JOIN produkte p ON kat.id = p.kategorie_id
LEFT JOIN bestellpositionen bp ON p.id = bp.produkt_id
LEFT JOIN bestellungen b ON bp.bestell_id = b.id AND b.status != 'storniert'
LEFT JOIN bewertungen bw ON p.id = bw.produkt_id
GROUP BY kat.id, kat.name
ORDER BY umsatz DESC;

Monatlicher Umsatztrend

SELECT
    strftime('%Y-%m', bestellt_am) AS monat,
    COUNT(*) AS bestellungen,
    ROUND(SUM(gesamtbetrag), 2) AS umsatz,
    ROUND(AVG(gesamtbetrag), 2) AS avg_bestellwert
FROM bestellungen
WHERE status != 'storniert'
GROUP BY strftime('%Y-%m', bestellt_am)
ORDER BY monat;

Zahlungsanalyse

SELECT
    zahlungsart,
    COUNT(*) AS anzahl,
    ROUND(SUM(betrag), 2) AS gesamt,
    ROUND(AVG(betrag), 2) AS durchschnitt,
    SUM(CASE WHEN status = 'abgeschlossen' THEN 1 ELSE 0 END) AS erfolgreich,
    SUM(CASE WHEN status = 'fehlgeschlagen' THEN 1 ELSE 0 END) AS fehlgeschlagen
FROM zahlungen
GROUP BY zahlungsart
ORDER BY gesamt DESC;

Schritt 5: Nuetzliche Views

-- Produktuebersicht mit Bewertungen
CREATE VIEW v_produkte_detail AS
SELECT
    p.id,
    p.name,
    p.preis,
    p.streichpreis,
    CASE WHEN p.streichpreis IS NOT NULL
         THEN ROUND((1 - p.preis / p.streichpreis) * 100)
         ELSE NULL
    END AS rabatt_pct,
    kat.name AS kategorie,
    p.lagerbestand,
    COALESCE(ROUND(AVG(bw.sterne), 1), 0) AS bewertung,
    COUNT(bw.id) AS anzahl_bewertungen
FROM produkte p
LEFT JOIN kategorien kat ON p.kategorie_id = kat.id
LEFT JOIN bewertungen bw ON p.id = bw.produkt_id
WHERE p.aktiv = TRUE
GROUP BY p.id, p.name, p.preis, p.streichpreis, kat.name, p.lagerbestand;

-- Bestelluebersicht
CREATE VIEW v_bestellungen AS
SELECT
    b.bestellnummer,
    k.vorname || ' ' || k.nachname AS kunde,
    k.email,
    b.bestellt_am,
    b.status,
    b.gesamtbetrag,
    b.versandart,
    z.zahlungsart,
    z.status AS zahlung_status
FROM bestellungen b
JOIN kunden k ON b.kunden_id = k.id
LEFT JOIN zahlungen z ON b.id = z.bestell_id;

Schritt 6: Warenkorb-Funktionalitaet

-- Produkt zum Warenkorb hinzufuegen (oder Menge erhoehen)
INSERT INTO warenkorb (id, kunden_id, produkt_id, menge)
VALUES (1, 5, 3, 1)
ON CONFLICT (kunden_id, produkt_id) DO UPDATE
SET menge = warenkorb.menge + 1;

-- Warenkorb eines Kunden anzeigen
SELECT
    p.name,
    p.preis,
    w.menge,
    p.preis * w.menge AS position_total
FROM warenkorb w
JOIN produkte p ON w.produkt_id = p.id
WHERE w.kunden_id = 5;

-- Warenkorb-Summe
SELECT
    COUNT(*) AS positionen,
    SUM(p.preis * w.menge) AS zwischensumme
FROM warenkorb w
JOIN produkte p ON w.produkt_id = p.id
WHERE w.kunden_id = 5;

Was kommt als Naechstes?

Im naechsten Projekt lernst du Datenanalyse mit SQL - du wirst echte analytische Fragestellungen mit fortgeschrittenen Techniken beantworten.

Zusammenfassung

  • Du hast ein professionelles E-Commerce-Schema entworfen
  • Hierarchische Kategorien mit Self-Referencing Foreign Keys
  • Warenkorb-Logik mit UPSERT (ON CONFLICT)
  • Business-Abfragen fuer Umsatz, Bestseller und Kundenanalyse
  • Window Functions fuer Rankings und Anteile
  • Views fuer wiederverwendbare Report-Abfragen
  • Zahlungs- und Versand-Tracking mit Status-Management

Uebungen

  1. Rabatt-Analyse: Zeige alle Produkte mit Rabatt und wie viel Prozent der Rabatt betraegt.
  2. Kundenlebenszyklus: Finde Kunden, die seit mehr als 30 Tagen nicht bestellt haben.
  3. Cross-Selling: Welche Produkte werden oft zusammen bestellt?
  4. Stornierungsanalyse: Welcher Anteil der Bestellungen wird storniert?
  5. Erweiterung: Fuege ein Gutschein-System hinzu (Tabelle gutscheine und gutschein_einloesungen).

Pro-Tipp: In echten Online-Shops werden Produktpreise und -namen in den Bestellpositionen gespeichert (nicht nur die Produkt-ID), damit sich nachtraegliche Preisaenderungen nicht auf alte Bestellungen auswirken. Genau das haben wir hier auch gemacht - die Spalte produktname und einzelpreis in bestellpositionen sind ein Snapshot zum Zeitpunkt der Bestellung.

Zurück zum SQL Kurs