Sprachkurs SQL - Views

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));

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', 25.90, 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);

-- Views:
CREATE VIEW teuere_produkte AS SELECT * FROM produkte WHERE preis > 10;
CREATE VIEW produkte_view AS SELECT p.produktnummer, bezeichnung, preis, hersteller, avg(sterne) AS bewertung FROM produkte p join bewertungen b ON p.produktnummer = b.produktnummer GROUP BY p.produktnummer, bezeichnung, preis, hersteller;

SELECT * FROM teuere_produkte;

SELECT * FROM produkte_view;

produktnummer	bezeichnung	preis	hersteller
91	Maschinenbau-Lehrbuch	25.90

produktnummer	bezeichnung	preis	hersteller	bewertung
17	Schokoriegel	0.89	Monsterfood	4.0000000000000000
29	Spülmaschinentabs	3.99	Calgonte	1.00000000000000000000

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
CREATE VIEW
CREATE VIEW