Datenmodellierung Acknowledgments Überblick

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
1. SQL einst und jetzt
SQL einst und jetzt
Überblick
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
create table Professoren
( PersNr integer primary key ,
Name
varchar (10) not null ,
Rang
character (2));
create table Professoren
( PersNr integer primary key ,
Name
varchar (10) not null ,
Rang
character (2));
create table voraussetzen
( VorgNr integer ,
NachfNr integer ,
primary key ( VorgNr , NachfNr )
);
Sebastian Skritek
Seite 7
Sebastian Skritek
Seite 7
Relationale Abfragesprachen
2. Datendefinitionssprache
Relationale Abfragesprachen
Datendefinitionssprache
2. Datendefinitionssprache
Datendefinitionssprache
Schemadefinition und -veränderung
Schemadefinition und -veränderung
drop table Professoren ;
drop table Professoren ;
alter table Professoren rename to Vortragende ;
alter table Professoren add Raum integer ;
alter table Professoren drop Raum ;
alter table Professoren alter
Name type varchar (30);
Sebastian Skritek
Relationale Abfragesprachen
Seite 8
2. Datendefinitionssprache
Sebastian Skritek
Relationale Abfragesprachen
Datendefinitionssprache
Seite 8
3. Datenabfragesprache
Datenabfragesprache
Schemadefinition und -veränderung
drop table Professoren ;
alter table Professoren rename to Vortragende ;
alter table Professoren add Raum integer ;
alter table Professoren drop Raum ;
alter table Professoren alter
Name type varchar (30);
alter table Professoren alter
column Name set data type varchar (30);
Sebastian Skritek
Seite 8
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
Nullwerte
10
Spezielle Sprachkonstrukte
11
Sichten (Views)
Sebastian Skritek
Seite 9
Relationale Abfragesprachen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Relationale Abfragesprachen
Einfache SQL Anfragen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen
Example
Example
select *
from Professoren ;
select *
from Professoren ;
Professoren
Sebastian Skritek
Relationale Abfragesprachen
Seite 10
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Rang
Sokrates
C4
Raum
226
2126
Russel
C4
232
2127
Kopernikus
C3
310
2133
Popper
C3
52
2134
Augustinus
C3
309
2136
Curie
C4
36
2137
Kant
C4
7
Seite 10
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen
Example
select Name , Raum
from Professoren ;
Name
2125
Sebastian Skritek
Relationale Abfragesprachen
Einfache SQL Anfragen
PersNr
Example
select Name , Raum
from Professoren ;
πName,Raum (Professoren)
πName,Raum (Professoren)
Professoren
Name
226
Russel
232
Kopernikus
310
Popper
Augustinus
Sebastian Skritek
Seite 11
Sebastian Skritek
Raum
Sokrates
52
309
Curie
36
Kant
7
Seite 11
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 – Duplikate
Beispiel (Duplikatelimination)
Beispiel (Duplikatelimination)
Geben Sie alle Rangbezeichnungen für Professoren aus (rel. Algebra)
Geben Sie alle Rangbezeichnungen für Professoren aus (rel. Algebra)
πRang (Professoren)
πRang (Professoren)
Ergebnis
Rang
C4
C3
Sebastian Skritek
Relationale Abfragesprachen
Seite 12
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Einfache SQL Anfragen – Duplikate
Seite 12
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen – Duplikate
Beispiel (Duplikatelimination)
Beispiel (Duplikatelimination)
Geben Sie alle Rangbezeichnungen für Professoren aus (rel. Algebra)
Geben Sie alle Rangbezeichnungen für Professoren aus (rel. Algebra)
πRang (Professoren)
πRang (Professoren)
Geben Sie alle Rangbezeichnungen für Professoren aus (SQL)
Geben Sie alle Rangbezeichnungen für Professoren aus (SQL)
select Rang
from Professoren ;
select Rang
from Professoren ;
Ergebnis
Rang
C4
C4
C4
C4
C3
C3
C3
Sebastian Skritek
Seite 12
Sebastian Skritek
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 – Duplikate
Beispiel (Duplikatelimination)
Beispiel (Duplikatelimination)
Geben Sie alle Rangbezeichnungen für Professoren ohne Duplikate aus
Geben Sie alle Rangbezeichnungen für Professoren ohne Duplikate aus
select distinct Rang
from Professoren ;
select distinct Rang
from Professoren ;
Ergebnis
Rang
C4
C3
Sebastian Skritek
Relationale Abfragesprachen
Seite 13
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Einfache SQL Anfragen – Duplikate
Seite 13
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 ;
Ergebnis
Rang
C4
C3
distinct . . . eliminiert Duplikate aus dem Ergebnis
Sebastian Skritek
Seite 13
Sebastian Skritek
Seite 14
Relationale Abfragesprachen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Relationale Abfragesprachen
Einfache SQL Anfragen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen
Example
Example
Finde die Raumnummer von Professor Popper
Finde die Raumnummer von Professor Popper
select Name , Raum
from Professoren
where Name = ‘ Popper ’;
select Name , Raum
from Professoren
where Name = ‘ Popper ’;
πName,Raum
σName=‘Popper 0 (Professoren)
πName,Raum
σName=‘Popper 0 (Professoren)
Professoren
Name
Popper
Sebastian Skritek
Relationale Abfragesprachen
Seite 14
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
Example
Finde die Raumnummer von Professor Popper
Finde die Raumnummer von Professor Popper
select Raum
from Professoren
where Name = ‘ Popper ’;
πRaum
σName=‘Popper 0 (Professoren)
Professoren
Raum
52
Sebastian Skritek
Seite 15
Sebastian Skritek
Seite 15
Relationale Abfragesprachen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Relationale Abfragesprachen
Einfache SQL Anfragen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen
Struktur einer einfachen Anfrage:
Struktur einer einfachen Anfrage:
select Attribute
from Tabelle
where Bedingung ;
select Attribute
from Tabelle
where Bedingung ;
Attribute: Liste jener Attribute, die ausgegeben werden
Tabelle: Name der Tabelle, die durchsucht wird
Bedingung: Kriterium, das jedes ausgegebene Tupel erfüllen muss
Sebastian Skritek
Seite 16
Relationale Abfragesprachen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Sebastian Skritek
Seite 16
Relationale Abfragesprachen
Einfache SQL Anfragen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen
Beispiel
Beispiel
Geben Sie Personalnummer und Name aller C4 Professoren an:
Geben Sie Personalnummer und Name aller C4 Professoren an:
Professoren
Ergebnis
Raum
Professoren
Ergebnis
PersNr
Name
Rang
PersNr
Name
PersNr
Name
Rang
PersNr
Name
2125
Sokrates
C4
226
2125
Sokrates
2125
Sokrates
C4
Raum
226
2125
Sokrates
2126
Russel
C4
232
2126
Russel
2126
Russel
C4
232
2126
Russel
2127
Kopernikus
C3
310
2136
Curie
2127
Kopernikus
C3
310
2136
Curie
2133
Popper
C3
52
2137
Kant
2133
Popper
C3
52
2137
Kant
2134
Augustinus
C3
309
2134
Augustinus
C3
309
2136
Curie
C4
36
2136
Curie
C4
36
2137
Kant
C4
7
2137
Kant
C4
7
select
PersNr , Name
from Professoren
where Rang = ’ C4 ’;
Sebastian Skritek
Seite 17
Sebastian Skritek
Seite 17
Relationale Abfragesprachen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Relationale Abfragesprachen
Einfache SQL Anfragen – Sortierung
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen – Sortierung
Beispiel (Sortierung)
Beispiel (Sortierung)
Geben Sie die Personalnummer, Name und Rang aller Professoren an.
Sortieren Sie das Ergebnis absteigend nach dem Rang und aufsteigend
nach dem Namen.
Geben Sie die Personalnummer, Name und Rang aller Professoren an.
Sortieren Sie das Ergebnis absteigend nach dem Rang und aufsteigend
nach dem Namen.
Ergebnis
PersNr
Name
Rang
2136
Curie
C4
2137
Kant
C4
2126
Russel
C4
2125
Sokrates
2134
Augustinus
2127
2133
Rang
Curie
C4
2137
Kant
C4
2126
Russel
C4
C4
2125
Sokrates
C4
C3
2134
Augustinus
C3
Kopernikus
C3
2127
Kopernikus
C3
Popper
C3
2133
Popper
C3
Seite 18
3. Datenabfragesprache
Ergebnis
Name
Sebastian Skritek
Relationale Abfragesprachen
select PersNr , Name , Rang
PersNr
from Professoren
order by Rang desc , Name asc ; 2136
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?
Ergebnis
PersNr
Name
2136
Curie
2137
Kant
2126
Russel
2125
Sokrates
2134
Augustinus
2127
Kopernikus
2133
Popper
Seite 19
Sebastian Skritek
Seite 20
Relationale Abfragesprachen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Relationale Abfragesprachen
Einfache SQL Anfragen
3. Datenabfragesprache
3.1. Einfache SQL Anfragen
Einfache SQL Anfragen
Beispiel
Beispiel
Welche Professoren lesen die LVA Mäeutik?
Information aus Tabelle: Professoren, Vorlesungen
Welche Professoren lesen die LVA Mäeutik?
Information aus Tabelle: Professoren, Vorlesungen
Professoren (PersNr, Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon)
Professoren (PersNr, Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon)
select Name , Titel
from Professoren , Vorlesungen
where PersNr = gelesenVon and
Titel = ‘ Mäeutik ’;
Sebastian Skritek
Relationale Abfragesprachen
Seite 20
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Sebastian Skritek
Relationale Abfragesprachen
Anfragen über mehrere Relationen
Seite 20
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Anfragen über mehrere Relationen
Struktur einer Abfrage über mehrere Tabellen:
Struktur einer Abfrage über mehrere Tabellen:
select Attribute
from Tabelle1 , Tabelle2 , ... , TabelleN
where Bedingungen ;
select Attribute
from Tabelle1 , Tabelle2 , ... , TabelleN
where Bedingungen ;
Bedingungen: greifen auf die Attribute der verschiedenen Tabellen zu
Sebastian Skritek
Seite 21
Sebastian Skritek
Seite 21
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
Sebastian Skritek
Relationale Abfragesprachen
Seite 21
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Sebastian Skritek
Seite 22
Relationale Abfragesprachen
Anfragen über mehrere Relationen
1
Welche Professoren lesen die LVA Mäeutik?
Information aus Tabelle: Professoren, Vorlesungen
Bilde Kreuzprodukt der from Tabellen
Professoren
Professoren (PersNr, Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon)
select Name , Titel
from Professoren , Vorlesungen
where PersNr = gelesenVon and
Titel = ‘ Mäeutik ’;
Sebastian Skritek
3.2. Anfragen über mehrere Relationen
Anfragen über mehrere Relationen – Abarbeitung
Beispiel
πName,Titel σPersNr=gelesenVon∧Titel=‘Mäeutik’ (Professoren × Vorlesungen)
3. Datenabfragesprache
Name
Rang
VorlNr
Titel
2125
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
×
Seite 22
Vorlesungen
PersNr
Sebastian Skritek
Raum
SWS
PersNr
Seite 23
Relationale Abfragesprachen
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Relationale Abfragesprachen
Anfragen über mehrere Relationen – Abarbeitung
Professoren × Vorlesungen
Raum
SWS
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Anfragen über mehrere Relationen – Abarbeitung
Professoren × Vorlesungen
PersNr
Name
Rang
VorlNr
Titel
VPersNr
PersNr
Name
Rang
VorlNr
Titel
2125
Sokrates
C4
226
5001
Grundzüge
4
2137
2125
Sokrates
C4
226
5001
Grundzüge
4
2137
2125
..
.
Sokrates
..
.
C4
..
.
226
..
.
5041
..
.
Ethik
..
.
4
..
.
2125
..
.
2125
..
.
Sokrates
..
.
C4
..
.
226
..
.
5041
..
.
Ethik
..
.
4
..
.
2125
..
.
2125
..
.
Sokrates
..
.
C4
..
.
226
..
.
5049
..
.
Mäeutik
..
.
2
..
.
2125
..
.
2125
..
.
Sokrates
..
.
C4
..
.
226
..
.
5049
..
.
Mäeutik
..
.
2
..
.
2125
..
.
2137
Kant
C4
7
4630
Die drei Kritiken
4
2137
2137
Kant
C4
7
4630
Die drei Kritiken
4
2137
2
Sebastian Skritek
Seite 24
Relationale Abfragesprachen
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
σPersNr =VPersNr ∧Titel=0 Mäeutik 0 (Professoren × Vorlesungen)
PersNr
Name
Rang
2125
Sokrates
C4
Raum
226
VorlNr
Titel
5049
Mäeutik
SWS
2
σPersNr =VPersNr ∧Titel=0 Mäeutik 0 (Professoren × Vorlesungen)
PersNr
Name
Rang
2125
Sokrates
C4
VPersNr
Raum
226
VorlNr
Titel
5049
Mäeutik
SWS
2
VPersNr
2125
Sebastian Skritek
Seite 24
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Anfragen über mehrere Relationen – Abarbeitung
σPersNr =VPersNr ∧Titel=0 Mäeutik 0 (Professoren × Vorlesungen)
VPersNr
PersNr
Name
Rang
2125
2125
Sokrates
C4
3
Sebastian Skritek
SWS
Überprüfe für jede Zeile die where Bedingungen
Relationale Abfragesprachen
Anfragen über mehrere Relationen – Abarbeitung
Raum
Seite 25
Sebastian Skritek
Raum
226
VorlNr
Titel
5049
Mäeutik
SWS
2
VPersNr
2125
Projiziere auf select Attribute
Seite 25
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
Sebastian Skritek
Relationale Abfragesprachen
Seite 25
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Sebastian Skritek
Relationale Abfragesprachen
Anfragen über mehrere Relationen
Seite 26
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Platzhalter für Tabellen und Umbenennung von Attributen
Beispiel
Beispiel
Selbe Anfrage bei der Vergabe von Platzhaltern für Tabellennamen:
Geben Sie Name und Matrikelnummer der Studierenden und den Titel
der von ihnen gehörten Vorlesungen aus.
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 ;
Studenten (MatrNr, Name , Sem )
hören (MatrNr, VorlNr)
Vorlesungen (VorlNr, Titel , SWS , gelesenVon )
select Studenten . MatrNr , Name , Titel
from Studenten , hören , Vorlesungen
where Studenten . MatrNr = hören . MatrNr and
hören . VorlNr = Vorlesungen . VorlNr ;
Sebastian Skritek
Seite 26
Sebastian Skritek
Seite 27
Relationale Abfragesprachen
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Relationale Abfragesprachen
Platzhalter für Tabellen und Umbenennung von Attributen
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
Relationale Abfragesprachen
Seite 27
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Sebastian Skritek
Seite 28
Relationale Abfragesprachen
Übersetzung in relationale Algebra
3. Datenabfragesprache
3.2. Anfragen über mehrere Relationen
Übersetzung in relationale Algebra
Allgemeine Form einer (ungeschachteteln) SQL-Anfrage:
Allgemeine Form einer (ungeschachteteln) SQL-Anfrage:
select A1 , . . . , An
from R1 , . . . , Rk
where P ;
select A1 , . . . , An
from R1 , . . . , Rk
where P ;
wird zu:
πA1 ,...,An σP (R1 × · · · × Rk )
Sebastian Skritek
Seite 29
Sebastian Skritek
Seite 29
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
Rk
R3
R2
Sebastian Skritek
Relationale Abfragesprachen
3. Datenabfragesprache
Seite 29
3. Datenabfragesprache
3.3. Mengenoperationen
Sebastian Skritek
Relationale Abfragesprachen
Mengenoperationen
Seite 30
3. Datenabfragesprache
3.4. Aggregatfunktionen
Aggregatfunktionen
Anfragen mit typkompatiblen Ausgabeattributen können mittels
Mengenoperationen verknüpft werden.
Aggregatfunktionen sind Operationen, die nicht auf einzelnen Tupeln,
sondern auf einer Menge von Tupeln arbeiten:
Ohne Duplikate: union, intersect, except (minus)
Mit Duplikaten: union all, intersect all, except all
Beispiel
Suchen Sie den Namen aller Assistenten oder Professoren
( select Name from Assistenten )
union
( select Name from Professoren );
Sebastian Skritek
Seite 30
Sebastian Skritek
Seite 31
Relationale Abfragesprachen
3. Datenabfragesprache
3.4. Aggregatfunktionen
Relationale Abfragesprachen
Aggregatfunktionen
3. Datenabfragesprache
3.4. Aggregatfunktionen
Aggregatfunktionen
Aggregatfunktionen sind Operationen, die nicht auf einzelnen Tupeln,
sondern auf einer Menge von Tupeln arbeiten:
Aggregatfunktionen sind Operationen, die nicht auf einzelnen Tupeln,
sondern auf einer Menge von Tupeln arbeiten:
avg(), max(), min(), sum() berechnen den Wert einer Menge
von Tupeln,
avg(), max(), min(), sum() berechnen den Wert einer Menge
von Tupeln,
count() zählt die Anzahl der Tupel in der Menge.
Sebastian Skritek
Relationale Abfragesprachen
Seite 31
3. Datenabfragesprache
3.4. Aggregatfunktionen
Sebastian Skritek
Relationale Abfragesprachen
Aggregatfunktionen
Seite 31
3. Datenabfragesprache
3.4. Aggregatfunktionen
Aggregatfunktionen
Beispiel
Beispiel
Geben Sie die durchschnittliche/maximale/minimale Inskriptionsdauer der
Studenten an; geben Sie an, wieviele Studierende es gibt.
Geben Sie die durchschnittliche/maximale/minimale Inskriptionsdauer der
Studenten an; geben Sie an, wieviele Studierende es gibt.
Studenten
Sebastian Skritek
Seite 32
Sebastian Skritek
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.4. Aggregatfunktionen
Aggregatfunktionen
Beispiel
Beispiel
Geben Sie die Summe der von C4 Professoren gehaltenen Vorlesungsstunden an.
Geben Sie die durchschnittliche/maximale/minimale Inskriptionsdauer der
Studenten an; geben Sie an, wieviele Studierende es gibt.
select avg ( Semester )
from Studenten ;
select max ( Semester )
from Studenten ;
select min ( Semester )
from Studenten ;
select count ( MatrNr )
from Studenten ;
Studenten
MatrNr
Name
Sem
24002
Xenokrates
25403
Jonas
12
26120
Fichte
10
26830
Aristoxenos
8
27550
Schopenhauer
6
28106
Carnap
3
29120
Theophrastos
2
29555
Feuerbach
Vorlesungen
18
VorlNr
2
Sebastian Skritek
Seite 32
Relationale Abfragesprachen
3. Datenabfragesprache
3.4. Aggregatfunktionen
Titel
Professoren
gelesenVon
5001
Grundzüge
4
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
Aggregatfunktionen
SWS
Seite 33
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 ’;
Problem: Wollen nicht die Summe aller Vorlesungsstunden absolut
berechnen, sondern zu jedem Vortragenden die Summe der
von ihm gehaltenen Stunden.
Vorlesungen
VorlNr
Titel
SWS
5001
Grundzüge
4
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
..
.
Sebastian Skritek
Professoren
gelesenVon
PersNr
Name
Rang
2125
Sokrates
C4
2126
Russel
C4
2133
..
.
Popper
..
.
C3
..
.
Seite 33
Sebastian Skritek
Seite 34
Relationale Abfragesprachen
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Relationale Abfragesprachen
Aggregate und Gruppierung
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Aggregate und Gruppierung
Problem: Wollen nicht die Summe aller Vorlesungsstunden absolut
berechnen, sondern zu jedem Vortragenden die Summe der
von ihm gehaltenen Stunden.
Problem: Wollen nicht die Summe aller Vorlesungsstunden absolut
berechnen, sondern zu jedem Vortragenden die Summe der
von ihm gehaltenen Stunden.
Lösung: Bilde zu jedem Vortragenden eine Gruppe (mittels der
group by Klause) und summiere nur innerhalb dieser
Gruppe.
Lösung: Bilde zu jedem Vortragenden eine Gruppe (mittels der
group by Klause) und summiere nur innerhalb dieser
Gruppe.
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.
Sebastian Skritek
Relationale Abfragesprachen
Seite 34
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Sebastian Skritek
Relationale Abfragesprachen
Aggregate und Gruppierung
Seite 34
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Bsp: Aggregate und Gruppierung
Problem: Wollen nicht die Summe aller Vorlesungsstunden absolut
berechnen, sondern zu jedem Vortragenden die Summe der
von ihm gehaltenen Stunden.
Beispiele
Geben Sie zu jedem C4 Professor die Summe der von ihr/ihm gehaltenen
Vorlesungsstunden an.
Lösung: Bilde zu jedem Vortragenden eine Gruppe (mittels der
group by Klause) und summiere nur innerhalb dieser
Gruppe.
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
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.
Sebastian Skritek
Seite 34
Sebastian Skritek
Seite 35
Relationale Abfragesprachen
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Relationale Abfragesprachen
Bsp: Aggregate und Gruppierung
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 ;
Sebastian Skritek
Relationale Abfragesprachen
Seite 35
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Sebastian Skritek
Seite 36
Relationale Abfragesprachen
Bsp: Aggregate und Gruppierung
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Aggregate und Gruppierung – Abarbeitung
Beispiele
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 )
select gelesenVon , Name , sum ( SWS )
from Vorlesungen , Professoren
where gelesenVon = PersNr and Rang = ’ C4 ’
group by gelesenVon , Name
having avg ( SWS ) >= 3;
PersNr
Name
Rang
2125
Sokrates
C4
2125
..
.
Sokrates
..
.
C4
..
.
2125
..
.
Sokrates
..
.
2133
..
.
2137
Professoren × Vorlesungen
Raum
VorlNr
Titel
SWS
gelesenVon
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
where-Bedingung
Sebastian Skritek
Seite 36
Sebastian Skritek
Seite 37
Relationale Abfragesprachen
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Relationale Abfragesprachen
Aggregate und Gruppierung
3. Datenabfragesprache
Aggregate und Gruppierung
Gruppierung
where-Bedingung
PersNr
Name
Rang
2137
Kant
C4
2125
Sokrates
C4
2126
Russel
C4
2125
Sokrates
C4
2125
Sokrates
2126
2126
2137
gelesenVon
PersNr
Name
Rang
4
2137
2125
Sokrates
C4
4
2125
2125
Sokrates
C4
Erkenntnistheorie
3
2126
2125
Sokrates
Mäeutik
2
2125
2126
Russel
4052
Logik
4
2125
2126
232
5052
Wissenschaftstheorie
3
2126
232
5216
Bioethik
2
2126
7
4630
Die drei Kritiken
4
2137
Raum
VorlNr
Titel
7
5001
Grundzüge
226
5041
Ethik
232
5043
226
5049
C4
226
Russel
C4
Russel
C4
Kant
C4
SWS
VorlNr
Titel
SWS
226
5041
Ethik
4
2125
226
5049
Mäeutik
2
2125
C4
226
4052
Logik
4
2125
C4
232
5043
Erkenntnistheorie
3
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
Gruppierung
Seite 38
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Seite 39
3. Datenabfragesprache
Rang
2125
Sokrates
C4
2125
Sokrates
C4
2125
Sokrates
2137
2137
Raum
3.5. Aggregate und Gruppierung
Aggregate und Gruppierung
having-Bedingung
Name
gelesenVon
Sebastian Skritek
Relationale Abfragesprachen
Aggregate und Gruppierung
PersNr
Raum
having-Bedingung
Sebastian Skritek
Relationale Abfragesprachen
3.5. Aggregate und Gruppierung
having-Bedingung
VorlNr
Titel
SWS
gelesenVon
PersNr
Name
Rang
226
5041
Ethik
226
5049
Mäeutik
4
2125
2125
Sokrates
C4
2
2125
2125
Sokrates
C4
C4
226
4052
Logik
4
2125
2125
Sokrates
Kant
C4
7
Kant
C4
7
5001
Grundzüge
4
2137
2137
4630
Die drei Kritiken
4
2137
2137
Raum
VorlNr
Titel
SWS
gelesenVon
226
5041
Ethik
4
2125
226
5049
Mäeutik
2
2125
C4
226
4052
Logik
4
2125
Kant
C4
7
5001
Grundzüge
4
2137
Kant
C4
7
4630
Die drei Kritiken
4
2137
Aggregation (sum) und Projektion
Sebastian Skritek
Seite 40
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
Aggregate und Gruppierung
Achtung: SQL erzeugt pro Gruppe ein Ergebnistupel
Achtung: SQL erzeugt pro Gruppe ein Ergebnistupel
⇒ 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.
⇒ 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.
select gelesenVon , Name, sum ( SWS )
........
group by gelesenVon , Name;
Sebastian Skritek
Relationale Abfragesprachen
Seite 41
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Sebastian Skritek
Relationale Abfragesprachen
Bsp: Aggregate und Gruppierung
Seite 41
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Bsp: Aggregate und Gruppierung
Beispiel
Beispiel
Geben Sie die Namen der Studenten aus, die am längsten studieren.
Geben Sie die Namen der Studenten aus, die am längsten studieren.
Studenten ( MatrNr , Name , Semester )
Studenten ( MatrNr , Name , Semester )
select Name , max ( Semester )
from Studenten ;
select Name , max ( Semester )
from Studenten ;
⇒ ORA-00937: not a single-group group function
Sebastian Skritek
Seite 42
Sebastian Skritek
Seite 42
Relationale Abfragesprachen
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Relationale Abfragesprachen
Bsp: Aggregate und Gruppierung
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Bsp: Aggregate und Gruppierung
Beispiel
Beispiel (2. Versuch)
Geben Sie die Namen der Studenten aus, die am längsten studieren.
Geben Sie die Namen der Studenten aus, die am längsten studieren.
Studenten ( MatrNr , Name , Semester )
Studenten ( MatrNr , Name , Semester )
select Name , max ( Semester )
from Studenten ;
⇒ 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 42
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Sebastian Skritek
Relationale Abfragesprachen
Bsp: Aggregate und Gruppierung
Seite 43
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
Bsp: Aggregate und Gruppierung
Beispiel (2. Versuch)
Beispiel (2. Versuch)
Geben Sie die Namen der Studenten aus, die am längsten studieren.
Geben Sie die Namen der Studenten aus, die am längsten studieren.
Studenten ( MatrNr , Name , Semester )
Studenten ( MatrNr , Name , Semester )
select Name , max ( Semester )
from Studenten
group by Name ;
select Name , max ( Semester )
from Studenten
group by Name ;
⇒ Alle Tupel ?!?
Sebastian Skritek
Seite 43
Sebastian Skritek
Seite 43
Relationale Abfragesprachen
3. Datenabfragesprache
3.5. Aggregate und Gruppierung
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 ;
⇒ Alle Tupel ?!?
Lösung: Geschachtelte Anfrage
Sebastian Skritek
Seite 43
Relationale Abfragesprachen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Sebastian Skritek
Seite 44
Relationale Abfragesprachen
Geschachtelte Anfragen
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)
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)
Ergebnis der Unteranfrage besteht aus einem Tupel mit einem
Attribut (= ein Wert):
Ergebnis der Unteranfrage besteht aus einem Tupel mit einem
Attribut (= ein Wert):
• Unteranfrage anstelle eines skalaren Wertes in select bzw. where
• Unteranfrage anstelle eines skalaren Wertes in select bzw. where
Klausel
Klausel
Ergebnis der Unterabfrage eine Relation
Ergebnis der Unterabfrage eine Relation
• Unteranfrage in from Klausel
• Mengenvergleiche: in, not in, any, all
• Verknüpfung über Quantoren: exists
• Unteranfrage in from Klausel
• Mengenvergleiche: in, not in, any, all
• Verknüpfung über Quantoren: exists
Bei geschachtelten Abfragen ist für die Laufzeit ausschlaggebend, ob es
sich um korrelierte oder unkorrelierte Abfragen handelt
Sebastian Skritek
Seite 44
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 where Klausel
Unteranfrage in der where Klausel
Beispiel
Geben Sie die Namen der Studenten aus, die am längsten studieren.
Studenten ( MatrNr , Name , Semester )
Sebastian Skritek
Relationale Abfragesprachen
Seite 45
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Geschachtelte Anfragen
Seite 45
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen
Unteranfrage in der select Klausel
Unteranfrage in der where Klausel
Beispiel
Geben Sie die Namen der Studenten aus, die am längsten studieren.
Studenten ( MatrNr , Name , Semester )
select Name
from Studenten
where Semester = ( select max ( Semester )
from Studenten );
Sebastian Skritek
Seite 45
Sebastian Skritek
Seite 46
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 select Klausel
Beispiel
Beispiel
Geben Sie zu jedem Vortragenden seine Lehrbelastung aus.
Geben Sie zu jedem Vortragenden seine Lehrbelastung aus.
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
select p . Name , ( select sum ( SWS )
from Vorlesungen
where gelesenVon = p . PersNr )
from Professoren p ;
select p . Name , ( select sum ( SWS )
from Vorlesungen
where gelesenVon = p . PersNr )
from Professoren p ;
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 46
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Geschachtelte Anfragen
Seite 46
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen
Beispiel (Entschachtelung mittels group by)
Beispiel (Entschachtelung mittels group by)
Geben Sie zu jedem Vortragenden seine Lehrbelastung aus.
Geben Sie zu jedem Vortragenden seine Lehrbelastung aus.
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
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 47
Relationale Abfragesprachen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Relationale Abfragesprachen
Geschachtelte Anfragen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen
Unteranfrage in der from Klausel
Unteranfrage in der from Klausel
Beispiel
Gesucht sind jene Studenten, die mehr als 2 Vorlesungen hören.
Studenten ( MatrNr , Name , Semester )
hören ( MatrNr , VorlNr )
Sebastian Skritek
Relationale Abfragesprachen
Seite 48
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Geschachtelte Anfragen
Seite 48
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen
Unteranfrage in der from Klausel
Beispiel
Beispiel (Entschachtelung mittels having)
Gesucht sind jene Studenten, die mehr als 2 Vorlesungen hören.
Gesucht sind jene Studenten, die mehr als 2 Vorlesungen hören.
Studenten ( MatrNr , Name , Semester )
hören ( MatrNr , VorlNr )
Studenten ( MatrNr , Name , Semester )
hören ( MatrNr , VorlNr )
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 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
Seite 48
Sebastian Skritek
Seite 49
Relationale Abfragesprachen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Relationale Abfragesprachen
Geschachtelte Anfragen – Mengenvergleiche
Unteranfrage in der where Klausel
Mengenvergleich mit in/not in
Sebastian Skritek
Seite 50
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen – Mengenvergleiche
Unteranfrage in der where Klausel
Relationale Abfragesprachen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Geschachtelte Anfragen – Mengenvergleiche
Seite 50
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen – Mengenvergleiche
Unteranfrage in der where Klausel
Mengenvergleich mit in/not in
Unteranfrage in der where Klausel
Mengenvergleich mit in/not in
Beispiel
Beispiel
Gesucht sind jene Professoren, die keine Lehrveranstaltungen halten.
Gesucht sind jene Professoren, die keine Lehrveranstaltungen halten.
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
select Name
from Professoren
where PersNr not in ( select gelesenVon
from Vorlesungen );
Sebastian Skritek
Seite 50
Sebastian Skritek
Seite 50
Relationale Abfragesprachen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Relationale Abfragesprachen
Geschachtelte Anfragen – Mengenvergleiche
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen – Mengenvergleiche
Mengenvergleich mit in/not in
Mengenvergleich mit in/not in
Beispiel
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
where ( MatrNr , VorlNr ) in ( select MatrNr , VorlNr
from prüfen
where Note < 5);
Sebastian Skritek
Relationale Abfragesprachen
Seite 51
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Geschachtelte Anfragen – Mengenvergleiche
Seite 51
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen – Mengenvergleiche
Beispiel (Entschachtelung mittels intersect)
Beispiel (Entschachtelung mittels intersect)
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);
Sebastian Skritek
Seite 52
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.)
Mengenvergleich mit any/all (Achtung: kein Allquantor, nur der
Vergleich eines Wertes mit einer Menge von Werten.)
Beispiel
Suchen Sie jene Studenten, die am längsten studieren.
select Name
from Studenten
where Semester >= all ( select Semester
from Studenten );
Sebastian Skritek
Relationale Abfragesprachen
Seite 53
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Geschachtelte Anfragen – Mengenvergleiche
Seite 53
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:
select Name
from Studenten
where Semester = ( select max ( Semester )
from Studenten );
Sebastian Skritek
Seite 53
Sebastian Skritek
Seite 54
Relationale Abfragesprachen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Relationale Abfragesprachen
Geschachtelte Anfragen – Mengenvergleiche
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Geschachtelte Anfragen – Aggregatfunktionen
Geschachtelte Aggregatfunktionen sind nicht erlaubt
⇒ Unteranfrage verwenden
Beispiel
Suchen Sie jene Studenten, die nicht am längsten studieren.
select Name
from Studenten
where Semester < any ( select Semester
from Studenten );
gleichbedeutend mit:
select Name
from Studenten
where Semester < ( select max ( Semester )
from Studenten );
Sebastian Skritek
Relationale Abfragesprachen
Seite 54
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Geschachtelte Anfragen – Aggregatfunktionen
Seite 55
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.
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , 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
Seite 55
Sebastian Skritek
Seite 55
Relationale Abfragesprachen
3. Datenabfragesprache
3.6. Geschachtelte Anfragen
Relationale Abfragesprachen
Geschachtelte Anfragen – Aggregatfunktionen
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 );
Sebastian Skritek
Relationale Abfragesprachen
Seite 56
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Existentiell quantifizierte Anfragen
Seite 56
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Existentiell quantifizierte Anfragen
Verknüpfung mit einer Unteranfrage durch Existenzquantor exists
Verknüpfung mit einer Unteranfrage durch Existenzquantor exists
exists überprüft, ob die Unterabfrage Tupel enthält oder nicht.
exists überprüft, ob die Unterabfrage Tupel enthält oder nicht.
Beispiel
Gesucht sind all jene Studenten, die älter als der jüngste Professor sind.
Sebastian Skritek
Seite 57
Sebastian Skritek
Seite 57
Relationale Abfragesprachen
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Relationale Abfragesprachen
Existentiell quantifizierte Anfragen
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Existentiell quantifizierte Anfragen
Verknüpfung mit einer Unteranfrage durch Existenzquantor exists
Verknüpfung mit einer Unteranfrage durch Existenzquantor exists
exists überprüft, ob die Unterabfrage Tupel enthält oder nicht.
exists überprüft, ob die Unterabfrage Tupel enthält oder nicht.
Beispiel
Beispiel
Gesucht sind all jene Studenten, die älter als der jüngste Professor sind.
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 );
select s .*
from Studenten s
where exists ( select p .*
from Professoren p
where p . GebDatum > s . GebDatum );
Korrelierte Unteranfrage (s.GebDatum und Studenten s)!
Sebastian Skritek
Relationale Abfragesprachen
Seite 57
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Existentiell quantifizierte Anfragen
Seite 57
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Existentiell quantifizierte Anfragen
Eine nicht korrelierte Lösung ist:
Eine nicht korrelierte Lösung ist:
Beispiel (Nicht korreliert)
Beispiel (Nicht korreliert)
Gesucht sind all jene Studenten, die älter als der jüngste Professor sind.
Gesucht sind all jene Studenten, die älter als der jüngste Professor sind.
select s .*
from Studenten s
where s . GebDatum < ( select max ( p . GebDatum )
from Professoren p );
select s .*
from Studenten s
where s . GebDatum < ( select max ( p . GebDatum )
from Professoren p );
oder
select s .* from Studenten s
where s . GebDatum < any ( select p . GebDatum
from Professoren p );
oder . . .
Sebastian Skritek
Seite 58
Sebastian Skritek
Seite 58
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 all jene Professoren, die keine Lehrveranstaltungen halten.
Suchen Sie all jene Professoren, die keine Lehrveranstaltungen halten.
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
Professoren ( PersNr , Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
select Name
from Professoren
where not exists ( select *
from Vorlesungen
where gelesenVon = PersNr );
Sebastian Skritek
Relationale Abfragesprachen
Seite 59
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Existentiell quantifizierte Anfragen
Seite 59
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 );
Korrelierte Unteranfrage (PersNr und Professoren)!
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
Eine nicht korrelierte Lösung ist:
Beispiel
select Name
from Professoren
where PersNr not in ( select gelesenVon
from Vorlesungen );
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 )
oder
( select PersNr
from Professoren )
except
( select gelesenVon
from Vorlesungen );
Sebastian Skritek
Relationale Abfragesprachen
Seite 60
3. Datenabfragesprache
3.7. Existentiell quantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Existentiell quantifizierte Anfragen
Seite 61
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 );
select a .*
from Assistenten a
where exists ( select p .*
from Professoren p
where a . Boss = p . PersNr and
p . GebDatum > a . GebDatum );
Korrelierte Unteranfrage (a.GebDatum und Assistenten a)!
Sebastian Skritek
Seite 61
Sebastian Skritek
Seite 61
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 );
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 ;
Sebastian Skritek
Relationale Abfragesprachen
Seite 62
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen
Seite 62
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen
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 )
Studenten ( MatrNr , Name , Semester )
hören ( MatrNr , VorlNr )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon )
hören ÷ πVorlNr σSWS=4 (Vorlesungen)
Sebastian Skritek
Seite 63
Sebastian Skritek
Seite 63
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen
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
Relationale Abfragesprachen
Seite 63
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – Nicht direkt in SQL
Seite 64
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?
SQL stellt keinen Allquantor zur Verfügung. Realisierung durch
SQL stellt keinen Allquantor zur Verfügung. Realisierung durch
1
Logische Äquivalenz (mittels 2 × not exists)
Sebastian Skritek
Seite 64
1
Logische Äquivalenz (mittels 2 × not exists)
2
Teilmengen (mittels exists und except)
Sebastian Skritek
Seite 64
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – Nicht direkt in SQL
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?
SQL stellt keinen Allquantor zur Verfügung. Realisierung durch
SQL stellt keinen Allquantor zur Verfügung. Realisierung durch
1
Logische Äquivalenz (mittels 2 × not exists)
1
Logische Äquivalenz (mittels 2 × not exists)
2
Teilmengen (mittels exists und except)
2
Teilmengen (mittels exists und except)
3
Abzählen (mittels count)
3
Abzählen (mittels count)
4
Division (mittels except)
Sebastian Skritek
Relationale Abfragesprachen
Seite 64
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Seite 64
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Umformulierung in äqivalente Anfrage mit Negation und Existenzquantor
(Prädikatenlogik)
Umformulierung in äqivalente Anfrage mit Negation und Existenzquantor
(Prädikatenlogik)
∀xF (x) ⇔ ¬∃x(¬F (x))
Sebastian Skritek
Seite 65
Sebastian Skritek
Seite 65
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Umformulierung in äqivalente Anfrage mit Negation und Existenzquantor
(Prädikatenlogik)
Umformulierung in äqivalente Anfrage mit Negation und Existenzquantor
(Prädikatenlogik)
∀xF (x) ⇔ ¬∃x(¬F (x))
∀xF (x) ⇔ ¬∃x(¬F (x))
Beispiel
Beispiel
Welche Studenten haben alle 4 stündigen Lehrveranstaltungen gehört?
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
Sebastian Skritek
Relationale Abfragesprachen
⇔ 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
Seite 65
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Seite 65
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 1. “Logische Umformung”
SQL Umsetzung folgt nun direkt aus:
Umformulierung in äqivalente Anfrage mit Negation und Existenzquantor
(Prädikatenlogik)
Suchen Sie jene Studenten für die nicht gilt:
∀xF (x) ⇔ ¬∃x(¬F (x))
Beispiel
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
Sebastian Skritek
Seite 66
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 1. “Logische Umformung”
SQL Umsetzung folgt nun direkt aus:
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:
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.
Sebastian Skritek
Relationale Abfragesprachen
Seite 66
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:
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.
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.
Beispiel
Beispiel
select s.*
from Studenten s
where not exists
Sebastian Skritek
select s.*
from Studenten s
where not exists
(select *
from Vorlesungen v
where v.SWS = 4 and
not exists
Seite 66
Sebastian Skritek
Seite 66
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 1. “Logische Umformung”
SQL Umsetzung folgt nun direkt aus:
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.
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.
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 ));
Sebastian Skritek
Relationale Abfragesprachen
Seite 66
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Seite 67
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 )
Sebastian Skritek
Seite 68
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Beispiel
{s
Umformung im relationalen Tupelkalkül:
| s ∈ Studenten ∧ ∀v ∈ Vorlesungen(v .SWS = 4 →
∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = t.MatrNr ))}
Beispiel
Studenten (MatrNr, Name , Sem )
hören (MatrNr, VorlNr)
Vorlesungen (VorlNr, Titel , SWS , gelesenVon )
{s
| s ∈ Studenten ∧ ∀v ∈ Vorlesungen(v .SWS = 4 →
∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = s.MatrNr ))}
Sebastian Skritek
Relationale Abfragesprachen
Seite 68
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
{s
3.8. Allquantifizierte Anfragen
Beispiel
| s ∈ Studenten ∧ ∀v ∈ Vorlesungen(v .SWS = 4 →
{s
∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = t.MatrNr ))}
| s ∈ Studenten ∧ ∀v ∈ Vorlesungen(v .SWS = 4 →
∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = t.MatrNr ))}
⇔
| s ∈ Studenten ∧ ¬∃v ∈ Vorlesungen¬(v .SWS = 4 →
{s
∃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
Sebastian Skritek
3. Datenabfragesprache
Allquantifizierte Anfragen – 1. “Logische Umformung”
Beispiel
⇔
Seite 69
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
{s
Sebastian Skritek
Seite 69
| s ∈ Studenten ∧ ¬∃v ∈ Vorlesungen¬(¬(v .SWS = 4) ∨
Sebastian Skritek
(∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = s.MatrNr )))}
Seite 69
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 )))}
select s.*
from Studenten s
where not exists
(select *
from Vorlesungen v
where v.SWS = 4 and
not exists
| 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
| s ∈ Studenten ∧ ¬∃v ∈ Vorlesungen((v .SWS = 4) ∧
(¬∃h ∈ hören(h.VorlNr = v .VorlNr ∧ h.MatrNr = s.MatrNr )))}
Sebastian Skritek
Seite 69
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 1. “Logische Umformung”
Seite 70
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 2. “Teilmengen”
Beispiel
{s
Suchen Sie alle Studierenden M für die gilt:
Menge aller 4 stündigen LVAs ⊆ Menge der von M gehörten LVAs
| s ∈ Studenten ∧ ¬∃v ∈ Vorlesungen((v .SWS = 4) ∧
(¬∃h ∈ hören(v .VorlNr = h.VorlNr ∧ s.MatrNr = h.MatrNr )))}
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 ));
Sebastian Skritek
Seite 70
Sebastian Skritek
Seite 71
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 2. “Teilmengen”
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 2. “Teilmengen”
Suchen Sie alle Studierenden M für die gilt:
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
“⊆” kein Operator in SQL, aber
“⊆” kein Operator in SQL, aber
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 = ∅
Sebastian Skritek
Relationale Abfragesprachen
Seite 71
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 2. “Teilmengen”
Seite 71
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
Sebastian Skritek
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 – 3. “Abzählen”
Allquantifizierung kann immer auch durch eine count-Aggregation
ausgedrückt werden
Allquantifizierung kann immer auch durch eine count-Aggregation
ausgedrückt werden
Beispiel
Beispiel
Welche Studenten haben alle 4 stündigen Lehrveranstaltungen gehört?
Welche Studenten haben alle 4 stündigen Lehrveranstaltungen gehört?
1
Sebastian Skritek
Relationale Abfragesprachen
Seite 73
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Zähle zu jedem Studenten, wieviele 4 stündigen LVAs er besucht hat
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 3. “Abzählen”
Seite 73
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 3. “Abzählen”
Allquantifizierung kann immer auch durch eine count-Aggregation
ausgedrückt werden
Allquantifizierung kann immer auch durch eine count-Aggregation
ausgedrückt werden
Beispiel
Beispiel
Welche Studenten haben alle 4 stündigen Lehrveranstaltungen gehört?
Welche Studenten haben alle 4 stündigen Lehrveranstaltungen gehört?
1
Zähle zu jedem Studenten, wieviele 4 stündigen LVAs er besucht hat
1
Zähle zu jedem Studenten, wieviele 4 stündigen LVAs er besucht hat
2
zähle wieviele 4 stündige LVAs es gibt.
2
zähle wieviele 4 stündige LVAs es gibt.
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 (*) =
Sebastian Skritek
Seite 73
Sebastian Skritek
Seite 73
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 Studenten haben alle 4 stündigen Lehrveranstaltungen gehört?
1
Zähle zu jedem Studenten, wieviele 4 stündigen LVAs er besucht hat
2
zähle wieviele 4 stündige LVAs es gibt.
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
Seite 73
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Seite 74
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Beispiel
Die Division kann durch die primitiven Operatoren ausgedrückt werden
Welche Studierenden haben alle 4-stündigen Vorlesungen gehört?
hören
hören ÷ πVorlNr σSWS=4 (Vorlesungen)
MatrNr
VorlNr
26120
5001
27550
5001
πVorlNr σSWS=4 (Vorlesungen)
27550
4052
VorlNr
28106
5041
5001
28106
5001
28106
4052
28106
4630
29120
5001
29120
5041
29120
5049
Sebastian Skritek
÷
5041
4052
R ÷S
=
MatrNr
28106
4630
Seite 74
Sebastian Skritek
Seite 75
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Die Division kann durch die primitiven Operatoren ausgedrückt werden
Bilde alle Paare von Studenten und 4 stündigen LVAs
Sebastian Skritek
Relationale Abfragesprachen
Seite 75
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Die Division kann durch die primitiven Operatoren ausgedrückt werden
1
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)
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Seite 75
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Die Division kann durch die primitiven Operatoren ausgedrückt werden
Die Division kann durch die primitiven Operatoren ausgedrückt werden
1
Bilde alle Paare von Studenten und 4 stündigen LVAs
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)
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
3
Aus den übrig gebliebenen Paaren, sammle die Studenten
4
Ziehe diese Studenten von der Menge aller Studenten ab
Sebastian Skritek
Seite 75
Sebastian Skritek
Seite 75
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 4. Relationale Algebra?
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Die Division kann durch die primitiven Operatoren ausgedrückt werden
1
3. Datenabfragesprache
Die Division kann durch die primitiven Operatoren ausgedrückt werden
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
1
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)
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
3
Aus den übrig gebliebenen Paaren, sammle die Studenten
4
Ziehe diese Studenten von der Menge aller Studenten ab
4
Ziehe diese Studenten von der Menge aller Studenten ab
Sebastian Skritek
Relationale Abfragesprachen
Seite 75
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Seite 75
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Die Division kann durch die primitiven Operatoren ausgedrückt werden
1
Sebastian Skritek
Die Division kann durch die primitiven Operatoren ausgedrückt werden
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
1
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
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
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
4
Ziehe diese Studenten von der Menge aller Studenten ab
Studenten die alle 4 stündigen LVAs besucht haben
Sebastian Skritek
Seite 75
Sebastian Skritek
Seite 75
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 4. Relationale Algebra?
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
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
R ÷ S = πR−S (R) − πR−S ((πR−S (R) × S) − R)
Sebastian Skritek
Relationale Abfragesprachen
;
Seite 75
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Die Division kann durch die primitiven Operatoren ausgedrückt werden
1
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Sebastian Skritek
Relationale Abfragesprachen
Allquantifizierte Anfragen – 4. Relationale Algebra?
Seite 76
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 4. Relationale Algebra?
1
Bilde alle Paare von Studenten und 4 stündigen LVAs
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)
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
3
Aus den übrig gebliebenen Paaren, sammle die Studenten
4
Ziehe diese Studenten von der Menge aller Studenten ab
4
Ziehe diese Studenten von der Menge aller Studenten ab
Beispiel
Beispiel
(select s.MatrNr, v.VorlNr
from Studenten s, Vorlesungen v
where v.SWS = 4)
(select s.MatrNr, v.VorlNr
from Studenten s, Vorlesungen v
where v.SWS = 4)
except
(select * from hören)
;
;
Sebastian Skritek
Seite 76
Sebastian Skritek
Seite 76
Relationale Abfragesprachen
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Relationale Abfragesprachen
Allquantifizierte Anfragen – 4. Relationale Algebra?
3. Datenabfragesprache
3.8. Allquantifizierte Anfragen
Allquantifizierte Anfragen – 4. Relationale Algebra?
1
Bilde alle Paare von Studenten und 4 stündigen LVAs
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)
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
3
Aus den übrig gebliebenen Paaren, sammle die Studenten
4
Ziehe diese Studenten von der Menge aller Studenten ab
4
Ziehe diese Studenten von der Menge aller Studenten ab
Beispiel
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);
(select snein.MatrNr
from ((select s.MatrNr, v.VorlNr
from Studenten s, Vorlesungen v
where v.SWS = 4)
except
(select * from hören)) snein);
Sebastian Skritek
Relationale Abfragesprachen
Seite 76
3. Datenabfragesprache
3.9. Nullwerte
Sebastian Skritek
Relationale Abfragesprachen
Nullwerte
Seite 76
3. Datenabfragesprache
3.9. Nullwerte
Nullwerte
Beispiel
Studenten
MatrNr
Name
Semester
24002
Xenokrates
18
25403
Jonas
12
26120
Fichte
10
26830
Aristoxenos
8
27550
Schopenhauer
6
28106
Carnap
3
Finde die Anzahl der Studenten.
Sebastian Skritek
Seite 77
Sebastian Skritek
Seite 77
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Relationale Abfragesprachen
3. Datenabfragesprache
Nullwerte
Nullwerte
Beispiel
Beispiel
Finde die Anzahl der Studenten:
Finde die Anzahl der Studenten:
3.9. Nullwerte
select count (*) from Studenten ;
Sebastian Skritek
Relationale Abfragesprachen
Seite 78
3. Datenabfragesprache
3.9. Nullwerte
Sebastian Skritek
Relationale Abfragesprachen
Seite 78
3. Datenabfragesprache
3.9. Nullwerte
Nullwerte
Nullwerte
Beispiel
Beispiel
Finde die Anzahl der Studenten:
Finde die Anzahl der Studenten:
select count (*) from Studenten ;
select count (*) from Studenten ;
select count ( MatrNr ) from Studenten ;
select count ( MatrNr ) from Studenten ;
select count ( Semester ) from Studenten ;
Sebastian Skritek
Seite 78
Sebastian Skritek
Seite 78
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Nullwerte
Nullwerte
Beispiel
Beispiel
Finde die Anzahl der Studenten:
Finde die Anzahl der Studenten:
select count (*) from Studenten ;
select count (*) from Studenten ;
(6)
select count ( MatrNr ) from Studenten ;
select count ( MatrNr ) from Studenten ;
(6)
select count ( Semester ) from Studenten ;
select count ( Semester ) from Studenten ;
(6)
select count (*) from Studenten
where Semester < 13 or Semester >= 13;
select count (*) from Studenten
where Semester < 13 or Semester >= 13;
(6)
Sebastian Skritek
Relationale Abfragesprachen
Seite 78
3. Datenabfragesprache
3.9. Nullwerte
Sebastian Skritek
Relationale Abfragesprachen
Nullwerte
Seite 78
3. Datenabfragesprache
3.9. Nullwerte
Nullwerte
Nullwerte entstehen
Nullwerte entstehen
wenn kein Wert in der Datenbank vorhanden ist,
wenn kein Wert in der Datenbank vorhanden ist,
im Zuge der Anfrageauswertung (Bsp. äußere Joins)
im Zuge der Anfrageauswertung (Bsp. äußere Joins)
Beispiel
Studenten
MatrNr
Name
Semester
24002
Xenokrates
25403
Jonas
12
26120
Fichte
NULL
26830
Aristoxenos
8
27550
Schopenhauer
6
28106
Carnap
3
18
Finde die Anzahl der Studenten.
Sebastian Skritek
Seite 79
Sebastian Skritek
Seite 79
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Nullwerte
Nullwerte
Beispiel
Beispiel
Finde die Anzahl der Studenten:
Finde die Anzahl der Studenten:
select count (*) from Studenten ;
select count (*) from Studenten ;
select count ( MatrNr ) from Studenten ;
select count ( MatrNr ) from Studenten ;
select count ( Semester ) from Studenten ;
select count ( Semester ) from Studenten ;
select count (*) from Studenten
where Semester < 13 or Semester >= 13;
select count (*) from Studenten
where Semester < 13 or Semester >= 13;
Sebastian Skritek
Relationale Abfragesprachen
Seite 80
3. Datenabfragesprache
3.9. Nullwerte
(6)
Sebastian Skritek
Relationale Abfragesprachen
Seite 80
3. Datenabfragesprache
Nullwerte
Nullwerte
Beispiel
Beispiel
Finde die Anzahl der Studenten:
Finde die Anzahl der Studenten:
3.9. Nullwerte
select count (*) from Studenten ;
(6)
select count (*) from Studenten ;
(6)
select count ( MatrNr ) from Studenten ;
(6)
select count ( MatrNr ) from Studenten ;
(6)
select count ( Semester ) from Studenten ;
select count ( Semester ) from Studenten ;
(5)
select count (*) from Studenten
where Semester < 13 or Semester >= 13;
select count (*) from Studenten
where Semester < 13 or Semester >= 13;
Sebastian Skritek
Seite 80
Sebastian Skritek
Seite 80
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Relationale Abfragesprachen
Nullwerte
3.9. Nullwerte
Einschub: count revisited
Verhalten von count():
Beispiel
Finde die Anzahl der Studenten:
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)
Sebastian Skritek
Relationale Abfragesprachen
3. Datenabfragesprache
count(*):
Zählt alle Tupel (inkl. Duplikate)
Seite 80
3. Datenabfragesprache
3.9. Nullwerte
Sebastian Skritek
Relationale Abfragesprachen
Einschub: count revisited
Seite 81
3. Datenabfragesprache
3.9. Nullwerte
Einschub: count revisited
Verhalten von count():
Verhalten von count():
count(*):
Zählt alle Tupel (inkl. Duplikate)
count(*):
Zählt alle Tupel (inkl. Duplikate)
count(attribute):
Zählt die Anzahl der von NULL verschiedenen Werte in der
angegebenen Spalte (inkl. Duplikate)
count(attribute):
Zählt die Anzahl der von NULL verschiedenen Werte in der
angegebenen Spalte (inkl. Duplikate)
count(distinct attribute):
Zählt die Anzahl der verschiedenen von NULL verschiedenen Werte
in der angegebenen Spalte (d.h: ohne Duplikate)
Sebastian Skritek
Seite 81
Sebastian Skritek
Seite 81
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Relationale Abfragesprachen
Nullwerte
3. Datenabfragesprache
3.9. Nullwerte
Nullwerte: Behandlung in Ausdrücken
Arithmetische Ausdrücke: Nullwerten werden propagiert: ist ein Operand
null so ist das Ergebnis null.
Beispiel
Beispiel
Gibt es Tupel, bei denen der Wert für Semester unbekannt ist, so gilt:
null + 1 = null; null * 0 = null; . . .
select count (*)
from Student
where Semester < 13 or Semester >= 13;
6=
select count (*) from Studenten ;
Sebastian Skritek
Relationale Abfragesprachen
Seite 82
3. Datenabfragesprache
3.9. Nullwerte
Sebastian Skritek
Relationale Abfragesprachen
Nullwerte: Behandlung in Ausdrücken
Seite 83
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; . . .
true
false
unknown
unknown
false
true
Vergleichsoperatoren: SQL hat dreiwertige Logik: true, false, unknown.
Das Resultat ist unknown, wenn mindestens eines der
Argumente null ist.
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
Seite 84
Relationale Abfragesprachen
3. Datenabfragesprache
3.9. Nullwerte
Relationale Abfragesprachen
Nullwerte – Auswertung logischer Ausdrücke
3. Datenabfragesprache
Nullwerte – Auswertung logischer Ausdrücke
Logische Ausdrücke: werden nach den folgenden Tabellen berechnet:
Logische Ausdrücke: werden nach den folgenden Tabellen berechnet:
not
not
true
false
true
false
unknown
unknown
unknown
unknown
false
true
false
true
and
true
unknown
false
and
true
unknown
false
true
true
unknown
false
true
true
unknown
false
unknown
unknown
unknown
false
unknown
unknown
unknown
false
false
false
false
false
false
false
false
false
Sebastian Skritek
Relationale Abfragesprachen
3.9. Nullwerte
Seite 84
3. Datenabfragesprache
3.9. Nullwerte
true
unknown
false
true
true
true
true
unknown
true
unknown
unknown
false
true
unknown
false
Sebastian Skritek
Relationale Abfragesprachen
Nullwerte – Auswertung
or
Seite 84
3. Datenabfragesprache
3.9. Nullwerte
Nullwerte – Auswertung
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.
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.
Gruppierung: wird null als ein eigenständiger Wert aufgefasst und in eine
eigene Gruppe eingeordnet.
Sebastian Skritek
Seite 85
Sebastian Skritek
Seite 85
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
Sebastian Skritek
Relationale Abfragesprachen
between
cross join
natural join
join
left, right, full outer join
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:
select * from Studenten
where Semester > = 1 and Semester < = 4;
select * from Studenten
where Semester between 1 and 4;
select * from Studenten
where Semester in (1 ,2 ,3 ,4);
Sebastian Skritek
Seite 87
Sebastian Skritek
Seite 88
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
like
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
like
Vergleich von Zeichenketten: Platzhalter ’%’ und ’ ’
Vergleich von Zeichenketten: Platzhalter ’%’ und ’ ’
%: beliebig viele (auch gar kein) Zeichen
: genau ein Zeichen
%: beliebig viele (auch gar kein) Zeichen
: genau ein Zeichen
Beispiele
Beispiele
Suchen Sie die Matrikelnummer von Theophrastos, wobei Sie nicht
wissen, ob er sich mit ’h’ schreibt:
Suchen Sie die Matrikelnummer von Theophrastos, wobei Sie nicht
wissen, ob er sich mit ’h’ schreibt:
select * from Studenten
where Name like ’T % eophrastos ’;
select * from Studenten
where Name like ’T % eophrastos ’;
Suchen Sie die Matrikelnummern jener Studenten, die mindestens eine
LVA über Ethik gehört haben:
select distinct MatrNr
from Vorlesungen v , hören h
where h . VorlNr = v . VorlNr and
v . Titel like ’% thik % ’;
Sebastian Skritek
Relationale Abfragesprachen
Seite 88
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Sebastian Skritek
Relationale Abfragesprachen
case
Seite 88
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
case
Beispiel
Beispiel
Wandeln Sie die Prüfungsnoten in Werte der Studienabteilung um:
Wandeln Sie die Prüfungsnoten in Werte der Studienabteilung um:
select MatrNr , ( case when
when
when
when
else
from prüfen ;
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 ’
Note
Note
Note
Note
’ N5 ’
< 1.5
< 2.5
< 3.5
< 4.0
end )
then
then
then
then
’ S1 ’
’ U2 ’
’ B3 ’
’ G4 ’
Die erste qualifizierende when-Klausel wird ausgeführt
Sebastian Skritek
Seite 89
Sebastian Skritek
Seite 89
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
Joins
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Joins
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
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
cross join:
R1 × R2
select * from Professoren , Vorlesungen ;
select * from Professoren cross join Vorlesungen ;
Sebastian Skritek
Relationale Abfragesprachen
Seite 90
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Sebastian Skritek
Relationale Abfragesprachen
natural join:
Seite 90
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
natural join:
R1
R2
R1
Die Werte jener Spalten, deren Attributnamen dieselben sind, werden
gleichgesetzt.
R2
Die Werte jener Spalten, deren Attributnamen dieselben sind, werden
gleichgesetzt.
Beispiel
Geben Sie Name und Matrikelnummer der Studierenden und den Titel
der von ihnen gehörten Vorlesungen aus.
Studenten (MatrNr, Name , Sem )
hören (MatrNr, VorlNr)
Vorlesungen (VorlNr, Titel , SWS , gelesenVon )
Sebastian Skritek
Seite 91
Sebastian Skritek
Seite 91
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
natural join:
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
natural join:
Beispiel
Beispiel
Geben Sie Name und Matrikelnummer der Studierenden und den Titel
der von ihnen gehörten Vorlesungen aus.
Geben Sie Name und Matrikelnummer der Studierenden und den Titel
der von ihnen gehörten Vorlesungen aus.
Studenten (MatrNr, Name , Sem )
hören (MatrNr, VorlNr)
Vorlesungen (VorlNr, Titel , SWS , gelesenVon )
Studenten (MatrNr, Name , Sem )
hören (MatrNr, VorlNr)
Vorlesungen (VorlNr, Titel , SWS , gelesenVon )
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 92
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Sebastian Skritek
Relationale Abfragesprachen
natural join:
Seite 92
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
[inner] join:
R1
Beispiel
θ
R2
Geben Sie Name und Matrikelnummer der Studierenden und den Titel
der von ihnen gehörten Vorlesungen aus.
Studenten (MatrNr, Name , Sem )
hören (MatrNr, VorlNr)
Vorlesungen (VorlNr, Titel , SWS , gelesenVon )
select Studenten . MatrNr , Name , Titel
from Studenten , hören , Vorlesungen
where Studenten . MatrNr = hören . MatrNr and
hören . VorlNr = Vorlesungen . VorlNr ;
select MatrNr , Name , Titel
from Studenten natural join hören
natural join Vorlesungen ;
Sebastian Skritek
Seite 92
Sebastian Skritek
Seite 93
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
[inner] join:
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
[inner] join:
R1
θ
R2
R1
θ
R2
Beispiel
Beispiel
Welche Professoren lesen die LVA Mäeutik?
Welche Professoren lesen die LVA Mäeutik?
Professoren (PersNr, Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon)
Professoren (PersNr, Name , Rang , Raum )
Vorlesungen ( VorlNr , Titel , SWS , gelesenVon)
select Name , Titel
from Professoren , Vorlesungen
where PersNr = gelesenVon and
Titel = ’ Mäeutik ’;
select Name , Titel
from Professoren , Vorlesungen
where PersNr = gelesenVon and
Titel = ’ Mäeutik ’;
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
Sebastian Skritek
Relationale Abfragesprachen
(left|right|full) outer join:
Seite 93
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
(left|right|full) outer join:
R1 (| |)R2
R1 (| |)R2
Beispiel
Geben Sie eine Liste aller Studierenden aus und ihre Noten zu den
abgeprüften Vorlesungen.
Sebastian Skritek
Seite 94
Sebastian Skritek
Seite 94
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
(left|right|full) outer join:
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
(left|right|full) outer join:
R1 (| |)R2
R1 (| |)R2
Beispiel
Beispiel
Geben Sie eine Liste aller Studierenden aus und ihre Noten zu den
abgeprüften Vorlesungen.
Geben Sie eine Liste aller Studierenden aus und ihre Noten zu den
abgeprüften Vorlesungen.
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 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 ;
Sebastian Skritek
Relationale Abfragesprachen
Seite 94
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Sebastian Skritek
Relationale Abfragesprachen
Seite 94
3. Datenabfragesprache
Studenten pr üfen
VorlNr
Studenten pr üfen
MatrNr
Name
MatrNr
Name
24002
Xenokrates
25403
Jonas
24002
Xenokrates
25403
26120
Fichte
Jonas
26120
Fichte
26830
Aristoxenos
27550
Schopenhauer
4630
2
26830
Aristoxenos
27550
28106
Carnap
5001
1
28106
29120
29555
Theophrastos
29120
Theophrastos
Feuerbach
29555
Feuerbach
5041
3.10. Spezielle Sprachkonstrukte
Note
2
VorlNr
Note
5041
2
Schopenhauer
4630
2
Carnap
5001
1
Ergebnis ohne Verwendung des outer Joins:
Studenten
Sebastian Skritek
Seite 95
Sebastian Skritek
pr üfen
MatrNr
Name
VorlNr
Note
25403
Jonas
5041
2
27550
Schopenhauer
4630
2
28106
Carnap
5001
1
Seite 95
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
coalesce()
coalesce()
Beispiel
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.
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 ;
Achtung: Typen müssen kompatibel sein
Sebastian Skritek
Relationale Abfragesprachen
Seite 96
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Sebastian Skritek
Relationale Abfragesprachen
Seite 96
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
concat()
concat()
Beispiel
Beispiel
Studenten ( MatrNr , Vorname , Nachname , Semester );
Studenten ( MatrNr , Vorname , Nachname , Semester );
Geben Sie den Namen aller Studierenden aus
Sebastian Skritek
Seite 97
Sebastian Skritek
Seite 97
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
Relationale Abfragesprachen
3. Datenabfragesprache
3.10. Spezielle Sprachkonstrukte
concat()
concat()
Beispiel
Beispiel
Studenten ( MatrNr , Vorname , Nachname , Semester );
Studenten ( MatrNr , Vorname , Nachname , Semester );
Geben Sie den Namen aller Studierenden aus
Geben Sie den Namen aller Studierenden aus
select Vorname || Nachname from Studenten ;
select Vorname || Nachname from Studenten ;
oder
oder
select concat ( Vorname , Nachname ) from Studenten ;
(oft auch “+” anstatt “||”)
Sebastian Skritek
Seite 97
Relationale Abfragesprachen
3. Datenabfragesprache
3.11. Sichten (Views)
Relationale Abfragesprachen
Sichten (Views)
Seite 97
3. Datenabfragesprache
3.11. Sichten (Views)
Sichten (Views)
. . . 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
Sebastian Skritek
Sebastian Skritek
. . . 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
Seite 98
Sebastian Skritek
Seite 98
Relationale Abfragesprachen
3. Datenabfragesprache
3.11. Sichten (Views)
Relationale Abfragesprachen
Sichten (Views)
3.11. Sichten (Views)
Anpassung an unterschiedliche Benutzergruppen
. . . 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.
Sebastian Skritek
Relationale Abfragesprachen
3. Datenabfragesprache
Beispiel
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 ;
Seite 98
3. Datenabfragesprache
3.11. Sichten (Views)
Sebastian Skritek
Relationale Abfragesprachen
Vereinfachung von Anfragen
Seite 99
3. Datenabfragesprache
3.11. Sichten (Views)
Vereinfachung von Anfragen
Beispiel
Beispiel
Vermeidung der ständigen Verwendung des Joins
Studenten hören Vorlesungen Professoren
Vermeidung der ständigen Verwendung des Joins
Studenten hören Vorlesungen Professoren
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 ;
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 ;
Suchen Sie Name, Semester aller Studenten von Sokrates
select distinct Name , Semester from StudProf
where Pname = ’ Sokrates ’;
Sebastian Skritek
Seite 100
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
Sichten realisieren Inklusion und Vererbung:
Sichten realisieren Inklusion und Vererbung:
Tupel des Untertyps sollen auch automatisch zum Obertyp gehören
Tupel des Untertyps sollen auch automatisch zum Obertyp gehören
Attribute des Obertyps: sollen automatisch vererbt werden.
Attribute des Obertyps: sollen automatisch vererbt werden.
⇒
entweder Untertypen oder Obertypen als Sicht definieren
Entscheidung aufgrund der Häufigkeit der Zugriffe
Sebastian Skritek
Relationale Abfragesprachen
Seite 101
3. Datenabfragesprache
3.11. Sichten (Views)
Sebastian Skritek
Relationale Abfragesprachen
Seite 101
3. Datenabfragesprache
3.11. Sichten (Views)
Realisierung der Generalisierung
Realisierung der Generalisierung
Beispiel (Untertypen als Sicht)
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 );
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 );
create view Professoren as select *
from Angestellte natural join ProfDaten ;
create view Assistenten as select *
from Angestellte natural join AssiDaten ;
Sebastian Skritek
Seite 102
Sebastian Skritek
Seite 102
Relationale Abfragesprachen
3. Datenabfragesprache
3.11. Sichten (Views)
Relationale Abfragesprachen
3. Datenabfragesprache
3.11. Sichten (Views)
Realisierung der Generalisierung
Realisierung der Generalisierung
Beispiel (Obertypen als Sicht)
Beispiel (Obertypen als 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 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
Relationale Abfragesprachen
Seite 103
3. Datenabfragesprache
3.11. Sichten (Views)
Sebastian Skritek
Relationale Abfragesprachen
Updates auf Sichten
Seite 103
3. Datenabfragesprache
3.11. Sichten (Views)
Updates auf Sichten
Problematisch: insert, update, delete auf eine Sicht
Problematisch: insert, update, delete auf eine Sicht
Beispiele (nicht updatefähige Sichten)
Beispiele (nicht updatefähige Sichten)
create view
WieHartAlsPrüfer ( PersNr , Durchschnitt ) as
select PersNr , avg ( Note )
from prüfen
group by PersNr ;
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
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
alle Sichten
theoretisch änderbare Sichten
in SQL änderbare Sichten
Sebastian Skritek
Seite 105
Relationale Abfragesprachen
4. Datenmanipulationssprache
Sebastian Skritek
Seite 106
Relationale Abfragesprachen
Datenmanipulationssprache – Tupel Einfügen
4. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Einfügen
Einfügen von Tupeln in eine angelegte Tabelle
Einfügen von Tupeln in eine angelegte Tabelle
Professoren ( PersNr : integer , Name : varchar (30) ,
Rang : character (2) , Raum : integer )
Professoren ( PersNr : integer , Name : varchar (30) ,
Rang : character (2) , Raum : integer )
Beispiel
Beispiel
Einfügen der Professorin Curie:
Einfügen der Professorin Curie:
insert into Professoren
values (2136 , ’ Curie ’ , ’ C4 ’ , 36);
insert into Professoren
values (2136 , ’ Curie ’ , ’ C4 ’ , 36);
Einfügen mehrerer Professoren:
insert into Professoren
values (2125 , ’ Sokrates ’ , ’ C4 ’ , 226) ,
(2126 , ’ Russel ’ , ’ C4 ’ , 232);
Sebastian Skritek
Seite 106
Sebastian Skritek
Seite 106
Relationale Abfragesprachen
4. Datenmanipulationssprache
Relationale Abfragesprachen
4. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Einfügen
Datenmanipulationssprache – Tupel Einfügen
Das Ergebnis einer Anfrage in eine Tabelle eintragen:
Das Ergebnis einer Anfrage in eine Tabelle eintragen:
Beispiel
Beispiel
Eintragen aller Studenten zur Vorlesung ‘Logik’:
Eintragen aller Studenten zur Vorlesung ‘Logik’:
hören ( MatrNr , VorlNr )
Studenten ( MatrNr , Name , Sem )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
hören ( MatrNr , VorlNr )
Studenten ( MatrNr , Name , Sem )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
insert into hören
Sebastian Skritek
Relationale Abfragesprachen
insert into hören
select MatrNr , VorlNr
from Studenten , Vorlesungen
where Titel = ’ Logik ’;
Seite 107
4. Datenmanipulationssprache
Sebastian Skritek
Relationale Abfragesprachen
Datenmanipulationssprache – Tupel Löschen
Seite 107
4. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Löschen
Auflisten der zu löschenden Tupeln:
Auflisten der zu löschenden Tupeln:
Beispiel
Beispiel
Löschen des Herrn Kant aus der Professorentabelle:
Löschen des Herrn Kant aus der Professorentabelle:
delete from Professoren
values (2137 , ’ Kant ’ , ’ C4 ’ , 7);
delete from Professoren
values (2137 , ’ Kant ’ , ’ C4 ’ , 7);
Lösche alle Tupel die eine Bedingung erfüllen:
Lösche alle Tupel die eine Bedingung erfüllen:
Beispiel
Beispiel
Löschen des Herrn Kant aus der Professorentabelle:
Löschen des Herrn Kant aus der Professorentabelle:
delete from Professoren where PersNr =2137;
delete from Professoren where PersNr =2137;
delete from Professoren where PersNr < 2137;
Sebastian Skritek
Seite 108
Sebastian Skritek
Seite 108
Relationale Abfragesprachen
4. Datenmanipulationssprache
Relationale Abfragesprachen
Datenmanipulationssprache – Tupel Löschen
4. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Löschen
Beispiel
Beispiel
Lösche alle Studenten, die die Vorlesung ‘Logik’ besuchen
Lösche alle Studenten, die die Vorlesung ‘Logik’ besuchen
hören ( MatrNr , VorlNr )
Studenten ( MatrNr , Name , Sem )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
hören ( MatrNr , VorlNr )
Studenten ( MatrNr , Name , Sem )
Vorlesungen ( VorlNr , Titel , SWS , PersNr )
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
Seite 109
4. Datenmanipulationssprache
Sebastian Skritek
Relationale Abfragesprachen
Datenmanipulationssprache – Tupel Verändern
Seite 109
4. Datenmanipulationssprache
Datenmanipulationssprache – Tupel Verändern
Verändern von Tupeln
Beispiel
Erhöhen der Semesteranzahl aller Studierender um 1:
update Studenten
set Semester = Semester + 1;
Beispiel
Alle C3 Professoren mit einer Personalnummer über 2500 erhalten den
Rang C2
update Professoren
set Rang = ’ C2 ’
where Rang = ’ C3 ’ and PersNr > 2500;
Sebastian Skritek
Seite 110
Sebastian Skritek
Seite 110
Relationale Abfragesprachen
4. Datenmanipulationssprache
Relationale Abfragesprachen
Datenmanipulationssprache – Tupel Verändern
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
Sebastian Skritek
Relationale Abfragesprachen
Seite 111
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 111