2x2 - DBAI

Relationale Abfragesprachen
Relationale Abfragesprachen
Acknowledgments
Datenmodellierung
VU 184.685, WS 2015
Relationale Abfragesprachen – SQL
Die Folien sind eine kleine Erweiterung der Folien von Katrin Seyr.
Sebastian Skritek
Institut für Informationssysteme
Technische Universität Wien
Sebastian Skritek
Seite 1
Relationale Abfragesprachen
Sebastian Skritek
Seite 2
Relationale Abfragesprachen
Überblick
1. SQL einst und jetzt
SQL einst und jetzt
SQL wurde auf Basis von relationaler Algebra und
Relationenkalkül entwickelt
deklarative Sprache
1
SQL einst und jetzt
2
Datendefinitionssprache
3
Datenabfragesprache
4
Datenmanipulationssprache
mengenorientierte Sprache
Abarbeitung Übersetzung der Abfragen mittels Parser in relationale
Algebra und Optimierung durch Anfragenoptimierer des
DBMS (VU Datenbanksysteme)
Relationen werden dargestellt in Form von Tabellen
SQL stellt eine standardisierte
Datendefinitions (DDL)Datenmanipulations (DML)Anfrage (Query)-Sprache
Sebastian Skritek
Seite 3
Sebastian Skritek
Seite 4
Relationale Abfragesprachen
1. SQL einst und jetzt
Relationale Abfragesprachen
SQL einst und jetzt
Datendefinitionssprache
Datentypen: Konstrukte für Zeichenketten, Zahlen, Datum, . . .
SQL 99: erweiterte SQL 92 um objektrelationale Konstrukte,
rekursive Abfragen und Trigger
character ( n ) ,
char ( n )
character varying ( n ) , varchar ( n )
SQL 2003: bietet erweiterte Unterstützung von Nested Tables, von
Merge Operationen und auf XML bezogene Eigenschaften
numeric (p , s )
integer , int
SQL 2006: Brücke zu XML, XQuery
SQL 2008, 2011: 2011 ist derzeit aktueller Standard
System R (IBM): erster DBMS Prototyp, Sprache: Structured English
Query Language ⇒ SQL
date
blob , raw
clob
DBMS: Oracle (Oracle Corporation), Informix (Informix),
SQL-Server (Microsoft), DB2 (IBM), PostgreSQL, MySQL
Sebastian Skritek
Relationale Abfragesprachen
2. Datendefinitionssprache
Seite 5
2. Datendefinitionssprache
Sebastian Skritek
Relationale Abfragesprachen
Datendefinitionssprache
% für große binäre bzw . Text Daten
% für große Text Daten
Seite 6
2. Datendefinitionssprache
Datendefinitionssprache
Schemadefinition und -veränderung
Schemadefinition und -veränderung
drop table Professoren ;
create table Professoren
( PersNr integer primary key ,
Name
varchar (10) not null ,
Rang
character (2));
alter table Professoren rename to Vortragende ;
alter table Professoren add Raum integer ;
alter table Professoren drop Raum ;
create table voraussetzen
( VorgNr integer ,
NachfNr integer ,
primary key ( VorgNr , NachfNr )
);
alter table Professoren alter
Name type varchar (30);
alter table Professoren alter
column Name set data type varchar (30);
Sebastian Skritek
Seite 7
Sebastian Skritek
Seite 8
Relationale Abfragesprachen
3. Datenabfragesprache
Relationale Abfragesprachen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen
Datenabfragesprache
Example
1
Einfache SQL-Anfragen
2
Anfragen über mehrere Relationen
3
Mengenoperationen
4
Aggregatfunktionen
5
Aggregate und Gruppierung
6
Geschachtelte Anfragen
7
Existenziell quantifizierte Anfragen
8
Allquantifizierte Anfragen
9
select *
from Professoren ;
Professoren
PersNr
Name
Rang
2125
Sokrates
C4
226
2126
Russel
C4
232
2127
Kopernikus
C3
310
Nullwerte
2133
Popper
C3
52
10
Spezielle Sprachkonstrukte
2134
Augustinus
C3
309
11
Sichten (Views)
2136
Curie
C4
36
2137
Kant
C4
7
Sebastian Skritek
Relationale Abfragesprachen
Seite 9
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Einfache SQL Anfragen
Raum
Seite 10
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen – Duplikate
Beispiel (Duplikatelimination)
Example
Geben Sie alle Rangbezeichnungen für Professoren aus (rel. Algebra)
select Name , Raum
from Professoren ;
πName,Raum (Professoren)
πRang (Professoren)
Ergebnis
Professoren
Name
Sokrates
226
Russel
232
Kopernikus
310
Popper
Augustinus
Rang
Raum
C4
C3
Geben Sie alle Rangbezeichnungen für Professoren aus (SQL)
select Rang
from Professoren ;
52
309
Ergebnis
Rang
Curie
36
C4
Kant
7
C4
C4
C4
Sebastian Skritek
Seite 11
Sebastian Skritek
C3
C3
Seite 12
Relationale Abfragesprachen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Relationale Abfragesprachen
Einfache SQL Anfragen – Duplikate
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen
Example
Beispiel (Duplikatelimination)
Finde die Raumnummer von Professor Popper
Geben Sie alle Rangbezeichnungen für Professoren ohne Duplikate aus
select distinct Rang
from Professoren ;
select Name , Raum
from Professoren
where Name = ‘ Popper ’;
Ergebnis
πName,Raum
σName=‘Popper 0 (Professoren)
Rang
C4
Professoren
C3
Name
distinct . . . eliminiert Duplikate aus dem Ergebnis
Popper
Sebastian Skritek
Relationale Abfragesprachen
Seite 13
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
52
Sebastian Skritek
Relationale Abfragesprachen
Einfache SQL Anfragen
Raum
Seite 14
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen
Example
Struktur einer einfachen Anfrage:
Finde die Raumnummer von Professor Popper
select Raum
from Professoren
where Name = ‘ Popper ’;
select Attribute
from Tabelle
where Bedingung ;
πRaum
σName=‘Popper 0 (Professoren)
Attribute: Liste jener Attribute, die ausgegeben werden
Tabelle: Name der Tabelle, die durchsucht wird
Professoren
Bedingung: Kriterium, das jedes ausgegebene Tupel erfüllen muss
Raum
52
Sebastian Skritek
Seite 15
Sebastian Skritek
Seite 16
Relationale Abfragesprachen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Relationale Abfragesprachen
Einfache SQL Anfragen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen – Sortierung
Beispiel
Beispiel (Sortierung)
Geben Sie Personalnummer und Name aller C4 Professoren an:
Geben Sie die Personalnummer, Name und Rang aller Professoren an.
Sortieren Sie das Ergebnis absteigend nach dem Rang und aufsteigend
nach dem Namen.
Professoren
PersNr
Name
Rang
2125
Sokrates
C4
2126
Russel
2127
Ergebnis
Raum
PersNr
Name
226
2125
Sokrates
C4
232
2126
Russel
Kopernikus
C3
310
2136
Curie
2133
Popper
C3
52
2137
Kant
2134
Augustinus
C3
309
2136
Curie
C4
2137
Kant
C4
select PersNr , Name , Rang
PersNr
from Professoren
order by Rang desc , Name asc ; 2136
Rang
Curie
C4
2137
Kant
C4
2126
Russel
C4
36
2125
Sokrates
C4
7
2134
Augustinus
C3
2127
Kopernikus
C3
2133
Popper
C3
select
PersNr , Name
from Professoren
where Rang = ’ C4 ’;
Sebastian Skritek
Relationale Abfragesprachen
Ergebnis
Name
Seite 17
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Einfache SQL Anfragen – Sortierung
Seite 18
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen
Beispiel (Sortierung)
Beispiel
Geben Sie die Personalnummer und den Namen aller Professoren an.
Sortieren Sie das Ergebnis absteigend nach dem Rang und aufsteigend
nach dem Namen.
select PersNr , Name
from Professoren
order by Rang desc , Name asc ;
Sebastian Skritek
Welche Professoren lesen die LVA Mäeutik?
Information aus Tabelle: Professoren, Vorlesungen
Ergebnis
PersNr
Name
2136
Curie
2137
Kant
2126
Russel
2125
Sokrates
2134
Augustinus
2127
Kopernikus
2133
Popper
Professoren (PersNr, Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon)
select Name , Titel
from Professoren , Vorlesungen
where PersNr = gelesenVon and
Titel = ‘ Mäeutik ’;
Seite 19
Sebastian Skritek
Seite 20
Relationale Abfragesprachen
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Relationale Abfragesprachen
Anfragen über mehrere Relationen
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Anfragen über mehrere Relationen
Beispiel
Struktur einer Abfrage über mehrere Tabellen:
Welche Professoren lesen die LVA Mäeutik?
Information aus Tabelle: Professoren, Vorlesungen
select Attribute
from Tabelle1 , Tabelle2 , ... , TabelleN
where Bedingungen ;
Professoren (PersNr, Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon)
Bedingungen: greifen auf die Attribute der verschiedenen Tabellen zu
select Name , Titel
from Professoren , Vorlesungen
where PersNr = gelesenVon and
Titel = ‘ Mäeutik ’;
Auswertung:
1
Bilde Kreuzprodukt der from Tabellen
2
Überprüfe für jede Zeile die where Bedingungen
3
Projiziere auf select Attribute
πName,Titel σPersNr=gelesenVon∧Titel=‘Mäeutik’ (Professoren × Vorlesungen)
Sebastian Skritek
Seite 21
Relationale Abfragesprachen
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
1
Name
3. Datenabfragesprache
Rang
Vorlesungen
Raum
VorlNr
Titel
SWS
PersNr
Sokrates
C4
226
5001
Grundzüge
4
2137
2126
Russel
C4
232
5041
Ethik
4
2125
2127
Kopernikus
C3
310
5043
Erkenntnistheorie
3
2126
2133
Popper
C3
52
5049
Mäeutik
2
2125
2134
Augustinus
C3
309
4052
Logik
4
2125
2136
Curie
C4
36
5052
Wissenschaftstheorie
3
2126
2137
Kant
C4
7
5216
Bioethik
2
2126
5259
Der Wiener Kreis
2
2133
5022
Glaube und Wissen
2
2134
4630
Die drei Kritiken
4
2137
×
3.2. Anfragen über mehrere Relationen
Anfragen über mehrere Relationen – Abarbeitung
2125
Sebastian Skritek
Seite 22
Bilde Kreuzprodukt der from Tabellen
Professoren
PersNr
Sebastian Skritek
Relationale Abfragesprachen
Anfragen über mehrere Relationen – Abarbeitung
Seite 23
Professoren × Vorlesungen
PersNr
Name
Rang
VorlNr
Titel
2125
Sokrates
C4
226
5001
Grundzüge
4
2137
2125
..
.
Sokrates
..
.
C4
..
.
226
..
.
5041
..
.
Ethik
..
.
4
..
.
2125
..
.
2125
..
.
Sokrates
..
.
C4
..
.
226
..
.
5049
..
.
Mäeutik
..
.
2
..
.
2125
..
.
2137
Kant
C4
7
4630
Die drei Kritiken
4
2137
2
SWS
VPersNr
Überprüfe für jede Zeile die where Bedingungen
σPersNr =VPersNr ∧Titel=0 Mäeutik 0 (Professoren × Vorlesungen)
PersNr
Name
Rang
2125
Sokrates
C4
Sebastian Skritek
Raum
Raum
226
VorlNr
Titel
5049
Mäeutik
SWS
2
VPersNr
2125
Seite 24
Relationale Abfragesprachen
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Relationale Abfragesprachen
Anfragen über mehrere Relationen – Abarbeitung
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Anfragen über mehrere Relationen
Beispiel
σPersNr =VPersNr ∧Titel=0 Mäeutik 0 (Professoren × Vorlesungen)
PersNr
Name
Rang
2125
Sokrates
C4
3
Raum
226
VorlNr
Titel
SWS
5049
Mäeutik
2
Geben Sie Name und Matrikelnummer der Studierenden und den Titel
der von ihnen gehörten Vorlesungen aus.
VPersNr
2125
Studenten (MatrNr, Name , Sem )
hören (MatrNr, VorlNr)
Vorlesungen (VorlNr, Titel , SWS , gelesenVon )
Projiziere auf select Attribute
πName,Titel (. . . )
Name
Titel
Sokrates
Mäeutik
select Studenten . MatrNr , Name , Titel
from Studenten , hören , Vorlesungen
where Studenten . MatrNr = hören . MatrNr and
hören . VorlNr = Vorlesungen . VorlNr ;
Sebastian Skritek
Relationale Abfragesprachen
Seite 25
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Sebastian Skritek
Relationale Abfragesprachen
Platzhalter für Tabellen und Umbenennung von Attributen
Seite 26
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Anfragen über mehrere Relationen
Beispiel
Selbe Anfrage bei der Vergabe von Platzhaltern für Tabellennamen:
Struktur einer Abfrage über mehrere Tabellen:
select s . MatrNr , s . Name , v . Titel
from Studenten s , hören h , Vorlesungen v
where s . MatrNr = h . MatrNr and
h . VorlNr = v . VorlNr ;
select Atrribut1 [
Attribut2 [
... ,
AttributM [
from Tabelle1 [ as
Tabelle2 [ as
... ,
TabelleN [ as
where Bedingungen ;
Beispiel
Finde die Vorlesungsnummern der Vorlesungen, die indirekte Vorgänger
2. Stufe der VO 5216 sind (= Vorgänger der Vorgänger von 5216)
voraussetzen ( VorgNr , NachfNr )
as ] a1 ,
as ] a2 ,
as ] aM
] t1 ,
] t2 ,
] tN
select v1 . VorgNr as Vorg_Stufe_2
from voraussetzen v1 , voraussetzen v2
where v1 . NachfNr = v2 . VorgNr and v2 . NachfNr =5216;
Sebastian Skritek
Seite 27
Sebastian Skritek
Seite 28
Relationale Abfragesprachen
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Relationale Abfragesprachen
3.3. Mengenoperationen
Mengenoperationen
Übersetzung in relationale Algebra
Anfragen mit typkompatiblen Ausgabeattributen können mittels
Mengenoperationen verknüpft werden.
πA1 ,...,An
Allgemeine Form einer (ungeschachteteln) SQL-Anfrage:
σP
Ohne Duplikate: union, intersect, except (minus)
Mit Duplikaten: union all, intersect all, except all
select A1 , . . . , An
from R1 , . . . , Rk
where P ;
×
×
wird zu:
πA1 ,...,An σP (R1 × · · · × Rk )
×
R1
Beispiel
Rk
Suchen Sie den Namen aller Assistenten oder Professoren
( select Name from Assistenten )
union
( select Name from Professoren );
R3
R2
Sebastian Skritek
Relationale Abfragesprachen
3. Datenabfragesprache
Seite 29
3. Datenabfragesprache
3.4. Aggregatfunktionen
Sebastian Skritek
Relationale Abfragesprachen
Aggregatfunktionen
Seite 30
3. Datenabfragesprache
3.4. Aggregatfunktionen
Aggregatfunktionen
Beispiel
Geben Sie die durchschnittliche/maximale/minimale Inskriptionsdauer der
Studenten an; geben Sie an, wieviele Studierende es gibt.
Aggregatfunktionen sind Operationen, die nicht auf einzelnen Tupeln,
sondern auf einer Menge von Tupeln arbeiten:
select avg ( Semester )
from Studenten ;
avg(), max(), min(), sum() berechnen den Wert einer Menge
von Tupeln,
select max ( Semester )
from Studenten ;
count() zählt die Anzahl der Tupel in der Menge.
select min ( Semester )
from Studenten ;
select count ( MatrNr )
from Studenten ;
Sebastian Skritek
Seite 31
Sebastian Skritek
Studenten
MatrNr
Name
Sem
24002
Xenokrates
18
25403
Jonas
12
26120
Fichte
10
26830
Aristoxenos
8
27550
Schopenhauer
6
28106
Carnap
3
29120
Theophrastos
2
29555
Feuerbach
2
Seite 32
Relationale Abfragesprachen
3. Datenabfragesprache
3.4. Aggregatfunktionen
Relationale Abfragesprachen
Aggregatfunktionen
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Aggregate und Gruppierung
Beispiel
Geben Sie die Summe der von C4 Professoren gehaltenen Vorlesungsstunden an.
select sum ( SWS )
from Vorlesungen , Professoren
where gelesenVon = PersNr and Rang = ’ C4 ’;
Lösung: Bilde zu jedem Vortragenden eine Gruppe (mittels der
group by Klause) und summiere nur innerhalb dieser
Gruppe.
Vorlesungen
VorlNr
5001
Titel
Grundzüge
SWS
4
Professoren
gelesenVon
2137
5041
Ethik
4
2125
5043
Erkenntnistheorie
3
2126
5049
Mäeutik
2
2125
4052
Logik
4
2125
5259
Der Wiener Kreis
2
2133
5216
..
.
Bioethik
..
.
2
..
.
2126
..
.
PersNr
Name
Rang
2125
Sokrates
C4
2126
Russel
C4
2133
..
.
Popper
..
.
C3
..
.
Sebastian Skritek
Relationale Abfragesprachen
Allgemein: Alle Zeilen einer Tabelle, die auf den Attributen der
group by Klause den selben Wert annehmen, werden zu
einer Gruppe zusammengefasst und die
Aggregatfunktionen werden bezüglich der Gruppe
ausgewertet.
Bedingungen an die aggregierten Werte werden in der having Klause
angeführt.
Seite 33
3. Datenabfragesprache
Problem: Wollen nicht die Summe aller Vorlesungsstunden absolut
berechnen, sondern zu jedem Vortragenden die Summe der
von ihm gehaltenen Stunden.
3.5. Aggregate und Gruppierung
Sebastian Skritek
Relationale Abfragesprachen
Bsp: Aggregate und Gruppierung
Seite 34
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Bsp: Aggregate und Gruppierung
Beispiele
Beispiele
Geben Sie zu jedem C4 Professor die Summe der von ihr/ihm gehaltenen
Vorlesungsstunden an.
Geben Sie zu jedem C4 Professor, der vor allem lange Vorlesungen
(Durchschnitt größer gleich 3) hält, die Summe der von ihr/ihm
gehaltenen Vorlesungsstunden an.
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
select gelesenVon , sum ( SWS )
from Vorlesungen , Professoren
where gelesenVon = PersNr and Rang = ’ C4 ’
group by gelesenVon ;
select gelesenVon , Name , sum ( SWS )
from Vorlesungen , Professoren
where gelesenVon = PersNr and Rang = ’ C4 ’
group by gelesenVon , Name
having avg ( SWS ) >= 3;
Sebastian Skritek
Seite 35
Sebastian Skritek
Seite 36
Relationale Abfragesprachen
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Relationale Abfragesprachen
Aggregate und Gruppierung – Abarbeitung
PersNr
Name
Rang
2125
Sokrates
C4
2125
..
.
Sokrates
..
.
C4
..
.
2125
..
.
Sokrates
..
.
2133
..
.
2137
Aggregate und Gruppierung
VorlNr
Titel
226
5001
Grundzüge
4
2137
226
..
.
5041
..
.
Ethik
..
.
4
..
.
2125
..
.
C4
..
.
226
..
.
5049
..
.
Mäeutik
..
.
2
..
.
2125
..
.
Popper
..
.
C3
..
.
52
..
.
5001
..
.
Grundzüge
..
.
4
..
.
2137
..
.
Kant
C4
7
4630
Die drei Kritiken
4
2137
SWS
gelesenVon
PersNr
Name
Rang
VorlNr
Titel
2137
Kant
C4
7
5001
Grundzüge
4
2137
2125
Sokrates
C4
226
5041
Ethik
4
2125
2126
Russel
C4
232
5043
Erkenntnistheorie
3
2126
2125
Sokrates
C4
226
5049
Mäeutik
2
2125
2125
Sokrates
C4
226
4052
Logik
4
2125
2126
Russel
C4
232
5052
Wissenschaftstheorie
3
2126
2126
Russel
C4
232
5216
Bioethik
2
2126
2137
Kant
C4
7
4630
Die drei Kritiken
4
2137
Sebastian Skritek
Seite 37
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Rang
2125
Sokrates
C4
2125
Sokrates
2125
Raum
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Aggregate und Gruppierung
having-Bedingung
VorlNr
Titel
SWS
226
5041
Ethik
4
2125
C4
226
5049
Mäeutik
2
2125
Sokrates
C4
226
4052
Logik
4
2125
2126
Russel
C4
232
5043
Erkenntnistheorie
3
2126
2126
Russel
C4
232
5052
Wissenschaftstheorie
3
2126
2126
Russel
C4
232
5216
Bioethik
2
2126
2137
Kant
C4
7
5001
Grundzüge
4
2137
2137
Kant
C4
7
4630
Die drei Kritiken
4
2137
gelesenVon
PersNr
Name
Rang
VorlNr
Titel
SWS
2125
Sokrates
C4
226
5041
Ethik
4
2125
2125
Sokrates
C4
226
5049
Mäeutik
2
2125
2125
Sokrates
C4
226
4052
Logik
4
2125
2137
Kant
C4
7
5001
Grundzüge
4
2137
2137
Kant
C4
7
4630
Die drei Kritiken
4
2137
Seite 39
Raum
gelesenVon
Aggregation (sum) und Projektion
having-Bedingung
Sebastian Skritek
gelesenVon
Seite 38
Gruppierung
Name
SWS
Sebastian Skritek
Relationale Abfragesprachen
Aggregate und Gruppierung
PersNr
Raum
Gruppierung
where-Bedingung
Relationale Abfragesprachen
3.5. Aggregate und Gruppierung
where-Bedingung
Professoren × Vorlesungen
Raum
3. Datenabfragesprache
Sebastian Skritek
gelesenVon
Name
sum(SWS)
2125
Sokrates
10
2137
Kant
8
Seite 40
Relationale Abfragesprachen
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Relationale Abfragesprachen
Aggregate und Gruppierung
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Bsp: Aggregate und Gruppierung
Beispiel
Achtung: SQL erzeugt pro Gruppe ein Ergebnistupel
Geben Sie die Namen der Studenten aus, die am längsten studieren.
⇒ Alle in der select Klausel aufgeführten Attribute- außer
den aggregierten - müssen auch in der group by Klausel
aufgeführt werden.
So wird sichergestellt, dass die ausgegebenen Attribute
sich nicht innerhalb der Gruppe ändern.
Studenten ( MatrNr , Name , Semester )
select Name , max ( Semester )
from Studenten ;
select gelesenVon , Name, sum ( SWS )
........
group by gelesenVon , Name;
⇒ ORA-00937: not a single-group group function
⇒ ERROR: column ‘‘studenten.name’’ must appear in the
GROUP BY clause or be used in an aggregate function
Sebastian Skritek
Relationale Abfragesprachen
Seite 41
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Seite 42
Relationale Abfragesprachen
Bsp: Aggregate und Gruppierung
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen
Es gibt vielfältige Möglichkeiten select Anweisungen zu verknüpfen.
Folgende Einteilung ist abhängig vom Ergebnis der Unteranfrage (ein
Wert oder eine Relation)
Beispiel (2. Versuch)
Geben Sie die Namen der Studenten aus, die am längsten studieren.
Ergebnis der Unteranfrage besteht aus einem Tupel mit einem
Attribut (= ein Wert):
Studenten ( MatrNr , Name , Semester )
• Unteranfrage anstelle eines skalaren Wertes in select bzw. where
Klausel
select Name , max ( Semester )
from Studenten
group by Name ;
Ergebnis der Unterabfrage eine Relation
• Unteranfrage in from Klausel
• Mengenvergleiche: in, not in, any, all
• Verknüpfung über Quantoren: exists
⇒ Alle Tupel ?!?
Bei geschachtelten Abfragen ist für die Laufzeit ausschlaggebend, ob es
sich um korrelierte oder unkorrelierte Abfragen handelt
Lösung: Geschachtelte Anfrage
Sebastian Skritek
Sebastian Skritek
Seite 43
Sebastian Skritek
Seite 44
Relationale Abfragesprachen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Relationale Abfragesprachen
Geschachtelte Anfragen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen
Unteranfrage in der select Klausel
Unteranfrage in der where Klausel
Beispiel
Beispiel
Geben Sie zu jedem Vortragenden seine Lehrbelastung aus.
Geben Sie die Namen der Studenten aus, die am längsten studieren.
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
Studenten ( MatrNr , Name , Semester )
select p . Name , ( select sum ( SWS )
from Vorlesungen
where gelesenVon = p . PersNr )
from Professoren p ;
select Name
from Studenten
where Semester = ( select max ( Semester )
from Studenten );
Achtung: Die Unteranfrage ist korreliert (= sie greift auf Attribute
der umschließenden Anfrage zu). Für jedes Ergebnistupel
wird die Unteranfrage einmal ausgeführt.
Sebastian Skritek
Relationale Abfragesprachen
Seite 45
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Geschachtelte Anfragen
Seite 46
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen
Unteranfrage in der from Klausel
Beispiel
Beispiel (Entschachtelung mittels group by)
Gesucht sind jene Studenten, die mehr als 2 Vorlesungen hören.
Geben Sie zu jedem Vortragenden seine Lehrbelastung aus.
Studenten ( MatrNr , Name , Semester )
hören ( MatrNr , VorlNr )
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
select tmp . MatrNr , tmp . Name , tmp . VorlAnzahl
from ( select s . MatrNr , s . Name ,
count (*) as VorlAnzahl
from Studenten s , hören h
where s . MatrNr = h . MatrNr
group by s . MatrNr , s . Name ) tmp
where tmp . VorlAnzahl > 2;
select p . PersNr , p . Name , sum ( SWS )
from Professoren p , Vorlesungen v
where v . gelesenVon = p . PersNr
group by p . PersNr , p . Name
Sebastian Skritek
Seite 47
Sebastian Skritek
Seite 48
Relationale Abfragesprachen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Relationale Abfragesprachen
Geschachtelte Anfragen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen – Mengenvergleiche
Beispiel (Entschachtelung mittels having)
Unteranfrage in der where Klausel
Mengenvergleich mit in/not in
Gesucht sind jene Studenten, die mehr als 2 Vorlesungen hören.
Beispiel
Studenten ( MatrNr , Name , Semester )
hören ( MatrNr , VorlNr )
Gesucht sind jene Professoren, die keine Lehrveranstaltungen halten.
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
select s . MatrNr , s . Name , count (*) as VorlAnzahl
from Studenten s , hören h
where s . MatrNr = h . MatrNr
group by s . MatrNr , s . Name
having count (*) > 2;
Sebastian Skritek
Relationale Abfragesprachen
select Name
from Professoren
where PersNr not in ( select gelesenVon
from Vorlesungen );
Seite 49
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Relationale Abfragesprachen
Geschachtelte Anfragen – Mengenvergleiche
Seite 50
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen – Mengenvergleiche
Mengenvergleich mit in/not in
Beispiel (Entschachtelung mittels intersect)
Beispiel
Gesucht sind für alle Studierenden jene Vorlesungen die sie gehört und zu
denen sie eine positive Prüfung abgelegt haben.
Gesucht sind für alle Studierenden jene Vorlesungen die sie gehört und zu
denen sie eine positive Prüfung abgelegt haben.
hören ( MatrNr , VorlNr )
prüfen ( MatrNr , VorlNr , PersNr , Note )
hören ( MatrNr , VorlNr )
prüfen ( MatrNr , VorlNr , PersNr , Note )
( select MatrNr , VorlNr
from hören )
intersect
( select MatrNr , VorlNr
from prüfen
where Note < 5);
select MatrNr , VorlNr
from hören
where ( MatrNr , VorlNr ) in ( select MatrNr , VorlNr
from prüfen
where Note < 5);
Sebastian Skritek
Sebastian Skritek
Seite 51
Sebastian Skritek
Seite 52
Relationale Abfragesprachen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Relationale Abfragesprachen
Geschachtelte Anfragen – Mengenvergleiche
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen – Mengenvergleiche
Mengenvergleich mit any/all (Achtung: kein Allquantor, nur der
Vergleich eines Wertes mit einer Menge von Werten.)
Beispiel
Beispiel
Suchen Sie jene Studenten, die nicht am längsten studieren.
Suchen Sie jene Studenten, die am längsten studieren.
select Name
from Studenten
where Semester < any ( select Semester
from Studenten );
select Name
from Studenten
where Semester >= all ( select Semester
from Studenten );
gleichbedeutend mit:
gleichbedeutend mit:
select Name
from Studenten
where Semester < ( select max ( Semester )
from Studenten );
select Name
from Studenten
where Semester = ( select max ( Semester )
from Studenten );
Sebastian Skritek
Relationale Abfragesprachen
Seite 53
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Relationale Abfragesprachen
Geschachtelte Anfragen – Aggregatfunktionen
Seite 54
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen – Aggregatfunktionen
Geschachtelte Aggregatfunktionen sind nicht erlaubt
⇒ Unteranfrage verwenden
Geschachtelte Aggregatfunktionen sind nicht erlaubt
⇒ Unteranfrage verwenden
Beispiel
Beispiel
Suchen Sie C4 Professoren, die die meisten Vorlesungsstunden halten.
Suchen Sie C4 Professoren, die die meisten Vorlesungsstunden halten.
select gelesenVon , sum ( SWS )
from Vorlesungen , Professoren
where gelesenVon = PersNr and Rang = ’ C4 ’
group by gelesenVon
having sum ( SWS ) >= all (
select sum ( SWS )
from Vorlesungen , Professoren
where gelesenVon = PersNr and Rang = ’ C4 ’
group by gelesenVon );
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
select gelesenVon , max(sum(SWS))
from Vorlesungen , Professoren
where gelesenVon = PersNr and Rang = ’ C4 ’
group by gelesenVon ;
Sebastian Skritek
Sebastian Skritek
Seite 55
Sebastian Skritek
Seite 56
Relationale Abfragesprachen
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Relationale Abfragesprachen
Existentiell quantifizierte Anfragen
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Existentiell quantifizierte Anfragen
Eine nicht korrelierte Lösung ist:
Verknüpfung mit einer Unteranfrage durch Existenzquantor exists
Beispiel (Nicht korreliert)
exists überprüft, ob die Unterabfrage Tupel enthält oder nicht.
Gesucht sind all jene Studenten, die älter als der jüngste Professor sind.
Beispiel
select s .*
from Studenten s
where s . GebDatum < ( select max ( p . GebDatum )
from Professoren p );
Gesucht sind all jene Studenten, die älter als der jüngste Professor sind.
select s .*
from Studenten s
where exists ( select p .*
from Professoren p
where p . GebDatum > s . GebDatum );
oder
select s .* from Studenten s
where s . GebDatum < any ( select p . GebDatum
from Professoren p );
Korrelierte Unteranfrage (s.GebDatum und Studenten s)!
oder . . .
Sebastian Skritek
Relationale Abfragesprachen
Seite 57
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Relationale Abfragesprachen
Existentiell quantifizierte Anfragen
Seite 58
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Existentiell quantifizierte Anfragen
Eine nicht korrelierte Lösung ist:
Beispiel
select Name
from Professoren
where PersNr not in ( select gelesenVon
from Vorlesungen );
Suchen Sie all jene Professoren, die keine Lehrveranstaltungen halten.
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
oder
select Name
from Professoren
where not exists ( select *
from Vorlesungen
where gelesenVon = PersNr );
( select PersNr
from Professoren )
except
( select gelesenVon
from Vorlesungen );
Korrelierte Unteranfrage (PersNr und Professoren)!
Sebastian Skritek
Sebastian Skritek
Seite 59
Sebastian Skritek
Seite 60
Relationale Abfragesprachen
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Relationale Abfragesprachen
Existentiell quantifizierte Anfragen
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Existentiell quantifizierte Anfragen
Beispiel
Beispiel
Suchen Sie jene Assistenten, die für einen Professor arbeiten, der jünger
ist, als sie selbst.
Suchen Sie jene Assistenten, die für einen Professor arbeiten, der jünger
ist, als sie selbst.
Professoren ( PersNr , Name , Rang , Raum , GebDatum )
Assistenten ( PersNr , Name , GebDatum , Boss )
Professoren ( PersNr , Name , Rang , Raum , GebDatum )
Assistenten ( PersNr , Name , GebDatum , Boss );
select a .*
from Assistenten a
where exists ( select p .*
from Professoren p
where a . Boss = p . PersNr and
p . GebDatum > a . GebDatum );
Eine nicht korrelierte Lösung mit Join ist:
select a .*
from Assistenten a , Professoren p
where a . Boss = p . PersNr and
p . GebDatum > a . GebDatum ;
Korrelierte Unteranfrage (a.GebDatum und Assistenten a)!
Sebastian Skritek
Relationale Abfragesprachen
Seite 61
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen
Seite 62
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – Nicht direkt in SQL
Beispiel
Beispiel
Welche Studenten haben alle 4 stündigen Lehrveranstaltungen gehört?
Welche Studenten haben alle 4 stündigen Lehrveranstaltungen gehört?
Studenten ( MatrNr , Name , Semester )
hören ( MatrNr , VorlNr )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
hören ÷ πVorlNr σSWS=4 (Vorlesungen)
SQL stellt keinen Allquantor zur Verfügung. Realisierung durch
SQL stellt keinen Allquantor zur Verfügung.
Sebastian Skritek
Sebastian Skritek
Seite 63
1
Logische Äquivalenz (mittels 2 × not exists)
2
Teilmengen (mittels exists und except)
3
Abzählen (mittels count)
4
Division (mittels except)
Sebastian Skritek
Seite 64
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
SQL Umsetzung folgt nun direkt aus:
Suchen Sie jene Studenten für die nicht gilt:
es gibt eine 4 stündige Lehrveranstaltung, für die nicht gilt:
der Student hat diese Lehrveranstaltung gehört.
∀xF (x) ⇔ ¬∃x(¬F (x))
Beispiel
Beispiel
select s.*
from Studenten s
where not exists
(select *
from Vorlesungen v
where v.SWS = 4 and
not exists ( select *
from hören h
where h . VorlNr = v . VorlNr
and s . MatrNr = h . MatrNr ));
Welche Studenten haben alle 4 stündigen Lehrveranstaltungen gehört?
⇔ Suchen Sie jene Studenten für die gilt:
haben alle 4 stündigen Lehrveranstaltungen gehört
⇔ Suchen Sie jene Studenten für die nicht gilt:
haben eine 4 stündige Lehrveranstaltung nicht gehört
⇔ Suchen Sie jene Studenten für die nicht gilt:
es gibt eine 4 stündige Lehrveranstaltung,
die der Student nicht gehört hat.
Sebastian Skritek
Seite 65
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Umformulierung in äqivalente Anfrage mit Negation und Existenzquantor
(Prädikatenlogik)
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Seite 66
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 1. “Logische Umformung”
SQL Umsetzung folgt nun direkt aus:
Suchen Sie jene Studenten für die nicht gilt:
es gibt eine 4 stündige Lehrveranstaltung, für die nicht gilt:
der Student hat diese Lehrveranstaltung gehört.
Umformung im relationalen Tupelkalkül:
Beispiel
Beispiel
select s.*
from Studenten s
where not exists
(select *
from Vorlesungen v
where v.SWS = 4 and
s.MatrNr not in ( select h . MatrNr
from hören h
where h . VorlNr = v . VorlNr ));
Sebastian Skritek
Seite 67
Studenten (MatrNr, Name , Sem )
hören (MatrNr, VorlNr)
Vorlesungen (VorlNr, Titel , SWS , gelesenVon )
{s
| s ∈ Studenten ∧ ∀v ∈ Vorlesungen(v .SWS = 4 →
Sebastian Skritek
∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = s.MatrNr ))}
Seite 68
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Beispiel
| s ∈ Studenten ∧ ∀v ∈ Vorlesungen(v .SWS = 4 →
∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = s.MatrNr ))}
| s ∈ Studenten ∧ ¬∃v ∈ Vorlesungen¬(¬(v .SWS = 4) ∨
(∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = s.MatrNr )))}
⇔
{s
| s ∈ Studenten ∧ ¬∃v ∈ Vorlesungen((v .SWS = 4) ∧
(¬∃h ∈ hören(v .VorlNr = h.VorlNr ∧ s.MatrNr = h.MatrNr )))}
(¬∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = s.MatrNr )))}
Sebastian Skritek
Relationale Abfragesprachen
Seite 69
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 2. “Teilmengen”
Seite 70
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 2. “Teilmengen”
Suchen Sie alle Studenten M für die nicht gilt:
Es gibt eine LVA in der Differenz
Menge aller 4 stündigen LVAs − Menge der von M gehörten LVAs
Suchen Sie alle Studierenden M für die gilt:
Menge aller 4 stündigen LVAs ⊆ Menge der von M gehörten LVAs
Beispiel
“⊆” kein Operator in SQL, aber
select s.*
from Studenten s
where not exists
((select VorlNr
from Vorlesungen v
where v.SWS = 4)
except
(select VorlNr
from hören h
where h.MatrNr = s.MatrNr))
Menge aller 4 stündigen LVAs ⊆ Menge der von M gehörten LVAs
⇔
Menge aller 4 stündigen LVAs − Menge der von M gehörten LVAs = ∅
∅ . . . es existiert keine Vorlesung in der Mengendifferenz
Sebastian Skritek
| s ∈ Studenten ∧ ¬∃v ∈ Vorlesungen((v .SWS = 4) ∧
select s.*
from Studenten s
where not exists
(select *
from Vorlesungen v
where v.SWS = 4 and
not exists ( select *
from hören h
where h . VorlNr = v . VorlNr
and s . MatrNr = h . MatrNr ));
| s ∈ Studenten ∧ ¬∃v ∈ Vorlesungen¬(v .SWS = 4 →
⇔
{s
{s
∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = t.MatrNr ))}
⇔
{s
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Beispiel
{s
3. Datenabfragesprache
Seite 71
Sebastian Skritek
Seite 72
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 3. “Abzählen”
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Allquantifizierung kann immer auch durch eine count-Aggregation
ausgedrückt werden
Beispiel
Welche Studierenden haben alle 4-stündigen Vorlesungen gehört?
Beispiel
hören
Welche Studenten haben alle 4 stündigen Lehrveranstaltungen gehört?
MatrNr
VorlNr
5001
hören ÷ πVorlNr σSWS=4 (Vorlesungen)
1
Zähle zu jedem Studenten, wieviele 4 stündigen LVAs er besucht hat
26120
2
zähle wieviele 4 stündige LVAs es gibt.
27550
5001
πVorlNr σSWS=4 (Vorlesungen)
27550
4052
VorlNr
28106
5041
5001
28106
5001
28106
4052
28106
4630
29120
5001
29120
5041
29120
5049
select s . MatrNr , s . Name
from Studenten s , hören h , Vorlesungen v
where s . MatrNr = h . Matrnr and
h . VorlNr = v . VorlNr and
v . SWS = 4
group by s . MatrNr , s . Name
having count (*) = (select count (*) from Vorlesungen
where SWS = 4);
Sebastian Skritek
Relationale Abfragesprachen
Seite 73
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Bilde alle Paare von Studenten und 4 stündigen LVAs
Inhalt der DB wenn alle Studenten alle 4 stündigen LVAs absolviert
hätten → suche Abweichungen
2
Entferne davon alle Paare (Student, LVA) an LVAs die ein Student
besucht hat (d.h. der Inhalt von hören)
Diese Paare aus Studenten und 4 stündigen LVAs “fehlen” in der DB
3
Aus den übrig gebliebenen Paaren, sammle die Studenten
Studenten die mindestens eine 4 stündige LVA nicht besucht haben
4
Ziehe diese Studenten von der Menge aller Studenten ab
Studenten die alle 4 stündigen LVAs besucht haben
28106
4052
4630
Seite 74
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
1
Bilde alle Paare von Studenten und 4 stündigen LVAs
2
Entferne davon alle Paare (Student, LVA) an LVAs die ein Student
besucht hat (d.h. der Inhalt von hören)
3
Aus den übrig gebliebenen Paaren, sammle die Studenten
4
Ziehe diese Studenten von der Menge aller Studenten ab
Beispiel
(select sja.MatrNr from Studenten sja)
except
(select snein.MatrNr
from ((select s.MatrNr, v.VorlNr
from Studenten s, Vorlesungen v
where v.SWS = 4)
except
(select * from hören)) snein);
R ÷ S = πR−S (R) − πR−S ((πR−S (R) × S) − R)
Sebastian Skritek
=
Allquantifizierte Anfragen – 4. Relationale Algebra?
Die Division kann durch die primitiven Operatoren ausgedrückt werden
1
5041
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 4. Relationale Algebra?
÷
R ÷S
MatrNr
Seite 75
Sebastian Skritek
Seite 76
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Relationale Abfragesprachen
Nullwerte
Nullwerte
Beispiel
Beispiel
3. Datenabfragesprache
3.9. Nullwerte
Finde die Anzahl der Studenten:
Studenten
MatrNr
Name
24002
Xenokrates
Semester
18
25403
Jonas
12
26120
Fichte
10
26830
Aristoxenos
8
27550
Schopenhauer
6
28106
Carnap
3
select count (*) from Studenten ;
(6)
select count ( MatrNr ) from Studenten ;
(6)
select count ( Semester ) from Studenten ;
(6)
select count (*) from Studenten
where Semester < 13 or Semester >= 13;
(6)
Finde die Anzahl der Studenten.
Sebastian Skritek
Relationale Abfragesprachen
Seite 77
3. Datenabfragesprache
3.9. Nullwerte
Sebastian Skritek
Relationale Abfragesprachen
Nullwerte
Seite 78
3. Datenabfragesprache
3.9. Nullwerte
Nullwerte
Nullwerte entstehen
wenn kein Wert in der Datenbank vorhanden ist,
Beispiel
im Zuge der Anfrageauswertung (Bsp. äußere Joins)
Finde die Anzahl der Studenten:
Beispiel
Studenten
MatrNr
Name
Semester
24002
Xenokrates
18
25403
Jonas
12
26120
Fichte
NULL
26830
Aristoxenos
8
27550
Schopenhauer
6
28106
Carnap
3
select count (*) from Studenten ;
(6)
select count ( MatrNr ) from Studenten ;
(6)
select count ( Semester ) from Studenten ;
(5)
select count (*) from Studenten
where Semester < 13 or Semester >= 13;
(5)
Finde die Anzahl der Studenten.
Sebastian Skritek
Seite 79
Sebastian Skritek
Seite 80
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Relationale Abfragesprachen
Einschub: count revisited
3. Datenabfragesprache
3.9. Nullwerte
Nullwerte
Verhalten von count():
Beispiel
count(*):
Zählt alle Tupel (inkl. Duplikate)
Gibt es Tupel, bei denen der Wert für Semester unbekannt ist, so gilt:
select count (*)
from Student
where Semester < 13 or Semester >= 13;
count(attribute):
Zählt die Anzahl der von NULL verschiedenen Werte in der
angegebenen Spalte (inkl. Duplikate)
6=
count(distinct attribute):
Zählt die Anzahl der verschiedenen von NULL verschiedenen Werte
in der angegebenen Spalte (d.h: ohne Duplikate)
Sebastian Skritek
Relationale Abfragesprachen
select count (*) from Studenten ;
Seite 81
3. Datenabfragesprache
3.9. Nullwerte
Sebastian Skritek
Relationale Abfragesprachen
Nullwerte: Behandlung in Ausdrücken
Seite 82
3. Datenabfragesprache
3.9. Nullwerte
Nullwerte – Auswertung logischer Ausdrücke
Logische Ausdrücke: werden nach den folgenden Tabellen berechnet:
Arithmetische Ausdrücke: Nullwerten werden propagiert: ist ein Operand
null so ist das Ergebnis null.
not
Beispiel
null + 1 = null; null * 0 = null; . . .
Vergleichsoperatoren: SQL hat dreiwertige Logik: true, false, unknown.
Das Resultat ist unknown, wenn mindestens eines der
Argumente null ist.
and
Beispiel
(Semester > 13) liefert unknown, wenn das Semester unbekannt ist, d.h.
den Wert null annimmt.
Achtung! z.B. auch (Semester = null)
Sebastian Skritek
Seite 83
Sebastian Skritek
true
false
unknown
unknown
false
true
true
unknown
false
true
true
unknown
false
unknown
unknown
unknown
false
false
false
false
false
or
true
unknown
false
true
true
true
true
unknown
true
unknown
unknown
false
true
unknown
false
Seite 84
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Relationale Abfragesprachen
Nullwerte – Auswertung
between
like
Gruppierung: wird null als ein eigenständiger Wert aufgefasst und in eine
eigene Gruppe eingeordnet.
case
Joins
Nullwerte werden abgefragt mittels: is null bzw. is not null.
•
•
•
•
Beispiel
select *
from Student
where Semester is null ;
Sebastian Skritek
Seite 85
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Spezielle Sprachkonstrukte
where-Bedingung: es werden nur Tupel eitergereicht, für die die
Bedingung zu true auswertet. Tupel, für die die Bedingung
zu unknown auswertet, werden nicht ins Ergebnis
aufgenommen.
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
cross join
natural join
join
left, right, full outer join
Sebastian Skritek
Relationale Abfragesprachen
between
Seite 86
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
like
Vergleich von Zeichenketten: Platzhalter ’%’ und ’ ’
%: beliebig viele (auch gar kein) Zeichen
: genau ein Zeichen
Beispiele
Suchen Sie jene Studenten, die zwischen dem ersten und dem vierten
Semester inskribiert sind:
Beispiele
Suchen Sie die Matrikelnummer von Theophrastos, wobei Sie nicht
wissen, ob er sich mit ’h’ schreibt:
select * from Studenten
where Semester > = 1 and Semester < = 4;
select * from Studenten
where Name like ’T % eophrastos ’;
select * from Studenten
where Semester between 1 and 4;
Suchen Sie die Matrikelnummern jener Studenten, die mindestens eine
LVA über Ethik gehört haben:
select * from Studenten
where Semester in (1 ,2 ,3 ,4);
Sebastian Skritek
select distinct MatrNr
from Vorlesungen v , hören h
where h . VorlNr = v . VorlNr and
v . Titel like ’% thik % ’;
Seite 87
Sebastian Skritek
Seite 88
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Joins
case
SQL unterstützt folgende Join-Schlüsselworte:
cross join Kreuzprodukt
natural join natürlicher Join
[inner] join Theta-Join (oder inner join)
(left|right|full) outer join äußerer Join
Beispiel
Wandeln Sie die Prüfungsnoten in Werte der Studienabteilung um:
select MatrNr , ( case when
when
when
when
else
from prüfen ;
Note
Note
Note
Note
’ N5 ’
< 1.5
< 2.5
< 3.5
< 4.0
end )
then
then
then
then
’ S1 ’
’ U2 ’
’ B3 ’
’ G4 ’
cross join:
R1 × R2
select * from Professoren , Vorlesungen ;
Die erste qualifizierende when-Klausel wird ausgeführt
select * from Professoren cross join Vorlesungen ;
Sebastian Skritek
Relationale Abfragesprachen
Seite 89
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Sebastian Skritek
Relationale Abfragesprachen
natural join:
Seite 90
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
natural join:
Beispiel
R1
Geben Sie Name und Matrikelnummer der Studierenden und den Titel
der von ihnen gehörten Vorlesungen aus.
R2
Die Werte jener Spalten, deren Attributnamen dieselben sind, werden
gleichgesetzt.
Studenten (MatrNr, Name , Sem )
hören (MatrNr, VorlNr)
Vorlesungen (VorlNr, Titel , SWS , gelesenVon )
Beispiel
Geben Sie Name und Matrikelnummer der Studierenden und den Titel
der von ihnen gehörten Vorlesungen aus.
select Studenten . MatrNr , Name , Titel
from Studenten , hören , Vorlesungen
where Studenten . MatrNr = hören . MatrNr and
hören . VorlNr = Vorlesungen . VorlNr ;
Studenten (MatrNr, Name , Sem )
hören (MatrNr, VorlNr)
Vorlesungen (VorlNr, Titel , SWS , gelesenVon )
Sebastian Skritek
select MatrNr , Name , Titel
from Studenten natural join hören
natural join Vorlesungen ;
Seite 91
Sebastian Skritek
Seite 92
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
[inner] join:
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
(left|right|full) outer join:
R1
θ
R2
R1 (| |)R2
Beispiel
Welche Professoren lesen die LVA Mäeutik?
Beispiel
Professoren (PersNr, Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon)
Geben Sie eine Liste aller Studierenden aus und ihre Noten zu den
abgeprüften Vorlesungen.
select Name , Titel
from Professoren , Vorlesungen
where PersNr = gelesenVon and
Titel = ’ Mäeutik ’;
select s . MatrNr , s . Name , p . VorlNr , p . Note
from Studenten s left outer join prüfen p
on s . MatrNr = p . MatrNr ;
select s . MatrNr , s . Name , p . VorlNr , p . Note
from prüfen p right outer join Studenten s
on s . MatrNr = p . MatrNr ;
select Name , Titel
from Professoren join
Vorlesungen on PersNr = gelesenVon
where Titel = ’ Mäeutik ’;
Sebastian Skritek
Relationale Abfragesprachen
Seite 93
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
Studenten pr üfen
MatrNr
Name
24002
Xenokrates
25403
Jonas
26120
Fichte
26830
Aristoxenos
27550
VorlNr
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
coalesce()
2
Schopenhauer
4630
2
28106
Carnap
5001
1
29120
Theophrastos
29555
Feuerbach
Beispiel
Geben Sie eine Liste aller Studierenden aus und ihre Noten zu den
abgeprüften Vorlesungen. Für Studierende die keine Vorlesung besucht
haben sollen VorlNr und Note den Wert 0 annehmen.
select s . MatrNr , s . Name ,
coalesce ( p . VorlNr ,0) ,
coalesce ( p . Note ,0)
from Studenten s left outer join prüfen p
on s . MatrNr = p . MatrNr ;
Ergebnis ohne Verwendung des outer Joins:
Sebastian Skritek
Seite 94
Note
5041
Studenten
Sebastian Skritek
pr üfen
MatrNr
Name
VorlNr
25403
Jonas
5041
Note
2
27550
Schopenhauer
4630
2
28106
Carnap
5001
1
Achtung: Typen müssen kompatibel sein
Seite 95
Sebastian Skritek
Seite 96
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
3.11. Sichten (Views)
Sichten (Views)
concat()
. . . sind gespeicherte Anfragen, die als virtuelle Tabelle zur
Verfügung stehen.
. . . waren ursprünglich nur für den Lesezugriff gedacht, sind
aber unter bestimmten Einschränkungen auch
update-fähig.
. . . werden bei jedem Zugriff dynamisch neu erstellt.
. . . sind nicht Teil des physischen Schemas
Verwendung:
Konzept zur Anpassung an verschiedene Benutzer
Vereinfachung von Anfragen
Realisierung der Generalisierung
DBMS proprietär:
materialized views (ORACLE): präkompilierte nicht
mehr virtuelle Anfrage
indexed views (SQL Server): zusätzlich gespeicherter
Index
verwendet zur Beschleunigung von sehr häufig
durchgeführten Anfragen.
Beispiel
Studenten ( MatrNr , Vorname , Nachname , Semester );
Geben Sie den Namen aller Studierenden aus
select Vorname || Nachname from Studenten ;
oder
select concat ( Vorname , Nachname ) from Studenten ;
(oft auch “+” anstatt “||”)
Sebastian Skritek
Relationale Abfragesprachen
3. Datenabfragesprache
Seite 97
3. Datenabfragesprache
3.11. Sichten (Views)
Sebastian Skritek
Relationale Abfragesprachen
Anpassung an unterschiedliche Benutzergruppen
Seite 98
3. Datenabfragesprache
3.11. Sichten (Views)
Vereinfachung von Anfragen
Beispiel
Vermeidung der ständigen Verwendung des Joins
Studenten hören Vorlesungen Professoren
Beispiel
create view
StudProf ( Sname , Semester , Titel , Pname ) as
select s . Name , s . Semester , v . Titel , p . Name
from Studenten s , hören h ,
Vorlesungen v , Professoren p
where s . MatrNr = h . MatrNr and
h . VorlNr = v . VorlNr and
v . gelesenVon = p . PersNr ;
Aus Datenschutzgründen soll im Allgemeinen nur die Prüfungsliste, nicht
aber die Note lesbar sein:
create view prüfenSicht as
select MatrNr , VorlNr , PersNr
from prüfen ;
Suchen Sie Name, Semester aller Studenten von Sokrates
select distinct Name , Semester from StudProf
where Pname = ’ Sokrates ’;
Sebastian Skritek
Seite 99
Sebastian Skritek
Seite 100
Relationale Abfragesprachen
3. Datenabfragesprache
3.11. Sichten (Views)
Relationale Abfragesprachen
Realisierung der Generalisierung
3. Datenabfragesprache
3.11. Sichten (Views)
Realisierung der Generalisierung
Beispiel (Untertypen als Sicht)
create table Angestellte ( PersNr integer not null ,
Name varchar (30) not null );
create table ProfDaten ( PersNr integer not null ,
Rang character (2) ,
Raum integer );
create table AssiDaten ( PersNr integer not null ,
Fachgebiet varchar (30) ,
Boss integer );
Sichten realisieren Inklusion und Vererbung:
Tupel des Untertyps sollen auch automatisch zum Obertyp gehören
Attribute des Obertyps: sollen automatisch vererbt werden.
⇒
entweder Untertypen oder Obertypen als Sicht definieren
Entscheidung aufgrund der Häufigkeit der Zugriffe
create view Professoren as select *
from Angestellte natural join ProfDaten ;
create view Assistenten as select *
from Angestellte natural join AssiDaten ;
Sebastian Skritek
Relationale Abfragesprachen
Seite 101
3. Datenabfragesprache
3.11. Sichten (Views)
Sebastian Skritek
Relationale Abfragesprachen
Realisierung der Generalisierung
Seite 102
3. Datenabfragesprache
3.11. Sichten (Views)
Updates auf Sichten
Beispiel (Obertypen als Sicht)
Problematisch: insert, update, delete auf eine Sicht
create table Professoren ( PersNr integer not null ,
Name varchar (30) not null ,
Rang character (2) ,
Raum integer );
create table Assistenten ( PersNr integer not null ,
Name varchar (30) not null ,
Fachgebiet varchar (30) ,
Boss integer );
create table AndereAnges ( PersNr integer not null ,
Name varchar (30) not null );
create view Angestellte as
( select PersNr , Name from Professoren ) union
( select PersNr , Name from Assistenten ) union
( select * from AndereAnges );
Sebastian Skritek
Seite 103
Beispiele (nicht updatefähige Sichten)
create view
WieHartAlsPrüfer ( PersNr , Durchschnitt ) as
select PersNr , avg ( Note )
from prüfen
group by PersNr ;
create view VorlesungenSicht as
select Titel , SWS , Name
from Vorlesungen , Professoren
where gelesenVon = PersNr ;
Sebastian Skritek
Seite 104
Relationale Abfragesprachen
3. Datenabfragesprache
3.11. Sichten (Views)
Relationale Abfragesprachen
Updates auf Sichten
4. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Einfügen
Einfügen von Tupeln in eine angelegte Tabelle
Einschränkungen der update-fähigen Sichten in SQL auf:
Professoren ( PersNr : integer , Name : varchar (30) ,
Rang : character (2) , Raum : integer )
nur eine Basisrelation
Schlüssel muss vorhanden sein
keine Aggregatfunktionen, Gruppierung und Duplikateliminierung
Beispiel
Einfügen der Professorin Curie:
alle Sichten
insert into Professoren
values (2136 , ’ Curie ’ , ’ C4 ’ , 36);
theoretisch änderbare Sichten
Einfügen mehrerer Professoren:
in SQL änderbare Sichten
insert into Professoren
values (2125 , ’ Sokrates ’ , ’ C4 ’ , 226) ,
(2126 , ’ Russel ’ , ’ C4 ’ , 232);
Sebastian Skritek
Seite 105
Relationale Abfragesprachen
4. Datenmanipulationssprache
Sebastian Skritek
Relationale Abfragesprachen
Datenmanipulationssprache – Tupel Einfügen
Seite 106
4. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Löschen
Auflisten der zu löschenden Tupeln:
Das Ergebnis einer Anfrage in eine Tabelle eintragen:
Beispiel
Beispiel
Löschen des Herrn Kant aus der Professorentabelle:
Eintragen aller Studenten zur Vorlesung ‘Logik’:
delete from Professoren
values (2137 , ’ Kant ’ , ’ C4 ’ , 7);
hören ( MatrNr , VorlNr )
Studenten ( MatrNr , Name , Sem )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
Lösche alle Tupel die eine Bedingung erfüllen:
Beispiel
insert into hören
select MatrNr , VorlNr
from Studenten , Vorlesungen
where Titel = ’ Logik ’;
Löschen des Herrn Kant aus der Professorentabelle:
delete from Professoren where PersNr =2137;
delete from Professoren where PersNr < 2137;
Sebastian Skritek
Seite 107
Sebastian Skritek
Seite 108
Relationale Abfragesprachen
4. Datenmanipulationssprache
Relationale Abfragesprachen
Datenmanipulationssprache – Tupel Löschen
4. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Verändern
Verändern von Tupeln
Beispiel
Beispiel
Lösche alle Studenten, die die Vorlesung ‘Logik’ besuchen
Erhöhen der Semesteranzahl aller Studierender um 1:
hören ( MatrNr , VorlNr )
Studenten ( MatrNr , Name , Sem )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
update Studenten
set Semester = Semester + 1;
delete from Studenten
where MatrNr in (
select MatrNr
from hören , Vorlesungen
where hören . VorlNr = Vorlesungen . VorlNr and
Titel = ’ Logik ’ );
Sebastian Skritek
Relationale Abfragesprachen
Beispiel
Alle C3 Professoren mit einer Personalnummer über 2500 erhalten den
Rang C2
update Professoren
set Rang = ’ C2 ’
where Rang = ’ C3 ’ and PersNr > 2500;
Seite 109
4. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Verändern
Verändern von Tupeln
Beispiel
Professoren ( PersNr : integer , Name : varchar (30) ,
Rang : character (2) , Raum : integer ,
Lehrbelastung: integer )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
Trage für jeden Professor seine Lehrbelastung (=Summe SWS) ein
update Professoren
set Lehrbelastung = (
select sum ( SWS )
from Vorlesungen v
where v . PersNr = Professoren . PersNr )
(Anmerkung: Lehrbelastung sollte so nicht gespeichert werden)
Sebastian Skritek
Seite 111
Sebastian Skritek
Seite 110