DROP TABLE IF EXISTS "adressen" CASCADE;
DROP TABLE IF EXISTS "allerg_prod" CASCADE;
DROP TABLE IF EXISTS "allergene" CASCADE;
DROP TABLE IF EXISTS "filialen" CASCADE;
DROP TABLE IF EXISTS "personen" CASCADE;
DROP TABLE IF EXISTS "kunden" CASCADE;
DROP TABLE IF EXISTS "mitarbeiter" CASCADE;
DROP TABLE IF EXISTS "produkte" CASCADE;
DROP TABLE IF EXISTS "rechn_prod" CASCADE;
DROP TABLE IF EXISTS "rechnungen" CASCADE;
-- 'adressen'
CREATE TABLE adressen
(
addr_id serial NOT NULL UNIQUE,
strasse text NOT NULL,
plz INT NOT NULL,
ort text NOT NULL,
hausnummer INT,
PRIMARY KEY(addr_id)
);
-- 'allergene'
CREATE TABLE allergene
(
kuerzel text NOT NULL UNIQUE,
bezeichnung text NOT NULL,
PRIMARY KEY (kuerzel)
);
-- 'produkte'
CREATE TABLE produkte
(
produkt_id serial NOT NULL UNIQUE,
"name" text NOT NULL,
preis DECIMAL NOT NULL,
mwst INT NOT NULL,
ausgelaufen BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (produkt_id)
);
-- 'allerg_prod'
CREATE TABLE allerg_prod
(
fk_kuerzel text NOT NULL UNIQUE,
fk_produkt_id serial NOT NULL UNIQUE,
PRIMARY KEY(fk_kuerzel, fk_produkt_id),
FOREIGN KEY(fk_kuerzel) REFERENCES allergene(kuerzel),
FOREIGN KEY(fk_produkt_id) REFERENCES produkte(produkt_id)
);
-- 'personen'
CREATE TABLE personen
(
pers_id serial NOT NULL UNIQUE,
vorname text,
nachname text,
geschlecht text CHECK (geschlecht IN('m','w')),
fk_addr_id serial,
PRIMARY KEY (pers_id),
FOREIGN KEY (fk_addr_id) REFERENCES adressen(addr_id)
);
-- 'kunden'
CREATE TABLE kunden
(
kun_id serial NOT NULL UNIQUE,
fk_pers_id serial NOT NULL UNIQUE,
PRIMARY KEY(kun_id),
FOREIGN KEY(fk_pers_id) REFERENCES personen (pers_id)
);
-- 'mitarbeiter'
CREATE TABLE mitarbeiter
(
fk_pers_id serial NOT NULL UNIQUE,
eintrittsdatum text NOT NULL,
austrittsdatum text,
PRIMARY KEY(fk_pers_id),
FOREIGN KEY(fk_pers_id) REFERENCES personen (pers_id)
);
-- 'filialen'
CREATE TABLE filialen
(
f_id serial NOT NULL UNIQUE,
uid INT,
"name" text NOT NULL,
telefon INT,
fax INT,
fk_addr_id serial NOT NULL UNIQUE,
geschlossen BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY(f_id),
FOREIGN KEY(fk_addr_id) REFERENCES adressen(addr_id)
);
-- 'rechnungen'
CREATE TABLE rechnungen
(
rechn_nr serial NOT NULL UNIQUE,
datum DATE NOT NULL,
uhrzeit TIME NOT NULL,
fk_f_id serial NOT NULL,
fk_kunden_pers_id serial,
fk_mitarbeiter_pers_id serial NOT NULL,
PRIMARY KEY(rechn_nr),
FOREIGN KEY(fk_f_id) REFERENCES filialen(f_id),
FOREIGN KEY(fk_kunden_pers_id) REFERENCES kunden(fk_pers_id),
FOREIGN KEY(fk_mitarbeiter_pers_id) REFERENCES mitarbeiter(fk_pers_id)
);
-- 'rechn_prod'
CREATE TABLE rechn_prod
(
"id" serial NOT NULL UNIQUE,
preis DECIMAL NOT NULL,
mwst INT NOT NULL,
fk_prod_id serial,
fk_rechn_nr serial NOT NULL,
PRIMARY KEY("id"),
FOREIGN KEY(fk_prod_id) REFERENCES produkte(produkt_id),
FOREIGN KEY(fk_rechn_nr) REFERENCES rechnungen(rechn_nr)
);
-- insert allergene
INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('A', 'Gluten');
INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('B', 'Krebstiere');
INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('C', 'Eier');
INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('D', 'Fisch');
INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('E', 'Erdnüsse');
INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('F', 'Sojabohnen');
INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('G', 'Milch');
INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('H', 'Schalenfruechte');
-- insert adressen
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Hauptstrasse', 2151, 'Asparn an der Zaya', 24);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Breunerstrasse', 2130, 'Mistelbach', 43);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Obere Landstrasse', 2130, 'Mistelbach', 5);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Krauthof', 2130, 'Mistelbach', 45);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Hauptplatz', 2130, 'Mistelbach', 75);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Krankenhaus', 1200, 'Wien', 24);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Kopfgasse', 1230, 'Wien', 87);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Krautstrasse', 1010, 'Wien', 7);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Kartoffelweg', 1010, 'Wien', 66);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Nasenhaarstrasse', 1030, 'Wien', 57);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Chinakohl', 1100, 'Wien', 12);
INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Brandweingasse', 1020, 'Wien', 15);
-- insert Filialen
INSERT INTO filialen (uid, "name", telefon, fax, geschlossen, fk_addr_id)
VALUES (24324, 'Hauptstrasse', 4363456, 9741781, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Hauptstrasse'));
INSERT INTO filialen (uid, "name", telefon, fax, geschlossen, fk_addr_id)
VALUES (23413, 'Krankenhaus', 6346463, 6456456, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Krankenhaus'));
INSERT INTO filialen (uid, "name", telefon, fax, geschlossen, fk_addr_id)
VALUES (23425, 'Kopfgasse', 7454536, 34674345, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Kopfgasse'));
-- insert Personen
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Hans', 'Kunz', 'm', (SELECT addr_id FROM adressen WHERE strasse='Chinakohl'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Mario', 'Dietrich', 'm', (SELECT addr_id FROM adressen WHERE strasse='Breunerstrasse'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Christine', 'Kraus', 'w', (SELECT addr_id FROM adressen WHERE strasse='Krankenhaus'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Karl', 'Knuddle', 'm', (SELECT addr_id FROM adressen WHERE strasse='Kopfgasse'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Margit', 'Kunz', 'w', (SELECT addr_id FROM adressen WHERE strasse='Chinakohl'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Manfred', 'Krakile', 'm', (SELECT addr_id FROM adressen WHERE strasse='Krautstrasse'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Charoline', 'Franz', 'w', (SELECT addr_id FROM adressen WHERE strasse='Kartoffelweg'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Alexander', 'Dietrich', 'm', (SELECT addr_id FROM adressen WHERE strasse='Nasenhaarstrasse'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Christof', 'Kolumbus', 'm', (SELECT addr_id FROM adressen WHERE strasse='Brandweingasse'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Franziska', 'Kolumbus', 'w', (SELECT addr_id FROM adressen WHERE strasse='Brandweingasse'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Hannes', 'Klampfl', 'm', (SELECT addr_id FROM adressen WHERE strasse='Obere Landstrasse'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Selina', 'Brinnich', 'w', (SELECT addr_id FROM adressen WHERE strasse='Hauptplatz'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Thiago', 'Gumhold', 'm', (SELECT addr_id FROM adressen WHERE strasse='Hauptplatz'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Philipp', 'Schermann', 'm', (SELECT addr_id FROM adressen WHERE strasse='Krankenhaus'));
INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Michael', 'Nemec', 'm', (SELECT addr_id FROM adressen WHERE strasse='Krauthof'));
-- INSERT MITARBEITER
INSERT INTO mitarbeiter (eintrittsdatum, fk_pers_id) VALUES ('2016-12-30', (SELECT pers_id FROM personen WHERE vorname='Hans'));
INSERT INTO mitarbeiter (eintrittsdatum, fk_pers_id) VALUES ('2016-03-15', (SELECT pers_id FROM personen WHERE vorname='Mario'));
INSERT INTO mitarbeiter (eintrittsdatum, fk_pers_id) VALUES ('2016-02-01', (SELECT pers_id FROM personen WHERE vorname='Christine'));
INSERT INTO mitarbeiter (eintrittsdatum, fk_pers_id) VALUES ('2016-12-26', (SELECT pers_id FROM personen WHERE vorname='Karl'));
INSERT INTO mitarbeiter (eintrittsdatum, fk_pers_id) VALUES ('2016-12-27', (SELECT pers_id FROM personen WHERE vorname='Margit'));
-- INSERT KUNDEN
INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Manfred'));
INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Charoline'));
INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Alexander'));
INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Christof'));
INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Franziska'));
INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Hannes'));
INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Selina'));
INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Thiago'));
INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Philipp'));
INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Michael'));
-- INSERT PRODUKTE
INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Schnitzel', 9.50, 10, FALSE);
INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Schweinsbraten', 13.50, 10, FALSE);
INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Deo', 5.30, 20, FALSE);
INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Shampoo', 2.50, 20, FALSE);
INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Magazin', 4.50, 10, FALSE);
INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Cola', 3.50, 10, FALSE);
INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Kartoffeln', 2.50, 10, FALSE);
INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Autobahnvignette', 89.90, 20, FALSE);
INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Scheibenwischer', 12.50, 20, FALSE);
INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Duftbaum', 5.00, 20, FALSE);
-- INSERT RECHNUNGEN
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-04-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Michael')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Karl')),
(SELECT f_id FROM filialen WHERE name='Krankenhaus'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-05-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Selina')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Christine')),
(SELECT f_id FROM filialen WHERE name='Kopfgasse'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-10-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Franziska')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Margit')),
(SELECT f_id FROM filialen WHERE name='Krankenhaus'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-10-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Christof')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Hans')),
(SELECT f_id FROM filialen WHERE name='Hauptstrasse'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-04-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Michael')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Karl')),
(SELECT f_id FROM filialen WHERE name='Krankenhaus'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-05-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Selina')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Christine')),
(SELECT f_id FROM filialen WHERE name='Kopfgasse'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-10-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Franziska')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Margit')),
(SELECT f_id FROM filialen WHERE name='Krankenhaus'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-10-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Christof')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Hans')),
(SELECT f_id FROM filialen WHERE name='Hauptstrasse'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-04-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Michael')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Karl')),
(SELECT f_id FROM filialen WHERE name='Krankenhaus'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-05-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Selina')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Christine')),
(SELECT f_id FROM filialen WHERE name='Kopfgasse'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-10-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Franziska')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Margit')),
(SELECT f_id FROM filialen WHERE name='Krankenhaus'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-10-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Christof')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Hans')),
(SELECT f_id FROM filialen WHERE name='Hauptstrasse'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-04-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Michael')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Karl')),
(SELECT f_id FROM filialen WHERE name='Krankenhaus'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-05-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Selina')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Christine')),
(SELECT f_id FROM filialen WHERE name='Kopfgasse'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-10-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Franziska')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Margit')),
(SELECT f_id FROM filialen WHERE name='Krankenhaus'));
INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
VALUES ('2016-10-30', '13:00:00',
(SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Christof')),
(SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
(SELECT pers_id FROM personen WHERE vorname='Hans')),
(SELECT f_id FROM filialen WHERE name='Hauptstrasse'));
-- INSERT RECHN_PROD
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Schnitzel'), (SELECT mwst FROM produkte WHERE name='Schnitzel'), 1, (SELECT produkt_id FROM produkte WHERE name='Schnitzel'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Shampoo'), (SELECT mwst FROM produkte WHERE name='Shampoo'), 1, (SELECT produkt_id FROM produkte WHERE name='Shampoo'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Cola'), (SELECT mwst FROM produkte WHERE name='Cola'), 1, (SELECT produkt_id FROM produkte WHERE name='Cola'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Duftbaum'), (SELECT mwst FROM produkte WHERE name='Duftbaum'), 2, (SELECT produkt_id FROM produkte WHERE name='Duftbaum'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Deo'), (SELECT mwst FROM produkte WHERE name='Deo'), 3, (SELECT produkt_id FROM produkte WHERE name='Deo'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 3, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Kartoffeln'), (SELECT mwst FROM produkte WHERE name='Kartoffeln'), 4, (SELECT produkt_id FROM produkte WHERE name='Kartoffeln'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Duftbaum'), (SELECT mwst FROM produkte WHERE name='Duftbaum'), 5, (SELECT produkt_id FROM produkte WHERE name='Duftbaum'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Autobahnvignette'), (SELECT mwst FROM produkte WHERE name='Autobahnvignette'), 5, (SELECT produkt_id FROM produkte WHERE name='Autobahnvignette'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 6, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 7, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 8, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 9, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 10, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 11, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 12, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 13, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 14, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 15, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
Comments