Relationale Algebra-SQL

HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 1
Lehrveranstaltung DM/DB
Datenmodellierung und Datenbanksysteme
• Kennenlernen, wie man Daten
in einer Datenbank manipuliert
und wiederfindet.
Wir klären Grundbegriffe der
relationalen Algebra und des
relationalen Tupelkalküls;
Wir lernen die Datenbanksprache
SQL kennen und lösen typische
SQL-Aufgaben mit den BasisStatements. Dies wird im
Folgesemester erweitert
fortgesetzt.
Schritt 2
(Grundlegende Folien für die Wiederholung sind mit
gekennzeichnet!)
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 2
Relationale Algebra
Im Gegensatz zur Normalisierung, die auf die Verbesserung der Qualität
des Speichermodells zielt, zielt die relationale Algebra (wie auch das
relationale Tupelkalkül) auf die Verbesserung der Qualität der
Datenpräsentation.
Wir speichern
redundanzfrei,
Datenbank
aber denken
redundant !
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 3
Virtuelle Denormalisierung durch die relationale Algebra
Die durch Normalisierung auf mehrere Relationen verteilten semantisch
zusammengehörigen Informationen sollen in einer einheitlichen Sicht
präsentiert werden.
A
präsentierte Sicht
B
X
Literatur: Schubert, Datenbanken, B. G. Teubner Verlag 2005, Kapitel 7
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
Relationale Algebra und
(05)Relationale Algebra-SQL.ppt
SQL
Theorie
(Relationale Algebra und
relationales Tupelkalkül)
Praxis
(SQL)
logisches Fundament
+ Praxisanforderungen
= handhabbare
Datenbanksprache
Der Sprachumfang von SQL übersteigt in Folge ergänzender praktischer
Anforderungen die Ausdrucksmöglichkeiten der relationalen Algebra.
Folie 4
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL die standardisierte DB-Sprache
Structured Query Language = Standardisierte deskriptive Datenbanksprache für
relationale Datenbanken, zunächst Sequel (Structured English Query Language), 1986
unter Federführung der ISO fertig gestellt
ISO 9075:1989 Database Language SQL auch SQL-86;
ISO 9075:1990 Database Language SQL 2 auch SQL-92;
SQL 3: 2000 Erweiterung u. a. um objektorientierte Konzepte.
Grundfunktionen:
- Datenbank erstellen,
- Schema und Katalog erstellen,
- Tabellen erstellen und ändern,
- Views erstellen und ändern,
- Dateien indizieren,
- Daten schützen,
- Datenbankdaten aktualisieren,
- Daten selektieren,
- Sortieren,
- Transaktionen formulieren.
Grundlage ist das Relationenkalkül
D
D
L
D
M
L
WAS ist zu tun, nicht WIE !
Folie 5
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 6
Es gibt auch alternative DB-Sprachen, z. B. QBE
Query By Example = Grafische deskriptive Datenbank-Anfragesprache für relationale
Datenbanken, von IBM Research parallel zu SQL entwickelt.
Beispiel MS-SQL-Server:
Aber auch QBE
fußt auf SQL
und hat nur
eingeschränkte
Ausdrucksmöglichkeiten.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 7
Relationale Algebra und SQL
Ausgangspunkt ist die
Menge
(1) Eine Relation (Tabelle) wird beschrieben durch
- ihren Namen, z. B. Artikel und
- die Namen der Attribute (Spalten), z. B. Anzahl.
(2) Alle Operationen beziehen sich auf die gesamte
Menge.
Wir wollen zunächst die grundlegenden logischen Operationen über
Mengen kennenlernen. Die Darstellung in SQL dient dabei vorerst nur
zum "Eingewöhnen".
Erst danach betrachten wir SQL als Programmiersprache und lernen in
den Übungen SQL programmieren.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Grundoperationen über Relationen
SQL
1. Projektion (Auswahl von Spalten)
PersNr
Name
Vorname
023
Muh
145
Personal
Geschlecht
Abt
Magda
w
34
Wau
Harald
m
34
437
Miau
Irene
w
56
759
Quak
Andre
m
56
854
Piep
Ursel
w
78
PersNr
Name
Vorname
023
Muh
Magda
Definition: π A (r)
145
Wau
Harald
π PersNr, Name, Vorname (Personal)
437
Miau
Irene
759
Quak
Andre
854
Piep
Ursel
Beispiel:
Auswahl der ersten
drei Spalten für eine
externe Sicht!
Beispiel: Anzeige ausgewählter Personaldaten
SQL: Select PersNr, Name,
Vorname from Personal
Folie 8
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Grundoperationen über Relationen
Folie 9
SQL
2. Selektion [auch Restriktion] (Auswahl von Zeilen)
PersNr
Name
Vorname
Geschlecht
Abt
023
Muh
Magda
w
34
145
Wau
Harald
m
34
437
Miau
Irene
w
56
759
Quak
Andre
m
56
854
Piep
Ursel
w
78
PersNr
Name
Vorname
Geschlecht
Abt
023
Muh
Magda
w
34
437
Miau
Irene
w
56
854
Piep
Ursel
w
78
Personal
Beispiel: Auswahl aller
Personen weiblichen
Geschlechts
( Geschlecht = "w" )
Definition:
σ P(r)
σ Geschlecht='w' (Personal)
SQL: Select * from Personal where Geschlecht = 'w'
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 10
Grundoperationen über Relationen
3. [natürlicher] Join (Verbund von zwei oder mehr Tabellen)
PersNr
Name
Vorname
023
Muh
Magda
145
Wau
Harald
437
Miau
Irene
759
Quak
Andre
854
Piep
Ursel
PersNr
Name
Vorname
023
Muh
023
Sprache
Grad
Magda
engl
2
Muh
Magda
franz
1
437
Miau
Irene
engl
2
437
Miau
Irene
russ
3
759
Quak
Andre
engl
3
854
Piep
Ursel
franz
2
PersNr
Sprache
Grad
023
engl
2
023
franz
1
437
engl
2
437
russ
3
759
engl
3
854
franz
2
Verbunden über die Felder PersNr
in beiden Tabellen.
Beispiel: Anzeige der Sprachkenntnisse
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 11
Grundoperationen über Relationen
3. [natürlicher] Join (Verbund von zwei oder mehr Tabellen)
Sprachen
Personal
PersNr
Sprache
Grad
023
engl
2
023
franz
1
437
engl
2
437
russ
3
Andre
759
engl
3
Piep
Ursel
854
franz
2
PersNr
Name
Vorname
023
Muh
023
PersNr
Name
Vorname
023
Muh
Magda
145
Wau
Harald
437
Miau
Irene
759
Quak
854
Sprache
Grad
Magda
engl
2
Muh
Magda
franz
1
437
Miau
Irene
engl
2
437
Miau
Irene
russ
3
759
Quak
Andre
engl
3
854
Piep
Ursel
franz
2
Achten Sie darauf, dass in der
Ergebnis-Tabelle nur die Zeilen
verbunden wurden, bei denen die
PersNr gleich ist!
Die Zeile mit der PersNr=145 aus
der Tabelle Personal fehlt z. B. im
Ergebnis.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Grundoperationen über Relationen
Folie 12
SQL
3. [natürlicher] Join (Verbund von zwei oder mehr Tabellen)
Sprachen
Personal
PersNr
Name
Vorname
023
Muh
Magda
145
Wau
Harald
437
Miau
Irene
759
Quak
Andre
854
Piep
Ursel
PersNr
Sprache
Grad
023
engl
2
023
franz
1
437
engl
2
437
russ
3
759
engl
3
854
franz
2
Beispiel: Anzeige der Sprachkenntnisse
Definition: r1 P r2
PersNr
Name
Vorname
Sprache
Grad
023
Muh
Magda
engl
2
023
Muh
Magda
franz
1
437
Miau
Irene
engl
2
437
Miau
Irene
russ
3
759
Quak
Andre
engl
3
854
Piep
Ursel
franz
2
Personal
Personal
PersNr=PersNr Sprachen
oder
Sprachen
SQL:
Select PersNr, Name, Vorname,
Sprache, Grad from Personal
inner Join Sprachen on
(Personal.PersNr = Sprachen.PersNr)
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 13
Grundoperationen über Relationen
3. [natürlicher] Join (Verbund von zwei oder mehr Tabellen)
PersNr
Sprache
Grad
34
023
engl
2
m
34
023
franz
1
Irene
w
56
437
engl
2
Quak
Andre
m
56
437
russ
3
Piep
Ursel
w
78
759
engl
3
854
franz
2
PersNr
Name
Vorname
023
Muh
023
PersNr
Name
Vorname
Geschlecht
023
Muh
Magda
w
145
Wau
Harald
437
Miau
759
854
Abt
Sprache
Grad
Magda
engl
2
Muh
Magda
franz
1
437
Miau
Irene
engl
2
437
Miau
Irene
russ
3
759
Quak
Andre
engl
3
854
Piep
Ursel
franz
2
Verbunden über die Felder PersNr
in beiden Tabellen.
Beispiel: Anzeige der Sprachkenntnisse
Anmerkung: Projektion, Selektion
und Verbund können kombiniert
angewendet werden.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 14
Grundoperationen über Relationen
Join (Verbund von zwei oder mehr Tabellen)
Der Join (Verbund, Verknüpfung, Verkettung ) ermöglicht die Ableitung einer
Relation R(A1, ... An) aus der tupelweisen Verknüpfung zweier Relationen
R1(B1, ..., Bi) x R2(C1, ..., Cj)
kartesisches Produkt.
Durch spezielle Formen des Joins werden Einschränkungen definiert:
1. Theta-Join:
Selektion der Tupel aus dem Produkt zweier Relationen auf
Grund eines Prädikates P (z. B. Ai>Bj).
2. Equi-Join:
Selektion der Tupel aus dem Produkt zweier Relationen,
bei Gleichheit des Inhalts von ausgewählten in Semantik
und Wertebereich (Domäne) gleichen Attributen.
3. Natürlicher Join: Equi-Join mit Elimination der doppelt auftretenden Attribute.
4. Outer-Join:
Übernahme aller Tupel der ersten Relation R1 und
natürlicher Join mit den Tupeln der zweiten Relation R2
(auch Semi-Join; bzw. Left-Join und Right-Join).
Anmerkung: Für die Wirtschaftsinformatik sind insbesondere
der natürliche und der Outer-Join bedeutsam.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 15
Grundoperationen über Relationen (1)
ergänzende Operationen
Neben den für die Datenbanktechnologie bedeutendsten Operationen Selection,
Projektion und Joining sind prinzipiell alle definierten Mengenfunktionen auf
Relationen anwendbar. Praktisch sind das insbesondere die Operationen
• UNION (Vereinigung) zur Ausgabe n strukturgleicher Relationen in einer
gemeinsamen Relation;
Beispiel: Anzeige der Artikel mit allen jeweils gültigen Angebotspreisen,
berechnet aus den Relationen Angebot-Aktuell und -Archiv.
• INTERSECTION (Durchschnitt) zur Ermittlung der gemeinsamen Schnittmenge zweier Union-kompatibler Relationen;
Beispiel: Anzeige der Adressen der Firmen, die sowohl Kunde als auch
Lieferant sind.
• DIFFERENCE (Differenz) zur Bildung einer Relation mit allen Tupeln der
ersten Relation, die nicht in einer zweiten Relation enthalten sind;
Beispiel: Anzeige der Adressen der Firmen, die kein Lieferant sind.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 16
Grundoperationen über Relationen (2)
ergänzende Operationen
Neben den für die Datenbanktechnologie bedeutendsten Operationen Selection,
Projektion und Joining sind prinzipiell alle definierten Mengenfunktionen auf
Relationen anwendbar. Praktisch sind das insbesondere die Operationen
• DIVIDE (Division) zur Auswahl von Tupeln einer Relation (dem Zähler) mit
Hilfe einer anderen Relation (dem Nenner);
Beispiel: Anzeige nur der Lieferanten, die alle im Angebot enthaltenen
Artikel anbieten.
• UMBENNEN von Spalten zur Zuordnung eines Resultates zu einer neu
strukturierten Menge.
*) gehört nicht zu den von E. F. Codd definierten Operatoren.
Das Relationenkalkül (auch relationales Tupel-Kalkül) ist eine im Gegensatz zur
funktional orientierten Relationenalgebra eine deklarative formal logische
Sprache zur Formulierung von Aussagen über Relationen
SQL !
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 17
Zusatzoperationen über Relationen
Aggregatfunktionen und Gruppierung
Einige für kommerzielle DBMS notwendige Operationen lassen sich nicht mit
den Grundoperationen der relationalen Algebra formulieren. Sie sind jedoch
unentbehrlich für professionelle Datenbankauswertungen.
Aggregatfunktionen berechnen Werte über alle Tupel (Zeilen)
einer Relation (Tabelle), wenn kein Gruppierungsmerkmal existiert
ODER
einer Gruppe von Tupeln (Zeilen) einer Tabelle, wenn ein
Gruppierungsmerkmal existiert.
Grundlegende Aggregatfunktionen sind:
SUM (Summierung), COUNT (Anzahl), AVG (Durchschnitt),
MIN (Minimum), MAX (Maximum)
Eine Gruppe kann eine oder mehrere Attribute (Spalten) einer Relation
(Tabelle) umfassen und wird durch die Gleichheit der gespeicherten Daten
in diesen Attributen definiert.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 18
Zusatzoperationen über Relationen
Rekursive Abschlussoperationen
Einige für kommerzielle DBMS notwendige Operationen lassen sich nicht mit
den Grundoperationen der relationalen Algebra formulieren. Sie sind jedoch
unentbehrlich für professionelle Datenbankauswertungen.
Die rekursive Hülle (Recursive Closure) wird als Operation auf eine rekursive Beziehung zwischen Tupeln des gleichen Typs angewandt.
Beispiel: PERSONAL(PersNr, Name, …, LeiterPersNr)
Die Attribute PersNr und LeiterPersNr basieren auf der gleichen
Domäne, jedoch PersNr in der Rolle des Angestellten und
LeiterPersNr in der Rolle des Vorgesetzten.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 19
Zusammenfassung
Bis mindestens zur
3NF normalisierte
Tabellen dienen zur
qualitätsgerechten
(redundanzfreien,
konsistenten)
Datenspeicherung.
Nach den Regeln der
relationalen Algebra
präsentierte Daten dienen
der optimalen Darstellung
der Ergebnisse von
Datenbankauswertungen.
… und nun geht es richtig los mit SQL!
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – der relationale Datenbankstandard
Sequel ist recht
umfangreich,
aber wir
betrachten in
diesem Semester
nur die
Grundfunktionen
(Standard-SQL)!
Folie 20
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 21
Nochmals zur Wiederholung: SQL – Grundfunktionalität (Folie 5)
•
•
•
•
•
•
•
•
•
•
Datenbank erstellen (Create Database) und löschen,
Tabellen erstellen (Create Table), löschen und ändern,
Views erstellen (Create View), löschen und ändern,
Stored Procedure und Trigger erstellen, löschen und ändern,
Dateien indizieren,
Daten schützen,
Datenbank aktualisieren (Insert, Update, Delete)
Daten selektieren (Select) und ggf. sortieren,
Stored Procedures ausführen,
Transaktionen definieren und ausführen.
DDL = Data Definition Language
DML = Data Manipulation Language
DCL = Data Control Language
In diesem Semester behandelt
SQL dient also nicht
nur der Datenpräsentation, sondern ist
eine allumfassende
DatenbankProgrammiersprache
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 22
SQL - Mengenorientierung (1)
Datenbank
als Menge
Bei Bedarf grundsätzlich strukturiert nach der Zugehörigkeit zu einem Schema
und/oder Eigner (Schema- bzw. Owner-Name)
erst ab SQL2
Für jedes Schema (Owner) strukturiert durch benannte Teilmengen Table
(Table-Namen), vertikal strukturiert innerhalb der Tables durch benannte
Column (Spaltennamen).
Präsentiert ferner durch berechnete externe Sichten (Views) mit Spaltennamen.
Informationsdefinition innerhalb einer Table bzw. View als Row (Zeile) durch
Bedingungen. Dabei werden leere Werte (null) logisch von true und false
unterschieden.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 23
SQL - Mengenorientierung (2)
Datenbank
als Menge
Die vollständige Bezeichnung eines gespeicherten Datenbankwertes besteht
also aus
Datenbankname.Schemaname.Tabellenname.Spaltenname bzw.
Datenbankname.Schemaname.Viewname.Spaltenname.
Jedoch können
die Präfixe bei Eindeutigkeit weggelassen,
die Spalten als Gesamtheit mit dem Platzhalter * benannt
werden.
Beachte: Eine SQL-Operation bezieht sich immer auf die Gesamtheit der
benannten Teilmengen.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 24
SQL – grundsätzlicher Syntax
SQL ist eine formatfreie Sprache
•
•
•
Groß- und Kleinschreibung beliebig;
1 bis n Leerzeichen als Trennzeichen;
Zeilentrennung beliebig, aber nicht
innerhalb von Schlüsselworten.
SQL-Anweisungen müssen
separiert werden
•
•
Vor dem zweiten und jedem weiteren
SQL-Statement einer SQL-Folge
steht ein Separator;
Typische Separatoren sind ; oder GO
•
•
•
Schlüsselwörter
Eigennamen (für Tabellen, Spalten, ...)
Konstanten
SQL benutzt Sprachelemente
SQL-Kommentare werden in der Regel mit zwei Minuszeichen
gekennzeichnet und gelten bis zum Zeilenende.
-- Beispiel einer gültigen SQL-Anweisungsfolge
Insert into Noten values ('0502347', 'GRDL-DBA', 2.3) ;
Select * from noten
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 25
SQL – Aufgabenschritte - Befehlsfolge
Aufgabe (Auswahl)
SQL-Statements
Datenbank anlegen
Tabelle anlegen
…
ändern
…
löschen
Create Database datenbankname
Create Table tabellenname
Alter Table tabellenname
Drop Table tabellenname
Daten in Tabelle einfügen
…
ändern
…
löschen
Insert into tabellenname
Update tabellenname set …
Delete from tabellenname
Daten in Tabelle suchen
Select … from tabellenname where …
Externe Sicht
…
Create View viewname
Drop view viewname
Recht vergeben
Recht entziehen
anlegen
löschen
Grant recht on … to nutzer | rolle
Revoke recht on … from nutzer | rolle
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 26
SQL – Beispielrelationen
Für die nachfolgenden Beispiele wird eine Datenbank mit folgenden Relationen
benutzt. Sie können diese Datenbank lesend nutzen. Sie liegt auf den Servern
SqlServer\Lehre2,1123 und heißt [Vorlesung DMDB]. Die Zugangsdaten sowie
einen Ausdruck der DB-Diagramme erhalten Sie in der Vorlesung.
Diagramm Hochschule:
• LV(LNR, Fach, DozNr, Semester, Jahr, FG) Lehrveranstaltungen
• Studierende(Mnr,Fname,Vname,Geschl,SG,Immatrikulationsdatum)
• Dozenten(DozNr, DozTitel, DoznName, Lehrgebiet)
• Fachgebiet(FG, FGBez)
• Noten(Mnr, Fach, Note, LNR)
Diagramm Adressen:
• Adressen(regnr, name, plz, ort, str, telefon, mail)
• Kunden(regnr, KTyp, VDatum, Boni, Erstkauf, Kommentar)
• Liefer(regnr, Typ, Spez)
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 27
SQL – Beispiel der Definition einer Tabelle (Relation)
Beachten Sie!
Für jeden der nachfolgenden SQL-Statements (Befehle) müssen folgende
Bedingungen erfüllt sein:
Der Nutzer muss eine Zugriffsberechtigung für das DBMS haben.
Der Nutzer muss eine entsprechende Zugriffsberechtigung auf die
Datenbank haben.
Im SQL-Editor muss die entsprechende Datenbank aktiviert sein
(z. B. use [Vorlesung DMDB]).
CREATE TABLE dbo. Studierende
( Mnr char (4) NOT NULL,
Fname varchar (50) NOT NULL,
Vname varchar (50) NULL,
Geschl char (1) NULL DEFAULT ('w'),
SG varchar (2) NULL,
Immatrikulationsdatum datetime NULL,
CONSTRAINT PK_Studierende PRIMARY KEY (Mnr ) )
Spalte darf keine
NULL-Werte
enthalten
Spalte besitzt den
Vorgabewert 'w'
PrimärschlüsselDefinition
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 28
SQL – Beispiel der Definition einer referentiellen Integrität
CREATE TABLE Studierende
( Mnr char (4) NOT NULL,
Fname varchar (50) NOT NULL
Vname varchar (50) NULL,
Geschl char (1) NULL DEFAULT ('w'),
SG varchar (2) NULL,
Immatrikulationsdatum datetime NULL,
CONSTRAINT PK_Studierende PRIMARY KEY (Mnr ) )
CREATE TABLE Noten
( Mnr char (4) NOT NULL,
Fach char (5) NOT NULL
Note numeric (2,1) NOT NULL,
CONSTRAINT PK_Noten PRIMARY KEY (Mnr, Fach ) )
ALTER TABLE Noten
ADD CONSTRAINT FK_NoSTu
FOREIGN KEY (Mnr) REFERENCES Studierende(Mnr)
beide Tabellen
sollen bereits in der
Datenbank
existieren
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – Beispiel der Definition eines CHECK-Constraints
CREATE TABLE Studierende
( Mnr char (4) NOT NULL,
Fname char (50) NOT NULL
Vname varchar (50) NULL,
Geschl char (1) NULL DEFAULT ('w'),
SG varchar (2) NULL,
Immatrikulationsdatum datetime NULL,
CONSTRAINT PK_Studierende PRIMARY KEY (Mnr ) )
Die Tabelle soll
bereits in der
Datenbank
existieren
ALTER TABLE Studierende
ADD CONSTRAINT PrufGeschl
CHECK (Geschl IN ('w','m'))
Es wird bei Eingabe (Insert) oder Änderung (Update) geprüft, dass nur
die Werte 'w' oder 'm' in der Spalte Geschl stehen dürfen.
Folie 29
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – Beispiele für das Löschen von Datenbankobjekten
Generell werden Datenbankobjekte wie z. B. die gesamte Datenbank, eine
Tabelle, eine View usw. mit der Operation DROP gelöscht.
Das Löschen der Tabelle Studierende erfolgt dem entsprechend mit:
Drop Table Studierende
Teile von Objekten, wie z. B. eine Spalte einer Tabelle werden hingegen mit
der ALTER-Anweisung (Ändere …) und dem Unterparameter DROP
gelöscht.
Das Löschen der Spalte Geschl in der Tabelle Studierende erfolgt also mit:
ALTER TABLE Studierende DROP Geschl
Hinweis: Beim Löschen eines Datenbankobjektes werden auch sämtliche
Daten gelöscht.
Verwechseln Sie aber niemals das Löschen eines
Datenbankobjektes (DROP) mit dem Löschen nur von Daten
(DELETE).
Folie 30
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 31
SQL – Beispiel der Manipulation von Daten in eine Tabelle
INSERT – das Einfügen von Daten
z. B. INSERT INTO Fachgebiet
-- einfügen in Tabelle
VALUES ('PM', 'Programmmanagement') -- die Werte
oder SELECT …. (eine Select-Anweisung als Datenquelle)
UPDATE – das Ändern von Daten
z. B. UPDATE Fachgebiet
SET FGBez = 'Projektmanagement'
WHERE FG = 'PM'
-- ändern in Tabelle
-- der geänderte Wert
-- die zu ändernde(n) Zeile(n)
DELETE – das Löschen von Daten
z. B. DELETE FROM Fachgebiet
WHERE FG = 'PM'
-- löschen aus Tabelle
-- die zu löschende(n) Zeile(n)
Bitte beachten Sie unbedingt, dass dies nur einfachste Beispiele aus der
Befehlsgruppe sind. Der volle Befehlsumfang ist
wesentlich leistungsstärker.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – Select - Das Basis-Statement für die Datenpräsentation
SELECT ausgabespaltenliste FROM tabellen [ WHERE bedingung ]
Liste der Spalten in der Ergebnistabelle
Liste der in die Suche einbezogenen Tabellen
wahlweise Angabe einer Vergleichsbedingung
Beispiele:
• Gib alle Spalten der Tabelle STUDIERENDE aus
Select * from Studierende
• Gibt den Familien- und Vornamen der Studentinnen aus
Select Fname, Vname from Studierende where Geschl='w'
• Gib alle Studierende mit ihren Noten (stehen in Tabelle NOTEN) aus
Select Fname, Vname, Fach, Note
from Studierende, Noten
where Studierende.Mnr = Noten.Mnr
Folie 32
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – Grundstruktur der Select-Anweisung
(1) Eine bis n (reale oder virtuelle) Tabellen bilden die Datenquelle.
(2) Mit der Auswahl von Spalten (Projektion) können die Attribute
ausgewählt werden, die in das Datenziel übernommen werden.
(3) Mit 0 bis n Auswahlbedingungen (Selection) können die Zeilen
ausgewählt werden, die aus den Tabellen selektiert werden.
(4) Bei Bedarf können mehrere Zeilen zu einer Gruppe zusammengefasst werden (Aggregation). Mit einer Gruppenauswahlbedingung
können die Gruppen ausgewählt werden, deren Daten in das
Datenziel übernommen werden.
(5) Das Datenziel ist immer genau eine Tabelle.
Nach der Art und Weise der Datenauswahl und gegebenenfalls
Gruppierung
Tabelle 1 unterscheidet man
• einfache,
Auswahl
Ergebnis
Tabelle
2
• verschachtelte
Das wird nun im Einzelnen erklärt!
Gruppierung
• gruppierte
Tabelle n
SQL-Anweisungen.
Folie 33
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – einfache Select-Anweisung
Daten aus einer oder mehreren Tabellen werden aus der Datenquelle
ausgewählt.
Das Resultat wird in die Ergebnistabelle (Datenziel) geschrieben.
Logischer Ablauf:
(1) Eine Zeile wird aus der Datenquelle gelesen.
(2) Die Auswahlbedingung wird auf die Zeile angewandt.
(3) Ist die Auswahlbedingung erfüllt (true), wird die Zeile in das
Datenziel übernommen.
Beispiele:
Auflistung aller Noten ohne Bedingung
Select Mnr, Lnr, Note from Noten
Auflistung der Noten nur für die Lehrveranstaltungen (Lnr) 10 und 11
Select Mnr, Lnr, Note from Noten where Lnr=10 or Lnr=11
Folie 34
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 35
Ein weiteres Beispiel für eine einfache Select-Anweisung
Aus der nachfolgend beschriebenen Tabelle STUDIERENDE sollen
ausgewählte Spalten (Matrikelnummer, Vorname, Familienname, Geschlecht,
Studiengang) in die Ergebnistabelle geschrieben werden, aber nur unter der
Bedingung, dass die Zeile eine Studentin betrifft.
Beispiel: STUDIERENDE(Mnr, Fname, Vname, Geschl, SG)
Select Mnr, Vname, Fname, SG as Studiengang
from Studierende
where Geschl = 'w'
Das Resultat dieser Select-Anweisung liefert die Daten aller
Studentinnen unter der Voraussetzung, dass die Codierung des
Geschlechts in der Spalte Geschl durch den Wert "w" erfolgt.
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 36
SQL – Select kann auch rechnen und Funktionen nutzen
Neben dem Selektieren von Informationen kann der Informationswert durch
Berechnungen (4-Grundrechenarten) und Anwendung von Funktionen
erhöht werden. Zum Beispiel ist das Rechnen mit Datumswerten eine Stärke
von SQL.
Beispiel: STUDIERENDE(Mnr, Fname, Vname, Geschl, SG, Immatrikulationsjahr)
Select Mnr, Vname, Fname, SG
from Studierende
where Immatrikulationsjahr = Year(Getdate())
Das Resultat dieser Select-Anweisung liefert die Daten aller
Studierenden, die im aktuellen Kalenderjahr immatrikuliert wurden.
Beachten Sie hier unbedingt die Modifikation Immatrikulationsjahr
gegenüber Immatrikulationsdatum in der Tabelle STUDIERENDE!
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – Select über mehrere Tabellen (Joining)
Eine SQL-Anweisung über mehrere Tabellen (Joining) wird logisch durch
Bildung des kartesischen Produktes und Streichung aller Zeilen der
Ergebnistabelle, für die die Where-Klausel den Wert false liefert, realisiert.
Beispiel: STUDIERENDE(Mnr, Fname, Vorname, Geschl, SG)
NOTEN(Mnr, Fach, Note)
Select Studierende.Mnr, Fname, Fach, Note
from Studierende, Noten where
Studierende.Mnr=Noten.Mnr
Die Realisierung erfolgt logisch durch die Bildung von n * m Zeilen, die
jede der n Zeilen der Tabelle Studierende mit den m Zeilen der Tabelle
Noten über die ausgewählten Spalten verknüpft. Danach werden alle
Zeilen eliminiert, für die die Matrikelnummer nicht gleich ist.
Folie 37
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 38
SQL – Select aus logischer Sicht (Beispiel)
Select * from Studierende, Noten where Studierende.Mnr = Noten.Mnr
Studierende
Noten
Mnr
Fname
Vname
Geschl
SG
Mnr
Fach
Note
0100
Zobel
Zacharias
m
WI
0100
DB
3
0200
Adler
Anna
w
WI
0200
DB
1,7
0200
SE
2
Resultat der Select-Anweisung
Mnr
Fname
Vname
Geschl
SG
Mnr
Fach
Note
0100
Zobel
Zacharias
m
WI
0100
DB
3
100=100
true
0100
Zobel
Zacharias
m
WI
0200
DB
1,7
100=200
false
0100
Zobel
Zacharias
m
WI
0200
SE
2
100=200
false
0200
Adler
Anna
w
WI
0100
DB
3
0200
Adler
Anna
w
WI
0200
DB
1,7
200=100
200=200
false
true
0200
Adler
Anna
w
WI
0200
SE
2
200=200
true
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – Joining über komplexe Modelle (1)
Mehrfaches Joining erhöht schnell die Komplexität, ist aber oft erforderlich !
Beispiel: Ausweis aller Noten mit Lehrveranstaltung und Dozent (SQL-89)
Select Noten.Mnr, Vname, Fname, LV.Fach, Dozname , Note
from Studierende, LV, Dozenten, Noten
where Studierende.Mnr = Noten.Mnr AND
Noten.LNR = LV.LNR AND
LV.DozNr = Dozenten.DozNr
Beachte: Im Regelfall müssen alle nach FROM aufgelisteten Tabellen im
Joining angesprochen werden.
Alle referentiellen Abhängigkeiten sind Kandidaten für ein
Joining, das Joining ist aber umfassender !!!
Folie 39
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 40
SQL – Joining über komplexe Modelle (2)
Mehrfaches Joining erhöht schnell die Komplexität, ist aber oft erforderlich !
Beispiel: Ausweis aller Noten mit Lehrveranstaltung und Dozent (SQL-92)
Select Noten.Mnr, Vname, Fname, LV.Fach, Dozname, Note
from Studierende INNER JOIN Noten
ON Studierende.Mnr = Noten.Mnr
INNER JOIN LV
ON LV.LNR = Noten.LNR
INNER JOIN Dozenten
ON LV.DozNr = Dozenten.Doznr
Hier wird das Joining exakt auf die referentiellen Beziehungen zwischen
den Tabellen abgebildet.
Aber: Welcher Dozent ist für sein Lehrgebiet eingesetzt ?
Select LV.Doznr, DozTitel, DozName, LV.FG
from Dozenten INNER JOIN LV ON (Dozenten.Doznr = LV.Doznr)
AND (Dozenten.Lehrgebiet = LV.FG)
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – verschachtelte Select-Anweisung (Subquery)
Daten aus einer oder mehreren Tabellen werden aus der Datenquelle
ausgewählt.
Die Datenauswahl kann in Form einer Sub-Select-Anweisung erfolgen und
zwar, in der Liste der Spalten für die Ergebnistabelle und/oder in der Auswahlbedingung und/oder als Datenquelle.
Das Resultat wird in die Ergebnistabelle (Datenziel) geschrieben.
Logischer Ablauf:
(1) Eine Zeile wird aus der Datenquelle gelesen, dabei wird die
Sub-Select-Anweisung für jede Zeile neu berechnet.
(2) Die Auswahlbedingung wird auf die Zeile angewandt.
(3) Ist die Auswahlbedingung erfüllt (true), wird die Zeile in das
Datenziel übernommen.
Beispiel: Gesucht sind alle Studierende, die eine Note 5 haben
Select * from Studierende where
5 = some (Select Note from Noten where Studierende.mnr =Noten.mnr)
(some oder auch any bedeutet "irgendein“)
Folie 41
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – Subquery aus logischer Sicht
Eine Unter- (auch Sub-) Select-Anweisung wird logisch als Joining jeder
einzelnen Ergebniszeile der (Haupt-) Select-Anweisung mit den in der SubSelect-Anweisung enthaltenen Tabellen realisiert.
Beispiel: -- Gesucht werden alle Studierenden ohne Noten
STUDIERENDE(Mnr, Fname, Vname, Geschl, SG)
NOTEN(Mnr, Fach, Note)
Select Mnr, Fname from Studierende where
not exists (Select * from Noten where
Studierende.Mnr = Noten.Mnr)
Die Realisierung erfolgt logisch durch die Existenzprüfung (exists), ob das
in der Sub-Select-Anweisung beschriebene Joining mindestens eine
Ergebniszeile hat. In das Joining einbezogen wird jedoch immer nur die
(eine) aktuelle Zeile der (Haupt-) Select-Anweisung.
Folie 42
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 43
SQL – Subquery aus logischer Sicht (Beispiel - 1)
Select Mnr, Fname from Studierende where not exists
(Select * from Noten where Studierende.Mnr = Noten.Mnr)
Studierende
Noten
Mnr
Fname
Vname
Geschl
SG
Mnr
Fach
Note
0100
Zobel
Zacharias
m
WI
0100
DB
3
0300
Bär
Berta
w
WI
0200
DB
1,7
0200
SE
2
Zwischen-Resultat der Select-Anweisung
Mnr
Fname
Vname
Geschl
SG
Mnr
Fach
Note
0100
Zobel
Zacharias
m
WI
0100
DB
3
0100
Zobel
Zacharias
m
WI
0200
DB
1,7
0100
Zobel
Zacharias
m
WI
0200
SE
2
0300
Bär
Berta
w
WI
0100
DB
3
0300
Bär
Berta
w
WI
0200
DB
1,7
0300
Bär
Berta
w
WI
0200
SE
2
1. Zeile
exists = true
2. Zeile
exists =false
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 44
SQL – Subquery aus logischer Sicht (Beispiel - 2)
Select Mnr, Fname from Studierende where not exists
(Select * from Noten where Studierende.Mnr = Noten.Mnr)
Zwischen-Resultat der Select-Anweisung
Mnr
Fname
Vname
Geschl
SG
Mnr
Fach
Note
0100
Zobel
Zacharias
m
WI
0100
DB
3
0100
Zobel
Zacharias
m
WI
0200
DB
1,7
0100
Zobel
Zacharias
m
WI
0200
SE
2
0300
Bär
Berta
w
WI
0100
DB
3
0300
Bär
Berta
w
WI
0200
DB
1,7
0300
Bär
Berta
w
WI
0200
SE
2
Resultat der Select-Anweisung
Mnr
Fname
Vname
Geschl
SG
0300
Bär
Berta
w
WI
1. Zeile
exists = true
2. Zeile
exists =false
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 45
SQL – Subquery (Beispiel - 3)
Sie Sub-Select-Anweisung ist aber noch universeller und kann z. B. auch
in der Ausgabespaltenliste oder in der Tabellenliste nach FROM
eingesetzt werden.
Beispiel: STUDIERENDE(Mnr, Fname, Vname, Geschl, SG)
NOTEN(Mnr, Fach, Note)
Select Mnr, Fname,
(Select avg(Note) from Noten
where Noten.Mnr = Studierende.Mnr) as Durchschnitt
from Studierende
Allerdings ist das Beispiel erst verständlich, wenn wir die Aggregatfunktion
kennen ;-)
Deshalb schauen wir uns nun diese an
nächste Folie
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – gruppierte Select-Anweisung
Daten aus einer oder mehreren Tabellen werden aus der Datenquelle
ausgewählt und zu einer Gruppe zusammengefasst.
Das Resultat wird in die Ergebnistabelle (Datenziel) geschrieben.
Logischer Ablauf:
(1) Eine Zeile wird aus der Datenquelle gelesen und einer Gruppe
zugeordnet.
(2) Die Auswahlbedingung wird auf die Zeile angewandt.
(3) Ist die Auswahlbedingung erfüllt (true), wird die Zeile in die Gruppe
übernommen.
(4) Sind alle Zeilen einer Gruppe bearbeitet, wird die Gruppenauswahlbedingung auf das Gruppenergebnis angewandt.
(5) Ist die Gruppenauswahlbedingung erfüllt (true), wird das Gruppenergebnis
in das Datenziel übernommen.
Beispiel: Berechnung der Anzahl der Studierenden je Studiengang
Select SG as Studiengang, Count(*) as Anzahl from Studierende
group by SG
Folie 46
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – Select mit Aggregation (1)
(1) Die Aggregation bildet einen Wert über eine Gruppe von Zeilen.
(2) Aggregate können sein,
- die Summe über eine numerische Spalte (SUM),
- der Durchschnitt über eine numerische Spalte (AVG),
- die Anzahl von Zeilen über die gesamte Gruppe (COUNT (*)),
- das Minimum über eine Spalte (MIN),
- das Maximum über eine Spalte (MAX).
(3) Eine Gruppe von Zeilen wird gebildet durch
- alle Zeilen einer Tabelle,
- eine bestimmte Menge von Zeilen, die ein gleiches Gruppenmerkmal
besitzt.
(4) Ein Gruppenmerkmal wird gebildet, durch den Inhalt einer bis n Spalten.
Beispiel: Es soll der Durchschnitt aller Noten gebildet werden
Select avg(Note) as Notendurchschnitt from Noten
Folie 47
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – Select mit Aggregation (2)
Beispiel: Es soll der Durchschnitt aller Noten, gruppiert nach
Studiengängen gebildet werden.
Select SG as Studiengang, avg(Note) as Notendurchschnitt from
Noten inner join Studierende on Noten.Mnr = Studierende.Mnr
group by SG
Group by SG bezeichnet den Inhalt der Spalte SG (Studiengang) als
Gruppierungsmerkmal.
Avg(Noten) ist die Aggregatfunktion zur Berechnung des
Notendurchschnitts.
as Notendurchschnitt benennt die durch eine Funktion berechnete
Spalte in der Ergebnistabelle.
Folie 48
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 49
SQL – Select wird also verwendet …
• Zur Auflistung von Datenbankinhalten mit und ohne Such-Bedingung.
• Zur Präsentation der Daten in einer bestimmten Sortierfolge.
• Als Sub-Select-Anweisung zur Verknüpfung einer Tabellenzeile mit einer
Auflistung von Datenbankinhalten (Rekursion möglich).
• Zur Formulierung des Joinings (direkt in SQL-92 bzw. indirekt in SQL-89).
• Zur Berechnung von Aggregaten über eine Gruppe von Zeilen.
• Zur Formulierung von Views.
• Und ferner als Anweisungsteil des Insert-, Delete- und Update-Statements,
• sowie in modifizierter Form zur Bereicherung der Programmiermöglichkeiten in Stored Procedures, Trigger und Funktionen.
Das Select-Statement ist damit das grundlegende SQL-Statement
zur Realisierung der Selection (Auswahl von Daten aus der
Datengesamtheit einer Datenbank).
Dazu noch einige Erweiterungen auf den Folgefolien
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
SQL – Select mit Rekursion
Beispiel: Es sollen alle Studierenden ermittelt werden, deren Notendurchschnitt besser als der generelle Notendurchschnitt ist.
Select Studierende.fname, avg(Note) as Durchschnittsnote
from Noten as NoteST inner join Studierende
on NoteST.Mnr = Studierende.Mnr
group by fname
having avg(Note) < (Select avg(Note) from Noten)
Group by fname bezeichnet den Inhalt der Spalte Fname
(Familienname) als Gruppierungsmerkmal.
Noten as NoteST benennt die Tabelle Noten virtuell um, damit sie
von dem Zugriff auf die gleiche Tabelle in der Sub-Select-Anweisung
unterschieden werden kann.
having avg(Note) ist die Bedingungsklausel für die Einbeziehung von
Aggregaten in Vergleiche (WHERE bezieht sich immer nur auf eine
Zeile einer Tabelle)
Folie 50
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Spezielle Ausprägungen des Select-Statements (1)
UNION – die logische Vereinigung mehrerer Ergebnistabellen
Beispiel:
Select Studierende.Mnr, Fname, Vname,
cast(avg(Note) as varchar) as Durchschnittsnote
from Studierende inner join Noten on Studierende.Mnr=Noten.Mnr
group by Studierende.Mnr, Fname, Vname
UNION
select Studierende.Mnr,Fname,Vname,'keine Note' from Studierende
where Mnr not in (Select distinct Mnr from Noten)
Wozu braucht man das?
Mehrere ähnliche Ergebnistabellen, die sich jedoch in der Herkunft
einzelner Spalten unterscheiden, können in eine gemeinsame
Ergebnistabelle gespeichert und sortiert werden.
Folie 51
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Spezielle Ausprägungen des Select-Statements (2)
INTERSECT – der logische Durchschnitt mehrerer Tabellen
Beispiel: Wer ist sowohl Kunde als Lieferant
Select regnr from Kunden
INTERSECT
Select regnr from Liefer
order by regnr
Wozu braucht man das?
Man kann aus ähnlichen Tabellen (insbesondere bei der Spezialisierung von Tabellen <Vererbung> ) ermitteln, welche Objekte in
beiden Kategorien anzutreffen sind.
Folie 52
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Spezielle Ausprägungen des Select-Statements (3)
EXCEPT – die logische Mengendifferenz mehrerer Tabellen
Beispiel: Wer ist Kunde aber nicht Lieferant
Select regnr from Kunden
EXCEPT
Select regnr from Liefer
order by regnr
Wozu braucht man das?
auf alle Fälle nur sehr selten ☺, aber man kann damit ermitteln, wer
nur Mitglied einer bestimmten Teilmenge ist.
Eine logisch gleichwertige Lösung wäre
Select regnr from Kunden
where regnr not in (Select regnr from liefer)
order by regnr
Folie 53
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Die VIEW – eine materialisierte Select-Anweisung (1)
Externe Sichten (Views) verkörpern als Baustein die "Intelligenz" der
Datenpräsentation.
Beispiel: Es soll der Durchschnitt aller Noten, gruppiert nach
Studiengängen gebildet werden, formuliert als View !
Create View DNoten (Studiengang, Notendurchschnitt)
As Select SG , round(avg(Note),1) from Noten
inner join Studierende on Noten.Mnr = Studierende.Mnr
group by SG
Diese als virtuelle Tabelle (Berechnungsvorschrift) gespeicherte View
wird bei jedem Aufruf neu berechnet. Sie kann als Projektbaustein
benutzt werden.
Folie 54
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 55
Die VIEW – eine materialisierte Select-Anweisung (2)
Externe Sichten (Views) verkörpern als Baustein die "Intelligenz" der
Datenpräsentation.
Beispiel: Es sollen alle Studierenden mit ihrem Notendurchschnitt ausgewiesen werden, die besser oder gleich dem Durchschnitt ihres
Studienganges sind.
Die als virtuelle Tabelle (Berechnungsvorschrift) gespeicherte View DNoten
wird bei jedem Aufruf neu berechnet. Sie kann als Projektbaustein benutzt
werden.
Select Noten.Mnr, Fname, Vname, avg(Note) as DurchschnNote
from Studierende, Noten, DNoten
Where Studierende.Mnr = Noten.Mnr AND
Studierende.SG = DNoten.Studiengang
group by Noten.Mnr, Fname, Vname, Notendurchschnitt
having avg(Note) <= Notendurchschnitt
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Dynamisches SQL – eine etwas komplizierte Praxisanforderung
Häufig werden in der Praxis SQL-Statements benutzt, die sich für verschiedenartigsten Anwendungsfälle nur durch die Änderung einer Konstanten unterscheiden.
Beispiel: Es sollen alle Lehrveranstaltungen einer Dozentin oder eines
Dozenten ermittelt werden.
Die dazu erforderliche SQL-Anweisung beibt immer gleich, es ändert sich
nur die jeweils gefragte Dozentennummer.
Um dieses Problem zu lösen, gibt es mehrere Möglichkeiten.
SQL bietet hierzu z. B. die Prozedur sp_executesql.
Mehr dazu in den Übungen!
Folie 56
HTW Berlin Prof. Dr. Zschockelt
Datenmodellierung/Datenbanken
(05)Relationale Algebra-SQL.ppt
Folie 57
Zum Abschluss: SQL in der Praxis …
… wird in verschiedenen Gebieten der (Wirtschafts-)
Informatik gebraucht. Die wichtigsten sind:
• Datenbankentwicklung (Tabellen, Sichten, Rechte usw. erstellen
und warten.
• Applikationsentwicklung (vor allem Daten manipulieren und präsentieren).
• Dynamische Web-Seiten (Zugriff mittels Skriptsprachen ASP.NET, PHP …).
• Betriebliches Informationsmanagement im weitesten Sinne.
• Business Intelligence (Entscheidungsunterstützung) usw.
Die Anwendung erfolgt häufig in Mischform mit DatenbankVerwaltungstools. Beispielsweise werden das Anlegen einer neuen
Datenbank, das Erteilen und Entziehen von Rechten und ähnliches häufig
direkt über Verwaltungstools realisiert und erst von diesen in SQLAnweisungen übersetzt.