SQL - WI-Labor - Hochschule Fulda
Werbung
Lecture notes
Propaedeutic Programme (SQL)
Summer semester 2009
Christian Pape
11. March 2009
1 / 197
Introduction
Contents
I
Basic relational operators
I
I
I
I
I
I
I
I
I
Union
Intersection
Subtraction
Symmetric subtraction
Cartesian product
Equi join
Natural join
Outer join
SQL
I
I
I
I
I
I
Database infrastructure
Syntax of a SELECT statement
Functions
Subqueries
Grouping
Aggregate functions
2 / 197
Introduction
Contents (2)
I
SQL (Continued)
I
I
I
I
I
I
I
I
I
I
I
Searching
Predicates
NULL-value
Joins
Manipulation of data
Creating tables
Foreign keys
Integrity
Views
Application development
Data control
I
I
I
Trigger
Access control
Transactions
3 / 197
Introduction
Informations
Informations lecture/exercises:
http://wi-labor.informatik.hs-fulda.de/pp
4 / 197
Infrastructure
Database servers
I
mtsthelens
I
krakatau
I
pinatubo
5 / 197
Infrastructure
mtsthelens.informatik.hs-fulda.de
Platform: Sun Fire V445
Operating system: Solaris 10
Database: Oracle 10g
SID: ORALV9A
Capacity: ca. 584 GB
6 / 197
Infrastructure
krakatau.informatik.hs-fulda.de
Platform: FSC TX200
Operating system: Solaris 10 x86
Database: Oracle 10g
SID: ORALV10A
Capacity: ca. 140 GB
7 / 197
Infrastructure
pinatubo.informatik.hs-fulda.de
Platform: FSC TX200
Operating system: Solaris 10 x86
Database: Oracle 10g
SID: ORALV8A
Capacity: ca. 140 GB
8 / 197
Infrastructure
How to access the databases
I
Oracle SQL Developer (Windows, Unix, Mac PPC + Intel)
I
iSQL*Plus (browser-based access)
9 / 197
Infrastructure
Oracle SQL Developer
I
Also available for
Mac OS X
10 / 197
Infrastructure
Oracle iSQL*Plus
I
Browser-based access to the
databases
URL:
http://isqlplus.informatik.hs-fulda.de
11 / 197
Basic relational operators
Union
R
A
a
d
b
B
x
y
z
C
1
2
3
R ∪S
S
A
b
d
B
x
y
C
3
2
A
a
d
b
b
B
x
y
z
x
C
1
2
3
3
12 / 197
Basic relational operators
Intersection
R
A
a
d
b
B
x
y
z
C
1
2
3
S
A
b
d
B
x
y
C
3
2
R ∩S
A
d
B
y
C
2
13 / 197
Basic relational operators
Subtraction
R
A
a
d
b
B
x
y
z
C
1
2
3
S
A
b
d
B
x
y
C
3
2
R −S
A
a
b
B
x
z
C
1
3
14 / 197
Basic relational operators
Symmetric subtraction
R
A
a
d
b
B
x
y
z
C
1
2
3
S
A
b
d
B
x
y
C
3
2
R4S
A
a
b
b
B
x
z
x
C
1
3
3
15 / 197
Basic relational operators
Cartesian product
R
A
a
d
b
B
x
y
z
C
1
2
3
S
D
b
d
E
x
y
F
3
2
R ×S
A
a
a
d
d
b
b
B
x
x
y
y
z
z
C
1
1
2
2
3
3
D
b
d
b
d
b
d
E
x
y
x
y
x
y
F
3
2
3
2
3
2
16 / 197
Basic relational operators
Equi join
R
A
a1
a2
B
b1
b2
C
c1
c2
S
D
c1
c3
E
d1
d2
F
e1
e2
R C =D S
A
a1
B
b1
C
c1
D
c1
E
d1
F
e1
17 / 197
Basic relational operators
Natural join
R
A
a1
a2
B
b1
b2
C
c1
c2
S
C
c1
c3
D
d1
d2
E
e1
e2
R S
A
a1
B
b1
C
c1
D
d1
E
e1
18 / 197
Basic relational operators
Outer join
R
A
a1
a2
B
b1
b2
C
c1
c2
S
C
c1
c3
D
d1
d2
E
e1
e2
R][S
A
a1
a2
≡
B
b1
b2
≡
C
c1
c2
c3
D
d1
≡
d2
E
e1
≡
e2
19 / 197
Basic relational operators
Left outer join
R
A
a1
a2
B
b1
b2
C
c1
c2
S
C
c1
c3
D
d1
d2
E
e1
e2
R] S
A
a1
a2
B
b1
b2
C
c1
c2
D
d1
≡
E
e1
≡
20 / 197
Basic relational operators
Right outer join
R
A
a1
a2
B
b1
b2
C
c1
c2
S
C
c1
c3
D
d1
d2
E
e1
e2
R [S
A
a1
≡
B
b1
≡
C
c1
c3
D
d1
d2
E
e1
e2
21 / 197
SQL
SELECT
SelectExpression::=
SELECT [ALL|DISTINCT] SelectItemCommaList
FROM TableReferenceCommalist
[WHERE ConditionExpression]
[GROUP BY ColumnReferenceCommaList
[HAVING ConditionExpression]]
[ORDER BY ColumnReferenceCommaList]
22 / 197
SQL
SELECT
SelectExpression
SELECT ALL DISTINCT TableReferenceCommaList
GROUP
ORDER
BY
BY
SelectItemCommaList
FROM WHERE ColumnReferenceCommaList
ColumnReferenceCommaList
ConditionExpression
HAVING ConditionExpression
23 / 197
SQL
SELECT (2)
I
SELECT * returns complete tuples
I
FROM-clause specifies the object (relation, view) for which
tuples should be processed by SELECT
I
WHERE-clause can include predicates connected by AND or OR
I
Predicates can be composed with:
Ai θan ,θ ∈ {=, ! =, <, <=, >, >=}
Ai θAj ,θ ∈ {=, ! =, <, <=, >, >=}
24 / 197
SQL
SELECT *
Find all poets (with all attributes):
SELECT * FROM POET
AUTHOR
Schiller
Goethe
Kleist
Lessing
B_PLACE
Marbach
Frankfurt
Kamenz
Frankfurt
B_YEAR
1759
1749
1777
1729
25 / 197
SQL
SELECT cartesian product
Find all combinations of poets and dramas with all attributes (i.e.
cartesian product):
SELECT * FROM POET, DRAMA
AUTHOR
Schiller
Goethe
Kleist
Lessing
Schiller
Goethe
Kleist
Lessing
Schiller
Goethe
..
.
B_PLACE
Marbach
Frankfurt
Kamenz
Frankfurt
Marbach
Frankfurt
Kamenz
Frankfurt
Marbach
Frankfurt
..
.
B_YEAR
1759
1749
1777
1729
1759
1749
1777
1729
1759
1749
..
.
TITLE
Maria Stuart
Maria Stuart
Maria Stuart
Maria Stuart
Wallenstein
Wallenstein
Wallenstein
Wallenstein
Tell
Tell
..
.
P_PLACE
Weimar
Weimar
Weimar
Weimar
Jena
Jena
Jena
Jena
Weimar
Weimar
..
.
P_YEAR
1800
1800
1800
1800
1799
1799
1799
1799
1804
1804
..
.
AUTHOR
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
..
.
26 / 197
SQL
WHERE-clause
Which dramas (TITLE) were not premiered in Weimar nor
Frankfurt?
SELECT TITLE FROM DRAMA WHERE
P_PLACE!=’Weimar’ AND P_PLACE!=’Frankfurt’
. . . oder auch:
SELECT TITEL FROM DRAMA WHERE NOT
(P_PLACE=’Weimar’ OR P_PLACE=’Frankfurt’)
TITLE
Wallenstein
Iphigenie
Nathan
27 / 197
SQL
SELECT example
Which dramas of Schiller were premiered after 1800?
SELECT TITLE FROM DRAMA WHERE
AUTHOR=’Schiller’ AND P_YEAR>1800
TITLE
Tell
28 / 197
SQL
SELECT DISTINCT
Which actors (PNR) had perfomed at the Schloßtheater? Each
actor should only appear once!
SELECT DISTINCT PNR FROM PERFORMER WHERE
THEATRE=’Schloßtheater’
PNR
2
I by default no duplicates will be eliminated
I DISTINCT forces elimination of duplicates
29 / 197
SQL
SELECT naming of result columns
I
Output of attributes, text or expressions via SELECT
I
Columns of the results can be renamed
I
Column names are case-sensitive when enclosed by quotes
("Ort" 6= "ORT")
SELECT TITLE AS "Title", ’premiered in:’ AS
TEXT, P_PLACE AS "Place", ’in the year:’ AS
TEXT, P_YEAR-1800 AS EXPRESSION, ’after 1800’
AS TEXT FROM DRAMA WHERE AUTHOR=’Schiller’
Titel
Maria Stuart
Wallenstein
Tell
TEXT
premiered in:
premiered in:
premiered in:
Place
Weimar
Jena
Weimar
TEXT
in the year:
in the year:
in the year:
EXPRESSION
0
-1
4
TEXT
after 1800
after 1800
after 1800
30 / 197
SQL
SELECT naming of result columns
I
Concatenation of attributes and text
SELECT TITLE || ’ was premiered in ’ ||
P_PLACE || ’ in the year ’ || P_YEAR || ’.’
AS "Sentence" FROM DRAMA WHERE
AUTHOR=’Schiller’
Sentence
Maria Stuart was premiered in Weimar in the year 1800.
Wallenstein was premiered in Jena in the year 1799.
Tell was premiered in Weimar in the year 1804.
31 / 197
SQL
Functions
Which dramas (TITLE, P_YEAR) were premiered at the begin of a
century?
SELECT TITLE, P_YEAR FROM DRAMA WHERE
MOD(P_YEAR,10)=0
. . . or using the TRUNC-Function:
SELECT TITLE, P_YEAR FROM DRAMA WHERE
P_YEAR=TRUNC(P_YEAR/10)*10
. . . or using the FLOOR-Function:
SELECT TITLE, P_YEAR FROM DRAMA WHERE
P_YEAR=FLOOR(P_YEAR/10)*10
TITLE
Maria Stuart
Faust
P_YEAR
1800
1790
32 / 197
SQL
Arithmetic functions
Function
ABS(n)
Description
Absolute value
CEIL(n)
smallest integer greater or equal n
COS(n)
COSH(n)
EXP(n)
Cosine
Cosine hyperbolicus
expontential function (Euler’s constant e)
FLOOR(n)
biggest integer value lower or equal to n
LN(n)
Natural logarithm
LOG(m,n)
Logarithm (base m)
MOD(n,m)
POWER(n,m)
Modulo (m) function, rest of integer division
Raise n to the power of m
ROUND(m[,m])
Rounds (m decimal digits)
SIGN(N)
Signum
SIN(n)
SINH(n)
SQRT(n)
TAN(n)
TANH(n)
TRUNC(n[,m])
Sinus
Sinus hyperbolicus
Square root of n
Tangent
Tanget hyperbolicus
Truncate (m decimal digits)
Examples
ABS(-5)
ABS(33)
CEIL(-3.8)
CEIL(4.5)
COS(0)
COS(3.141592)
EXP(1)
EXP(-1)
FLOOR(-3.8)
FLOOR(-2)
LN(1)
LN(EXP(1))
LOG(5,125)
LOG(2,1024)
MOD(8,5)
MOD(10,3)
POWER(4,3)
POWER(12,2)
ROUND(3.75)
ROUND(3.75,1)
ROUND(-3.759
ROUND(-3.75,1)
ROUND(4771.3,2)
SIGN(3)
SIGN(0)
SIGN(-2)
SIN(0)
SINH(1.570796)
SQRT(36)
TAN(0)
TAN(0.785398163)
TRUNC(12.2082)
TRUNC(12.2082,2)
TRUNC(1209,-2)
Results
5
33
-3
5
1
-1
2.7182818
0.36787944
-4
-2
0
1
3
10
3
1
64
144
4
3.8
-4
-3.8
4800
1
0
-1
0
1
6
0
1
12
12.2
1200
33 / 197
SQL
String-functions
Function
ASCII(c)
CHR(n)
CONCAT(str1,str2)
INITCAP(str)
INSTR(str1,str2)
LENGTH(str)
LOWER(str)
LPAD(str,n[,padstr])
LTRIM(str[,trimset])
Description
Decimal
representation of an ASCII
character
ASCII character with
decimal code n
Concatenation of two
strings
Making first charcacter of each word uppercase
Position of string str2
in str1
Length of string in
characters
lowercase representatrion of string str
fills string with padstr
to the left until maximum length n reached
Removes
all
occurences of characters
given by trimset from
the begin of the string
Examples
ASCII(’7’)
ASCII(’N’)
Results
55
78
CHR(55)
CHR(78)
CONCAT(’SQL’,’-Standard’)
CONCAT(’SQL’,CONCAT(’’,’Standard’))
INITCAP(’Aller Anfang ist schwer’)
INITCAP(’Aller*Anfang;ist+schwer’)
’7’
’N’
’SQL-Standard’
’SQL-Standard’
INSTR(’Pineapple’,’ea’)
INSTR(’Pineapple’,’P’)
INSTR(’bubblegum’,’BB’)
LENGTH(’potato’)
LENGTH(’bubblegum’)
LOWER(’SMALL
IS
BEAUTIFUL’)
LPAD(’Pad’,10,’_#’)
LPAD(’Pad’,10)
4
1
0
LTRIM(’bubblegum’,’b’)
LTRIM(’bubblegum’,’bu’)
’ubblegum’
’legum’
’Aller Anfang Ist Schwer’
’Aller*Anfang;Ist+Schwer’
6
9
’small is beautiful’
’_#_#_#_Pad’
’
Pad’
34 / 197
SQL
String-functions (2)
Function
REPLACE(str,
search-str[,replstr])
Description
Search and replace all
occurrences of searchstr in str with repl-str,
if repl-str is not set
remove all occurrences
of search-str
RPAD(str,n[,padstr]) fills string with padstr
to the right until maximum length n reached
RTRIM(str[,trimset]) Removes
all
occurences of characters
given by trimset from
the end of the string
SOUNDEX(str)
Phonetic
represenation of the string, used
for searching different
spellings
SUBSTR(str,m[,n])
Returns a substring of
SUBSTRB(str,m[,n]) str beginning with the
m-th character and
the length n
TRANSLATE(str,from-translation character
set,to-set)
by character;
if a
character c of str is
found on the i-th position in the from-set,
the character will be
replaced by the i-th
character of the to-set
UPPER(str)
Uppercase
represantion of a string
Examples
REPLACE(’banana’,’an’)
REPLACE(’banana’,’an’,’el’)
Results
’ba’
’belela’
RPAD(’Pad’,10,’_#’)
RPAD(’Pad’,10)
’Pad_#_#_#_’
’Pad
’
RTRIM(’Risotto’,’o’)
RTRIM(’Risotto’,’ot’)
’Risott’
’Ris’
SOUNDEX(’trick’)
SOUNDEX(’trik’)
SOUNDEX(’trikk’)
’T620’
’T620’
’T620’
SUBSTR(’Pineapple’,5)
SUBSTR(’Pineapple’1,4)
SUBSTR(’Pineapple’,-4)
SUBSTR(’Pineapple’,-6,2)
TRANSLATE(’xkhxcecbkm’,
’ehxvkc’, ’lmsbia’)
’apple’
’Pine’
’pple’
’ea’
UPPER(’the big apple’)
UPPER(’the<big>apple’)
’THE BIG APPLE’
’THE<BIG>APPLE’
’simsalabim’
35 / 197
SQL
Date- and time-functions
Datatype(s) for storing date and time values
I
Oracle uses only one datatype DATE for date and time values
I
Standard SQL defines DATE for a date, TIME for a time value
and TIMESTAMP
I
the Oracle datentyp DATE is quite similar to TIMESTAMP in
standard SQL
Converting and formatting:
Time-value → String:
TO_CHAR(time, format-string)
String → Time-value:
TO_DATE(string, format-string)
36 / 197
SQL
Date- and time-functions (2)
format-element
-,/;:
"text"
AD
A.D.
AM
A.M.
BC
B.C.
CC
SCC
DAY
D
DD
DDD
DY
IW
IYY
IY
I
IYYY
HH
HH12
Description
inserts text
Examples
MM". Monat"
Results
9. Monat
AD indicator (anno domini - after Chr.)
YYYYAD
YYYYA.D.
HH:MIAM
HH:MIA.M.
YYYYBC
YYYYB.C.
CC". century"
1995AD
1995A.D.
10:30AM
10:30A.M.
1995BC
1995B.C.
20. century
DAY
D
DD
DDD
DY
IW". week of "YYYY
IYY
IY
I
IYYY
HH
HH12
FRIDAY
6
01
244
FRI
35. week of 95
995
95
5
1995
10
2
ante meridian - forenoon)
BC indicator (before Chr.)
Century with ’-’ for B.C..
Day (word)
Day of week(1-7
Day of month (1-31)
Day of year (1-366)
3-character abbrevation
Week of year (ISO)
last 3,2 or 1 digits of the ISO-year
ISO-year
Hour of day (1-12)
37 / 197
SQL
Date- and time-functions (3)
format-element
HH24
Description
Hour odf day (0-23)
Examples
HH24
J
MI
MM
MONTH
MON
RM
Q
W
WW
PM
P.M.
SS
SSSSS
YEAR
SYEAR
YYYY
SYYYY
YYY
YY
Y
julian day (by 01.01.4712 before Chr.)
Minute (0-59)
Month (1-12)
Monat (word)
3-character abbrevation
Month (roman numerals)
Quarter (1-4)
Week of month
Week of year
PM indicator (post meridian - afternoon)
J" julian day"
HH24:MI
DD-MM-YY
MONTH
DD-MON-YY
DD-RM-YY
Q". quarter "YYYY
W". week in"MON
WW
HH:MIPM
HH:MIP.M.
SS
SSSSS
YEAR
Second (0-59)
Second of day (0-86399)
Year (word, b.Chr. with ’-’)
Jahreszahl
Year (b.Chr. with ’-’)
last 3, 2 or 1 digits of the year
YYYY
SYYYY
YYY
YY
Y
Results
10
14
2449962 julian day
10:30
01-09-95
SEPTEMBER
01-SEP-95
01-IX-95
3. quarter 1995
1. week in SEP
35
10:30PM
10:30P.M.
00
37800
NINETEEN
NINETY-FIVE
1995
1995
995
95
5
38 / 197
SQL
Examples date- and time-functions
Find projects which number is lower or equal to 4. The output
should contain number, project start and the weekly meeting.
SELECT JNR, JSTART,
TO_CHAR(JMEETING,’HH24:MI’) AS JMEETING FROM
PROJ WHERE JNR<=4
JNR
1
2
3
4
JSTART
01-SEP-95
01-FEB-94
05-JUN-96
01-JAN-94
JMEETING
10:30
11:00
9:00
39 / 197
SQL
Examples date- and time-functions (2)
Which projects ended before 30.09.1995? Project number, name
and project end should be displayed. The project end should be
displayed as Day of week (word), hyphen, Day (number), dot,
month (number), dot, year (4-digits).
SELECT JNR "Projectnumber", JNAME
"Projectname", TO_CHAR(JEND,’DAY-DD.MM.YYYY’)
"Projectend" FROM PROJ WHERE JEND <
TO_DATE(’30.09.1995’,’DD.MM.YYYY’)
Projectnumber
4
Projectname
ABC-Analyse
Projectende
TUESDAY-20.06.1995
40 / 197
SQL
Comparing date- and time-values
I
String (e.g. ’30.09.1995’) must be converted to time-values
before comparing.
I
A comparison JEND < ’30.09.1995’ returns a wrong result
because strings (CHAR, VARCHAR) will be compared in an
alphanumeric order.
41 / 197
SQL
Checking membership of a set
The predicate IN can be used in a WHERE-clause to check wether an
attribute is member of a set.
I
Ai IN (a1 , . . . , ak )
I
Ai IN (SELECT ...)
Find all actors (PNR, FIGUR) who played Faust, Hamlet or
Wallenstein.
SELECT PNR, CHARACTER FROM PERFOMER WHERE
CHARACTER IN (’Faust’,’Hamlet’,’Wallenstein’)
PNR
1
1
2
CHARACTER
Faust
Wallenstein
Faust
42 / 197
SQL
Checking membership of a set (2)
Find the characters who appear in dramas of Schiller or Goethe.
SELECT CHARACTER FROM ROLE WHERE TITLE IN
(SELECT TITLE FROM DRAMA WHERE AUTHOR IN
(’Goethe’,’Schiller’))
CHARACTER
Faust
Mephisto
Gretchen
Wallenstein
Piccolomini
Tell
Geßler
Maria Stuart
Elisabeth
Iphigenie
43 / 197
SQL
Checking membership of a set (3)
Find actors (PNR) who never performed.
SELECT PNR FROM ACTOR WHERE PNR NOT IN (SELECT
PNR FROM PERFOMER)
PNR
3
6
7
8
44 / 197
SQL
Symmetric notation (realising joins)
Find actors (NAME) from Berlin and the theatres they performed
at.
SELECT NAME, THEATRE
FROM ACTOR, PERFORMER WHERE
ACTOR.PNR=PERFORMER.PNR
AND R_PLACE=’Berlin’
NAME
George
George
THEATRE
Theater des Westens
Pfalztheater
Full qualified statement:
SELECT ACTOR.NAME, PERFORMER.THEATRE FROM
ACTOR, PERFORMER WHERE ACTOR.PNR=PERFORMER.PNR
AND ACTOR.R_PLACE=’Berlin’
45 / 197
SQL
Symmetric notation (tuple variables)
Find the characters and their authors of dramas written by Schiller
or Goethe.
SELECT CHARACTER, AUTHOR FROM ROLE R, DRAMA D
WHERE R.TITLE=D.TITLE AND AUTHOR IN
(’Goethe’,’Schiller’)
CHARACTER
Faust
Mephisto
Gretchen
Wallenstein
Piccolomini
Tell
Geßler
Maria Stuart
Elisabeth
Iphigenie
AUTHOR
Goethe
Goethe
Goethe
Schiller
Schiller
Schiller
Schiller
Schiller
Schiller
Goethe
46 / 197
SQL
Symmetric notation example
Find all rogues from dramas whos authors were not born in
Frankfurt. Output the character, the year of premiere, the author
and his year of birth.
SELECT R.CHARACTER, D.P_YEAR, A.AUTHOR,
A.B_YEAR FROM ROLE R, DRAMA D, POET A
WHERE R.TITLE=D.TITLE
AND A.AUTHOR=D.AUTHOR
AND A.B_PLACE<>’Frankfurt’
AND R.R_TYPE=’Rogue’
CHARACTER
Piccolomini
Geßler
Elisabeth
P_YEAR
1799
1804
1800
AUTHOR
Schiller
Schiller
Schiller
B_YEAR
1759
1759
1759
47 / 197
SQL
Correlation of subqueries
Find employees (PNR, PNAME, PAGE) who are older than their
managers.
SELECT P.PNR, P.PNAME, P.PAGE FROM PERS P
WHERE P.PAGE>(SELECT M.PAGE FROM PERS M WHERE
M.PNR=P.MNR)
PNR
PNAME
PAGE
102
Mohican
30
I inner statement will be executed per tuple of the outer statement
I the values of the outer statement will be used as constants for the inner
statement
I will be optimized by the database
I Caution: attributes may overlap!
48 / 197
SQL
Correlation of subqueries (2)
Find all projects (JNR, JNAME, JMNR, JSTART, JEND,
JMEETING) where the manager does not work by 100 percent.
SELECT JNR, JNAME, JMNR, JSTART, JEND,
JMEETING FROM PROJ WHERE PROJ.JMNR IN (SELECT
WORK.PNR FROM WORK WHERE WORK.JNR=PROJ.JNR AND
WORK.TIME_QUOTA!=100)
JNR
2
3
JNAME
NT 4.0
JMNR
100
100
JSTART
01-FEB-94
05-JUN-96
JEND
JMEETING
01-MAY-97
49 / 197
SQL
Order of output / sorting
Find name, age and ANR of all employees sorted by age
(ascending). Persons that are of equal age should be sorted by
ANR (ascending).
SELECT PNAME, PAGE, ANR FROM PERS ORDER BY
PAGE ASC, ANR ASC
PNAME
Gerneboss
Cherokee
Goldon
Mohican
Adlatus
Miniboss
Apache
Sioux
Bigboss
PAGE
25
25
28
30
30
40
40
40
50
ANR
55
55
100
55
56
45
45
45
56
I
Ascending (ASC) and
descending (DESC) sorting
I
Column name and positional
statements possible
50 / 197
SQL
Order of output / sorting (2)
Output name, salary, bonus and total earnings (salary+bonus)
sorted by the total earnings. Equal totals should be sorted
descending by the ratio of bonus to salary.
SELECT PNAME, PSALARY, PBONUS, PSALARY+PBONUS
"TOTAL" FROM PERS ORDER BY 4, PBONUS/PSALARY
DESC
or:
SELECT PNAME, PSALARY, PBONUS, PSALARY+PBONUS
"TOTAL" FROM PERS ORDER BY PSALARY+PBONUS ASC,
PBONUS/PSALARY DESC
51 / 197
SQL
Aggregat functions
Determine the count, the maximum salary, the average salary and
the sum of total earnings (salary+bonus) of all employees.
SELECT
COUNT(*) "Number of employees",
MAX(PSALARY) "Maximum salary",
AVG(PSALARY/12) "Average salary",
SUM(PSALARY+PBONUS) "Salary sum"
FROM PERS
Number of Employees
9
Maximum salary
100000
Average salary
4953.7037
Salary sum
610000
52 / 197
SQL
Aggregat functions (2)
I
Built-in functions AVG, MAX and SUM
I
COUNT(*) counts number of qualified tuples
I
per default no elimilation of duplicates (ALL)
I
use DISTINCT to discard duplicates
53 / 197
SQL
Aggregat functions (3)
Find the number of employees, the number of different salary levels
and the mean of the salary levels.
SELECT
COUNT(*) "Number of employees",
COUNT(DISTINCT PSALARY) "Salary levels",
AVG(DISTINCT PSALARY) "Average salary level"
FROM PERS
Number of employees
9
Salary levels
6
Average salary level
69166.667
54 / 197
SQL
Aggregat functions (4)
Find all employees (PNR, PNAME, PSALARY) whos salary differs
more than 40% from the average salary of the company.
SELECT PNR, PNAME, PSALARY FROM PERS WHERE
PSALARY <(SELECT 0.6*AVG(PSALARY) FROM PERS)
OR PSALARY >(SELECT 1.4*AVG(PSALARY) FROM
PERS)
PNR
1
300
PNAME
Bigboss
Goldon
PSALARY
100000
85000
55 / 197
SQL
Grouping
Create a listing of all departments and the average salary.
SELECT DNR, AVG(PSALARY) AVGSALARY FROM PERS
GROUP BY DNR
DNR
45
55
56
100
AVGSALARY
53333.333
50000
70000
85000
56 / 197
SQL
Grouping (2)
Create a listing of all departments, the average salary and
departments names.
SELECT A.DNR DNR, A.DNAME DNAME, AVG(PSALARY)
AVGSALARY FROM PERS P, DEPT A WHERE
P.DNR=A.DNR GROUP BY A.DNR, A.DNAME
DNAME must also be a grouping attribute, because only attributes
declarated in the GROUP-BY-clause, constants or built-in functions
can be used in the SELECT-clause.
DNR
45
55
56
100
DNAME
Beschaffung
Produktion
Leitung
Vertrieb
AVGSALARY
53333.333
50000
70000
85000
57 / 197
SQL
Selecting groups
List all departments between 50 and 60 where the average
employees age ist lower than 30 years.
SELECT DNR, AVG(PAGE) AVGAGE FROM PERS WHERE
DNR<=60 AND DNR>=50 GROUP BY DNR HAVING
AVG(PAGE)<30
DNR
55
AVGAGE
26.666667
I HAVING-clause can only be used in conjunction with GROUP-BY.
I the condition will be checked against group tuples
58 / 197
SQL
Selecting groups (2)
Find all actors (PNR, NAME) whos performed at least two different
roles and only performed once at each theatre.
SELECT S.PNR PNR, S.NAME NAME FROM ACTOR S,
PERFORMER D
WHERE S.PNR=D.PNR
GROUP BY S.PNR, S.NAME
HAVING COUNT(*)>=2 AND COUNT(*)=COUNT(DISTINCT
THEATRE)
PNR
1
4
5
NAME
Schön
Schiller
George
59 / 197
SQL
Evaluation of SQL-statements
1. Determination of all tables by the FROM-clause
2. The cartesian product of all tables in the
FROM-clause will be computed
3. Tuples will be selected by the WHERE-clause
4. The resulting tuples will be grouped according to the
GROUP-BY-clause
60 / 197
SQL
Evaluation of SQL-statements (2)
5. Group tuples will be selected according to the
HAVING-clause
6. The output is defined by the SELECT-clause
7. The order will be adjusted by the ORDER-BY-clause,
without ORDER-BY the order of the output is
non-deterministic
61 / 197
SQL
Evaluation of SQL-statements example (1)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>=50 ORDER BY A
R
A
Rot
Rot
Rot
Gelb
Gelb
Blau
Blau
Blau
B
10
20
10
10
80
10
80
20
C
10
10
20
50
180
10
10
200
62 / 197
SQL
Evaluation of SQL-statements example (2)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>=50 ORDER BY A
R0
A
Rot
Rot
Rot
Gelb
Gelb
Blau
Blau
Blau
B
10
20
10
10
80
10
80
20
C
10
10
20
50
180
10
10
200
63 / 197
SQL
Evaluation of SQL-statements example (3)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>=50 ORDER BY A
R00
A
Rot
Rot
Rot
Gelb
Blau
Blau
B
10
20
10
10
10
20
C
10
10
20
50
10
200
64 / 197
SQL
Evaluation of SQL-statements example (4)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>=50 ORDER BY A
R000
A
Rot
Rot
Rot
Gelb
Blau
Blau
B
10
20
10
10
10
20
C
10
10
20
50
10
200
65 / 197
SQL
Evaluation of SQL-statements example (5)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>50 ORDER BY A
R0000
A
Gelb
Blau
SUM(B)
10
30
12
12
12
66 / 197
SQL
Evaluation of SQL-statements example (6)
SELECT A, SUM(B), 12 FROM R WHERE B<=50 GROUP
BY A HAVING MAX(C)>50 ORDER BY A
R00000
A
Blau
Gelb
SUM(B)
30
10
12
12
12
67 / 197
SQL
Dynamically created tables
Three types of references are allowed in the FROM-clause:
I
persistent tables
I
dynamically generated or defined logical views
I
dynamically generated tables by a SELECT-statement
68 / 197
SQL
Dynamically created tables example
Create a report with the following informations: the number of
each department, the average salary of the department, the
percentage ratio of the salary sum related to the total salary sum of
the company.
SELECT DNR, AVG(PSALARY) AVGSALARY,
SUM(PSALARY)/SALSUM*100 "RATIO SALARY SUM"
FROM PERS, (SELECT SUM(PSALARY) SALSUM FROM
PERS) GROUP BY DNR, SALSUM
DNR
45
55
56
100
AVGSALARY
53333.333
50000
70000
85000
RATIO SALARY SUM
29.906542
28.037383
26.168224
15.88785
69 / 197
SQL
Dynamically created tables / tuple variables
For dynamically created tables...
I
columns can be renamed by the SELECT-clause
I
tuple variables can be defined and used for computing
SELECT P.DNR, AVG(P.PSALARY) AVGSALARY,
SUM(P.PSALARY)/S.SALSUM*100 "RATIO SALARY SUM"
FROM PERS P, (SELECT SUM(Q.PSALARY) SALSUM
FROM PERS Q) S GROUP BY P.DNR, S.SALSUM
70 / 197
SQL
Searching
Combination of predicates
I
combined by AND, OR, NOT
I
order of evaluation can be specified by parentheses
non-quantified predicates
I
comparison predicates θ
I
LIKE-, BETWEEN-, IN-predicates
I
Test for NULL values
I
MATCH-predicates
I
UNIQUE-predicates
quantified predicates
I
ALL, ANY, EXISTS
71 / 197
SQL
Constructing of tuples
Find all persons (PNR, PNAME) who have the same age, same
salary and same manager like Sioux.
SELECT PNR, PNAME FROM PERS WHERE (PAGE,
PSALARY, MNR) IN (SELECT PAGE, PSALARY, MNR
FROM PERS WHERE PNAME=’Sioux’)
PNR, PNAME
46
47
Apache
Sioux
72 / 197
SQL
LIKE-predicate
I
Pattern-based searching of data
I
LIKE-predicates compare data with pattern/mask
two special placeholders:
I
I
I
% "zero or more characters"
_ "one character"
LIKE-predicate is true, if data value matches the pattern with the
allowed substitutions for % and _.
73 / 197
SQL
LIKE-predicate (2)
NAME LIKE ’%ERD%’
fulfilled by "ERDMANN", "WERDER", "HERD", "ERD"
DNR LIKE ’_7%’
fulfilled by departments with 7 as second character
NAME NOT LIKE ’%-%’
fulfilled by names without hyphen
Search for ’%’ or ’_’ with ESCAPE-character possible:
STRING LIKE ’%\_%’ ESCAPE ’\’
fulfilled by strings with underscore
No general regular expressions possible → REGEXP_LIKE
74 / 197
SQL
Example LIKE-predictae
Find all theatres whose names contain the string ’Theater’ or
’theater’.
SELECT DISTINCT THEATRE FROM PERFORMER WHERE
THEATRE LIKE ’%Theater%’ OR THEATRE LIKE
’%theater%’
THEATRE
Schillertheater
Schloßtheater
Theater des Westens
Pfalztheater
75 / 197
SQL
Example LIKE-predicate (2)
Find all theatres whose names consists of at least three (seperated
by spaces) words. The second word must consist of three
characters and begins with ’d’.
SELECT DISTINCT THEATRE FROM PERFORMER WHERE
THEATRE LIKE ’_% d__ _%’
THEATRE
Theater des Westens
76 / 197
SQL
BETWEEN-predicate
Find all employees with a salary between 80000 and 150000.
SELECT PNAME FROM PERS WHERE PSALARY BETWEEN
80000 AND 150000
PNAME
Bigboss
Miniboss
Goldon
y BETWEEN x AND z ⇔ x<=y AND y<=z
y NOT BETWEEN x AND z ⇔ NOT (y BETWEEN x AND z)
77 / 197
SQL
IN-predicate
Find the numbers (PNR) of actors whos played the character
"Faust".
SELECT S.NAME FROM ACTOR S WHERE ’Faust’ IN
(SELECT CHARACTER FROM PERFORMER D WHERE
D.PNR=S.PNR)
oder auch
SELECT S.NAME FROM ACTOR S WHERE S.PNR IN
(SELECT D.PNR FROM PERFORMER D WHERE
CHARACTER=’Faust’)
NAME
Schön
Müller
x IN (a,b,...,z) ⇔ x=a OR x=b OR...OR x=z
x IN (...) ⇔ x = ANY (...)
x NOT IN y ⇔ NOT (x IN y)
78 / 197
SQL
IN-predicate (2)
Find the numbers (PNR) of actors whos played the character
"Faust".
SELECT NAME FROM ACTOR WHERE PNR = (SELECT PNR
FROM PERFORMER WHERE CHARACTER=’Faust’)
Fehler in Zeile 1:
ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile
Comparing using IN-predicate:
I predicate is true, when a match for at least one tuple of the sub-select is found
I predicate is false, when sub-select returns a empty set
Comparing with operators (i.e. ’=’):
I true, if sub-select returns exactly one tuple and the condition is met, undefined
for more tuples
79 / 197
SQL
NULL-values
On declaration of each column the insertion of null can be allowed
or forbidden.
I
the result of an arithmetic operation with a NULL-value is
always the NULL-value
I
there is no output of a NULL-value in the SELECT-clause
I
a NULL-value in a complex expression always results in a
NULL-value
I
the comparison of a NULL-value with a predicate results in a
UNKNOWN value
80 / 197
SQL
NULL-value (2)
The following logic is used for boolean expressions:
NOT
T
F
?
F
T
?
AND
T
F
?
T
T
F
?
F
F
F
F
?
?
F
?
OR
T
F
?
T
T
T
T
F
T
F
?
?
T
?
?
81 / 197
SQL
NULL-values (3)
Create a list of projects (JNR, JNAME) with the name of the
project manager.
SELECT PNAME, JNR, JNAME FROM PERS, PROJ WHERE
PNR=JMNR
Tuples containing NULL-values in the join attribute will not
participate in the join.
PNAME
Goldon
Gerneboss
Gerneboss
Apache
JNR
1
2
3
4
JNAME
Jahresabschluß
NT 4.0
ABC-Analyse
82 / 197
SQL
NULL-values (4)
Find the employees older than 30 with a salary lower than 40000.
SELECT * FROM PERS WHERE PAGE>30 OR
PSALARY<40000
PERS-tuple will be displayed if:
I
PAGE not NULL and greater 30 (PSALARY can be NULL)
I
PSALARY not NULL and smaller 40000 (PAGE can be NULL)
83 / 197
SQL
Predicate for testing NULL-values
Find the projects with no manager associated.
SELECT JNR, JNAME FROM PROJ WHERE JMNR IS NULL
JNR
5
6
JNAME
ISO 9000
A arithmetic comparison with NULL always results in a NULL-value:
SELECT JNR, JNAME FROM PROJ WHERE JMNR=NULL
Es wurden keine Zeilen gefunden.
84 / 197
SQL
NULL-values and aggregat functions
Built-in functions ignoring NULL-values.
I
COUNT(*) counts all lines
I
AVG, MIN, MAX, SUM do not use lines with NULL-values
I
DISTINCT does not count NULL
85 / 197
SQL
NULL-values and aggregat functions (2)
Create a list of the different time quotas, the average hours and the
average of different hours values.
SELECT COUNT(*), COUNT(DISTINCT TIME_QUOTA),
AVG(HOURS), AVG(DISTINCT HOURS) FROM WORK
COUNT(*)
14
COUNT(DISTINCT TIME_QUOTA)
6
AVG(HOURS)
131.25
AVG(DISTINCT HOURS)
133.33333
AVG(HOURS)
131.25
AVG(DISTINCT HOURS)
133.33333
...WHERE HOURS IS NOT NULL
COUNT(*)
8
COUNT(DISTINCT TIME_QUOTA)
4
86 / 197
SQL
Making NULL-values visible
Special built-in function NVL(Argument, Default) for displaying
NULL-values.
I
return value is Argument, if not NULL
I
return value is Default, if Argument is NULL
SELECT JNR, NVL(JNAME,’#undefined#’) JNAME,
NVL(JMNR,-1) JMNR FROM PROJ WHERE JMR>2
JNR
3
4
5
6
JNAME
#undefined#
ABC-Analyse
ISO 9000
#undefined#
JMNR
100
46
-1
-1
87 / 197
SQL
Quantified predicates
row-constr θ (ALL|ANY) (table-exp)
θ ALL: is true, if θ-expression is true for all results
θ ANY: true, if θ-expression is true for one result value
88 / 197
SQL
Example ANY-predicate
Find employees (PNR, PNAME) of other departments, who are
below the age of any employee of department 55.
SELECT PNR, PNAME FROM PERS WHERE DNR!=55 AND
PAGE < ANY (SELECT PAGE FROM PERS WHERE
DNR=55)
PNR
300
PNAME
Goldon
I Existential quantifier fullfilled, if the comparison for at least one tuple of the
nested SELECT is true.
I Existential quantifier can’t be fullfilled when SELECT returns an empty set
I Existential quantifier can’t be fullfilled, if nested SELECT returns only NULL
values
89 / 197
SQL
Example ANY-predicate (2)
Find the numbers of actors, who played Faust.
Statement with quantified predicate:
SELECT NAME FROM ACTOR WHERE PNR = ANY (SELECT
PNR FROM PERFORMER WHERE CHARACTER=’Faust’)
NAME
Schön
Müller
90 / 197
SQL
Example ALL-predicate
Find all projects (JNR, JNAME) that have been finished before all
OS/2-projects ("OS/2" part of the project name).
SELECT JNR, JNAME FROM PROJ WHERE JEND < ALL
(SELECT JEND FROM PROJ WHERE JNAME LIKE
’%OS/2%’)
JNR
1
2
3
4
5
6
JNAME
Jahresabschluß
NT 4.0
ABC-Analyse
ISO 9000
I Universal quantifier will be fulfilled,
if the comparison for all tuples of the
nested SELECT returns true
I Universal quantifier will be fulfilled
ever, when nested SELECT returns
the empty set.
I Universal quantifier can’t be fulfilled,
if nested SELECT returns at least
one NULL value.
91 / 197
SQL
Testing for existence
[NOT] EXISTS (table-exp)
I
Predicate is false, if table-exp returns a empty set, otherwise
true.
Semantik
x θ ANY (SELECT y FROM T WHERE p) ⇔
EXISTS (SELECT * FROM T WHERE p AND x θ T.y
x θ ALL (SELECT y FROM T WHERE p) ⇔
NOT EXISTS (SELECT * FROM T WHERE p AND NOT (x
θ T.y))
92 / 197
SQL
Example EXISTS-predicate
Display name, age and salary of all managers.
SELECT PNAME, PAGE, PSALARY FROM PERS M WHERE
EXISTS (SELECT * FROM PERS P WHERE
P.MNR=M.PNR)
PNAME
Bigboss
Miniboss
Gerneboss
PAGE
50
40
25
PSALARY
100000
80000
50000
I Special existential quantifier is true, if nested SELECT returns at least one tuple.
93 / 197
SQL
Set-theoretic operations
Union (UNION), Intersection (INTERSECT) and difference
(MINUS) of relations.
(table-exp) {UNION|INTERSECT|MINUS} [ALL]
(table-exp)
I
Elimination of all duplicates if ALL is not specified
I
compatability for all operands required
94 / 197
SQL
Example UNION
Find all places in the POET database: place of residence, place of
premiere, place of birth.
(SELECT
UNION
(SELECT
UNION
(SELECT
UNION
(SELECT
B_PLACE PLACES FROM POET)
P_PLACE FROM DRAMA)
S_PLACE FROM PERFORMER)
R_PLACE FROM ACTOR)
PLACES
Berlin
Bonn
Dresden
Frankfurt
Fulda
Jena
Kaiserslautern
Kamenz
Mannheim
Marbach
Weimar
95 / 197
SQL
JOIN-expressions
SELECT * FROM ACTOR A, PERFORMER P WHERE
A.PNR=P.PNR
equivalent:
SELECT * FROM ACTOR NATURAL JOIN PERFORMER
SELECT * FROM ACTOR JOIN PERFORMER USING (PNR)
SELECT * FROM ACTOR A JOIN PERFORMER P ON
A.PNR=P.PNR
I
Outer join: LEFT JOIN, RIGHT JOIN, FULL JOIN
I
Cartesian product: CROSS JOIN
A CROSS JOIN B ⇔ SELECT * FROM A,B
96 / 197
SQL
JOIN-expressions (ORACLE syntax)
Different outer join syntax for ORACLE databases using (+).
I
R.A=S.B(+) returns left outer join (all tuples of relation R will
be used; the relation specified with (+) will be supplemented
with NULL-values)
I
R.A(+)=S.B returns right outer join (all tuples of relation S
will be used)
97 / 197
SQL
JOIN-expressions (ORACLE syntax) (2)
Create a listing of all projects (JNR, JNAME); the name of the
project manager (MGRNAME) should also be displayed.
SELECT JNR, JNAME, PNAME MGRNAME FROM PROJ,
PERS WHERE JMNR=PNR(+)
JNR
1
2
3
4
5
6
JNAME
Jahresabschluß
NT 4.0
ABC-Analyse
ISO 9000
MGRNAME
Goldon
Gerneboss
Gerneboss
Apache
98 / 197
SQL
JOIN-expressions (ORACLE syntax) (3)
Find the names and the age of all employees at least 35 years old.
Also display the names of their manager.
SELECT P.PNAME "Emp.-Name", P.PAGE
"Emp.-Alter", D.PNAME "Man.-Name" FROM PERS P,
PERS D WHERE P.MNR=D.PNR(+) AND P.PAGE>35
Emp.-Name
Bigboss
Miniboss
Apache
Sioux
Emp.-Alter
50
40
40
40
Man.-Name
Bigboss
Miniboss
Miniboss
99 / 197
SQL
Exercises
The following question and the results are given:
Which employees (PNR, PNAME, PAGE) are older than
their manager?
PNR
102
PNAME
Mohican
PAGE
30
Which of the following SQL-statements are correct?
100 / 197
SQL
Exercises (1)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT PNR FROM PERS
WHERE PNR=MNR
AND PALTER<PALTER);
101 / 197
SQL
Exercises (2)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT PNR FROM PERS
WHERE PNR=MNR
AND PALTER<PALTER);
I
the table PERS in the nested SELECT overlaps the outer table
PERS. No reference to the outer tuples in the inner SELECT
are possible
I
no tuples will be returned
102 / 197
SQL
Exercises (3)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT M.PNR FROM PERS M
WHERE M.PNR=MNR
AND M.PALTER<PALTER);
103 / 197
SQL
Exercises (4)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT M.PNR FROM PERS M
WHERE M.PNR=MNR
AND M.PALTER<PALTER);
I
identifier for outer table is PERS, for the inner table M
I
unqualified attributes will be supplemented with M. The
statement is not correct.
I
no tuples will be returned
104 / 197
SQL
Exercises (5)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT M.PNR FROM PERS M
WHERE M.PNR=PERS.MNR
AND M.PALTER<PERS.PALTER);
105 / 197
SQL
Exercises (6)
SELECT PNR, PNAME, PALTER FROM PERS
WHERE MNR=(SELECT M.PNR FROM PERS M
WHERE M.PNR=PERS.MNR
AND M.PALTER<PERS.PALTER);
I
identifier for outer table is PERS, for the inner table M
I
there are no unqualified attributes, so no attributes wil be
supplemented with M
I
the correct tuples will be returned!
106 / 197
SQL
Exercises (7)
Find actors (all attributes) whos place of residence does not end
with ’n’. The length of actor’s name should be equal to any
character’s name.
107 / 197
SQL
Exercises (8)
Find actors (all attributes) whos place of residence does not end
with ’n’. The length of actor’s name should be equal to any
character’s name.
SELECT * FROM ACTOR WHERE LENGTH(NAME) IN
(SELECT LENGTH(CHARACTER) FROM ROLE)
AND SUBSTR(R_PLACE,-1)!=’n’
PNR
1
2
4
6
7
R_PLACE
Frankfurt
Frankfurt
Jena
Frankfurt
Marbach
NAME
Schön
Müller
Schiller
Mann
Krug
108 / 197
SQL
Exercises (9)
Find title and premiere place of dramas, that were premiered at
most 35 years after the author was born.
109 / 197
SQL
Exercises (10)
Find title and premiere place of dramas, that were premiered at
most 35 years after the author was born.
SELECT TITLE, P_PLACE FROM DRAMA D
WHERE P_YEAR<=(SELECT B_YEAR+35 FROM POET A
WHERE A.AUTHOR=D.AUTHOR)
TITLE
Iphigenie
Zerbrochene Krug
P_PLACE
Mannheim
Weimar
110 / 197
SQL
Exercises (11)
Find employees (PNAME, PSALARY, PNAME of the manager)
that have a greater salary than their direct managers.
111 / 197
SQL
Exercises (12)
Find employees (PNAME, PSALARY, PNAME of the manager)
that have a greater salary than their direct managers.
SELECT P.PNAME, P.PSALARY, M.PNAME FROM PERS P, PERS M
WHERE P.MNR=M.PNR
AND P.PSALARY>M.PSALARY
P.PNAME
Cherokee
P.PSALARY
60000
M.PNAME
Gerneboss
112 / 197
SQL
Transitive closure
Z
5
2
1
I
B
3
E
STRUK
A
3
5
8
C
3
D
1
10
F
G
3
H
OTNR
Z
Z
A
A
A
B
B
C
C
D
G
UTNR
I
B
B
C
D
E
F
F
G
G
H
MENGE
5
1
2
5
8
3
3
3
1
10
3
113 / 197
SQL
Transitive closure (2)
Find all parts needed to construct the component X.
Problems:
I
nesting of any depth
I
unknown maximum depth
I
no support in the SQL-standard
114 / 197
SQL
Transitive closure (2)
Find all parts needed to construct the component X.
Solution for maximum depth of 2:
(SELECT UTNR FROM STRUK WHERE OTNR=’A’)
UNION
(SELECT UTNR FROM STRUK WHERE OTNR IN (SELECT
UTNR FROM STRUK WHERE OTNR=’A’))
UTNR
B
C
D
E
F
G
115 / 197
SQL
Transitive closure / ORACLE extension
Find all parts needed to construct the component X.
Solution using ORACLE-extension:
SELECT UTNR FROM STRUK START WITH OTNR=’A’
CONNECT BY PRIOR UTNR=OTNR
UTNR
B
C
D
E
F
G
H
116 / 197
SQL
Transitive closure / ORACLE extension (2)
I
CONNECT BY-clause for specifying recursions.
I
predicate for defining the input set of the recursion
START WITH
I
Recursion condition
CONNECT BY PRIOR A θ B; θ ∈ {=, <, . . . }
I
restriction of values found during recursion with
WHERE-condition
no join operations possible
not compatible with GROUP BY and HAVING
I
I
117 / 197
SQL
Transitive closure / example
Find all components that require part G. Display also the level in
the assembly hierarchy.
SELECT OTNR, LEVEL FROM STRUK START WITH
UTNR=’G’ CONNECT BY UTNR=PRIOR OTNR
LEVEL-column returns depth of recursion
I
can be used for output
I
can be used to select tuples with CONNECT BY or WHERE
OTNR
C
A
D
A
LEVEL
1
2
1
2
118 / 197
SQL
Insertion of tuples
Insert a actor Garfield with the PNR 4711.
INSERT INTO ACTOR (PNR, NAME) VALUES (4711,
’Garfield’)
I
if values are in correct order the attributes list can be ommitted
119 / 197
SQL
Insertion of tuples
Insert a actor Garfield with the PNR 4711.
INSERT INTO ACTOR VALUES (4711, NULL,
’Garfield’)
I
not specified attributes will be NULL
120 / 197
SQL
Insertion of tuples
Insert the actors from Frankfurt in the relation TEMP.
INSERT INTO TEMP (SELECT * FROM ACTOR WHERE
R_PLACE=’Frankfurt’)
I
set-oriented insertion with SELECT-statement possible
I
existing relation TEMP required
I
compatible data types required
I
the inserted tuples are independent copied tuples
121 / 197
SQL
Insertion of tuples
Add a department 200 with the name Research. Also add
employees Newboss, Kiowa, Shoshone with the numbers 301, 310,
and 320 and the ages 35, 32 and 30. Newboss works for Bigboss,
the other two working for Newboss. All of them receive a salary of
60000 and a bonus of 0.
INSERT
INSERT
VALUES
INSERT
VALUES
INSERT
VALUES
INTO DEBT (DNR, DNAME) VALUES (200, ’Research’);
INTO PERS (PNR, PNAME, PAGE, PSALARY, PBONUS, MNR, DNR)
(301, ’Newboss’, 35, 60000, 0, 1, 200);
INTO PERS (PNR, PNAME, PAGE, PSALARY, PBONUS, MNR, DNR)
(310, ’Kiowa’, 32, 60000, 0, 301, 200);
INTO PERS (PNR, PNAME, PAGE, PSALARY, PBONUS, MNR, DNR)
(320, ’Shoshone’, 30, 60000, 0, 301, 200);
122 / 197
SQL
Deleting tuples
Delete the actor with the PNR 8.
DELETE FROM ACTOR WHERE PNR=8
I
WHERE-clause used in the same manner like it is used for
SELECT-statements
123 / 197
SQL
Deleting tuples (2)
Delete all actors never performed a role.
DELETE FROM ACTOR WHERE PNR NOT IN (SELECT PNR
FROM PERFORMER)
124 / 197
SQL
Modification of tuples
Increase the salary of employees in department 55 with a salary
below 55000 by 10%.
UPDATE PERS SET PSALARY= PSALARY*1.1 WHERE
DNR=55 AND PSALARY <55000
I
WHERE-clause used in the same manner like it is used for
SELECT-statements
125 / 197
SQL
Modification of tuples (2)
Increase the salary by 10% and set a bonus of 20000 for employees
with the lowest income of each department.
UPDATE PERS P SET PSALARY= PSALARY*1.1,
PBONUS=20000 WHERE PSALARY =(SELECT MIN(S.
PSALARY) FROM PERS S WHERE S.DNR=P.DNR)
126 / 197
SQL
Modification of tuples (3)
Employees of department 55 and 56 with no bonus should now
receive a bonus. The bonus should be 50% of the average paid
bonus in the department.
UPDATE PERS P SET PBONUS=(SELECT
0.5*AVG(PBONUS) FROM PERS B WHERE B.DNR=P.DNR)
WHERE DNR IN (55,56) AND (PBONUS=0 OR PBONUS
IS NULL)
127 / 197
SQL
Exercise grouping
Determine the results of the following statements on relation R.
R
A
1
2
3
4
5
6
7
8
9
10
11
B
rot
rot
rot
blau
blau
grün
grün
gelb
gelb
gelb
gelb
C
alt
alt
neu
neu
neu
alt
alt
neu
neu
neu
alt
D
10
20
10
20
30
20
30
20
20
30
10
128 / 197
SQL
Exercise grouping (2)
SELECT SUM(D) FROM R WHERE MOD(A,6)=0
SUM(D)
129 / 197
SQL
Exercise grouping (3)
SELECT SUM(D) FROM R WHERE MOD(A,6)=0
SUM(D)
20
130 / 197
SQL
Exercise grouping (4)
SELECT B, C, SUM(A) FROM R GROUP BY B, C HAVING
COUNT(*)=2
B
C
SUM(A)
131 / 197
SQL
Exercise grouping (5)
SELECT B, C, SUM(A) FROM R GROUP BY B, C HAVING
COUNT(*)=2
B
C
SUM(A)
rot
alt
3
blau
neu
9
grün
alt
13
132 / 197
SQL
Exercise grouping (6)
SELECT B, SUM(A) FROM R WHERE MOD(A,10)!=0 GROUP BY B,
C HAVING MAX(D)<30
B
SUM(A)
133 / 197
SQL
Exercise grouping (7)
SELECT B, SUM(A) FROM R WHERE MOD(A,10)!=0 GROUP BY B,
C HAVING MAX(D)<30
B
rot
SUM(A)
3
gelb
11
gelb
17
rot
3
134 / 197
SQL
Concepts of SQL database management systems
Data-objects: tables,
columns, schemas,
catalogs
Users
Domains
SQL-Database
Permissions
Integrity constraints
135 / 197
SQL
Concepts of SQL database management systems (2)
SQL-Environment - "The database"
Catalog
Schema
Tables and columns
Lines and rows
136 / 197
SQL
Concepts of SQL database management systems (3)
I
Term of database not standardized
I
SQL-environment includes all data managed by DBMS
I
Data divided into catalogs (SQL2), Oracle calls them
database-instances
I
SQL-environment includes data and meta-data, all organised in
tables
137 / 197
SQL
Aufbau eines Katalogs (ORACLE)
Table name
CATALOG
COL_COMMENTS
COL_PRIVS
CONSTRAINTS
INDEXES
IND_COLUMNS
OBJECTS
SEQUENCES
SYNONYMS
TABLES
TAB_COLUMNS
TAB_COMMENTS
TAB_PRIVS
TRIGGERS
USERS
VIEWS
Description
Tables, views, synonyms, sequences
Comments on table columns
Permissions on table comlumns
Intergity constraints
Indices
Table columns used by indices
SQL-Objects (Tables, synonyms, columns,. . . )
Sequences
Synonyms (or aliases)
Tables
Table columns
Comments on tables
Permissions on tables
Triggers
Users
Views
138 / 197
SQL
Datatypes (ORACLE)
Datatype
Description
VARCHAR2(size)
NVARCHAR2(size)
variable length character-string up to 4000 characters
variable length character-string up to 4000 characters in
national charset
Numerical datatype with size digits and d decimal places
variable length character-data up to 2GB, not usable in
WHERE-clause, functions, indices, . . .
Date-/time-value
Timestamp, precision decimal places for seconds
Timstamp with time zone
NUMBER(size,d)
LONG
DATE
TIMESTAMP(precision)
TIMESTAMP(precision)
WITH TIME ZONE
TIMESTAMP(precision)
WITH LOCAL TIME ZONE
Timestamp, normalised for local time zone
139 / 197
SQL
Datatypes (ORACLE) (2)
Datatype
Description
INTERVAL
YEAR(precision) TO
MONTH
INTERVAL
DEAY(day_precision) TO
SECOND(second_precision)
RAW(size)
LONG RAW
ROWID
CHAR(size)
NCHAR(size)
CLOB
NCLOB
BLOB
BFILE
Period in years and months, precision number of digits
of the year
Period in days, hours, minutes and seconds
Binary data up to 255 bytes
Binary data up to 2GB
Value identifying single row in ORACLE-database
fixed-length character-dataup to 2000 characters
multibyte-version of CHAR
alphanumerical data up to 4GB
like CLOB with multibyte data
Binary data up to 4GB
Pointer to a binary system file
140 / 197
SQL
Creating tables (ORACLE)
The creation of tables includes
I
Column names and datatypes
I
definition of the primary key
I
foreign keys
I
intergity constraints
141 / 197
SQL
Creating tables syntax (ORACLE)
CREATE TABLE base-table
[(base-table-element-commalist)]
[{ENABLE|DISABLE}CONSTRAINT constraint]
[AS select-exp]
base-table-element::=
column-def|base-table-constraint-def
base-table-constraint-def::=
[CONSTRAINT constraint]
{{PRIMARY KEY|UNIQUE}(column-commalist)|
FOREIGN KEY (column-commalist) references-def|
CHECK(cond-exp)}[DISABLE]
142 / 197
SQL
Creating columns (ORACLE)
CREATE TABLE STUDENT (
MATRIKELNR INT,
NAME VARCHAR(40),
VORNAME VARCHAR(40),
GESCHLECHT VARCHAR(1)
);
143 / 197
SQL
Definition of the primary key (ORACLE)
CREATE TABLE STUDENT (
MATRIKELNR INT PRIMARY KEY,
NAME VARCHAR(40),
VORNAME VARCHAR(40),
GESCHLECHT VARCHAR(1)
);
144 / 197
SQL
Foreign keys (ORACLE)
CREATE TABLE STUDENT (
MATRIKELNR INT PRIMARY KEY,
NAME VARCHAR(40),
VORNAME VARCHAR(40),
GESCHLECHT VARCHAR(1)
);
CREATE TABLE PRUEFUNG (
MATRIKELNR INT,
LEHRVERANSTALTUNG VARCHAR(40),
NOTE DECIMAL(2,1),
VERSUCH INT,
CONSTRAINT PK PRIMARY KEY(MATRIKELNR, VERSUCH, LEHRVERANSTALTUNG),
CONSTRAINT FK_MATRIKELNR FOREIGN KEY (MATRIKELNR)
REFERENCES STUDENT(MATRIKELNR) ON DELETE CASCADE
);
I Naming not required for constraints
I Naming constraints is recommend (testing, documentation)
145 / 197
SQL
Integrity constraints (ORACLE)
CREATE TABLE STUDENT (
MATRIKELNR INT PRIMARY KEY,
NAME VARCHAR(40) NOT NULL,
VORNAME VARCHAR(40) NOT NULL,
GESCHLECHT VARCHAR(1) NOT NULL,
CONSTRAINT CHK_MATRIKELNR CHECK (MATRIKELNR>=100000 AND MATRIKELNR<=999999),
CONSTRAINT CHK_GESCHLECHT CHECK (GESCHLECHT=’w’ OR GESCHLECHT=’m’)
);
CREATE TABLE PRUEFUNG (
MATRIKELNR INT,
LEHRVERANSTALTUNG VARCHAR(40),
NOTE DECIMAL(2,1),
VERSUCH INT,
CONSTRAINT PK PRIMARY KEY(MATRIKELNR, VERSUCH, LEHRVERANSTALTUNG),
CONSTRAINT FK_MATRIKELNR FOREIGN KEY (MATRIKELNR)
REFERENCES STUDENT(MATRIKELNR) ON DELETE CASCADE
);
Integrity constraints can be defined disabled (DISABLE) or enabled (ENABLE).
146 / 197
SQL
Integrity constraints (2)
Concept of transactions
I
processing window for compliance of semantic integrity
constraints
I
concurency isolations
I
failure isolation
I
Logging and recovery (COMMIT WORK, ROLLBACK WORK)
147 / 197
SQL
Integrity constraints (3)
SET AUTOCOMMIT OFF
INSERT INTO STUDENT
INSERT INTO STUDENT
INSERT INTO STUDENT
INSERT INTO STUDENT
COMMIT
(MATRIKELNR,
(MATRIKELNR,
(MATRIKELNR,
(MATRIKELNR,
NAME,
NAME,
NAME,
NAME,
VORNAME,
VORNAME,
VORNAME,
VORNAME,
GESCHLECHT)
GESCHLECHT)
GESCHLECHT)
GESCHLECHT)
VALUES
VALUES
VALUES
VALUES
(...
(...
(...
(...
Completing a transaction:
I
transaction successful, persistent storage (COMMIT)
I
Error, roll back changes (ROLLBACK)
148 / 197
SQL
Integrity constraints
I
CHECK-constraints
I
Forbid NULL-values, UNIQUE, PRIMARY KEY
I
Foreign key constraints
Definition of validation point possible:
I
IMMEDIATE: at the end of a modfying operation (Default)
I
DEFFERED: at transaction end (COMMIT)
Changing the validation point:
SET CONSTRAINTS {ALL|constraint-commalist}
{DEFERRED|IMMEDIATE}
149 / 197
SQL
Time of validation Beispiel
CREATE TABLE A (ID_A NUMBER PRIMARY KEY, CONTENT_A VARCHAR2(255));
CREATE TABLE B (ID_B NUMBER PRIMARY KEY, CONTENT_B VARCHAR2(255));
ALTER TABLE A ADD REF_B NUMBER NOT NULL;
ALTER TABLE A ADD CONSTRAINT FKAB
FOREIGN KEY (REF_B) REFERENCES B(ID_B) DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE B ADD REF_A NUMBER NOT NULL;
ALTER TABLE B ADD CONSTRAINT FKBA
FOREIGN KEY (REF_A) REFERENCES A(ID_A) DEFERRABLE INITIALLY IMMEDIATE;
150 / 197
SQL
Time of validation Beispiel (2)
INSERT INTO A (ID_A, REF_B, CONTENT_A) VALUES (1, 2, ’textA’);
INSERT INTO B (ID_B, REF_A, CONTENT_B) VALUES (2, 1, ’textB’);
SELECT * FROM A;
SELECT * FROM B;
INSERT INTO A (ID_A, REF_B, CONTENT_A) VALUES (1, 2, ’textA’)
*
FEHLER in Zeile 1:
ORA-02291: Integritäts-Constraint (DBS2P20.FKAB) verletzt - übergeordneter
Schlüssel nicht gefunden
INSERT INTO B (ID_B, REF_A, CONTENT_B) VALUES (2, 1, ’textB’)
*
FEHLER in Zeile 1:
ORA-02291: Integritäts-Constraint (DBS2P20.FKBA) verletzt - übergeordneter
Schlüssel nicht gefunden
Es wurden keine Zeilen ausgewählt
Es wurden keine Zeilen ausgewählt
151 / 197
SQL
Time of validation Beispiel (3)
SET AUTOCOMMIT OFF;
SET CONSTRAINT FKAB DEFERRED;
SET CONSTRAINT FKBA DEFERRED;
INSERT INTO A (ID_A, REF_B, CONTENT_A) VALUES (1, 2, ’textA’);
INSERT INTO B (ID_B, REF_A, CONTENT_B) VALUES (2, 1, ’textB’);
COMMIT
SELECT * FROM A;
SELECT * FROM B;
Constraint wurde festgelegt.
Constraint wurde festgelegt.
1 Zeile wurde erstellt.
1 Zeile wurde erstellt.
ID_A
CONTENT_A
REF_B
---------- ------------ ---------1 textA
2
1 Zeile wurde ausgewählt.
ID_B CONTENT_B
REF_A
---------- ------------ ---------2 textB
1
1 Zeile wurde ausgewählt.
152 / 197
SQL
Creating a table using SELECT example (ORACLE)
CREATE TABLE C (
C1 PRIMARY KEY,
C2 DEFAULT 5,
C3 CHECK (C3>0) DISABLE)
AS SELECT B1,B2,B3 FROM B
The SELECT-clause
I
defines datatypes
I
defines column names if not set
I
fills the new table
153 / 197
SQL
Modifying tables (ORACLE)
ALTER TABLE base-table
{ADD (column data-type [DEFAULT default-expr]
[column-constraint-def]...)|
MODIFY (column [data-type][DEFAULT default-expr]
[column-constraint-def]...)|
ADD (base-table-constraint-def)}
[ENABLE enable-clause]...
[DISABLE disable-clause]...
.
.
.
Adding of an deactivated integrity constraint and activating it:
ALTER TABLE PRUEFUNG
ADD (CONSTRAINT CHK_VERSUCH CHECK (VERSUCH IN (1,2,3)))
ALTER TABLE PRUEFUNG
DISABLE CONSTRAINT CHK_VERSUCH;
154 / 197
SQL
Modifying tables example (ORACLE)
Adding of two columns:
ALTER TABLE STUDENT
ADD (SEMESTER INT, WOHNORT VARCHAR(40));
Modification of columns:
ALTER TABLE PRUEFUNG
MODIFY (WOHNORT NOT NULL);
I
I
Adding of integrity constraints
limited modification of the datatype
I
I
Increasing precision of numerical datatypes
Increasing the maximal length of strings
155 / 197
SQL
Deletion of tables (ORACLE)
DROP {TABLE base-table [CASCADE CONSTRAINTS]|
VIEW view}
I
use CASCADE CONSTRAINTS to automatically drop all
referenced constraints
I
without the option CASCADE CONSTRAINTS the object can not
be deleted if there are any dependecies left
I
views will not affected by the deletion of tables, they will be
marked as invalid
156 / 197
SQL
Concept of views (ORACLE)
CREATE [OR REPLACE]
[FORCE|NOFORCE] VIEW view [(column-commalist)]
AS table-exp
[WITH CHECK OPTION [CONSTRAINT constraint]|
WITH READ ONLY]
I modification of a existing view possible (OR REPLACE)
I views can be created independently of the existance of the underlying tables
(FORCE)
I Forbid update operations (READ ONLY)
I Update operations that will result in eliminating tuples out of the view can be
I
I
denied (WITH CHECK OPTION)
integrity constraint can be named (CONSTRAINT)
157 / 197
SQL
Example views (ORACLE)
Create a read-only view READPERS of the table PERS.
CREATE VIEW READPERS AS
SELECT * FROM PERS
WITH READ ONLY
The view READPERS should now defined as read-write.
CREATE OR REPLACE VIEW READPERS AS
SELECT * FROM PERS
158 / 197
SQL
Example views (ORACLE) (2)
Create a view NOBONUS of all employees. Updates should not be
able to eliminate employees out of the view.
CREATE VIEW NOBONUS AS
SELECT * FROM PERS
WHERE PBONUS IS NULL OR PBONUS=0
WITH CHECK OPTION CONSTRAINT NBON
159 / 197
SQL
Example views (ORACLE) (3)
Create a view PRODBESCH of all employess of the departments
’Produktion’ and ’Beschaffung’. Updates should not be able to
eliminate employees out of the view.
CREATE VIEW PRODBESCH AS
SELECT * FROM PERS
WHERE DNR IN (SELECT DNR FROM DEPT
WHERE DNAME IN (’Produktion’,’Beschaffung’))
WITH CHECK OPTION CONSTRAINT BRBE
160 / 197
SQL
Example views (ORACLE) (4)
Create a view PRODBESCH of all employess of the departments
’Produktion’ and ’Beschaffung’. Updates should not be able to
eliminate employees out of the view. (alternative solution)
CREATE VIEW PRODBESCH AS
SELECT P.* FROM PERS P, DEPT D
WHERE P.DNR=D.DNR
AND D.DNAME IN (’Produktion’,’Beschaffung’)
WITH CHECK OPTION CONSTRAINT BRBE
161 / 197
SQL
Creating index structures (ORACLE)
I
Defintion of index structures by the DBA or the user
I
Usage of an index will be decided by the DBMS-optimizer
I
no defined in the SQL-standard
162 / 197
SQL
Creating index structures (ORACLE) (2)
Creating an index:
CREATE [UNIQUE|BITMAP] INDEX index
ON base-table (column [ORDER][, column [ORDER]...)
Example:
CREATE UNIQUE INDEX PERSIND1 ON PERS (PNR)
I
Realised for example by B-Trees
I
no duplicates possible
163 / 197
SQL
Creating index structures (ORACLE) (3)
For example: Additional column GENDER in table PERS with the
possible values ’w’ and ’m’.
The table has 2 million rows.
Index size
Runtime (COUNT)
B-Tree
29,69 MB
1,01 sec
Bitmap
753 KB
0,03 sec
For only a few possible values and low number of update operations
a Bitmap-index is recommended.
164 / 197
SQL
EXPLAIN PLAN
Analyse SQL statements:
I
examination of costs of a statement
I
ouput of the execution plan
I
depends on the size of the used tables
165 / 197
SQL
EXPLAIN PLAN Example
Find actors who played the role ’Faust’.
Solution 1: SELECT S.NAME FROM SCHAUSPIELER S WHERE
’Faust’ IN (SELECT FIGUR FROM DARSTELLER D
WHERE D.PNR=S.PNR);
Solution 2: SELECT S.NAME FROM SCHAUSPIELER S WHERE
S.PNR IN (SELECT D.PNR FROM DARSTELLER D
WHERE FIGUR=’Faust’);
166 / 197
SQL
EXPLAIN PLAN Example (2)
EXPLAIN PLAN SET STATEMENT_ID=’stmt1’ FOR
SELECT S.NAME FROM SCHAUSPIELER S
WHERE ’Faust’ IN
(SELECT FIGUR FROM DARSTELLER D WHERE D.PNR=S.PNR);
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, ’stmt1’, ’TYPICAL’));
167 / 197
SQL
EXPLAIN PLAN Example (3)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------Plan hash value: 451918592
----------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
32 |
800 | 2953
(1)| 00:00:36 |
|* 1 | FILTER
|
|
|
|
|
|
|
2 |
TABLE ACCESS FULL| SCHAUSPIELER | 91478 | 2233K|
87
(5)| 00:00:02 |
|* 3 |
INDEX UNIQUE SCAN| SYS_C0055527 |
1 |
25 |
1
(0)| 00:00:01 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------1 - filter( EXISTS (SELECT /*+ */ 0 FROM "DARSTELLER" "D" WHERE
"FIGUR"=’Faust’ AND "D"."PNR"=:B1))
3 - access("D"."PNR"=:B1 AND "FIGUR"=’Faust’)
Note
----- dynamic sampling used for this statement
21 Zeilen ausgewählt.
168 / 197
SQL
EXPLAIN PLAN Example (4)
EXPLAIN PLAN SET STATEMENT_ID=’stmt2’ FOR
SELECT S.NAME FROM SCHAUSPIELER S
WHERE S.PNR IN
(SELECT D.PNR FROM DARSTELLER D WHERE FIGUR=’Faust’);
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, ’stmt2’, ’TYPICAL’));
169 / 197
SQL
EXPLAIN PLAN Example (5)
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------Plan hash value: 587324159
----------------------------------------------------------------------------------| Id | Operation
| Name
| Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
| 1090 | 54500 |
119 (31)| 00:00:02 |
|
1 | NESTED LOOPS SEMI |
| 1090 | 54500 |
119 (31)| 00:00:02 |
|
2 |
TABLE ACCESS FULL| SCHAUSPIELER | 91478 | 2233K|
87
(5)| 00:00:02 |
|* 3 |
INDEX UNIQUE SCAN| SYS_C0055527 |
13 |
325 |
0
(0)| 00:00:01 |
----------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------3 - access("S"."PNR"="D"."PNR" AND "FIGUR"=’Faust’)
Note
----- dynamic sampling used for this statement
19 Zeilen ausgewählt.
170 / 197
Programming interfaces
Overview
Examples for APIs:
I
PL/SQL
I
Embedded SQL, Oracle Call Interface (OCI)
I
Open Database Connectivity (Microsoft)
I
Java Database Connectivity (JDBC)
171 / 197
Programming interfaces
PL/SQL
PL/SQL (Procedural Language/SQL)
I
proprietary programming language of Oracle
I
designed for the database
I
SQL-statements are program code, not only strings
I
syntax can be checked before runtime
172 / 197
Programming interfaces
PL/SQL basics
DECLARE
-- declarations
BEGIN
-- program code
EXCEPTION
-- exception handling
END;
173 / 197
Programming interfaces
PL/SQL cursor concept
I
cursor is a symbolic name associated with a statement
I
results can be fetched (one tuple at a time) using the cursor
I
values can be transferred into variables of the programming
language INTO
I
converting of data-types
Beispiel:
OPEN c1;
FETCH C1 INTO VAR1, VAR2, ..., VARN
CLOSE c1;
174 / 197
Programming interfaces
PL/SQL cursor example
CREATE OR REPLACE PROCEDURE testCursor (autor_in IN VARCHAR2) IS
titel VARCHAR(30);
u_ort VARCHAR(30);
u_jahr INT;
CURSOR myCursor IS
SELECT TITEL, U_ORT, U_JAHR FROM DRAMA WHERE AUTOR=autor_in;
BEGIN
OPEN myCursor;
LOOP
FETCH myCursor INTO titel, u_ort, u_jahr;
EXIT WHEN myCursor%NOTFOUND;
dbms_output.put(titel);
dbms_output.put(’ ’);
dbms_output.put(u_ort);
dbms_output.put(’ ’);
dbms_output.put(u_jahr);
dbms_output.new_line;
END LOOP;
CLOSE myCursor;
END testCursor;
175 / 197
Programming interfaces
PL/SQL loop example
Loops (without declaration of cursor) also possible:
CREATE OR REPLACE PROCEDURE testPlSql (autor_in IN VARCHAR2) IS
BEGIN
FOR row IN (SELECT TITEL, U_ORT, U_JAHR FROM DRAMA WHERE AUTOR=autor_in)
LOOP
dbms_output.put(row.TITEL); dbms_output.put(’ ’);
dbms_output.put(row.U_ORT); dbms_output.put(’ ’);
dbms_output.put(row.U_JAHR); dbms_output.new_line;
END LOOP;
END testPlSql;
Aufruf:
EXEC testCursor(’Schiller’);
Maria Stuart Weimar 1800
Wallenstein Jena 1799
Tell Weimar 1804
176 / 197
Programming interfaces
PL/SQL function example
CREATE OR REPLACE FUNCTION getAuthor (drama_in IN VARCHAR2)
RETURN VARCHAR2 IS autor_out VARCHAR2(30);
BEGIN
SELECT AUTOR INTO autor_out FROM DRAMA WHERE TITEL=drama_in;
RETURN autor_out;
END getAuthor;
Calling the function:
SELECT * FROM DICHTER WHERE AUTOR=getAuthor(’Faust’);
177 / 197
Programming interfaces
Embedded SQL
Embedded SQL
I
Precompiler, generates Oracle-specific code
I
creating simple database-driven programs without knowledge
of the Oracle-API
178 / 197
Programming interfaces
Embedded SQL - concept
179 / 197
Programming interfaces
Embedded SQL example
#include <stdio.h>
#include <sqlca.h>
void sqlerror();
// Definition von Variablen
EXEC SQL BEGIN DECLARE SECTION;
// Connection-String Benutzername/Passwort
char *connstr = "dbs2p20/dbs2p20";
// Variable zur Aufnahme von TITEL
char db_titel[30];
// Variable zur Aufnahme von U_ORT
char db_uort[30];
// Variable zur Aufnahme von U_JAHR
int db_ujahr;
EXEC SQL END DECLARE SECTION;
180 / 197
Programming interfaces
Embedded SQL example (2)
//Main-Methode
int main() {
// bei jedem Fehler die Methode sqlerror() aufrufen
EXEC SQL WHENEVER SQLERROR DO sqlerror();
// bei Warnungen einfach fortfahren
EXEC SQL WHENEVER SQLWARNING CONTINUE;
// Verbindung zu Datenbank aufbauen
EXEC SQL CONNECT :connstr;
// Cursor mit entsprechendem SELECT-Statement definieren
EXEC SQL DECLARE drama_cursor CURSOR FOR
SELECT TITEL, U_ORT, U_JAHR
FROM DRAMA
WHERE AUTOR = ’Schiller’;
181 / 197
Programming interfaces
Embedded SQL example (3)
//Main-Methode Fortsetzung
// Cursor "öffnen" um die Ergebnisse zu durchlaufen
EXEC SQL OPEN drama_cursor;
// wenn keine Ergebnisse mehr, mit break die Schleife verlassen
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) {
// jedes Tupel in die entsprechenden Variablen schreiben...
EXEC SQL FETCH drama_cursor INTO :db_titel, :db_uort, :db_ujahr;
// ...und ausgeben
printf("\t%s\t%s\t%i\n", db_titel, db_uort, db_ujahr);
}
// Cursor schliessen
EXEC SQL CLOSE drama_cursor;
// Transaktion wurde erfolgreich ausgeführt
EXEC SQL COMMIT WORK RELEASE;
// Main-Methode ohne Fehlercode verlassen
return 0;
}
182 / 197
Programming interfaces
Embedded SQL example (4)
//Methode sqlerror() zur Ausgabe von Fehlerinformationen
void sqlerror() {
// Fehlernummer ausgeben
printf("Oracle error code: %i\nOracle error description:
sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
// hier einfach weiter machen
EXEC SQL WHENEVER SQLERROR CONTINUE;
// Rollback durchführen...
EXEC SQL ROLLBACK WORK RELEASE;
// ...und Programm verlassen
exit(1);
return;
}
%s\n",
183 / 197
Programming interfaces
Embedded SQL example compilation
-bash-3.00$ proc iname=testProC.pc
-bash-3.00$ gcc testProC.c -o testProC -lclntsh -L/opt/oracle/oracle/product/
10.2.0/db_1/lib32 -I/opt/oracle/oracle/product/10.2.0/db_1/precomp/public
-bash-3.00$ ./testProC
Maria Stuart
Weimar
1800
Wallenstein
Jena
1799
Tell
Weimar
1804
184 / 197
Programming interfaces
ORACLE Call Interface
OCI
I
powerful interface for the Oracle database
I
complex, many functions and parameters
I
high complexity for easy statements
I
very high performace die ORACLE-Api zu haben
185 / 197
Programming interfaces
OCI example
#include
#include
#include
#include
<stdio.h>
<stdlib.h>
<string.h>
<oci.h>
/* Definition von Variablen */
static OCIEnv *p_env;
static OCIError *p_err;
static OCISvcCtx *p_svc;
static OCIStmt *p_sql;
static OCIDefine *p_dfn = (OCIDefine *) 0;
static OCIBind *p_bnd = (OCIBind *) 0;
186 / 197
Programming interfaces
OCI example (2)
/* Main-Methode */
int main()
{
int p_bvi;
char p_sli1[40];
char p_sli2[40];
int p_sli3;
int rc;
char errbuf[100];
int errcode;
/* OCI initialisieren */
rc = OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
(dvoid * (*)(dvoid *, size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t))0,
(void (*)(dvoid *, dvoid *)) 0 );
187 / 197
Programming interfaces
OCI example (3)
/* Umgebung initialisieren */
rc = OCIEnvInit( (OCIEnv **) &p_env, OCI_DEFAULT, (size_t) 0, (dvoid **) 0 );
/* Handler initialisieren */
rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,
(size_t) 0, (dvoid **) 0);
rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0);
/* Verbindung zu Datenbank herstellen */
rc = OCILogon(p_env, p_err, &p_svc, "dbs2p20",7, "dbs2p20",7, "oralv10a",8);
if (rc != 0) {
OCIErrorGet((dvoid *)p_err, (ub4) 1, (text *) NULL, &errcode, errbuf,
(ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
printf("Error - %.*s\n", 512, errbuf);
exit(8);
}
188 / 197
Programming interfaces
OCI example (4)
/* Statement vorbereiten */
rc = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_sql,
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
rc = OCIStmtPrepare(p_sql, p_err,
"SELECT TITEL, U_ORT, U_JAHR FROM DRAMA WHERE AUTOR=’Schiller’",
(ub4) 61, (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
/* Variable für TITEL binden */
rc = OCIDefineByPos(p_sql, &p_dfn, p_err,
(sword) 40, SQLT_STR, (dvoid *) 0, (ub2
(ub2 *)0, OCI_DEFAULT);
/* Variable für U_ORT binden */
rc = OCIDefineByPos(p_sql, &p_dfn, p_err,
(sword) 40, SQLT_STR, (dvoid *) 0, (ub2
(ub2 *)0, OCI_DEFAULT);
/* Variable für U_JAHR binden */
rc = OCIDefineByPos(p_sql, &p_dfn, p_err,
(sword) sizeof(sword), SQLT_INT, (dvoid
(ub2 *)0, OCI_DEFAULT);
1, (dvoid *) &p_sli1,
*)0,
2, (dvoid *) &p_sli2,
*)0,
3, (dvoid *) &p_sli3,
*) 0, (ub2 *)0,
189 / 197
Programming interfaces
OCI example (5)
/* Statement ausführen */
rc = OCIStmtExecute(p_svc, p_sql, p_err, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT);
/* Ergebnisse ausgeben */
while (rc != OCI_NO_DATA) {
printf("%s %s %i\n",p_sli1, p_sli2, p_sli3);
rc = OCIStmtFetch(p_sql, p_err, 1, 0, 0);
}
/* Verbindung beenden und Handler freigeben */
rc = OCILogoff(p_svc, p_err);
rc = OCIHandleFree((dvoid *) p_sql, OCI_HTYPE_STMT);
rc = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);
rc = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);
return 0;
}
190 / 197
Programming interfaces
ODBC
Open Database Connectivity
I
developed by Microsoft
I
standard database interface
I
development of database-driven applications independet of the
database management system
191 / 197
Programming interfaces
ODBC C# example
using
using
using
using
System;
System.Collections.Generic;
System.Text;
System.Data.Odbc;
namespace TestODBC
{
class Program
{
static void Main(string[] args)
{
// Datenbankverbindung erzeugen...
OdbcConnection DbConnection = new OdbcConnection(
"Driver=Microsoft ODBC for Oracle;Server=ORALV9A;UID=dbs2p20;PWD=dbs2p20
// ...und öffnen
DbConnection.Open();
// Statement erzeugen
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText =
"SELECT TITEL, U_JAHR, U_ORT FROM DRAMA WHERE AUTOR=’Schiller’";
192 / 197
Programming interfaces
ODBC C# example (2)
// Statement ausführen
OdbcDataReader DbReader = DbCommand.ExecuteReader();
// Ergebniszeilen ausgeben
while( DbReader.Read())
{
for (int i = 0; i < DbReader.FieldCount; i++)
{
String col = DbReader.GetString(i);
Console.Write(col + "\t");
}
Console.WriteLine();
}
// Aufräumen
DbReader.Close();
DbCommand.Dispose();
DbConnection.Close();
}
}
}
193 / 197
Programming interfaces
JDBC
Java Database Connectivity
I
developed by Sun Microsystems
I
database interface of the java platform
I
JDBC-ODBC-bridge
194 / 197
Programming interfaces
JDBC example
import java.sql.*;
public class TestJDBC {
public static void main(String[] args) {
try {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
Connection connection = DriverManager.getConnection(
"jdbc:oracle:thin:@mtsthelens.informatik.hs-fulda.de:1521:ORALV9A",
"DBS2P20","DBS2P20");
Statement stmt = connection.createStatement();
ResultSet resultSet = stmt.executeQuery(
"SELECT TITEL, U_JAHR, U_ORT FROM DRAMA WHERE AUTOR=’Schiller’");
while (resultSet.next()) {
System.out.println(resultSet.getString("TITEL") + "\t"
+ resultSet.getInt("U_JAHR") + "\t"
+ resultSet.getString("U_ORT"));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}
195 / 197
Quellen
Christian Böhm
Skript zur Vorlesung Datenbanksysteme
Universität Heidelberg, Institut für Informatik, Lehrstuhl für Datenbanksysteme,
2005
Prof. Dr. Dietmar Seipel
Skript Vorlesung Datenbanken
Universität Würzburg, Institut für Informatik, Lehrstuhl für Informatik I, 2006
Prof. Dr. Gottfried Vossen
Datenmodelle, Datenbanksprachen und Datenbankmanagementsysteme
4. korrigierte und ergänzte Auflage, Oldenbourg Wissenschaftsverlag GmbH,
München, 2000
Prof. Dr.-Ing. Stefan Deßloch
Skript zur Vorlesung Informationssysteme
Technische Universität Kaiserslautern, Fachbereich Informatik, AG Heterogene
Informationssysteme, 2007
196 / 197
Quellen (2)
Prof. Dr. Peter Peinl
Skript zur Vorlesung Datenbanksysteme II
Hochschule Fulda, Fachbereich Angewandte Informatik, 2006
Kevin Loney, George Koch
ORACLE 9i - Die umfassende Referenz
Carl Hanser Verlag, München, Wien, 2003
Badran Farwati
Renntrimm - Tipps für das Datenbank-Tuning
Linux Magazin, 04/2005
197 / 197