Unterabfragen, CTEs und Mengenoperationen

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
-- Kunden mit überdurchschnittlichen Punkten
SELECT name, punkte FROM kunden
WHERE punkte > (SELECT avg(punkte) FROM kunden);

-- Kunden, die mindestens eine Bestellung haben
SELECT name FROM kunden WHERE id IN (SELECT kunde_id FROM bestellungen);

-- Kunden ohne Bestellung
SELECT name FROM kunden k
WHERE NOT EXISTS (SELECT 1 FROM bestellungen b WHERE b.kunde_id = k.id);

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT name,
       (SELECT count(*) FROM bestellungen b WHERE b.kunde_id = k.id) AS anzahl
FROM kunden k
ORDER BY anzahl DESC, name;

SELECT ort, durchschnitt
FROM (SELECT ort, avg(punkte) AS durchschnitt FROM kunden GROUP BY ort)
WHERE durchschnitt > 100
ORDER BY ort;

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
WITH umsatz AS (
    SELECT kunde_id, sum(preis * menge) AS summe
    FROM bestellungen
    WHERE kunde_id IS NOT NULL
    GROUP BY kunde_id
),
rang AS (
    SELECT kunde_id, summe FROM umsatz WHERE summe > 100
)
SELECT k.name, r.summe
FROM rang r
JOIN kunden k ON k.id = r.kunde_id
ORDER BY r.summe DESC;

WITH RECURSIVE zahlen(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM zahlen WHERE n < 5
)
SELECT n, n * n AS quadrat FROM zahlen;

CREATE TABLE mitarbeiter (id INTEGER PRIMARY KEY, name TEXT, chef_id INTEGER);
INSERT INTO mitarbeiter VALUES (1,'Anna',NULL),(2,'Ben',1),(3,'Cem',1),(4,'Dina',2),(5,'Eli',4);

WITH RECURSIVE baum(id, name, ebene, pfad) AS (
    SELECT id, name, 0, name FROM mitarbeiter WHERE chef_id IS NULL
    UNION ALL
    SELECT m.id, m.name, b.ebene + 1, b.pfad || ' > ' || m.name
    FROM mitarbeiter m JOIN baum b ON m.chef_id = b.id
)
SELECT ebene, pfad FROM baum ORDER BY pfad;

CREATE TABLE kunden (id INTEGER PRIMARY KEY, name TEXT NOT NULL, ort TEXT, punkte INTEGER);
CREATE TABLE bestellungen (id INTEGER PRIMARY KEY, kunde_id INTEGER, produkt TEXT, preis REAL, menge INTEGER, datum TEXT);
INSERT INTO kunden VALUES (1,'Mia','Berlin',120),(2,'Tom','Hamburg',80),(3,'Zoe','Berlin',200),(4,'Ben','München',NULL),(5,'Eva','Köln',150);
INSERT INTO bestellungen VALUES
 (1,1,'Laptop',999.00,1,'2025-01-10'),(2,1,'Maus',25.50,2,'2025-01-12'),(3,2,'Monitor',189.90,1,'2025-02-01'),
 (4,3,'Tastatur',49.00,1,'2025-02-15'),(5,3,'Maus',25.50,1,'2025-03-03'),(6,3,'Laptop',999.00,1,'2025-03-20'),
 (7,NULL,'Kabel',5.00,3,'2025-03-21');
SELECT ort FROM kunden WHERE punkte >= 120
UNION
SELECT ort FROM kunden WHERE ort = 'Hamburg'
ORDER BY ort;

SELECT produkt FROM bestellungen WHERE preis > 100
INTERSECT
SELECT produkt FROM bestellungen WHERE menge = 1
ORDER BY produkt;

SELECT name FROM kunden
EXCEPT
SELECT k.name FROM kunden k JOIN bestellungen b ON b.kunde_id = k.id
ORDER BY name;