SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE benutzer ( benutzer_id integer PRIMARY KEY, benutzername varchar(20), email varchar(40), passwort varchar(255) ); CREATE TABLE zutat ( zutat_id integer PRIMARY KEY, bezeichnung varchar(50) ); CREATE TABLE rezept ( rezept_id integer PRIMARY KEY, titel varchar(255), benutzer_id integer REFERENCES benutzer(benutzer_id), privat boolean, dauer_min integer, anz_portionen smallint ); CREATE TABLE bewertung ( benutzer_id integer REFERENCES benutzer(benutzer_id), rezept_id integer REFERENCES rezept(rezept_id), text varchar(200), bewertung smallint, PRIMARY KEY (benutzer_id, rezept_id) ); CREATE TABLE einheit ( kurzname varchar(3) PRIMARY KEY, name varchar(20), basis_einheit varchar(3) REFERENCES einheit(kurzname), faktor_zur_basis numeric(8,3) ); CREATE TABLE sammlung ( sammlung_id integer, ersteller integer REFERENCES benutzer(benutzer_id), name varchar(50), privat boolean ); CREATE TABLE rezept_in_sammlung ( sammlung_id integer, rezept_id integer REFERENCES rezept(rezept_id), ordnungszahl smallint, ts_hinzugefuegt timestamp without time zone DEFAULT now(), PRIMARY KEY (sammlung_id, rezept_id) ); CREATE TABLE rezept_schritt ( rezept integer REFERENCES rezept(rezept_id), nr_schritt smallint, titel varchar(255), anweisungstext text, PRIMARY KEY (rezept, nr_schritt) ); CREATE TABLE rezept_schritt_zutaten ( rezept integer, nr_schritt integer, zutat integer REFERENCES zutat(zutat_id), menge numeric(8,2), einheit varchar(4) REFERENCES einheit(kurzname), PRIMARY KEY (rezept, nr_schritt, zutat), FOREIGN KEY (rezept, nr_schritt) REFERENCES rezept_schritt (rezept, nr_schritt) ); INSERT INTO benutzer VALUES (1, 'melonenlord', 'melonen@lord.de', 'test12345'); INSERT INTO benutzer VALUES (2, 'muffinman', 'muffin@man.de', 'passwort'); INSERT INTO benutzer VALUES (3, 'lebkuchenmann', 'lebkuchenmann@shrek.de', '123456'); INSERT INTO benutzer VALUES (4, 'beefträger', 'mail@beef-traeger.de', '987654321'); INSERT INTO benutzer VALUES (5, 'gordonramsey', 'idiot@sandwich.de', 'Hallo,dasistsicher!11!!1'); INSERT INTO zutat VALUES (1, 'Lasagneplatten'); INSERT INTO zutat VALUES (2, 'Milch'); INSERT INTO zutat VALUES (3, 'Mehl'); INSERT INTO zutat VALUES (4, 'Schalotten'); INSERT INTO zutat VALUES (5, 'Schokolade'); INSERT INTO zutat VALUES (6, 'Weizenmehl'); INSERT INTO zutat VALUES (7, 'Zucker'); INSERT INTO zutat VALUES (8, 'Vanillezucker'); INSERT INTO zutat VALUES (9, 'Salz'); INSERT INTO zutat VALUES (10, 'Butter'); INSERT INTO zutat VALUES (11, 'Eier'); INSERT INTO zutat VALUES (12, 'Backpulver'); INSERT INTO zutat VALUES (13, 'Tomaten (Rispen)'); INSERT INTO zutat VALUES (14, 'Schneckenhaus'); INSERT INTO zutat VALUES (15, 'Karamell'); INSERT INTO zutat VALUES (16, 'Puderzucker'); INSERT INTO zutat VALUES (17, 'Zucchini'); INSERT INTO zutat VALUES (18, 'Aubergine'); INSERT INTO zutat VALUES (19, 'Knoblauchzehe'); INSERT INTO zutat VALUES (20, 'Tomaten (gehackt)'); INSERT INTO zutat VALUES (21, 'Tomatenmark'); INSERT INTO zutat VALUES (22, 'Zwiebel'); INSERT INTO zutat VALUES (23, 'Olivenöl'); INSERT INTO zutat VALUES (24, 'Oregano'); INSERT INTO zutat VALUES (25, 'Pfeffer'); INSERT INTO zutat VALUES (26, 'Nudeln'); INSERT INTO zutat VALUES (27, 'Basilikum'); INSERT INTO zutat VALUES (28, 'Melone'); INSERT INTO zutat VALUES (29, 'Zitrone'); INSERT INTO zutat VALUES (30, 'Zucker (braun)'); INSERT INTO zutat VALUES (31, 'Wasser'); INSERT INTO zutat VALUES (32, 'Eiswürfel'); INSERT INTO zutat VALUES (33, 'Wein'); INSERT INTO zutat VALUES (34, 'Joghurt'); INSERT INTO zutat VALUES (35, 'Öl'); INSERT INTO zutat VALUES (36, 'Karotten'); INSERT INTO zutat VALUES (37, 'Linsen (rot)'); INSERT INTO zutat VALUES (38, 'Tomaten (stückig)'); INSERT INTO zutat VALUES (39, 'Tomaten (passiert)'); INSERT INTO zutat VALUES (40, 'Zartbitterschokolade'); INSERT INTO zutat VALUES (41, 'Vanilleeis'); INSERT INTO einheit VALUES ('l', 'liter', 'l', 1.00); INSERT INTO einheit VALUES ('ml', 'Milliliter', 'l', 0.001); INSERT INTO einheit VALUES ('kg', 'Kilogramm', 'kg', 1.00); INSERT INTO einheit VALUES ('g', 'Gramm', 'kg', 0.001); INSERT INTO einheit VALUES ('Stk', 'Stück', 'Stk', 1.00); INSERT INTO einheit VALUES ('TL', 'Teelöffel', 'TL', 1.00); INSERT INTO einheit VALUES ('Prs', 'Prise', 'Prs', 1.00); INSERT INTO einheit VALUES ('EL', 'Esslöffel', 'EL', 1.00); INSERT INTO rezept VALUES (1, 'Nudeln kochen', 2, false, 10, 2); INSERT INTO rezept VALUES (3, 'Rührei', 5, false, 90, 3); INSERT INTO rezept VALUES (4, 'Lavakuchen', 3, true, 30, 4); INSERT INTO rezept VALUES (6, 'Pfannenbrot', 5, false, 15, 3); INSERT INTO sammlung VALUES (1, 4, 'Schwierig', false); INSERT INTO sammlung VALUES (2, 5, 'Legger Schoggo', false); INSERT INTO rezept_in_sammlung VALUES (1, 1, 2); INSERT INTO rezept_in_sammlung VALUES (1, 3, 2); INSERT INTO rezept_in_sammlung VALUES (2, 4, 1); INSERT INTO rezept_in_sammlung VALUES (3, 3, 1); INSERT INTO rezept_schritt VALUES (1, 1, 'Wasser kochen lassen', 'Das Wasser in einen großen Topf zum kochen bringen und großzügig salzen.'); INSERT INTO rezept_schritt VALUES (1, 2, 'Nudeln hineingeben', 'Die Nudeln in das kochende Wasser geben und so lange wie auf der Verpackung angegeben kochen lassen.'); INSERT INTO rezept_schritt VALUES (1, 3, 'Aus dem Topf nehmen', 'Die Nudeln durch ein großes Sieb abschütten und sofort servieren. Nach Belieben mit Ketchup abschmecken!'); INSERT INTO rezept_schritt VALUES (3, 1, 'Eier in die Pfanne', 'Die Eier in die Pfanne geben und rühren.'); INSERT INTO rezept_schritt VALUES (4, 1, 'Masse vorbereiten', 'Die Schokolade im Wasserbad schmelzen. Anschließend vom Herd nehmen und die Butter unterrühren. Die Eier und den Zucker in eine Schüssel geben und so lange schlagen, bis ein weißer Schaum entsteht. Die geschmolzene Schokolade zur anderen Masse hinzufügen und dann das Mehl unterrühren. Die Masse auf vier einzelne, gut gefettete Förmchen verteilen.'); INSERT INTO rezept_schritt VALUES (4, 2, 'Backen und servieren', 'Im vorgeheizten Backofen bei 200°C Ober-/Unterhitze (Umluft: 180°C) im etwa 10 Minuten backen, so dass das Dessert außen fest, aber innen noch flüssig ist. Noch heiß mit jeweils einer Kugel Vanilleeis servieren.'); INSERT INTO rezept_schritt VALUES (6, 1, 'Zutaten vermengen', 'Alle Zutaten zu einem glatten Teig verrühren. 10 Minuten ruhen lassen.'); INSERT INTO rezept_schritt VALUES (6, 2, 'In Pfanne braten', 'Öl in der Pfanne erhitzen. Teig in Portionen teilen und bei mittlerer Hitze ausbacken. Beim Wenden mit dem Pfannenwender flach drücken.'); INSERT INTO rezept_schritt_zutaten VALUES (1, 1, 31, 2.00, 'l'); INSERT INTO rezept_schritt_zutaten VALUES (1, 1, 9, 3.00, 'g'); INSERT INTO rezept_schritt_zutaten VALUES (1, 2, 26, 250.00, 'g'); INSERT INTO rezept_schritt_zutaten VALUES (3, 1, 11, 6.00, 'Stk'); INSERT INTO rezept_schritt_zutaten VALUES (4, 1, 40, 200.00, 'g'); INSERT INTO rezept_schritt_zutaten VALUES (4, 1, 10, 200.00, 'g'); INSERT INTO rezept_schritt_zutaten VALUES (4, 1, 11, 3.00, 'Stk'); INSERT INTO rezept_schritt_zutaten VALUES (4, 1, 7, 70.00, 'g'); INSERT INTO rezept_schritt_zutaten VALUES (4, 1, 3, 50.00, 'g'); INSERT INTO rezept_schritt_zutaten VALUES (4, 2, 41, 4.00, 'Stk'); INSERT INTO rezept_schritt_zutaten VALUES (6, 1, 3, 150.00, 'g'); INSERT INTO rezept_schritt_zutaten VALUES (6, 1, 12, 0.50, 'TL'); INSERT INTO rezept_schritt_zutaten VALUES (6, 1, 34, 100.00, 'ml'); INSERT INTO rezept_schritt_zutaten VALUES (6, 1, 9, 1.00, 'Prs'); INSERT INTO rezept_schritt_zutaten VALUES (6, 2, 35, 0.50, 'EL'); INSERT INTO bewertung VALUES (1, 1, 'Wow. Viel zu schwer. Mir sind die Nudeln angebrannt, aber Salz und Ketchup waren trotzdem lecker.', 4); INSERT INTO bewertung VALUES (4, 1, 'Danke für das tolle Rezept! Endlich habe ich auch mal was hinbekommen.', 5); INSERT INTO bewertung VALUES (5, 4, 'Ich mag Schoggo - GR', 5); INSERT INTO bewertung VALUES (2, 3, 'Habe statt Eiern Mehl genommen. Hat leider gar nicht geschmeckt.', 1); INSERT INTO bewertung VALUES (4, 3, 'Unfassbar gut!', 5); INSERT INTO bewertung VALUES (5, 3, 'Leider ohne Schokolade. - GR', 2); INSERT INTO bewertung VALUES (3, 4, 'Tolles Rezept, schau doch auch mal auf meiner Seite vorbei :) <3', 5); /* 1.1 di postgressql baru keupdate sampe segini*/ ALTER TABLE sammlung ADD PRIMARY KEY (sammlung_id); /* 1.2 * Als Grundregel der referentiellen Integritaet gilt, dass Fremdschluessel immer auf existierende Datensaetze verweisen muessen. * Also muss SammlungId mit dem Wert 3 in diesem Fall erstmal geaendert werden und danach die Fremdschluessel erstellen. */ UPDATE rezept_in_sammlung SET sammlung_id = '2' WHERE rezept_id = '3' AND ordnungszahl = '1'; ALTER TABLE rezept_in_sammlung ADD FOREIGN KEY (sammlung_id) REFERENCES sammlung(sammlung_id); /* 1.3 */ UPDATE benutzer SET passwort = MD5(passwort); /* 3.1 */ CREATE TABLE einkaufsliste( zutat_id integer PRIMARY KEY, menge numeric(8,2), einheit varchar(4) ); CREATE OR REPLACE PROCEDURE gesamtzutatenmenge( erezept_id rezept.rezept_id%type ) LANGUAGE PLPGSQL AS $$ BEGIN INSERT INTO einkaufsliste SELECT zutat, SUM(menge), einheit FROM rezept_schritt_zutaten WHERE rezept = erezept_id GROUP BY zutat, einheit; END; $$; CALL gesamtzutatenmenge('1'); SELECT * FROM einkaufsliste; /* 3.2 */ ALTER TABLE benutzer ADD COLUMN durchschnittsbewertung_aller numeric(8,2) DEFAULT 0; UPDATE benutzer b SET durchschnittsbewertung_aller = (SELECT AVG(bw.bewertung) FROM bewertung bw INNER JOIN rezept r ON r.rezept_id = bw.rezept_id INNER JOIN benutzer b ON b.benutzer_id = bw.benutzer_id WHERE r.rezept_id = bw.rezept_id AND b.benutzer_id = r.benutzer_id GROUP BY b.benutzer_id); SELECT * FROM benutzer;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear