FROM employees

SQL I - Basiswissen
Christine Apfel, DESY, Juni 2011
SQL Schulung Übersicht I
 Relationale Datenbanken Einführung
 Terminologie
 Eigenschaften
 SQL-Anweisungen
 Select Klausel
 From Klausel
 Where Klausel
 Vergleichsoperatoren
 Logische Operatoren
 Single Row- Funktionen (teilweise Oracle spezifisch)
 Aggregatfunktionen
SQL Schulung Übersicht II
 Datengruppen
 GROUP BY-Klausel
 HAVING-Klausel
 Joins
 Natural Joins
 Inner Joins und Outer Joins
 Left Outer Joins
 Right Outer Joins
 Full Outer Joins
 Kartesische Produkte
 Cross Joins
SQL Schulung Übersicht III
 Subselects (Unterabfragen)



Single Row Unterabfragen
Gruppenfunktionen
Multiple Row Unterabfragen
 Mengenoperatoren




UNION / UNION ALL
INTERSECT
MINUS
Richtlinien
SQL Schulung Übersicht IV
 DDL-Anweisungen
 Datenbankobjekte
 Tabellen erstellen (CREATE TABLE-Anweisung)
 Datentypen und Constraints
 Tabellendefinition ändern (ALTER TABLE-Anweisung)
 Tabellen löschen (DROP TABLE-Anweisung)
 DML-Anweisungen
 INSERT-Anweisung
 UPDATE-Anweisung
 DELETE-Anweisung
 TRUNCATE-Anweisung
 COMMIT- u. ROLLBACK-Anweisung
SQL Schulung Übersicht V
 weitere Schemaobjekte





Views
Sequences
Pseudospalten
Indizes
Synonyme
 Data Dictionary
Einführung
SQL Schulung: Kapitel 1
Relationale Datenbanken - Konzept
 Dr. E.F. Codd entwickelte das relationale
Modell für Datenbank-Systeme 1970
 Relation ist Grundlage für das relationale
Datenbank-Management-System (RDBMS)
 Komponenten des relationalen Modells sind:



Relationen (Zusammenstellung von Objekten)
Menge von Operatoren, mit denen die
Relationen bearbeitet werden
Datenintegrität für Korrektheit und Konsistenz
Datenbanksysteme
 DBMS



organisiert intern die strukturierte Speicherung
der Daten gemäß Datenmodell
kontrolliert alle lesenden u. schreibenden
Zugriffe
stellt DB-Anfragesprache (SQL)
 Datenbank


die Daten
die Datenbeschreibung (Data Dictionary)
Relationale Datenbanken - Definition
Eine relationale Datenbank ist eine
Zusammenstellung von Relationen oder
zweidimensionalen Tabellen
Relation / Tabelle
Relationenname
Attribute / Felder / Spalten
Tabelle 1
A1
Wert 1
A2
Wert 2
Wert 2
Wert 3
Wert 3
Wert 3
Wert 4
Wert 4
…
An
Relationenschema
Tupel/
Zeilen
Entity-Relationship-Modell
 beschreibt Ausschnitt der „realen“ Welt
 Daten werden in Entitys unterteilt
 Zwischen verschiedenen Entitys gibt es
Beziehungen (Relationship)
 keine Abbildung von ausgeführten Aktivitäten
(siehe UML)
ER-Grundlagen I
 Entität (Entity)




Objekt der „realen“ Welt
Entitätstyp
Typisierung gleichartiger Entitäten
Beziehung (Relationship)
semantische Beziehung zwischen Entitäten
Beziehungstyp
Typisierung gleichartiger Beziehungen
Kardinalität
mögliche Anzahl der an einer Beziehung beteiligten
Entitäten
ER-Grundlagen II
 Attribut / identifizierende Attribute
Eigenschaft eines Entitätstyps
 identifizierende Attribute
Attribut o. Attributkombination eines
Entitättyps
 Beziehungstyp mit Attributen
(m:n-Beziehungen)
ER-Modelle
Rel. Datenbanken - Terminologie
 Zeile (Tupel; Schlüssel-Wert-Paar) ← Entität
 Spalte ← Attribut



Primärschlüssel
Fremdschlüssel
sonstige
 Feld
Relationale DBMS Eigenschaften
 Kann durch Ausführen von SQL-
Anweisungen gelesen und bearbeitet werden
 Enthält eine Zusammenstellung von Tabellen
ohne physische Zeiger
 Verwendet eine Menge von Operatoren
 Sicherheitsfunktionen (Zugriff auf Daten und
deren Benutzung)
Das DB-Mangement System
 SGA (System Global Area)
 Benutzertabellen und Data Dictionary
SQL
Structured Query Language:
 Datenmanipulationssprache (DML)
SELECT, INSERT, UPDATE, DELETE, MERGE
 Datendefinitionssprache (DDL)
CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT
 Transaktionssteuerung
COMMIT, ROLLBACK, SAVEPOINT
 Datenkontrollsprache (DCL)
GRANT, REVOKE
SELECT-Klausel
SQL Schulung: Kapitel 2
SELECT - Anweisung
 Abruf von Informationen aus der Datenbank
 Projektion
SELECT-Klausel
 Join
FROM-Klausel
 Selektion
WHERE-Klausel
SELECT-Basis-Syntax
SELECT * | {
[DISTINCT] column|expression [alias]
}*
FROM table;
SELECT Beispiele
 SELECT *
FROM departments;
DEPARTMENT_ID
---------------------10
20
30
40
50
DEPARTMENT_NAME
-----------------------------Administration
Marketing
Purchasing
Human Resources
Shipping
MANAGER_ID
---------------------200
201
114
203
121
LOCATION_ID
---------------------1700
1800
1700
2400
1500
60
IT
103
1400
SELECT Beispiele II
 SELECT department_id, location_id
FROM departments;
DEPARTMENT_ID
---------------------10
20
30
40
LOCATION_ID
---------------------1700
1800
1700
2400
SELECT Beispiele III
 SELECT department_id “Dep-id“,
location_id “Loc-id“
FROM departments;
Dep-id
---------------------10
20
30
40
Loc-id
---------------------1700
1800
1700
2400
SQL Beispiele IV (Alias)
 select d.department_id "Dep-id", d.location_id "Loc-id"
from departments d;
Dep-id
---------------------10
20
30
40
Loc-id
---------------------1700
1800
1700
2400
Hinweis: Bei Oracle kann in der „SELECT-Klausel“ auch AS verwendet
werden.
SELECT department_is AS Department, location_id AS Location
SQL-Anweisungen erstellen
 in SQL Anweisungen wird nicht zwischen
Gross- und Kleinschreibung unterschieden
 SQL-Anweisungen können aus einer oder
mehrerer Zeilen bestehen
 Schlüsselwörter dürfen nicht abgekürzt oder
auf mehrere Zeilen verteilt werden
 Einrückungen verbessern die Lesbarkeit
 ! Semikolon (;) am Ende der Anweisung !
 Wo ?
Exkurs SQL Developer
Arithmetische Ausdrücke
 Arithmetische Operatoren
+, -, *, /
 Datentypen: NUMBER u. DATE
 in jeder Klausel einer SQL-Anweisung
verwendbar, ausgenommen FROM
 Punkt-Rechnung vor Strichrechnung
 Auswertunsrichtung: von links nach rechts
bei gleicher Priorität
 Klammern für Umgehung der Prioritätsregeln
Arithmetische Operatoren Beispiele
 SELECT last_name, salary, salary + 300
FROM employees;
 SELECT last_name, salary, 12*salary+100
FROM employees;
 SELECT last_name, salary, 12*(salary+100)
FROM employees;
Nullwerte definieren
 ein Nullwert steht für einen nicht verfügbaren,
nicht zugewiesenen, unbekannten oder nicht
anwendbaren Wert.
 nicht zu verwechseln mit der Zahl Null (0)
 nicht zu verwechseln mit einem Leerzeichen
 Nullwert für alle Spalten und Datentypen
möglich
 bestimmte Constraints verbieten Nullwerte
Nullwerte Beispiel
 SELECT last_name, job_id, commission_pct
FROM employees;
LAST_NAME
------------------------OConnell
Grant
Vargas
Russell
Partners
JOB_ID
---------SH_CLERK
SH_CLERK
ST_CLERK
SA_MAN
SA_MAN
SALARY
---------------------2600
2600
2500
14000
13500
COMMISSION_PCT
----------------------
0,4
0,3
Nullwerte in arithm. Ausdrücken
 arithmetische Ausdrücke, die einen Nullwert
benutzen, liefern einen Nullwert
 SELECT last_name, 12*salary*commission_pct
FROM employees;
LAST_NAME
------------------------OConnell
Grant
Vargas
Russell
Partners
Errazuriz
JOB_ID
---------SH_CLERK
SH_CLERK
ST_CLERK
SA_MAN
SA_MAN
SA_MAN
12*SALARY*COMMISSION_PCT
------------------------
67200
48600
43200
Verkettungsoperatoren
 verknüpfen Spalten oder Zeichenfolgen mit
anderen Spalten oder Zeichenfolgen
 wird durch 2 senkrechte Striche (||)
dargestellt
 erstellt eine Ergebnisspalte
 Nullwerte werden ignoriert
z.B.: LAST_NAME || NULL ergibt LAST_NAME
Literale Zeichenfolgen
 Ein Literal ist ein Zeichen, eine Zahl oder ein
Datum in einer SELECT-Klausel
 Literale Datums- und Zeichenwerte müssen
in Hochkommata (' ') gesetzt werden
 Jede Zeichenfolge wird für jede
zurückgegebene Zeile einmal ausgegeben
 keine Spalten !!!
Verkettungsoperatoren u. Literale
Beispiel
SELECT '[' || employee_id || ']' || first_name || ' ' || last_name
"Employee"
FROM employees;
Employee
-------------------------------------------------------------[198] Donald OConnell
[199] Douglas Grant
[200] Jennifer Whalen
…
DISTINCT
 zum Ausblenden von mehrfach vorhandenen
Zeilen
 gültig für alle Spalten der Projektion
SELECT department_id
FROM employees;
vs.
SELECT DISTINCT department_id
FROM employees;
Tabellenstruktur
 Welche Spalten gibt es in der Tabelle?
 Welche Datentypen wurden verwendet?
 Welche NOT-NULL Constraints liegen vor?
 DESC(RIBE) tabellen-name
DESCRIBE Beispiel
 desc employees;
Name
-----------------------------EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
SALARY
COMMISSION_PCT
MANAGER_ID
DEPARTMENT_ID
Null
Type
-------- ----------------------------NOT NULL NUMBER(6)
VARCHAR2(20)
NOT NULL VARCHAR2(25)
NOT NULL VARCHAR2(25)
VARCHAR2(20)
NOT NULL DATE
NOT NULL VARCHAR2(10)
NUMBER(8,2)
NUMBER(2,2)
NUMBER(6)
NUMBER(4)
WHERE-Klausel
SQL-Schulung Kapitel: 3
Syntax WHERE-Klausel
SELECT * | {
[DISTINCT] column|expression [alias] } *
FROM table;
[ WHERE condition(s)];
 WHERE schränkt die Abfrage auf die Zeilen
ein, die eine Bedingung erfüllen
 condition besteht aus Spaltennamen,
Ausdrücken, Konstanten und einem
Vergleichsoperator
WHERE-Klausel Beispiel
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90;
EMPLOYEE_ID
----------------100
101
102
LAST_NAME
-----------------King
Kochhar
De Haan
JOB_ID
---------AD_PRES
AD_VP
AD_VP
DEPARTMENT_ID
-------------90
90
90
Zeichenfolgen und Datumsangaben
 Zeichenfolgen und Datumswerte werden in Hochkommata
gesetzt
 Bei Zeichenwerten wird die Gross/Kleinschreibung beachtet
 Datumswerte sind formatabhängig
Defaultformat: DD-MON-YY
Beispiel:
SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = 'Whalen';
!= last_name = 'WHALEN'
SELECT last_name, job_id, department_id
FROM employees
WHERE hire_date = '17-SEP-87';
Vergleichsoperatoren
Operator
Bedeutung
=
Gleich
>
Grösser als
>=
Grösser/gleich
<
Kleiner als
<=
Kleiner/gleich
<>
Ungleich
BETWEEN…AND…
IN(set)
LIKE
IS NULL bzw. IS NOT NULL
Zwischen zwei Werten (Werte eingeschlossen)
Entspricht einem Wert aus einer Werteliste
Entspricht einem Zeichenmuster
Ist ein Nullwert bzw. ist kein Nullwert
Syntax Vergleichsoperatoren
… WHERE expr operator value
Beispiele:
… WHERE salary >= 6000
… WHERE last_name LIKE 'Smi%'
… WHERE salary BETWEEN 2000 AND 4000
… WHERE last_name BETWEEN 'King' AND 'Smith'
… WHERE department_id IN (30,50)
… WHERE last_name IN ('Hartstein', 'Vargas')
… WHERE commission_pct IS NULL
Vergleichsoperator LIKE
 Symbole _ und %
 beliebig kombinierbar
 ESCAPE-Identifier, um nach Symbolen zu
suchen
Beispiel:
... WHERE last_name LIKE '_o%'
... WHERE job_id LIKE '%SA\_%' ESCAPE '\'
Logische Operatoren
Operator
AND
OR
NOT
Bedeutung
Gibt TRUE zurück, wenn jede der durch
AND kombinierten Bedingungen wahr ist
Gibt TRUE zurück, wenn eine der durch OR
kombinierten Bedingungen wahr ist
Gibt TRUE zurück, wenn die nachfolgende
Bedingung falsch ist
AND-Operator
 AND fordert, dass beiden Komponenten der
Bedingung wahr sind
 AND-Wahrheitstafel
AND
TRUE
FALSE
NULL
TRUE
TRUE
FALSE
NULL
FALSE
FALSE
FALSE
FALSE
NULL
NULL
FALSE
NULL
OR-Operator
 OR fordert, dass eine Komponente der Bedingung
wahr ist
 OR-Wahrheitstafel
OR
TRUE
FALSE
NULL
TRUE
TRUE
TRUE
TRUE
FALSE
TRUE
FALSE
NULL
NULL
TRUE
NULL
NULL
NOT-Operator
 NOT negiert das Ereignis
 kann mit IN, BETWEEN, LIKE etc. kombiniert werden
 NOT-Wahrheitstafel
NOT
TRUE
FALSE
NULL
FALSE
TRUE
NULL
Prioritätsregeln
Priorität Operator(en)
1
2
3
Arithmetische Operatoren
Verkettungsoperator
Vergleichsoperator
4
5
IS [NOT] NULL, LIKE, [NOT] IN
[NOT] BETWEEN
6
7
8
Ungleich
Logischer Operator NOT
Logischer Operator AND
9
Logischer Operator OR
Prioritätsregeln Beispiele
SELECT last_name, job_id, salary
FROM employees
WHERE
job_id = 'SA_REP'
OR
job_id = 'AD_PRES'
AND
salary > 15000;
oder
SELECT last_name, job_id, salary
FROM employees
WHERE
(job_id = 'SA_REP'
OR
job_id = 'AD_PRES‘)
AND
salary > 15000;
Syntax ORDER BY-Klausel
SELECT
expr
FROM
table
[WHERE
condition(s)]
[ORDER BY {column, expr, num.position} [ASC|DESC]]
 sortieren der abgerufenen Zeilen
 ASC: aufsteigende Reihenfolge, Default
 DESC: absteigende Reihenfolge
 steht am Ende der SELECT-Anweisung
 expr muss Sortierspalte nicht beeinhalten
Sortieren
 In absteigender Reihenfolge sortieren:
… ORDER BY hire_date DESC;
 Nach Spalten Alias sortieren:
… ORDER BY annsal;
 Nach mehreren Spalten sortieren:
… ORDER BY department_id, salary DESC;
 Nach numerischer Position sortieren:
… ORDER BY 2 DESC
Austauschvariablen
 Syntax: &variablenname bzw.
'&variablenname ‘
 Benutzer wird zur Eingabe aufgefordert
 Überall im SELECT-Statement erlaubt




WHERE-Klausel mit variablen Bedingungen
SELECT-Klausel mit variablen Spalten
ORDER BY-Klausel mit variablen
Sortierkriterium
etc.
 &&variablenname für Wiederverwendbarkeit
Austauschvariablen Beispiele
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num;
SELECT employee_id, last_name, job_id, &col
FROM employees
WHERE &condition
ORDER BY &order_column;
SELECT employee_id, last_name, job_id, &&col
FROM employees
ORDER BY &col;
DEFINE / UNDEFINE
DEFINE employee_num = 200
SELECT employee_id, last_name, salary,
department_id
FROM employees
WHERE employee_id = &employee_num;
UNDEFINE employee_num
Hinweise
SELECT *
FROM employees
WHERE last_name = &name;
Benutzer muss für name 'irgendwas' eingeben
vs.
SELECT *
FROM employees
WHERE last_name = '&name';
Benutzer muss für name irgendwas eingeben
Single Row Funktionen
SQL Schulung: Kapitel 4
SQL-Funktionen (Oracle)
 Bearbeitung von Datenwerten
 Single Row-Funktionen
 numerische
 datumsbezogene
 Funktionen dienen folgenden Zwecken:
 Berechnungen mit Daten durchführen
 Einzelne Datenelemente ändern
 Ausgabe für Zeilengruppen bearbeiten
 Datumswerte und Zahlen für die Anzeige formatieren
 Spaltendatentypen konvertieren
 Multiple Row-Funktionen (→ Aggregatfunktionen)
Single Row-Funktionen
 bearbeiten Datenelemente
 akzeptieren Argumente und geben einen Wert zurück
 bearbeiten jede zurückgegebene Zeile
 geben ein Ergebnis pro Zeile zurück
 können den Datentyp ändern
 können verschachtelt sein
 akzeptieren Spalten oder Ausdrücke als Argumente
function_name [(arg1, arg2, …)]
SR-Funktionen -Funktionalität
 jede von der Abfrage zurückgegebene Zeile wird




bearbeitet
pro Zeile wird ein Ergebnis zurückgegeben
der zurückgegebene Datenwert kann einen anderen
Datentyp haben als der referenzierte Wert
die Funktionen erwarten möglicherweise ein oder
mehrere Argumente
sie können in SELECT-, WHERE- und ORDER BYKlauseln verwendet, sowie verschachtelt werden
Single Row-Funktionen
 Zeichenfunktionen




akzeptieren Zeichenwerte und liefern Zeichen- oder
numerische- Werte zurück
numerische Funktionen
akzeptieren numerische Werte und geben numerische Werte
zurück
Datumsfunktionen
bearbeiten Werte vom Datentyp DATE. Zurückgegeben wird ein
DATE mit einer Ausnahme ein NUMBER.
Konvertierungsfunktionen
konvertieren Werte von einem Datentyp in einen anderen
Allgemeine Funktionen
NVL, NVL2, NULLIF, COALESCE, CASE und DECODE
Zeichenfunktionen I
 Funktionen zur Umwandlung der
Groß/Kleinschreibung
Funktion
Zweck
LOWER(column|expr)
Konvertiert alphanum. Zeichenwerte in
Kleinbuchstaben
UPPER(column|expr)
Konvertiert alphanum. Werte in
Grossbuchstaben
INITCAP(column|expr)
Konvertiert alphanum. Werte in Großschreibung, wobei der 1.Buchstabe groß,
alles folgende kleingeschrieben wird
Zeichenfunktionen II
 Funktionen zum Bearbeiten von Zeichen
Funktion
Zweck
CONCAT (col1|expr1,
col2|expr2 )
Verkettet den 1. Zeichenwert mit dem 2.
Entspricht ||-Operator
SUBSTR(column|expr,m Gibt bestimmte Zeichen aus einem
[,n])
Zeichenwert zurück. Die zurückgegebene
Teilzeichenfolge beginnt bei der
Zeichenposition m und ist n Zeichen lang.
LENGTH(column|expr)
Liefert die Anzahl der Zeichen i. Ausdruck
INSTR(column|expr,‘stri
ng‘, [,m],[n])
Gibt die numerische Position einer benannten
Zeichenfolge zurück. Optional kann mit m die
Position angegeben werden, wo die Suche
beginnen soll. n Default m,n=1
Zeichenfunktionen II-Fortsetzung
Funktion
Zweck
LPAD(column|expr,n,‘ Füllt den Zeichenwert rechts- bzw.
string‘)
linksbündig bis zur Zeichenposition n mit
RPAD(column|expr,n, Leerzeichen auf.
‘string‘)
TRIM(leading|trailing| Ermöglicht es, Zeichen am Anfang und/oder
both, trim_character
am Ende einer Zeichenfolge
FROM trim_source)
abzuschneiden. Wenn trim_character oder
trim_source ein Zeichenliteral ist, müssen
Sie in Hochkommata gesetzt werden.
REPLACE(text,
search_string,
replacement_string)
Sucht nach einer Zeichenfolge u. ersetzt
diese durch die angegebene
Ersatzzeichenfolge.
Funktionen zur Umwandlung der
Groß/Kleinschreibung Beispiele
-- Konvertiert in Kleinbuchstaben
SELECT LOWER('SQL Kurs')
FROM DUAL;
-- Konvertiert in Grossbuchstaben
SELECT UPPER('SQL Kurs')
FROM DUAL;
-- Konvertiert den 1.Buchstaben in Gross-- folgende Zeichen in Kleinschreibweise
SELECT INITCAP('SQL Kurs')
FROM DUAL;
Hinweis: die DUAL-Tabelle dient als Mittel zur Anzeige von Funktionsergebnissen
-- gibt die Namen in Gross- und die job-id‘s in Kleinbuchstaben aus
SELECT 'The Job-ID for ' || UPPER(last_name) || ' is ' || LOWER(job_id)
AS "Employee Details"
FROM employees;
Funktionen zur Umwandlung der
Groß/Kleinschreibung Beispiele II
SELECT employee_id, last_name, department_id
FROM employees
WHERE last_name = 'higgins';
0 rows selected
SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';
EMPLOYEE_ID
LAST_NAME
DEPARTMENT_ID
---------------------- ------------------------- ---------------------205
Higgins
110
1 rows selected
… WHERE UPPER(last_name) = 'HIGGINS';
… WHERE INITCAP(last_name) = 'Higgins';
Funkt. zur Bearbeitung von Zeichen
Beispiel
Ausgabe
SELECT CONCAT('Hello', 'World')
FROM DUAL;
HelloWorld
SELECT SUBSTR('HelloWorld',1,5)
FROM DUAL;
Hello
SELECT LENGTH('HelloWorld')
FROM DUAL;
10
SELECT INSTR('HelloWorld','W')
FROM DUAL;
6
SELECT LPAD(salary,10 ,'*')
FROM EMPLOYEES;
… *****24000
SELECT RPAD(salary,10 ,'*')
FROM EMPLOYEES;
… 24000***** …
SELECT REPLACE ('JACK and JUE','J','BL')
FROM DUAL;
BLACK and BLUE
SELECT TRIM ('H' FROM 'HelloWorld')
FROM DUAL;
elloWorld
Funktion z. Bearbeitung von Zeichen
SELECT employee_id,
CONCAT(first_name, last_name) NAME,
LENGTH (last_name),
INSTR(last_name, 'a') "Contains 'a' ?"
FROM employees
WHERE SUBSTR(last_name, -1,1) = 'n';
EMPLOYEE_ID
-----------200
201
102
105
110
112
NAME
------------------JenniferWhalen
MichaelHartstein
LexDe Haan
DavidAustin
JohnChen
Jose ManuelUrman
LENGTH(LAST_NAME)
-----------------6
9
7
6
4
5
Contains 'a' ?
--------------3
2
5
0
0
4
Numerische Funktionen
 akzeptieren numerische Werte und liefern
numerische Werte zurück
Funktion
Zweck
ROUND(column|expr, n)
Rundet eine Spalte, einen Ausdruck oder einen
Wert auf n Dezimalstellen. Ist kein Wert für n
angegeben, wird auf einen ganzzahligen Wert
gerundet.
TRUNC(column|expr, n)
Schneidet eine Spalte, einen Ausdruck oder
einen Wert auf n Dezimalstellen ab. Ist kein
Wert für n angegeben, werden die Ziffern hinter
dem Dezimalkomma abgeschnitten
MOD(m,n)
Gibt den Rest von m geteilt durch n zurück
Hinweis: MOD wird häufig verwendet um zu
ermitteln, ob ein Wert gerade oder ungerade ist.
Numerische Funktionen Beispiele
Beispiel
Ausgabe
SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM DUAL;
45,92
46
50
SELECT TRUNC(45.923,2), TRUNC(45.923,0),
TRUNC(45.923,-1)
FROM DUAL;
45,92
45
40
SELECT MOD(45,2), MOD(45,0), MOD(45,77),
MOD(46,2)
FROM DUAL;
1
45
45
0
Mit Datumswerten arbeiten
 Oracle speichert Datumswerte im folgenden
Format:
Jahrhundert, Jahr, Monat, Tag, Stunde, Minute, Sekunde
 Default-Anzeigeformat:
DD-MON-RR
 gültige Datumswerte liegen zwischen
1.Januar 4712 v.Chr. und dem 31.Dezember
9999
 SYSDATE liefert Datum und Uhrzeit
Mit Datumswerten rechnen
 eine Zahl addieren oder subtrahieren um einen
neuen Datumswert zu erhalten
Zahl ↔ Anzahl an Tagen
Datum +/- Zahl = Datum
 zwei Datumswerte subtrahieren um die Anzahl
dazwischenliegender Tage zu ermitteln
Datum – Datum = Anzahl Tage
 Stunden zu einem Datum addieren, indem die Anzahl
der Stunden durch 24 dividiert wird.
Datum + Zahl/24 = Datum
 Arithmetische Operatoren mit Datumswerten:
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
Datumsfunktionen
Funktion
Ergebnis
MONTH_BETWEEN(d1, d2)
Berechnet die Anzahl Monate zwischen
zwei Datumswerten (d1 und d2)
ADD_MONTH(date,n)
Addiert n Kalendermonate zu einem
Datum date
NEXT_DAY(date, char)
Sucht nach dem 1.Auftreten des durch
char angegebenen Wochentags
LAST_DAY(date)
Letzter Tag des Monats von date
ROUND(date [, format])
Datumswert runden
TRUNC(date [, format])
Datumswert abschneiden
Datumsfunktionen Beispiele
Beispiel
Ausgabe
SELECT MONTHS_BETWEEN('01-SEP-95','01-JAN-94')
FROM DUAL;
20
SELECT MONTHS_BETWEEN('01-JAN-94','01-SEP-95')
FROM DUAL;
-20
SELECT ADD_MONTHS('11-JAN-94',6)
FROM DUAL;
11-JUL-94
SELECT LAST_DAY('11-FEB-94')
FROM DUAL;
28-FEB-94
SELECT ROUND(SYSDATE,'MONTH'), ROUND(SYSDATE,'YEAR')
FROM DUAL;
wenn sysdate: 25.07.07
01-AUG-07
01-JAN-08
SELECT TRUNC(SYSDATE,'MONTH'), TRUNC(SYSDATE,'YEAR')
FROM DUAL;
wenn sysdate: 25.07.07
01-JUL-07
01-JAN-07
Hinweis: Das Ausgabeformat ist abhängig von den gesetzten
Datenbankparametern!
Datumsfunktionen Beispiele II
SELECT employee_id EMP_ID,
hire_date,
TRUNC (MONTHS_BETWEEN (SYSDATE, hire_date),4) TENSURE,
ADD_MONTHS(hire_date,6) REVIEW,
NEXT_DAY(hire_date, 'FRIDAY') NEXTDAY,
LAST_DAY(hire_date) LASTDAY
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 91;
EMP_ID
------167
173
HIRE_DATE
---------21.04.00
21.04.00
TENSURE
--------90,402
90,402
REVIEW
--------21.10.00
21.10.00
NEXTDAY
--------28.04.00
28.04.00
LASTDAY
--------30.04.00
30.04.00
Implizite Datentypkonvertierung
 bei Zuweisungen unter den folgenden Datentypen
kann der Oracle-Server eine automatische
Typkonvertierung vornehmen:
 Hinweis: Es sollte auf eine implizite
Datenkonvertierung verzichtet werden, um die
Zuverlässigkeit einer Anweisung zu garantieren.
Von
VARCHAR2 oder CHAR
In
NUMBER
VARCHAR2 oder CHAR
DATE
NUMBER
DATE
VARCHAR2
VARCHAR2
Explizite Datentypkonvertierung
 SQL stellt 3 Funktionen bereit, um einen Wert von einem
Datentyp in einen anderen zu konvertieren:
 TO_CHAR(number|date, [fmt], [nlsparams])
Konvertiert eine Zahlen- o. Datumswert in eine VARCHAR2Zeichenfolge nach der Formatmaske fmt.
numerische Werte:
nlsparams gibt die folgenden Zeichen an, die von
Zahlenformatelementen zurückgegeben werden:




Dezimalzeichen
Gruppentrennzeichen
lokales Währungszeichen
internationales Währungszeichen
Ohne nlsparams wird der Defaultwert der Session verwendet
Explizite Datentypkonvertierung II

TO_CHAR(number|date, [fmt], [nlsparams])
Datumswerte:
nlsparams legt die Sprache fest, in der die Namen der
Monate und Tage sowie deren Abkürzungen zurückgegeben
werden.

TO_NUMBER (char, [fmt],[nlsparams])
Konvertiert eine Zeichenfolge, die aus Ziffern besteht, in eine Zahl
im Format fmt.
nlsparams hat hier denselben Zweck wie in TO_CHAR
TO_DATE (char, [fmt],[nlsparams])
Konvertiert eine Zeichenfolge, die ein Datum darstellt, gemäß der
Angaben in fmt in einen Datumswert. Wird fmt nicht angegeben, ist
das Format DD-MON-YY
nlsparams hat hier denselben Zweck wie in TO_CHAR

TO_CHAR mit Datumswerten
TO_CHAR(date, 'formatmaske')
Die Formatmaske:
 muss in Hochkommata gesetzt werden
 unterscheidet zwischen Gross- u. Kleinschreibung
 kann alle gültigen Elemente eines Datumsformats
enthalten
 verfügt über ein fm-Element, um aufgefüllte
Leerzeichen zu löschen oder führende Nullen zu
unterdrücken
 wird durch ein Komma vom Datumswert getrennt
Datumsformatmaske - Elemente
Element
Ergebnis
YYYY
Vollständiges Jahr als Zahl
YEAR
Jahr in Worten (Englisch)
MM
Zweistelliger Zahlenwert für den Monat
MONTH
Vollständiger Name des Monats
MON
Abkürzung aus 3 Buchstaben für den Monat
DY
Abkürzung aus 3 Buchstaben f.d.Wochentag
DAY
Vollständiger Name des Wochentages
DD
Tag des Monats als numerischer Wert
HH/HH12/HH24 Sunde des Tages, Stunde (1-12) o. Stunde (0-23)
MI
Minute (0-59)
SS/SSSSS
Sekunde (0-59) / Sek.n. Mitternacht (0-86399)
TO_CHAR mit num. Werten
TO_CHAR(number, 'formatmaske') ddspth
Element
Beschreibung
9
Numerische Position (#9 bestimmt Anzeige)
0
Führende Nullen anzeigen
$
Führendes Dollarzeichen
L
Führendes lokales Wähungssymbol
./,
Dezimalpunkt / Komma an angegebener Position
D
Gibt das Dezimalzeichen a. d. angegebenen Position aus
G
Gibt das Gruppentrennzeichen a. d. angegebenen Position aus
U
Gibt das Eurozeichen mit an
S
Gibt das Vorzeichen mit an +/-
B
Zeigt den Wert Null nicht als 0 sondern als leeres Feld an
Konvertierungsfunktionen Beispiele
Beispiel
Ausgabe
SELECT TO_CHAR(SYSDATE ,'MM/YY')
FROM DUAL;
11/07
SELECT TO_CHAR(SYSDATE ,'MONTH/YEAR')
FROM DUAL;
NOVEMBER/TWO THOUSAND SEVEN
SELECT TO_CHAR(SYSDATE ,'HH:MI:SS')
FROM DUAL;
02:15:33
SELECT TO_CHAR(SYSDATE ,'HH:MI:SSSSS')
FROM DUAL;
02:15:51365
SELECT TO_CHAR(salary, '$99,999.00')
FROM employees
WHERE employee_id = 100;
$24,000.00
Funktionen verschachteln
 Single Row-Funktionen können in einer
beliebigen Zahl von Ebenen verschachtelt
werden
 Verschachtelte Funktionen werden von innen
nach außen ausgewertet
F3 (F2 (F1 (col, arg1) ,arg2) ,arg3)
1.
2.
3.
Funktionen verschachteln Beispiel
SELECT last_name, UPPER(CONCAT(SUBSTR(last_name,1,8)
‚_US‘))
FROM employees
WHERE department_id = 60;
1.
2.
3.
Erg1 = SUBSTR(last_name,1,8)
Erg2 = CONCAT(Erg1, ‚_US‘)
Erg3 = UPPER(Erg2)
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6), ‚FRIDAY‘),
‚fmDay, Month DDth, YYYY‘) „Next 6 Month Review“
FROM employees
ORDER BY hire_date;
Aggregatfunktionen
SQL-Schulung: Kapitel 5
Syntax Aggregatfunktion
SELECT * | {
[DISTINCT] column|expression [alias] } *
FROM table
[WHERE
[GROUP BY
[ORDER BY
condition]
column]
column];
 werden auf Gruppen von Zeilen angewandt
Zeilen werden durch die WHERE-Klausel definiert.
 sind in der WHERE-Klausel selbst nicht erlaubt (später)
 liefern ein Ergebnis pro Gruppe
 Nullwerte werden ignoriert
Aggregatfunktionen-Richtlinien




DISTINCT bewirkt, dass die Funktion keine doppelten Werte berücksichtigt
ALL bewirkt, dass alle Werte, einschl. Doppelter berücksichtigt werden (Default)
expr-Argument kann vom Typ CHAR, VARCHAR2, NUMBER oder DATE sein
n-Argument bezieht sich auf numerische Datentypen
Arten von Gruppenfunktionen und Syntax:
Funktion
Beschreibung
AVG ( [DISTINCT | ALL] n)
Durchschnittwert von n.
COUNT ({*|[DISTINCT | ALL] expr})
Ermittelt die #Zeilen, für die expr ausgewertet wird
MAX ([DISTINCT | ALL] expr)
Höchster Wert von expr
MIN ([DISTINCT | ALL] expr)
Kleinster Wert von expr.
STDDEV ([DISTINCT | ALL] n)
Standardabweichung von n
SUM ([DISTINCT | ALL] n)
Summe der Werte von n
VARIANCE ([DISTINCT | ALL] n)
Varianz von n
Aggregatfunktionen Beispiele
SELECT AVG(salary), MAX(salary), MIN(salary),
SUM(salary)
FROM employees
WHERE hire_date >= '29.01.00'
AND job_id LIKE '%REP%';
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
COUNT Funktion
 3 Formate:
 COUNT (*)
gibt die Anzahl Zeilen aus der Tabelle zurück, die die
Kriterien der SELECT-Anweisung erfüllen.
 COUNT(expr)
gibt die Anzahl der nicht leeren Werte in der durch expr
angegebenen Spalte zurück
 COUNT(DISTINCT expr)
gibt die Anzahl der eindeutigen, nicht leeren Werte in
der durch expr angegebenen Spalte zurück
COUNT Beispiele
SELECT COUNT(*)
FROM employees;
→ ermittelt Anzahl Angestellter
SELECT COUNT(commission_pct)
FROM employees;
→ ermittelt Anzahl Angestellter, die eine Provision bekommen
SELECT COUNT(*) - COUNT(commission_pct)
FROM employees;
→ ermittelt die Anzahl Angestellter, die keine Provision bekommen
SELECT COUNT(DISTINCT department_id)
FROM employees;
→ ermittelt die Anzahl unterschiedlicher Abteilungen, die Angestellte haben
Aggregatfunktionen u. Nullwerte
 Nullwerte werden ignoriert
 WHERE Klauseln können dies nicht verhindern:
SELECT COUNT(commission_pct)
FROM employees
WHERE commission_pct is null;
→ 0 Zeilen
 NVL zwingt Aggregatfunktionen Nullwerte einzubeziehen
SELECT COUNT(NVL(commission_pct,0))
FROM employees
WHERE commission_pct is null;
→ 72 Zeilen
Exkurs:
NVL konvertiert einen Nullwert in einen konkreten Wert.
Definiert ist NVL für DATE, CHAR, VARCHAR2 und NUMBER
Datengruppen
SQL-Schulung: Kapitel 6
Syntax GROUP-BY Klausel
SELECT * | {
[DISTINCT] column|expression [alias] } *
FROM
[WHERE
[GROUP BY
[ORDER BY
table
condition]
group_by_expression]
column]
 Tabellenzeilen können in kleine Gruppen aufgeteilt werden
 group_by_condition gibt die Spalten an, deren Werte die Basis
für die Gruppierung der Zeilen darstellen.
 Die group_by_condition Spalte muss nicht Bestandteil der
SELECT-Klausel sein.
 In Verbindung mit Aggregatfunktionen, müssen alle Spalten in
der SELECT-Klausel, die nicht in Aggregatfunktionen enthalten
sind, in der GROUP BY-Klausel angegeben werden
GROUP BY-Klausel Beispiele
SELECT department_id, AVG(salary)
FROM employees;
→ Fehler
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
→ Ermittelt Durchschnittsgehalt pro Department
DEPARTMENT_ID
---------------------10
20
30
…
AVG(SALARY)
---------------------4400
9500
4150
Gruppen in Gruppen
 GROUP BY-Klausel mit mehreren Spaltenangaben
 Reihenfolge in der GROUP BY-Klausel definiert Haupt- u.
Untergruppen
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id, job_id;
DEPARTMENT_ID
---------------------10
20
20
30
30
JOB_ID
---------AD_ASST
MK_MAN
MK_REP
PU_CLERK
PU_MAN
AVG(SALARY)
-----------------4400
13000
6000
2780
11000
Syntax HAVING-Klausel
SELECT * | {
[DISTINCT] column|expression [alias] } *
FROM
[WHERE
[GROUP BY
[HAVING
[ORDER BY
table
condition]
group_by_expression]
group_by_condition]
column]
 Gruppenergebnisse einschränken auf Basis der
Aggregatfunktion
 Aggregatfunktionen sind in der WHERE-Klausel nicht erlaubt →
HAVING-Klausel
 legt fest, welche Gruppen angezeigt werden sollen
 HAVING auch ohne GROUP BY möglich
HAVING-Klausel Beispiel
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
Abarbeitungsreihenfolge:
1. Zeilen werden gruppiert
2. die Aggregatfunktion wird auf die Gruppe
angewandt
3. die Gruppe, die die Kriterien in der HAVING-Klausel
erfüllt, werden angezeigt
HAVING-Klausel Beispiele II
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary)
SELECT MAX(AVG(salary)) "Max. Durchschnittsgehalt"
FROM employees
GROUP BY department_id
→ Hinweis: Gruppenfunktionen können in bis zu 2 Ebenen verschachtelt werden
Joins
(Daten aus mehreren Tabellen anzeigen)
SQL-Schulung: Kapitel 7
Arten von Joins nach SQL:1999-Standard
Zu den SQL:1999.Standard konformen Joins
zählen:
 Cross Joins
 Natural Joins
 USING-Klausel
 Full (zweiseitige) Outer Joins
 Beliebige Join-Bedingungen für Outer Joins
Syntax SQL:1999-Standard
SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON (table1.column_name = table2.column_name)]
|
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]
|
[CROSS JOIN table2];
NATURAL JOIN
 basiert auf allen Spalten, die in beiden Tabellen
denselben Namen haben
 ausgewählt werden die Zeilen, die in allen
übereinstimmenden Spalten die gleichen Werte
haben
 gleichnamige Spalten mit unterschiedlichen
Datentypen lösen Fehler aus
 Spalten, die in beiden Tabellen denselben Namen
haben, müssen ohne Kennzeichner auskommen
NATURAL JOIN Beispiel
desc locations;
Name
-----------------------------LOCATION_ID
STREET_ADDRESS
POSTAL_CODE
CITY
STATE_PROVINCE
COUNTRY_ID
desc departments;
Name
-----------------------------DEPARTMENT_ID
DEPARTMENT_NAME
MANAGER_ID
LOCATION_ID
Null
Type
-------- ------------------------------NOT NULL NUMBER(4)
VARCHAR2(40)
VARCHAR2(12)
NOT NULL VARCHAR2(30)
VARCHAR2(25)
CHAR(2)
Null
-------NOT NULL
NOT NULL
Type
------------------------------NUMBER(4)
VARCHAR2(30)
NUMBER(6)
NUMBER(4)
NATURAL JOIN Beispiel Forts.
SELECT department_id, department_name, location_id, city
FROM departments NATURAL JOIN locations
DEPARTMENT_ID
--------------60
50
10
20
30
90
DEPARTMENT_NAME
------------------------IT
Shipping
Administration
Marketing
Purchasing
Executive
LOCATION_ID
--------------1400
1500
1700
1800
1700
1700
CITY
---------------------Southlake
South San Francisco
Seattle
Toronto
Seattle
Seattle
…
SELECT department_id, department_name, location_id, city
FROM departments NATURAL JOIN locations
WHERE department_id IN (20,50)
DEPARTMENT_ID
----------------20
50
DEPARTMENT_NAME
--------------------Marketing
Shipping
LOCATION_ID
----------------1800
1500
CITY
---------------------Toronto
South San Francisco
JOINS mit der USING-Klausel
 wenn mehrere Spalten denselben Namen, aber unterschiedliche
Datentypen haben, können Sie die NATURAL JOIN-Klausel mit
der USING-Klausel ändern und die Spalten angeben, die für
einen Equi Join verwendet werden
 mit der USING-Klausel können Sie angeben, dass nur eine
Spalte verwendet werden soll, wenn mehrere übereinstimmende
Spalten existieren
 NATURAL JOIN- und USING-Klauseln schliessen sich
gegenseitig aus
 für die in der USING-Klausel referenzierten Spalten dürfen in
der gesamten SQL-Anweisung keine Kennzeichner verwendet
werden
USING-Klausel Beispiel
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING
(location_id)
WHERE location_id = 1400;
CITY
DEPARTMENT_NAME
------------------------------ -----------------------------Southlake
IT
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING
(location_id)
WHERE d.location_id = 1400
ORA-25154: column part of USING clause cannot have qualifier
Mehrdeutige Spaltennamen
 mit Tabellenpräfixen können die
Spaltennamen, die in mehreren Tabellen
vorkommen, eindeutig gekennzeichnet
werden
 Tabellenpräfixe verbessern die Performance
 mit Spalten-Aliasnamen können die Spalten
unterschieden werden, die identische Namen
in unterschiedlichen Tabellen aufweisen
Tabellen-Aliasnamen verwenden
 mit Aliasnamen können Abfragen vereinfacht werden
 die Lesbarkeit von Abfragen wird erhöht
 die Performance wird gesteigert
 max. 30 Zeichen
 gilt nur für die aktuelle SELECT-Anweisung
SELECT e.employee_id, e.last_name, d.location_id,
department_id
FROM employees e JOIN departments d
USING (department_id);
ON-Klausel
 die Join-Bedingung für Natural Joins ist im Prinzip ein




Equi-Join aller Spalten mit gleichen Namen.
mit der ON-Klausel können beliebige Bedingungen
oder die zu verknüpfenden Spalten angegeben
werden
die Spalten müssen nicht den gleichen Namen haben
die Join-Bedingung wird von anderen Suchkriterien
getrennt
die ON-Klausel macht den Code lesbarer
ON-Klausel Beispiel
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id );
EMPLOYEE_ID
-----------200
201
202
114
119
115
116
…
LAST_NAME
----------Whalen
Hartstein
Fay
Raphaely
Colmenares
Khoo
Baida
DEPARTMENT_ID
--------------10
20
20
30
30
30
30
DEPARTMENT_ID
----------------10
20
20
30
30
30
30
LOCATION_ID
-----------1700
1800
1800
1700
1700
1700
1700
ON-Klausel (Self Join)
 eine Tabelle wird mit sich selbst verknüpft
 es müssen unterschiedliche Aliasnamen verwendet werden
 sinnvoll, wenn Werte einer Spalte mit anderen Werten derselben Spalte
verglichen werden (z.B. hierarchisch strukturierte Daten)
Beispiel:
SELECT e.last_name emp, m.last_name mgr
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id );
EMP
------------------------Fay
Gietz
Zlotkey
Cambrault
…
MGR
------------------------Hartstein
Higgins
King
King
Zusätzliche Bedingungen
 die Join-Bedingung kann mit beliebigen
Bedingungen kombiniert werden.
SELECT e.employee_id, e.last_name, e.department_id, d.department_id,
d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id )
AND e.manager_id=149;
ist equivalent zu
SELECT e.employee_id, e.last_name, e.department_id, d.department_id,
d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id )
WHERE e.manager_id=149;
3-Way Joins mit der ON-Klausel
 Verbindung von 3 Tabellen
 Ausführungsrichtung nach SQL:1999 von links nach
rechts
SELECT employee_id, city, department_name
FROM employees e JOIN departments d
ON (e.department_id = d.department_id )
JOIN locations l
ON (d.location_id = l.location_id);
 1. ausführbarer Join: emloyees JOIN departments
referenzierbar sind Spalten dieser Tabellen
 2. ausführbarer Join: departments JOIN locations
Non Equi Join
 nicht auf den = -Operator bezogen
Beispiel:
SELECT e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;
Outer Join
 verwenden, wenn Datensätze ohne direkte
Übereinstimmung existieren
 Zeile wird angezeigt, trotzdem Join-Bedingung nicht
erfüllt ist
 es werden alle Zeilen zurückgegeben, die die JoinBedingung erfüllen +
einige oder alle Zeilen aus einer Tabelle, für die keine
Zeilen in der anderen Tabelle die Join-Beziehung
erfüllen
 Arten:



LEFT OUTER
RIGHT OUTER
FULL OUTER
OUTER Join Arten
 LEFT OUTER:
ruft alle Zeile aus der linken Tabelle ab, auch wenn in
der rechten Tabelle keine Übereinstimmungen
existieren
 RIGHT OUTER:
ruft alle Zeilen aus der rechten Tabelle ab, auch wenn
in der linken Tabelle keine Übereinstimmungen
existieren
 FULL OUTER:
Zeigt alle Zeilen an, die Übereinstimmungen
aufweisen, zzgl. Zeilen aus beiden Tabellen, die
keine Übereinstimmungen aufweisen
LEFT OUTER JOIN Beispiel
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
LAST_NAME
----------------Grant
Higgins
Gietz
Sciarra
DEPARTMENT_ID
DEPARTMENT_NAME
------------------ -----------------110
110
100
Accounting
Accounting
Finance
Faviet
100
Finance
→ es werden alle Angestellten mit Ihrer Department-Zuordnung
angezeigt, auch wenn es keine Zuordnung gibt
RIGHT OUTER JOIN Beispiel
SELECT e.last_name, d.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;
LAST_NAME
DEPARTMENT_ID
--------------- ---------------120
270
130
…
260
Zlotkey
80
Whalen
10
Weiss
50
DEPARTMENT_NAME
-------------------Treasury
Payroll
Corporate Tax
Recruiting
Sales
Administration
Shipping
→ es werden alle Departments mit Ihrer Department-Zuordnung
angezeigt, auch wenn ihnen keine Angestellten zugeordnet sind
FULL OUTER JOIN Beispiel
SELECT e.last_name, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
LAST_NAME
DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------- -----------------------------120
Treasury
270
Payroll
130
Corporate Tax
Vishney
80
Sales
Vargas
50
Shipping
Grant
50
Shipping
Grant
→ es werden alle Departments und alle Angestellten anzeigt, mit
und ohne jeweiliger Zuordung
Kartesische Produkte
 Definition: Alle Zeilen aus der 1.Tabelle werden mit
allen Zeilen aus der 2.Tabelle verknüpft
 Anzahl generierter Zeilen = Anzahl Zeilen d. 1.
Tabelle * Anzahl Zeilen d. 2.Tabelle
 ein kartesisches Produkt wird gebildet, wenn:



eine Join-Bedingung fehlt
eine Join-Bedingung ungültig ist
alle Zeilen aus der ersten Tabelle mit allen Zeilen aus
der zweiten Tabelle verknüpft werden
 → gültige Join Bedingungen sind wichtig!
CROSS JOIN-Klausel
 erstellt Kreuzprodukt aus zwei Tabellen
 ein Kreuzprodukt entspricht dem kartesischen
Produkt
Beispiel:
SELECT e.department_name, d.department_name
FROM departments e CROSS JOIN departments d;
Unterabfragen
SQL-Schulung: Kapitel 8
Zweck von Unterabfragen
 eine Unterabfrage ist eine SELECT-
Anweisung, die in eine Klausel einer anderen
SELECT-Anweisung eingebettet ist z.B.


WHERE-Klausel
HAVING-Klausel
 nützlich, wenn Zeilen aus einer Tabelle mit
einer Bedingung ausgewählt werden, die von
den Daten in einer Tabelle selbst abhängt
Unterabfragen Syntax
SELECT
FROM
WHERE
select_list
table
expr operator
(SELECT
select_list
FROM
table);
 die Unterabfrage (innere Abfrage) wird einmal vor der
Hauptabfrage (äussere Abfrage) ausgeführt.
 das Ergebnis der Unterabfrage wird von der
Hauptabfrage verwendet
operator
eine Vergleichsbedingung
Single Row- u. Multiple Row-Operatoren
Unterabfrage Beispiel
SELECT last_name, salary
FROM employees
11000
WHERE salary >
(SELECT salary
FROM employees
WHERE last_name = 'Abel');
LAST_NAME
------------------------King
Kochhar
De Haan
Greenberg
Russell
Partners
Errazuriz
Ozer
SALARY
---------------------24000
17000
17000
12000
14000
13500
12000
11500
Unterabfragen Richtlinien
 Unterabfragen werden in Klammern ()
eingeschlossen
 bessere Lesbarkeit durch: Unterabfragen werden auf
der rechten Seite der Vergleichsbedingung
angegeben
 Verwendung von Single Row- und Multiple RowOperatoren führen zu 2 Arten von Unterabfragen:


Single Row-Unterabfrage
Multiple Row-Unterabfrage
 Haupt- und Unterabfrage können sich auf
unterschiedliche Tabellen beziehen
Arten von Unterabfragen
 Single Row-Unterabfrage
Abfrage, deren innere SELECT-Anweisung
nur eine Zeile zurückgibt
 Multiple Row-Unterabfrage
Abfrage, deren innere SELECT-Anweisung
mehrere Zeilen zurückgibt
 Multiple Column-Unterabfragen
Abfrage, deren innere SELECT-Anweisung
mehrere Spalten zurückgibt (später)
Singe Row-Unterabfrage
 geben nur eine Zeile zurück
 verwenden Single Row-Operatoren
=, >, >=, <, <=, <>
Beispiel:
SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 141);
LAST_NAME
------------------------Nayer
Mikkilineni
Landry
Markle
Bissot
JOB_ID
---------ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK
ST_CLERK …
Single Row-Unterabfrage Beispiel
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);
Aggregatfunktionen in Unterabfragen
 mit einer Aggregatfunktion kann eine
Unterabfrage auf eine Zeile reduziert werden
Beispiel:
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees
);
LAST_NAME
JOB_ID
SALARY
------------------------- ---------- ---------------------Olson
ST_CLERK
2100
Unterabfragen in HAVING-Klausel
 das Managementsystem führt Unterabfragen
zuerst aus
 Unterabfragen-Ergebnis wird an die HAVING
Klausel der Hauptabfrage weitergeleitet
Beispiel:
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
Wieviele Zeilen werden
zurückgegeben?
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = ‘Haas‘ );
no rows selected
Hinweis:
Es gibt keinen Angestellten mit dem Namen Haas, so liefert die
Unterabfrage keine Zeilen zurück. Ergebnis der Unterabfrage ist
null. Ein Vergleich mit null liefert immer null, auch wenn es eine
Jobkennung null geben würde.
Wo liegt der Fehler?
SELECT
FROM
WHERE
employee_id, last_name
employees
salary = ( SELECT MIN(salary)
FROM employees
GROUP BY department_id);
ERROR at line 4: ORA-01427: single-row subquery
returns more than one row
Die Unterabfrage liefert mehrere Zeilen zurück, daher muss
auch ein Multiple Row Operator für die Verarbeitung der
Unterabfrage eingesetzt werden!
Multiple Row-Unterabfrage
 geben mehrere Zeilen zurück
 verwenden Multiple Row-Vergleichsoperatoren
IN, ANY, ALL
SELECT
FROM
WHERE
employee_id, last_name
employees
salary IN ( SELECT MIN(salary)
FROM employees
GROUP BY department_id);
Das Suchkriterium der Hauptabfrage sieht dann wie folgt aus:
SELECT employee_id, last_name
FROM
employees
WHERE
salary IN (2500, 4200, 4400, 6000, 7000, 8300,
8600, 17000);
ANY-Operator
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
Bedeutung und Anwendung von ANY:
< ANY
weniger als das Maximum
> ANY
mehr als das Minimum
= ANY
hat dieselbe Bedeutung wie IN
ALL-Operator
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
Bedeutung und Anwendung von ALL:
< ALL
weniger als das Minimum
> ALL
mehr als das Maximum
Mengenoperatoren
SQL-Schulung: Kapitel 9
Mengenoperatoren
 kombinieren die Ergebnisse von zwei oder mehr
Komponentenabfragen zu einem Ergebnis
 werden auch zusammengesetzte Abfragen genannt
 haben die gleiche Priorität
 Auswertung von links nach rechts, bzw. open nach unten
Operator
Rückgabe
UNION
Alle eindeutigen Zeilen, die von einer der Abfragen
ausgewählt werden
UNION ALL
Alle Zeilen, die von einer der Abfragen ausgewählt werden,
einschliesslich doppelter Zeilen
INTERSECT
Alle eindeutigen Zeilen, die von beiden Abfragen
ausgewählt werden
MINUS
Alle eindeutigen Zeilen, die von der ersten, nicht jedoch
von der zweiten Anweisung ausgewählt werden
UNION Operator
 gibt Ergebnisse aus beiden Abfragen zurück
 doppelte Ergebnisse werden ausgeblendet
 Anzahl und Datentyp der gewählten Spalten müssen




in allen von der Abfrage verwendeten SELECTAnweisungen identisch sein
UNION gilt für alle ausgewählten Spalten
die Spaltennamen müssen nicht identisch sein
der IN-Operator hat eine höhere Priorität als der
UNION-Operator
Standard: Ausgabe in aufsteigender Reihenfolge
sortiert, basierend auf der ersten Spalte
UNION-Operator Beispiel
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;
 doppelte Datensätze werden ausgeblendet
 Datensätze gelten als gleich, wenn sie in allen
Projektionsspalten gleich sind
UNION ALL-Operator
 im Gegensatz zu UNION werden doppelte
Zeilen nicht ausgeblendet
 die Ausgabe wird nicht standardmässig
sortiert
 DISTINCT kann nicht verwendet werden
INTERSECT-Operator
 gibt alle die Zeilen zurück, die in allen Abfragen




vorkommen
Anzahl und Datentyp der von den SELECTAnweisungen der Abfragen gewählten Spalten
müssen in allen von der Abfrage verwendeten
SELECT-Anweisungen identisch sein
Spaltennamen müssen nicht identisch sein
eine Änderung der Reihenfolge der Abfragen, welche
die Schnittmenge bilden, wirkt sich nicht auf das
Ergebnis aus
INTERSECT ignoriert NULL-Werte nicht
INTERSECT-Operator Beispiel
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;
 liefert die Angestellten, die einmal die Job-
Position gewechselt haben und wieder in die
alte Position zurückgekehrt sind
MINUS-Operator
 gibt die Zeilen aus der ersten Abfrage zurück, die in
der zweiten Abfrage nicht vorkommen
 Anzahl und Datentyp der von den SELECTAnweisungen der Abfragen gewählten Spalten
müssen in allen von der Abfrage verwendeten
SELECT-Anweisungen identisch sein
 Spaltennamen müssen nicht identisch sein
 alle Spalten in der WHERE-Klausel müssen in der
SELECT-Klausel enthalten sein, damit der Operator
MINUS ordnungsgemäss funktioniert
MINUS-Operator Beispiel
SELECT employee_id, job_id
FROM employees
MINUS
SELECT employee_id, job_id
FROM job_history;
 liefert die Angestellten, die noch nie den Job
gewechselt haben
Mengenoperatoren Richtlinien
 die Ausdrücke in den SELECT-Listen müssen in
Anzahl und Datentyp übereinstimmen
 durch Klammern kann die Ausführungsreihenfolge
geändert werden
 die ORDER BY-Klausel


darf nur ganz am Ende der Anweisung stehen
akzeptiert den Spaltennamen, Aliasnamen aus der
ersten SELECT-Anweisung oder die positionale
Notation
 Mengenoperatoren können in Unterabfragen
verwendet werden
Ausdrücke in den SELECT-Listen
SELECT employee_id, department_id
FROM employees
WHERE (employee_id, department_id)
IN (SELECT employee_id, department_id
FROM employees
UNION
SELECT employee_id, department_id
FROM job_history);
Dummy Spalten
 um SELECT-Listen anzugleichen
SELECT department_id,
TO_NUMBER(null) location, hire_date
FROM employees
UNION
SELECT department_id,
location_id, TO_DATE(null)
FROM departments;
DML (Data Manipulation Language)
SQL-Schulung: Kapitel 10
Datenmanipulationssprache (DML)
 eine DML-Anweisung wird ausgeführt, wenn



in einer Tabelle eine neue Zeile hinzugefügt wird
→ INSERT
in einer Tabelle eine vorhandene Zeile geändert wird
→ UPDATE
in einer Tabelle eine vorhandene Zeile gelöscht wird
→ DELETE
 eine Transaktion besteht aus einer Zusammenstellung von
DML-Anweisungen, die eine logische Arbeitseinheit bilden
 eine Transaktion kann bestätigt werden
→ COMMIT
 eine Transaktion kann rückgängig gemacht werden
→ ROLLBACK
INSERT-Anweisung Syntax
INSERT INTO table [(column [, column….])]
VALUES (value [, value…]);
 mit dieser VALUE-Klausel-Syntax wird genau eine
Zeile eingefügt
table
column
value
steht für den Namen der Tabelle
ist der Name der Tabellenspalte
ist der entsprechende Wert der Spalte
INSERT-Anweisung
 die Spaltenangaben können weggelassen werden,
wenn für alle Spalten Werte in Default-Reihenfolge
angegeben werden
 Implizite Methode: weglassen der Spalte aus der
Spaltenliste:
INSERT INTO departments (department_id,
department_name)
VALUES (30, 'Purchasing');
 Explizite Methode: Für alle Spalten werden Werte
angegeben:
INSERT INTO departments
VALUES (30, 'Purchasing',NULL,NULL);
INSERT Anweisung Forts.
 Datumswerte und Zeichenketten werden in
einfachen Anführungszeichen angegeben
 mit DESC tabellenname kann die DefaultReihenfolge ermittelt werden
 DBMS prüft Datentypen, Datenbereiche und
Datenintegritätsbedingungen (Constraints)




NOT NULL Constraint
UNIQUE Constraint
FOREIGN KEY Constraint
CHECK Constraint
Spezielle Werte
 SYSDATE ist eine Funktion, die das aktuelle Datum
und Uhrzeit zurückliefert
INSERT INTO employees
VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567',
SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100);
 TO_DATE für nicht Default-Formate
INSERT INTO employees
VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.124.4567',
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT',
6900, NULL, 205, 100);
Default-Format: DD-MON-YY
YY entspricht dem aktuellem Jahrhundert, DefaultUhrzeit ist 00:00:00
Zeilen aus anderen Tabellen kopieren
 INSERT in Verbindung mit einer Unterabfrage
INSERT INTO sales_reps(id, name, salary,
commission_pct)
SELECT employee_id, last_name, salary,
commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
 keine VALUES-Klausel
 die Anzahl der Spalten in der INSERT-Klausel
muss mit der Spaltenanzahl in der
Unterabfrage übereinstimmen
INSERT über Unterabfrage Syntax
INSERT INTO table [(column [, column….])]
subquery;
 Anzahl der Datentypen der Spalten in der
Spaltenliste der INSERT-Klausel müssen mit
der Anzahl und den Datentypen der Werte in
der Unterabfrage übereinstimmen.
 Für eine Kopie aller Zeilen einer Tabelle:
INSERT INTO copy_emp
SELECT * FROM employees;
Achtung: copy_emp muss natürlich existieren!
UPDATE-Anweisung Syntax
UPDATE table
SET
column = value [, column = value, …]
[WHERE condition];
table
column
value
condition
steht für den Namen der Tabelle
ist die Tabellenspalte, deren Wert
geändert werden soll
ist der entsprechende Wert
identifiziert die zu aktualisierenden Zeilen und
besteht aus Spaltennamen, Ausdrücken,
Konstanten, Unterabfragen und Vergleichsoperatoren
UPDATE-Anweisung Beispiele
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
Updates mit Hilfe von Unterabfragen:
UPDATE employees
SET job_id = (SELECT job_id
FROM employees
WHERE employee_id = 205),
salary = (SELECT salary
FROM employees
WHERE employee_id = 205)
WHERE employee_id = 114;
Hinweis: Die Unterabfragen brauchen sich nicht auf die gleiche
Tabelle beziehen
DELETE-Anweisung Syntax
DELETE [FROM] table
[WHERE condition];
table
condition
ist der Name der Tabelle
identifiziert die zu löschenden Zeilen und besteht
aus Spaltennamen, Ausdrücken, Konstanten,
Unterabfragen und Vergleichsoperatoren
DELETE-Anweisung Beispiele
DELETE FROM departments
WHERE department_name = 'FINANCE';
→ Löscht das Department Finance
DELETE FROM copy_emp;
→ Löscht alle Datensätze der Tabelle copy_emp
DELETE FROM departments
WHERE department_id IN (30,40);
→ Löscht die Departments mit der ID 30 und 40
DELETE-Anweisung Beispiele II
DELETE FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name LIKE '%Public%');
→ Löscht alle Angestellten aller Abteilungen, welche im
Namen Public verwenden.
TRUNCATE-Anweisung
 Entfernt alle Zeilen aus einer Tabelle, so dass die
Tabelle anschliessend leer und die Tabellenstruktur
weiter intakt ist
 ist eine Anweisung der Datendefinitionssprache
(DDL), keine DML-Anweisung, und kann nicht ohne
Weiteres rückgängig gemacht werden
 Syntax
TRUNCATE TABLE table_name;
 Beispiel:
TRUNCATE TABLE copy_emp;
TRUNCATE
 effiziente Methode um Tabellen zu leeren
 schneller als DELETE, weil:



es eine DDL-Anweisung ist, ohne
ROLLBACK-Informationen
existierende DELETE-Trigger werden
umgangen
unterliegt dennoch CONSTRAINTS
Datenbanktransaktionen
Können bestehen aus:
 DML-Anweisungen, die eine konsistente
Änderung der Daten bewirken
 einer DDL-Anweisung
 einer Anweisung der Datenkontrollsprache
(DCL)
Datenbanktransaktionen II
 eine Transaktion beginnt, wenn die erste DML-SQL-Anweisung
ausgeführt wird
 eine Transaktion endet bei einem der folgenden Ereignisse:
 eine COMMIT- oder ROLLBACK-Anweisung wird abgesetzt
 eine DDL- oder DCL-Anweisung wird ausgeführt
 ein Benutzer beendet die Session
 das System stürzt ab
 Nach der Beendigung einer Transaktion startet die nächste
ausführbare SQL-Anweisung automatisch die nächste
Transaktion
 DDL- und DCL-Anweisungen werden autom. festgeschrieben,
die Transaktion wird implizit beendet.
COMMIT- und ROLLBACK
Anweisungen
 stellen die Datenkonsistenz sicher
 erstellen eine Sicht auf die Datenänderung
bevor sie dauerhaft gespeichert wird
→ Kontrolle über die dauerhafte
Speicherung
 gruppieren logisch zusammenhängende
Operationen
 explizite Steueranweisungen für
Transaktionen
Transaktionen steuern
Anweisung
Beschreibung
COMMIT
Beendet die aktuelle Transaktion, indem alle noch nicht
gespeicherten Datenänderungen dauerhaft
festgeschrieben werden
SAVEPOINT name Markiert einen Savepoint innerhalb einer Transaktion
ROLLBACK
Beendet die aktuelle Transaktion, indem alle noch nicht
gespeicherten Datenänderungen verworfen werden
Rollt die akt. Transaktion zum angegebenen Savepoint
ROLLBACK TO
SAVEPOINT name zurück, wodurch alle Änderungen und/oder Savepoints
verworfen werden, die nach dem Savepoint erstellt
wurden, zu dem das Rollback erfolgt. Wird die TO
SAVEPOINT-Klausel weggelassen, so wird die
gesammte Transaktion zurückgesetzt.
Änderungen zurückrollen
UPDATE ….
SAVEPOINT update_done;
Savepoint created.
INSERT…
ROLLBACK TO update_done;
Rollback complete.
→ das Insert wird rückgängig gemacht
Implizite Transaktionsverarbeitung
 Commit wird automatisch gesetzt bei:


DDL-Anweisungen
DCL-Anweisungen
 autom. Rollback bei Systemausfall
 Vorsicht bei bzw. mit AUTOCOMMIT
Zustand der Daten (vor Commit u. Rollback)
 jede Datenänderung ist im Verlauf einer Transaktion
vorübergehend, bis die Transaktion festgeschrieben wird
 DML-Operationen finden zunächst im Datenbank-Buffer statt
 der aktuelle Benutzer kann die Ergebnisse von DML-
Operationen durch Abfragen der entsprechenden Tabellen
prüfen
 andere Benutzer können die Ergebnisse der DML-Operation
des akt. Benutzers nicht sehen
 DML-Operationen sperren die entsprechenden Zeilen, damit die
Daten nicht von mehreren Seiten gleichzeitig geändert werden
Zustand der Daten (nach Commit)
 Datenänderungen werden dauerhaft in der
Datenbank festgeschrieben
 der vorherige Zustand kann nicht wiederhergestellt
werden (Ausnahme, durch das DBMS-Recovery)
 alle Benutzer können sich die Ergebnisse anzeigen
 Sperren der betroffenen Zeilen werden freigegeben.
Diese Zeilen können von den Benutzern wieder
bearbeitet werden
 alle Savepoints werden gelöscht
Daten festschreiben
DELETE FROM employees
WHERE employee_id = 99999;
1 row deleted.
INSERT INTO departments
VALUES (290, ' Corporate Tax ', NULL, 1700);
1 row created.
COMMIT;
Commit complete.
Zustand der Daten (nach Rollback)
 Datenänderungen werden rückgängig
gemacht
 der vorherige Zustand der Daten wird wieder-
hergestellt
 Sperren der betroffenen Zeilen werden
freigegeben.
 alle Savepoints werden gelöscht
Daten festschreiben
DELETE FROM employees;
107 rows deleted.
ROLLBACK;
Rollback complete.
DELETE FROM employees
WHERE employee_id = 164;
1 row deleted.
SELECT * FROM employees WHERE employee_id = 164;
no rows selected.
SELECT * FROM employees;
106 rows selected.
COMMIT;
Commit complete.
DDL-Anweisungen !!!
DELETE FROM employees;
107 rows deleted.
COMMIT;
CREATE TABLE xyz;
COMMIT;
SELECT * FROM emloyees;
no rows selected.
ROLLBACK;
SELECT * FROM employees;
no rows selected.
implizites Commit vor und nach
jeder DDL-Anweisung!!!
Lesekonsistenz
 die Lesekonsistenz stellt jederzeit eine konsistente Ansicht der





Daten sicher.
Lesevorgängen werden keine Daten angezeigt, die gerade
geändert werden.
die Änderungen eines Benutzers verursachen keine Konflikte
mit den Änderungen eines anderen Benutzers
Schreibvorgänge können sicher sein, dass Änderungen an der
Datenbank konsistent erfolgen.
die Lesekonsistenz stellt für dieselben Daten folgendes sicher:
 Lesevorgänge müssen keine Schreibvorgänge abwarten
 Schreibvorgänge müssen keine Lesevorgänge abwarten
die Lesekonsistenz stellt sicher, dass jedem Benutzer die Daten
mit dem Status zum Zeitpunkt des letzten Commits angezeigt
werden
Lesekonsistenz technisch
 sie wird automatisch implementiert
 es wird eine Teilkopie der Datenbank in
Undo-Segmenten aufbewahrt, sobald eine
DML-Operation ausgeführt wird
 Daten aus den Undo-Segmenten: Snapshot
 Undo-Segmente werden nach dem
Commit/Rollback wieder freigegeben
 im Rollback-Fall werden die Daten aus den
Undo-Segmenten wieder in die Tabellen
geschrieben
DDL (Data Definition Language)
SQL-Schulung: Kapitel 11
Datendefinitionssprache (DDL)
 CREATE datenbankobjekt
Erzeugt ein neues Datenbankobjekt
 ALTER datenbankobjekt
Nimmt Änderungen an der Definition
des Datenbankobjekts vor
 DROP datenbankopjekt
Löscht ein Datenbankobjekt
Hinweis: Es sind unterschiedliche Privilegien notwendig,
um die Methoden der DDL zu verwenden.
Datenbankobjekte
 Tabellen
 Views
 Sequences
 Pseudospalten
 Indizes
 Synonyme
DDL- Tabellen
SQL-Schulung: Kapitel 12
Benennungsregeln
Tabellen und Spaltennamen:
 müssen mit einem Buchstaben beginnen
 dürfen 1-30 Zeichen lang sein
 dürfen nur die folgenden Zeichen enthalten:
A-Z, a-z, 0-9, _,$,#
 dürfen nicht mit dem Namen eines anderen Objekts
desselben Benutzers übereinstimmen
 dürfen nicht mit einem reservierten Wort
übereinstimmen (abhängig vom jeweiligen DBMS)
Ansonsten:
 es sollten aussagekräftige Namen gewählt werden
 Gross- und Kleinschreibung spielt keine Rolle
CREATE TABLE-Anweisung
Syntax
CREATE TABLE [schema].table
(column datatype [DEFAULT expr] [,…]);
 CREATE TABLE Privileg
 Speicherbereich
schema
table
column
datatype
DEFAULT expr
Eigentümer
Tabellenname
Spaltenname
Datentyp u. Länge der Spalte
definiert default, falls der Spalte
kein Wert zugewiesen wird
Tabellen anderer Benutzer
referenzieren
 Tabellen anderer Benutzer befinden sich in einem
anderen Schema.
 Schema muss als Präfix vor den Tabellennamen
gesetzt werden
 User muss berechtigt sein, die Tabellen lesen zu
dürfen
SELECT *
FROM userA.employees;
SELECT *
FROM userB.employees;
OPTION DEFAULT
 Für Einfügeoperationen kann ein Default-Wert definiert werden
… hire_date DATE DEFAULT SYSDATE
 zulässige Werte sind Literalwerte, Ausdrücke und SQLFunktionen
 Unzulässige Werte sind Spaltennamen und Pseudospalten
 der Default-Datentyp muss dem Spaltendatentyp entsprechen
CREATE TABLE hire_dates
(id
NUMBER(8),
hire_date
DATE DEFAULT SYSDATE);
Table created.
DESCRIBE dept
Datentypen
Datentyp
Beschreibung
VARCHAR2(n)
Zeichendaten variabler Länge mit (1 < n > 4000 Bytes)
CHAR(n)
Zeichdaten fester Länge mit (1 < n > 2000 Bytes)
NUMBER(n,p)
Numerische Daten variabler Länge. n ist
Gesamtstellenzahl und p ist Anzahl Nachkommastellen
DATE
Datums- u. Uhrzeit-Wert
LONG
Zeichendaten variabler Länge (→ 2 GB)
CLOB
Zeichendaten (→ 4 GB)
RAW (n) und
LONG RAW
Binärdaten des Typs RAW (1 < n > 2000 Bytes)
LONG RAW (→ 2 GB)
BLOB
Binärdaten (→ 4GB)
BFILE
In einer externen Datei gespeicherte Binärdaten (→ 4 GB)
ROWID
Ein Base-64-Code, der die eindeutige Adresse einer Zeile
in einer Tabelle bestimmt
Datetime-Datentypen
Datentyp
Beschreibung
TIMESTAMP
Datum mit Nachkommastellen f. Sekunden
INTERVAL
YEAR TO
MONTH
Wird als Intervall von Jahren und Monaten
gespeichert
INTERVAL DAY
TO SECOND
Wird als Intervall von Tagen, Stunden, Minuten
und Sekunden gespeichert
 TIMESTAMP ist eine Erweiterung von DATE
 setzt sich zusammen aus Jahr, Monat, Tag sowie Stunden,
Minuten, Sekunden und 1/1000 stel Sekunde
 Angabge von Zeitzonen moeglich)
Constraints
 erzwingen Regeln auf Tabellenebene
 verhindern das Löschen einer Tabelle, wenn
Abhängigkeiten vorhanden sind
 Constraint-Typen:





NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
Constraints und ihre Bedeutung
Constraint
Beschreibung
NOT NULL
Gibt an, dass die Spalte keinen Nullwert
enthalten darf
UNIQUE
Gibt eine Spalte oder Spaltenkombination an,
deren Werte in allen Zeilen der Tabelle
eindeutig sein muss
PRIMARY KEY
Identifiziert jede Zeile einer Tabelle eindeutig
FOREIGN KEY
Richtet eine Fremdschlüsselbeziehung
zwischen der Spalte und einer Spalte in der
referenzierten Tabelle ein und setzt diese
durch
CHECK
Legt eine Bedingung fest, die erfüllt sein
muss
Constraints Richtlinien
 nicht benannte Constraints bekommen einen
generierten Namen (bei Oracle SYS_Cn)
 können mit der Tabelle zusammen erstellt
oder im nachhinein definiert werden
 definiert auf Spalten- oder Tabellenebene
 Constraints, die mehrere Spalten betreffen
muessen auf Tabellenebene definiert werden
 im Data Dictionary gespeichert
Constraints definieren
 Syntax
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr]
[column_constraint],
…
[table_constraint][,…]);
 auf Spaltenebene
column [CONSTRAINT constraint_name] constraint_type
 auf Tabellenebene
column,…
[CONSTRAINT constraint_name] constraint_type
(column, …)
Constraints definieren Beispiel
 Constraint auf Spaltenebene:
CREATE TABLE employees (
employee_id NUMBER(6)
CONSTRAINT emp_emp_id_pk PRIMARY KEY,
first_name
VARCHAR2(20),…
)
 Constraint auf Tabelleneben:
CREATE TABLE employees (
employee_id NUMBER(6),
first_name
VARCHAR2(20),..
job_id
VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));
)
NOT NULL Constraint
 stellt sicher, dass für die Spalte keine
Nullwerte akzeptiert werden
 Spalten ohne NOT NULL Constraint dürfen
standardmässig Nullwerte enthalten
ausgenommen Primary Key-Spalten
 NOT NULL Constraints werden auf
Spaltenebene definiert
UNIQUE-Constraint
 definiert, dass ein Wert für eine Spalte o.
Spaltenkombination nur einmal in der Tabelle für die
Spalte vorkommt
 UNIQUE-Schlüssel
 definiert auf Tabellen- o. Spaltenebene
 Erlauben Nullwerte, diese dürfen dann auch
mehrfach vorkommen, da sie keinem anderen Wert
entsprechen
Hinweis: Ein Nullwert in einer Spalte erfüllt stets ein
UNIQUE-Constraint
PRIMARY KEY-Constraint
 erstellt Primärschlüssel für die Tabelle
 es kann nur einen pro Tabelle geben
 ist eine Spalte oder Spaltengrupe, die jede Zeile
einer Tabelle eindeutig identifiziert
 setzt Eindeutigkeit der Spalte oder
Spaltenkombination durch und stellt sicher, dass
keine Spalte, die zu einem Primärschlüssel gehört,
einen Nullwert enthält
Implizit wird auf die Spalte o. Spaltenkombination ein
eindeutiger Index gesetzt.
FOREIGN KEY-Constraint
 FOREIGN KEY (o. referenzielle Integritäts-)




Constraints bestimmen eine Spalte oder
Spaltenkombination als Fremdschlüssel
richten eine Beziehung zwischen einem Primär- oder
UNIQUE-Schlüssel in derselben oder einer anderen
Tabelle ein
ein Fremdschlüsselwert muss einem vorhandenen
Wert in der übergeordneten Tabelle entsprechen oder
NULL sein
basieren auf Datenwerten und sind rein logische
(nicht physische) Zeiger
definiert auf Spalten- o. Tabellen-Ebene
FOREIGN KEY-Constraint II
 ein zusammengesetzter Fremdschlüssel muss mit Hilfe der
Definition auf Tabellenebene erstellt werden.
 ein Einspaltiger-Fremdschlüssel kann auf Spaltenebene
definiert werden.
CREATE TABLE employees (…
department_id
NUMBER(4) CONSTRAINT emp_depid_fk
REFERENCES departments (department_id));
oder
CREATE TABLE employees (…
department_id
NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments (department_id), … );
FOREIGN KEY-Constraint
Schlüsselwörter
 FOREING KEY definiert die Spalte der referenzierten Tabelle
(untergeordnete Tabelle) auf Tabellen Constraint-Ebene
 REFERENCES identifiziert die Tabelle und Spalte in der
übergeordneten Tabelle
 ON DELETE CASCADE löscht die abhängigen Zeilen aus der
untergeordneten Tabelle, wenn eine Zeile in der übergeordneten
Tabelle gelöscht wird
 ON DELETE SET NULL konvertiert abhängige Fremdschlüsselwerte in
Nullwerte
Hinweis, Richtung:
 Der Fremdschlüssel wird in der untergeordneten Tabelle definiert.
 Die Tabelle mit der referenzierten Spalte ist die übergeordnete Tabelle
 Ohne die Option ON DELETE CASCADE oder ON DELETE SET NULL
kann die Zeile in der übergeordneten Tabelle nicht gelöscht werden,
wenn diese in der untergeordneten Tabelle referenziert wird.
CHECK Constraint
 definiert eine Bedinung, die jede Zeile erfüllen muss
 nicht zulässig sind:
 Referenzen auf die Pseudospalten CURRVAL,
NEXTVAL, LEVEL und ROWNUM
 Aufrufe der Funktionen SYSDATE, UID, USER und
USERENV
 Abfragen, die andere Werte in andere Zeilen
referenzieren
…, salary NUMBER(2)
CONSTRAINT emp_salary_min CHECK (salary >
0),…
CREATE TABLE Beispiel
CREATE TABLE employee
(employee_id
NUMBER(6)
CONSTRAINT ep_employee_id PRIMARY KEY,
first_name
VARCHAR2(20),
last_name
VARCHAR2(25) CONSTRAINT ep_last_name_nn
NOT NULL,
email
VARCHAR2(25) CONSTRAINT ep_email_nn
NOT NULL,
CONSTRAINT ep_email_uk UNIQUE,
phone_number VARCHAR2(20),
hire_date
DATE
CONSTRAINT
job_id
VARCHAR2(10) CONSTRAINT
salary
NUMBER(8,2) CONSTRAINT
commission_pct NUMBER(2,2),
manager_id
NUMBER(6),
department_id NUMBER(4)
CONSTRAINT
departments (department_id)
);
ep_hire_date_nn NOT NULL,
ep_job_nn NOT NULL,
ep_sal_ch CHECK (salary > 0),
ep_dept_fk REFERENCES
Constraints verletzen
UPDATE employees
SET department_id = 55
WHERE department_id = 110;
Hinweis: department 55 existiert nicht!
UPDATE employees
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EP_DEPT_FK)
violated – parent key not found
Constraints verletzen II
DELETE FROM departments
WHERE department_id = 60;
Hinweis: In der Abteilung 60 arbeiten Angestellte (employees)
DELETE FROM departments
*
ERROR at line 1:
ORA-02292: integrity constraint
(HR.ep_dept_fk) violated – child record found
CREATE TABLE incl. Subquery
 Tabellen können auch anhand einer Unterabfrage
erstellt werden
Syntax
CREATE TABLE table
[(column, column…)] AS subquery
 die Anzahl der Spalten in der neuen Tabelle muss mit
der Projektionsklausel der Unteranweisung
übereinstimmen
 definieren von Spalten mit Spaltennamen und
Default-Werten
CREATE TABLE incl. Subquery
Richtlinien
 die Tabelle wird mit den angegebenen Spaltennamen erstellt
 die von der SELECT-Anweisung abgerufenen Zeilen werden in




die neue Tabelle eingefügt
die Spaltendefinition darf nur den Spaltennamen und DefaultWert enthalten
sind Spaltenspezifikationen angegeben, so muss die Anzahl der
Spalten der Anzahl der Spalten in der SELECT-Liste der
Unterabfrage entsprechen
sind keine Spaltenspezifikationen angegeben, so entsprechen
die Spaltennamen der Tabelle den Spaltennamen der
Unterabfrage
im Gegensatz zu den Datentypendefinitionen der Spalten
werden die Integritätsregeln nicht an die neue Tabelle
übergeben
CREATE TABLE incl. Subquery
Beispiel
CREATE TABLE dept80
AS
SELECT
employee_id,
last_name,
salary * 12 ANNSAL,
hire_date
FROM
employees
WHERE
department_id = 80;
Table created.
DESCRIBE dept80
Name
------------------EMPLOYEE_ID
LAST_NAME
ANNSAL
HIRE_DATE
Null
--------NOT NULL
NOT NULL
NOT NULL
Type
-------------------------NUMBER(6)
VARCHAR2(25)
NUMBER
DATE
ALTER TABLE
SQL-Schulung: Kapitel
ALTER TABLE-Anweisung
 neue Spalten zu einer Tabelle hinzufügen
 vorhandene Spalten einer Tabelle ändern
 Spalten aus einer Tabelle entfernen
 neue Constraints für eine Tabelle definieren
 Default-Werte für Spalten setzen o. löschen
Tabellen löschen
 alle Daten und die Struktur der Tabelle werden






gelöscht
alle noch nicht gespeicherten Transaktionen werden
festgeschrieben (committed)
alle Indizes werden gelöscht
alle Constraints werden gelöscht
Views und Synonyme bleiben erhalten, sind jedoch
ungültig
kein ROLLBACK !
nur der Ersteller der Tabelle oder ein Benutzer mit
dem Privileg DROP ANY TABLE kann die Tabelle
entfernen !
Views
SQL-Schulung: Kapitel 13
VIEW
 eine View ist eine logische Tabelle, die auf einer
Tabelle oder einer anderen View basiert
 eine View enthält selber keine Daten!
 Grundlegende Tabellen: Basistabellen
 wird im Data Dictionary als SELECT-Anweisung
gespeichert




Datenzugriff beschränken
verschiedene Ansichten derselben Daten
vereinfachen von komplexen Abfragen
Datenunabhängigkeit
Einfache und komplexe Views
 einfache View:
 leitet Daten aus nur einer Tabelle ab
 enthält keine Funktionen oder Datengruppen
 kann DML-Operationen über die View
ausführen
 komplexe View:
 leitet Daten aus mehreren Tabellen ab
 enhält Funktionen oder Datengrupen
 lässt nicht immer DML-Operationen über die
View zu
CREATE VIEW-Anweisung
Syntax:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[,
alias]…)] AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]]
Schlüsselwort
Bedeutung
OR REPLACE
erstellt die View, auch wenn sie schon vorhanden ist
FORCE
Erstellt die View, auch wenn Basistabellen nicht existieren
NOFORCE
Erstellt die View nur, wenn die Basistabellen existieren
view / constraint
Name der View bzw. Name des jeweiligen Contraints
Alias
Gibt Namen für die Ausdrücke an, die von der Abfrage der View ausgewählt
werden
subquery
Eine vollständige Select-Anweisung
WITH CHECK OPTION
es werden nur Zeilen eingefügt /aktualisiert, für welche die View berechtigt ist
WITH READ ONLY
es können keine DML-Operationen an dieser View ausgeführt werden
Views erstellen Richtlinien
 die Unterabfrage, die eine View definiert, kann
komplexe SELECT-Syntax, einschliesslich Joins,
Gruppen und Unterabfragen, enthalten
 werden keine Constraint-Namen vergeben, so
generiert das DBMS einen Namen
Oracle: SYS_Cn
 die Option OR REPLACE kann verwendet werden,
um die Definition einer View zu verändern, ohne sie
zuvor zu löschen und neu zu erstellen oder die zuvor
erteilten Objektprivilegien neu zu erteilen.
CREATE VIEW Beispiele
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
View created.
CREATE VIEW salvu50
AS SELECT employee_id ID_NUMBER, last_name
NAME, salary*12 ANN_SALARY
FROM employees
WHERE department_id = 50;
View created.
Views auslesen
 Daten aus Views werden genauso ausgelesen wie
aus einer Tabelle
 in der FROM-Klausel wird der Viewname angegeben
 können mit Tabellen verjoint werden
 können in beliebig komplexen SELECT-Anweisungen
eingebunden sein
Beispiele:
SELECT *
FROM salvu50;
SELECT last_name
FROM empvu50
WHERE salary > 6000;
komplexe View Beispiel
CREATE VIEW dept_num_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary), AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
DML-Operationen an Views ausführen
 DML-Operationen können i.d. Regel an allen
einfachen Views ausgeführt werden
 es können keine Zeilen entfernt werden,
wenn die View eines der folgenden Elemente
enthält:




Aggregatfunktionen
GROUP BY-Klausel
Schlüsselwort DISTINCT
Pseudospalten-Schlüsselwort ROWNUM
DML-Operationen an Views ausführen
 DML-Operationen können i.d. Regel an allen
einfachen Views ausgeführt werden
 es können keine Zeilen geändert werden,
wenn die View eines der folgenden Elemente
enthält:





Aggregatfunktionen
GROUP BY-Klausel
Schlüsselwort DISTINCT
Pseudospalten-Schlüsselwort ROWNUM
Spalten, die durch Ausdrücke definiert sind
DML-Operationen an Views ausführen
 DML-Operationen können i.d. Regel an allen
einfachen Views ausgeführt werden
 es können keine Zeilen hinzugefügt werden, wenn
die View eines der folgenden Elemente enthält:






Aggregatfunktionen
GROUP BY-Klausel
Schlüsselwort DISTINCT
Pseudospalten-Schlüsselwort ROWNUM
Spalten, die durch Ausdrücke definiert sind
NOT NULL-Spalten in den Basistabellen, die nicht für
die View selektiert wurden
WITH CHECK OPTION
 mit der Klausel wird sichergestellt, dass an einer View
ausgeführte DML-Operationen innerhalb der Domäne der View
bleiben
Beispiel:
CREATE OR REPLACE VIEW empvu20
AS SELECT
FROM employees
WHERE department_id 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;
Der Versuch, die Abteilungsnummer für eine Zeile in der View zu
ändern, scheitert, da er gegen das WITH CHECK OPTIONConstraint verstösst.
DML-Operationen verweigern
 durch WITH READ ONLY werden alle DML-
Operationen verhindert
 Jeder Versuch, eine DML-Operation an der View
abzusetzen führt zu einer Fehlermeldung
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 10
WITH READ ONLY;
Views entfernen
DROP VIEW view;
Name der View
DROP VIEW empvu80;
View dropped.
 eine View wird ohne Datenverluste entfernt
 nur der Ersteller oder ein Benutzer mit dem
Privileg DROP ANY VIEW kann eine View
löschen
Sequences
SQL-Schulung: Kapitel 14
Sequences
 eine Sequence ist ein Datenbankobjekt, das






Ganzzahlwerte erstellt
kann automatisch eindeutige Nummern generieren
ist ein gemeinsam verwendbares Objekt
kann verwendet werden um einen Primärschlüssel zu
erstellen
ersetzt Anwendungs-Code
beschleunigt den Zugriff auf Sequence-Werte wenn
sie im Cache gespeichert sind
eine Sequence ist nicht an eine Tabelle gebunden!
CREATE SEQUENCE- Anweisung
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
CREATE SEQUENCE SYNTAX
Schlüsselwort
Bedeutung
sequence
Steht für den Namen des Sequence-Generators
INCREMENT BY n
Gibt das Intervall zwischen Sequencenummern an,
fehlt die Klausel wird 1 als Intervall verwendet
START WITH n
Gibt die erste zu generierende Sequence-Nummer
an. Fehlt die Klausel wird mit 1 begonnen.
MAXVALUE n
Gibt den höchsten Wert an, den die Sequence
generieren kann
NOMAXVALUE
Legt einen Höchstwert von 1027 für die aufsteigende
Sequence und -1 für die absteigende Sequence fest
MINVALUE n
Gibt den kleinsten Wert der Sequence an
NOMINVALUE
Legt einen kleinsten Wert von 1 für eine
aufsteigende Sequence und -1026 für die absteigene
Sequence fest
SEQUENCES Beispiel
 für eine Primärschlüssel Sequence
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
Sequence created.
Pseudospalten NEXTVAL und
CURRVAL




NEXTVAL gibt den nächsten verfügbaren Sequence-Wert zurück. Bei jeder
Referenzierung wird ein eindeutiger Wert zurückgegeben, selbst bei
verschiedenen Benutzern
CURRVAL ruft den aktuellen Sequence-Wert ab
NEXTVAL muss für die Sequence ausgegeben werden, bevor CURRVAL einen
Wert enthält
können verwendet werden in:





SELECT-Liste einer SELECT-Anweisung, die nicht Bestandteil einer
Unterabfrage ist
SELECT-Liste einer Unterabfrage in einer INSERT-Anweisung
VALUES-Klausel einer INSERT-Anweisung
SET-Klausel einer UPDATE-Anweisung
können nicht verwendet werden in:





SELECT-Liste einer View
SELECT-Anweisung mit dem Schlüsselwort DISTINCT
SELECT-Anweisung mit den Klausel GROUP BY, HAVING oder ORDER BY
Unterabfrage in einer SELECT-, DELETE- oder UPDATE-Anweisung
DEFAULT Ausdruck in einer CREATE TABLE- oder ALTER TABLE-Anweisung
Sequence verwenden
SELECT dept_deptid_seq.CURRVAL
FROM dual;
SELECT dept_deptid_seq.NEXTVAL
FROM dual;
Sequence-Werte im Cache
 das Caching von Sequence-Werten
ermöglicht einen schnelleren Zugriff auf die
Werte
 Lücken zwischen den Sequence-Werten
können in folgenden Fällen auftreten



Bei einem ROLLBACK
Bei einem Systemausfall
wenn eine Sequence von mehreren Tabellen
verwendet wird
SEQUENCES ändern
ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
Sequences ändern Richtlinien
 nur der Eigentümer oder ein Benutzer mit dem ALTER-Privileg




darf Änderungen vornehmen
die Änderung wirkt sich nur auf zukünftige Sequence-Nummern
aus
die Sequence muss gelöscht und neu erstellt werden, wenn der
Startwert o. aktuelle Wert neu gesetzt werden soll
bestimmte Validierungsmassnahmen werden durchgeführt, so
kann kein kleiner Max.Value gesetzt werden.
das Entfernen einer Sequence erfolgt mit der DROPAnweisung:
DROP SEQUENCE sequence;
Sequence dropped.
Indizes
SQL-Schulung: Kapitel 15
Indizes
 ein Index ist ein Schemaobjekt
 wird vom Server verwendet, um den Abruf von Zeilen





mit Hilfe eines Zeigers zu beschleunigen
kann I/O-Vorgänge mit Hilfe einer beschleunigten
Pfadzugriffsmethode reduzieren, die Daten schneller
zu finden
ist unabhängig von der indizierten Tabelle
aber…wird eine Tabelle gelöscht, so werden auch die
zugehörigen Indizes gelöscht
wird automatisch verwendet und verwaltet
Ohne Indizes → Full Table scan
Indexerstellung - Verfahren
 automatisch:
ein eindeutiger Index wird automatisch erstellt, wenn
ein PRIMARY KEY- oder UNIQUE-Constraint in einer
Tabellendefinition verwendet wird
 manuell:
vom Benutzer explizit angelegte nicht-eindeutige
Indizes, welche den Zeilenzugriff beschleunigen
z.B. bei Foreign Key Beziehungen.
Hinweis: ein eindeutiger Index kann auch manuell
erstellt werden, es ist jedoch nicht ratsam, da hierfür
ein UNIQUE-Constraint verwendet werden muss,
welches dann implizit einen eindeutigen Index
erstellt.
Indizes erstellen
CREATE INDEX index
ON table (column [, column]…);
index
table
column
Name des Index
Name der Tabelle
Tabellenspalte, die indiziert werden soll
Beispiel für einen Index auf last_name:
CREATE INDEX emp_last_name_idx
ON employees(last_name);
Index created.
Index erstellen - Richtlinien
Ein Index sollte in folgenden Fällen erstellt werden, wenn:
 eine Spalte viele verschiedene Werte hat
 eine Spalte viele Nullwerte enthält
 eine oder mehrere Spalten häufig gemeinsam in einer
WHERE-Klausel oder Join-Bedingung verwendet werden
Es sollte kein Index erstellt werden, wenn:
 die Spalten nur selten als Bedingung in Abfragen verwendet
werden
 die Tabelle nur klein ist und die meisten Abfragen
wahrscheinlich mehr als 2 bis 4 Prozent der Zeilen in der
Tabelle abrufen
 die Tabelle häufig aktualisiert wird
 die indizierten Spalten als Bestandteil eines Ausdrucks
referenziert werden
Index erstellen – Richtlinien II
Achtung:
 es sollten nicht zuviele Indizes für eine
Tabelle angelegt werden
 bei jeder DML-Operation, müssen die Indizes
aktualisiert werden
 je mehr Indizes mit einer Tabelle verknüpft
sind, desto grösser ist der Aufwand für den
DB-Server bei der Aktualisierung aller Indizes
Indizes entfernen
DROP INDEX index;
 entfernt einen Index aus dem Data Dictionary
 dies hat keine Auswirkungen auf die sonstige
Tabellenstruktur oder die Daten
 Indizes können nicht geändert sondern nur gelöscht
und neu angelegt werden
 man muss Eigentümer sein, oder das Privileg DROP
ANY INDEX besitzen, um einen Index zu löschen
 Wird eine Tabelle gelöscht, so werden auch die
zugehörigen Indizes gelöscht
Synonyme
SQL-Schulung: Kapitel 16
Synonyme
 sind Alias-Namen um Datenbankobjekten einen alternativen
Namen zu geben
 vereinfacht Zugriff
 einfache Referenzen auf Tabellen anderer Benutzer
 kürzen von langen Objektnamen
CREATE [PUBLIC] SYNONYM synonym
FOR object;
PUBLIC
erstellt ein Synonym, das allen Benutzern zugänglich ist
synonym steht für den Namen des zu erstellenden Synonyms
object
bezeichnet das Objekt, für welches das Synonym
erstellt wird
 Das Objekt darf nicht in einem Package enthalten sein
 der Name eines privaten (private) Synonyms muss sich von
allen anderen Objekten des Benutzers unterscheiden
Synonyme erstellen u. entfernen
CREATE SYNONYM d_sum
FOR dept_sum_vu;
Synonym created.
DROP SYNONYM d_sum;
Synonym dropped.
CREATE PUBLIC SYNONYM dept
FOR alice.departments;
Synonym created.
DROP PUBLIC SYNONYM dept;
Synonym dropped.
Data Dictionary
SQL-Schulung: Kapitel 17
Data Dictionary
 speichert Informationen zur Datenbank
 ist genau wie andere Datenbankdaten in Tabellen
und Views strukturiert
 ist schreibgeschützt
 Zugriff mit SQL
Views
 Definitionen aller Schemaobjekte in der Datenbank
 Default-Werte für Spalten
 Integritäts-Constraint-Informationen
 Namen von Oracle-Benutzern
 Privilegien und Rollen der einzelnen Benutzer
 andere allgemeine Datenbankinformationen
Data Dictionary-Struktur
View-Präfix
Zweck
USER_
View des Benutzers (Inhalt des Schemas,
Elemente, Eigentümer)
ALL_
Erweiterte View des Benutzers (Objekte, auf die
der Benutzer zugreifen kann)
DBA_
View des Datenbankadministrators
V$
Performance-bezogene Daten
 USER_OBJECTS
z.B.: OBJECT_NAME, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS, GENERATED
 ALL_OBJECTS
USER-Views
View
Bedeutung
USER_OBJECTS
Alle Schemabezogenen Objekte
USER_TABLES
Tabelleninformationen
USER_TAB_COLUMNS
Spalteninformationen
USER_CONSTRAINTS
Constraintinformationen
USER_CONS_COLUMNS Spalten-Constraintzuordnung
USER_VIEWS
View-Informationen incl. SelectStatement
USER_SEQUENCES
Sequence-Information
USER_SYNONYMS
Synonym-Information
COMMENT-Anweisung
 Kommentare setzen in Tabellen, Spalten, Views
 → 2000 Byte max.
 Kommentare anzeigen lassen:
 ALL_COL_COMMENTS
 USER_COL_COMMENTS
 ALL_TAB_COMMENTS
 USER_TAB_COMMENTS
 Kommentare hinzufügen
COMMENT ON [TABLE table | COLUMN
table.column ] IS 'text';