Einführung in SQL Universeller Zugriff auf Daten in

Elementare Datentypen (3/3) l Datum und Zeit – date belegt 7 Bytes - CC YY MM DD HH MI SS l Funktionen to_date, sysdate l Datum ohne Uhrzeit -> Mitternacht (00: 00) l Standard format: DD-MON-YY l Dipl. -Ing. Walter Sabin -- 2006 10

Operatoren und Literale (1/2) l Operator: manipuliert Datenelemente und liefert ein Ergebnis – unäre Operatoren - l z. B. : +5 oder – 2 – binäre Operatoren - < operand > l +-*/ – Verkettungsoperator – verbindet Zeichenketten l || - z. B. : 'Oracle' || 'Datenbank' ergibt 'Oracle. Datenbank' Dipl. -Ing. Walter Sabin -- 2006 11

Operatoren und Literale (2/2) l Literale – repräsentieren einen konstanten Wert – Text oder Zeichenketten in einfachen Hochkomma: 'Die Schule ist toll' – Ganze Zahlen (Integer): 24 oder – 455 – Zahlen (Number) 24. 45 oder – 433. 78 oder 2. 3 E-4 Dipl. -Ing. Walter Sabin -- 2006 12

Einfache Abfragen – SELECT 1/12 l Allgemeiner Aufbau der Abfrage – SELECT FROM WHERE ORDER BY Liefert als Ergebnis eine Menge von Datensätzen (Dataset oder Resultset) l Beispieldatenbank - Anhang l Dipl. -Ing. Walter Sabin -- 2006 13

Einfache Abfragen – SELECT 2/12 l SQL> SELECT * FROM jobs; JOBID JOB_TITLE MIN_SALARY MAX_SALARY AD_PRES President 20000 40000 Finance Manager 8200 16000 4200 9000 ------FI_MGR FI_ACCOUNT Accountant ------- Dipl. -Ing. Walter Sabin -- 2006 14

Einfache Abfragen – SELECT 3/12 l SQL> SELECT job_title, min_salary FROM jobs; JOB_TITLE President MIN_SALARY 20000 ------Finance Manager 8200 Accountant 4200 ------- Dipl. -Ing. Walter Sabin -- 2006 15

Einfache Abfragen – SELECT 4/12 l l Alias Namen SQL> SELECT job_title AS ‘‘Title‘‘, min_salary AS "Minimum Salary" FROM jobs; Title President Minimum Salary 20000 ------Finance Manager 8200 Accountant 4200 ------- Dipl. -Ing. Walter Sabin -- 2006 16

Einfache Abfragen – SELECT 5/12 l l Eindeutigkeit der Ergebnismenge sicherstellen SQL> SELECT DISTINCT department_id FROM employees; DEPARTMENT_ID 10 20 30 --100 110 12 rows selected Dipl. -Ing. Walter Sabin -- 2006 17

SELECT SYSDATE, USER FROM" src="https://present5.com/presentation/5d6af8c3e3e3f445c422fc4445dde91b/image-18.jpg" alt="Einfache Abfragen – SELECT 6/12 l l Pseudotabelle "dual" SQL> SELECT SYSDATE, USER FROM" /> Einfache Abfragen – SELECT 6/12 l l Pseudotabelle "dual" SQL> SELECT SYSDATE, USER FROM dual; SYSDATE USER 20 -NOV-03 HR Dipl. -Ing. Walter Sabin -- 2006 18

Einfache Abfragen – SELECT 7/12 l l Ergebniszeilen einschränken SQL> SELECT first_name || ' ' || last_name "Name", department_id FROM employees WHERE department_id=90; Name DEPARTMENT_ID Steven King Neena Kochhar 90 Lex De Haan l 90 90 Vergleichsoperatoren l = <> (!=, ^=) <, <=, >, >= l Logische Operatoren: NOT, AND, OR Dipl. -Ing. Walter Sabin -- 2006 19

Einfache Abfragen – SELECT 8/12 l Sonstige Operatoren – IN, NOT IN, BETWEEN, EXISTS l SELECT * FROM employees WHERE department_id in (10, 20, 90); l SELECT * FROM employees WHERE salary BETWEEN 5000 and 6000; l SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d. department_id = e. department_id AND d. department_name = 'Administration'); Dipl. -Ing. Walter Sabin -- 2006 20

Einfache Abfragen – SELECT 9/12 l Null Werte – IS NULL, IS NOT NULL l SELECT last_name FROM employees WHERE department_id IS NULL; l LIKE – Pattern-Matching – _, %, Escape clause _ matched einzelnes Zeichen % matched beliebig viele Zeichen Escape Claus erlaubt die Verwendung obiger Zeichen l SELECT * FROM jobs WHERE job_id LIKE 'AC_%' ESCAPE ''; WHERE salary BETWEEN 5000 and 6000; Dipl. -Ing. Walter Sabin -- 2006 21

Einfache Abfragen – SELECT 10/12 l l Sortieren der Ergebniszeilen SQL> SELECT first_name || ' ' || last_name "Mitarbeiter Name" FROM employees WHERE department_id=90 ORDER BY last_name; Mitarbeiter Name ----------Lex De Haan Steven King Neena Kochar Dipl. -Ing. Walter Sabin -- 2006 22

Einfache Abfragen – SELECT 11/12 l l Sortieren der Ergebniszeilen mit DISTINCT SQL> SELECT DISTINCT 'Region ' || region_id FROM hr. countries ORDER BY region_id; ERROR ORA-01791: not a SELECTed expression l SQL> SELECT DISTINCT 'Region ' || region_id FROM hr. countries ORDER BY 'Region ' || region_id; 'Region ' || region_id ------------Region 1 Region 2 Region 3 Region 4 Dipl. -Ing. Walter Sabin -- 2006 23

Einfache Abfragen – SELECT 12/12 l Expressions verwenden z. B. : in SELECT Klausel – SELECT ((2*4)/(3+1))*10 FROM dual; l CASE Expression - "if. . then. . else" Logik – SELECT country_name, CASE region_id WHEN 1 THEN 'Europa' WHEN 2 THEN 'AMERIKA' WHEN 3 THEN 'Asien' ELSE 'Anderes' END "Kontinent" FROM countries WHERE country_name LIKE 'I%'; COUNTRY_NAME Kontinent Israel India Italy Anderes Asien Europa Dipl. -Ing. Walter Sabin -- 2006 24

Quiz ? Dipl. -Ing. Walter Sabin -- 2006 25

Zusammenfassung Geschichte von SQL. l Elementare Datentypen und Operatoren. l Einfache Abfragebefehle mit l – SELECT, FROM, WHERE und ORDER BY – Operatoren in der WHERE Klause – Pattern-Matching Operatoren Dipl. -Ing. Walter Sabin -- 2006 26

Jemand versucht den Wert: 34567. 2255 in ein Feld mit dem Datentyp number(7, 2) zu speichern. Welcher Wert wird tatsächlich gespeichert? A – 34567. 00 l B – 34567. 23 l C – 34567. 22 l D – 3456. 22 l Dipl. -Ing. Walter Sabin -- 2006 27

Welche Standardanzeigelänge hat ein Datumsfeld? A– 8 l B – 19 l. C– 9 l. D– 6 l Dipl. -Ing. Walter Sabin -- 2006 28

Gegeben ist die folgende Abfrage: SELECT 'Mitarbeiter Name: ' || ename FROM emp where deptno=10; Welche Komponente ist ein Literal? A – 10 l B – ename l C – emp l D – || l Dipl. -Ing. Walter Sabin -- 2006 29

Welche Klause in einer Abfrage schränkt die Anzahl der Datensätze ein? A – ORDER BY l B – SELECT l C – FROM l D – WHERE l Dipl. -Ing. Walter Sabin -- 2006 30

Gegeben ist die folgende Abfrage: SELECT empno, ename FROM emp where empno=7782 or empno=7876; Welcher Operator kann die "OR Bedingung" ersetzen? A – IN l B – BETWEEN. . AND. . l C – LIKE l D – <= l Dipl. -Ing. Walter Sabin -- 2006 31

Jemand versucht den Wert: 12345678 in ein Feld mit dem Datentyp number(5, -2) zu speichern. Welcher Wert wird tatsächlich gespeichert? A – 12345600 l B – 123456. 78 l C – Fehler l D – 123456 l Dipl. -Ing. Walter Sabin -- 2006 32

Folgende Klauseln können in SQL Select statements vorkommen: 1. WHERE 2. FROM 3. ORDER BY In welcher Reihenfolge treten sie auf? A – 1, 3, 2 l B – 2, 1, 3 l C – 2, 3, 1 l D – Die Reihenfolge ist gleichgültig l Dipl. -Ing. Walter Sabin -- 2006 33

FUNKTIONEN l „Single Row“ - Funktionen – Grundlagen – Zeichen Funktionen – Numerische Funktionen – Datums – Funktionen – Konvertierungsfunktionen – Sonstige Funktionen l „Group“ – Funktionen – Grundlagen Dipl. -Ing. Walter Sabin -- 2006 34

FUNKTIONEN Single Row 1/ 16 Verschiedene Datentype als Argumente l Auch in PL/SQL verwendbar l Verwendbar unter anderem in SELECT, WHERE und ORDER BY Klauseln l Z. B. : l – SELECT last_name, TO_CHAR(hire_date, 'Day, DD-MON-YYYY') FROM employees WHERE UPPER(last_name) like 'AL%' ORDER BY soundex(last_name); Dipl. -Ing. Walter Sabin -- 2006 35

FUNKTIONEN Single Row 2/16 l NULL Werte Funktionen – NVL(Ausdruck 1, Ausdruck 2) SELECT last_name, salary*commission_pct bonus, (salary+salary*NVL(commission_pct, 0)) Gehalt FROM employees, – NVL 2(Ausdruck 1, Ausdruck 2, Ausdruck 3) NVL 2(commission_pct, salary+salary* commission_pct, salary) Dipl. -Ing. Walter Sabin -- 2006 36

FUNKTIONEN Single Row 3/16 l Zeichenfunktionen – ASCII(c 1) liefert den Ascii Wert des ersten Zeichens in c 1 l SELECT ASCII('A') GR_A, ASCII('z') KL_Z FROM dual; GR_A KL_Z 65 122 l – CHR(i) l Liefert das Zeichen entsprechend dem Wert i SELECT CHR(65), chr(122), chr(223) FROM dual; Dipl. -Ing. Walter Sabin -- 2006 37

FUNKTIONEN Single Row 4/ 16 l Zeichenfunktionen (Fs) – INITCAP(c 1) liefert den ersten Buchstaben jedes Wortes in c 1 als Großbuchstaben und alle anderen als Kleinbuchstaben l SELECT INITCAP('die drei lauser') FROM dual; => Die Drei Lauser l – LENGTH(c) Liefert Länge einer Zeichenkette in Zeichen l SELECT LENGTH('Die Spengergasse') FROM DUAL; => 16 l Dipl. -Ing. Walter Sabin -- 2006 38

FUNKTIONEN Single Row 5/16 l Zeichenfunktionen (Fs) – INSTR(c 1, c 2[, i [, j]]) l l Liefert die Zeichenposition für das j-te Vorkommen von c 2 in c 1, beginnend mit Position i. Negatives i bedeutet Suche von rechts (sonst von links) SELECT INSTR('Mississippi', 'i', -2, 3) FROM dual; => 2 – SUBSTR(c 1, i[, j]) l l liefert einen Teilstring aus c 1 beginnend an der Position i von j Zeichen. Ist j < 0 => Rest des Strings. Ist í < 0 => Zählen der Position von rechts. SELECT SUBSTR('Die Spenger Gasse', 5, 7) FROM dual; => Spenger Dipl. -Ing. Walter Sabin -- 2006 39

FUNKTIONEN Single Row 6/ 16 l Zeichenfunktionen (Fs) – LOWER(c 1) l Kovertiert alle Zeichen auf Kleinbuchstaben – UPPER(c 1) l Konvertiert alle Zeichen auf Großbuchstaben – SELECT LOWER(job_id), last_name FROM employees WHERE UPPER(last_name) LIKE 'KIN%'; => ad_pres King Dipl. -Ing. Walter Sabin -- 2006 40

FUNKTIONEN Single Row 7/ 16 l Zeichenfunktionen (Fs) – LPAD(c 1, i[, c 2]) l l Erweitert den String c 1 auf i Zeichen. Verwendet c 2 um den freien Raum links aufzufüllen SELECT LPAD(job_id, 10, '. ') FROM employees =>. . . AD_PRES usw. – RPAD(c 1, i[, c 2]) l l Erweitert den String c 1 auf i Zeichen. Verwendet c 2 um den freien Raum rechts aufzufüllen SELECT RPAD(job_id, 10, '. ') FROM employees => AD_PRES. . . usw. Dipl. -Ing. Walter Sabin -- 2006 41

FUNKTIONEN Single Row 8/ 16 l Zeichenfunktionen (Fs) – LTRIM(c 1, c 2) Diese Funktion liefert c 1 ohne die führenden Zeichen aus c 2. Default: ' ' l SELECT LTRIM('Mississippi', 'Mis') FROM dual; =>ppi l – RTRIM(c 1, c 2) l Diese Funktion liefert c 1 ohne die nachlaufenden Zeichen aus c 2. Default: ' ' Dipl. -Ing. Walter Sabin -- 2006 42

FUNKTIONEN Single Row 9/ 16 l Zeichenfunktionen (Fs) – REPLACE(c 1, c 2[c 3]) Liefert c 1 wobei alle Strings c 2 durch c 3 ersezt werden l SELECT REPLACE('uptown', 'up', 'down') FROM dual; => downtown l – SOUNDEX(c 1) Liefert die phonetische Darstellung von C 1 l SELECT SOUNDEX('Dawes' from dual); => D 200 l Dipl. -Ing. Walter Sabin -- 2006 43

FUNKTIONEN Single Row 10/ 16 l Zeichenfunktionen (Fs) – TRANSLATE(c 1, c 2, c 3) l l l Liefert c 1 wobei alle in c 1 vorkommenden Zeichen aus c 2 durch die der Position in c 2 entsprechenden Zeichen aus c 3 ersetzt werden. SELECT TRANSLATE('Mississippi', 'Mis', 'm. IS') FROM dual; => m. ISSISSIpp. I SELECT translate(KUNDEN. RORT, 'ÄÖÜäöüß', chr(142) || chr(153) || chr(154) || chr(132) || chr(148) || chr(129) || chr(225)) RORT FROM KUNDEN where knr=882; Dipl. -Ing. Walter Sabin -- 2006 44

FUNKTIONEN Single Row 11/ 16 l Numerische Funktionen – ABS(n) – Absolutbetrag von n – COS, SIN, TAN, ASIN, ACOS COSH, SINH l Winkelfunktionen – CEIL(n) – Kleinster ganzzahliger Wert größer oder gleich n l SELECT CEIL(9. 8), CEIL(-32. 85) from dual; => 10 -32 – FLOOR(n) Größter ganzzahliger Wert kleiner oder gleich n l SELECT FLOOR(9. 8), FLOOR(-32. 85) from dual; => 9 -33 Dipl. -Ing. Walter Sabin -- 2006 45

FUNKTIONEN Single Row 12/ 16 l Numerische Funktionen (Fs) – LN(n) – Natürlicher Logarithmus von n l SELECT LN(2. 7) FROM dual; => 0, 993251773010283 – LOG(n 1, n 2) l l Liefert den Logarithmus von n 1 zur Basis n 2 SELECT LOG(27, 3) FROM dual; => 0, 33333333 – MOD(n 1, n 2) l l Liefert n 1 modulo n 2 SELECT MOD(14, 5) FROM dual; => 4 Dipl. -Ing. Walter Sabin -- 2006 46

FUNKTIONEN Single Row 13/ 16 l Numerische Funktionen (Fs) – POWER(n 1, n 2) – Liefert n 1 hoch n 2 – SQRT(n) – Quadratwurzel aus n – ROUND(n 1, n 2) – Liefert n 1 gerundet auf n 2 Stellen – SIGN(n) – liefert 1 falls n pos. -1 falls negativ, 0 wenn 0 – TRUNC(n 1, n 2) – Liefert n 1 auf n 2 Stellen abgeschnitten Dipl. -Ing. Walter Sabin -- 2006 47

FUNKTIONEN Single Row 14/ 16 l Datumsfunktionen – ADD_MONTHS(d, i) l Addiert i Monate zu Datum d – MONTHS_BETWEEN(d 1, d 2) l Liefert die Anzahl der Monate zwischen d 1 und d 2 – SELECT MONTHS_BETWEEN('19. 12. 2002', '19. 03. 2002') test from dual; => 9 – LAST_DAY(d) l Liefert den letzten Tag des Monats für das Datum d – SELECT LAST_DAY(SYSDATE), LAST_DAY(SYSDATE) + 1 from dual; Dipl. -Ing. Walter Sabin -- 2006 48

FUNKTIONEN Single Row 15/16 l Datumsfunktionen (Fs) – EXTRACT(c FROM d) Liefert die durch c angegebene Komponente von d. c: YEAR, MONTH, DAY, HOUR, MIN, SECOND l SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual; => 1 – TRUNC(d[, fmt]) Liefert ein Datum abgeschnitten je nach fmt. l SELECT TRUNC(last_analysed, 'HH') FROM user_tables WHERE table_name='TEST_CASE'; => 10 -Jan-2003 11: 00 Dipl. -Ing. Walter Sabin -- 2006 49

FUNKTIONEN Single Row 16/16 l Conversionsfunktionen – TO_CHAR(x[, fmt]) – x: Datum oder Zahl, fmt: Formatcode l SELECT to_char(SYSDATE, 'DD-MM-YYYY HH: MI'), to_char(12. 3, '0009. 90') => 13 -01 -2003 19: 35 0012. 30 – TO_NUMBER(c[, fmt]) – Liefert Zahl aus String – TO_DATE(c[, fmt]) – Liefert Datum aus String l Sonstige Funktionen – DECODE(x, m 1, r 1, m 2, r 2, . . , d) l SELECT DECODE(command, 0, 'None', 2, 'Insert', 3, 'Select'. . . , 'Other') cmd from v$session where type <> 'BACKGROUND'; – LEAST(exp_list), GREATEST(exp_list) Dipl. -Ing. Walter Sabin -- 2006 50

FUNKTIONEN "Group" l Grundlagen – Aggregat Funktionen – Wert basierend auf Inputmenge – Vernachlässigt Nullwerte und liefern keine Nullwerte (Ausnahme: sum bei nur Nullwerten) Dipl. -Ing. Walter Sabin -- 2006 51

FUNKTIONEN "Group" (Fs) l Elementare Funktionen – COUNT {* | [DISTINCT | ALL] x } l Anzahl – SUM {[DISTINCT | ALL] x } l Summe – MAX {[DISTINCT | ALL] x } l Maximalwert – MIN {[DISTINCT | ALL] x } l Minimal – AVG {[DISTINCT | ALL] x } l Durchschnitt Dipl. -Ing. Walter Sabin -- 2006 52

FUNKTIONEN "Group" (Fs) l l Statement: SELECT employee_id, salary FROM hr. employees WHERE department_id = 60 ORDER BY salary; Ergebnis: EMPLOYEE_ID SALARY 107 4200 105 4800 106 4800 104 6000 103 9000 Dipl. -Ing. Walter Sabin -- 2006 53

FUNKTIONEN "Group" (Fs) l SELECT AVG(salary) avg, AVG(ALL salary) avg_all, AVG(DISTINCT salary) avg_dist, COUNT(salary) cnt, COUNT(DISTINCT salary) cnt_dist, SUM(salary) sum_all, SUM(DISTINCT salary) sum_dist FROM hr. employees WHERE department_id = 60 ORDER BY salary; l Ergebnis avg 5760 avg_all avg_dist cnt_dist sum_all 5760 6000 Dipl. -Ing. Walter Sabin -- 2006 5 4 28800 sum_dist 24000 54

FUNKTIONEN "Group" (Fs) l Klausel: GROUP BY – Gruppieren von Daten nach einem oder mehreren Kriterien – SELECT department_id, count(*) MA_Anzahl FROM hr. employees GROUP BY department_id; – Ergebnis: DEPARTMENT_ID MA_ANZAHL 10 20 30 40 50 60 1 2 6 1 45 5 Dipl. -Ing. Walter Sabin -- 2006 70 80 90 100 110 1 34 3 6 2 1 55

FUNKTIONEN "Group" (Fs) – Klausel: HAVING Einschränken der zurückgelieferten Gruppen l Keine Gruppenfunktionen in der where Klausel l SELECT department_id DID, sum(salary) SUMSAL, count(*) ANZ FROM hr. employees GROUP BY department_id HAVING count(*) > 10; l Ergebnis: DID SUMSAL ANZ 50 156. 400 45 80 304. 500 34 l Dipl. -Ing. Walter Sabin -- 2006 56

Joins und Subqueries l l l Selektieren von Daten aus mehreren Tabellen FROM Klausel enthält mehrere Tabellenangaben "JOIN" verbindet Datenzeilen aus mehreren Tabellen Um die Datenmenge zu reduzieren sollten die Beziehungen zwischen den einzelnen Datensätzen in der where Klausel spezifiziert werden alternativ kann aoch eine (neue) JOIN Klausel ab Oracle 9 i verwendet werden (ISO / ANSI SQL 1999) ohne diese Klauseln -> kartesisches Produkt Dipl. -Ing. Walter Sabin -- 2006 57

Einfache JOINs 2 Tabellen und "=" Operator l SELECT locations. location_id, city, department_name FROM locations, departments WHERE locations. location_id = departments. location_id l Dipl. -Ing. Walter Sabin -- 2006 58

Tabellen Aliase l temporäres Umbenennen von Tabellen – SELECT l. location_id, city, department_name FROM locations l, departments d WHERE l. location_id = d. location_id AND country_id != 'US'; l mit Schemaname – SELECT locations. location_id, hr. locations. city, department_name FROM hr. locations, hr. departments WHERE location_id = departments. location_id; Dipl. -Ing. Walter Sabin -- 2006 59

JOINS – ANSI Syntax l NATURAL JOIN – kein Alias möglich – basiert auf Feldern mit gleichen Namen – SELECT location_id, city, department_name FROM locations NATURAL JOIN departments; – SELECT region_name, country_name, city FROM regions NATURAL JOIN countries NATURAL JOIN locations; Dipl. -Ing. Walter Sabin -- 2006 60

JOINS – ANSI Syntax Fs. l JOIN. . USING – spezifiziert die Felder, die für das JOIN verwendet werden sollen – SELECT region_name, country_name, city FROM regions JOIN countries USING (region_id) JOIN locations USING (country_id) – Fehlerhaft: SELECT region_name, country_name, city FROM regions JOIN locations USING (country_id) JOIN countries USING (region_id) Dipl. -Ing. Walter Sabin -- 2006 61

JOINS – ANSI Syntax Fs. l JOIN. . ON – wenn es keine gemeinsamen Feldnamen gibt – SELECT region_name, country_name, city FROM regions r JOIN countries c ON r. region_id = c. region_id JOIN locations l ON c. country_id = l. country_id where c. country_id = 'US'; Dipl. -Ing. Walter Sabin -- 2006 62

Kartesische JOINS verbindet jede Zeile der ersten Tabelle mit jeder Zeile der zweiten Tabelle l möglichst vermeiden – Performance! l zumindest immer n-1 JOIN Bedingungen (n. . Anzahl der Tabellen in der FROM Klausel) l SELECT region_name, country_name FROM regions, countries where countries. country_ld LIKE 'I%'; l Dipl. -Ing. Walter Sabin -- 2006 63

OUTER JOINS Liefert alle Werte einer Tabelle auch bei fehlenden Entsprechungen in der zweiten Tabelle l 2 Schreibweisen: l – Traditionell: (+) zu Feld in der where Klause – ANSI: LEFT JOIN, RIGHT JOIN, FULL JOIN l SELECT c. country_name, l. city FROM countries c, locations l WHERE c. country_id = l. country_id (+); Dipl. -Ing. Walter Sabin -- 2006 64

OUTER JOINS Fs. l l l SELECT c. country_name, l. city FROM countries c, locations l WHERE c. country_id (+) = l. country_id; SELECT c. country_name, l. city FROM countries c, LEFT JOIN locations l ON c. country_id = l. country_id; SELECT c. country_name, l. city FROM countries c, RIGHT JOIN locations l ON c. country_id = l. country_id; Dipl. -Ing. Walter Sabin -- 2006 65

OUTER JOIN Fs. l Full OUTER JOIN – neu in Oracle 9 i – nur ANSI Syntax möglich – SELECT e. employee_id, e. last_name, d. department_id, d. department_name FROM employees e FULL OUTER JOIN departments d ON e. department_id = d. department_id; Dipl. -Ing. Walter Sabin -- 2006 66

Andere JOINS l SELF-Joins – verbindet eine Tabelle mit sich selbst – Beispiel: Liste die Mitarbeiternamen und die dazugehörigen Managernamen aus der employees Tabelle: SELECT e. last_name Mitarbeiter, m. last_name Manager FROM employees e, employees m WHERE m. employee_id = e. manager_id; l ANSI Syntax ? ? ? l Dipl. -Ing. Walter Sabin -- 2006 67

Andere JOINS Fs l Nicht Gleichheits JOIN – Tabelle grades: SELECT * FROM grades; GRADE LOW_SALARY HIGH_SALARY P 5 0 3000 P 4 3001 5000 P 3 5001 7000 P 2 7001 9000 P 1 10001 Dipl. -Ing. Walter Sabin -- 2006 68

Andere JOINS Fs l Nicht Gleichheits JOIN Fs – SELECT last_name, salary, grade FROM employees, grades WHERE last_name LIKE 'R%' AND salary >= low_salary AND salary <= NVL(high_salary, salary); Raphaely Rogers Rajs Russell Dipl. -Ing. Walter Sabin -- 2006 11000 2900 3500 14000 P 1 P 5 P 4 P 1 69

Andere JOINS Fs. l Set – Operatoren – Kombinieren die Ergebnismengen von zwei Abfragen zu einer Ergebnismenge – Datentypen und Feldanzahl beider Abfragen sollten übereinstimmen – Die Namen der Felder aus der ersten Abfrage werden für die Bezeichnung der Ergebnisfelder verwendet – UNION Liefert alle eindeutigen Datensätze beider Abfragen – UNION ALL liefert alle Datensätze beider Abfragen – INTERSECT Liefert die Datensätze, die in beiden Abfragen gleich sind – MINUS liefert eindeutige Zeilen der ersten Abfrage abzüglich der Zeilen der zweiten Abfrage Dipl. -Ing. Walter Sabin -- 2006 70

Andere JOINS Fs. l SELECT last_name, hire_date FROM employees WHERE department_id = 90 UNION SELECT last_name, hire_date FROM employees WHERE lastname LIKE 'K%' (3 Zeilen + 6 Zeilen) ergibt 7 Zeilen Dipl. -Ing. Walter Sabin -- 2006 71

Andere JOINS Fs. Sortierung durch ein! ORDER BY möglich l SELECT last_name, hire_date FROM employees WHERE department_id = 90 UNION SELECT last_name, hire_date FROM employees WHERE lastname LIKE 'K%' ORDER BY first_name l Dipl. -Ing. Walter Sabin -- 2006 72

Single Row Subqueries Ein Query im Query l Leifert nur einen Datensatz als Ergebnis l – SELECT last_name, first_name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees) Dipl. -Ing. Walter Sabin -- 2006 73

Multiple Row Subqueries Liefern mehr als einen Datensatz vom Subquery l Sicherer wenn die Ergebnismenge nicht sicher ist l SELECT last_name, first_name FROM employees where department_id in (SELECT department_id FROM employees WHERE first_name = 'John'); l Dipl. -Ing. Walter Sabin -- 2006 74

Korrelierte Subqueries Subquery bezieht sich auf Felder des Mutterqueries l Subquery wird für jeden zurückgelieferten Datensatz des Mutterqueries ausgeführt l SELECT department_id, last_name, salary FROM employees e 1 WHERE salary = (select max(salary) FROM employees e 2 WHERE e 1. department_id = e 2. department_id) ORDER by 1, 2, 3; l Dipl. -Ing. Walter Sabin -- 2006 75

Skalare Subqueries l l Liefern genau einen Wert Können an vielen Stellen statt einem Feldwert verwendet werden Nicht in GROUP BY und HAVING Klauseln in einem CASE Ausdruck: SELECT city, country_id, (CASE WHEN country_id in (SELECT country_id FROM countries WHERE country_name = 'India') THEN 'Indian' ELSE 'NON-Indian' END) "INDIA? " FROM locations where city LIKE 'B%'; Dipl. -Ing. Walter Sabin -- 2006 76

Skalare Subqueries Fs. l In einer SELECT Klausel – SELECT last_name, department_id, (select MAX(salary) FROM employees sq WHERE sq. department_id = e. department_id) Hi. Sal FROM employees e WHERE last_name LIKE 'R%'; Dipl. -Ing. Walter Sabin -- 2006 77

Skalare Subqueries Fs. In einer where Klausel – SELECT department_name, manager_id, (SELECT last_name FROM employees e WHERE e. employee_id = d. manager_id) MGR_NAME FROM departments d WHERE ((SELECT country_id FROM locations l WHERE d. location_id = l. location_id) IN (SELECT country_id FROM countries c WHERE c. country_name = 'United States. . ' OR c. country_name = 'Canada')) AND d. manager_id IS NOT NULL; Dipl. -Ing. Walter Sabin -- 2006 78

DML Data Manipulation Language l Ändern von Daten in Tabellen l Koordinieren von mehrfachen Veränderungen l Verwendung von Locks l Steuerung der Änderungen l Dipl. -Ing. Walter Sabin -- 2006 79

DML – Modifying Data INSERT – fügt Datensätze in Tabellen ein l UPDATE – Ändert Feldwerte in Tabellen l MERGE – Fügt ein oder ändert l DELETE - Löscht Datensätze l SELECT FOR UPDATE – Verhindert gleichzeitiges Verändern durch andere Transaktionen l LOCK TABLE – Schützt vor gleichzeitiger Veränderung l Dipl. -Ing. Walter Sabin -- 2006 80

DML - INSERT Fügt Datensätze in ein oder mehrere Tabellen ein l Werte können eingegeben werden l Werte können von Subqueries ermittelt werden l Dipl. -Ing. Walter Sabin -- 2006 81

DML – Single Table INSERT INTO departments (department_id, department_name, manager_id, location_id) VALUES (280, 'Cash Management', 108, 1700) l NULL Werte werden in fehlende Spalten eingefügt l Dipl. -Ing. Walter Sabin -- 2006 82

DML – Single Table INSERT Fs. l l l Folgende Statements sind äquivalent INSERT INTO departments (department_id, department_name, manager_id, location_id) VALUES (280, 'Cash Management', NULL, 1700) INSERT INTO departments (department_id, department_name, location_id) VALUES (280, 'Cash Management', 1700) Dipl. -Ing. Walter Sabin -- 2006 83

DML – Single Table INSERT Fs. Verwendung von Subselects l INSERT INTO job_history (employee_id, start_date, job_id) SELECT employee_id, to_date('01. 1900', 'DD. MM. YYYY'), job_id from employees; l Dipl. -Ing. Walter Sabin -- 2006 84

DML – Multiple Table INSERT Neu in 9 i l INSERT ALL WHEN Bedingung THEN Into Klausel ELSE Into Klausel; l Verkaufsdaten – Tabelle vkdat: anr. . Artikelnummer, agr. . Artikelgruppe vdat. . Verkaufsdatum, knr. . Kundennr. sq. . Verkaufsmenge, sp. . Verkaufspreis agr: B. . Bücher, V. . Video oder P. . Papier l Dipl. -Ing. Walter Sabin -- 2006 85

DML – Multiple Table INSERT Fs. Tabellen: vkbuch, vkvideo, vkpapier l Allg. Felder anr, vdat, knr, ums l Spezielle Felder vkbuch: isbn, vkvideo: ej -- Erscheinungsjahr, vkpapier: atyp -- Artikeltyp l Dipl. -Ing. Walter Sabin -- 2006 86

DML – Multiple Table INSERT Fs. INSERT ALL l WHEN agr='B' THEN INTO vkbuch (anr, vdat, knr, ums) VALUES (anr, vdat, knr, u) WHEN agr='V' THEN INTO vkvideo (anr, vdat, knr, ums) VALUES (anr, vdat, knr, u) WHEN agr='P' THEN INTO vkpapier (anr, vdat, knr, ums) VALUES (anr, vdat, knr, u) SELECT agr, vdat, knr, sp*sq u FROM vkdat l Dipl. -Ing. Walter Sabin -- 2006 87

DML - UPDATE Modifiziert vorhandene Datensätze in einer Tabelle l UPDATE tabellenname set (Felder, . . . ) = (subquery) WHERE Bedingung l UPDATE tabellenname set Feld 1 = Wert 1, Feld 2 = Wert 2. . . WHERE Bedingung l Dipl. -Ing. Walter Sabin -- 2006 88

DML - UPDATE Fs. UPDATE employees SET commission_pct = 0. 01 WHERE commission_pct is NULL; l UPDATE employees SET salary = salary*0. 15, commission_pct = 0. 2 WHERE department_id = 60; l Dipl. -Ing. Walter Sabin -- 2006 89

DML - UPDATE Fs. l UPDATE job_history j SET start_date = (SELECT hire_date FROM employees e WHERE e. employee_id=j. employee_id) WHERE NOT EXISTS (select * from job_history j 2 where j 2. end_date is not null and j. employee_id = j 2. employee_id); Dipl. -Ing. Walter Sabin -- 2006 90

DML - UPDATE Fs. l UPDATE job_history j 1 SET start_date = (SELECT end_date + 1 FROM job_history j 2 WHERE j 2. end_date is not NULL and j 2. employee_id = j 1. employee_id and j 2. end_date = (select max(j 3. end_date) FROM job_history j 3 WHERE j 3. end_date is not NULL and j 3. employee_id = j 2. employee_id)) WHERE j 1. end_date is NULL and exists (SELECT * from job_history j 4 where j 4. employee_id=j 1. employee_id and j 4. end_date is not NULL) Dipl. -Ing. Walter Sabin -- 2006 91

DML - MERGE l l MERGE INTO tabelle USING {tabelle | subquery | view} ON (bedingung) WHEN MATCHED THEN UPDATE SET. . . WHEN NOT MATCHED THEN INSERT. . . VALUES (. . . ); select * from pi P_ID C_ID L_P M_P 2986 33 135 121 3163 33 35 29 3165 33 40 34 3164 33 40 35 3166 33 40 32 Dipl. -Ing. Walter Sabin -- 2006 92

DML - MERGE Fs. l select * from np; P_ID 2986 3163 3164 l L_P 135 40 40 M_P 111 32 35 merge into pi pi using (select p_id, l_p, m_p from np) np on (pi. p_id = np. p_id) when matched then update set pi. l_p = np. l_p, pi. m_p = np. m_p when not matched then insert (pi. p_id, pi. c_id, pi. l_p, pi. m_p) values (np. p_id, 33, np. l_id, np. m_p); Dipl. -Ing. Walter Sabin -- 2006 93

DML - MERGE Fs. Ergebnis: l select * from pi; P_ID C_ID 2986 33 135 3163 33 40 3165 33 40 3164 33 40 l Dipl. -Ing. Walter Sabin -- 2006 L_P M_P 111 32 34 35 94

DML - DELETE - TRUNCATE DELETE FROM table WHERE bedingung l DELETE FROM employees WHERE salary > 15000; l Löschen der gesamten Tabelle ohne Rollback l TRUNCATE table [DROP | REUSE] STORAGE l schnell, benötigt wenig Resourcen l Dipl. -Ing. Walter Sabin -- 2006 95

DML - LOCKING l l SELECT. . FOR UPDATE SELECT * from employees WHERE NVL(commission_pct, 0) > 0 FOR UPDATE; Sperren gesamter Tabelle LOCK table IN lockmode: EXCLUSIVE MODE. . . queries erlaubt, alles andere nicht SHARE MODE. . . verhindert Updates ROW SHARE MODE. . verhindert exclusiven LOCK Dipl. -Ing. Walter Sabin -- 2006 96

DML - Transaktionskontrolle l COMMIT – Beendet Transaktion – Änderungen werden permanent – Änderungen werden sichtbar l ROLLBACK – Alle Änderungen (außer DDL) werden zurückgesetzt l l SAVEPOINT Name Setzt eine benannte Marke ROLLBACK TO SAVEPOINT – Setzt bis zur benannten Marke wieder zurück Dipl. -Ing. Walter Sabin -- 2006 97

TABELLEN - Datentypen l Zeichen: CHAR(2000 Zeichen), VARCHAR 2(4000 Zeichen), CLOB(4 GB), LONG(2 GB - alt) l Numerische Datentypen: NUMBER[ ( [,

TABELLEN - Datentypen Fs. Binäre Datentypen RAW (2000 Byte), BLOB (bis 4 GB), BFILE(external FILE bis 4 GB) l Sonstige Datentypen ROWID – Pseudospalte bei jeder Tabelle (physische Datensatzadresse – schnellster Zugriffsweg, kann sich bei update ändern) l Dipl. -Ing. Walter Sabin -- 2006 99

TABELLEN - Erstellen CREATE TABLE l CREATE TABLE produkte ( produkt_id NUMBER (4), produkt_name VARCHAR 2(50), lager_menge NUMBER(15), preis NUMBER(15, 2) ); l Dipl. -Ing. Walter Sabin -- 2006 100

TABELLEN - Erstellen Fs. l Zusatzinformation beim Erstellen von Tabellen: – Standardwerte – "Constraints" – Tabellenart – Speicherangaben – "Tablespace" – "Partitionierungs" – Information Dipl. -Ing. Walter Sabin -- 2006 101

TABELLEN - Erstellen Fs. Namen von Tabellen und Feldern: maximal 30 Zeichen lang l Alphanumerische Zeichen + "_, $, #" erlaubt (beginnend mit Aplhabetischem Zeichen l Caseinsensitiv wenn nicht in "" eingeschlossen l Dipl. -Ing. Walter Sabin -- 2006 102

TABELLEN - Erstellen Fs. CREATE TABLE auftraege ( auftrags_nummer NUMBER(8), status VARCHAR 2(10) DEFAULT 'PENDING'); l INSERT INTO auftraege (auftrags_nummer) VALUES (4004); l SELECT * FROM ORDERS; AUFTRAGS_NUMMER STATUS 4004 PENDING l Dipl. -Ing. Walter Sabin -- 2006 103

TABELLEN - Erstellen Fs. l NULL Werte unterdrücken - Constraints l CREATE TABLE JOB_HISTORY ( EMPLOYEE_ID NUMBER (6) CONSTRAINT JHIST_EMPLOYEE_NN NOT NULL, START_DATE CONSTRAINT JHIST_START_DATE_NN NOT NULL, END_DATE, JOB_ID VARCHAR 2 (10), DEPARTMENT_ID NUMBER (4), CONSTRAINT JHIST_DATE_INTERVAL CHECK (end_date > start_date), CONSTRAINT JHIST_EMP_ID_ST_DATE_PK PRIMARY KEY ( EMPLOYEE_ID, START_DATE ) ) ; Dipl. -Ing. Walter Sabin -- 2006 104

TABELLEN - Erstellen Fs. l l Erstellen einer Tabelle aus einer anderen Tabelle CREATE TABLE emp 2 AS SELECT * FROM EMPLOYEES – leere Tabelle wenn Query keine Datensätze liefert – Alias Namen für neue Feldnamen – Nur "NOT NULL" constraint wird kopiert andere constraints nicht Dipl. -Ing. Walter Sabin -- 2006 105

TABELLEN - Verändern l Felder hinzufügen – ALTER TABLE employees ADD city VARCHAR 2(30); l Felddatentypen verändern – ALTER TABLE departments MODIFY department_id NUMBER(12); l Felder löschen – ALTER TABLE employees DROP COLUMN city; l TABELLEN löschen – DROP TABLE emp 2; l TABELLEN umbenennen – RENAME emp 2 TO employees_save l DESCRIBE Tabellenname – listet alle Felder einer Tabelle Dipl. -Ing. Walter Sabin -- 2006 106

CONSTRAINTS l NOT NULL – CREATE TABLE orders ( order_num Number (4) CONSTRAINT nn_order NOT NULL, order_date DATE NOT NULL, product_id NUMBER(14)); l Check Constraints – CREATE TABLE bonus ( emp_id VARCHAR 2 (40) NOT NULL, salary NUMBER (13, 2), bonus NUMBER(13, 2), CONSTRAINT ck_bonus CHECK (bonus > 0)) Dipl. -Ing. Walter Sabin -- 2006 107

CONSTRAINTS Fs. l UNIQUE Constraints – Definition auf Feldebene (Einzelfeld) oder Tabellenebene (mehrere Felder) – ALTER TABLE employees ADD ssn VARCHAR 2(11) CONSTRAINT uk_ssn UNIQUE; – ALTER TABLE employees ADD CONSTRAINT uk_name UNIQUE (first_name, last_name); – Erstellt automatisch einen Index – Erlaubt Nullwerte Dipl. -Ing. Walter Sabin -- 2006 108

CONSTRAINTS Fs. l PRIMARY KEY Constraints – Erlaubt keine Nullwerte – Nur ein PRIMARY KEY je Tabelle – Definition auf Feldebene (Einzelfeld) oder Tabellenebene (mehrere Felder) – Kann nicht mehr gelöscht werden – CREATE TABLE EMPLOYEES ( EMPLOYEE_ID NUMBER (6) NOT NULL, FIRST_NAME VARCHAR 2 (20), . . DEPARTMENT_ID NUMBER (4), CONSTRAINT EMP_ID_PK PRIMARY KEY ( EMPLOYEE_ID ) ) ; Dipl. -Ing. Walter Sabin -- 2006 109

CONSTRAINTS Fs. l FOREIGN KEYS – Erlaubt Nullwerte – Der referenzierte Key muß der Primary Key oder ein Unique Key in der referenzierten Tabelle sein – Definition auf Feldebene (Einzelfeld) oder Tabellenebene (mehrere Felder) – Die Datentypen der Eltern und Kind Tabelle sollten übereinstimmen – ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES HR. DEPARTMENTS (DEPARTMENT_ID) ON DELETE SET NULL ; – [ON DELETE {CASCADE | SET NULL} Dipl. -Ing. Walter Sabin -- 2006 110

CONSTRAINTS Fs. l Constraints können enabled und disabled werden – ALTER TABLE EMPLOYEES DISABLE CONSTRAINT EMP_DEPT_FK; – ALTER TABLE departments DIABLE PRIMARY KEY CASCADE; – DEFERRABLE – prüfbar mit Transaktionsende l bei ADD CONSTRAINT (nicht ALTER TABLE) – INITIALLY {DEFERRED | IMMEDIATE} l auch mit ALTER TABLE möglich Dipl. -Ing. Walter Sabin -- 2006 111

Andere Datenbankobjekte l SEQUENCES – CREATE SEQUENCE emp_seq START WITH 1000 INCREMENT BY 10 – DROP SEQUENCE emp_seq – SELECT empseq. nextval FROM emp_seq – Änderung erfolgt außerhalb von Transaktionen Dipl. -Ing. Walter Sabin -- 2006 112

SYNONYME l CREATE [PUBLIC] SYNONYM synonym_name FOR object – CREATE PUBLIC SYNONYM employees FOR hr. employees; l CREATE TABLE my_emp AS SELECT * FROM EMPLOYEES; ALTER TABLE my_emp add home_phone VARCHAR 2(10); CREATE SYNONYM employees FOR my_emp; Dipl. -Ing. Walter Sabin -- 2006 113

INDEXE l Lesen von Datensätzen – mittels ROWID – mittels "full table scan" l B_Tree Index oder Bitmap Index – Beide bilden Feldwerte auf ROWIDs ab l l l Indexe können SELECT DELETE und UPDATE Befehle beschleunigen Ein Index kann verwendet werden, falls das "führende Subset" des INDEX in der SELECT oder WHERE Klause vorkommt. Fallweise kann auch ausschließlich der Index ohne die Tabelle verwendet werden Dipl. -Ing. Walter Sabin -- 2006 114

INDEXE Fs. l l SELECT count(*) from employees where last_name='Taylor'; Performance für DML Befehle kann verschlechtert werden – Index muß zusätzlich zur Tabelle geändert werden l B-Tree Indexe – gebräuchlichster Index – gut für Felder mit "hoher Kardinalität" (Felder mit vielen unterschiedlichen Werten z. B. : last_name) Dipl. -Ing. Walter Sabin -- 2006 115

INDEXE Fs. l B-Tree Fs. – meist verwendet wenn weniger als 10% der Datensätze gesucht werden. – Besteht aus "Zweigen" und "Blättern" l l l Zweige enthalten den KEY und die Adresse des Indexblockes auf der nächsten Ebene Blätter enthalten den Key und die ROWID des Datensatzes Blätter sind zusätzlich doppelt verlinkt – Warum? – Verwendung bei führenden Feldern Dipl. -Ing. Walter Sabin -- 2006 116

INDEXE Fs. l BITMAP Index – hauptsächlich bei Datawarehouses verwendet – gut bei geringer und mittlerer Kardinalität – Jeder Schlüsselwert hat eine BITMAP mit einem Bit für jeden Datensatz – z. B. Verpackungsart (Papier, Holz, Metall und Plastik) – 4 Bitmaps – Komprimierte Speicherung – sehr effizient – Mehrere BITMAPS können mit AND bzw. OR verknüpft werden. Dipl. -Ing. Walter Sabin -- 2006 117

INDEXE Fs. B-Tree Index (eindeutig) CREATE UNIQUE INDEX emp_uk_nam ON employees (last_name, first_name, employee_id); l Index auf Funktion: CREATE INDEX upper_ix ON employees (UPPER(last_name)); l Bitmap Index CREATE BITMAP INDEX first. B ON employees (substr(last_name, 1, 1)); l Dipl. -Ing. Walter Sabin -- 2006 118

Views – Datensichten l Logische Darstellung von Daten von einer oder mehreren Tabellen – base tables Wie gespeicherte Abfrage l Abfrage im Data Dictionary gespeichert l DESCRIBE Befehl l – listet die Tabellen (View) Definition Dipl. -Ing. Walter Sabin -- 2006 119

Views – Erstellung CREATE VIEW admin_employees AS SELECT first_name || last_name NAME, email, job_id POSITION FROM employees WHERE department_id = 10; l DESCRIBE admin_employees l CREATE FORCE VIEW. . l – Erstellt auch fehlerhafte Views Dipl. -Ing. Walter Sabin -- 2006 120

Views – Erstellung mit Feldnamen l CREATE VIEW emp_hire (employee_id, employee_name, department_name, hire_date, commission_amt) AS SELECT employee_id, first_name || last_name, department_name, to_char(hire_date, 'DD-MM-YYYY'), salary*NVL(commission_pct, 0. 5) FROM employees JOIN departments USING (department_id) ORDER BY first_name || last_name; Dipl. -Ing. Walter Sabin -- 2006 121

Views – Create mit Fehlern l CREATE VIEW test_view as select c 1, c 2 from test_table ORA-00942: table or view does not exist l CREATE FORCE VIEW test_view as select c 1, c 2 from test_table l SELECT * FROM test_view; ORA-04063: view "HR. TEST_VIEW" jas errors Dipl. -Ing. Walter Sabin -- 2006 122

Views – Diverses l Read-Only Views: – WITH READ ONLY l l Constraints können definiert werden nur "deklarativ" – DISABLE NOVALIDATE Ändern von Views – CREATE OR REPLACE – ALTER VIEW ALTER VEIW test_view COMPILE nach Änderungen der Basistabelle l DROP VIEW Dipl. -Ing. Walter Sabin -- 2006 123

Views – Verwendung Darstellung eines "Subsets" der Daten l Darstellung eines "Supersets" der Daten l Verdecken von komplexen JOINS l Verwendung von sprechenden Feldnamen l Erreichung eines höheren Unabhängigkeitsgrades l Dipl. -Ing. Walter Sabin -- 2006 124

Views – Datenänderung l DML verwendbar mit Einschränkungen – – – l l kein DISTINCT kein GROUP BY kein ROWNUM keine SET Operatoren (UNION. . . ) kein Subquery in "SELECT" Klausel Erzwingt nicht die "WHERE" Bedingung Joinviews – nur 1 Tabelle veränderbar – nur die "key-preserved" Tabelle ist änderbar Dipl. -Ing. Walter Sabin -- 2006 125