5 SQL – Structured Query Language
Werbung
5
5.2 SQL-DDL: Definition eines Datenbankschemas ..............
8
24.05.2017
5.3 SQL-DML: Abfragen in SQL (Query Language) .............. 15
5.4 SQL-DML: Mutationen in SQL (Insert, Update, Delete) … 65
SQL - Structured Query Language
5.1 Allgemeines zu SQL .........................................................
AIFB
5 SQL – Structured Query Language
4
5.1 Allgemeines zu SQL
(1|3)
Entwicklungsziele:
möglichst einfach für den Endbenutzer;
Benennung:
SQL: „structured query language"
24.05.2017
möglichst deskriptiv, nicht prozedural
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
SQL - Structured Query Language
Entwicklung: (im IBM-Labor)
AIFB
ursprünglich: SEQUEL – „structured english query language"
IBM: SQL/DS (1981), DB2 (1983)
5
5.1 Allgemeines zu SQL
(2|3)
Standardisierung
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.
AIFB
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 - Structured Query Language
SQL-89 (1989): Lücken (z.B.:
Fremdschlüssel) von SQL-86 z.T. geschlossen;
24.05.2017
SQL-86 (1986): erster Standardisierungsversuch
6
5.1 Allgemeines zu SQL
(3|3)
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.
AIFB
Terminologie:
SQL - Structured Query Language
SQL kann sowohl interaktiv,
als auch eingebettet in einer konventionellen
Programmiersprache (z.B. C, FORTRAN, …) verwendet werden.
24.05.2017
Schnittstellen:
7
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
AIFB
Allgemeines zu SQL ........................................................
SQL - Structured Query Language
5.1
24.05.2017
5 SQL – Structured Query Language
8
5.2 Definition eines Datenbankschemas (SQL-DDL)
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.
AIFB
Übersicht: SQL-DDL (vereinfacht)
24.05.2017
: Menge von Basistabellen
: Views
SQL - Structured Query Language
Datenbank
Benutzersichten
9
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)]
AIFB
SQL kennt nicht den benannten Relationstyp
SQL - Structured Query Language
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“).
24.05.2017
5.2.1 Definition von Basistabellen
10
5.2.2 Datentypen und benutzerdefinierte Domänen
(1/2)
SQL bietet folgende (Basis-)Datentypen: (Auswahl)
CHARACTER
CHARACTER (n)
CHARACTER VARYING (n)
Kurzform
CHAR
CHAR (n)
∗)
VARCHAR (n)
DECIMAL [(p,[,s])]
NUMERIC [(p,[,s])]
INTEGER
SMALLINT
DOUBLE PRECISION
FLOAT (n)
REAL
AIFB
BIT
BIT (n)
BIT VARYING (n)
24.05.2017
Datentyp
DEC(p,s)
INT
SQL - Structured Query Language
Beschreibung
Zeichen(ketten):
Ein einzelnes Zeichen
Kette fester Länge
Kette variabler Länge
Bit-Datentyp:
Ein Bit
Bitfolge fester Länge
Bitfolge variabler Länge
Exakte Zahlen:
mit Nachkommastellen
dto.
Ganzzahl
kleine Ganzzahl
Gleitkommazahlen:
hohe Genauigkeit
benutzerdefinierte Gen.
geringere Genauigkeit
*) n=integer, p, s ebenso.
11
5.2.2 Datentypen und benutzerdefinierte Domänen
DATE
TIME
TIMESTAMP
INTERVAL f
INTERVAL sf to ef
f, sf, ef ∈ {YEAR, MONTH, DAY,
HOUR, MINUTE, SECOND}
(„sf>ef“)
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);
24.05.2017
Kurzform
AIFB
Datentyp
SQL - Structured Query Language
Beschreibung
Zeit, Datum, Zeitintervall:
Datum (y,m,d)
Zeitpunkt (h,min,sec)
Zeitpunkt (y,m,d,h, min, sec)
Zeitintervall
(2/2)
12
5.2.3 Ein Beispiel für ein Datenbankschema
CREATE SCHEMA Projektverwaltung;
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)).
AIFB
angestellte
PersonalNr PRIMARY KEY,
VARCHAR (30),
VARCHAR (30),
INTEGER);
SQL - Structured Query Language
CREATE TABLE
(ANG-NR
NAME
WOHNORT
ABT-NR
24.05.2017
CREATE DOMAIN PersonalNr AS INTEGER;
CREATE DOMAIN ProjektNr AS INTEGER;
CREATE DOMAIN ProzentAngabe AS ...
13
Beispiel 5-2:
CREATE VIEW angestellteAusKarlsruhe
AS SELECT ANG-NR, NAME, WOHNORT, ABT-NR
FROM
angestellte
WHERE WOHNORT = ‘Karlsruhe‘;
AIFB
([WITH CHECK OPTION]: bei Mutationen: Prüfung ob eingefügte
oder geänderte Tupel zu View gehören.)
SQL - Structured Query Language
CREATE VIEW table-name
[(column-name [,column-name] …)]
AS query expression
[WITH CHECK OPTION];
24.05.2017
5.2.4 Definition von Sichten
14
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
AIFB
Allgemeines zu SQL ............................................................................
SQL - Structured Query Language
5.1
24.05.2017
5 SQL – Structured Query Language
15
5.3 Abfragen in SQL (Query Language)
(1/2)
projekt
P-NAME
P-1
P-2
P-3
P-4
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
AIFB
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
SQL - Structured Query Language
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
24.05.2017
Tabellen zu den folgenden Beispielen:
16
PROZ-ARBZEIT
100
50
50
40
50
20
40
70
40
30
60
60
60
50
100
50
100
80
50
100
24.05.2017
(#=m)
ANG-NR
3207
3115
3190
1435
3425
2244
1237
2814
2454
2814
1435
1237
2454
3425
2412
3190
2314
2244
3115
1324
AIFB
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
770008
770008
770008
770114
770114
770114
770114
770114
770114
770231
770231
770231
770231
770231
(2/2)
SQL - Structured Query Language
5.3 Abfragen in SQL (Query Language)
17
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
AIFB
…
…
…
SQL - Structured Query Language
Die SELECT-Abfrage
Grundform:
SELECT
FROM
WHERE
24.05.2017
5.3.1 Die Syntax der SELECT-Abfrage
18
5.3.2 Abfragen mit Bedingungen (″σ″)
(1/9)
WHERE <search condition>
BETWEEN, LIKE, IN
- Boolesche Operatoren: AND, OR, NOT
(d.h. Bezugnahme auf eine Zeile, wie σ-Operation)
AIFB
- Vergleichsoperationen: =, <, >, <>, >=, <=,
SQL - Structured Query Language
folgendes enthalten kann:
24.05.2017
Die search condition ist ein logischer Ausdruck, der
19
5.3.2 Abfragen mit Bedingungen (″σ″)
(2/9)
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
AIFB
NAME, ABT-NR
Ergebnis:
angestellte
WOHNORT=‘Karlsruhe’;
SQL - Structured Query Language
SELECT
FROM
WHERE
24.05.2017
1. „Name und Abteilungsnummer aller Angestellten mit Wohnort Karlsruhe“
20
5.3.2 Abfragen mit Bedingungen (″σ″)
(3/9)
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
AIFB
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
SQL - Structured Query Language
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
Müller
30
FROM
angestellte
Maus
30
WHERE
Groß
35
WOHNORT=‘Karlsruhe’
Müller
32
OR ABT-NR=30;
Meier
30
24.05.2017
Zusammengesetzte Suchbedingung
21
5.3.2 Abfragen mit Bedingungen (″σ″)
(4/9)
Abfragen mit Bereichsgrenzen
3. „Alle Angestellten (Nummer und Name) mit Nummer zwischen 1435 und
2314 (jeweils einschließlich)“
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
AIFB
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
ANG-NR, NAME
SQL - Structured Query Language
SELECT
FROM
WHERE
24.05.2017
„ x BETWEEN a AND b“ entspricht „x >= a AND x <= b“
22
5.3.2 Abfragen mit Bedingungen (″σ″)
(5/9)
Abfragen mit Wertaufzählung
angestellte
ANG-NR
NAME
WOHNORT
3115
Meyer
Karlsruhe
3207
Müller
Mannheim
2814
Klein
Mannheim
3190
Maus
Karlsruhe
2314
Groß
Karlsruhe
1324
Schmitt
Heidelberg
1435
Mayerlein
Bruchsal
2412
Müller
Karlsruhe
2244
Schulz / NOT INBruchsal
Negation:
NOT BETWEEN
1237
Krämer
Ludwigshafen
3425
Meier
Pforzheim
2454
Schuster
Worms
3115
3207
3190
2314
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
AIFB
angestellte
ABT-NR IN (30, 35) AND
WOHNORT IN (’Karlsruhe’, ’Mannheim’);
SQL - Structured Query Language
FROM
WHERE
24.05.2017
4. „Alle Angestellten-Nummern der Abteilungen 30 und 35 aus Karlsruhe und
Mannheim“
ANG-NR
Ergebnis:
SELECT
ANG-NR
23
5.3.2 Abfragen mit Bedingungen (″σ″)
(6/9)
Abfrage mit Teilstring-Suche (Wildcards)
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
AIFB
SELECT
FROM
WHERE
SQL - Structured Query Language
5. „Nummer und P-FILIALE aller Projekte, deren P-Filialen in Städten
liegen, deren Namen ein „ei“ enthalten.“
24.05.2017
Das Zeichen "%" im Suchstring steht für eine beliebige Folge von
Characters, "passt" also auf jeden String.
24
5.3.2 Abfragen mit Bedingungen (″σ″)
(7/9)
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
AIFB
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
ANG-NR, NAME
angestellte
NAME LIKE ‘Me_er’;
SQL - Structured Query Language
SELECT
FROM
WHERE
24.05.2017
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.“
25
5.3.2 Abfragen mit Bedingungen (″σ″)
(8/9)
Kombination von „_“ und „%“
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
AIFB
angestellte
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
NAME
angestellte
NAME LIKE ‘M_ _er%’;
SQL - Structured Query Language
SELECT
FROM
WHERE
24.05.2017
7. „Alle Angestellten-Namen, deren Anfang wie ‘Maier’ klingt.“
26
5.3.2 Abfragen mit Bedingungen (″σ″)
(9/9)
Retrieval mit Sortierung
8. „Nummer und Name aller Angestellten, die zur Abteilung 30 gehören,
aufsteigend sortiert nach Nummer und Name.“
ANG-NR
3115
3207
2814
3190
2314
1324
1435
2412
2244
1237
3425
2454
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
AIFB
ANG-NR, NAME,
angestellte
ABT-NR=30
ANG-NR, NAME ASC;
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
SQL - Structured Query Language
SELECT
FROM
WHERE
ORDER BY
24.05.2017
auf / absteigend ([ASC] / DESC)
nach Wert einer Spalte oder mehrerer Spalten (zusammengesetztes
Sortierkriterium, ASC - DESC beliebig mischbar)
27
5.3.3 Spezielle Abfragen an eine Relation
(1/6)
Auswahl von allen Zeilen
9. „Name aller Projekte“
24.05.2017
P-NAME
projekt;
projekt
P-NAME
P-1
P-2
P-3
P-4
AIFB
Ergebnis:
P-NR
761235
770008
770114
770231
P-FILIALE
Karlsruhe
Karlsruhe
Heidelberg
Mannheim
P-LEITER
3115
3115
1324
2814
SQL - Structured Query Language
SELECT
FROM
28
5.3.3 Spezielle Abfragen an eine Relation
(2/6)
Auswahl von allen Spalten
10. „alle Projekte in Karlsruhe“
AIFB
24.05.2017
∗
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
SQL - Structured Query Language
SELECT
FROM
WHERE
29
5.3.3 Spezielle Abfragen an eine Relation
(3/6)
Keine Auswahl von Spalten und von Zeilen (Gesamte Relation ausgeben)
11. „Alle Daten der Relation 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
AIFB
24.05.2017
∗
angestellte;
SQL - Structured Query Language
SELECT
FROM
30
5.3.3 Spezielle Abfragen an eine Relation
(4/6)
Elimination von Duplikaten:
12. „Die Orte aller P-Filialen ohne Duplikate“
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
AIFB
24.05.2017
DISTINCT P-FILIALE
projekt;
SQL - Structured Query Language
SELECT
FROM
31
5.3.3 Spezielle Abfragen an eine Relation
(5/6)
Rechnen mit Attributwerten
P-NR, ANG-NR, PROZ-ARBZEIT ∗ 0.01
ang-pro;
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
...
AIFB
Ergebnis:
ANG-NR
3207
3115
3190
1435
3425
2244
...
PROZ-ARBZEIT
∗0.01
PROZ-ARBZEIT
1.00
100
50
0.50
50
0.50
40
0.40
50
0.50
20
0.20
...
SQL - Structured Query Language
SELECT
FROM
24.05.2017
13. „(Zusammengehörige) Projekt- und Angestelltennummern mit
relativer Arbeitszeit“
32
5.3.3 Spezielle Abfragen an eine Relation
(6/6)
Integration von Konstanten in die Antwort
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
...
AIFB
P-NR, ANG-NR,‘Anteil=‘, PROZ-ARBZEIT/100
SQL - Structured Query Language
SELECT
FROM
24.05.2017
14. „(Zusammengehörige) Projekt- und Angestelltennummern
mit relativer Arbeitszeit; vor der Arbeitszeit soll stehen: Anteil =.“
33
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
3115
Meyer
Karlsruhe
35
3207
Müller
Mannheim
30
2814
Klein
Mannheim
32
3190
Maus
Karlsruhe
30
2314
Groß
Karlsruhe
35
1324
Schmitt Heidelberg
35
11
1435 Mayerlein Bruchsal
32
2412
Müller
Karlsruhe
32
2244
Schulz
Bruchsal
31
1237
Krämer Ludwigshafen
31
3425
Meier
Pforzheim
30
2454
Schuster
Worms
31
24.05.2017
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
AIFB
Funktion
COUNT
SUM
AVG
MAX
MIN
(1/4)
SQL - Structured Query Language
5.3.4 Aggregatfunktionen (COUNT, SUM, ...)
34
5.3.4 Aggregatfunktionen (COUNT, SUM, ...)
(2/4)
16. Funktion, auf Spalte bezogen
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 mit Aliasnamen
Bemerkung: Das Schlüsselwort
AS bei der Einführung von
Aliasnamen ist optional.
Ergebnis:
PROZ-ARBZEIT-MAX
100
AIFB
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;
SQL - Structured Query Language
SELECT
FROM
24.05.2017
„Höchster vorkommender Arbeitszeitanteil“
35
5.3.4 Aggregatfunktionen (COUNT, SUM, ...)
(3/4)
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
AIFB
ang-pro
P-NR
761235
761235
761235
761235
761235
770008
770008
770008
770008
770114
770114
770114
770114
770114
770114
770231
770231
770231
770231
770231
Arbeitszeitanteil am Projekt Nr. 761235“
MAX(PROZ-ARBZEIT) AS PROZ-ARBEIT-MAX-761235
ang-pro
P-NR = 761235;
SQL - Structured Query Language
„Höchster
SELECT
FROM
WHERE
24.05.2017
17. Funktion, auf Spalte bezogen, mit zusätzlicher Selektion
36
5.3.4 Aggregatfunktionen (COUNT, SUM, ...)
(4/4)
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
AIFB
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;
SQL - Structured Query Language
SELECT
FROM
WHERE
24.05.2017
18. Funktion, auf Spalte bezogen, mit zusätzlicher Selektion
„Gesamtarbeitsanteil von Projekt Nr. 770008“
37
SELECT
P-NR, SUM(PROZ-ARBZEIT) AS PROZ-ARBEIT-PROJEKT
P-NR
ANG-NR
PROZ-ARBZEIT
ang-pro
FROM
770008
1237
40
761235
3207
100
GROUP BY P-NR;
770114
1237
60
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
AIFB
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.
24.05.2017
(1/3)
SQL - Structured Query Language
5.3.5 Bildung von Gruppen (GROUP BY)
38
5.3.5 Bildung von Gruppen (GROUP BY)
(2/3)
Auswahl von Gruppen mit HAVING-Bedingung:
(WHERE für Auswahl einzelner Zeilen)
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
AIFB
P-NR
MAX
70
SQL - Structured Query Language
SELECT
FROM
24.05.2017
20. „Gib die Projekte an, in denen der maximale Arbeitszeitanteil 100%
erreicht.“
MAX
100
MAX
100
39
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
AIFB
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;
24.05.2017
(3/3)
SQL - Structured Query Language
5.3.5 Bildung von Gruppen (GROUP BY)
40
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)
AIFB
Abfragen mit Join („Natural Join“)
22. „Alle Projekte mit den Informationen der zugehörigen Angestellten.“
SELECT
P-NR, a.ANG-NR, NAME, WOHNORT, ABT-NR,
PROZ-ARBZEIT
angestellte a, ang-pro ap
FROM
Qualifikation mit Aliasnamen
WHERE
a.ANG-NR=ap.ANG-NR;
24.05.2017
(1/3)
SQL - Structured Query Language
5.3.6 Verknüpfung von Relationen (Join)
41
(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;
24.05.2017
5.3.6 Verknüpfung von Relationen (Join)
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)
SQL - Structured Query Language
AIFB
Animation und Ergebnis dieses Beispiels (siehe Seite 70)
42
5.3.6 Verknüpfung von Relationen (Join)
(3/3)
a
ANG-NR
3115
3115
3115
3207
3207
3207
2814
2814
2814
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:
A-NR
2814
B-NR
3207
AIFB
ANG-NR
3115
3207
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;
SQL - Structured Query Language
SELECT
FROM
WHERE
24.05.2017
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)
43
5.3.7 Join-Operationen ab SQL-92
(1/9)
Syntax einer Join-Operation:
join-op :=
CROSS JOIN |
[NATURAL] [INNER] JOIN |
[NATURAL] {LEFT | RIGHT | FULL} [OUTER] JOIN |
UNION JOIN
AIFB
SQL - Structured Query Language
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.
24.05.2017
Ab SQL-92 gibt es explizite Join-Operationen.
44
5.3.7 Join-Operationen ab SQL-92
(2/9)
Kartesisches Produkt:
table1 CROSS JOIN table2
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.
AIFB
Natural Join:
Die Verwendung des Schlüsselworts NATURAL führt zur Berechnung des
Natural Join.
SQL - Structured Query Language
SELECT * FROM table1, table2
24.05.2017
berechnet das Kartesische Produkt und entspricht
45
5.3.7 Join-Operationen ab SQL-92
(3/9)
Join mit Joinbedingung:
Ein Join mit Joinbedingung wird mit INNER JOIN (oder nur JOIN)
durchgeführt, wobei die Join-Bedingung mit
– 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.
AIFB
Outer-Join:
Sämtliche Variationen des Outer-Join (FULL, LEFT, RIGHT) können
formuliert werden. Dabei kann entweder ein
SQL - Structured Query Language
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).
24.05.2017
join-spec := ON search condition
46
5.3.7 Join-Operationen ab SQL-92
(4/9)
Union-Join:
Der Union-Join kann mit Hilfe des Schlüsselworts
durchgeführt werden. Damit kann man Tabellen vereinigen, die gar nicht
vereinigungskompatibel sind. Die privaten Attribute der jeweils anderen
24.05.2017
UNION JOIN
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.
SQL - Structured Query Language
AIFB
Tabelle werden ergänzt und mit NULL-Werten ausgefüllt.
47
5.3.7 Join-Operationen ab SQL-92
(5/9)
Beispiel 5-3 (Umformulierung der Bsp. 22-26)
Ab SQL-92:
SELECT *
FROM angestellte NATURAL JOIN ang-pro;
AIFB
SQL - Structured Query Language
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;
24.05.2017
27. (Beispiel 22, S.41, „Natural Join“)
„Alle Projektmitarbeiter mit den zugehörigen Angestellteninfos.“
48
5.3.7 Join-Operationen ab SQL-92
(6/9)
Beispiel 5-3 (Umformulierung der Bsp. 22-26)
oder
SELECT P-NR, NAME
FROM angestellte INNER JOIN ang-pro
ON (angestellte.ANG-NR=ang-pro.ANG-NR AND
PROZ-ARBZEIT=50);
AIFB
Ab SQL-92:
SELECT P-NR, NAME
FROM angestellte NATURAL JOIN ang-pro
WHERE PROZ-ARBZEIT = 50;
SQL - Structured Query Language
SELECT P-NR, NAME
FROM angestellte a, ang-pro ap
WHERE a.ANG-NR=ap.ANG-NR AND PROZ-ARBZEIT=50;
24.05.2017
28. (Beispiel 23, S.41, Join mit Joinbedingung)
„Alle Projekt-Nummern mit den Namen derjenigen Angestellten, die zu
50% mitarbeiten.“
49
5.3.7 Join-Operationen ab SQL-92
(7/9)
Ab SQL-92:
SELECT P-NR, NAME
FROM angestellte JOIN projekt
ON (ANG-NR <> P-LEITER);
AIFB
SELECT P-NR, NAME
FROM angestellte, projekt
WHERE ANG-NR <> P-LEITER;
SQL - Structured Query Language
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)
24.05.2017
Beispiel 5-3 (Umformulierung der Bsp. 22-26)
50
5.3.7 Join-Operationen ab SQL-92
(8/9)
Ab SQL-92:
SELECT NAME, P-FILIALE
FROM angestellte NATURAL JOIN ang-pro
NATURAL JOIN projekt;
AIFB
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;
SQL - Structured Query Language
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.“
24.05.2017
Beispiel 5-3 (Umformulierung der Bsp. 22-26)
51
5.3.7 Join-Operationen ab SQL-92
(9/9)
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;
AIFB
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;
SQL - Structured Query Language
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)
24.05.2017
Beispiel 5-3 (Umformulierung der Bsp. 22-26)
52
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
AIFB
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)
SELECT
ANG-NR
ang-pro
FROM
WHERE
PROZ-ARBZEIT=100;
24.05.2017
(1/7)
SQL - Structured Query Language
5.3.8 Verknüpfung von Relationen mit Subqueries
53
5.3.8 Verknüpfung von Relationen mit Subqueries
(2/7)
2. Schritt: Wähle in angestellte alle Tupel aus, deren ANG-NR in der oben
gegebenen Ergebnismenge liegt: (Query b)
Ergebnis:
NAME
Müller
Groß
Schmitt
Müller
AIFB
angestellte
(#=n)
ANG-NR NAME WOHNORT ABT-NR
3115
Meyer
Karlsruhe
35
3207
Müller
Mannheim
30
2814
Klein
Mannheim
32
3190
Maus
Karlsruhe
30
2314
Groß
Karlsruhe
35
1324
Schmitt Heidelberg
35
1435 Mayerlein Bruchsal
32
2412
Müller
Karlsruhe
32
2244
Schulz
Bruchsal
31
1237
Krämer Ludwigshafen
31
3425
Meier
Pforzheim
30
2454
Schuster
Worms
31
24.05.2017
NAME
angestellte
ANG-NR IN (3207, 2412, 2314,1324);
SQL - Structured Query Language
SELECT
FROM
WHERE
54
5.3.8 Verknüpfung von Relationen mit Subqueries
(3/7)
Vorgehensweise des Systems:
a) Abarbeitung Subquery (a)
b) Übergabe Ergebnis an übergeordnete (äußere) Query (b)
c) Abarbeitung Query b mit dem übergebenen Ergebnis
AIFB
SQL - Structured Query Language
{äußere Query b}
SELECT NAME
FROM angestellte
WHERE ANG-NR IN
{Subquery a}
(SELECT ANG-NR
FROM ang-pro
WHERE PROZ-ARBZEIT = 100);
24.05.2017
Zusammenfassung zu geschachtelter Query:
55
5.3.8 Verknüpfung von Relationen mit Subqueries
(4/7)
Vergleich 32. mit 33.: (vereinfachte Betrachtung!)
Man betrachte Anzahl der notwendigen DB-Zugriffe!
bei 32.: n+m
bei 33.: n∗m
AIFB
Ergebnis von 32.
SELECT NAME
FROM
angestellte
WHERE ANG-NR IN
(SELECT ANG-NR
FROM ang-pro
WHERE PROZ-ARBZEIT = 100);
SQL - Structured Query Language
SELECT NAME
FROM angestellte, ang-pro
WHERE PROZ-ARBZEIT = 100
AND ang-pro.ANG-NR = angestellte.ANG-NR;
24.05.2017
33. Äquivalente Abfrage mit Join:
56
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)
AIFB
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
24.05.2017
(5/7)
SQL - Structured Query Language
5.3.8 Verknüpfung von Relationen mit Subqueries
57
5.3.8 Verknüpfung von Relationen mit Subqueries
(6/7)
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‘;
AIFB
a.NAME
SQL - Structured Query Language
SELECT
24.05.2017
35. Äquivalente Formulierung mit 2-fach Join:
58
5.3.8 Verknüpfung von Relationen mit Subqueries
(7/7)
„Correlated subquery“:
Die Subquery ist durch eine „äußere Referenz“ von der umgebenden
äußeren Query abhängig.
(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)
AIFB
SELECT DISTINCT P-FILIALE
FROM projekt a
WHERE 1 <
SQL - Structured Query Language
(b)
24.05.2017
: Gib alle Orte an, an denen mehr als ein Projekt angesiedelt ist.“
36.
59
5.3.9 Abfragen mit Existenz-Quantor
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)
AIFB
q≠∅
wahr
falsch
SQL - Structured Query Language
EXISTS q
NOT EXISTS q
q=∅
falsch
wahr
24.05.2017
Sei q Ergebnis(menge) einer Subquery; q leer?
60
5.3.10 Mengenoperationen
(1/3)
Verknüpfung von Abfragen mit Mengenoperationen: Ergebnismengen müssen
„vereinigungskompatibel“ sein (Namen und Wertebereiche
korrespondierender Spalten müssen übereinstimmen).
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)
AIFB
Wird ALL verwendet, dann arbeiten die Operationen mit „uneigentlichen
Tabellen“, sonst mit „eigentlichen Tabellen“ (keine Duplikate).
SQL - Structured Query Language
{UNION | INTERSECT | EXCEPT} [ALL]
24.05.2017
Operationen:
61
5.3.10 Mengenoperationen
(2/3)
39„Nummern aller Angestellten, die an mehr als einem Projekt arbeiten und
SELECT ANG-NR
FROM ang-pro
GROUP BY ANG-NR
HAVING COUNT (ANG-NR) > 1;
Animation und Ergebnis dieses Beispiels (siehe Seite 76)
AIFB
INTERSECT
SQL - Structured Query Language
SELECT ANG-NR
FROM angestellte
WHERE WOHNORT = ‘Karlsruhe‘
24.05.2017
in Karlsruhe wohnen.“
62
5.3.10 Mengenoperationen
(3/3)
SELECT P-LEITER as ANG-NR
FROM projekt;
Animation und Ergebnis dieses Beispiels (siehe Seite 77)
AIFB
EXCEPT
SQL - Structured Query Language
SELECT ANG-NR
FROM angestellte
WHERE NAME like ‘M%‘
24.05.2017
40. „Die Nummern aller Angestellten, deren Name mit ‘M‘ anfängt, die nicht
Projektleiter sind.“
63
5 SQL – Structured Query Language
5.3 SQL-DML: Abfragen in SQL (Query Language) .............. 15
5.4 SQL-DML: Mutationen in SQL (Insert, Update, Delete) ... 65
AIFB
5.2 SQL-DLL: Definition eines Datenbankschemas ............... 8
24.05.2017
5
SQL - Structured Query Language
5.1 Allgemeines zu SQL .........................................................
64
5.4 Die Mutationen
(1|2)
DELETE
DELETE FROM table
[WHERE search condition];
Beispiel:
DELETE FROM ang-pro
WHERE ANG-NR = 3190;
AIFB
SQL - Structured Query Language
Beispiel:
UPDATE projekt
SET P-LEITER = 3207, P-FILIALE = ’Mannheim’
WHERE P-NR = 761235;
24.05.2017
UPDATE
UPDATE table
SET column = expression [, column = expression …]
[WHERE search condition];
65
5.4 Die Mutationen
(2|2)
INSERT
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‘;
AIFB
INSERT
INTO table [(column [, column …])]
query;
SQL - Structured Query Language
oder
24.05.2017
INSERT
INTO table [(column [, column …])]
VALUES (constant [, constant …]);
66
Beenden
SQL - Structured Query Language
AIFB
24.05.2017
ENDE
67
Beispiel für „Natural Join“
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
3115 Meyer Karlsruhe
35
761235 3115
50
3115 Meyer Karlsruhe
35
770008 2814
70
35
3115 Meyer Karlsruhe
770114 2814
30
35
3115 Meyer Karlsruhe
770231 3115
50
3115 Meyer Karlsruhe
35
770231 1324
100
2814
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
1324 Schmitt Heidelberg
35
761235 3115
50
1324 Schmitt Heidelberg
35
770008 2814
70
1324 Schmitt Heidelberg
35
770114 2814
30
1324 Schmitt Heidelberg
35
770231 3115
50
1324 Schmitt Heidelberg
35
770231 1324
100
ANG-NR
3115
2814
2814
3115
1324
Ergebnis:
P-NR
761235
770231
770008
770114
770231
PROZ-ARBZEIT
50
70
30
50
100
AIFB
ANG-NR NAME
Meyer
3115
Klein
2814
Schmitt
1324
24.05.2017
P-NR, a.ANG-NR, NAME, WOHNORT, ABT-NR,PROZ-ARBZEIT
angestellte a, ang-pro ap
a.ANG-NR=ap.ANG-NR;
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
SQL - Structured Query Language
SELECT
FROM
WHERE
68
Beispiel für Join mit Joinbedingung
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:
P-NR
761235
770231
NAME
Meyer
Meyer
AIFB
ANG-NR NAME
Meyer
3115
Klein
2814
Schmitt
1324
24.05.2017
P-NR, NAME
angestellte a, ang-pro ap
a.ANG-NR=ap.ANG-NR AND PROZ-ARBZEIT=50;
SQL - Structured Query Language
SELECT
FROM
WHERE
69
Beispiel für Abfragen mit Join (Spezialfall der Joinbed.: θ-Join)
ABT-NR
35
32
35
P-NR
761235
770008
770114
770231
NAME
Meyer
Klein
Schmitt
ANG-NR
3115
3115
3115
3115
2814
2814
2814
2814
1324
1324
1324
1324
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
(kartesisches
Produkt)
P-FILIALE P-LEITER
Karlsruhe
3115
Karlsruhe
3115
Heidelberg
1324
Mannheim
2814
Ergebnis:
P-NR
770114
770231
761235
770008
770114
761235
770008
770231
NAME
Meyer
Meyer
Klein
Klein
Klein
Schmitt
Schmitt
Schmitt
AIFB
WOHNORT
Karlsruhe
Mannheim
Heidelberg
P-NAME
P-1
P-2
P-3
P-4
ANG-NR
3115
2814
1324
24.05.2017
P-NR, NAME
angestellte, projekt
ANG-NR <> P-LEITER;
SQL - Structured Query Language
SELECT
FROM
WHERE
70
Beispiel für Join mit 3 Relationen
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:
NAME P-FILIALE
Meyer Karlsruhe
Klein Karlsruhe
24.05.2017
NAME
Meyer
Klein
AIFB
ANG-NR
3115
2814
NAME, P-FILIALE
angestellte a, ang-pro ap, projekt p
a.ANG-NR=ap.ANG-NR AND ap.P-NR=p.P-NR;
SQL - Structured Query Language
SELECT
FROM
WHERE
71
Beispiel für Mehrfach geschachtelte Queries Ergebnis:
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‘));
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
(#=n)
ABT-NR
35
30
32
30
35
35
32
32
31
31
30
31
Klein
Maus
Mayerlein
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
24.05.2017
Krämer
AIFB
(#=k)
P-NR P-FILIALE P-LEITER
761235 Karlsruhe
3115
770008 Karlsruhe
3115
770114 Heidelberg
1324
770231 Mannheim
2814
Müller
SQL - Structured Query Language
projekt
P-NAME
P-1
P-2
P-3
P-4
Meyer
72
Beispiel für „Correlated subquery“ (äußere Referenz)
P-FILIALE P-LEITER
Karlsruhe
3115
Karlsruhe
3115
Heidelberg
1324
Mannheim
2814
Ergebnis:
P-FILIALE
Kalrsruhe
P-FILIALE P-LEITER
Karlsruhe
3115
2
Karlsruhe
3115
Heidelberg
1324
1
Mannheim
2814
1
AIFB
P-NR
761235
770008
770114
770231
projekt b
P-NAME P-NR
P-1
761235
P-2
770008
P-3
770114
P-4
770231
SQL - Structured Query Language
projekt a
P-NAME
P-1
P-2
P-3
P-4
24.05.2017
SELECT DISTINCT P-FILIALE
FROM
projekt a
WHERE 1 <
(SELECT COUNT (*)
FROM
projekt b
WHERE b.P-FILIALE = a.P-FILIALE);
73
Beispiel für Abfragen mit Existenz-Quantor
P-FILIALE P-LEITER
Karlsruhe
3115
Karlsruhe
3115
Heidelberg
1324
Mannheim
2814
Ergebnis:
NAME
Müller
Maus
Groß
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
AIFB
P-NR
761235
770008
770114
770231
angestellte
ANG-NR
NAME
3115
Meyer
3207
Müller
2814
Klein
3190
Maus
2314
Groß
1324
Schmitt
1435
Mayerlein
2412
Müller
2244
Schulz
1237
Krämer
3425
Meier
2454
Schuster
SQL - Structured Query Language
projekt
P-NAME
P-1
P-2
P-3
P-4
24.05.2017
SELECT NAME
FROM angestellte
WHERE NOT EXISTS
(SELECT P-LEITER
FROM
projekt
WHERE P-LEITER = ANG-NR);
74
Beispiel für Mengenoperationen (Union)
Ergebnis:
angestellte
ANG-NR
NAME
3115
Meyer
3207
Müller
2814
Klein
3190
Maus
2314
Groß
1324
Schmitt
1435
Mayerlein
2412
Müller
2244
Schulz
1237
Krämer
3425
Meier
2454
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
Zwischenergebnis:
ANG-NR
3115
3190
2314
2412
projekt
P-NAME
P-1
P-2
P-3
P-4
Zwischenergebnis:
ANG-NR
3115
3115
P-NR
761235
770008
770114
770231
P-FILIALE P-LEITER
Karlsruhe
3115
Karlsruhe
3115
Heidelberg
1324
Mannheim
2814
AIFB
UNION
SELECT P-LEITER as ANG-NR
FROM projekt
WHERE P-FILIALE = ‘Karlsruhe‘;
24.05.2017
ANG-NR
3115
3190
2314
2412
3115
3115
SQL - Structured Query Language
SELECT ANG-NR
FROM angestellte
WHERE WOHNORT = ‘Karlsruhe‘
75
Beispiel für Mengenoperationen (INTERSECT)
Ergebnis:
SELECT ANG-NR
FROM angestellte
WHERE WOHNORT = ‘Karlsruhe‘
INTERSECT
Zwischenergebnis:
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
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
AIFB
angestellte
ANG-NR
NAME
3115
Meyer
3207
Müller
2814
Klein
3190
Maus
2314
Groß
1324
Schmitt
1435
Mayerlein
2412
Müller
2244
Schulz
1237
Krämer
3425
Meier
2454
Schuster
Zwischenergebnis:
SQL - Structured Query Language
SELECT ANG-NR
FROM ang-pro
GROUP BY ANG-NR
HAVING COUNT (ANG-NR) > 1;
24.05.2017
ANG-NR
3115
3190
76
Beispiel für Mengenoperationen (EXCEPT)
SELECT ANG-NR
FROM angestellte
WHERE NAME like ‘M%‘
EXCEPT
ANG-NR
3207
3190
1435
2412
3425
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
SQL - Structured Query Language
angestellte
ANG-NR
NAME
3115
Meyer
3207
Müller
2814
Klein
3190
Maus
2314
Groß
1324
Schmitt
1435
Mayerlein
2412
Müller
2244
Schulz
1237
Krämer
3425
Meier
2454
Schuster
AIFB
SELECT P-LEITER as ANG-NR
FROM projekt;
24.05.2017
Ergebnis:
77
