Rodex_

Exercise_4

Apr 22nd, 2017
342
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 15.31 KB | None | 0 0
  1.  
  2. -- -----------------------------------------------------
  3. -- Schema toprast
  4. -- -----------------------------------------------------
  5. DROP TABLE IF EXISTS "allerg_prod" ;
  6. DROP TABLE IF EXISTS "allergene" ;
  7. DROP TABLE IF EXISTS "rechn_prod" ;
  8. DROP TABLE IF EXISTS "produkte" ;
  9. DROP TABLE IF EXISTS "rechnungen" ;
  10. DROP TABLE IF EXISTS "filialen" ;
  11. DROP TABLE IF EXISTS "kunden" ;
  12. DROP TABLE IF EXISTS "mitarbeiter" ;
  13. DROP TABLE IF EXISTS "personen" ;
  14. DROP TABLE IF EXISTS "adressen" ;
  15.  
  16. -- -----------------------------------------------------
  17. -- Table "adressen"
  18. -- -----------------------------------------------------
  19.  
  20. CREATE TABLE IF NOT EXISTS "adressen" (
  21.   "addr_id" SERIAL NOT NULL,
  22.   "strasse" VARCHAR(64) NOT NULL,
  23.   "plz" VARCHAR(8) NOT NULL,
  24.   "ort" VARCHAR(64) NOT NULL,
  25.   "hausnummer" VARCHAR(10) NULL,
  26.   PRIMARY KEY ("addr_id"))
  27. ;
  28.  
  29.  
  30. -- -----------------------------------------------------
  31. -- Table "personen"
  32. -- -----------------------------------------------------
  33.  
  34. CREATE TABLE IF NOT EXISTS "personen" (
  35.   "pers_id" SERIAL NOT NULL,
  36.   "vorname" VARCHAR(45) NULL,
  37.   "nachname" VARCHAR(45) NULL,
  38.   "geschlecht" CHAR(1) CHECK (geschlecht IN('m','w')),
  39.   "fk_addr_id" INT NULL,
  40.   PRIMARY KEY ("pers_id"),
  41.   CONSTRAINT "fk_personen_adressen"
  42.     FOREIGN KEY ("fk_addr_id")
  43.     REFERENCES "adressen" ("addr_id")
  44.     ON DELETE SET NULL
  45.     ON UPDATE SET NULL)
  46. ;
  47.  
  48.  
  49. -- -----------------------------------------------------
  50. -- Table "kunden"
  51. -- -----------------------------------------------------
  52.  
  53. CREATE TABLE IF NOT EXISTS "kunden" (
  54.   "fk_pers_id" INT NOT NULL,
  55.   PRIMARY KEY ("fk_pers_id"),
  56.   CONSTRAINT "fk_kunden_personen1"
  57.     FOREIGN KEY ("fk_pers_id")
  58.     REFERENCES "personen" ("pers_id")
  59.     ON DELETE CASCADE
  60.     ON UPDATE CASCADE)
  61. ;
  62.  
  63.  
  64. -- -----------------------------------------------------
  65. -- Table "filialen"
  66. -- -----------------------------------------------------
  67.  
  68. CREATE TABLE IF NOT EXISTS "filialen" (
  69.   "f_id" SERIAL NOT NULL,
  70.   "uid" VARCHAR(12) NULL,
  71.   "name" VARCHAR(45) NOT NULL,
  72.   "telefon" VARCHAR(45) NULL,
  73.   "fax" VARCHAR(45) NULL,
  74.   "fk_addr_id" INT NOT NULL,
  75.   "geschlossen" BOOLEAN NOT NULL DEFAULT FALSE,
  76.   PRIMARY KEY ("f_id"),
  77.   CONSTRAINT "fk_filialen_adressen1"
  78.     FOREIGN KEY ("fk_addr_id")
  79.     REFERENCES "adressen" ("addr_id")
  80.     ON DELETE NO ACTION
  81.     ON UPDATE NO ACTION)
  82. ;
  83.  
  84.  
  85. -- -----------------------------------------------------
  86. -- Table "mitarbeiter"
  87. -- -----------------------------------------------------
  88.  
  89. CREATE TABLE IF NOT EXISTS "mitarbeiter" (
  90.   "fk_pers_id" INT NOT NULL,
  91.   "eintrittsdatum" DATE NOT NULL,
  92.   "austrittsdatum" DATE NULL,
  93.   PRIMARY KEY ("fk_pers_id"),
  94.   CONSTRAINT "fk_mitarbeiter_personen1"
  95.     FOREIGN KEY ("fk_pers_id")
  96.     REFERENCES "personen" ("pers_id")
  97.     ON DELETE NO ACTION
  98.     ON UPDATE NO ACTION)
  99. ;
  100.  
  101.  
  102. -- -----------------------------------------------------
  103. -- Table "rechnungen"
  104. -- -----------------------------------------------------
  105.  
  106. CREATE TABLE IF NOT EXISTS "rechnungen" (
  107.   "rechn_nr" SERIAL NOT NULL,
  108.   "datum" DATE NOT NULL,
  109.   "uhrzeit" TIME NOT NULL,
  110.   "fk_f_id" INT NOT NULL,
  111.   "fk_kunden_pers_id" INT NULL,
  112.   "fk_mitarbeiter_pers_id" INT NULL,
  113.   PRIMARY KEY ("rechn_nr"),
  114.   CONSTRAINT "fk_rechnungen_filialen1"
  115.     FOREIGN KEY ("fk_f_id")
  116.     REFERENCES "filialen" ("f_id")
  117.     ON DELETE NO ACTION
  118.     ON UPDATE NO ACTION,
  119.   CONSTRAINT "fk_rechnungen_kunden1"
  120.     FOREIGN KEY ("fk_kunden_pers_id")
  121.     REFERENCES "kunden" ("fk_pers_id")
  122.     ON DELETE SET NULL
  123.     ON UPDATE SET NULL,
  124.   CONSTRAINT "fk_rechnungen_mitarbeiter1"
  125.     FOREIGN KEY ("fk_mitarbeiter_pers_id")
  126.     REFERENCES "mitarbeiter" ("fk_pers_id")
  127.     ON DELETE SET NULL
  128.     ON UPDATE SET NULL)
  129. ;
  130.  
  131.  
  132. -- -----------------------------------------------------
  133. -- Table "produkte"
  134. -- -----------------------------------------------------
  135.  
  136. CREATE TABLE IF NOT EXISTS "produkte" (
  137.   "produkt_id" SERIAL NOT NULL,
  138.   "name" VARCHAR(64) NOT NULL,
  139.   "preis" REAL NOT NULL,
  140.   "mwst" REAL NOT NULL,
  141.   "ausgelaufen" BOOLEAN NOT NULL DEFAULT FALSE,
  142.   PRIMARY KEY ("produkt_id"))
  143. ;
  144.  
  145.  
  146. -- -----------------------------------------------------
  147. -- Table "rechn_prod"
  148. -- -----------------------------------------------------
  149.  
  150. CREATE TABLE IF NOT EXISTS "rechn_prod" (
  151.   "id" SERIAL NOT NULL,
  152.   "preis" REAL NOT NULL,
  153.   "mwst" REAL NOT NULL,
  154.   "fk_prod_id" INT NULL,
  155.   "fk_rechn_nr" INT NOT NULL,
  156.   PRIMARY KEY ("id"),
  157.   CONSTRAINT "fk_rechn_prod_rechnungen1"
  158.     FOREIGN KEY ("fk_rechn_nr")
  159.     REFERENCES "rechnungen" ("rechn_nr")
  160.     ON DELETE CASCADE
  161.     ON UPDATE CASCADE,
  162.   CONSTRAINT "fk_rechn_prod_produkte1"
  163.     FOREIGN KEY ("fk_prod_id")
  164.     REFERENCES "produkte" ("produkt_id")
  165.     ON DELETE SET NULL
  166.     ON UPDATE SET NULL)
  167. ;
  168.  
  169.  
  170. -- -----------------------------------------------------
  171. -- Table "allergene"
  172. -- -----------------------------------------------------
  173.  
  174. CREATE TABLE IF NOT EXISTS "allergene" (
  175.   "kuerzel" CHAR(1) NOT NULL,
  176.   "bezeichnung" VARCHAR(64) NOT NULL,
  177.   PRIMARY KEY ("kuerzel"))
  178. ;
  179.  
  180.  
  181. -- -----------------------------------------------------
  182. -- Table "allerg_prod"
  183. -- -----------------------------------------------------
  184.  
  185. CREATE TABLE IF NOT EXISTS "allerg_prod" (
  186.   "fk_kuerzel" CHAR(1) NOT NULL,
  187.   "fk_produkt_id" INT NOT NULL,
  188.   PRIMARY KEY ("fk_kuerzel", "fk_produkt_id"),
  189.   CONSTRAINT "fk_allerg_prod_allergene1"
  190.     FOREIGN KEY ("fk_kuerzel")
  191.     REFERENCES "allergene" ("kuerzel")
  192.     ON DELETE CASCADE
  193.     ON UPDATE CASCADE,
  194.   CONSTRAINT "fk_allerg_prod_produkte1"
  195.     FOREIGN KEY ("fk_produkt_id")
  196.     REFERENCES "produkte" ("produkt_id")
  197.     ON DELETE CASCADE
  198.     ON UPDATE CASCADE)
  199. ;
  200.  
  201. -- -----------------------------------------------------
  202. -- Table "vornamen"
  203. -- -----------------------------------------------------
  204. CREATE TABLE IF NOT EXISTS "vornamen" (
  205.   "vn_id" serial,
  206.   "vorname" text NOT NULL,
  207.   "geschlecht" CHAR(1) CHECK (geschlecht IN('m','w')),
  208.   PRIMARY KEY ("vn_id")
  209. );
  210.  
  211. -- -----------------------------------------------------
  212. -- Table "nachnamen"
  213. -- -----------------------------------------------------
  214. CREATE TABLE IF NOT EXISTS "nachnamen" (
  215.   "nn_id" serial,
  216.   "nachname" text NOT NULL,
  217.   PRIMARY KEY ("nn_id")
  218. );
  219.  
  220. -- -------------------------------------------------------------------------------------------------------
  221. -- Inserts
  222. -- -------------------------------------------------------------------------------------------------------
  223.  
  224. -- insert nachnamen
  225.  
  226. INSERT INTO vornamen ("vn_id", "vorname", "geschlecht") VALUES
  227. (DEFAULT, 'Liam', 'm'),
  228. (DEFAULT, 'Milan', 'm'),
  229. (DEFAULT, 'Elias', 'm'),
  230. (DEFAULT, 'Julian', 'm'),
  231. (DEFAULT, 'Levi', 'm'),
  232. (DEFAULT, 'Elias', 'm'),
  233. (DEFAULT, 'Henry', 'm'),
  234. (DEFAULT, 'Oskar', 'm'),
  235. (DEFAULT, 'Sven', 'm'),
  236. (DEFAULT, 'Levi', 'm'),
  237. (DEFAULT, 'Hannes', 'm'),
  238. (DEFAULT, 'Artur', 'm'),
  239. (DEFAULT, 'Noel', 'm'),
  240. (DEFAULT, 'Pascal', 'm'),
  241. (DEFAULT, 'Maurice', 'm'),
  242. (DEFAULT, 'David', 'm'),
  243. (DEFAULT, 'Jan', 'm'),
  244. (DEFAULT, 'Anton', 'm'),
  245. (DEFAULT, 'Lars', 'm'),
  246. (DEFAULT, 'Lias', 'm'),
  247. (DEFAULT, 'Jeremy', 'm'),
  248. (DEFAULT, 'Jonathan', 'm'),
  249. (DEFAULT, 'Tim', 'm'),
  250. (DEFAULT, 'Liam', 'm'),
  251. (DEFAULT, 'Tjark', 'm'),
  252. (DEFAULT, 'Carl', 'm'),
  253. (DEFAULT, 'Finn', 'm'),
  254. (DEFAULT, 'Luca', 'm'),
  255. (DEFAULT, 'Jerome', 'm'),
  256. (DEFAULT, 'Joey', 'm'),
  257. (DEFAULT, 'Felix', 'm'),
  258. (DEFAULT, 'Mika', 'm'),
  259. (DEFAULT, 'Ben', 'm'),
  260. (DEFAULT, 'Mauricio', 'm'),
  261. (DEFAULT, 'Emil', 'm'),
  262. (DEFAULT, 'Johannes', 'm'),
  263. (DEFAULT, 'Mattis', 'm'),
  264. (DEFAULT, 'Liam', 'm'),
  265. (DEFAULT, 'Fabian', 'm'),
  266. (DEFAULT, 'Jaden', 'm'),
  267. (DEFAULT, 'Vincent', 'm'),
  268. (DEFAULT, 'Nils', 'm'),
  269. (DEFAULT, 'Daniel', 'm'),
  270. (DEFAULT, 'Jonas', 'm'),
  271. (DEFAULT, 'Benjamin', 'm'),
  272. (DEFAULT, 'Jacob', 'm'),
  273. (DEFAULT, 'Michael', 'm'),
  274. (DEFAULT, 'Timo', 'm'),
  275. (DEFAULT, 'Julien', 'm'),
  276. (DEFAULT, 'Alexander', 'm'),
  277. (DEFAULT, 'Jette', 'w'),
  278. (DEFAULT, 'Fiona', 'w'),
  279. (DEFAULT, 'Lena', 'w'),
  280. (DEFAULT, 'Stella', 'w'),
  281. (DEFAULT, 'Ida', 'w'),
  282. (DEFAULT, 'Lotta', 'w'),
  283. (DEFAULT, 'Alina', 'w'),
  284. (DEFAULT, 'Julia', 'w'),
  285. (DEFAULT, 'Marie', 'w'),
  286. (DEFAULT, 'Finja', 'w'),
  287. (DEFAULT, 'Anna', 'w'),
  288. (DEFAULT, 'Amalia', 'w'),
  289. (DEFAULT, 'Lucia', 'w'),
  290. (DEFAULT, 'Chiara', 'w'),
  291. (DEFAULT, 'Merle', 'w'),
  292. (DEFAULT, 'Fabienne', 'w'),
  293. (DEFAULT, 'Jael', 'w'),
  294. (DEFAULT, 'Elin', 'w'),
  295. (DEFAULT, 'Alicia', 'w'),
  296. (DEFAULT, 'Leona', 'w'),
  297. (DEFAULT, 'Dior', 'w'),
  298. (DEFAULT, 'Dariah', 'w'),
  299. (DEFAULT, 'Johanna', 'w'),
  300. (DEFAULT, 'Angelina', 'w'),
  301. (DEFAULT, 'Joenna', 'w'),
  302. (DEFAULT, 'Laura', 'w'),
  303. (DEFAULT, 'Celina', 'w'),
  304. (DEFAULT, 'Joy', 'w'),
  305. (DEFAULT, 'Malina', 'w'),
  306. (DEFAULT, 'Charlott', 'w'),
  307. (DEFAULT, 'Paula', 'w'),
  308. (DEFAULT, 'Annika', 'w'),
  309. (DEFAULT, 'Paulina', 'w'),
  310. (DEFAULT, 'Kyara', 'w'),
  311. (DEFAULT, 'Carla', 'w'),
  312. (DEFAULT, 'Emilia', 'w'),
  313. (DEFAULT, 'Jana', 'w'),
  314. (DEFAULT, 'Emily ', 'w'),
  315. (DEFAULT, 'Mira', 'w'),
  316. (DEFAULT, 'Nele', 'w'),
  317. (DEFAULT, 'Leana', 'w'),
  318. (DEFAULT, 'Aurelia', 'w'),
  319. (DEFAULT, 'Sandra', 'w'),
  320. (DEFAULT, 'Elisa', 'w'),
  321. (DEFAULT, 'Aurelija', 'w'),
  322. (DEFAULT, 'Noemi', 'w'),
  323. (DEFAULT, 'Sophia', 'w'),
  324. (DEFAULT, 'Maxima', 'w'),
  325. (DEFAULT, 'Allegra', 'w'),
  326. (DEFAULT, 'Carolina', 'w');
  327.  
  328.  
  329. -- insert vornamen
  330.  
  331. INSERT INTO nachnamen ("nn_id", "nachname") VALUES
  332. (DEFAULT, 'Müller'),
  333. (DEFAULT, 'Schmidt'),
  334. (DEFAULT, 'Schneider'),
  335. (DEFAULT, 'Fischer'),
  336. (DEFAULT, 'Meyer'),
  337. (DEFAULT, 'Weber'),
  338. (DEFAULT, 'Wagner'),
  339. (DEFAULT, 'Becker'),
  340. (DEFAULT, 'Schulz'),
  341. (DEFAULT, 'Hoffmann'),
  342. (DEFAULT, 'Schäfer'),
  343. (DEFAULT, 'Koch'),
  344. (DEFAULT, 'Bauer'),
  345. (DEFAULT, 'Richter'),
  346. (DEFAULT, 'Klein'),
  347. (DEFAULT, 'Schröder'),
  348. (DEFAULT, 'Wolf'),
  349. (DEFAULT, 'Neumann'),
  350. (DEFAULT, 'Schwarz'),
  351. (DEFAULT, 'Zimmermann'),
  352. (DEFAULT, 'Krüger'),
  353. (DEFAULT, 'Braun'),
  354. (DEFAULT, 'Hofmann'),
  355. (DEFAULT, 'Schmitz'),
  356. (DEFAULT, 'Hartmann'),
  357. (DEFAULT, 'Lange'),
  358. (DEFAULT, 'Schmitt'),
  359. (DEFAULT, 'Werner'),
  360. (DEFAULT, 'Krause'),
  361. (DEFAULT, 'Meier'),
  362. (DEFAULT, 'Schmid'),
  363. (DEFAULT, 'Lehmann'),
  364. (DEFAULT, 'Schulze'),
  365. (DEFAULT, 'Maier'),
  366. (DEFAULT, 'Köhler'),
  367. (DEFAULT, 'Herrmann'),
  368. (DEFAULT, 'Walter'),
  369. (DEFAULT, 'Körtig'),
  370. (DEFAULT, 'Mayer'),
  371. (DEFAULT, 'Huber'),
  372. (DEFAULT, 'Kaiser'),
  373. (DEFAULT, 'Fuchs'),
  374. (DEFAULT, 'Peters'),
  375. (DEFAULT, 'Möller'),
  376. (DEFAULT, 'Scholz'),
  377. (DEFAULT, 'Lang'),
  378. (DEFAULT, 'Weiß'),
  379. (DEFAULT, 'Jung'),
  380. (DEFAULT, 'Hahn'),
  381. (DEFAULT, 'Vogel'),
  382. (DEFAULT, 'Friedrich'),
  383. (DEFAULT, 'Günther'),
  384. (DEFAULT, 'Keller'),
  385. (DEFAULT, 'Schubert'),
  386. (DEFAULT, 'Berger'),
  387. (DEFAULT, 'Frank'),
  388. (DEFAULT, 'Roth'),
  389. (DEFAULT, 'Beck'),
  390. (DEFAULT, 'Winkler'),
  391. (DEFAULT, 'Lorenz'),
  392. (DEFAULT, 'Baumann'),
  393. (DEFAULT, 'Albrecht'),
  394. (DEFAULT, 'Ludwig'),
  395. (DEFAULT, 'Franke'),
  396. (DEFAULT, 'Simon'),
  397. (DEFAULT, 'Böhm'),
  398. (DEFAULT, 'Schuster'),
  399. (DEFAULT, 'Schumacher'),
  400. (DEFAULT, 'Kraus'),
  401. (DEFAULT, 'Winter'),
  402. (DEFAULT, 'Otto'),
  403. (DEFAULT, 'Krämer'),
  404. (DEFAULT, 'Stein'),
  405. (DEFAULT, 'Vogt'),
  406. (DEFAULT, 'Martin'),
  407. (DEFAULT, 'Jäger'),
  408. (DEFAULT, 'Groß'),
  409. (DEFAULT, 'Sommer'),
  410. (DEFAULT, 'Brandt'),
  411. (DEFAULT, 'Haas'),
  412. (DEFAULT, 'Heinrich'),
  413. (DEFAULT, 'Seidel'),
  414. (DEFAULT, 'Schreiber'),
  415. (DEFAULT, 'Schulte'),
  416. (DEFAULT, 'Graf'),
  417. (DEFAULT, 'Dietrich'),
  418. (DEFAULT, 'Ziegler'),
  419. (DEFAULT, 'Engel'),
  420. (DEFAULT, 'Kühn'),
  421. (DEFAULT, 'Kuhn'),
  422. (DEFAULT, 'Pohl'),
  423. (DEFAULT, 'Horn'),
  424. (DEFAULT, 'Thomas'),
  425. (DEFAULT, 'Busch'),
  426. (DEFAULT, 'Wolff'),
  427. (DEFAULT, 'Sauer'),
  428. (DEFAULT, 'Bergmann'),
  429. (DEFAULT, 'Pfeiffer'),
  430. (DEFAULT, 'Voigt'),
  431. (DEFAULT, 'Ernst');
  432.  
  433.  
  434. -- insert Produkte
  435.  
  436. INSERT INTO produkte ("produkt_id", "name", "preis", "mwst", "ausgelaufen") VALUES
  437. (DEFAULT, 'Schnitzel', 9.50, 10, FALSE),
  438. (DEFAULT, 'Schweinsbraten', 13.50, 10, FALSE),
  439. (DEFAULT, 'Deo', 5.30, 20, FALSE),
  440. (DEFAULT, 'Shampoo', 2.50, 20, FALSE),
  441. (DEFAULT, 'Magazin', 4.50, 10, FALSE),
  442. (DEFAULT, 'Cola', 3.50, 10, FALSE),
  443. (DEFAULT, 'Kartoffeln', 2.50, 10, FALSE),
  444. (DEFAULT, 'Autobahnvignette', 89.90, 20, FALSE),
  445. (DEFAULT, 'Scheibenwischer', 12.50, 20, FALSE),
  446. (DEFAULT, 'Duftbaum', 5.00, 20, FALSE);
  447.  
  448.  
  449. -- insert allergene
  450.  
  451. INSERT INTO allergene ("kuerzel", "bezeichnung") VALUES
  452. ('A','Glutenhältige Getreide'),
  453. ('B','Krebstiere (Krustentiere bzw. Crustaceae)'),
  454. ('C','Aus Ei hergestellte Produkte'),
  455. ('D','Fisch'),
  456. ('E','Erdnüsse'),
  457. ('F','Soja'),
  458. ('G','Milch (einschließlich Laktose)'),
  459. ('H','Schalenfrüchte (Nüsse)'),
  460. ('L','Sellerie'),
  461. ('M','Senf'),
  462. ('N','Sesam'),
  463. ('O','Schwefeldioxid und Sulphit'),
  464. ('P','Lupinen'),
  465. ('R','Weichtiere (Mollusken)');
  466.  
  467.  
  468. -- insert allerg_prod
  469.  
  470. INSERT INTO allerg_prod ("fk_kuerzel", "fk_produkt_id") VALUES
  471. ('A', 3),
  472. ('A', 6),
  473. ('A', 7),
  474. ('C', 3),
  475. ('C', 5),
  476. ('C', 9),
  477. ('E', 9),
  478. ('G', 9),
  479. ('H', 9),
  480. ('C', 6),
  481. ('M', 6);
  482.  
  483.  
  484. -- insert adressen
  485.  
  486. INSERT INTO adressen ("strasse", "plz", "ort", "hausnummer") VALUES
  487. ('Hauptstrasse', 2151, 'Asparn an der Zaya', 24),
  488. ('Breunerstrasse', 2130, 'Mistelbach', 43),
  489. ('Obere Landstrasse', 2130, 'Mistelbach', 5),
  490. ('Krauthof', 2130, 'Mistelbach', 45),
  491. ('Hauptplatz', 2130, 'Mistelbach', 75),
  492. ('Krankenhaus', 1200, 'Wien', 24),
  493. ('Kopfgasse', 1230, 'Wien', 87),
  494. ('Krautstrasse', 1010, 'Wien', 7),
  495. ('Kartoffelweg', 1010, 'Wien', 66),
  496. ('Nasenhaarstrasse', 1030, 'Wien', 57),
  497. ('Chinakohl', 1100, 'Wien', 12),
  498. ('Brandweingasse', 1020, 'Wien', 15);
  499.  
  500.  
  501. -- insert filialen
  502. INSERT INTO filialen ("uid", "name", "telefon", "fax", "geschlossen", "fk_addr_id")
  503.     VALUES (24324, 'Hauptstrasse', 4363456, 9741781, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Hauptstrasse'));
  504. INSERT INTO filialen ("uid", "name", "telefon", "fax", "geschlossen", "fk_addr_id")
  505.     VALUES (23413, 'Krankenhaus', 6346463, 6456456, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Krankenhaus'));
  506. INSERT INTO filialen ("uid", "name", "telefon", "fax", "geschlossen", "fk_addr_id")
  507.     VALUES (23425, 'Kopfgasse', 7454536, 34674345, FALSE, (SELECT addr_id FROM adressen WHERE strasse='Kopfgasse'));
  508.  
  509.  
  510. -- -------------------------------------------------------------------
  511. -- Insert selects:
  512. -- -------------------------------------------------------------------
  513.  
  514. INSERT INTO personen (vorname, nachname, geschlecht)
  515. SELECT v.vorname, n.nachname, v.geschlecht
  516. FROM vornamen v, nachnamen n;
  517.  
  518. INSERT INTO mitarbeiter (fk_pers_id , eintrittsdatum, austrittsdatum)
  519. SELECT per.pers_id,
  520.     to_date(concat('201', round(random()*7), '-', to_char(round(random()*12), 'fm00'), '-' , to_char(round(random()*28), 'fm00')), 'YYYY-MM-DD'),
  521.     NULLIF (to_date(concat('2017-01-2' , round(random()*0.57)), 'YYYY-MM-DD'), to_date('2017-01-20', 'YYYY-MM-DD'))
  522. FROM personen per
  523. WHERE pers_id % 201 = 0;
  524.  
  525. INSERT INTO kunden (fk_pers_id)
  526. SELECT per.pers_id
  527. FROM personen per
  528. WHERE pers_id % 3 != 0 OR pers_id % 5 != 0 OR pers_id % 7 != 0;
  529.  
  530. INSERT INTO rechnungen (datum, uhrzeit, fk_f_id, fk_kunden_pers_id, fk_mitarbeiter_pers_id)
  531. SELECT to_date(concat('201', FLOOR((random()*7.5)), '-', to_char(round(random()*12), 'fm00'), '-' , to_char(round(random()*28), 'fm00')), 'YYYY-MM-DD'),
  532. '23:59:59',
  533. 1, -- Dummyfiliale
  534. kun.fk_pers_id, -- kundennummer
  535. (SELECT mi.fk_pers_id FROM mitarbeiter mi ORDER BY random() LIMIT 1)
  536. FROM kunden kun;
  537.  
  538. INSERT INTO  rechn_prod (preis, mwst, fk_prod_id, fk_rechn_nr)
  539. SELECT pr.preis * round(random()*10), pr.mwst, pr.produkt_id, r.rechn_nr
  540. FROM produkte pr, rechnungen r;
  541.  
  542. -- -------------------------------------------------------------------
  543. -- drop temporary tables
  544. -- -------------------------------------------------------------------
  545.  
  546. DROP TABLE IF EXISTS "vornamen" ;
  547. DROP TABLE IF EXISTS "nachnamen" ;
Advertisement
Add Comment
Please, Sign In to add comment