DROP SEQUENCE seq;
CREATE SEQUENCE seq START WITH 10 INCREMENT BY 5 MINVALUE -100;

DROP TABLE besitzer CASCADE CONSTRAINTS;
DROP TABLE autos CASCADE CONSTRAINTS;
DROP TABLE reparaturen CASCADE CONSTRAINTS;
DROP TABLE vorbesitzer CASCADE CONSTRAINTS;
DROP TABLE vergehen CASCADE CONSTRAINTS;
DROP TABLE strafen CASCADE CONSTRAINTS;
DROP SEQUENCE seqVergehen;

CREATE TABLE besitzer (
  id INTEGER,
  name VARCHAR2(30) NOT NULL,
  gebdat DATE NOT NULL,
  adresse VARCHAR2(30) NOT NULL,
  CONSTRAINT pkBesitzer PRIMARY KEY (id),
  CONSTRAINT ucNameAdresse UNIQUE (name, adresse),
  CONSTRAINT ckGebDat CHECK (TO_CHAR(gebdat, 'YYYY') <= 1999) /* Opel Corsa Lösung (Rolator) */
);

CREATE TABLE autos (
  id INTEGER,
  bezeichnung VARCHAR2(30) NOT NULL,
  ps INTEGER NOT NULL,
  id_besitzer INTEGER NOT NULL, /* Kommentar zu FKy */
  CONSTRAINT pkAutos3 PRIMARY KEY (id),
  CONSTRAINT ucBezeichnung3 UNIQUE (bezeichnung),
  CONSTRAINT fkBesitzer3 FOREIGN KEY (id_besitzer) REFERENCES besitzer(id),
  CONSTRAINT ckPS3 CHECK (ps >= 20)
);

CREATE TABLE reparaturen (
  repnr INTEGER,
  id_autos INTEGER,
  beschreibung VARCHAR2(30) NOT NULL,
  kosten INTEGER NOT NULL,
  datum DATE NOT NULL,
  CONSTRAINT pkRepnrAutos3 PRIMARY KEY (repnr, id_autos),
  CONSTRAINT fkAutos3 FOREIGN KEY (id_autos) REFERENCES autos(id),
  CONSTRAINT ckKosten3 CHECK (kosten >= 10),
  CONSTRAINT ckBeschreibung3 CHECK (LENGTH(beschreibung) >= 4)
);

CREATE TABLE vorbesitzer (
  id_besitzer INTEGER,
  id_autos INTEGER,
  fromdate DATE,
  todate DATE,
  CONSTRAINT pkBesitzerAutosFrom3 PRIMARY KEY (id_autos, fromdate),
  CONSTRAINT fkVorbesitzerBesitzer3 FOREIGN KEY (id_besitzer) REFERENCES besitzer(id),
  CONSTRAINT fkVorbesitzerAutos3 FOREIGN KEY (id_autos) REFERENCES autos(id),
  CONSTRAINT ckFromDateToDate3 CHECK (fromdate < todate),
  CONSTRAINT ckFromDateSunday3 CHECK (TO_CHAR(fromdate, 'FMDAY') NOT LIKE 'S%'),
  CONSTRAINT ckToDateSunday3 CHECK (TO_CHAR(todate, 'FMDAY') NOT LIKE 'S%'),
  CONSTRAINT ckFromDateToDateDifference3 CHECK ((todate IS NULL) OR ((todate - fromdate) >= 7))
);

CREATE TABLE vergehen (
  id INTEGER,
  bezeichnung VARCHAR2(60),
  schweregrad VARCHAR2(6),
  bußgeld INTEGER,
  CONSTRAINT pkVergehenID3 PRIMARY KEY (id),
  CONSTRAINT ckVergehenBezeichnung3 CHECK (bezeichnung LIKE 'Vergehen: %'),
  CONSTRAINT ckVergehenSchweregradBußgeld3 CHECK (
    (schweregrad LIKE 'leicht' AND (bußgeld BETWEEN 20 AND 40))
    OR (schweregrad LIKE 'mittel' AND (bußgeld BETWEEN 100 AND 200))
    OR (schweregrad LIKE 'schwer' AND (bußgeld BETWEEN 500 AND 1000))
  )
);
CREATE SEQUENCE seqVergehen START WITH 10 INCREMENT BY 5 MINVALUE 0;

/*SERT INTO strafen VALUES(id_auto, von, id_vergehen, wann_passirt, wo, wann_gezahlt);*/
CREATE TABLE strafen (
  id_auto INTEGER,
  von DATE,
  id_vergehen INTEGER,
  wann_passiert DATE,
  wo VARCHAR2(100),
  wann_gezahlt DATE,
  CONSTRAINT pkAutoVonVergehenWann3 PRIMARY KEY (id_auto, id_vergehen, wann_passiert),
  CONSTRAINT fkStrafenAutoVon3 FOREIGN KEY (id_auto, von) REFERENCES vorbesitzer(id_autos, fromdate),
  CONSTRAINT fkStrafenVergehen3 FOREIGN KEY (id_vergehen) REFERENCES vergehen(id),
  CONSTRAINT ckStrafenWannPG3 CHECK (MONTHS_BETWEEN(wann_gezahlt, wann_passiert) BETWEEN 0 AND 2),
  CONSTRAINT ckStrafenWo3 CHECK ((wo LIKE '%Straße%') OR (wo LIKE '%Ortsgebiet%'))
);

/*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'));*/

/*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'));*/

/*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'));*/

INSERT INTO besitzer VALUES(11, 'Einstein', TO_DATE('11.01.1961', 'DD.MM.YYYY'), 'Favoritenstraße 1');
INSERT INTO besitzer VALUES(22, 'Zweistein', TO_DATE('11.01.1972', 'DD.MM.YYYY'), 'Favoritenstraße 2');
/*
INSERT INTO besitzer VALUES(NULL, 'Dreistein', TO_DATE('11.01.1969', 'DD.MM.YYYY'), 'Favoritenstraße 3');
INSERT INTO besitzer VALUES(44, NULL, TO_DATE('11.01.1965', 'DD.MM.YYYY'), 'Favoritenstraße 4');
INSERT INTO besitzer VALUES(55, 'Zweistein', TO_DATE('11.01.1972', 'DD.MM.YYYY'), 'Favoritenstraße 2');
INSERT INTO besitzer VALUES(66, 'Zweistein', TO_DATE('11.01.1972', 'DD.MM.YYYY'), 'Favoritenstraße 5');
INSERT INTO besitzer VALUES(22, 'Siebenstein', TO_DATE('11.01.2000', 'DD.MM.YYYY'), 'Favoritenstraße 7');
*/

INSERT INTO autos VALUES(111, 'Opel Astra', 200, 11);
INSERT INTO autos VALUES(222, 'Opel Corsa', 150, 11);
INSERT INTO autos VALUES(333, 'Opel Movano', 300, 22);
/*
INSERT INTO autos VALUES(333, 'Opel Movano', 250, 22);
INSERT INTO autos VALUES(444, NULL, 100, 22);
INSERT INTO autos VALUES(555, 'Opel Movano', 300, 22);
INSERT INTO autos VALUES(666, 'VW Amarok', 300, 99);
INSERT INTO autos VALUES(777, 'Ford Focus', 19, 22);
INSERT INTO autos VALUES(888, 'Ford Ranger', 20, 22);
INSERT INTO autos VALUES(999, 'VW T3', 21, 22);
*/

INSERT INTO reparaturen VALUES(1, 111, 'Erste Rep', 10, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
INSERT INTO reparaturen VALUES(1, 222, 'Zweite Rep 1', 10, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
INSERT INTO reparaturen VALUES(2, 222, 'Zweite Rep 2', 10, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
/*
INSERT INTO reparaturen VALUES(1, 111, 'dre', 10, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
INSERT INTO reparaturen VALUES(1, 111, 'drei', 9, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
INSERT INTO reparaturen VALUES(1, 111, 'Erste Rep 2', 10, TO_DATE('24.10.2017', 'DD.MM.YYYY'));
*/

INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('31.10.2017', 'DD.MM.YYYY'), TO_DATE('7.11.2017', 'DD.MM.YYYY'));
INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('7.11.2017', 'DD.MM.YYYY'), TO_DATE('14.11.2017', 'DD.MM.YYYY'));
/*
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
INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('29.10.2017', 'DD.MM.YYYY'), TO_DATE('7.11.2017', 'DD.MM.YYYY')); --Sonntag anmelden
INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('27.10.2017', 'DD.MM.YYYY'), TO_DATE('5.11.2017', 'DD.MM.YYYY')); --Sonntag abmelden
INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('28.10.2017', 'DD.MM.YYYY'), TO_DATE('6.11.2017', 'DD.MM.YYYY')); --Samstag anmelden
INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('26.10.2017', 'DD.MM.YYYY'), TO_DATE('4.11.2017', 'DD.MM.YYYY')); --Samstag abmelden
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
INSERT INTO vorbesitzer VALUES(11, 111, TO_DATE('07.11.2017', 'DD.MM.YYYY'), TO_DATE('31.10.2017', 'DD.MM.YYYY')); --Daten vertauscht
*/
INSERT INTO vorbesitzer VALUES(11, 222, TO_DATE('31.10.2017', 'DD.MM.YYYY'), TO_DATE('7.11.2017', 'DD.MM.YYYY'));
/*INSERT INTO vorbesitzer VALUES(22, 111, TO_DATE('31.10.2017', 'DD.MM.YYYY'), TO_DATE('7.11.2017', 'DD.MM.YYYY'));*/

INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'leicht', 20);
INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'leicht', 40);
INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'mittel', 100);
INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'mittel', 200);
INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'schwer', 500);
/*INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'schwer', 200);*/
/*INSERT INTO vergehen VALUES(seqVergehen.NEXTVAL, 'Vergehen: Geschwindigkeitsüberschreitung', 'schwex', 1000);*/

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'));
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'));
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);

COMMIT;

SELECT * FROM besitzer;
SELECT * FROM autos;
SELECT * FROM reparaturen;
SELECT * FROM vorbesitzer;
SELECT * FROM vergehen;

SELECT * FROM autos
WHERE ps >= 150 AND ps <= 200;

SELECT * FROM autos
WHERE ps BETWEEN 150 AND 200;

SELECT * FROM autos
WHERE ps = 150 OR ps = 200;

SELECT * FROM autos
WHERE ps IN (150, 200);

SELECT todate - fromdate FROM vorbesitzer;
SELECT (todate - fromdate)/30,MONTHS_BETWEEN(todate, fromdate) FROM vorbesitzer;

SELECT name, TO_CHAR(gebdat, 'DD-MM-YYYY'), TO_CHAR(gebdat, 'DD. MONTH, YYYY, DAY'),
  TO_CHAR(SYSDATE, 'YYYY') - TO_CHAR(gebdat, 'YYYY') AS "alter"
  FROM besitzer;

SELECT name, gebdat, TO_CHAR(gebdat, 'DAY') as Wochentag
  FROM besitzer
WHERE TO_CHAR(gebdat, 'FMDAY') LIKE '%TA_'; /* WHERE zum filtern von Zeilen. */

SELECT TO_CHAR(TO_DATE('24.12.9999', 'DD.MM.YYYY'), 'FMDAY')
  FROM besitzer;

SELECT besitzer.name, autos.bezeichnung FROM autos
  INNER JOIN besitzer ON autos.id_besitzer = besitzer.id;
  
SELECT b.NAME, v.FROMDATE, ROUND(MONTHS_BETWEEN(v.TODATE, v.FROMDATE)) "Anzahl Monate", a.BEZEICHNUNG
  FROM besitzer b INNER JOIN vorbesitzer v ON b.id=v.ID_BESITZER INNER JOIN autos a ON a.ID=v.ID_AUTOS;
  
SELECT b.NAME, s.WANN_PASSIERT, ver.BEZEICHNUNG
  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;
  
  
  
SELECT a.bezeichnung, r.beschreibung, r.datum
  FROM AUTOS a INNER JOIN REPARATUREN r ON