Datenbanksysteme I Dr. Gjergji Kasneci (Folien von Prof. Dr. Felix

Transcrição

Datenbanksysteme I
SQL
Dr. Gjergji Kasneci (Folien von Prof. Dr. Felix Naumann)
7.5.2013
SQL – Historie
2
■ SEQUEL
(1974, IBM Research Labs San Jose)
■ SEQUEL2
(1976, IBM Research Labs San Jose)
□ System R
■ SQL
(1982, IBM)
Don Chamberlin, IBM
http://www.almaden.ibm.com/cs/people/
chamberlin/sequel-1974.pdf
Gjergji Kasneci | Datenbanksysteme I | Sommer 2014
SQL – Standardisierung
3
■ SQL1 von ANSI als Standard verabschiedet (1986)
■ SQL1 von der (ISO) als Standard verabschiedet (1987)
□ 1989 nochmals überarbeitet.
■ SQL2 oder SQL-92 von der ISO verabschiedet (1992)
■ SQL3 oder SQL:1999 verabschiedet
□ Trigger, rekursive Anfragen
□ Objektrelationale Erweiterungen
■ SQL:2003 von der ISO verabschiedet
□ XML-Support durch SQL/XML
■ SQL/XML:2006
□ XQuery eingebunden
■ SQL:2008
□ Updates auf Sichten, logisches Löschen (TRUNCATE), …
■ SQL:2011
□ Aktuelle Revision des SQL-Standards.
■ Trotz Standardisierung: Inkompatibilitäten zwischen Systemen der einzelnen
Hersteller
SQL:2008 Struktur
4
■ Part
□
■ Part
□
■ Part
□
■ Part
□
■ Part
□
■ Part
□
■ Part
□
■ Part
□
■ Part
□
1: Framework (SQL/Framework) – 82 Seiten
Überblick
2: Foundation (SQL/Foundation) – 1316 Seiten
Datenmodell, DDL, DML, Abfragen
3: Call-Level Interface (SQL/CLI) – 389 Seiten
Zugriff auf DBMS mittels Funktionsaufrufen aus anderen Programmiersprachen
4: Persistent Stored Modules (SQL/PSM) – 188 Seiten
Prozedurale Erweiterungen
9: Management of External Data (SQL/MED) – 484 Seiten
Neue Datentypen und Funktionen
10: Object Language Bindings (SQL/OLB) – 396 Seiten
Auch (SQLJ); zur Einbettung von SQL in Java
11: Information and Definition Schemas (SQL/Schemata) – 286 Seiten
DBMS werden selbst-beschreibend durch normierten Katalog
13: SQL Routines and Types (SQL/JRT) – 198 Seiten
Externe Java Routinen als „stored procedures“
Zusammen:
14: XML-Related Specifications (SQL/XML) – 438 Seiten
XML Datentyp und Erweiterung von SQL um XQuery
3777 Seiten
Motivation für SQL
5
■ Meist-verbreitete Datenbankanfragesprache
■ Ad-hoc und einfach
■ Deklarativ
□ Nicht prozedural / imperativ
□ Optimierbar
■ Very-High-Level Language
■ Anfragen an relationale Algebra angelehnt
□ Hinzu kommt DDL: Data definition language
□ Hinzu kommt DML: Data manipulation language
■ Achtung: Syntax kann sich von System zu System leicht
unterscheiden.
■ Achtung: Funktionalität kann sich von System zu System leicht
unterscheiden.
SQL Referenzen
6
■ DBMS-spezifische Webseiten
■ DB2
□ http://publib.boulder.ibm.com/infocenter/db2help/
□ SQL Reference Volume 1:
http://public.dhe.ibm.com/ps/products/db2/info/vr101/pdf/en_US
/DB2SQLRefVol1-db2s1e1011.pdf
□ SQL Reference Volume 2:
http://public.dhe.ibm.com/ps/products/db2/info/vr101/pdf/en_US
/DB2SQLRefVol2-db2s2e1011.pdf
■ Oracle
□ http://docs.oracle.com/cd/B19306_01/server.102/b14200/toc.ht
m
■ MS SQL Server
□ http://msdn.microsoft.com/enus/library/ms181080(v=sql.105).aspx
Überblick
7
■ Einfache Anfragen
■ Anfragen über mehrere Relationen
■ Geschachtelte Anfragen
■ Operationen auf einer Relation
■ Datenbearbeitung (DML)
■ Schemata (DDL)
■ Sichten
Beispielschema
8
Studio
Film
Titel
spielt_in
Jahr
Länge
FilmTitel
Schauspieler
Name
inFarbe
FilmJahr
Adresse
Manager
Name
Adresse
StudioName
VorsitzenderID
ProduzentID
Name
Geschlecht
Name
Adresse
Geburtstag
ManagerID
Gehalt
SELECT … FROM … WHERE …
9
■ SELECT *
FROM
Film
WHERE StudioName = ‘Disney‘ AND Jahr= 1990;
■ Lesereihenfolge (und Schreibreihenfolge):
1. FROM: Relation(en) aus denen die Daten stammen
2. WHERE: Bedingung(en) an die Daten
3. SELECT: Schema der Ergebnisrelation
◊ *: Alle Attribute der Inputrelationen
■ Ausführung
□ Für jedes Tupel aus „Film“ prüfe die Bedingungen und gebe
gültige Tupel aus.
Groß- und Kleinschreibung
10
■ In SQL wird Groß- und Kleinschreibung nicht beachtet
□ From = FROM = from = FrOm
■ Auch bei Attribut- und Relationennamen
□ SELect vorNAMe fROm fiLM = SELECT vorname FROM film
■ Natürlich nicht bei Konstanten:
□ ‘FROM‘ ≠ ‘from‘ ≠ from
■ Konvention zur Lesbarkeit
□ Schlüsselworte großschreiben
□ Schemaelemente kleinschreiben
Projektion in SQL (SELECT, )
11
Spezifikation in der SELECT Klausel
■ SELECT * FROM FILM
□ Alle Attribute
■ SELECT Titel, Jahr, inFarbe FROM FILM
□ Projektion auf die drei Attribute
Erweiterte Projektion
■ Umbenennung:
□ SELECT Titel AS Name, Jahr AS Zeit FROM FILM
■ Arithmetischer Ausdruck:
□ SELECT Titel, Länge * 0.016667 AS Stunden FROM FILM
■ Konstanten:
□ SELECT Titel, Länge * 0.016667 AS Stunden,
‘std.‘ AS inStunden FROM FILM
Selektion in SQL (WHERE, )
12
Spezifikation in der WHERE Klausel
■ Bedingungen wie in einer Programmiersprache
■ Sechs Vergleichsoperatoren
□ =, <>, <, >, <=, >=
□ <>, <=, >= entspricht ≠, , 
■ Operanden
□ Konstanten und Attributnamen
□ Auch Attribute, die nicht in der SELECT Klausel genannt
werden.
■ Arithmetische Ausdrücke für numerische Attribute
□ Z.B.: (Jahr - 1930) * (Jahr - 1930) <= 100
■ Konkatenation für Strings
□ ‘Star‘ || ‘Wars‘ entspricht ‘StarWars‘
Selektion in SQL
13
Ergebnis ist stets Boole‘scher Wert
■ TRUE oder FALSE
■ Können mit AND, OR und NOT verknüpft werden.
□ Klammerungen sind erlaubt.
■ Nur wenn insgesamt auf TRUE evaluiert wird, erscheint das
entsprechende Tupel im Ergebnis.
Beispiele
■ SELECT Titel
FROM Film
WHERE Jahr > 1970 AND NOT inFarbe;
■ SELECT Titel
FROM Film
WHERE (Jahr > 1970 OR Länge < 90) AND StudioName = ‘MGM‘;
Stringvergleiche
14
■ Datentypen
□ Array fester Länge, Buchstabenliste variabler Länge,
Konstanten
□ SQL erlaubt viele Vergleiche über Datentypen hinweg
■ foo _ _ _ _ _ = foo = ‘foo‘
■ Vergleiche mit <, >, <=, >=
□ Lexikographischer Vergleich
□ ‘fodder‘ < ‘foo‘; ‘bar‘ < ‘bargain‘;
□ Sortierreihenfolge upper-case/lower-case usw. je nach DBMS
String-Mustervergleiche mit LIKE
15
■ string LIKE pattern
□ bzw. string NOT LIKE pattern
■ Pattern hat spezielle Zeichen
□ ‘%‘: Beliebige Sequenz von 0 oder mehr Zeichen
□ ‘_‘: Ein beliebiges Zeichen
■ SELECT Titel FROM Film
WHERE Titel LIKE ‘Star
_ _ _ _‘;
□ Star Wars und Star Trek
■ SELECT Titel FROM Film WHERE Titel LIKE ‘%War%‘;
Datum und Uhrzeit
16
Spezielle Datentypen und Repräsentationen
■ Datumskonstante:
□ DATE ‚YYYY-MM-DD‘
□ DATE ‚1948-05-14‘
■ Zeitkonstante
□ TIME ‚HH:MM:SS.S‘
□ TIME ‚15:00:02.5‘
■ Zeitstempel
□ TIMESTAMP ‚1948-05-14 15:00:02.5‘
■ Zeitvergleiche
□ TIME ‚15:00:02.5‘ < TIME ‚15:02:02.5‘ ergibt TRUE
□ Selektion: ERSCHEINUNGSTAG >= DATE ‚1949-11-12‘
Nullwerte
17
■ Darstellung: NULL bzw. ⊥
■ Mögliche Interpretationen
□ Unbekannter Wert
◊ Geburtstag eines
Schauspielers
□ Wert unzulässig
◊ Ehegatte eines
unverheirateten
Schauspielers
□ Wert unterdrückt
◊ Geheime
Telefonnummer
■ Regeln für Umgang mit Nullwerten
□ Arithmetische Operationen mit NULL
ergeben NULL
□ Vergleich mit NULL ergibt
Wahrheitswert UNKNOWN
□ NULL ist keine Konstante, sondern
erscheint nur als Attributwert
■ Beispiel (sei der Wert von x NULL):
□ x+3 ergibt NULL.
□ NULL+3 ist kein zulässiger
Ausdruck.
□ x = 3 ergibt UNKNOWN.
■ Prüfen von Nullwerten in WHERE Klausel
□ Geburtstag IS NULL
□ bzw. Geburtstag IS NOT NULL
Wahrheitswerte
18
AND
true
unknown
false
true true
unknown
false
unknown unknown
unknown
false
false
false
false false
OR
true
unknown
false
true
true
unknown true
unknown
unknown
false true
unknown
false
true true
NOT
true false
unknown unknown
false true
Wahrheitswerte
19
AND
true
unknown
false
true true
unknown
false
unknown unknown
unknown
false
false
false
false false
OR
true
unknown
false
true
true
unknown true
unknown
unknown
false true
unknown
false
true true
NOT
true false
unknown unknown
false true
Rechenregeln
■ TRUE
=1
FALSE
=0
UNKNOWN = ½
■ AND: Minimum der beiden
Werte
■ OR: Maximum der beiden Werte
■ NOT: 1 – Wert
■ Beispiel
□ TRUE AND (FALSE OR
NOT(UNKNOWN))
= MIN(1, MAX(0, (1 - ½ )))
= MIN(1, MAX(0, ½ )
= MIN(1, ½ ) = ½.
Wahrheitswerte
20
Titel
Jahr
Länge
inFarbe
Studio
ProduzentID
Total Recall
1990
NULL
True
Fox
12345
■ Überraschendes Verhalten
□ SELECT *
FROM Film
WHERE Länge <= 90 OR Länge > 90;
UNKNOWN
UNKNOWN
UNKNOWN
□ Tupel erscheint nicht im Ergebnis.
■ Ausführungspriorität: NOT vor AND vor OR
Sortierung
21
ORDER BY Klausel ans Ende der Anfrage
■ ORDER BY <Attributliste> DESC/ASC
■ ASC (aufsteigend) ist default
■ SELECT *
FROM
Film
WHERE StudioName = ‘Disney‘ AND Jahr = 1990
ORDER BY Länge, Titel;
■ SELECT *
FROM
Film
WHERE StudioName = ‘Disney‘ AND Jahr = 1990
ORDER BY Länge ASC, Titel DESC;
Überblick
22
■ Schemata (DDL)
■ Sichten
Motivation
23
■ Hauptstärke der Relationalen Algebra ist die Kombination von
Relationen
■ Erst mit mehreren Relationen sind viele interessante Anfragen
möglich.
■ Nennung der beteiligten Relationen in der FROM Klausel
Kreuzprodukt und Join
24
■ Film(Titel, Jahr, Länge, inFarbe, StudioName, ProduzentID)
■ Manager(Name, Adresse, ManagerID, Gehalt)
■ SELECT
FROM
WHERE
AND
Name
Film, Manager
Titel = ‘Star Wars‘
ProduzentID = ManagerID;
Kreuzprodukt
Selektionsbedingung
Joinbedingung
■ Semantik
□ Betrachte jedes Tupelpaar der Relationen Film und Manager.
□ Wende Bedingung der WHERE Klausel auf jedes Tupelpaar an
□ Falls Bedingung erfüllt, produziere ein Ergebnistupel.
■ Kreuzprodukt gefolgt von Selektion: Join
Uneindeutige Attributnamen
25
■ Schauspieler(Name, Adresse, Geschlecht, Geburtstag)
Bei gleichen Attributnamen aus mehreren beteiligten Relationen:
■ Relationenname als Präfix:
□ SELECT Schauspieler.Name, Manager.Name
FROM
Schauspieler, Manager
WHERE Schauspieler.Adresse = Manager.Adresse;
■ Präfix ist auch erlaubt wenn Attributname eindeutig ist.
□ Erleichtert das Lesen von SQL Anfragen
Tupelvariablen
26
■ Zur eindeutigen Kennzeichnung von Tupeln beteiligter Relationen
□ „Alias“ einer Relation
□ Insbesondere: Bei der mehrfachen Verwendung einer Relation
in einer Anfrage
■ Gesucht: Schauspieler, die zusammen leben
□ SELECT Star1.Name, Star2.Name
FROM
Schauspieler Star1, Schauspieler Star2
WHERE Star1.Adresse = Star2.Adresse
■ Auch sinnvoll als abkürzenden Schreibweise
Äquivalent zu
Schauspieler AS Star2
□ SELECT S.Name, M.Name
FROM
Schauspieler S, Manager M
WHERE S.Adresse = M.Adresse;
■ Ohne explizites Angeben einer Tupelvariablen wird der
Relationenname als Tupelvariable verwendet.
Tupelvariablen – Selfjoin
27
Name
Adresse
Carrie Fisher
123 Maple St., Hollywood
F
9/9/99
Mark Hamill
456 Oak Rd., Brentwood
M
8/8/88
Brad Pitt
123 Maple St., Hollywood
M
7/7/77
■ SELECT Star1.Name, Star2.Name
FROM
WHERE Star1.Adresse = Star2.Adresse;
■ SELECT
FROM
WHERE
AND
Star1.Name, Star2.Name
Star1.Adresse = Star2.Adresse
Star1.Name <> Star2.Name;
■ SELECT
FROM
WHERE
AND
Star1.Name, Star2.Name
Star1.Adresse = Star2.Adresse
Star1.Name < Star2.Name;
Geschlecht
Geburt
Star1.Name
Star2.Name
Carrie Fisher
Carrie Fisher
Carrie Fisher
Brad Pitt
Brad Pitt
Carrie Fisher
Brad Pitt
Brad Pitt
Mark Hamill
Mark Hamill
Star1.Name
Star2.Name
Carrie Fisher
Brad Pitt
Brad Pitt
Carrie Fisher
Star1.Name
Star2.Name
Brad Pitt
Carrie Fisher
Interpretation von Anfragen
28
Drei Interpretationsvarianten für Anfragen mit mehreren Relationen
■ Nested Loops (geschachtelte Schleifen)
□ Bei mehreren Tupelvariablen: Eine geschachtelte Schleife für
jede Variable
■ Parallele Zuordnung
□ Alle Kombinationen werden parallel bezüglich der Bedingungen
geprüft.
■ Relationale Algebra
□ Bilde Kreuzprodukt
□ Wende Selektionsbedingungen auf jedes Resultat-Tupel an
Interpretation von Anfragen
29
■ Gegeben drei Relationen: R(A), S(A) und T(A)
■ Gesucht: R  (S  T)
□ SELECT
FROM
WHERE
OR
(= (R  S)  (R  T) )
R.A
R, S, T
R.A = S.A
R.A = T.A;
■ Problemfall: T ist leer, hat also kein Tupel
■ Vermeintliches Resultat: R  S
■ Tatsächliches Resultat: leere Menge
□ Ausführung als
drei geschachtelte Schleifen
SELECT *
FROM
(
(SELECT A FROM R)
INTERSECT
(SELECT * FROM
(SELECT A FROM S)
UNION
(SELECT A FROM T)
)
)
Joins
30
Man kann Joins auch auf andere Weise ausdrücken.
■ Geschmacksfrage
■ Film CROSS JOIN spielt_in
□ Kreuzprodukt
□ Doppelte Attributnamen werden mit Präfix der Relation aufgelöst
■ Film JOIN spielt_in
ON Titel = FilmTitel AND Jahr = FilmJahr
□ Theta-Join
□ SELECT Titel, Jahr, Länge, inFarbe, StudioName,
ProduzentID, SchauspielerName
FROM
Film JOIN spielt_in ON Titel = FilmTitel
AND Jahr = FilmJahr;
□ Eliminiert redundante Attribute FilmTitel und FilmJahr
■ Schauspieler NATURAL JOIN Manager
□ Natural Join; Eliminiert redundante Attribute
Outer Joins
31
■ Schauspieler, die zugleich Manager sind
□ SELECT Name, Adresse, Geburtstag, Gehalt
FROM Schauspieler NATURAL INNER JOIN Manager
■ Schauspieler und gegebenenfalls ihre Managerinfo
□ …FROM Schauspieler NATURAL LEFT OUTER JOIN Manager
□ Gehalt bleibt gegebenenfalls NULL
■ Manager und gegebenenfalls ihre Schauspielerinfo
□ …FROM Schauspieler NATURAL RIGHT OUTER JOIN Manager
□ Geburtstag bleibt gegebenenfalls NULL
■ Alle Schauspieler und Manager
□ …FROM Schauspieler NATURAL FULL OUTER JOIN Manager
□ Geburtstag oder Gehalt bleiben gegebenenfalls leer
□ Unterschied zu UNION: Nur eine Zeile pro Person
Outer Joins
32
Manager
Schauspieler
Geburtstag
Name
1.2.1960 Mark Hamill
27.5.1969 Carrie Fischer
11.12.1940 Alec Guinness
Adresse
Gehalt
LA
NULL
New York
NULL
London
NULL
22.3.1981 Ben Affleck
Boston
23.8.1973 Quentin Tarantino
Berlin
5Mio
10Mio
NULL
George Lukas
San Jose
100Mio
NULL
Steven Spielberg
LA
500Mio
Outer Joins
33
■ Alle Filme mit Schauspielern
□ Film JOIN spielt_in
ON Titel = FilmTitel AND Jahr = FilmJahr;
■ Alle Filme
□ Film FULL OUTER JOIN spielt_in
■ Alle Filme, gegebenenfalls mit Schauspieler
□ Film LEFT OUTER JOIN spielt_in
Kreuzprodukt
34
■ Alle Paare aus Tupeln der beteiligten Relationen
□ SELECT *
FROM Schauspieler CROSS JOIN Film
□ SELECT *
FROM Schauspieler, Film
■ Selten verwendet
■ Grundbaustein für Joins
Mengenoperationen in SQL
35
■ Vereinigung: UNION
■ Schnittmenge: INTERSECT
■ Differenz: EXCEPT
■ Mengenoperationen nur zwischen geklammerten
Anfrageergebnissen
■ Mengenoperationen haben implizit eine Mengensemantik
□ Wandeln Input-Relationen in Mengen um
□ Wandeln Output in Menge um
■ Anfrageergebnisse müssen gleiche Schemata haben
□ Gleiche Attributnamen
□ Gleiche Datentypen
Schnittmenge: INTERSECT
36
■ Entspricht dem logischen „und“
■ (SELECT Name, Adresse FROM Schauspieler)
INTERSECT
(SELECT Name, Adresse FROM Manager);
■ (SELECT Name, Adresse
FROM
Schauspieler
WHERE Geschlecht = ‘F‘)
INTERSECT
(SELECT Name, Adresse
FROM
Manager
WHERE Gehalt > 1.000.000)
■ Multimengen-Semantik: INTERSECT ALL
Vereinigung: UNION
37
■ Entspricht dem logischen „oder“
■ (SELECT Name, Adresse FROM Schauspieler)
UNION
(SELECT Name, Adresse FROM Manager);
■ Multimenge: UNION ALL
□ Beliebt, da schnell
□ Verwenden falls
◊ Semantik egal
◊ Multimengensemantik erwünscht
◊ Mengeneigenschaft von Input und Output bekannt
Differenz: EXCEPT
38
■ Auch MINUS
■ (SELECT Titel, Jahr
FROM
Film)
EXCEPT
(SELECT FilmTitel AS Titel, FilmJahr AS Jahr
FROM
spielt_in)
■ Multimenge: EXCEPT ALL
Klammerung
39
SELECT *
FROM
(
(SELECT A FROM R)
INTERSECT
(SELECT * FROM
(SELECT A FROM S)
UNION
(SELECT A FROM T)
)
)
Zusammenfassung der Semantik
40
R1
R2
1
1
1
2
2
2
3
4
4
5
1
1
3
3
3
3
4
UNION
ALL
UNION
EXCEPT
ALL
EXCEPT
INTERSECT
ALL
1
1
3
4
INTERSECT
1
1
1
2
1
1
2
2
5
3
1
3
2
4
1
4
2
1
5
4
2
5
2
2
3
3
3
3
3
4
4
http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topi
4
c/com.ibm.db2.luw.sql.ref.doc/doc/r0000877.html
5
Überblick
41
■ Schemata (DDL)
■ Sichten
Motivation
42
Eine Anfrage kann Teil einer anderen Anfrage sein.
■ Theoretisch beliebig tiefe Schachtelung
Drei Varianten
1. Subanfrage erzeugt einen einzigen Wert, der in der WHEREKlausel mit einem anderen Wert verglichen werden kann.
2. Subanfrage erzeugt eine Relation, die auf verschiedene Weise in
WHERE-Klausel verwendet werden kann.
3. Subanfrage erzeugt eine Relation, die in der FROM Klausel
verwendet werden kann.
□ Wie jede normale Relation
Skalare Subanfragen
43
Allgemeine Anfragen produzieren Relationen.
■ Mit mehreren Attributen
□ Zugriff auf ein bestimmtes Attribut ist möglich
■ i.A. mit mehreren Tupeln
■ Manchmal (garantiert) nur ein Tupel und Projektion auf nur ein
Attribut
□ „Skalare Anfrage“
□ Verwendung wie eine Konstante möglich
SUM
17
Skalare Subanfragen
44
■ Film(Titel, Jahr, Länge, inFarbe, StudioName, ProduzentID)
■ Gesucht: Produzent von Star Wars
□ SELECT Name
FROM
Film, Manager
WHERE Titel = ‘Star Wars‘ AND Jahr = ‘1977‘
AND
ProduzentID = ManagerID;
■ Oder aber
□ SELECT Name
FROM
Manager
WHERE ManagerID =
( SELECT ProduzentID
FROM
Film
WHERE Titel = ‘Star Wars‘ AND Jahr = ‘1977‘ );
■ DBMS erwartet genau ein Tupel als Ergebnis der Teilanfrage
□ Falls kein Tupel: Laufzeitfehler
□ Falls mehr als ein Tupel: Laufzeitfehler
Skalare Subanfragen – Beispiel
45
Abteilungen, deren durchschnittliche Bonuszahlungen höher sind als
deren durchschnittliches Gehalt.
SELECT a.Name, a.Standort
FROM
Abteilung a
WHERE (SELECT AVG(bonus)
FROM
personal p
WHERE a.AbtID = p.AbtID)
>
(SELECT AVG(gehalt)
FROM
personal p
Skalare Subanfragen – Beispiel
46
■ Alle Potsdamer Abteilungen mit ihrem Maximalgehalt.
□ SELECT a.AbtID, a.Name,
(SELECT MAX(Gehalt)
FROM
Personal p
WHERE a.AbtID = p.AbtID) AS maxGehalt
FROM
Abteilung a
WHERE a.Ort = ‘Potsdam‘
■ Anmerkung: Auch Abteilungen ohne Mitarbeiter erscheinen im
Ergebnis.
■ Nicht so in der folgenden Anfrage:
□ SELECT a.AbtID, a.Name, MAX(p.Gehalt) AS maxGehalt
FROM
Abteilung a, Personal p
WHERE a.Ort = ‘Potsdam‘
AND
a.AbtID = p.AbtID
GROUP BY a.AbtID, a.Name
Bedingungen mit Relationen
47
Bestimmte SQL Operatoren auf Relationen erzeugen Boole‘sche Werte
■ EXISTS R
□ TRUE, falls R nicht leer
■ x IN R
□
□
□
■ x >
TRUE falls x gleich einem Wert in R ist (R hat nur ein Attribut)
Verallgemeinerung auf Tupel später
x NOT IN R: TRUE falls x keinem Wert in R gleicht
ALL R
□
□
□
■ x >
TRUE falls x größer als jeder Wert in R ist (R hat nur ein Attribut)
Alternativ: <, >, <=, >=, <>, =
x <> ALL R: Entspricht x NOT IN R bzw. auch NOT(x in R)
ANY R
□ TRUE falls x größer als mindestens ein Wert in R ist (R hat nur ein Attribut)
□ Alternativ: <, >, <=, >=, <>, =
□ x = ANY R: Entspricht x IN R
□ Alternativer Befehl: SOME
■ Negation mit NOT(…) ist immer möglich.
EXISTS Beispiele
48
■ Die ISBNs aller ausgeliehenen Bücher
□ SELECT ISBN
FROM
BuchExemplar
WHERE EXISTS
(SELECT *
FROM Ausleihe
WHERE Ausleihe.Inventarnr = BuchExemplar.Inventarnr)
EXISTS Beispiele
49
■ Lehrstuhlbezeichnungen der Professoren, die alle von ihnen
gelesenen Vorlesungen auch schon einmal geprüft haben.
■ bzw. Lehrstuhlbezeichnungen von Professoren, so dass keine von
diesem gelesene Vorlesung existiert, die von ihm nicht geprüft
wurde.
□ SELECT Lehrstuhlbezeichnung
FROM
Prof
WHERE NOT EXISTS
(SELECT *
FROM
Liest
WHERE Liest.PANr = Prof.PANr
AND NOT EXISTS (SELECT *
FROM Prüft
WHERE Prüft.PANr = Prof.PANr
AND
Prüft.VL_NR = Liest.VL_NR)
)
IN Beispiele
50
■ Eine Auswahl an Büchern
□ SELECT Titel
FROM Bücher
WHERE ISBN IN
(‘3898644006‘, ‘1608452204‘, ‘0130319953‘)
■ Matrikel der Studenten, die zumindest einen Prüfer gemeinsam
mit dem Studenten der Matrikel ‚123456‘ haben
□ SELECT Matrikel
FROM
Prüft
WHERE Prüfer IN ( SELECT Prüfer
FROM Prüft
WHERE Matrikel = ‘123456‘)
IN Beispiele
51
■ Nachnamen aller Professoren, die schon einmal eine 1,0 vergeben
haben.
□ SELECT Nachname
FROM
Prof
WHERE 1.0 IN ( SELECT Note
FROM
Prüft
WHERE Prüfer = Prof.ID )
■ Achtung: Korrellierte Subanfrage
ALL und ANY Beispiele
52
■ Die schlechteste Note des Studenten mit Matrikel 123456
□ SELECT
FROM
WHERE
AND
Note
Prüft
Matrikel = ‘123456‘
Note >= ALL (SELECT Note
FROM
Prüft
WHERE Matrikel = ‘123456‘
■ All Studenten, die mindestens eine Prüfung absolvierten
□ SELECT Name, Matrikel
FROM
Student
WHERE Matrikel = ANY (SELECT Matrikel
FROM
Prüft)
Bedingungen mit Tupeln
53
Verallgemeinerung von IN, ALL und ANY auf Tupel
■ t IN R
□ TRUE falls t ein Tupel in R ist (mehr als ein Attribut möglich)
□ Setzt gleiche Schemata voraus
□ Setzt gleiche Reihenfolge der Attribute voraus
■ t > ALL R
□ TRUE falls t größer als jedes Tupel in R ist
□ Vergleiche in Standardreihenfolge der Attribute
■ t <> ANY R
□ TRUE falls R mindestens ein Tupel hat, das ungleich t ist
Bedingungen mit Tupeln
54
■ Namen von Produzenten von Filmen mit Harrison Ford
□ SELECT Name
FROM
Manager
WHERE ManagerID IN
( SELECT ProduzentID
FROM
Film
WHERE (Titel, Jahr) IN
( SELECT FilmTitel AS Titel, FilmJahr AS Jahr
FROM
spielt_in
WHERE SchauspielerName = ‘Harrison Ford‘
));
■ Analyse am besten von innen nach außen
■ Alternative Formulierung
□ SELECT
FROM
WHERE
AND
AND
AND
Name
Manager, Film, spielt_in
ManagerID = ProduzentID
Titel = FilmTitel
Jahr = FilmJahr
SchauspielerName = ‘Harrison Ford‘;
Subanfragen in FROM-Klausel
55
Bisher: Nur Subanfragen in WHERE-Klausel
■ Anstelle einfacher Relation steht eine geklammerte Subanfrage
■ Es muss ein Alias vergeben werden.
□ SELECT M.Name
FROM Manager M, (SELECT ProduzentID AS ID
FROM
Film, spielt_in
WHERE Titel = FilmTitel
AND
Jahr = FilmJahr
AND
Schauspieler = ‘Harrison Ford‘)
Produzent
WHERE M.ManagerID = Produzent.ID;
Korrelierte Subanfragen
56
■ Unkorrelliert: Subanfragen einmalig ausführen und das Ergebnis
weiterverwenden
■ Korrelierte Subanfragen werden mehrfach ausgeführt, einmal pro
Bindung der korrelierten Variable der äußeren Anfrage
■ Alle mehrfachen Filme mit Ausnahme der jeweils jüngsten
Ausgabe
Scope: Attributnamen
□ SELECT Titel, Jahr
gehören zunächst zur
FROM Film Alt
Tupelvariablen der aktuellen
WHERE Jahr < ANY
Anfrage. Sonst: Suche von
innen nach außen.
( SELECT Jahr
FROM Film
WHERE Titel = Alt.Titel);
□ Ausführung der Subanfrage für jedes Tupel in Filme
Korrelierte Subanfragen – Beispiel
57
Unkorreliert:
Name und Gehalt aller Mitarbeiter
in Potsdam
Korreliert:
SELECT Name, Gehalt
FROM Personal p
WHERE AbtID IN
SELECT Name, Gehalt
FROM
Personal p
WHERE Gehalt >
(SELECT 0.1*Budget
FROM Abteilung a
(SELECT AbtID
FROM
Abteilung
WHERE Ort = ‘Potsdam‘)
Name und Gehalt aller
Mitarbeiter, deren Gehalt höher
als 10% des Abteilungsbudgets
ist.
Überblick
58
■ Schemata (DDL)
■ Sichten

Datenbanksysteme I Dr. Gjergji Kasneci (Folien von Prof. Dr. Felix

Transcrição

Documentos relacionados

Select English, London - Business Programm

438: `Hälfte des Lebens`. Zwei Übersetzungen. Von Michael

BVT erhält KVG Zulassung - Rohmert

Business Intelligence - FG-DB

EUROPRO SECTION VENTE

5. Datenabfrage mit SQL

Piet Veerman - Sailin` Home - The Town where you was born

Planer HIGHLIGHTS: 3. September - 16.September

Manipulationsmöglichkeit im Rahmen der SAP - Revision

Haus der Jugend LEMON - Ferienbörse Rheinland