Vorlesung 4 - SQL (Teil 1)

WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Vorlesung #4
SQL (Teil 1)
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
„Fahrplan“
 Wiederholung/Zusammenfassung
 Relationales Modell
 Relationale Algebra
 Relationenkalkül






Geschichte der Sprache SQL
SQL DDL (CREATE TABLE ...)
SQL DML (INSERT, UPDATE, DELETE)
SQL Abfragen
Aggregation und Gruppierung
Null-Werte, 3-wertige Logik
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
2
Geschichte von SQL
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 Anfang 70er Jahre „System R“ von IBM mit der
Sprache „SEQUEL“ – Structured English Query
Language, später aus patentrechtlichen Gründen in
SQL unbenannt
 Parallel entwickelte man QUEL als Anfragesprache
für das INGRES-System
 SQL setzte sich durch als Industrie-Standard (wegen
„Ignorranz“ der INGRES-Entwickler)
 1986: SQL-86 1. SQL Norm von ANSI Comitee
 1992: SQL-92 bzw. SQL-2
 1999: SQL-99 bzw. SQL-3 (objektrelationale
Erweiterungen)
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
3
Datentypen in SQL
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Atomare Datentypen als Attribut-Domänen
 Zahlen
 numeric(p,s) – number(p,s)
 integer
 float
 Zeichenketten
 character(n) – char(n)
 char varying (n) – varchar(n), varchar2(n)
 Datumstyp
 date
 Weitere: BLOB (Binary Large Objects), RAW für
große Binärdatein, CLOB (Character LOB), benutzerdefinierte Typen als objektrelationale Erweiterung ...
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
4
Einfache Datendefinition
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
SQL DDL (Data Definition Language)
CREATE TABLE Professoren
(PersNr
INTEGER,
Name
CHARACTER VARYING(30),
Rang
CHARACTER(2));
CREATE TABLE professoren
(persnr
NUMBER(*,0),
name
VARCHAR2(30),
rang
CHAR(2));
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
5
Schemaveränderungen
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
SQL DDL (Data Definition Language)
Hinzufügen eines Attributs bzw. einer Spalte
ALTER TABLE Professoren
ADD Raum INTEGER;
Löschen eines Attributs bzw. einer Spalte
ALTER TABLE Professoren
DROP COLUMN Raum;
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
6
Datenmanipulation (1)
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
SQL DML (Data Manipultaion Language)
Einfügen von Tupeln
insert into hören
select MatrNr, VorlNr
from Studenten, Vorlesungen
where Titel= `Logik‘ ;
insert into Studenten (MatrNr, Name)
values (28121, `Archimedes‘);
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
7
Datenmanipulation (2)
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
SQL DML (Data Manipultaion Language)
Löschen von Tupeln
delete Studenten
where Semester > 13;
Verändern von Tupeln
update Studenten
set Semester= Semester + 1;
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
8
Einfache SQL Anfragen
select
PersNr, Name
from
Professoren
where
Rang= ´C4´;
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
PersNr Name
2125 Sokrates
2126
Russel
2136
Curie
2137
Kant
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
9
Einfache SQL Anfragen (2)
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Sortierung
PersNr
Name
Rang
2136
Curie
C4
from Professoren
2137
Kant
C4
order by Rang desc, Name asc;
2126
Russel
C4
2125
Sokrates
C4
select PersNr, Name, Rang
2134 Augustinus C3
desc – descending 
2127 Kopernikus C3
asc – ascending 
2133
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
Popper
C3
10
Einfache SQL Anfragen (3)
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Duplikateliminierung
select distinct Rang
from Professoren;
© Bojan Milijaš, 28.10.2011
Rang
C3
C4
Vorlesung #4 - SQL (Teil 1)
11
Anfragen über mehrere
Relationen
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Welcher Professor liest "Mäeutik"?
select Name, Titel
from Professoren, Vorlesungen
where PersNr = gelesenVon and Titel = `Mäeutik‘ ;

Name, Titel
( PersNr  gelesenVon  Titel ' Mäeutik' (Professor en  Vorlesunge n))
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
12
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Anfragen über mehrere
Relationen (2)
Professoren
PersNr Name Rang Raum
2125 Sokrates C4 226
2126 Russel
C4 232
2137
Kant
C4
7
Vorlesungen
VorlNr
Titel
SWS gelesenVon
5001 Grundzüge
4
2137
5041
Ethik
4
2125
5049
Mäeutik
4630 Die 3 Kritiken
2
2125
4
2137
Verknüpfung

© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
13
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
14
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
3 Wege Join
Welche Studenten hören welche Vorlesungen?
select Name, Titel
from Studenten, hören, Vorlesungen
where Studenten.MatrNr = hören.MatrNr and
hören.VorlNr = Vorlesungen.VorlNr;
Alternativ:
select s.Name, v.Titel
from Studenten s, hören h, Vorlesungen v
where s. MatrNr = h. MatrNr and
h.VorlNr = v.VorlNr
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
15
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
16
Aggregatfunktionen
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 min, max, avg, sum, count
SELECT
FROM
SELECT
FROM
SELECT
FROM
SELECT
FROM
SELECT
min(Semester)
Studenten;
max(Semester)
Studenten;
avg(Semester)
Studenten;
sum(Semester)
Studenten;
count(MatrNr), -- count(*)
count(DISTINCT Semester)
FROM Studenten;
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
17
... mit Gruppierung
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 min, max, avg, sum, count mit GROUP BY
select gelesenVon, sum (SWS)
from Vorlesungen
group by gelesenVon;
 mit HAVING
select gelesenVon, Name, sum (SWS)
from Vorlesungen, Professoren
where gelesenVon = PersNr and Rang = ´C4´
group by gelesenVon, Name
having avg (SWS) >= 3;
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
18
WS 2011/12
Besonderheiten bei
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Aggregatoperationen
• SQL erzeugt pro Gruppe ein Ergebnistupel
• Deshalb müssen alle in der select-Klausel
aufgeführten Attribute - außer den
aggregierten – auch in der group byKlausel aufgeführt werden
• Nur so kann SQL sicherstellen, dass sich
das Attribut nicht innerhalb der Gruppe
ändert
SELECT A1, A2, An, count(*)
FROM ...
WHERE ..
GROUP BY A1, A2, An
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
19
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
20
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
21
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
22
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Ergebnis der Abfrage
gelesenVon
2125
Name
Sokrates
sum (SWS)
10
2137
Kant
8
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
23
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Nullwerte
 unbekannter Wert
 „wird vielleicht später nachgereicht“
 Nullwerte können auch im Zuge der
Anfrageauswertung entstehen (Bsp. äußere Joins)
 manchmal sehr überraschende Anfrageergebnisse,
wenn Nullwerte vorkommen

UPDATE studenten SET semester = '' WHERE matrnr = 24002;
select count (*)
from Studenten
where Semester < 13 or Semester >= 13
 Tupel (Zeilen) mit Null-Werten werden einfach
nicht mitgezählt !!!
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
24
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Nullwerte (2)
 NULL + 1 = NULL, NULL * 1 = NULL
 Beispiele (Oracle)
SELECT NULL + 2
FROM Dual;
SELECT NULL * 2
FROM Dual;
 Dual ist so etwas wie „Dummy“-Tabelle in Oracle
 Es gilt die sogenannte 3-wertige Logik in SQL
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
25
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
3-wertige Logik
(NULL Logik)
not
TRUE FALSE
NULL NULL
FALSE TRUE
and
TRUE
NULL
FALSE
TRUE NULL
TRUE NULL
NULL
NULL
FALSE FALSE
or
TRUE
TRUE
TRUE
NULL
TRUE
FALSE
TRUE
NULL
FALSE
TRUE
TRUE
NULL
NULL
NULL
FALSE
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
FALSE
FALSE
FALSE
FALSE
26
Regeln für NULL Werte
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 In arithmetischen Ausdrücken werden NULL Werte
propagiert (NULL + 3 = NULL)
 Es gilt die 3-wertige Logik. Vergleichsoperatoren
(=, >,<=) liefern immer NULL zurück, wenn
mindestens ein Argument NULL ist
 Logische Ausdrücke werden entsprechend den
vorgestellten 3-wertige-Logik Tabellen ausgewertet
 Tupeln mit NULL als Ergebnis in einer WHERE
Klausel werden nicht weitergereicht, d.h NULL
Werte werden in WHERE Klauseln ausgelassen
 Bei Gruppierung ist NULL ein eigenständiger Wert
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
27
Behandlung von NULL Werten
(Oracle und SQL-92)
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
 In Oracle gibt es eine „null value“ Funktion nvl (if
NULL then):
SELECT nvl(NULL,5) + 2 AS Result
FROM Dual;
Result
-----7
 Man kann NULL Werte mit IS NULL oder IS NOT
NULL testen (SQL-92)
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
28
Ausblick Vorlesung #4









WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
SQL, Fortsetzung
Geschachtelte Anfragen
Korrelierte Anfragen
Mengenoperationen
Quantifizierte Anfragen (, )
Spezielle Sprachkonstrukte
Joins in SQL-92, SQL-99
Rekursion
Sichten (Views)
© Bojan Milijaš, 28.10.2011
Vorlesung #4 - SQL (Teil 1)
29
WS 2011/12
Datenbanksysteme
Fr 15:15 – 16:45
R 0.006
Vorlesung #4
Ende