1. DROP SEQUENCE seq;
2. CREATE SEQUENCE seq START WITH 10 INCREMENT BY 5 MINVALUE -100;
3.  
4. DROP TABLE besitzer CASCADE CONSTRAINTS;
5. DROP TABLE autos CASCADE CONSTRAINTS;
6. DROP TABLE reparaturen CASCADE CONSTRAINTS;
7. DROP TABLE vorbesitzer CASCADE CONSTRAINTS;
8. DROP TABLE vergehen CASCADE CONSTRAINTS;
9. DROP TABLE strafen CASCADE CONSTRAINTS;
10. DROP SEQUENCE seqVergehen;
11.  
12. CREATE TABLE besitzer (
13.   id INTEGER,
14.   name VARCHAR2(30) NOT NULL,
15.   gebdat DATE NOT NULL,
16.   adresse VARCHAR2(30) NOT NULL,
17.   CONSTRAINT pkBesitzer PRIMARY KEY (id),
18.   CONSTRAINT ucNameAdresse UNIQUE (name, adresse),
19.   CONSTRAINT ckGebDat CHECK (TO_CHAR(gebdat, 'YYYY') <= 1999) /* Opel Corsa Lösung (Rolator) */
20. );
21.  
22. CREATE TABLE autos (
23.   id INTEGER,
24.   bezeichnung VARCHAR2(30) NOT NULL,
25.   ps INTEGER NOT NULL,
26.   id_besitzer INTEGER NOT NULL, /* Kommentar zu FKy */
27.   CONSTRAINT pkAutos3 PRIMARY KEY (id),
28.   CONSTRAINT ucBezeichnung3 UNIQUE (bezeichnung),
29.   CONSTRAINT fkBesitzer3 FOREIGN KEY (id_besitzer) REFERENCES besitzer(id),
30.   CONSTRAINT ckPS3 CHECK (ps >= 20)
31. );
32.  
33. CREATE TABLE reparaturen (
34.   repnr INTEGER,
35.   id_autos INTEGER,
36.   beschreibung VARCHAR2(30) NOT NULL,
37.   kosten INTEGER NOT NULL,
38.   datum DATE NOT NULL,
39.   CONSTRAINT pkRepnrAutos3 PRIMARY KEY (repnr, id_autos),
40.   CONSTRAINT fkAutos3 FOREIGN KEY (id_autos) REFERENCES autos(id),
41.   CONSTRAINT ckKosten3 CHECK (kosten >= 10),
42.   CONSTRAINT ckBeschreibung3 CHECK (LENGTH(beschreibung) >= 4)
43. );
44.  
45. CREATE TABLE vorbesitzer (
46.   id_besitzer INTEGER,
47.   id_autos INTEGER,
48.   fromdate DATE,
49.   todate DATE,
50.   CONSTRAINT pkBesitzerAutosFrom3 PRIMARY KEY (id_autos, fromdate),
51.   CONSTRAINT fkVorbesitzerBesitzer3 FOREIGN KEY (id_besitzer) REFERENCES besitzer(id),
52.   CONSTRAINT fkVorbesitzerAutos3 FOREIGN KEY (id_autos) REFERENCES autos(id),
53.   CONSTRAINT ckFromDateToDate3 CHECK (fromdate < todate),
54.   CONSTRAINT ckFromDateSunday3 CHECK (TO_CHAR(fromdate, 'FMDAY') NOT LIKE 'S%'),
55.   CONSTRAINT ckToDateSunday3 CHECK (TO_CHAR(todate, 'FMDAY') NOT LIKE 'S%'),
56.   CONSTRAINT ckFromDateToDateDifference3 CHECK ((todate IS NULL) OR ((todate - fromdate) >= 7))
57. );
58.  
59. CREATE TABLE vergehen (
60.   id INTEGER,
61.   bezeichnung VARCHAR2(60),
62.   schweregrad VARCHAR2(6),
63.   bußgeld INTEGER,
64.   CONSTRAINT pkVergehenID3 PRIMARY KEY (id),
65.   CONSTRAINT ckVergehenBezeichnung3 CHECK (bezeichnung LIKE 'Vergehen: %'),
66.   CONSTRAINT ckVergehenSchweregradBußgeld3 CHECK (
67.     (schweregrad LIKE 'leicht' AND (bußgeld BETWEEN 20 AND 40))
68.     OR (schweregrad LIKE 'mittel' AND (bußgeld BETWEEN 100 AND 200))
69.     OR (schweregrad LIKE 'schwer' AND (bußgeld BETWEEN 500 AND 1000))
70.   )
71. );
72. CREATE SEQUENCE seqVergehen START WITH 10 INCREMENT BY 5 MINVALUE 0;
73.  
74. /*SERT INTO strafen VALUES(id_auto, von, id_vergehen, wann_passirt, wo, wann_gezahlt);*/
75. CREATE TABLE strafen (
76.   id_auto INTEGER,
77.   von DATE,
78.   id_vergehen INTEGER,
79.   wann_passiert DATE,
80.   wo VARCHAR2(100),
81.   wann_gezahlt DATE,
82.   CONSTRAINT pkAutoVonVergehenWann3 PRIMARY KEY (id_auto, id_vergehen, wann_passiert),
83.   CONSTRAINT fkStrafenAutoVon3 FOREIGN KEY (id_auto, von) REFERENCES vorbesitzer(id_autos, fromdate),
84.   CONSTRAINT fkStrafenVergehen3 FOREIGN KEY (id_vergehen) REFERENCES vergehen(id),
85.   CONSTRAINT ckStrafenWannPG3 CHECK (MONTHS_BETWEEN(wann_gezahlt, wann_passiert) BETWEEN 0 AND 2),
86.   CONSTRAINT ckStrafenWo3 CHECK ((wo LIKE '%Straße%') OR (wo LIKE '%Ortsgebiet%'))
87. );
88.  
89. /*INSERT INTO strafen VALUES(111, TO_DATE('31.10.2017', 'DD.MM.YYYY'), 15, TO_DATE('14.11.2017', 'DD.MM.YYYY'), 'Ortsgebiet: Radenthein', TO_DATE('14.11.2017', 'DD.MM.YYYY'));*/
90.  
91. /*INSERT INTO strafen VALUES(111, TO_DATE('31.10.2017', 'DD.MM.YYYY'), 15, TO_DATE('14.11.2017', 'DD.MM.YYYY'), 'Ortsgebiet: Radenthein', TO_DATE('14.11.2017', 'DD.MM.YYYY'));*/
92.  
93. /*INSERT INTO strafen VALUES(111, TO_DATE('31.10.2017', 'DD.MM.YYYY'), 20, TO_DATE('14.11.2017', 'DD.MM.YYYY'), 'Ortsgebiet: Radenthein', TO_DATE('14.11.2017', 'DD.MM.YYYY'));*/
94.  
95. INSERT INTO besitzer VALUES(11, 'Einstein', TO_DATE('11.01.1961', 'DD.MM.YYYY'), 'Favoritenstraße 1');
96. INSERT INTO besitzer VALUES(22, 'Zweistein', TO_DATE('11.01.1972', 'DD.MM.YYYY'), 'Favoritenstraße 2');
97. /*
98. INSERT INTO besitzer VALUES(NULL, 'Dreistein', TO_DATE('11.01.1969', 'DD.MM.YYYY'), 'Favoritenstraße 3');
99. INSERT INTO besitzer VALUES(44, NULL, TO_DATE('11.01.1965', 'DD.MM.YYYY'), 'Favoritenstraße 4');
100. INSERT INTO besitzer VALUES(55, 'Zweistein', TO_DATE('11.01.1972', 'DD.MM.YYYY'), 'Favoritenstraße 2');
101. INSERT INTO besitzer VALUES(66, 'Zweistein', TO_DATE('11.01.1972', 'DD.MM.YYYY'), 'Favoritenstraße 5');
102. INSERT INTO besitzer VALUES(22, 'Siebenstein', TO_DATE('11.01.2000', 'DD.MM.YYYY'), 'Favoritenstraße 7');
103. */
104.  
105. INSERT INTO autos VALUES(111, 'Opel Astra', 200, 11);
106. INSERT INTO autos VALUES(222, 'Opel Corsa', 150, 11);
107. INSERT INTO autos VALUES(333, 'Opel Movano', 300, 22);
108. /*
109. INSERT INTO autos VALUES(333, 'Opel Movano', 250, 22);
110. INSERT INTO autos VALUES(444, NULL, 100, 22);
111. INSERT INTO autos VALUES(555, 'Opel Movano', 300, 22);
112. INSERT INTO autos VALUES(666, 'VW Amarok', 300, 99);
113. INSERT INTO autos VALUES(777, 'Ford Focus', 19, 22);
114. INSERT INTO autos VALUES(888, 'Ford Ranger', 20, 22);
115. INSERT INTO autos VALUES(999, 'VW T3', 21, 22);
116. */
117.  
118. INSERT INTO reparaturen VALUES(1, 111, 'Erste Rep', 10, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
119. INSERT INTO reparaturen VALUES(1, 222, 'Zweite Rep 1', 10, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
120. INSERT INTO reparaturen VALUES(2, 222, 'Zweite Rep 2', 10, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
121. /*
122. INSERT INTO reparaturen VALUES(1, 111, 'dre', 10, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
123. INSERT INTO reparaturen VALUES(1, 111, 'drei', 9, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
124. INSERT INTO reparaturen VALUES(1, 111, 'Erste Rep 2', 10, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
125. */
126.  
127. INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('31.10.2017', 'DD.MM.YYYY'), TO_DATE('7.11.2017', 'DD.MM.YYYY'));
128. INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('7.11.2017', 'DD.MM.YYYY'), TO_DATE('14.11.2017', 'DD.MM.YYYY'));
129. /*
130. INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('31.10.2017', 'DD.MM.YYYY'), TO_DATE('7.11.2017', 'DD.MM.YYYY')); --Zweimal das gleiche
131. INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('29.10.2017', 'DD.MM.YYYY'), TO_DATE('7.11.2017', 'DD.MM.YYYY')); --Sonntag anmelden
132. INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('27.10.2017', 'DD.MM.YYYY'), TO_DATE('5.11.2017', 'DD.MM.YYYY')); --Sonntag abmelden
133. INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('28.10.2017', 'DD.MM.YYYY'), TO_DATE('6.11.2017', 'DD.MM.YYYY')); --Samstag anmelden
134. INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('26.10.2017', 'DD.MM.YYYY'), TO_DATE('4.11.2017', 'DD.MM.YYYY')); --Samstag abmelden
135. INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('31.10.2017', 'DD.MM.YYYY'), TO_DATE('6.11.2017', 'DD.MM.YYYY')); --Dauer zu klein
136. INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('07.11.2017', 'DD.MM.YYYY'), TO_DATE('31.10.2017', 'DD.MM.YYYY')); --Daten vertauscht
137. */
138. INSERT INTO vorbesitzer VALUES(11, 222, TO_DATE('31.10.2017', 'DD.MM.YYYY'), TO_DATE('7.11.2017', 'DD.MM.YYYY'));
139. /*INSERT INTO vorbesitzer VALUES(22, 111, TO_DATE('31.10.2017', 'DD.MM.YYYY'), TO_DATE('7.11.2017', 'DD.MM.YYYY'));*/
140.  
141. INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'leicht', 20);
142. INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'leicht', 40);
143. INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'mittel', 100);
144. INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'mittel', 200);
145. INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'schwer', 500);
146. /*INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'schwer', 200);*/
147. /*INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'schwex', 1000);*/
148.  
149. INSERT INTO strafen VALUES(111, TO_DATE('31.10.2017', 'DD.MM.YYYY'), 25, TO_DATE('1.5.2017', 'DD.MM.YYYY'), 'Finkenstein Ortsgebiet', TO_DATE('2.6.2017', 'DD.MM.YYYY'));
150. INSERT INTO strafen VALUES(111, TO_DATE('7.11.2017', 'DD.MM.YYYY'), 20, TO_DATE('1.5.2017', 'DD.MM.YYYY'), 'Finkenstein Ortsgebiet', TO_DATE('1.7.2017', 'DD.MM.YYYY'));
151. INSERT INTO strafen VALUES(222, TO_DATE('31.10.2017', 'DD.MM.YYYY'), 15, TO_DATE('1.5.2017', 'DD.MM.YYYY'), 'Faakersee Straße', NULL);
152.  
153. COMMIT;
154.  
155. SELECT * FROM besitzer;
156. SELECT * FROM autos;
157. SELECT * FROM reparaturen;
158. SELECT * FROM vorbesitzer;
159. SELECT * FROM vergehen;
160.  
161. SELECT * FROM autos
162. WHERE ps >= 150 AND ps <= 200;
163.  
164. SELECT * FROM autos
165. WHERE ps BETWEEN 150 AND 200;
166.  
167. SELECT * FROM autos
168. WHERE ps = 150 OR ps = 200;
169.  
170. SELECT * FROM autos
171. WHERE ps IN (150, 200);
172.  
173. SELECT todate - fromdate FROM vorbesitzer;
174. SELECT (todate - fromdate)/30,MONTHS_BETWEEN(todate, fromdate) FROM vorbesitzer;
175.  
176. SELECT name, TO_CHAR(gebdat, 'DD-MM-YYYY'), TO_CHAR(gebdat, 'DD. MONTH, YYYY, DAY'),
177.   TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(gebdat, 'YYYY') AS "alter"
178.   FROM besitzer;
179.  
180. SELECT name, gebdat, TO_CHAR(gebdat, 'DAY') as Wochentag
181.   FROM besitzer
182. WHERE TO_CHAR(gebdat, 'FMDAY') LIKE '%TA_'; /* WHERE zum filtern von Zeilen. */
183.  
184. SELECT TO_CHAR(TO_DATE('24.12.9999', 'DD.MM.YYYY'), 'FMDAY')
185.   FROM besitzer;
186.  
187. SELECT besitzer.name, autos.bezeichnung FROM autos
188.   INNER JOIN besitzer ON autos.id_besitzer = besitzer.id;
189.  
190. SELECT b.NAME, v.FROMDATE, ROUND(MONTHS_BETWEEN(v.TODATE, v.FROMDATE)) "Anzahl Monate", a.BEZEICHNUNG
191.   FROM besitzer b INNER JOIN vorbesitzer v ON b.id=v.ID_BESITZER INNER JOIN autos a ON a.ID=v.ID_AUTOS;
192.  
193. SELECT b.NAME, s.WANN_PASSIERT, ver.BEZEICHNUNG
194.   FROM besitzer b INNER JOIN vorbesitzer v ON v.ID_BESITZER=b.ID INNER JOIN strafen s ON s.ID_AUTO=v.ID_AUTOS AND s.WANN_PASSIERT=v.FROMDATE INNER JOIN vergehen ver ON s.ID_VERGEHEN=ver.ID;
195.  
196.  
197.  
198. SELECT a.bezeichnung, r.beschreibung, r.datum
199.   FROM AUTOS a INNER JOIN REPARATUREN r ON