SELECT
Werbung
5.1 Allgemeines zu SQL ......................................................... 5
5.2 SQL-DDL: Definition eines Datenbankschemas ..............
8
5.3 SQL-DML: Abfragen in SQL (Query Language) .............. 15
5.4 SQL-DML: Mutationen in SQL (Insert, Update, Delete) … 65
01.10.2007 AIFB
SQL - Structured Query Language
5 SQL – Structured Query Language
1
(1|3)
Entwicklungsziele:
möglichst einfach für den Endbenutzer;
möglichst deskriptiv, nicht prozedural
Benennung:
SQL: „structured query language"
ursprünglich: SEQUEL – „structured english query language"
Entwicklung: (im IBM-Labor)
basiert auf relationaler Algebra und Tupelkalkül
erste Vorschläge 1974; weiterentwickelt 1976-77
Implementierungen 1977 im System R (Prototyp eines rel. DBS)
Erste kommerzielle Implementierungen:
Oracle: 1980
IBM: SQL/DS (1981), DB2 (1983)
01.10.2007 AIFB
SQL - Structured Query Language
5.1 Allgemeines zu SQL
2
(2|3)
Standardisierung
SQL-86 (1986): erster Standardisierungsversuch
SQL-89 (1989): Lücken (z.B.:
Fremdschlüssel) von SQL-86 z.T. geschlossen;
SQL-92 (1992): Aktueller, umfassender Standard;
drei Standardisierungslevels
(entry, intermediate, full level);
Der Entry Level wird von den meisten Datenbanken
unterstützt. Der Intermediate Level im vollen
Sprachumfang von keiner einzigen.
SQL-99 (1999): Weiterentwicklung mit objekt-orientierten Ansätzen;
in kommerziellen Produkten nur teilweise implementiert;
Gliederung in Core-SQL (Sprachkern) und
verschiedenen Packages, die aus Features bestehen.
Sprachumfang:
SQL deckt die Aspekte der DDL und der DML (Abfragen, Mutationen) ab.
01.10.2007 AIFB
SQL - Structured Query Language
5.1 Allgemeines zu SQL
3
(3|3)
Schnittstellen:
SQL kann sowohl interaktiv,
als auch eingebettet in einer konventionellen
Programmiersprache (z.B. C, JAVA, …) verwendet werden.
Terminologie:
table
–
Relation
row
–
Tupel
column
–
Attribut
relationales Modell:
SQL ist eine Umsetzung des relationalen Modells, wobei es neben den
eigentlichen Tabellen (set of rows) auch uneigentliche Tabellen (multi-set
of rows) gibt. Bei diesen können Tupel mehrfach auftreten, was dem
Mengenbegriff widerspricht.
01.10.2007 AIFB
SQL - Structured Query Language
5.1 Allgemeines zu SQL
4
5.1
Allgemeines zu SQL ........................................................
5
5.2
SQL-DDL: Definition eines Datenbankschemas .............
8
4.2.1 Definition von Basistabellen ................................... 10
4.2.2 Datentypen und benutzerdefinierte Domänen ....... 11
4.2.3 Ein Beispiel für ein Datenbankschema ..................
13
4.2.4 Definition von Sichten ............................................ 14
5.3
SQL-DML: Abfragen in SQL (Query Language) ............. 15
5.4
SQL-DML: Mutationen in SQL .......................................
65
01.10.2007 AIFB
SQL - Structured Query Language
5 SQL – Structured Query Language
5
Datenbank
Benutzersichten
: Menge von Basistabellen
: Views
Übersicht: SQL-DDL (vereinfacht)
Anweisungen
Bedeutung
CREATE
DROP
CREATE
ALTER
SCHEMA
SCHEMA
TABLE
TABLE
DROP
CREATE
DROP
CREATE
DROP
TABLE
VIEW
VIEW
DOMAIN
DOMAIN
Anlegen eines Datenbankschemas
Löschen eines Schemas
Definition einer Basistabelle
Ändern einer Basistabelle
(Hinzufügen von Attributen)
Löschen einer Basistabelle
Definition einer Sicht
Löschen einer Sicht
benutzerdefinierte „Domain“ anlegen
benutzerdefinierte „Domain“ löschen
Die CREATE TABLE Anweisung ist davon die wichtigste.
01.10.2007 AIFB
SQL - Structured Query Language
5.2 Definition eines Datenbankschemas (SQL-DDL)
6
Definition einer Relation:
CREATE TABLE table-name
({column-definition | table-constraint}[, {column-definition | table-constr.}…]);
definiert einen Relationstyp und legt gleichzeitig eine entsprechende Relation
(Instanz) an („Schema einer Relation“).
SQL kennt nicht den benannten Relationstyp
Spaltendefinition:
column-definition:
column-name {data-type | domain-name} [column-constraint …]
column-constraint:
NOT NULL | UNIQUE | PRIMARY KEY | reference-constraint
reference-constraint:
REFERENCES table-name [(reference-column)]
Constraints über mehrere Attribute: (table-constraint)
UNIQUE (column-list)
PRIMARY KEY (column-list)
FOREIGN KEY (referencing-columns)
REFERENCES table-name [(column-list)]
01.10.2007 AIFB
SQL - Structured Query Language
5.2.1 Definition von Basistabellen
7
(1/2)
SQL bietet folgende (Basis-)Datentypen: (Auswahl)
Beschreibung
Zeichen(ketten):
Ein einzelnes Zeichen
Kette fester Länge
Kette variabler Länge
Bit-Datentyp:
Ein Bit
Datentyp
CHARACTER
CHARACTER (n)
CHARACTER VARYING (n)
CHAR
CHAR (n)
∗)
VARCHAR (n)
BIT
Bitfolge fester Länge
Exakte Zahlen:
Bitfolge variabler Länge
mit Nachkommastellen
dto.
BIT (n)
Ganzzahl
kleine Ganzzahl
Gleitkommazahlen:
INTEGER
SMALLINT
BIT VARYING (n)
DECIMAL [(p,[,s])]
NUMERIC [(p,[,s])]
hohe Genauigkeit
DOUBLE PRECISION
benutzerdefinierte Gen.
FLOAT (n)
geringere Genauigkeit
REAL
*) n=integer, p, s ebenso.
Kurzform
DEC(p,s)
INT
01.10.2007 AIFB
SQL - Structured Query Language
5.2.2 Datentypen und benutzerdefinierte Domänen
8
Beschreibung
Zeit, Datum, Zeitintervall:
Datentyp
Datum (y,m,d)
DATE
Zeitpunkt (h,min,sec)
TIME
Zeitpunkt (y,m,d,h, min, sec)
Zeitintervall
TIMESTAMP
INTERVAL f
INTERVAL sf to ef
f, sf, ef ∈ {YEAR, MONTH, DAY,
HOUR, MINUTE, SECOND}
(„sf>ef“)
(2/2)
Kurzform
In SQL können benutzerdefinierte Datentypen als Einschränkung der
vorgegebenen Datentypen definiert werden.
Beispiel 5-1:
Prozentangaben sind Gleitkommawerte zwischen 0 und 100.
CREATE DOMAIN ProzentAngabe AS REAL
CONSTRAINT ProzAngabeConstraint
CHECK ( VALUE >= 0.0 AND VALUE <= 100.0);
01.10.2007 AIFB
SQL - Structured Query Language
5.2.2 Datentypen und benutzerdefinierte Domänen
9
CREATE SCHEMA Projektverwaltung;
CREATE DOMAIN PersonalNr AS INTEGER;
CREATE DOMAIN ProjektNr AS INTEGER;
CREATE DOMAIN ProzentAngabe AS ...
CREATE TABLE
(ANG-NR
NAME
WOHNORT
ABT-NR
angestellte
PersonalNr PRIMARY KEY,
VARCHAR (30),
VARCHAR (30),
INTEGER);
CREATE TABLE
(P-NR
P-NAME
P-FILIALE
P-LEITER
projekt
ProjektNr PRIMARY KEY,
VARCHAR (15),
VARCHAR (30),
PersonalNr REFERENCES angestellte(ANG-NR));
CREATE TABLE
ang-pro
(P-NR
ProjektNr REFERENCES projekt (P-NR),
ANG-NR
PersonalNr REFERENCES angestellte (ANG-NR),
PROZ-ARBZEIT
ProzentAngabe,
PRIMARY KEY (P-NR, ANG-NR)).
01.10.2007 AIFB
SQL - Structured Query Language
5.2.3 Ein Beispiel für ein Datenbankschema
10
CREATE VIEW table-name
[(column-name [,column-name] …)]
AS query expression
[WITH CHECK OPTION];
([WITH CHECK OPTION]: bei Mutationen: Prüfung ob eingefügte
oder geänderte Tupel zu View gehören.)
Beispiel 5-2:
CREATE VIEW angestellteAusKarlsruhe
AS SELECT ANG-NR, NAME, WOHNORT, ABT-NR
FROM
angestellte
WHERE WOHNORT = ‘Karlsruhe‘;
01.10.2007 AIFB
SQL - Structured Query Language
5.2.4 Definition von Sichten
11
5.1
Allgemeines zu SQL ............................................................................
5
5.2
SQL-DDL: Definition eines Datenbankschemas ...................................
8
5.3
SQL-DML: Abfragen in SQL (Query Language) ...................................
15
5.3.1
Die Syntax der SELECT-Abfrage .......................................
18
5.3.2
Abfragen mit Bedingungen .......................................................
19
5.3.3
Spezielle Abfragen an eine Relation..........................................28
5.3.4
Aggregatfunktionen (COUNT, SUM, …) ...................................
34
5.3.5
Bildung von Gruppen (GROUP) ...............................................
38
5.3.6
Verknüpfung von Relationen (Join) ..........................................
41
5.3.7
Join-Operationen ab SQL-92 ...................................................
44
5.3.8
Verknüpfung von Relationen mit Subqueries ...........................
53
5.3.9
Abfragen mit Existenz-Quantor ................................................
60
5.3.10 Mengenoperationen .................................................................
61
SQL-DML: Mutationen in SQL (Insert, Update, Delete) ......................
65
5.4
01.10.2007 AIFB
SQL - Structured Query Language
5 SQL – Structured Query Language
12
(1/2)
Tabellen zu den folgenden Beispielen:
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
projekt
P-NAME
P-1
P-2
P-3
P-4
NAME
Meyer
Müller
Klein
Maus
Groß
Schmitt
Mayerlein
Müller
Schulz
Krämer
Meier
Schuster
(#=n)
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
P-NR
761235
770008
770114
770231
(#=k)
P-FILIALE
Karlsruhe
Karlsruhe
Heidelberg
Mannheim
P-LEITER
3115
3115
1324
2814
01.10.2007 AIFB
SQL - Structured Query Language
5.3 Abfragen in SQL (Query Language)
13
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
770008
770008
770008
770114
770114
770114
770114
770114
770114
770231
770231
770231
770231
770231
(#=m)
ANG-NR
3207
3115
3190
1435
3425
2244
1237
2814
2454
2814
1435
1237
2454
3425
2412
3190
2314
2244
3115
1324
(2/2)
PROZ-ARBZEIT
100
50
50
40
50
20
40
70
40
30
60
60
60
50
100
50
100
80
50
100
01.10.2007 AIFB
SQL - Structured Query Language
5.3 Abfragen in SQL (Query Language)
14
Die SELECT-Abfrage
Grundform:
SELECT
FROM
WHERE
…
…
…
Auswahl von Spalten (π)
Relation(en)/Tabelle(n)
Selektionsbedingung (σ)
SELECT-Abfrage ::=
SELECT [DISTINCT] derived column(s)
FROM table(s)
[WHERE search condition]
[GROUP BY column(s)
[HAVING search condition]]
[ORDER BY column(s)];
Mengenoperationen:
SELECT-Abfrage op SELECT-Abfrage
op := {EXCEPT | INTERSECT | UNION} [ALL]
Ab SQL-92 können SELECT-Abfragen außerdem durch explizite JoinOperationen verknüpft werden. → Abs. 5.3.7
01.10.2007 AIFB
SQL - Structured Query Language
5.3.1 Die Syntax der SELECT-Abfrage
15
WHERE <search condition>
Die search condition ist ein logischer Ausdruck, der
folgendes enthalten kann:
- Vergleichsoperationen: =, <, >, <>, >=, <=,
BETWEEN, LIKE, IN
- Boolesche Operatoren: AND, OR, NOT
(d.h. Bezugnahme auf eine Zeile, wie σ-Operation)
01.10.2007 AIFB
SQL - Structured Query Language
5.3.2 Abfragen mit Bedingungen (″σ″)(1/9)
16
1. „Name und Abteilungsnummer aller Angestellten mit Wohnort Karlsruhe“
SELECT
FROM
WHERE
NAME, ABT-NR
Ergebnis:
angestellte
WOHNORT=‘Karlsruhe’;
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
NAME
Meyer
Müller
Klein
Maus
Groß
Schmitt
Mayerlein
Müller
Schulz
Krämer
Meier
Schuster
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
NAME
Meyer
Maus
Groß
Müller
ABT-NR
35
30
35
32
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
01.10.2007 AIFB
SQL - Structured Query Language
5.3.2 Abfragen mit Bedingungen (″σ″)(2/9)
17
Zusammengesetzte Suchbedingung
2. „Name und Abteilungsnummer aller Angestellten, die in Karlsruhe wohnen
oder in Abteilung 30 arbeiten“
NAME
ABT-NR
Ergebnis:
SELECT
NAME, ABT-NR
Meyer
35
FROM
WHERE
angestellte
WOHNORT=‘Karlsruhe’
OR ABT-NR=30;
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
NAME
Meyer
Müller
Klein
Maus
Groß
Schmitt
Mayerlein
Müller
Schulz
Krämer
Meier
Schuster
Müller
Maus
Groß
Müller
Meier
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
30
30
35
32
30
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
01.10.2007 AIFB
SQL - Structured Query Language
5.3.2 Abfragen mit Bedingungen (″σ″)(3/9)
18
Abfragen mit Bereichsgrenzen
3. „Alle Angestellten (Nummer und Name) mit Nummer zwischen 1435 und
2314 (jeweils einschließlich)“
„ x BETWEEN a AND b“ entspricht „x >= a AND x <= b“
SELECT
FROM
WHERE
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
ANG-NR, NAME
Ergebnis:
angestellte
ANG-NR BETWEEN 1435 AND 2314;
NAME
Meyer
Müller
Klein
Maus
Groß
Schmitt
Mayerlein
Müller
Schulz
Krämer
Meier
Schuster
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
ANG-NR
2314
1435
2244
NAME
Groß
Mayerlein
Schulz
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
01.10.2007 AIFB
SQL - Structured Query Language
5.3.2 Abfragen mit Bedingungen (″σ″)(4/9)
19
Abfragen mit Wertaufzählung
4. „Alle Angestellten-Nummern der Abteilungen 30 und 35 aus Karlsruhe und
Mannheim“
ANG-NR
Ergebnis:
SELECT
ANG-NR
FROM
WHERE
angestellte
ABT-NR IN (30, 35) AND
WOHNORT IN (’Karlsruhe’, ’Mannheim’);
angestellte
ANG-NR
NAME
WOHNORT
Meyer
Karlsruhe
3115
Müller
Mannheim
3207
Klein
Mannheim
2814
Maus
Karlsruhe
3190
Groß
Karlsruhe
2314
Schmitt
Heidelberg
1324
Mayerlein
Bruchsal
1435
Müller
Karlsruhe
2412
Negation:
NOT BETWEEN
/ NOT Bruchsal
Schulz
2244
Ludwigshafen
IN Krämer
1237
Meier
Pforzheim
3425
Schuster
Worms
2454
3115
3207
3190
2314
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
01.10.2007 AIFB
SQL - Structured Query Language
5.3.2 Abfragen mit Bedingungen (″σ″)(5/9)
20
Abfrage mit Teilstring-Suche (Wildcards)
Das Zeichen "%" im Suchstring steht für eine beliebige Folge von
Characters, "passt" also auf jeden String.
5. „Nummer und P-FILIALE aller Projekte, deren P-Filialen in Städten
liegen, deren Namen ein „ei“ enthalten.“
SELECT
FROM
WHERE
projekt
P-NAME
P-1
P-2
P-3
P-4
Ergebnis:
P-NR, P-FILIALE
projekt
P-FILIALE LIKE ‘%ei%’;
P-NR
761235
770008
770114
770231
P-FILIALE
Karlsruhe
Karlsruhe
Heidelberg
Mannheim
P-NR
770114
770231
P-FILIALE
Heidelberg
Mannheim
P-LEITER
3115
3115
1324
2814
Bemerkungen:
- "like", nicht "=", nach "=" steht ein Wert, z.B. "=‘Heidelberg’"
- Groß/Kleinschreibung beachten
- Anderes Beispiel: Telefonnummer in Telefonliste
01.10.2007 AIFB
SQL - Structured Query Language
5.3.2 Abfragen mit Bedingungen (″σ″)(6/9)
21
Der Unterstrich "_" im Suchstring steht für genau 1 beliebiges Zeichen.
6. „Nummer und Name aller Angestellten, deren Namen mit ‘Me’ beginnen,
mit ‘er’ aufhören und dazwischen nur einen einzelnen Buchstaben
beinhalten.“
SELECT
FROM
WHERE
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
ANG-NR, NAME
angestellte
NAME LIKE ‘Me_er’;
NAME
Meyer
Müller
Klein
Maus
Groß
Schmitt
Mayerlein
Müller
Schulz
Krämer
Meier
Schuster
Ergebnis:
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
ANG-NR
3115
3425
NAME
Meyer
Meier
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
01.10.2007 AIFB
SQL - Structured Query Language
5.3.2 Abfragen mit Bedingungen (″σ″)(7/9)
22
Kombination von „_“ und „%“
7. „Alle Angestellten-Namen, deren Anfang wie ‘Maier’ klingt.“
SELECT
FROM
WHERE
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
NAME
angestellte
NAME LIKE ‘M_ _er%’;
NAME
Meyer
Müller
Klein
Maus
Groß
Schmitt
Mayerlein
Müller
Schulz
Krämer
Meier
Schuster
Ergebnis:
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
NAME
Meyer
Meyerlein
Meier
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
01.10.2007 AIFB
SQL - Structured Query Language
5.3.2 Abfragen mit Bedingungen (″σ″)(8/9)
23
Retrieval mit Sortierung
auf / absteigend ([ASC] / DESC)
nach Wert einer Spalte oder mehrerer Spalten (zusammengesetztes
Sortierkriterium, ASC - DESC beliebig mischbar)
8. „Nummer und Name aller Angestellten, die zur Abteilung 30 gehören,
aufsteigend sortiert nach Nummer und Name.“
SELECT
FROM
WHERE
ORDER BY
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
ANG-NR, NAME,
angestellte
ABT-NR=30
ANG-NR, NAME ASC;
NAME
WOHNORT ABT-NR
Meyer
Karlsruhe
35
Müller
Mannheim
30
Klein
Mannheim
32
Maus
Karlsruhe
30
Groß
Karlsruhe
35
Schmitt
Heidelberg
35
Mayerlein
Bruchsal
32
Müller
Karlsruhe
32
Schulz
Bruchsal
31
Krämer Ludwigshafen
31
Meier
Pforzheim
30
Schuster
Worms
31
Unsortiert:
ANG-NR
3207
3190
3425
Ergebnis:
NAME
Müller
Maus
Meier
WOHNORT ABT-NR
Mannheim
30
Karlsruhe
30
Pforzheim
30
ANG-NR
3190
3207
3425
NAME
Maus
Müller
Meier
01.10.2007 AIFB
SQL - Structured Query Language
5.3.2 Abfragen mit Bedingungen (″σ″)(9/9)
24
(1/6)
Auswahl von allen Zeilen
9. „Name aller Projekte“
SELECT
FROM
P-NAME
projekt;
Ergebnis:
projekt
P-NAME
P-1
P-2
P-3
P-4
P-NR
761235
770008
770114
770231
P-FILIALE
Karlsruhe
Karlsruhe
Heidelberg
Mannheim
P-LEITER
3115
3115
1324
2814
01.10.2007 AIFB
SQL - Structured Query Language
5.3.3 Spezielle Abfragen an eine Relation
25
(2/6)
Auswahl von allen Spalten
10. „alle Projekte in Karlsruhe“
SELECT
FROM
WHERE
∗
projekt
P-FILIALE=‘Karlsruhe‘;
Ergebnis:
projekt
P-NAME
P-1
P-2
P-3
P-4
P-NR
761235
770008
770114
770231
P-FILIALE
Karlsruhe
Karlsruhe
Heidelberg
Mannheim
P-LEITER
3115
3115
1324
2814
01.10.2007 AIFB
SQL - Structured Query Language
5.3.3 Spezielle Abfragen an eine Relation
26
(3/6)
Keine Auswahl von Spalten und von Zeilen (Gesamte Relation ausgeben)
11. „Alle Daten der Relation angestellte“
SELECT
FROM
∗
angestellte;
Ergebnis: vollständige Kopie von angestellte
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
NAME
Meyer
Müller
Klein
Maus
Groß
Schmitt
Mayerlein
Müller
Schulz
Krämer
Meier
Schuster
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
01.10.2007 AIFB
SQL - Structured Query Language
5.3.3 Spezielle Abfragen an eine Relation
27
(4/6)
Elimination von Duplikaten:
12. „Die Orte aller P-Filialen ohne Duplikate“
SELECT
FROM
DISTINCT P-FILIALE
projekt;
Ergebnis: eine einzelne Spalte ohne Duplikation.
P-FILIALE
Karlsruhe
Heidelberg
Mannheim
projekt
P-NAME
P-1
P-2
P-3
P-4
P-NR
761235
770008
770114
770231
P-FILIALE
Karlsruhe
Karlsruhe
Heidelberg
Mannheim
P-LEITER
3115
3115
1324
2814
01.10.2007 AIFB
SQL - Structured Query Language
5.3.3 Spezielle Abfragen an eine Relation
28
(5/6)
Rechnen mit Attributwerten
13. „(Zusammengehörige) Projekt- und Angestelltennummern mit
relativer Arbeitszeit“
SELECT
FROM
P-NR, ANG-NR, PROZ-ARBZEIT ∗ 0.01
ang-pro;
Ergebnis:
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
...
ANG-NR
3207
3115
3190
1435
3425
2244
...
PROZ-ARBZEIT
PROZ-ARBZEIT
∗0.01
1.00
100
0.50
50
0.50
50
40
0.40
0.50
50
0.20
20
...
01.10.2007 AIFB
SQL - Structured Query Language
5.3.3 Spezielle Abfragen an eine Relation
29
(6/6)
Integration von Konstanten in die Antwort
14. „(Zusammengehörige) Projekt- und Angestelltennummern
mit relativer Arbeitszeit; vor der Arbeitszeit soll stehen: Anteil =.“
SELECT
FROM
P-NR, ANG-NR,‘Anteil=‘, PROZ-ARBZEIT/100
ang-pro;
Ergebnis:
ang-pro
P-NR
P-NR
761235
761235
761235
761235
761235
761235
761235
761235
761235
761235
770008
770008
...
...
ANG-NR
ANG-NR
3207
3207
3115
3115
3190
3190
1435
1435
3425
3425
2244
2244 ...
...
Anteil =
Anteil =
Anteil =
Anteil =
Anteil =
Anteil =
Anteil =
...
PROZ-ARBZEIT/100
PROZ-ARBZEIT
PROZ-ARBZEIT
100
1.00
100
500.50
50
500.50
50
40
0.40
40
50
0.50
50
20
20
...0.20
...
01.10.2007 AIFB
SQL - Structured Query Language
5.3.3 Spezielle Abfragen an eine Relation
30
Funktion
COUNT
SUM
AVG
MAX
MIN
Bedeutung
Anzahl der Werte in Spalte (Name) bzw. Tabelle (*)
Summe der Werte in Spalte
Durchschnitt der Werte in Spalte
Größter Wert in Spalte
Kleinster Wert in Spalte
15. Funktion, auf Tabelle bezogen
„Anzahl aller Angestellten“
SELECT
FROM
COUNT(*)
angestellte;
12
Ergebnis: 12
Ohne Duplikation:
SELECT COUNT(DISTINCT NAME)
FROM angestellte;
Ergebnis: 11
ANG-NR NAME WOHNORT ABT-NR
Meyer
Karlsruhe
35
3115
Müller
Mannheim
30
3207
Klein
Mannheim
32
2814
Maus
Karlsruhe
30
3190
Groß
Karlsruhe
35
2314
Schmitt Heidelberg
35
1324
11
32
1435 Mayerlein Bruchsal
Müller
Karlsruhe
32
2412
Schulz
Bruchsal
31
2244
Krämer Ludwigshafen
31
1237
Meier
Pforzheim
30
3425
Schuster
Worms
31
2454
01.10.2007 AIFB
SQL - Structured Query Language
5.3.4 Aggregatfunktionen (COUNT, SUM, ...) (1/4)
31
16. Funktion, auf Spalte bezogen
„Höchster vorkommender Arbeitszeitanteil“
SELECT
FROM
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
770008
770008
770008
770114
770114
770114
770114
770114
770114
770231
770231
770231
770231
770231
MAX(PROZ-ARBZEIT) AS PROZ-ARBEIT-MAX
ang-pro;
ANG-NR
3207
3115
3190
1435
3425
2244
1237
2814
2454
2814
1435
1237
2454
3425
2412
3190
2314
2244
3115
1324
PROZ-ARBZEIT
100
50
50
40
50
20
40
70
40
30
60
60
60
50
100
50
100
80
50
100
Qualifikation
Qualifikationmit
mitAliasnamen
Aliasnamen
Bemerkung:
Bemerkung:Das
DasSchlüsselwort
Schlüsselwort
AS
ASbei
beider
derEinführung
Einführungvon
von
Aliasnamen
ist
optional.
Aliasnamen ist optional.
Ergebnis:
PROZ-ARBZEIT-MAX
100
01.10.2007 AIFB
SQL - Structured Query Language
5.3.4 Aggregatfunktionen (COUNT, SUM, ...) (2/4)
32
17. Funktion, auf Spalte bezogen, mit zusätzlicher Selektion
„Höchster Arbeitszeitanteil am Projekt Nr. 761235“
MAX(PROZ-ARBZEIT) AS PROZ-ARBEIT-MAX-761235
SELECT
ang-pro
FROM
P-NR = 761235;
WHERE
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
770008
770008
770008
770114
770114
770114
770114
770114
770114
770231
770231
770231
770231
770231
ANG-NR
3207
3115
3190
1435
3425
2244
1237
2814
2454
2814
1435
1237
2454
3425
2412
3190
2314
2244
3115
1324
PROZ-ARBZEIT
100
50
50
40
50
20
40
70
40
30
60
60
60
50
100
50
100
80
50
100
Ergebnis:
PROZ-ARBZEIT-MAX-761235
100
01.10.2007 AIFB
SQL - Structured Query Language
5.3.4 Aggregatfunktionen (COUNT, SUM, ...) (3/4)
33
18. Funktion, auf Spalte bezogen, mit zusätzlicher Selektion
„Gesamtarbeitsanteil von Projekt Nr. 770008“
SELECT
FROM
WHERE
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
770008
770008
770008
770114
770114
770114
770114
770114
770114
770231
770231
770231
770231
770231
SUM (PROZ-ARBZEIT) AS PROZ-ARBEIT-SUM-770008
ang-pro
P-NR = 770008;
ANG-NR
3207
3115
3190
1435
3425
2244
1237
2814
2454
2814
1435
1237
2454
3425
2412
3190
2314
2244
3115
1324
PROZ-ARBZEIT
100
50
50
40
50
20
40
70
40
30
60
60
60
50
100
50
100
80
50
100
Ergebnis:
PROZ-ARBZEIT-SUM-770008
170
170
01.10.2007 AIFB
SQL - Structured Query Language
5.3.4 Aggregatfunktionen (COUNT, SUM, ...) (4/4)
34
(1/3)
Zusammenfassung von Zeilen
• mit demselben Wert in einer oder mehreren vorgegebenen Spalten
• zum Zweck der Anwendung einer Standardfunktion auf diese Gruppe
o.ä.
19. „Gib den Gesamtarbeitszeitanteil jedes Projekts an.
P-NR, SUM(PROZ-ARBZEIT) AS PROZ-ARBEIT-PROJEKT
SELECT
P-NR
ANG-NR
PROZ-ARBZEIT
ang-pro
FROM
770008
1237
40
761235
3207
100
770114
1237
60
GROUP BY P-NR;
761235
3115
50
Gruppe
761235
Ergebnis:
P-NR
761235
770008
770114
770231
Gruppe
770008
PROZ-ARBZEITPROJEKT
290
170
360
380
Gruppe
770114
Gruppe
770231
770231
761235
770114
761235
761235
770231
770008
770008
770231
770008
770114
770008
770008
770114
770114
770008
770114
770114
770231
770114
761235
770114
770231
761235
770231
761235
770231
770114
770231
761235
770231
1324
3190
1435
1435
3425
2244
2244
1237
2314
2814
2412
2454
2454
2814
2454
1435
2814
1237
2814
2454
3115
3425
3115
2412
3190
3190
2314
3207
2244
3425
3115
3425
1324
100
50
60
40
40
50
80
20
20
40
100
70
100
40
40
30
60
70
60
30
60
50
50
100
50
50
100
100
80
50
50
100
SUM
290
SUM
170
SUM
360
SUM
380
01.10.2007 AIFB
SQL - Structured Query Language
5.3.5 Bildung von Gruppen (GROUP BY)
35
(2/3)
Auswahl von Gruppen mit HAVING-Bedingung:
(WHERE für Auswahl einzelner Zeilen)
20. „Gib die Projekte an, in denen der maximale Arbeitszeitanteil 100%
erreicht.“
SELECT
FROM
P-NR
ang-pro
GROUP BY P-NR
HAVING
MAX(PROZ-ARBZEIT)
=100;
Gruppe
761235
Gruppe
770008
Ergebnis:
P-NR
761235
770114
770231
Gruppe
770114
Gruppe
770231
P-NR
761235
761235
761235
761235
761235
770008
770008
770008
770008
770114
770114
770114
770114
770114
770114
770231
770231
770231
770231
770231
ANG-NR
3207
3115
3190
1435
3425
2244
1237
2814
2454
2814
1435
1237
2454
3425
2412
3190
2314
2244
3115
1324
PROZ-ARBZEIT
100
50
50
40
50
20
40
70
40
30
60
60
60
50
100
50
100
80
50
100
MAX
100
MAX
70
MAX
100
MAX
100
01.10.2007 AIFB
SQL - Structured Query Language
5.3.5 Bildung von Gruppen (GROUP BY)
36
(3/3)
21. „Gib alle Projekte an, an denen mind. 5 Angestellte beteiligt sind.“
P-NR
SELECT
FROM
ang-pro
GROUP BY P-NR
HAVING
COUNT (DISTINCT ANG-NR)>=5;
Ergebnis:
P-NR
761235
770114
770231
Gruppe
761235
Gruppe
770008
Gruppe
770114
Gruppe
770231
P-NR
761235
761235
761235
761235
761235
770008
770008
770008
770008
770114
770114
770114
770114
770114
770114
770231
770231
770231
770231
770231
ANG-NR
3207
3115
3190
1435
3425
2244
1237
2814
2454
2814
1435
1237
2454
3425
2412
3190
2314
2244
3115
1324
PROZ-ARBZEIT
100
50
COUNT
50
540
50
20
40
COUNT
4 70
40
30
60
60
COUNT
6 60
50
100
50
100
COUNT
80
5
50
100
01.10.2007 AIFB
SQL - Structured Query Language
5.3.5 Bildung von Gruppen (GROUP BY)
37
(1/3)
Abfragen mit Join („Natural Join“)
22. „Alle Projekte mit den Informationen der zugehörigen Angestellten.“
P-NR, a.ANG-NR, NAME, WOHNORT, ABT-NR,
SELECT
PROZ-ARBZEIT
angestellte a, ang-pro ap
FROM
Qualifikation
Qualifikationmit
mitAliasnamen
Aliasnamen
a.ANG-NR=ap.ANG-NR;
WHERE
Animation und Ergebnis dieses Beispiels (siehe Seite 68)
Animation vom Projekt Vikar
Kartesisches Produkt
„Natural Join“
Join mit Joinbedingung
23. „Alle Projekt-Nummern mit den Namen derjenigen Angestellten, die zu
50% mitarbeiten.“
SELECT
FROM
WHERE
P-NR, NAME
angestellte a, ang-pro ap
a.ANG-NR=ap.ANG-NR AND PROZ-ARBZEIT=50;
Animation und Ergebnis dieses Beispiels (siehe Seite 69)
01.10.2007 AIFB
SQL - Structured Query Language
5.3.6 Verknüpfung von Relationen (Join)
38
(2/3)
Abfragen mit Join (Spezialfall der Joinbedingung: θ-Join)
24. „Gib für jedes Projekt die Projektnummer und die Namen aller
Angestellten an, die dort nicht Projektleiter sind.“ (Angestellter muß
nicht an Projekt mitarbeiten)
SELECT P-NR, NAME
angestellte, projekt
FROM
WHERE ANG-NR <> P-LEITER;
Animation und Ergebnis dieses Beispiels (siehe Seite 70)
Join mit 3 Relationen
25. „Gib für jeden Angestellten seinen Namen, sowie die Projekt-Filialen
der Projekte an, an denen er mitarbeitet.“
SELECT NAME, P-FILIALE
angestellte a, ang-pro ap, projekt p
FROM
WHERE a.ANG-NR=ap.ANG-NR AND ap.P-NR=p.P-NR;
Animation und Ergebnis dieses Beispiels (siehe Seite 71)
01.10.2007 AIFB
SQL - Structured Query Language
5.3.6 Verknüpfung von Relationen (Join)
39
(3/3)
Join mit derselben Relation
Verwendung von Alias-Namen & Qualifikation
26. „Suche Paare von (unterschiedlichen) Angestellten(Ang-Nr), die in der
selben Stadt wohnen.“ (Das Ergebnis sollte keine redundanten Angaben
enthalten)
SELECT
FROM
WHERE
ANG-NR
3115
3207
2814
a
ANG-NR
3115
3115
3115
3207
3207
3207
2814
2814
2814
a.ANG-NR A-NR, b.ANG-NR B-NR
angestellte a, angestellte b
a.WOHNORT=b.WOHNORT AND a.ANG-NR < b.ANG-NR;
NAME
Meyer
Müller
Klein
NAME
Meyer
Meyer
Meyer
Müller
Müller
Müller
Klein
Klein
Klein
WOHNORT
Karlsruhe
Mannheim
Mannheim
ABT-NR
35
30
32
(kartesisches
Produkt)
b
WOHNORT ABT-NR ANG-NR
Karlsruhe
35
3115
Karlsruhe
35
3207
Karlsruhe
35
2814
Mannheim
30
3115
Mannheim
30
3207
Mannheim
30
2814
Mannheim
32
3115
Mannheim
32
3207
Mannheim
32
2814
NAME
Meyer
Müller
Klein
Meyer
Müller
Klein
Meyer
Müller
Klein
ANG-NR
3115
3207
2814
NAME
Meyer
Müller
Klein
WOHNORT ABT-NR
Karlsruhe
35
Mannheim
30
Mannheim
32
Karlsruhe
35
Mannheim
30
Mannheim
32
Karlsruhe
35
Mannheim
30
Mannheim
32
WOHNORT
Karlsruhe
Mannheim
Mannheim
ABT-NR
35
30
32
Ergebnis:
Ergebnis:
A-NR
2814
B-NR
3207
01.10.2007 AIFB
SQL - Structured Query Language
5.3.6 Verknüpfung von Relationen (Join)
40
(1/9)
Ab SQL-92 gibt es explizite Join-Operationen.
Es können sowohl durch SELECT-Abfragen abgeleitete Tabellen in der
Form: (SELECT … FROM … WHERE …)
join-op
(SELECT … FROM … WHERE …) [join-spec];
als auch Basis-Tabellen bzw. Views in der Form:
SELECT …
FROM table join-op table [join-spec]
WHERE …
miteinander verbunden werden.
Syntax einer Join-Operation:
join-op :=
CROSS JOIN |
[NATURAL] [INNER] JOIN |
[NATURAL] {LEFT | RIGHT | FULL} [OUTER] JOIN |
UNION JOIN
01.10.2007 AIFB
SQL - Structured Query Language
5.3.7 Join-Operationen ab SQL-92
41
(2/9)
Kartesisches Produkt:
table1 CROSS JOIN table2
berechnet das Kartesische Produkt und entspricht
SELECT * FROM table1, table2
Natural Join:
Die Verwendung des Schlüsselworts NATURAL führt zur Berechnung
des Natural Join.
Wenn nicht alle gemeinsamen Attribute verschmolzen werden sollen,
kann mit
join-spec := USING (column-list)
auf die angegebenen Attribute eingeschränkt werden. In diesem Fall
darf das Schlüsselwort NATURAL nicht angegeben werden.
01.10.2007 AIFB
SQL - Structured Query Language
5.3.7 Join-Operationen ab SQL-92
42
(3/9)
Join mit Joinbedingung:
Ein Join mit Joinbedingung wird mit INNER JOIN (oder nur JOIN)
durchgeführt, wobei die Join-Bedingung mit
join-spec := ON search condition
angegeben wird (search condition entspricht der Bedingung, die nach
WHERE in einer SELECT-Abfrage angegeben werden kann). Beim Join
mit Joinbedingung kommt es zu keiner Verschmelzung gemeinsamer
Attribute (wie bei der gleichnamigen Operation der relationalen Algebra).
Outer-Join:
Sämtliche Variationen des Outer-Join (FULL, LEFT, RIGHT) können
formuliert werden. Dabei kann entweder ein
– Natural-Join (NATURAL, wenn opt. USING-Klausel, muss
Schlüsselwort NATURAL weggelassen werden) oder ein
– Join mit Joinbedingung (ON-Klausel)
durchgeführt werden.
Das Schlüsselwort OUTER ist optional, sollte aber aus Gründen der
Leserlichkeit benutzt werden.
01.10.2007 AIFB
SQL - Structured Query Language
5.3.7 Join-Operationen ab SQL-92
43
(4/9)
Union-Join:
Der Union-Join kann mit Hilfe des Schlüsselworts
UNION JOIN
durchgeführt werden. Damit kann man Tabellen vereinigen, die gar nicht
vereinigungskompatibel sind. Die privaten Attribute der jeweils anderen
Tabelle werden ergänzt und mit NULL-Werten ausgefüllt.
Bemerkung:
Die expliziten Join-Operationen können zwar auch mit früheren SQLVersionen formuliert werden, allerdings z.T. nur sehr umständlich.
Die neuen Schlüsselwörter dienen der verbesserten Lesbarkeit;
insbesondere die WHERE-Klausel wird dadurch entlastet.
01.10.2007 AIFB
SQL - Structured Query Language
5.3.7 Join-Operationen ab SQL-92
44
(5/9)
Beispiel 5-3 (Umformulierung der Bsp. 22-26)
27. (Beispiel 22, S.41, „Natural Join“)
„Alle Projektmitarbeiter mit den zugehörigen Angestellteninfos.“
SELECT P-NR, a.ANG-NR, NAME, WOHNORT, ABT-NR,
PROZ-ARBZEIT
FROM angestellte a, ang-pro ap
WHERE a.ANG-NR=ap.ANG-NR;
Ab SQL-92:
SELECT *
FROM angestellte NATURAL JOIN ang-pro;
01.10.2007 AIFB
SQL - Structured Query Language
5.3.7 Join-Operationen ab SQL-92
45
(6/9)
Beispiel 5-3 (Umformulierung der Bsp. 22-26)
28. (Beispiel 23, S.41, Join mit Joinbedingung)
„Alle Projekt-Nummern mit den Namen derjenigen Angestellten, die zu
50% mitarbeiten.“
SELECT P-NR, NAME
FROM angestellte a, ang-pro ap
WHERE a.ANG-NR=ap.ANG-NR AND PROZ-ARBZEIT=50;
Ab SQL-92:
SELECT P-NR, NAME
FROM angestellte NATURAL JOIN ang-pro
WHERE PROZ-ARBZEIT = 50;
oder
SELECT P-NR, NAME
FROM angestellte INNER JOIN ang-pro
ON (angestellte.ANG-NR=ang-pro.ANG-NR AND
PROZ-ARBZEIT=50);
01.10.2007 AIFB
SQL - Structured Query Language
5.3.7 Join-Operationen ab SQL-92
46
(7/9)
Beispiel 5-3 (Umformulierung der Bsp. 22-26)
29. (Beispiel 24, S.42, Spezialfall der Joinbedingung: θ-Join)
„Gib für jedes Projekt die Projektnummer und die Namen aller
Angestellten an, die dort nicht Projektleiter sind.“ (Angestellter muß
nicht an Projekt mitarbeiten)
SELECT P-NR, NAME
FROM angestellte, projekt
WHERE ANG-NR <> P-LEITER;
Ab SQL-92:
SELECT P-NR, NAME
FROM angestellte JOIN projekt
ON (ANG-NR <> P-LEITER);
01.10.2007 AIFB
SQL - Structured Query Language
5.3.7 Join-Operationen ab SQL-92
47
(8/9)
Beispiel 5-3 (Umformulierung der Bsp. 22-26)
30. (Beispiel 25, S.42, Join mit 3 Relationen)
„Gib für jeden Angestellten seinen Namen, sowie die Projekt-Filialen
der Projekte an, an denen er mitarbeitet.“
SELECT NAME, P-FILIALE
FROM angestellte a, ang-pro ap, projekt p
WHERE a.ANG-NR=ap.ANG-NR AND ap.P-NR=p.P-NR;
Ab SQL-92:
SELECT NAME, P-FILIALE
FROM angestellte NATURAL JOIN ang-pro
NATURAL JOIN projekt;
01.10.2007 AIFB
SQL - Structured Query Language
5.3.7 Join-Operationen ab SQL-92
48
(9/9)
Beispiel 5-3 (Umformulierung der Bsp. 22-26)
31. (Beispiel 26, S.43, Join mit derselben Relation)
„Suche Paare von (unterschiedlichen) Angestellten, die in der selben
Stadt wohnen.“ (Das Ergebnis sollte keine redundanten Angaben
enthalten)
SELECT a.ANG-NR A-NR, b.ANG-NR B-NR
FROM angestellte a, angestellte b
WHERE a.WOHNORT=b.WOHNORT AND a.ANG-NR<b.ANG-NR;
Ab SQL-92
SELECT a.ANG-NR, b.ANG-NR
FROM (angestellte a) JOIN (angestellte b)
USING (WOHNORT)
WHERE a.ANG-NR < b.ANG-NR;
01.10.2007 AIFB
SQL - Structured Query Language
5.3.7 Join-Operationen ab SQL-92
49
(1/7)
Dabei handelt es sich um geschachtelte Queries.
32. „Namen aller Angestellten, die mit 100 % ihrer Arbeitszeit an einem
Projekt mitarbeiten.“
1. Schritt: Bestimme aus ang-pro alle ANG-NR, die zu
PROZ-ARBZEIT=100 gehören (Query a)
ANG-NR
SELECT
ang-pro
FROM
PROZ-ARBZEIT=100;
WHERE
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
770008
770008
770008
770114
770114
770114
770114
770114
770114
770231
770231
770231
770231
770231
ANG-NR
3207
3115
3190
1435
3425
2244
1237
2814
2454
2814
1435
1237
2454
3425
2412
3190
2314
2244
3115
1324
(#=m)
PROZ-ARBZEIT
100
50
50
40
50
20
40
70
40
30
60
60
60
50
100
50
100
80
50
100
Ergebnis:
ANG-NR
3207
2412
2314
1324
01.10.2007 AIFB
SQL - Structured Query Language
5.3.8 Verknüpfung von Relationen mit Subqueries
50
(2/7)
2. Schritt: Wähle in angestellte alle Tupel aus, deren ANG-NR in der oben
gegebenen Ergebnismenge liegt: (Query b)
SELECT
FROM
WHERE
NAME
angestellte
ANG-NR IN (3207, 2412, 2314,1324);
angestellte
(#=n)
ANG-NR NAME WOHNORT ABT-NR
Meyer
Karlsruhe
35
3115
Müller
Mannheim
30
3207
Klein
Mannheim
32
2814
Maus
Karlsruhe
30
3190
Groß
Karlsruhe
35
2314
Schmitt Heidelberg
35
1324
32
1435 Mayerlein Bruchsal
Müller
Karlsruhe
32
2412
Schulz
Bruchsal
31
2244
Krämer Ludwigshafen
31
1237
Meier
Pforzheim
30
3425
Schuster
Worms
31
2454
Ergebnis:
NAME
Müller
Groß
Schmitt
Müller
01.10.2007 AIFB
SQL - Structured Query Language
5.3.8 Verknüpfung von Relationen mit Subqueries
51
(3/7)
Zusammenfassung zu geschachtelter Query:
{äußere Query b}
SELECT NAME
FROM angestellte
WHERE ANG-NR IN
{Subquery a}
(SELECT ANG-NR
FROM ang-pro
WHERE PROZ-ARBZEIT = 100);
Vorgehensweise des Systems:
a) Abarbeitung Subquery (a)
b) Übergabe Ergebnis an übergeordnete (äußere) Query (b)
c) Abarbeitung Query b mit dem übergebenen Ergebnis
01.10.2007 AIFB
SQL - Structured Query Language
5.3.8 Verknüpfung von Relationen mit Subqueries
52
(4/7)
33. Äquivalente Abfrage mit Join:
SELECT NAME
FROM angestellte, ang-pro
WHERE PROZ-ARBZEIT = 100
AND ang-pro.ANG-NR = angestellte.ANG-NR;
Ergebnis von 32.
SELECT NAME
FROM
angestellte
WHERE ANG-NR IN
(SELECT ANG-NR
FROM ang-pro
WHERE PROZ-ARBZEIT = 100);
Vergleich 32. mit 33.: (vereinfachte Betrachtung!)
Man betrachte Anzahl der notwendigen DB-Zugriffe!
bei 32.: n+m
bei 33.: n∗m
01.10.2007 AIFB
SQL - Structured Query Language
5.3.8 Verknüpfung von Relationen mit Subqueries
53
(5/7)
Mehrfach geschachtelte Queries
34. „Namen aller Angestellten, die an mindestens einem Projekt in
Karlsruhe mitarbeiten.“
Vorgehensweise:
a) in projekt: Auswahl P-NR der Projekte in Karlsruhe
b) in ang-pro: Auswahl ANG-NR, deren P-NR in der obigen
Ergebnismenge liegen
c) in angestellte: Auswahl NAMEn, deren ANG-NR in der o.E. liegen
c)
SELECT NAME
FROM angestellte
WHERE ANG-NR IN
b)
(SELECT ANG-NR
FROM ang-pro
WHERE P-NR IN
a)
(SELECT P-NR
FROM projekt
WHERE P-FILIALE =‘Karlsruhe‘));
Animation und Ergebnis dieses Beispiels (siehe Seite 72)
01.10.2007 AIFB
SQL - Structured Query Language
5.3.8 Verknüpfung von Relationen mit Subqueries
54
(6/7)
35. Äquivalente Formulierung mit 2-fach Join:
SELECT
a.NAME
FROM
angestellte a, ang-pro ap, projekt p
WHERE
a.ANG-NR = ap.ANG-NR AND ap.P-NR=p.P-NR AND
p.P-FILIALE=‘Karlsruhe‘;
01.10.2007 AIFB
SQL - Structured Query Language
5.3.8 Verknüpfung von Relationen mit Subqueries
55
(7/7)
„Correlated subquery“:
Die Subquery ist durch eine „äußere Referenz“ von der umgebenden
äußeren Query abhängig.
: Gib alle Orte an, an denen mehr als ein Projekt angesiedelt ist.“
36.
(b)
SELECT DISTINCT P-FILIALE
FROM projekt a
WHERE 1 <
(a)
(SELECT COUNT (*)
FROM projekt b
WHERE b.P-FILIALE = a.P-FILIALE );
Jetzt kann Subquery (a) nicht vorab ausgewertet werden, da sie durch
äußere Referenz von umgebender Query (b) abhängig ist.
Animation und Ergebnis dieses Beispiels (siehe Seite 73)
01.10.2007 AIFB
SQL - Structured Query Language
5.3.8 Verknüpfung von Relationen mit Subqueries
56
Sei q Ergebnis(menge) einer Subquery; q leer?
EXISTS q
NOT EXISTS q
q=∅
falsch
wahr
q≠∅
wahr
falsch
37. „Suche Namen aller Angestellten, die keine Projektleiter sind.“
SELECT NAME
FROM angestellte
WHERE NOT EXISTS
(SELECT P-LEITER
FROM projekt
WHERE P-LEITER = ANG-NR);
Alternative möglich?
Animation und Ergebnis dieses Beispiels (siehe Seite 74)
01.10.2007 AIFB
SQL - Structured Query Language
5.3.9 Abfragen mit Existenz-Quantor
57
(1/3)
Verknüpfung von Abfragen mit Mengenoperationen: Ergebnismengen müssen
„vereinigungskompatibel“ sein (Namen und Wertebereiche
korrespondierender Spalten müssen übereinstimmen).
Operationen:
{UNION | INTERSECT | EXCEPT} [ALL]
Wird ALL verwendet, dann arbeiten die Operationen mit „uneigentlichen
Tabellen“, sonst mit „eigentlichen Tabellen“ (keine Duplikate).
38. „Nummern aller Angestellten, die in Karlsruhe wohnen oder dort Leiter
eines Projektes sind“
SELECT ANG-NR
FROM angestellte
WHERE WOHNORT = ‘Karlsruhe‘
UNION
SELECT P-LEITER as ANG-NR
FROM projekt
WHERE P-FILIALE = ‘Karlsruhe‘;
Animation und Ergebnis dieses Beispiels (siehe Seite 75)
01.10.2007 AIFB
SQL - Structured Query Language
5.3.10 Mengenoperationen
58
(2/3)
39„Nummern aller Angestellten, die an mehr als einem Projekt arbeiten und
in Karlsruhe wohnen.“
SELECT ANG-NR
FROM angestellte
WHERE WOHNORT = ‘Karlsruhe‘
INTERSECT
SELECT ANG-NR
FROM ang-pro
GROUP BY ANG-NR
HAVING COUNT (ANG-NR) > 1;
Animation und Ergebnis dieses Beispiels (siehe Seite 76)
01.10.2007 AIFB
SQL - Structured Query Language
5.3.10 Mengenoperationen
59
(3/3)
40. „Die Nummern aller Angestellten, deren Name mit ‘M‘ anfängt, die nicht
Projektleiter sind.“
SELECT ANG-NR
FROM angestellte
WHERE NAME like ‘M%‘
EXCEPT
SELECT P-LEITER as ANG-NR
FROM projekt;
Animation und Ergebnis dieses Beispiels (siehe Seite 77)
01.10.2007 AIFB
SQL - Structured Query Language
5.3.10 Mengenoperationen
60
5.1 Allgemeines zu SQL ......................................................... 5
5.2 SQL-DLL: Definition eines Datenbankschemas ............... 8
5.3 SQL-DML: Abfragen in SQL (Query Language) .............. 15
5.4 SQL-DML: Mutationen in SQL (Insert, Update, Delete) ... 65
01.10.2007 AIFB
SQL - Structured Query Language
5 SQL – Structured Query Language
61
UPDATE
UPDATE table
SET column = expression [, column = expression …]
[WHERE search condition];
Beispiel:
UPDATE projekt
SET P-LEITER = 3207, P-FILIALE = ’Mannheim’
WHERE P-NR = 761235;
DELETE
DELETE FROM table
[WHERE search condition];
Beispiel:
DELETE FROM ang-pro
WHERE ANG-NR = 3190;
01.10.2007 AIFB
SQL - Structured Query Language
5.4 Die Mutationen (1|2)
62
INSERT
INSERT
INTO table [(column [, column …])]
VALUES (constant [, constant …]);
oder
INSERT
INTO table [(column [, column …])]
query;
Beispiel:
INSERT
INTO angestellte (ANG-NR, NAME, WOHNORT, ABT-NR)
VALUES (1717, ’Häberle’,’Stuttgart’, 30);
Annahme: Es existiert eine Tabelle ‘angestellte_in_ka‘ mit dem gleichen Aufbau
wie ‘angestellte‘:
INSERT INTO angestellte_in_ka
SELECT *
FROM angestellte
WHERE WOHNORT = ‘Karlsruhe‘;
01.10.2007 AIFB
SQL - Structured Query Language
5.4 Die Mutationen (2|2)
63
Beenden
01.10.2007 AIFB
SQL - Structured Query Language
ENDE
64
SELECT
FROM
WHERE
P-NR, a.ANG-NR, NAME, WOHNORT, ABT-NR,PROZ-ARBZEIT
angestellte a, ang-pro ap
a.ANG-NR=ap.ANG-NR;
ANG-NR NAME
Meyer
3115
Klein
2814
Schmitt
1324
WOHNORT ABT-NR
Karlsruhe
35
Mannheim
32
Heidelberg
35
kartesisches
Produkt
P-NR
761235
770008
770114
770231
770231
ANG-NR NAME WOHNORT ABT-NR P-NR ANG-NR PROZ-ARBZEIT
35
3115 Meyer Karlsruhe
761235 3115
50
Meyer
Karlsruhe
35
3115
770008 2814
70
35
3115 Meyer Karlsruhe
770114 2814
30
35
3115 Meyer Karlsruhe
770231 3115
50
35
3115 Meyer Karlsruhe
770231 1324
100
Klein Mannheim
32
761235 3115
50
2814
Klein
Mannheim
32
770008
2814
70
2814
Klein Mannheim
32
770114 2814
30
2814
Klein Mannheim
32
770231 3115
50
2814
Klein Mannheim
32
770231 1324
100
2814
35
761235 3115
50
1324 Schmitt Heidelberg
Schmitt
Heidelberg
35
770008
2814
70
1324
35
770114 2814
30
1324 Schmitt Heidelberg
35
770231 3115
50
1324 Schmitt Heidelberg
35
770231 1324
100
1324 Schmitt Heidelberg
ANG-NR
3115
2814
2814
3115
1324
Ergebnis:
Ergebnis:
P-NR
761235
770231
770008
770114
770231
PROZ-ARBZEIT
50
70
30
50
100
01.10.2007 AIFB
SQL - Structured Query Language
Beispiel für „Natural Join“
ANGABT- PROZNAME WOHNORT
NR
NR ARBZEIT
3115 Meyer Karlsruhe 35
50
3115 Meyer Karlsruhe 35
50
2814 Klein Mannheim 32
70
2814 Klein Mannheim 32
30
1324 Schmitt Heidelberg 35
100
65
SELECT
FROM
WHERE
P-NR, NAME
angestellte a, ang-pro ap
a.ANG-NR=ap.ANG-NR AND PROZ-ARBZEIT=50;
ANG-NR NAME
Meyer
3115
Klein
2814
Schmitt
1324
ANG-NR
3115
3115
3115
3115
3115
2814
2814
2814
2814
2814
1324
1324
1324
1324
1324
NAME
Meyer
Meyer
Meyer
Meyer
Meyer
Klein
Klein
Klein
Klein
Klein
Schmitt
Schmitt
Schmitt
Schmitt
Schmitt
WOHNORT
Karlsruhe
Mannheim
Heidelberg
ABT-NR
35
32
35
(kartesisches
Produkt)
WOHNORT ABT-NR P-NR ANG-NR
Karlsruhe
35
761235
3115
Karlsruhe
35
770008
2814
Karlsruhe
35
770114
2814
Karlsruhe
35
770231
3115
Karlsruhe
35
770231
1324
Mannheim
32
761235
3115
Mannheim
32
770008
2814
Mannheim
32
770114
2814
Mannheim
32
770231
3115
Mannheim
32
770231
1324
Heidelberg
35
761235
3115
Heidelberg
35
770008
2814
Heidelberg
35
770114
2814
Heidelberg
35
770231
3115
Heidelberg
35
770231
1324
P-NR
761235
770008
770114
770231
770231
ANG-NR
3115
2814
2814
3115
1324
PROZ-ARBZEIT
50
70
30
50
100
50
70
30
50
100
50
70
30
50
100
PROZ-ARBZEIT
50
70
30
50
100
Ergebnis:
Ergebnis:
P-NR
761235
770231
NAME
Meyer
Meyer
01.10.2007 AIFB
SQL - Structured Query Language
Beispiel für Join mit Joinbedingung
66
SELECT
FROM
WHERE
P-NR, NAME
angestellte, projekt
ANG-NR <> P-LEITER;
ABT-NR
35
32
35
P-NR
761235
770008
770114
770231
NAME
Meyer
Klein
Schmitt
ANG-NR
NAME WOHNORT ABT-NR P-NAME P-NR P-FILIALE P-LEITER
Meyer
Karlsruhe
35
p-1
761235 Karlsruhe
3115
Meyer
Karlsruhe
35
p-2
770008 Karlsruhe
3115
Meyer
Karlsruhe
35
p-3
770114 Heidelberg
1324
Meyer
Karlsruhe
35
P-4
770231 Mannheim
2814
Klein
Mannheim
32
p-1
761235 Karlsruhe
3115
Klein
Mannheim
32
p-2
770008 Karlsruhe
3115
Klein
Mannheim
32
p-3
770114 Heidelberg
1324
Klein
Mannheim
32
P-4
770231 Mannheim
2814
Schmitt Heidelberg
35
p-1
761235 Karlsruhe
3115
Schmitt Heidelberg
35
p-2
770008 Karlsruhe
3115
Schmitt Heidelberg
35
p-3
770114 Heidelberg
1324
Schmitt Heidelberg
35
P-4
770231 Mannheim
2814
3115
3115
3115
3115
2814
2814
2814
2814
1324
1324
1324
1324
WOHNORT
Karlsruhe
Mannheim
Heidelberg
P-NAME
P-1
P-2
P-3
P-4
ANG-NR
3115
2814
1324
(kartesisches
Produkt)
P-FILIALE P-LEITER
Karlsruhe
3115
Karlsruhe
3115
Heidelberg
1324
Mannheim
2814
Ergebnis:
Ergebnis:
P-NR
770114
770231
761235
770008
770114
761235
770008
770231
NAME
Meyer
Meyer
Klein
Klein
Klein
Schmitt
Schmitt
Schmitt
01.10.2007 AIFB
SQL - Structured Query Language
Beispiel für Abfragen mit Join (Spezialfall der Joinbed.: θ -Join)
67
SELECT
FROM
WHERE
ANG-NR
3115
2814
NAME
Meyer
Klein
NAME, P-FILIALE
angestellte a, ang-pro ap, projekt p
a.ANG-NR=ap.ANG-NR AND ap.P-NR=p.P-NR;
WOHNORT
Karlsruhe
Mannheim
ANG-NR NAME WOHNORT
3115
3115
2814
2814
Meyer
Meyer
Klein
Klein
Karlsruhe
Karlsruhe
Mannheim
Mannheim
ANG-NR NAME WOHNORT
3115
3115
3115
3115
2814
2814
2814
2814
Meyer
Meyer
Meyer
Meyer
Klein
Klein
Klein
Klein
Karlsruhe
Karlsruhe
Karlsruhe
Karlsruhe
Mannheim
Mannheim
Mannheim
Mannheim
ABTNR
35
35
32
32
ABTNR
35
35
35
35
32
32
32
32
ABT-NR
35
32
(kartesisches
Produkt)
P-NR
ANG-NR
761235
770008
761235
770008
3115
2814
3115
2814
P-NR ANG-NR
761235
761235
770008
770008
761235
761235
770008
770008
3115
3115
2814
2814
3115
3115
2814
2814
PROZARBZEIT
50
70
50
70
P-NR ANG-NR PROZ-ARBZEIT
761235
3115
50
770008
2814
70
(kartesische
Produkt)
PPPP-NR
NAME
FILIALE LEITER
P-1 761235 Karlsruhe 3115
P-2 770008 Karlsruhe 3115
PROZPPPP-NR
ARBZEIT NAME
FILIALE LEITER
50
p-1 761235 Karlsruhe 3115
50
p-2 770008 Karlsruhe 3115
70
p-1 761235 Karlsruhe 3115
70
p-2 770008 Karlsruhe 3115
50
p-1 761235 Karlsruhe 3115
50
p-2 770008 Karlsruhe 3115
70
p-1 761235 Karlsruhe 3115
70
p-2 770008 Karlsruhe 3115
Ergebnis:
Ergebnis:
NAME P-FILIALE
Meyer Karlsruhe
Klein Karlsruhe
01.10.2007 AIFB
SQL - Structured Query Language
Beispiel für Join mit 3 Relationen
68
NAME
SELECT NAME
FROM
angestellte
WHERE ANG-NR IN
(SELECT ANG-NR
FROM
ang-pro
WHERE P-NR IN
(SELECT P-NR
FROM
projekt
WHERE P-FILIALE =‘Karlsruhe‘));
projekt
P-NAME
P-1
P-2
P-3
P-4
P-NR
761235
770008
770114
770231
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
(#=k)
P-FILIALE P-LEITER
Karlsruhe
3115
Karlsruhe
3115
Heidelberg
1324
Mannheim
2814
NAME
Meyer
Müller
Klein
Maus
Groß
Schmitt
Mayerlein
Müller
Schulz
Krämer
Meier
Schuster
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
(#=n)
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
Meyer
Müller
Klein
Maus
Mayerlein
Krämer
Meier
Schuster
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
770008
770008
770008
770114
770114
770114
770114
770114
770114
770231
770231
770231
770231
770231
ANG-NR
3207
3115
3190
1435
3425
2244
1237
2814
2454
2814
1435
1237
2454
3425
2412
3190
2314
2244
3115
1324
(#=m)
PROZ-ARBZEIT
100
50
50
40
50
20
40
70
40
30
60
60
60
50
100
50
100
80
50
100
01.10.2007 AIFB
SQL - Structured Query Language
Ergebnis:
Ergebnis:
Beispiel für Mehrfach geschachtelte Queries
69
SELECT DISTINCT P-FILIALE
FROM
projekt a
WHERE 1 <
(SELECT COUNT (*)
FROM
projekt b
WHERE b.P-FILIALE = a.P-FILIALE);
projekt a
P-NAME
P-1
P-2
P-3
P-4
P-NR
761235
770008
770114
770231
P-FILIALE P-LEITER
Karlsruhe
3115
Karlsruhe
3115
Heidelberg
1324
Mannheim
2814
projekt b
P-NAME P-NR
P-1
761235
P-2
770008
P-3
770114
P-4
770231
Ergebnis:
Ergebnis:
P-FILIALE
Kalrsruhe
P-FILIALE P-LEITER
Karlsruhe
3115
2
Karlsruhe
3115
Heidelberg
1324
1
Mannheim
2814
1
01.10.2007 AIFB
SQL - Structured Query Language
Beispiel für „Correlated subquery“ (äußere Referenz)
70
SELECT NAME
FROM angestellte
WHERE NOT EXISTS
(SELECT P-LEITER
FROM
projekt
WHERE P-LEITER = ANG-NR);
projekt
P-NAME
P-1
P-2
P-3
P-4
P-NR
761235
770008
770114
770231
P-FILIALE P-LEITER
Karlsruhe
3115
Karlsruhe
3115
Heidelberg
1324
Mannheim
2814
Ergebnis:
Ergebnis:
NAME
Müller
Maus
Groß
Mayerlein
Müller
Schulz
Krämer
Meier
Schuster
angestellte
ANG-NR
NAME
Meyer
3115
Müller
3207
Klein
2814
Maus
3190
Groß
2314
Schmitt
1324
Mayerlein
1435
Müller
2412
Schulz
2244
Krämer
1237
Meier
3425
Schuster
2454
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
01.10.2007 AIFB
SQL - Structured Query Language
Beispiel für Abfragen mit Existenz-Quantor
71
SELECT ANG-NR
FROM angestellte
WHERE WOHNORT = ‘Karlsruhe‘
Ergebnis:
Ergebnis:
ANG-NR
3115
3190
2314
2412
3115
3115
UNION
SELECT P-LEITER as ANG-NR
FROM projekt
WHERE P-FILIALE = ‘Karlsruhe‘;
angestellte
ANG-NR
NAME
Meyer
3115
Müller
3207
Klein
2814
Maus
3190
Groß
2314
Schmitt
1324
Mayerlein
1435
Müller
2412
Schulz
2244
Krämer
1237
Meier
3425
Schuster
2454
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
Zwischenergebnis:
Zwischenergebnis:
ANG-NR
3115
3190
2314
2412
projekt
P-NAME
P-1
P-2
P-3
P-4
Zwischenergebnis:
Zwischenergebnis:
ANG-NR
3115
3115
P-NR
761235
770008
770114
770231
P-FILIALE P-LEITER
Karlsruhe
3115
Karlsruhe
3115
Heidelberg
1324
Mannheim
2814
01.10.2007 AIFB
SQL - Structured Query Language
Beispiel für Mengenoperationen (Union)
72
Ergebnis:
Ergebnis:
SELECT ANG-NR
FROM angestellte
WHERE WOHNORT = ‘Karlsruhe‘
INTERSECT
ANG-NR
3115
3190
SELECT ANG-NR
FROM ang-pro
GROUP BY ANG-NR
HAVING COUNT (ANG-NR) > 1;
angestellte
ANG-NR
NAME
Meyer
3115
Müller
3207
Klein
2814
Maus
3190
Groß
2314
Schmitt
1324
Mayerlein
1435
Müller
2412
Schulz
2244
Krämer
1237
Meier
3425
Schuster
2454
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
ZwischenZwischenergebnis:
ergebnis:
Zwischenergebnis:
Zwischenergebnis:
ANG-NR
2314
2412
3115
3190
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
...
ANG-NR
3207
3115
3190
1435
3425
2244
...
PROZ-ARBZEIT
100
50
50
40
50
20
...
ANG-NR
1237
1435
2244
2454
2814
3115
3190
3425
01.10.2007 AIFB
SQL - Structured Query Language
Beispiel für Mengenoperationen (INTERSECT)
73
SELECT ANG-NR
FROM angestellte
WHERE NAME like ‘M%‘
EXCEPT
Ergebnis:
Ergebnis:
ANG-NR
3207
3190
1435
2412
3425
SELECT P-LEITER as ANG-NR
FROM projekt;
angestellte
ANG-NR
NAME
Meyer
3115
Müller
3207
Klein
2814
Maus
3190
Groß
2314
Schmitt
1324
Mayerlein
1435
Müller
2412
Schulz
2244
Krämer
1237
Meier
3425
Schuster
2454
WOHNORT
Karlsruhe
Mannheim
Mannheim
Karlsruhe
Karlsruhe
Heidelberg
Bruchsal
Karlsruhe
Bruchsal
Ludwigshafen
Pforzheim
Worms
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
projekt
P-NAME
P-1
P-2
P-3
P-4
P-NR
761235
770008
770114
770231
P-FILIALE P-LEITER
Karlsruhe
3115
Karlsruhe
3115
Heidelberg
1324
Mannheim
2814
01.10.2007 AIFB
SQL - Structured Query Language
Beispiel für Mengenoperationen (EXCEPT)
74
