Rodex_

Exercise_3

Apr 5th, 2017
326
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 22.62 KB | None | 0 0
  1. DROP TABLE IF EXISTS "adressen" CASCADE;
  2. DROP TABLE IF EXISTS "allerg_prod" CASCADE;
  3. DROP TABLE IF EXISTS "allergene" CASCADE;
  4. DROP TABLE IF EXISTS "filialen" CASCADE;
  5. DROP TABLE IF EXISTS "personen" CASCADE;
  6. DROP TABLE IF EXISTS "kunden" CASCADE;
  7. DROP TABLE IF EXISTS "mitarbeiter" CASCADE;
  8. DROP TABLE IF EXISTS "produkte" CASCADE;
  9. DROP TABLE IF EXISTS "rechn_prod" CASCADE;
  10. DROP TABLE IF EXISTS "rechnungen" CASCADE;
  11.  
  12. -- 'adressen'
  13. CREATE TABLE adressen
  14. (
  15.     addr_id serial NOT NULL UNIQUE,
  16.     strasse text NOT NULL,
  17.     plz INT NOT NULL,
  18.     ort text NOT NULL,
  19.     hausnummer INT,
  20.     PRIMARY KEY(addr_id)
  21. );
  22.  
  23. -- 'allergene'
  24. CREATE TABLE allergene
  25. (
  26.     kuerzel text NOT NULL UNIQUE,
  27.     bezeichnung text NOT NULL,
  28.     PRIMARY KEY (kuerzel)
  29. );
  30.  
  31. -- 'produkte'
  32. CREATE TABLE produkte
  33. (
  34.     produkt_id serial NOT NULL UNIQUE,
  35.     "name" text NOT NULL,
  36.     preis DECIMAL NOT NULL,
  37.     mwst INT NOT NULL,
  38.     ausgelaufen BOOLEAN NOT NULL DEFAULT FALSE,
  39.     PRIMARY KEY (produkt_id)
  40. );
  41.  
  42. -- 'allerg_prod'
  43. CREATE TABLE allerg_prod
  44. (
  45.     fk_kuerzel text NOT NULL UNIQUE,
  46.     fk_produkt_id serial NOT NULL UNIQUE,
  47.     PRIMARY KEY(fk_kuerzel, fk_produkt_id),
  48.     FOREIGN KEY(fk_kuerzel) REFERENCES allergene(kuerzel),
  49.     FOREIGN KEY(fk_produkt_id) REFERENCES produkte(produkt_id)
  50. );
  51.  
  52. -- 'personen'
  53. CREATE TABLE personen
  54. (
  55.     pers_id serial NOT NULL UNIQUE,
  56.     vorname text,
  57.     nachname text,
  58.     geschlecht text CHECK (geschlecht IN('m','w')),
  59.     fk_addr_id serial,
  60.     PRIMARY KEY (pers_id),
  61.     FOREIGN KEY (fk_addr_id) REFERENCES adressen(addr_id)
  62. );
  63.  
  64. -- 'kunden'
  65. CREATE TABLE kunden
  66. (
  67.     kun_id serial NOT NULL UNIQUE,
  68.     fk_pers_id serial NOT NULL UNIQUE,
  69.     PRIMARY KEY(kun_id),
  70.     FOREIGN KEY(fk_pers_id) REFERENCES personen (pers_id)
  71. );
  72.  
  73. -- 'mitarbeiter'
  74. CREATE TABLE mitarbeiter
  75. (
  76.     fk_pers_id serial NOT NULL UNIQUE,
  77.     eintrittsdatum text NOT NULL,
  78.     austrittsdatum text,
  79.     PRIMARY KEY(fk_pers_id),
  80.     FOREIGN KEY(fk_pers_id) REFERENCES personen (pers_id)
  81. );
  82.  
  83. -- 'filialen'
  84. CREATE TABLE filialen
  85. (
  86.     f_id serial NOT NULL UNIQUE,
  87.     uid INT,
  88.     "name" text NOT NULL,
  89.     telefon INT,
  90.     fax INT,
  91.     fk_addr_id serial NOT NULL UNIQUE,
  92.     geschlossen BOOLEAN NOT NULL DEFAULT FALSE,
  93.     PRIMARY KEY(f_id),
  94.     FOREIGN KEY(fk_addr_id) REFERENCES adressen(addr_id)
  95. );
  96.  
  97. -- 'rechnungen'
  98. CREATE TABLE rechnungen
  99. (
  100.     rechn_nr serial NOT NULL UNIQUE,
  101.     datum DATE NOT NULL,
  102.     uhrzeit TIME NOT NULL,
  103.     fk_f_id serial NOT NULL,
  104.     fk_kunden_pers_id serial,
  105.     fk_mitarbeiter_pers_id serial NOT NULL,
  106.     PRIMARY KEY(rechn_nr),
  107.     FOREIGN KEY(fk_f_id) REFERENCES filialen(f_id),
  108.     FOREIGN KEY(fk_kunden_pers_id) REFERENCES kunden(fk_pers_id),
  109.     FOREIGN KEY(fk_mitarbeiter_pers_id) REFERENCES mitarbeiter(fk_pers_id)    
  110. );
  111.  
  112. -- 'rechn_prod'
  113. CREATE TABLE rechn_prod
  114. (
  115.     "id" serial NOT NULL UNIQUE,
  116.     preis DECIMAL NOT NULL,
  117.     mwst INT NOT NULL,
  118.     fk_prod_id serial,
  119.     fk_rechn_nr serial NOT NULL,
  120.     PRIMARY KEY("id"),
  121.     FOREIGN KEY(fk_prod_id) REFERENCES produkte(produkt_id),
  122.     FOREIGN KEY(fk_rechn_nr) REFERENCES rechnungen(rechn_nr)
  123. );
  124.  
  125. -- insert allergene
  126.  
  127. INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('A', 'Gluten');
  128. INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('B', 'Krebstiere');
  129. INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('C', 'Eier');
  130. INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('D', 'Fisch');
  131. INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('E', 'Erdnüsse');
  132. INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('F', 'Sojabohnen');
  133. INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('G', 'Milch');
  134. INSERT INTO allergene (kuerzel, bezeichnung) VALUES ('H', 'Schalenfruechte');
  135.  
  136. -- insert adressen
  137.  
  138. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Hauptstrasse', 2151, 'Asparn an der Zaya', 24);
  139. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Breunerstrasse', 2130, 'Mistelbach', 43);
  140. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Obere Landstrasse', 2130, 'Mistelbach', 5);
  141. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Krauthof', 2130, 'Mistelbach', 45);
  142. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Hauptplatz', 2130, 'Mistelbach', 75);
  143. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Krankenhaus', 1200, 'Wien', 24);
  144. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Kopfgasse', 1230, 'Wien', 87);
  145. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Krautstrasse', 1010, 'Wien', 7);
  146. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Kartoffelweg', 1010, 'Wien', 66);
  147. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Nasenhaarstrasse', 1030, 'Wien', 57);
  148. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Chinakohl', 1100, 'Wien', 12);
  149. INSERT INTO adressen (strasse, plz, ort, hausnummer) VALUES ('Brandweingasse', 1020, 'Wien', 15);
  150.  
  151. -- insert Filialen
  152.  
  153. INSERT INTO filialen (uid, "name", telefon, fax, geschlossen, fk_addr_id)
  154.     VALUES (24324, 'Hauptstrasse', 4363456, 9741781, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Hauptstrasse'));
  155. INSERT INTO filialen (uid, "name", telefon, fax, geschlossen, fk_addr_id)
  156.     VALUES (23413, 'Krankenhaus', 6346463, 6456456, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Krankenhaus'));
  157. INSERT INTO filialen (uid, "name", telefon, fax, geschlossen, fk_addr_id)
  158.     VALUES (23425, 'Kopfgasse', 7454536, 34674345, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Kopfgasse'));
  159.    
  160. -- insert Personen
  161.  
  162. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Hans', 'Kunz', 'm', (SELECT addr_id FROM adressen WHERE strasse='Chinakohl'));
  163. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Mario', 'Dietrich', 'm', (SELECT addr_id FROM adressen WHERE strasse='Breunerstrasse'));
  164. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Christine', 'Kraus', 'w', (SELECT addr_id FROM adressen WHERE strasse='Krankenhaus'));
  165. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Karl', 'Knuddle', 'm', (SELECT addr_id FROM adressen WHERE strasse='Kopfgasse'));
  166. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Margit', 'Kunz', 'w', (SELECT addr_id FROM adressen WHERE strasse='Chinakohl'));
  167. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Manfred', 'Krakile', 'm', (SELECT addr_id FROM adressen WHERE strasse='Krautstrasse'));
  168. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Charoline', 'Franz', 'w', (SELECT addr_id FROM adressen WHERE strasse='Kartoffelweg'));
  169. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Alexander', 'Dietrich', 'm', (SELECT addr_id FROM adressen WHERE strasse='Nasenhaarstrasse'));
  170. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Christof', 'Kolumbus', 'm', (SELECT addr_id FROM adressen WHERE strasse='Brandweingasse'));
  171. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Franziska', 'Kolumbus', 'w', (SELECT addr_id FROM adressen WHERE strasse='Brandweingasse'));
  172. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Hannes', 'Klampfl', 'm', (SELECT addr_id FROM adressen WHERE strasse='Obere Landstrasse'));
  173. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Selina', 'Brinnich', 'w', (SELECT addr_id FROM adressen WHERE strasse='Hauptplatz'));
  174. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Thiago', 'Gumhold', 'm', (SELECT addr_id FROM adressen WHERE strasse='Hauptplatz'));
  175. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Philipp', 'Schermann', 'm', (SELECT addr_id FROM adressen WHERE strasse='Krankenhaus'));
  176. INSERT INTO personen (vorname, nachname, geschlecht, fk_addr_id) VALUES ('Michael', 'Nemec', 'm', (SELECT addr_id FROM adressen WHERE strasse='Krauthof'));
  177.  
  178. -- INSERT MITARBEITER
  179. INSERT INTO mitarbeiter (eintrittsdatum, fk_pers_id) VALUES ('2016-12-30', (SELECT pers_id FROM personen WHERE vorname='Hans'));
  180. INSERT INTO mitarbeiter (eintrittsdatum, fk_pers_id) VALUES ('2016-03-15', (SELECT pers_id FROM personen WHERE vorname='Mario'));
  181. INSERT INTO mitarbeiter (eintrittsdatum, fk_pers_id) VALUES ('2016-02-01', (SELECT pers_id FROM personen WHERE vorname='Christine'));
  182. INSERT INTO mitarbeiter (eintrittsdatum, fk_pers_id) VALUES ('2016-12-26', (SELECT pers_id FROM personen WHERE vorname='Karl'));
  183. INSERT INTO mitarbeiter (eintrittsdatum, fk_pers_id) VALUES ('2016-12-27', (SELECT pers_id FROM personen WHERE vorname='Margit'));
  184.  
  185. -- INSERT KUNDEN
  186. INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Manfred'));
  187. INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Charoline'));
  188. INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Alexander'));
  189. INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Christof'));
  190. INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Franziska'));
  191. INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Hannes'));
  192. INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Selina'));
  193. INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Thiago'));
  194. INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Philipp'));
  195. INSERT INTO kunden (fk_pers_id) VALUES ((SELECT pers_id FROM personen WHERE vorname='Michael'));
  196.  
  197. -- INSERT PRODUKTE
  198. INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Schnitzel', 9.50, 10, FALSE);
  199. INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Schweinsbraten', 13.50, 10, FALSE);
  200. INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Deo', 5.30, 20, FALSE);
  201. INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Shampoo', 2.50, 20, FALSE);
  202. INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Magazin', 4.50, 10, FALSE);
  203. INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Cola', 3.50, 10, FALSE);
  204. INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Kartoffeln', 2.50, 10, FALSE);
  205. INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Autobahnvignette', 89.90, 20, FALSE);
  206. INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Scheibenwischer', 12.50, 20, FALSE);
  207. INSERT INTO produkte (name, preis, mwst, ausgelaufen) VALUES ('Duftbaum', 5.00, 20, FALSE);
  208.  
  209. -- INSERT RECHNUNGEN
  210. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  211.     VALUES ('2016-04-30', '13:00:00',
  212.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  213.              (SELECT pers_id FROM personen WHERE vorname='Michael')),
  214.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  215.              (SELECT pers_id FROM personen WHERE vorname='Karl')),
  216.             (SELECT f_id FROM filialen WHERE name='Krankenhaus'));
  217.  
  218. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  219.     VALUES ('2016-05-30', '13:00:00',
  220.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  221.              (SELECT pers_id FROM personen WHERE vorname='Selina')),
  222.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  223.              (SELECT pers_id FROM personen WHERE vorname='Christine')),
  224.             (SELECT f_id FROM filialen WHERE name='Kopfgasse'));
  225.            
  226. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  227.     VALUES ('2016-10-30', '13:00:00',
  228.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  229.              (SELECT pers_id FROM personen WHERE vorname='Franziska')),
  230.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  231.              (SELECT pers_id FROM personen WHERE vorname='Margit')),
  232.             (SELECT f_id FROM filialen WHERE name='Krankenhaus'));
  233.  
  234. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  235.     VALUES ('2016-10-30', '13:00:00',
  236.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  237.              (SELECT pers_id FROM personen WHERE vorname='Christof')),
  238.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  239.              (SELECT pers_id FROM personen WHERE vorname='Hans')),
  240.             (SELECT f_id FROM filialen WHERE name='Hauptstrasse'));
  241.            
  242. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  243.     VALUES ('2016-04-30', '13:00:00',
  244.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  245.              (SELECT pers_id FROM personen WHERE vorname='Michael')),
  246.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  247.              (SELECT pers_id FROM personen WHERE vorname='Karl')),
  248.             (SELECT f_id FROM filialen WHERE name='Krankenhaus'));
  249.  
  250. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  251.     VALUES ('2016-05-30', '13:00:00',
  252.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  253.              (SELECT pers_id FROM personen WHERE vorname='Selina')),
  254.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  255.              (SELECT pers_id FROM personen WHERE vorname='Christine')),
  256.             (SELECT f_id FROM filialen WHERE name='Kopfgasse'));
  257.            
  258. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  259.     VALUES ('2016-10-30', '13:00:00',
  260.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  261.              (SELECT pers_id FROM personen WHERE vorname='Franziska')),
  262.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  263.              (SELECT pers_id FROM personen WHERE vorname='Margit')),
  264.             (SELECT f_id FROM filialen WHERE name='Krankenhaus'));
  265.  
  266. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  267.     VALUES ('2016-10-30', '13:00:00',
  268.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  269.              (SELECT pers_id FROM personen WHERE vorname='Christof')),
  270.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  271.              (SELECT pers_id FROM personen WHERE vorname='Hans')),
  272.             (SELECT f_id FROM filialen WHERE name='Hauptstrasse'));
  273.  
  274. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  275.     VALUES ('2016-04-30', '13:00:00',
  276.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  277.              (SELECT pers_id FROM personen WHERE vorname='Michael')),
  278.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  279.              (SELECT pers_id FROM personen WHERE vorname='Karl')),
  280.             (SELECT f_id FROM filialen WHERE name='Krankenhaus'));
  281.  
  282. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  283.     VALUES ('2016-05-30', '13:00:00',
  284.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  285.              (SELECT pers_id FROM personen WHERE vorname='Selina')),
  286.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  287.              (SELECT pers_id FROM personen WHERE vorname='Christine')),
  288.             (SELECT f_id FROM filialen WHERE name='Kopfgasse'));
  289.            
  290. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  291.     VALUES ('2016-10-30', '13:00:00',
  292.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  293.              (SELECT pers_id FROM personen WHERE vorname='Franziska')),
  294.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  295.              (SELECT pers_id FROM personen WHERE vorname='Margit')),
  296.             (SELECT f_id FROM filialen WHERE name='Krankenhaus'));
  297.  
  298. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  299.     VALUES ('2016-10-30', '13:00:00',
  300.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  301.              (SELECT pers_id FROM personen WHERE vorname='Christof')),
  302.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  303.              (SELECT pers_id FROM personen WHERE vorname='Hans')),
  304.             (SELECT f_id FROM filialen WHERE name='Hauptstrasse'));
  305.  
  306. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  307.     VALUES ('2016-04-30', '13:00:00',
  308.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  309.              (SELECT pers_id FROM personen WHERE vorname='Michael')),
  310.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  311.              (SELECT pers_id FROM personen WHERE vorname='Karl')),
  312.             (SELECT f_id FROM filialen WHERE name='Krankenhaus'));
  313.  
  314. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  315.     VALUES ('2016-05-30', '13:00:00',
  316.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  317.              (SELECT pers_id FROM personen WHERE vorname='Selina')),
  318.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  319.              (SELECT pers_id FROM personen WHERE vorname='Christine')),
  320.             (SELECT f_id FROM filialen WHERE name='Kopfgasse'));
  321.            
  322. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  323.     VALUES ('2016-10-30', '13:00:00',
  324.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  325.              (SELECT pers_id FROM personen WHERE vorname='Franziska')),
  326.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  327.              (SELECT pers_id FROM personen WHERE vorname='Margit')),
  328.             (SELECT f_id FROM filialen WHERE name='Krankenhaus'));
  329.  
  330. INSERT INTO rechnungen (datum, uhrzeit, fk_kunden_pers_id, fk_mitarbeiter_pers_id, fk_f_id)
  331.     VALUES ('2016-10-30', '13:00:00',
  332.             (SELECT fk_pers_id FROM kunden WHERE fk_pers_id=
  333.              (SELECT pers_id FROM personen WHERE vorname='Christof')),
  334.             (SELECT fk_pers_id FROM mitarbeiter WHERE fk_pers_id=
  335.              (SELECT pers_id FROM personen WHERE vorname='Hans')),
  336.             (SELECT f_id FROM filialen WHERE name='Hauptstrasse'));
  337.            
  338. -- INSERT RECHN_PROD
  339. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  340.     VALUES ((SELECT preis FROM produkte WHERE name='Schnitzel'), (SELECT mwst FROM produkte WHERE name='Schnitzel'), 1, (SELECT produkt_id FROM produkte WHERE name='Schnitzel'));
  341. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  342.     VALUES ((SELECT preis FROM produkte WHERE name='Shampoo'), (SELECT mwst FROM produkte WHERE name='Shampoo'), 1, (SELECT produkt_id FROM produkte WHERE name='Shampoo'));
  343. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  344.     VALUES ((SELECT preis FROM produkte WHERE name='Cola'), (SELECT mwst FROM produkte WHERE name='Cola'), 1, (SELECT produkt_id FROM produkte WHERE name='Cola'));
  345. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  346.     VALUES ((SELECT preis FROM produkte WHERE name='Duftbaum'), (SELECT mwst FROM produkte WHERE name='Duftbaum'), 2, (SELECT produkt_id FROM produkte WHERE name='Duftbaum'));
  347. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  348.     VALUES ((SELECT preis FROM produkte WHERE name='Deo'), (SELECT mwst FROM produkte WHERE name='Deo'), 3, (SELECT produkt_id FROM produkte WHERE name='Deo'));
  349. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  350.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 3, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
  351. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  352.     VALUES ((SELECT preis FROM produkte WHERE name='Kartoffeln'), (SELECT mwst FROM produkte WHERE name='Kartoffeln'), 4, (SELECT produkt_id FROM produkte WHERE name='Kartoffeln'));
  353. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  354.     VALUES ((SELECT preis FROM produkte WHERE name='Duftbaum'), (SELECT mwst FROM produkte WHERE name='Duftbaum'), 5, (SELECT produkt_id FROM produkte WHERE name='Duftbaum'));
  355. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  356.     VALUES ((SELECT preis FROM produkte WHERE name='Autobahnvignette'), (SELECT mwst FROM produkte WHERE name='Autobahnvignette'), 5, (SELECT produkt_id FROM produkte WHERE name='Autobahnvignette'));
  357. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  358.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 6, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
  359. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  360.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 7, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
  361. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  362.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 8, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
  363. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  364.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 9, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
  365. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  366.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 10, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
  367. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  368.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 11, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
  369. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  370.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 12, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
  371. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  372.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 13, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
  373. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  374.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 14, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
  375. INSERT INTO rechn_prod (preis, mwst, fk_rechn_nr, fk_prod_id)
  376.     VALUES ((SELECT preis FROM produkte WHERE name='Scheibenwischer'), (SELECT mwst FROM produkte WHERE name='Scheibenwischer'), 15, (SELECT produkt_id FROM produkte WHERE name='Scheibenwischer'));
Advertisement
Add Comment
Please, Sign In to add comment