Einf_SQL_02b

Einführung in SQL
Universeller Zugriff auf Daten in
Datenbanksystemen
unterschiedlicher Systemhersteller!
1
SQL – Überblick


Nichtprozedurale Sprache zum Zugriff auf Daten.
Herstellerspezifische prozedurale Erweiterungen
– Oracle -> PL/SQL


Deklarative Beschreibung der gewünschten Daten
Genormt aber doch nicht einheitlich
– z.B.: DB-Verwaltung bei Oracle in SQL integriert

Themen:
–
–
–
–
Geschichte
SQL - Befehlskategorien
Elementare Datentypen und Operatoren
Einfache Abfragen
Dipl.-Ing. Walter Sabin -- 2006
2
Geschichte

1974 – SEQUEL (Chamberlin, Boyce)
– Structured English Query Language
– basierend auf SQUARE, einer mathematischen
Formelsprache

Erster Prototyp: System R – IBM / San Jose
~1975 (Basis für DB2 und SQL/DS)
– Wassergekühlter Computer

Erstes Produkt: Oracle – 1979 Version 2!!
– PDP 11, Assembler
– basiert auf veröffentlichten "System R"
Spezifikationen

Derzeit gültiger ANSI Standard – SQL 2008
Dipl.-Ing. Walter Sabin -- 2006
3
Relationale Datenbank (1/3)

Definition:
– Eine relationale Datenbank ist eine Datenbank, die aus
Sicht des Benutzers aus Tabellen und nur aus Tabellen
besteht.
– Relation – mathematischer Ausdruck für Tabelle



Atomare Elemente
Gesamter Informationsinhalt in Daten
Basisarbeit: Dr. E. F. Codd (Codd Father!!)
A Relational Model of Data for Large Shared
Data Banks" (Comm. of ACM 1970)
Dipl.-Ing. Walter Sabin -- 2006
4
Relationale Datenbank (2/3)

Beispiel:
–
–
–
–
Lieferanten - Produkte – Versand
2 Entities mit Relation
Keine "Links" oder "Pointer"
zumindest nicht erkennbar für den Benutzer
Dipl.-Ing. Walter Sabin -- 2006
5
Relationale Datenbank (3/3)
Lieferanten:
LNR
LNAME
S1
S2
S3
S4
S5
Markovitsch
Adam
Schwarz
Pichler
Huber
Versand
STATUS ORT
20
10
30
20
30
Wien
Graz
Graz
Wien
Linz
Produkte
PNR
PNAME
FARBE
P1
P2
P3
P4
P5
P6
Scheibe
Blozen
Schraube
Schraube
Haken
Stift
messing
silber
kupfer
messing
silber
messing
Dipl.-Ing. Walter Sabin -- 2006
GEWICHT
LNRPNR Menge
S1 P1
300
S1 P2
200
S1 P3
300
S1 P4
200
S1 P5
100
S1 P6
100
S2 P1
300
S2 P2
400
S3 P2
200
S4 P2
200
S4 P4
300
S4 P5
400
12
17
17
14
12
19
6
SQL Befehlskategorien - Oracle

DML – Data Manipulation Language
– z.B.: SELECT, INSERT, UPDATE, DELETE

DDL – Data Definition Language
– z.B.: (CREATE, ALTER, DROP, RENAME)
TABLE
Transaktionssteuerung (COMMIT etc.)
 System- und Session – Steuerung

– z.B.: ALTER SYSTEM
Dipl.-Ing. Walter Sabin -- 2006
7
Elementare Datentypen (1/3)

Zeichenketten
– char(<size>)
z.B.: char(10)
 feste Größe, rechts mit Leerzeichen aufgefüllt
 maximal 2000 Zeichen

– varchar2(<size>)
z.B.: varchar2(500)
 variable Länge, maximal 4000 Zeichen

Dipl.-Ing. Walter Sabin -- 2006
8
Elementare Datentypen (2/3)

Zahlen
– number(<p>,<s>)
z.B.: number(6,2) - 1226.45
 p: Precission – maximal 38
 s: Scale – Nachkommastellen
 Rundung wenn Anzahl der Nachkommastellen > s
 negatives "s" möglich - rundet links vom Dezimalpunkt
z.B.: 12345.345 in number(5,-2) ergibt 12300

Dipl.-Ing. Walter Sabin -- 2006
9
Elementare Datentypen (3/3)

Datum und Zeit
– date
belegt 7 Bytes - CC YY MM DD HH MI SS
 Funktionen to_date, sysdate
 Datum ohne Uhrzeit -> Mitternacht (00:00:00)
 Standard format: DD-MON-YY

Dipl.-Ing. Walter Sabin -- 2006
10
Operatoren und Literale (1/2)

Operator: manipuliert Datenelemente und liefert
ein Ergebnis
– unäre Operatoren - <operator><operand>

z.B.: +5 oder –2
– binäre Operatoren - < operand ><operator><operand>

+-*/
– Verkettungsoperator – verbindet Zeichenketten

|| - z.B.: 'Oracle' || 'Datenbank' ergibt 'OracleDatenbank'
Dipl.-Ing. Walter Sabin -- 2006
11
Operatoren und Literale (2/2)

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.3E-4
Dipl.-Ing. Walter Sabin -- 2006
12
Einfache Abfragen – SELECT 1/12

Allgemeiner Aufbau der Abfrage
– SELECT <attributeList>
FROM <relations>
WHERE <condition>
ORDER BY <orderClause>
Liefert als Ergebnis eine Menge von
Datensätzen (Dataset oder Resultset)
 Beispieldatenbank - Anhang

Dipl.-Ing. Walter Sabin -- 2006
13
Einfache Abfragen – SELECT 2/12

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

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


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


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
Einfache Abfragen – SELECT 6/12


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


Ergebniszeilen einschränken
SQL> SELECT first_name || ' ' || last_name "Name",
department_id
FROM employees WHERE department_id=90;
Name

DEPARTMENT_ID
Steven King
90
Neena Kochhar
90
Lex De Haan
90
Vergleichsoperatoren
 = <> (!=, ^=) <, <=, >, >=
 Logische Operatoren: NOT, AND, OR
Dipl.-Ing. Walter Sabin -- 2006
19
Einfache Abfragen – SELECT 8/12

Sonstige Operatoren
– IN, NOT IN, BETWEEN, EXISTS
 SELECT * FROM employees
WHERE department_id in (10, 20, 90);
 SELECT * FROM employees
WHERE salary BETWEEN 5000 and 6000;
 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

Null Werte
– IS NULL, IS NOT NULL
 SELECT last_name FROM employees
WHERE department_id IS NULL;

LIKE – Pattern-Matching
– _, %, Escape clause
_ matched ein einzelnes Zeichen
% matched beliebig viele Zeichen
Escape Claus erlaubt die Verwendung obiger Zeichen

SELECT * FROM employees
WHERE job_id LIKE 'AC\_%' ESCAPE '\'
and salary BETWEEN 5000 and 6000;
Dipl.-Ing. Walter Sabin -- 2006
21
Einfache Abfragen – SELECT 10/12


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


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
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

Expressions verwenden z.B.: in SELECT Klausel
– SELECT ((2*4)/(3+1))*10 FROM dual;

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.
 Elementare Datentypen und Operatoren.
 Einfache Abfragebefehle mit

– 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
 B – 34567.23
 C – 34567.22
 D – 3456.22

Dipl.-Ing. Walter Sabin -- 2006
27
Welche Standardanzeigelänge hat ein
Datumsfeld?
A–8
 B – 19
C–9
D–6

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
 B – ename
 C – emp
 D – ||

Dipl.-Ing. Walter Sabin -- 2006
29
Welche Klause in einer Abfrage
schränkt die Anzahl der Datensätze
ein?
A – ORDER BY
 B – SELECT
 C – FROM
 D – WHERE

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
 B – BETWEEN .. AND ..
 C – LIKE
 D – <=

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
 B – 123456.78
 C – Fehler
 D – 123456

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
 B – 2,1,3
 C – 2,3,1
 D – Die Reihenfolge ist gleichgültig

Dipl.-Ing. Walter Sabin -- 2006
33
FUNKTIONEN

„Single Row“ - Funktionen
–
–
–
–
–
–

Grundlagen
Zeichen Funktionen
Numerische Funktionen
Datums – Funktionen
Konvertierungsfunktionen
Sonstige Funktionen
„Group“ – Funktionen
– Grundlagen
Dipl.-Ing. Walter Sabin -- 2006
34
FUNKTIONEN Single Row 1/ 16
Verschiedene Datentype als Argumente
 Auch in PL/SQL verwendbar
 Verwendbar unter anderem in SELECT,
WHERE und ORDER BY Klauseln
 Z.B.:

– 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

NULL Werte Funktionen
– NVL(Ausdruck1, Ausdruck2)
SELECT last_name, salary
salary*commission_pct bonus,
(salary+salary*NVL(commission_pct,0)) Gehalt
FROM employees,
– NVL2(Ausdruck1, Ausdruck2, Ausdruck3)
NVL2(commission_pct,
salary+salary* commission_pct,
salary)
Dipl.-Ing. Walter Sabin -- 2006
36
FUNKTIONEN Single Row 3/16

Zeichenfunktionen
– ASCII(c1)
liefert den Ascii Wert des ersten Zeichens in c1
 SELECT ASCII('A') GR_A, ASCII('z') KL_Z FROM dual;
GR_A KL_Z
65
122

– CHR(i)

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

Zeichenfunktionen (Fs)
– INITCAP(c1)
liefert den ersten Buchstaben jedes Wortes in c1
als Großbuchstaben und alle anderen als
Kleinbuchstaben
 SELECT INITCAP('die drei lauser') FROM dual;
=> Die Drei Lauser

– LENGTH(c)
Liefert Länge einer Zeichenkette in Zeichen
 SELECT LENGTH('Die Spengergasse') FROM
DUAL;
=> 16

Dipl.-Ing. Walter Sabin -- 2006
38
FUNKTIONEN Single Row 5/16

Zeichenfunktionen (Fs)
– INSTR(c1, c2[,i [,j]])


Liefert die Zeichenposition für das j-te Vorkommen von c2 in c1,
beginnend mit Position i.
Negatives i bedeutet Suche von rechts (sonst von links)
SELECT INSTR('Mississippi','i',-2,3) FROM dual;
=> 2
– SUBSTR(c1, i[,j])


liefert einen Teilstring aus c1 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

Zeichenfunktionen (Fs)
– LOWER(c1)

Kovertiert alle Zeichen auf Kleinbuchstaben
– UPPER(c1)

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

Zeichenfunktionen (Fs)
– LPAD(c1,i[,c2])


Erweitert den String c1 auf i Zeichen. Verwendet c2 um den
freien Raum links aufzufüllen
SELECT LPAD(job_id,10,'.') FROM employees
=> ...AD_PRES usw.
– RPAD(c1,i[,c2])


Erweitert den String c1 auf i Zeichen. Verwendet c2 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

Zeichenfunktionen (Fs)
– LTRIM(c1, c2)
Diese Funktion liefert c1 ohne die führenden
Zeichen aus c2. Default: ' '
 SELECT LTRIM('Mississippi', 'Mis') FROM dual;
=>ppi

– RTRIM(c1,c2)

Diese Funktion liefert c1 ohne die nachlaufenden
Zeichen aus c2. Default: ' '
Dipl.-Ing. Walter Sabin -- 2006
42
FUNKTIONEN Single Row 9/ 16

Zeichenfunktionen (Fs)
– REPLACE(c1,c2[c3])
Liefert c1 wobei alle Strings c2 durch c3 ersezt werden
 SELECT REPLACE('uptown','up','down') FROM dual;
=> downtown

– SOUNDEX(c1)
Liefert die phonetische Darstellung von C1
 SELECT SOUNDEX('Dawes' from dual);
=> D200

Dipl.-Ing. Walter Sabin -- 2006
43
FUNKTIONEN Single Row 10/ 16

Zeichenfunktionen (Fs)
– TRANSLATE(c1,c2,c3)

Liefert c1 wobei alle in c1 vorkommenden Zeichen aus c2
durch die der Position in c2 entsprechenden Zeichen aus c3
ersetzt werden.

SELECT TRANSLATE('Mississippi','Mis','mIS') FROM
dual;
=> mISSISSIppI
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

Numerische Funktionen
– ABS(n) – Absolutbetrag von n
– COS, SIN, TAN, ATAN, ASIN, ACOS COSH, SINH

Winkelfunktionen
– CEIL(n) – Kleinster ganzzahliger Wert größer oder gleich n

SELECT CEIL(9.8), CEIL(-32.85) from dual;
=> 10 -32
– FLOOR(n) Größter ganzzahliger Wert kleiner oder gleich n

SELECT FLOOR(9.8), FLOOR(-32.85) from dual;
=> 9 -33
Dipl.-Ing. Walter Sabin -- 2006
45
FUNKTIONEN Single Row 12/ 16

Numerische Funktionen (Fs)
– LN(n) – Natürlicher Logarithmus von n

SELECT LN(2.7) FROM dual;
=> 0,993251773010283
– LOG(n1,n2)


Liefert den Logarithmus von n1 zur Basis n2
SELECT LOG(27,3) FROM dual;
=> 0,333333333333333
– MOD(n1, n2)


Liefert n1 modulo n2
SELECT MOD(14,5) FROM dual;
=> 4
Dipl.-Ing. Walter Sabin -- 2006
46
FUNKTIONEN Single Row 13/ 16

Numerische Funktionen (Fs)
–
–
–
–
–
POWER(n1,n2) – Liefert n1 hoch n2
SQRT(n) – Quadratwurzel aus n
ROUND(n1, n2) – Liefert n1 gerundet auf n2 Stellen
SIGN(n) – liefert 1 falls n pos.-1 falls negativ, 0 wenn 0
TRUNC(n1, n2) – Liefert n1 auf n2 Stellen
abgeschnitten
Dipl.-Ing. Walter Sabin -- 2006
47
FUNKTIONEN Single Row 14/ 16

Datumsfunktionen
– ADD_MONTHS(d,i)

Addiert i Monate zu Datum d
– MONTHS_BETWEEN(d1, d2)

Liefert die Anzahl der Monate zwischen d1 und d2
– SELECT MONTHS_BETWEEN('19.12.2002','19.03.2002')
test from dual;
=> 9
– LAST_DAY(d)

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

Datumsfunktionen (Fs)
– EXTRACT(c FROM d)
Liefert die durch c angegebene Komponente von d.
c: YEAR, MONTH, DAY,HOUR, MIN, SECOND

SELECT EXTRACT(MONTH FROM SYSDATE)
FROM dual;
=> 1
– TRUNC(d[,fmt])
Liefert ein Datum abgeschnitten je nach fmt.

SELECT TRUNC(last_analysed,'HH') FROM user_tables
WHERE table_name='TEST_CASE';
=> 10-Jan-2003 11:00:00
Dipl.-Ing. Walter Sabin -- 2006
49
FUNKTIONEN Single Row 16/16

Conversionsfunktionen
– TO_CHAR(x[,fmt]) – x:Datum oder Zahl, fmt: Formatcode

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

Sonstige Funktionen
– DECODE(x,m1,r1,m2,r2,....,d)

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"

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)

Elementare Funktionen
– COUNT {* | [DISTINCT | ALL] x }

Anzahl
– SUM {[DISTINCT | ALL] x }

Summe
– MAX {[DISTINCT | ALL] x }

Maximalwert
– MIN {[DISTINCT | ALL] x }

Minimal
– AVG {[DISTINCT | ALL] x }

Durchschnitt
Dipl.-Ing. Walter Sabin -- 2006
52
FUNKTIONEN "Group" (Fs)


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)

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;

Ergebnis
avg
sum_all
avg_all
avg_dist
sum_dist
5760
5760
Dipl.-Ing. Walter Sabin -- 2006
6000
cnt
5
cnt_dist
4
54
FUNKTIONEN "Group" (Fs)

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
10
20
30
40
50
60
MA_ANZAHL DEPARTMENT_ID
1
2
6
1
45
5
Dipl.-Ing. Walter Sabin -- 2006
70
80
90
100
110
MA_ANZAHL
1
34
3
6
2
1
55
FUNKTIONEN "Group" (Fs)
– Klausel: HAVING
Einschränken der zurückgelieferten Gruppen
 Keine Gruppenfunktionen in der where Klausel
 SELECT department_id DID, sum(salary)
SUMSAL, count(*) ANZ FROM hr.employees
GROUP BY department_id HAVING count(*)
> 10;
 Ergebnis:
DID
SUMSAL
ANZ
50
156.400
45
80
304.500
34

Dipl.-Ing. Walter Sabin -- 2006
56
Joins und Subqueries






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 9i verwendet werden (ISO / ANSI
SQL1999)
ohne diese Klauseln -> kartesisches Produkt
Dipl.-Ing. Walter Sabin -- 2006
57
Einfache JOINs
2 Tabellen und "=" Operator
 SELECT locations.location_id, city,
department_name
FROM locations, departments
WHERE
locations.location_id = departments.location_id

Dipl.-Ing. Walter Sabin -- 2006
58
Tabellen Aliase

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';

mit Schemaname
– SELECT locations.location_id, hr.locations.city,
department_name
FROM hr.locations, hr.departments
WHERE location.location_id =
departments.location_id;
Dipl.-Ing. Walter Sabin -- 2006
59
JOINS – ANSI Syntax

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.

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.

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
 möglichst vermeiden – Performance!
 zumindest immer n-1 JOIN Bedingungen
(n .. Anzahl der Tabellen in der FROM
Klausel)
 SELECT region_name, country_name
FROM regions, countries
where countries.country_ld LIKE 'I%';

Dipl.-Ing. Walter Sabin -- 2006
63
OUTER JOINS
Liefert alle Werte einer Tabelle auch bei
fehlenden Entsprechungen in der zweiten
Tabelle
 2 Schreibweisen:

– Traditionell: (+) zu Feld in der where Klause
– ANSI: LEFT JOIN, RIGHT JOIN, FULL JOIN

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.



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.

Full OUTER JOIN
– neu in Oracle 9i
– 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

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;
 ANSI Syntax ???

Dipl.-Ing. Walter Sabin -- 2006
67
Andere JOINS Fs

Nicht Gleichheits JOIN
– Tabelle grades:
SELECT * FROM grades;
GRADE LOW_SALARY HIGH_SALARY
P5
0
3000
P4
3001
5000
P3
5001
7000
P2
7001
9000
P1
10001
Dipl.-Ing. Walter Sabin -- 2006
68
Andere JOINS Fs

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
P1
P5
P4
P1
69
Andere JOINS Fs.

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.

SELECT last_name, hire_date FROM employees
WHERE department_id = 90
UNION
SELECT last_name, hire_date FROM employees
WHERE last_name 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
 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

Dipl.-Ing. Walter Sabin -- 2006
72
Single Row Subqueries
Ein Query im Query
 Liefert nur einen Datensatz als Ergebnis

– 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
 Sicherer wenn die Ergebnismenge nicht
sicher ist
 SELECT last_name, first_name
FROM employees
where department_id in (SELECT
department_id FROM employees
WHERE first_name = 'John');

Dipl.-Ing. Walter Sabin -- 2006
74
Multiple Row Subqueries

Verwendung eines Subqueries an Stelle einer
Tabelle
– Attributnamen bilden die neuen Spalten der Tabelle

Beispiel: Welcher Mitarbeiter (Familienname
und Gehalt) hat das 5. höchste Gehalt
select last_name, salary
from (select rownum Reihe, last_name, salary
from (select last_name, salary from
employees order by salary desc)
)
where Reihe = 5
Dipl.-Ing. Walter Sabin -- 2006
75
Korrelierte Subqueries
Subquery bezieht sich auf Felder des
Mutterqueries
 Subquery wird für jeden zurückgelieferten
Datensatz des Mutterqueries ausgeführt
 SELECT department_id, last_name, salary
FROM employees e1
WHERE salary = (select max(salary)
FROM employees e2
WHERE e1.department_id =
e2.department_id)
ORDER by 1,2,3;

Dipl.-Ing. Walter Sabin -- 2006
76
Skalare Subqueries




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
77
Skalare Subqueries Fs.

In einer SELECT Klausel
– SELECT last_name, department_id,
(select MAX(salary) FROM employees sq
WHERE sq.department_id = e.department_id) HiSal
FROM employees e
WHERE last_name LIKE 'R%';
Dipl.-Ing. Walter Sabin -- 2006
78
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
79
DML
Data Manipulation Language
 Ändern von Daten in Tabellen
 Koordinieren von mehrfachen
Veränderungen
 Verwendung von Locks
 Steuerung der Änderungen

Dipl.-Ing. Walter Sabin -- 2006
80
DML – Modifying Data
INSERT – fügt Datensätze in Tabellen ein
 UPDATE – Ändert Feldwerte in Tabellen
 MERGE – Fügt ein oder ändert
 DELETE - Löscht Datensätze
 SELECT FOR UPDATE – Verhindert
gleichzeitiges Verändern durch andere
Transaktionen
 LOCK TABLE – Schützt vor gleichzeitiger
Veränderung

Dipl.-Ing. Walter Sabin -- 2006
81
DML - INSERT
Fügt Datensätze in ein oder mehrere
Tabellen ein
 Werte können eingegeben werden
 Werte können von Subqueries ermittelt
werden

Dipl.-Ing. Walter Sabin -- 2006
82
DML – Single Table INSERT
INSERT INTO departments
(department_id, department_name,
manager_id, location_id)
VALUES
(280, 'Cash Management', 108,1700)
 NULL Werte werden in fehlende Spalten
eingefügt

Dipl.-Ing. Walter Sabin -- 2006
83
DML – Single Table INSERT Fs.



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
84
DML – Single Table INSERT Fs.
Verwendung von Subselects
 INSERT INTO job_history (employee_id,
start_date, job_id)
SELECT employee_id,
to_date('01.01.1900','DD.MM.YYYY'),
job_id from employees;

Dipl.-Ing. Walter Sabin -- 2006
85
DML – Multiple Table INSERT
Neu in 9i
 INSERT ALL WHEN Bedingung
THEN Into Klausel ELSE Into Klausel
(SELECT ......) ;
 Verkaufsdaten – Tabelle vkdat:
anr .. Artikelnummer, agr .. Artikelgruppe
vdat .. Verkaufsdatum, knr .. Kundennr.
sq .. Verkaufsmenge, sp .. Verkaufspreis
agr: B .. Bücher, V .. Video oder P .. Papier

Dipl.-Ing. Walter Sabin -- 2006
86
DML – Multiple Table INSERT Fs.
Tabellen: vkbuch, vkvideo, vkpapier
 Allg. Felder anr, vdat, knr, ums
 Spezielle Felder
vkbuch: isbn,
vkvideo: ej -- Erscheinungsjahr,
vkpapier: atyp -- Artikeltyp

Dipl.-Ing. Walter Sabin -- 2006
87
DML – Multiple Table INSERT Fs.
INSERT ALL
 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, anr,vdat, knr, sp*sq u FROM
vkdat

Dipl.-Ing. Walter Sabin -- 2006
88
DML - UPDATE
Modifiziert vorhandene Datensätze in
einer Tabelle
 UPDATE tabellenname
set (Felder, ...) = (subquery)
WHERE Bedingung
 UPDATE tabellenname
set Feld1 = Wert1, Feld2 = Wert2 ...
WHERE Bedingung

Dipl.-Ing. Walter Sabin -- 2006
89
DML - UPDATE Fs.
UPDATE employees
SET commission_pct = 0.01
WHERE commission_pct is NULL;
 UPDATE employees
SET salary = salary*0.15,
commission_pct = 0.2
WHERE department_id = 60;

Dipl.-Ing. Walter Sabin -- 2006
90
DML - UPDATE Fs.

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 j2
where j2.end_date is not null and
j.employee_id = j2.employee_id);
Dipl.-Ing. Walter Sabin -- 2006
91
DML - UPDATE Fs.

UPDATE job_history j1
SET start_date =
(SELECT end_date + 1 FROM job_history j2
WHERE j2.end_date is not NULL and
j2.employee_id = j1.employee_id and
j2.end_date = (select max(j3.end_date)
FROM job_history j3
WHERE j3.end_date is not NULL and
j3.employee_id = j2.employee_id))
WHERE j1.end_date is NULL and exists
(SELECT * from job_history j4 where
j4.employee_id=j1.employee_id and j4.end_date is not
NULL)
Dipl.-Ing. Walter Sabin -- 2006
92
DML - MERGE


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
93
DML - MERGE Fs.


select * from np;
P_ID L_P M_P
2986 135 111
3163 40
32
3164 40
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
94
DML - MERGE Fs.
Ergebnis:
 select * from
P_ID C_ID
2986 33 135
3163 33 40
3165 33 40
3164 33 40

Dipl.-Ing. Walter Sabin -- 2006
pi;
L_P M_P
111
32
34
35
95
DML - DELETE - TRUNCATE
DELETE FROM table WHERE bedingung
 DELETE FROM employees WHERE
salary > 15000;
 Löschen der gesamten Tabelle ohne
Rollback
 TRUNCATE table [DROP | REUSE]
STORAGE
 schnell, benötigt wenig Resourcen

Dipl.-Ing. Walter Sabin -- 2006
96
DML - LOCKING




SELECT .... FOR UPDATE
SELECT * from employees WHERE
NVL(commission_pct,0) > 0 FOR UPDATE;
Sperren gesamter Tabelle
LOCK table IN lockmode
lockmode:
EXCLUSIVE MODE ...
queries erlaubt, alles andere nicht
SHARE MODE ... verhindert Updates
ROW SHARE MODE
.. verhindert exclusiven LOCK
Dipl.-Ing. Walter Sabin -- 2006
97
DML - Transaktionskontrolle

COMMIT
– Beendet Transaktion
– Änderungen werden permanent
– Änderungen werden sichtbar

ROLLBACK
– Alle Änderungen (außer DDL) werden zurückgesetzt


SAVEPOINT Name Setzt eine benannte Marke
ROLLBACK TO SAVEPOINT
– Setzt bis zur benannten Marke wieder zurück
Dipl.-Ing. Walter Sabin -- 2006
98
TABELLEN - Datentypen

Zeichen:
CHAR(2000 Zeichen), VARCHAR2(4000 Zeichen),
CLOB(4GB), LONG(2GB - alt)

Numerische Datentypen:
NUMBER[ (<precision> [,<scale>])]
kann für alle numerischen Daten verwendet werden

DATUM
DATE – Datum und Zeit (bis Sekunden)
TIMESTAMP – Datum und Zeit
(bis 9 stellige Sekundenbruchteile)
Dipl.-Ing. Walter Sabin -- 2006
99
TABELLEN - Datentypen Fs.
Binäre Datentypen
RAW (2000 Byte), BLOB (bis 4GB),
BFILE(external FILE bis 4GB)
 Sonstige Datentypen
ROWID – Pseudospalte bei jeder Tabelle
(physische Datensatzadresse – schnellster
Zugriffsweg, kann sich bei update ändern)

Dipl.-Ing. Walter Sabin -- 2006
100
TABELLEN - Erstellen
CREATE TABLE
 CREATE TABLE produkte
( produkt_id
NUMBER (4),
produkt_name VARCHAR2(50),
lager_menge NUMBER(15),
preis
NUMBER(15,2)
);

Dipl.-Ing. Walter Sabin -- 2006
101
TABELLEN - Erstellen Fs.

Zusatzinformation beim Erstellen von
Tabellen:
–
–
–
–
–
–
Standardwerte
"Constraints"
Tabellenart
Speicherangaben
"Tablespace"
"Partitionierungs" – Information
Dipl.-Ing. Walter Sabin -- 2006
102
TABELLEN - Erstellen Fs.
Namen von Tabellen und Feldern:
maximal 30 Zeichen lang
 Alphanumerische Zeichen + "_, $, #"
erlaubt (beginnend mit Aplhabetischem
Zeichen
 Caseinsensitiv wenn nicht in ""
eingeschlossen

Dipl.-Ing. Walter Sabin -- 2006
103
TABELLEN - Erstellen Fs.
CREATE TABLE auftraege (
auftrags_nummer NUMBER(8),
status
VARCHAR2(10)
DEFAULT 'PENDING');
 INSERT INTO auftraege (auftrags_nummer)
VALUES (4004);
 SELECT * FROM AUFTRAEGE;
AUFTRAGS_NUMMER
STATUS
4004
PENDING

Dipl.-Ing. Walter Sabin -- 2006
104
TABELLEN - Erstellen Fs.

NULL Werte unterdrücken - Constraints

CREATE TABLE JOB_HISTORY (
EMPLOYEE_ID
NUMBER (6)
CONSTRAINT JHIST_EMPLOYEE_NN
NOT NULL,
START_DATE
DATE
CONSTRAINT JHIST_START_DATE_NN
NOT
NULL,
END_DATE
DATE,
JOB_ID
VARCHAR2 (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
105
TABELLEN - Erstellen Fs.


Erstellen einer Tabelle aus einer anderen
Tabelle
CREATE TABLE emp2 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
106
TABELLEN - Verändern






Felder hinzufügen
– ALTER TABLE employees ADD city VARCHAR2(30);
Felddatentypen verändern
– ALTER TABLE departments MODIFY department_id
NUMBER(12);
Felder löschen
– ALTER TABLE employees DROP COLUMN city;
TABELLEN löschen
– DROP TABLE emp2;
TABELLEN umbenennen
– RENAME emp2 TO employees_save
DESCRIBE Tabellenname
– listet alle Felder einer Tabelle
Dipl.-Ing. Walter Sabin -- 2006
107
CONSTRAINTS

NOT NULL
– CREATE TABLE orders (
order_num Number (4)
CONSTRAINT nn_order NOT NULL,
order_date DATE NOT NULL,
product_id NUMBER(14));

Check Constraints
– CREATE TABLE bonus (
emp_id VARCHAR2 (40) NOT NULL,
salary NUMBER (13,2),
bonus NUMBER(13,2),
CONSTRAINT ck_bonus CHECK (bonus
> 0))
Dipl.-Ing. Walter Sabin -- 2006
108
CONSTRAINTS Fs.

UNIQUE Constraints
– Definition auf Feldebene (Einzelfeld) oder
Tabellenebene (mehrere Felder)
– ALTER TABLE employees ADD
ssn VARCHAR2(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
109
CONSTRAINTS Fs.

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
VARCHAR2 (20),
....
DEPARTMENT_ID NUMBER (4),
CONSTRAINT EMP_EMP_ID_PK
PRIMARY KEY ( EMPLOYEE_ID ) ) ;
Dipl.-Ing. Walter Sabin -- 2006
110
CONSTRAINTS Fs.

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
111
CONSTRAINTS Fs.

Constraints können enabled und disabled
werden
– ALTER TABLE EMPLOYEES DISABLE
CONSTRAINT EMP_DEPT_FK;
– ALTER TABLE departments DISABLE PRIMARY
KEY CASCADE;
– DEFERRABLE – prüfbar mit Transaktionsende

bei ADD CONSTRAINT (nicht ALTER TABLE)
– INITIALLY {DEFERRED | IMMEDIATE}

auch mit ALTER TABLE möglich
Dipl.-Ing. Walter Sabin -- 2006
112
Andere Datenbankobjekte

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
113
SYNONYME

CREATE [PUBLIC] SYNONYM
synonym_name FOR object
– CREATE PUBLIC SYNONYM employees FOR
hr.employees;

CREATE TABLE my_emp AS SELECT *
FROM EMPLOYEES;
ALTER TABLE my_emp add home_phone
VARCHAR2(10);
CREATE SYNONYM employees FOR
my_emp;
Dipl.-Ing. Walter Sabin -- 2006
114
INDEXE

Lesen von Datensätzen
– mittels ROWID
– mittels "full table scan"

B_Tree Index oder Bitmap Index
– Beide bilden Feldwerte auf ROWIDs ab



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
115
INDEXE Fs.


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

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
116
INDEXE Fs.

B-Tree Fs.
– meist verwendet wenn weniger als 10% der
Datensätze gesucht werden.
– Besteht aus "Zweigen" und "Blättern"



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
117
INDEXE Fs.

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
118
INDEXE Fs.
B-Tree Index (eindeutig)
CREATE UNIQUE INDEX emp_uk_nam
ON employees (last_name, first_name,
employee_id);
 Index auf Funktion:
CREATE INDEX upper_ix ON employees
(UPPER(last_name));
 Bitmap Index
CREATE BITMAP INDEX firstB ON
employees (substr(last_name,1,1));

Dipl.-Ing. Walter Sabin -- 2006
119
Views – Datensichten

Logische Darstellung von Daten von
einer oder mehreren Tabellen
– base tables
Wie gespeicherte Abfrage
 Abfrage im Data Dictionary gespeichert
 DESCRIBE Befehl

– listet die Tabellen (View) Definition
Dipl.-Ing. Walter Sabin -- 2006
120
Views – Erstellung
CREATE VIEW admin_employees AS
SELECT first_name || last_name
NAME,
email, job_id POSITION
FROM employees
WHERE department_id = 10;
 DESCRIBE admin_employees
 CREATE FORCE VIEW ....

– Erstellt auch fehlerhafte Views
Dipl.-Ing. Walter Sabin -- 2006
121
Views – Erstellung mit Feldnamen

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
122
Views – Create mit Fehlern

CREATE VIEW test_view as
select c1, c2 from test_table
ORA-00942: table or view does not exist

CREATE FORCE VIEW test_view as
select c1, c2 from test_table

SELECT * FROM test_view;
ORA-04063: view "HR.TEST_VIEW" jas
errors
Dipl.-Ing. Walter Sabin -- 2006
123
Views – Diverses

Read-Only Views:
– WITH READ ONLY


Constraints können definiert werden
nur "deklarativ" – DISABLE NOVALIDATE
Ändern von Views
– CREATE OR REPLACE
– ALTER VIEW
ALTER VIEW test_view COMPILE
nach Änderungen der Basistabelle

DROP VIEW
Dipl.-Ing. Walter Sabin -- 2006
124
Views – Verwendung
Darstellung eines "Subsets" der Daten
 Darstellung eines "Supersets" der
Daten
 Verdecken von komplexen JOINS
 Verwendung von sprechenden
Feldnamen
 Erreichung eines höheren
Unabhängigkeitsgrades

Dipl.-Ing. Walter Sabin -- 2006
125
Views – Datenänderung

DML verwendbar mit Einschränkungen
–
–
–
–
–


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
126