Sprachkurs SQL - Rekursives SQL

You must execute the code again to save those values in the database.

-- Ich verwende hier PostgeSQL
-- MySQL meckert beim UNIQUE-Constraint der email-Spalte, weil dafür ein Index angelegt werden muss, und VARCHAR(500) ist MySQL zu lang für Keys im Index.
-- Oracle kann kein ON UPDATE CASCADE

CREATE TABLE kunden (kundennummer INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(500) UNIQUE, passwort CHAR(32), land VARCHAR(100), geworben_von INT REFERENCES kunden(kundennummer) ON DELETE SET NULL);
CREATE TABLE hersteller (firma VARCHAR(50) PRIMARY KEY, land VARCHAR(100));
CREATE TABLE produkte (produktnummer INT PRIMARY KEY, bezeichnung VARCHAR(100) NOT NULL, preis DECIMAL(9,2),
                       hersteller VARCHAR(50) REFERENCES hersteller(firma) ON UPDATE CASCADE);
CREATE TABLE bewertungen (kundennummer INT REFERENCES kunden(kundennummer),
                          produktnummer INT REFERENCES produkte(produktnummer),
                          sterne INT DEFAULT 5 CHECK(sterne BETWEEN 1 AND 5),
                          bewertungstext VARCHAR(100000),
                          PRIMARY KEY(kundennummer, produktnummer));
CREATE TABLE bewertungslikes (liker INT REFERENCES kunden(kundennummer),
                              kundennummer INT, produktnummer INT,
                             PRIMARY KEY(liker, kundennummer, produktnummer),
                             FOREIGN KEY(kundennummer, produktnummer) REFERENCES bewertungen(kundennummer, produktnummer));
CREATE TABLE bestellungen (bestellnummer INT PRIMARY KEY, kundennummer INT REFERENCES kunden(kundennummer), zeit TIMESTAMP, preis DECIMAL(9,2));
CREATE TABLE bestellungen_positionen (bestellnummer INT REFERENCES bestellungen(bestellnummer), 
                                      produktnummer INT REFERENCES produkte(produktnummer), anzahl INT, 
                                      PRIMARY KEY(bestellnummer, produktnummer));

INSERT INTO kunden VALUES(4,'Ute', 'ute@example.com', NULL, 'Deutschland', NULL);                              
INSERT INTO kunden VALUES(5,'Peter', 'peter@example.com', NULL, 'Deutschland', 4);
INSERT INTO kunden VALUES(8,'Anna', 'anna@example.com', 5, 'Italien', 5);
INSERT INTO hersteller VALUES ('Calgonte', 'Italien');
INSERT INTO hersteller VALUES ('Monsterfood', 'USA');
INSERT INTO hersteller VALUES ('Holzkopf', 'Österreich');
INSERT INTO produkte VALUES(17, 'Schokoriegel', 0.89, 'Monsterfood');
INSERT INTO produkte VALUES(18, 'Müsliriegel', 1.19, 'Monsterfood');
INSERT INTO produkte VALUES(29, 'Spülmaschinentabs', 3.99, 'Calgonte');
INSERT INTO produkte VALUES(88, 'Katzenfutter', 4.99, NULL);
INSERT INTO produkte VALUES(91, 'Maschinenbau-Lehrbuch', 22.90, NULL);
INSERT INTO produkte VALUES(92, 'Regal', 100.00, NULL);
INSERT INTO produkte VALUES(998, 'Geschenkverpackung', 0.00, NULL);
INSERT INTO produkte VALUES(999, 'Katalog', 0.00, NULL);
INSERT INTO bewertungen VALUES(5, 17, 4, 'Guter Schokoriegel, aber die Verpackung geht schwer auf');
INSERT INTO bewertungen VALUES(5, 29, 1, 'Mein Geschirr wird nicht sauber!');
INSERT INTO bewertungslikes VALUES(8,5,17);
INSERT INTO bestellungen VALUES(101, 5, '2018-05-26 20:31:00', 34.80);
INSERT INTO bestellungen VALUES(102, 8, '2018-05-26 20:31:01', 100);
INSERT INTO bestellungen VALUES(103, 8, '2018-05-27 8:15:00', 0.89);
INSERT INTO bestellungen_positionen VALUES(101, 91, 1);
INSERT INTO bestellungen_positionen VALUES(101, 18, 10);
INSERT INTO bestellungen_positionen VALUES(102, 91, 1);
INSERT INTO bestellungen_positionen VALUES(103, 17, 1);

WITH RECURSIVE kunden_huelle AS (SELECT kundennummer, geworben_von FROM kunden WHERE geworben_von IS NOT NULL
UNION ALL SELECT r.kundennummer, k.geworben_von FROM kunden_huelle r JOIN kunden k ON r.geworben_von = k.kundennummer WHERE k.geworben_von IS NOT NULL)
SELECT geworben_von, COUNT(*) FROM kunden_huelle GROUP BY geworben_von;

geworben_von	count
5	1
4	2

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1