Cornel Brücher, Frank Jüdes, Wulf Kollmann

Oracle SQL Thinking

Vom Problem zum SQL-Statement

Bibliografische Information der Deutschen Nationalbibliothek

Die Deutsche Nationalbibliothek verzeichnet diese Publikation in der Deutschen Nationalbibliografie; detaillierte bibliografische Daten sind im Internet über abrufbar.

ISBN 978-3-8266-9581-01. Auflage 2014

www.mitp.deE-Mail: mitp-verlag@sigloch.deTelefon: +49 7953 / 7189 - 079Telefax: +49 7953 / 7189 - 082

© 2014 mitp Verlags GmbH & Co. KG

Dieses Werk, einschließlich aller seiner Teile, ist urheberrechtlich geschützt. Jede Verwertung außerhalb der engen Grenzen des Urheberrechtsgesetzes ist ohne Zustimmung des Verlages unzulässig und strafbar. Dies gilt insbesondere für Vervielfältigungen, Übersetzungen, Mikroverfilmungen und die Einspeicherung und Verarbeitung in elektronischen Systemen.

Die Wiedergabe von Gebrauchsnamen, Handelsnamen, Warenbezeichnungen usw. in diesem Werk berechtigt auch ohne besondere Kennzeichnung nicht zu der Annahme, dass solche Namen im Sinne der Warenzeichen- und Markenschutz-Gesetzgebung als frei zu betrachten wären und daher von jedermann benutzt werden dürften.

Lektorat: Ernst-Heinrich Pröfener, Sabine JanatschekSprachkorrektorat: Jürgen DubauSatz: III-satz, Husby, www.drei-satz.deCoverbild: © Vladitto

Inhaltsverzeichnis

Vorwort. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

1 Das Problem mit dem Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.1 SQL – Die Sprache des Orakels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141.2 Die ersten Fragen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

A.2.1 Voraussetzungen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151.2.2 Welches Datum haben wir heute? . . . . . . . . . . . . . . . . . . . . . 151.2.3 Hello World . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

1.3 Abfrage von echten Tabellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191.3.1 Anzeige von Tabelleninhalten – und mehr . . . . . . . . . . . . . . 191.3.2 Die erste Textaufgabe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

2 Wer oder was und woher? (Navigation im Datenmodell). . . . . . . . . . 252.1 Voraussetzungen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252.2 Welche Mitarbeiter haben wir?

(Abfrage von Details aus einer Tabelle) . . . . . . . . . . . . . . . . . . . . . . . . 262.2.1 Die benötigten Tabellen ermitteln . . . . . . . . . . . . . . . . . . . . . 272.2.2 Die erforderlichen Spalten auswählen . . . . . . . . . . . . . . . . . . 28

2.3 In welchen Abteilungen sind die Mitarbeiter? (Abfrage über 2 Tabellen) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302.3.1 Navigation im Datenmodell. . . . . . . . . . . . . . . . . . . . . . . . . . . 302.3.2 Formulierung der Abfrage über zwei Tabellen

(INNER JOIN). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352.3.3 Arbeit sparen auf natürliche Art (NATURAL JOIN) . . . . . . . 41

2.4 Welche Abteilungen sind in welchen Regionen? . . . . . . . . . . . . . . . . 452.4.1 Verbundene Abfrage über vier Tabellen (INNER JOIN) . . . . 452.4.2 Sortieren der Ergebnisse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49

2.5 In welchen Regionen sind welche Abteilungen? . . . . . . . . . . . . . . . . 492.5.1 Kehrt, marsch! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502.5.2 In welchen Regionen, Ländern und Locations haben

wir Abteilungen und in welchen nicht? . . . . . . . . . . . . . . . . . 50

5

Inhaltsverzeichnis

6

2.6 Wer arbeitet im Marketing? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532.6.1 Abfrage mit Filter (WHERE-Bedingung) . . . . . . . . . . . . . . . . 532.6.2 Möglichkeiten der Ergebnisfilterung . . . . . . . . . . . . . . . . . . . 542.6.3 Verneinung von Bedingungen. . . . . . . . . . . . . . . . . . . . . . . . . 552.6.4 Verknüpfung von Bedingungen . . . . . . . . . . . . . . . . . . . . . . . 562.6.5 JOIN über WHERE-Bedingungen

(ANSI-92-Syntax vs. alte Syntax) . . . . . . . . . . . . . . . . . . . . . . . 562.7 In welchen Regionen sind wir nicht vertreten? . . . . . . . . . . . . . . . . . . 582.8 Russische Geschäfte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

3 Wie viel X pro Y? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 673.1 Wie viel ...? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

3.1.1 Wie viele Mitarbeiter haben wir? . . . . . . . . . . . . . . . . . . . . . . . 673.1.2 Wie viele Mitarbeiter haben ein variables Gehalt? . . . . . . . . . 683.1.3 Wie viele Mitarbeiter haben die Abteilungen? . . . . . . . . . . . . 68

3.2 Wie viel X pro Y (unterteilt nach Z) . . . . . . . . . . . . . . . . . . . . . . . . . . . 743.2.1 Wie viele Mitarbeiter haben die Abteilungen? . . . . . . . . . . . . 743.2.2 ... unterteilt nach Jobs? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 753.2.3 Zwischensummen und Gesamtsumme . . . . . . . . . . . . . . . . . 783.2.4 Welche Abteilungen haben mehr als fünf Mitarbeiter?. . . . . 88

3.3 X pro Y im Verhältnis zur Gesamtmenge X . . . . . . . . . . . . . . . . . . . . 903.3.1 Das Durchschnittsgehalt welcher Abteilungen liegt

über dem Gesamt-durchschnitt? . . . . . . . . . . . . . . . . . . . . . . . 90

4 Mengenlehre . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 974.1 Voraussetzungen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

4.1.1 Fachlicher Hintergrund . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 974.1.2 Einrichten des Users und der Tabellen. . . . . . . . . . . . . . . . . . 97

4.2 Datenmodell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1014.3 Wie viel (im Vergleich) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

4.3.1 Wie viele Besucher waren auf der letzten Messe? . . . . . . . . . 1014.3.2 Wie viele Besucher waren auf der letzten Messe im

Vergleich zur Vormesse? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1064.4 Was im Vergleich . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

4.4.1 Welche Besucher der letzten Messe fehlten bei der Vormesse? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114

4.4.2 Welche Besucher waren auf beiden Messen?. . . . . . . . . . . . . 1194.4.3 Welche Besucher waren insgesamt auf beiden

Chicken Breeding Fairs? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1234.5 Spickzettel Mengenoperationen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126

Inhaltsverzeichnis

5 Wie viel Y pro X und Z(eitspanne) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1275.1 Voraussetzungen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127

5.1.1 Fachlicher Hintergrund . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1275.1.2 Einrichten des Users und der Tabellen . . . . . . . . . . . . . . . . . 127

5.2 Datenmodell. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1285.3 Wie viele Rechnungen sind 30/60/90/>90 Tage überfällig? . . . . . . 128

5.3.1 Wie erkennen wir überfällige Rechnungen? . . . . . . . . . . . . . 1295.3.2 Wie errechnen wir die Zeitspanne? . . . . . . . . . . . . . . . . . . . . 1305.3.3 Datumsarithmetik . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1315.3.4 Datumskonvertierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1335.3.5 Einteilung in die vorgegebenen Kategorien . . . . . . . . . . . . . . 137

5.4 Wie viel Umsatz pro Kunde und Jahr? . . . . . . . . . . . . . . . . . . . . . . . . 1415.4.1 ROLLUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1425.4.2 CUBE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1435.4.3 PIVOT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1495.4.4 UNPIVOT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151

6 Sternzeit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1556.1 Datenmodell. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1556.2 Welche Farbe war die meistverkaufte pro Jahr? . . . . . . . . . . . . . . . . . 156

6.2.1 Data Warehouse-Abfrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1566.2.2 Relationale Abfrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159

6.3 Wie hat sich der Umsatz in der Quartalsbetrachtung entwickelt?. . . 1626.3.1 Data Warehouse-Abfrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1626.3.2 Relationale Abfrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165

6.4 Umsatz, Quartalsbetrachtung, Holzprodukte . . . . . . . . . . . . . . . . . . . 1676.4.1 Data Warehouse-Abfrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1676.4.2 Relationale Abfrage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168

7 Wer kennt wen? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1737.1 Einrichten des Users und der Tabellen . . . . . . . . . . . . . . . . . . . . . . . . 1737.2 Datenmodell. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1747.3 Suche in der Baumstruktur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175

7.3.1 Wen kenne ich direkt? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1757.3.2 Wen kenne ich über wie viele Ecken? . . . . . . . . . . . . . . . . . . . 1767.3.3 Wen kenne ich über Maverick? . . . . . . . . . . . . . . . . . . . . . . . . 1807.3.4 Gibt es eine Verbindung zwischen Cornel und

dem Kanuschlüssel? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1827.3.5 Die kürzeste Verbindung zwischen Cornel und Rajesh? . . . 184

7

Inhaltsverzeichnis

8

7.4 Suche im Netzwerk . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1927.4.1 Gibt es Verbindungen zwischen Maverick und Averell? . . . . 192

7.5 Der kürzeste Pfad. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1977.6 Praxisanwendungen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202

7.6.1 Welche Strecke hat die meisten freien Leitungen? . . . . . . . . 2037.6.2 Navigation im Datenmodell . . . . . . . . . . . . . . . . . . . . . . . . . . . 206

8 Schrödingers Gruppierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2158.1 Datenmodell . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2158.2 X pro Y im Verhältnis zur Gesamtmenge X . . . . . . . . . . . . . . . . . . . . 216

8.2.1 Mit wie viel Prozent der Kunden machen wir wie viel Prozent unseres Umsatzes? . . . . . . . . . . . . . . . . . . . . . . . . . . . 216

8.2.2 X Prozent weniger Arbeit mit analytischen Funktionen . . . . 2208.3 Sichtbereiche analytischer Funktionen . . . . . . . . . . . . . . . . . . . . . . . . 2298.4 Rangfolge mit und ohne Gruppierung. . . . . . . . . . . . . . . . . . . . . . . . . 233

8.4.1 Wie viel Umsatz machen wir mit dem obersten Drittel unserer Kunden, und welche Kunden sind das? . . . . . . . . . . 234

9 Mustersuche . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2419.1 Suche in Zahlenreihen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241

9.1.1 V-Formation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2419.1.2 Formulieren von Suchmustern . . . . . . . . . . . . . . . . . . . . . . . . 2549.1.3 Der kleine Waschsalon . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262

9.2 Wie schreibt sich Herr Meier? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2669.2.1 Orakeln mit Regex . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269

10 Datenmodelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27510.1 Datenbankmodell vs. Datenmodell. . . . . . . . . . . . . . . . . . . . . . . . . . . . 27510.2 Grundlagen des relationalen Konzepts . . . . . . . . . . . . . . . . . . . . . . . . 27610.3 Grundlagen von Datenmodellen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277

10.3.1 Das einfachste Datenmodell: Eine Tabelle . . . . . . . . . . . . . . . 27710.3.2 Die Department-Tabelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27810.3.3 Das Telefonnummernproblem . . . . . . . . . . . . . . . . . . . . . . . . 28110.3.4 Geteilte Departments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28210.3.5 Wer ist mein Manager? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28310.3.6 Normalisierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28510.3.7 Beziehungskisten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28710.3.8 Schlüsseltechnologie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29010.3.9 Integritätsbedingungen (Constraints) . . . . . . . . . . . . . . . . . . . 29610.3.10 Sequenzen und Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300

Inhaltsverzeichnis

10.4 Das Modell und die Datenbank . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30310.4.1 Anlegen eines Benutzerkontos . . . . . . . . . . . . . . . . . . . . . . . . 30310.4.2 Der CREATE TABLE-Befehl . . . . . . . . . . . . . . . . . . . . . . . . . . 30410.4.3 Der ALTER TABLE-Befehl. . . . . . . . . . . . . . . . . . . . . . . . . . . . 30510.4.4 Der CREATE INDEX-Befehl . . . . . . . . . . . . . . . . . . . . . . . . . . 30710.4.5 Der CREATE SEQUENCE-Befehl . . . . . . . . . . . . . . . . . . . . . . 30810.4.6 Der CREATE TRIGGER-Befehl. . . . . . . . . . . . . . . . . . . . . . . . 30810.4.7 Übersicht und andere wichtige DDL-Befehle. . . . . . . . . . . . . 309

11 InsUpDel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31311.1 Der INSERT-Befehl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313

11.1.1 Eingefügte Werte zurückliefern . . . . . . . . . . . . . . . . . . . . . . . 31411.1.2 Der INSERT-Befehl als Datenschleuder . . . . . . . . . . . . . . . . . 31511.1.3 Einfügen in mehrere Tabellen. . . . . . . . . . . . . . . . . . . . . . . . . 317

11.2 Der UPDATE-Befehl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32111.3 Aktualisierte Daten zurückliefern . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32311.4 Der DELETE-Befehl. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32411.5 Gelöschte Daten zurückliefern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32411.6 Spezialisten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325

11.6.1 Der TRUNCATE-Befehl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32511.6.2 Der MERGE-Befehl . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326

12 Transaktionen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33112.1 Allgemeines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33112.2 COMMIT und ROLLBACK. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33212.3 Savepoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33212.4 Explizite Transaktionen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33312.5 Sequenzen und Transaktionen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33612.6 Datensätze sperren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33712.7 Tabellen sperren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339

12.7.1 Deadlock-Situationen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340

13 Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34313.1 Voraussetzungen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343

13.1.1 Einrichten des Datenbank-Accounts. . . . . . . . . . . . . . . . . . . . 34313.2 Tunen der Hardware . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34413.3 Datenbank-Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344

13.3.1 Datenbankstatistiken . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34413.3.2 Automatic Workload Repository . . . . . . . . . . . . . . . . . . . . . . . 346

9

Inhaltsverzeichnis

10

13.4 Datenmodell-Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34713.4.1 Erstellen der Testdaten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34813.4.2 Testen des Datenmodells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35013.4.3 Kosten- versus regelbasierter Optimierer . . . . . . . . . . . . . . . . 35613.4.4 Verarbeitung von SQL-Abfragen . . . . . . . . . . . . . . . . . . . . . . . 35613.4.5 Statistiken . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35713.4.6 Informationen aus dem Data Dictionary . . . . . . . . . . . . . . . . 36013.4.7 Indexverwaltung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36113.4.8 Explain Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362

13.5 SQL-Statement Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36313.6 Applikations-Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364

A Vorbereitungen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367A.1 Installation der Datenbank . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367

A.1.1 Oracle Database Express Edition 11g Release 2 . . . . . . . . . . . 367A.1.2 Oracle Database 12c Release 1 . . . . . . . . . . . . . . . . . . . . . . . . . 368

A.2 Hochfahren und Herunterfahren. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375A.3 SQL Developer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376

A.3.1 Die mitgelieferte Version . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376A.3.2 Die aktuelle Version . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376A.3.3 Übungsdatenbank im Netz . . . . . . . . . . . . . . . . . . . . . . . . . . . 379

B NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381

C DDL-Skripte . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385C.1 Anlegen eines Benutzers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385C.2 Erzeugen der Beispieltabellen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385C.3 Erzeugen der Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386C.4 Erzeugen der Suchindexe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388C.5 Anlegen der Sequenzen. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388C.6 Die Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388

D EXCEL-Daten importieren . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391

Stichwortverzeichnis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397

Vorwort

In diesem Buch lernen Sie nicht, wie man programmiert. Ein Programm be-schreibt, was ein Rechner tun soll und wie er es tun soll. Programmierer denkenin Schrittfolgen, Verzweigungen und Schleifen. Man stellt eine Abfrage, verarbei-tet das Ergebnis und holt sich dann wiederholt weitere Details aus der Datenbank.Das ist keine Datenbank-Abfrage, das ist ein Datenbank-Interview. In diesem Buchlernen Sie, aus der verbalen Beschreibung der gewünschten Daten eine einzigeDatenbankabfrage zu formulieren. Eine Frage, eine Antwort. Das ist SQL!

Wir zeigen Ihnen in diesem Buch nicht einfach, was ein LEFT OUTER JOIN oderein GROUP BY ist, und präsentieren dann ein Beispiel. Es bringt nichts, Syntax-elemente auswendig zu lernen und auf das passende Problem zu warten. Im rich-tigen Leben ist es genau andersherum. Das Problem kommt zu Ihnen, und Siemüssen entscheiden, welche SQL-Features Sie einsetzen müssen, um es zu lösen.Wir zeigen Ihnen, wie. Syntaktische Details kann man nachschlagen.

Wir wünschen Ihnen mit diesem Buch viel Erfolg – und viel Spaß!

Cornel Brücher Frank Jüdes Wulf Kollmann

Der Aufbau dieses Buches

In den Kapiteln 1 bis 9 beschäftigen wir uns mit fachlichen Fragestellungen undden dazu passenden Datenbankabfragen. Die Kapitel 10 und 11 zeigen, wie manDaten verändert und mit den dadurch ausgelösten Transaktionen zurechtkommt.Kapitel 12 ist der methodischen Erstellung von Datenmodellen gewidmet, undKapitel 13 widmet sich dem Thema Tuning.

Wichtige Voraussetzungen

Sie benötigen Zugriff auf eine Oracle-Datenbank 12c, deren Installation imAnhang beschrieben ist. Für die meisten Aufgaben genügt allerdings auch dieOracle Express Edition 11g. Beides fällt, wenn nur zur persönlichen Einarbeitungbenutzt, aktuell unter die »Technet-Lizenz« und ist für Sie nicht mit Kosten ver-bunden. (Da das kein rechtsgültiger Hinweis ist, und die Lizenzbedingungenrecht kurz sind, lesen Sie diese bitte vor einem Download durch.)

11

Vorwort

12

Sie benötigen DBA-Rechte (User SYS oder SYSTEM), um die verwendeten Bei-spieluser zu entsperren oder anzulegen.

Als Client-Betriebssysteme sind Windows, Linux und OS X geeignet. Eine Aus-wahl unter mehreren Entwicklungsumgebungen steht Ihnen nur bei Windowszur Verfügung. Auf Linux und OS X kann der SQL Developer von Oracle verwen-det werden.

Notation

Die Notation ist bewusst so einfach wie nur irgendwie möglich gehalten. Schlüs-selworte sind fett gedruckt, einsetzbare Parameter kursiv (Beispiel: SELECT spalte1FROM tabelle).

Bei Dialogbeispielen in SQL*Plus kennzeichnet der von SQL*Plus ausgegebenePrompt SQL> die Eingabezeile des Benutzers. Zeilen ohne vorangestelltes SQL>enthalten die Ausgaben der Datenbank. Falls wir Ihnen und uns das in diesemBuch wirklich antun wollen.

Drei Punkte (...) stehen für ausgeblendeten, weil an dieser Stelle unwichtigenCode.

Alternative Parameter sind in eckige Klammern eingeschlossen und durch einenSchrägstrich getrennt. Beispiel: [ASCending/DESCending]

Verwendete Systemumgebungen

� Windows 7-64

� Oracle 12c

Verwendete Entwicklungsumgebungen

� Oracle SQL Developer

� Oracle SQL*Plus

Die verwendeten Beispieldaten finden Sie unter http://www.sqlthinking.de.

Kapitel 1

Das Problem mit dem Problem

Bevor man sich auf den Weg vom Problem zum SQL-Statement machen kann,muss man zuerst das Problem genau kennen. Da Zitate sich in einer Einleitungimmer gut machen und wir zufällig ein passendes gefunden haben, lassen wir andieser Stelle den berühmten Strategen Sun Tsu zu Wort kommen: »Wenn du denFeind und dich selbst kennst, brauchst du den Ausgang von hundert Schlachtennicht zu fürchten.« Etwas zeitgemäßer und in die IT übertragen: »Wenn du dieFragestellung und deine Daten kennst, brauchst du das Ergebnis von hundertAbfragen nicht zu fürchten.«

Da fängt es an, das Problem mit dem Problem. Es ist schon Tradition, Anfragen andie IT unpräzise, wolkig oder global-galaktisch zu stellen. Die IT kann Gedankenlesen und liefert automatisch das richtige Ergebnis. Wie das in der Praxis tatsäch-lich aussieht, hat Douglas Adams, der wohl bekannteste Experte für unpräzise Fra-gestellungen und deren Auswirkungen, in seinen Werken ausführlich dargestellt.Auf die Frage nach dem Projekt, dem Budget und dem ganzen Rest können Sieeinfach mit »42%« antworten. Das »%« ist bei Betriebswirten sehr beliebt undunterstreicht die Glaubwürdigkeit Ihrer Antwort.

Das Problem mit dem Problem ist also die präzise Formulierung desselben. Allzuoft schaffen es unpräzise Fragestellungen durch alle Konzeptstufen hindurch biszum Entwickler, der sich dann die fehlenden Anforderungsdetails nach bestemWissen und Gewissen zusammenreimen muss. Das Ergebnis wird dann klassifi-ziert als »works as designed«, eine vornehme Umschreibung für »Das Ergebniskann zwar keiner gebrauchen, aber wir haben geliefert, was bestellt wurde«. Ander präzisen Problembeschreibung bzw. Fragestellung führt kein Weg vorbei.

Die zentrale Frage vor jeder Datenbankabfrage lautet:

»Welche Frage sollen die Daten beantworten?«

Stellen Sie diese Frage demjenigen, der Ihre Daten haben will. Wenn niemandsonst verfügbar ist, stellen Sie sich selbst diese Frage, bevor Sie beginnen.

Damit wir aber jetzt zur Sache kommen können, setzen wir präzise Fragestellun-gen einfach voraus. Wir haben uns das bei den Mathematikern abgeschaut undsagen einfach:

Gegeben sei eine präzise Fragestellung.

13

Kapitel 1Das Problem mit dem Problem

14

1.1 SQL – Die Sprache des Orakels

Die Antworten des Orakels aus Redwood sind zwar verständlicher als damals beiden alten Griechen in Delphi, dafür ist die Befragung etwas komplizierter. DiesesOrakel versteht nur SQL (wenn Sie das »Sssickwl« aussprechen, outen Sie sich alsExperte. Deswegen heißt dieses Buch auch »Sssickwl Thinking«).

SQL (Structured Query Language) ist die standardisierte Datenzugriffssprache fürrelationale Datenbanken. SQL ist keine Programmiersprache, da sich keine Pro-grammabläufe, Verzweigungen oder Schleifen formulieren lassen. SQL unterteiltsich in DDL (Data Definition Language) und DML (Data Manipulation Language).In DDL werden die Datenbankobjekte und -strukturen definiert (zum BeispielTabellen angelegt). Mit DML werden die Daten eingefügt (INSERT), geändert(UPDATE), abgefragt (SELECT) und gelöscht (DELETE).

In diesem Buch liegt der Schwerpunkt auf DML, also dem Arbeiten mit den Datenselbst, und insbesondere auf der Abfrage der Daten mit SELECT. Sie sollen einGefühl für diese Abfragen bekommen. Sie werden sehen, dass man mit SQL-Abfra-gen auf einer Oracle-Datenbank Auswertungen erzeugen kann, die normalerweisemit viel Nach(t)arbeit und einem schwarzen Gürtel in Excel erledigt werden.

Das Schlüsselwort für jede Datenbankabfrage ist SELECT. Die mit SELECT einge-leitete Datenbankabfrage wird SELECT-Statement genannt. Die Grundlage allerSELECT-Statements lässt sich so zusammenfassen:

Mit einem SELECT-Statement formulieren Sie nicht, was ein Programm tun soll.Sie beschreiben, was Sie haben wollen und wo es zu finden ist. Man könnte auchsagen, Sie spielen eine Art »Hol das Stöckchen!« mit der Datenbank. Das erfordertdie gleiche Präzision wie beim Programmieren. Wenn Sie Ihre Abfrage nichtgenau genug formulieren, kann es passieren, dass Sie statt des erwarteten Stöck-chens den ganzen Wald bekommen, nur Holzspäne oder gar nichts.

Ein nicht unbedeutender Seiteneffekt unpräziser Abfragen auf einer Produktions-datenbank ist übrigens eine hohe Systemlast, mit der man die Kollegen und insbe-sondere den Administrator gegen sich aufbringt.

Sprachlich gesehen ist eine Datenbankabfrage eher ein Befehl als eine Frage. AmAnfang steht aber tatsächlich eine Frage. Irgendjemand in der (betriebswirtschaft-lichen) Welt da draußen will irgendetwas wissen, das nur Sie herausfinden kön-nen, weil die Antwort irgendwo da drin (in der Datenbank) ist. Wir beginnen malmit einer alltäglichen Frage.

SELECT irgendwas FROM irgendwoher

1.2Die ersten Fragen

1.2 Die ersten Fragen

A.2.1 Voraussetzungen

In diesem Kapitel arbeiten wir mit dem User SCOTT, der zu jeder Oracle-Daten-bank mitgeliefert wird und in seinem »Schema« ein stark vereinfachtes Datenmo-dell aus dem Bereich Human Resources bereithält. Bitte melden Sie sich mitdiesem User an. (Wie User zuvor entsperrt werden und wie man sich mit demSQL-Developer anmeldet, können Sie in Anhang A nachschlagen. Alternativ kön-nen Sie auch den User SQLTHINKING der in Abschnitt A.3.3 beschriebenenÜbungsdatenbank benutzen.)

Nachdem Sie sich erfolgreich angemeldet haben, öffnet sich automatisch ein SQL-Fenster für die ausgewählte Datenbankverbindung. Nachdem Sie ein Statement ein-gegeben haben, können Sie es mit dem grünen Pfeil oder (Strg)+(¢) ausführen.

Abb. 1.1: Statement im SQL Developer ausführen

1.2.2 Welches Datum haben wir heute?

Nicht auf den Kalender schauen, die Datenbank weiß alles. Wir benötigen ledig-lich einen Funktionsaufruf. Das Datum erfährt man über die SQL-Funktion SYS-DATE. SQL-Funktionen verhalten sich wie Funktionen in Programmiersprachen.Sie rufen die Funktion im SQL-Statement in einer Spalte auf, und bei der Ausgabedes SELECT-Statements steht an der entsprechenden Stelle das Ergebnis derFunktion, der sogenannte Rückgabewert. Im Gegensatz zu SYSDATE benötigendie meisten Funktionen Parameter. Diese hier können wir einfach aufrufen. Nurwie kommen wir an den Rückgabewert heran?

In SQL gibt es kein System.out.println oder printf. Sie können der Datenbank nichtbefehlen, etwas irgendwohin auszugeben. Sie dürfen auswählen. Dann mal los:

Antwort vom Orakel:

SELECT sysdate

ORA-00923: Schlüsselwort FROM nicht an erwarteter Stelle gefunden

15