Facebook
From Voluminous Bushbaby, 1 Year ago, written in Plain Text.
Embed
Download Paste or View Raw
Hits: 252
  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