iMiU5yQx

1SQLMemes.sql
2Details
3Activity
4Today
51:45 PM
6S
7You renamed an item
8SQL
9SQLMemes.sql
10BIF2C1_Challenge5_Wermann_Oracle.sql
111:45 PM
12S
13You uploaded an item
14SQL
15BIF2C1_Challenge5_Wermann_Oracle.sql
16No recorded activity before May 9, 2019
17
18
19DROP TABLE if exists a_kunde CASCADE;
20DROP TABLE if exists a_mechaniker CASCADE;
21DROP TABLE if exists a_kfz CASCADE;
22DROP TABLE if exists a_servicebucheintrag CASCADE;
23DROP TABLE if exists a_artikel CASCADE;
24DROP TABLE if exists a_rechnung CASCADE;
25DROP TABLE if exists a_rech_art CASCADE;
26
27CREATE TABLE a_kunde(
28kunden_id INT PRIMARY KEY,
29vorname VARCHAR(32) NOT NULL,
30nachname VARCHAR(32) NOT NULL,
31geschlecht CHAR(1) check(geschlecht IN ('m', 'w', 'd')));
32
33CREATE TABLE a_mechaniker(
34mech_id INT PRIMARY KEY,
35vorname VARCHAR(32) NOT NULL,
36nachname VARCHAR(32) NOT NULL,
37geschlecht CHAR(1) check(geschlecht IN ('m', 'w', 'd')));
38
39CREATE TABLE a_kfz(
40kfz_id INT PRIMARY KEY,
41kennzeichen VARCHAR(15) NOT NULL,
42pickerl VARCHAR(20) NOT NULL,
43fk_kunden_id INT references a_kunde(kunden_id) ON DELETE SET NULL);
44
45CREATE TABLE a_servicebucheINTrag(
46service_id INT PRIMARY KEY,
47datum date NOT NULL,
48notitz VARCHAR(255),
49fk_kfz_id INT references a_kfz(kfz_id) ON DELETE SET NULL,
50fk_mech_id INT references a_mechaniker(mech_id) ON DELETE SET NULL);
51
52CREATE TABLE a_artikel(
53art_id INT PRIMARY KEY,
54bezeichnung VARCHAR(100) unique NOT NULL,
55netto FLOAT(24) NOT NULL,
56ust FLOAT(24) NOT NULL);
57
58CREATE TABLE a_rechnung(
59rech_id INT PRIMARY KEY,
60datum date NOT NULL,
61fk_kunden_id INT references a_kunde(kunden_id) ON DELETE SET NULL,
62fk_mech_id INT references a_mechaniker(mech_id) ON DELETE SET NULL);
63
64CREATE TABLE a_rech_art(
65fk_rech_id INT references a_rechnung(rech_id) ON DELETE CASCADE,
66fk_art_id INT references a_artikel(art_id) ON DELETE CASCADE,
67netto FLOAT(24) NOT NULL,
68ust FLOAT(24) NOT NULL,
69PRIMARY KEY(fk_rech_id, fk_art_id));
70
71INSERT INTO a_kunde
72VALUES(1, 'Hans', 'Wurst', 'm');
73INSERT INTO a_kunde
74VALUES(2, 'Karla', 'Klopp', 'w');
75INSERT INTO a_kunde
76VALUES(3, 'Ben', 'Schlagmichtot', 'd');
77
78INSERT INTO a_mechaniker
79VALUES(1, 'Gordon', 'Freeman', 'm');
80INSERT INTO a_mechaniker
81VALUES(2, 'Lara', 'Croft', 'w');
82
83INSERT INTO a_kfz
84VALUES(1, 'W-12345F', 'F123456', 1);
85INSERT INTO a_kfz
86VALUES(2, 'G-67302Y', 'G98734', 2);
87
88INSERT INTO a_servicebucheINTrag
89VALUES(1, TO_DATE('06/11/2018', 'DD/MM/YYYY'), 'Zahnriemen', 1, 2);
90INSERT INTO a_servicebucheINTrag
91VALUES(2, TO_DATE('18/03/2019', 'DD/MM/YYYY'), 'Filter und Oel', 2, 1);
92
93INSERT INTO a_artikel
94VALUES(1, 'Ventil', 86.20, 20);
95INSERT INTO a_artikel
96VALUES(2, 'Kolbenring', 220.80, 20);
97INSERT INTO a_artikel
98VALUES(3, 'Servo-oel', 1111.30, 20);
99
100INSERT INTO a_rechnung
101VALUES(1, TO_DATE('06/11/2018', 'DD/MM/YYYY'), 1, 1);
102INSERT INTO a_rechnung
103VALUES(2, TO_DATE('18/03/2019', 'DD/MM/YYYY'), 3, 2);
104INSERT INTO a_rechnung
105VALUES(3, TO_DATE('01/04/2019', 'DD/MM/YYYY'), 2, 1);
106
107INSERT INTO a_rech_art
108VALUES(1, 1, 86.20, 20);
109INSERT INTO a_rech_art
110VALUES(2, 2, 220.80, 20);
111INSERT INTO a_rech_art
112VALUES(2, 1, 86.20, 20);
113INSERT INTO a_rech_art
114VALUES(3, 3, 1111.30, 20);