5. Datenabfrage mit SQL

Informationsbestände analysieren
Datenabfrage mit SQL
5.
Datenabfrage mit SQL14
5.1.
Einige Infos zu SQL
SQL wurde Ende der 70er Jahre in den IBM Labors entwickelt und später als
Standard für relationale Datenbank eingeführt.
Bei SQL handelt es sich um eine deklarative Programmiersprache, d.h. dass nicht
das WIE – typisch bei prozeduralen (auch imperativen) Programmiersprachen wie
Pascal, C usw. – sonder das WAS im Vordergrund steht.
Was will ich von der Datenbank wissen?
Beispiel :
Zeige mir alle Datensätze von Peter Meier an der Hohlstrasse in Zürich, bei
denen der Kaufbetrag über Fr.100.- liegt und die Rechnung noch nicht
beglichen ist.
5.1.1. Sprachteile von SQL
SQL selbst setze sich aus diversen Sprachteilen zusammen, die je nach Aktion (des
Benutzers bzw. Administrators) zum Einsatz kommen.
Ebenen eines DB-Systems
SQL
Anwender 1
Anwender 2
DQL
DML
Externe
Sicht
Programm 1
Programm 2
DDL
DCL
TPL
ER
Konzeptionelle
Sicht
Logische Datenstruktur
Interne
Sicht
Physische Datenstruktur
Betriebssystem
File
ls -l
cd mydir
Hardware
Abbildung 54 :
14
SQL und Datenbanksystem
Structured Query Language
© René Probst
Jan 2004
5-1
Modul-100
Theorie
DQL
Data Query Language, beschreibt den Teil der Sprache, mit dem sich Daten aus
dem bestand der DB abfragen lassen
DML
Data Manipulation Language, beschreibt den Teil der Sprache, der für einfügen,
ändern und löschen von Daten zuständig ist.
DDL
Data Definitin Language, beschreibt den Teil der Sprache, der für die Definition der
Datentabellen und der Beziehungen benötigt wird.
DCL
Data Control Language, beschreibt den Teil, der für die sogenannte referentielle
Integrität der Daten verantwortlich ist.
Unter referentieller Integrität versteht man eine durch die Datenbank überwachte
Beziehung zwischen Tabellen.
Beispiel :
Wenn die Beziehung Person-Konto eine
referentielle Integrität aufweist, lässt sich
eine Person nicht löschen, solange ein
zugehörender Eintrag in der Tabelle Konto
existiert. Anderseits lässt sich in der
Tabelle Konto ein Datensatz nur
speichern, wenn auch eine Referenz auf
einen Eintrag in der Tabelle Person
besteht.
Person
PID
Name
Konto
KID
Person
Saldo
TPL
Transaction Programing Language, beschreibt den Teil der Sprache, der für
umfassende Lese-/Schreibvorgänge verwendet wird.
Unter einer Transaktion versteht man mehrere zusammengehörende SQLAnweisungen, die nur in ihrer Gesamtheit einen Sinn ergeben. Daher muss bei
deren Ausführung geprüft werden, ob alle Schritte korrekt abgelaufen sind, damit
dann die gemachten Änderungen in der Datenbank auch wirklich gültig sind.
Beispiel :
Vom Bankkonto von Frau Huber soll der Betrag von Fr. 540.05 abgebucht und
bei der Firma TopVersand wieder eingebucht werden. Wenn z.B. nach dem
Abbuchen das Computersystem einen Ausfall erfährt, würde der Betrag bei
Frau Huber fehlen, wäre aber bei der Firma TopVersand noch nicht
eingebucht, weshalb diese dann den Betrag wohl mahnen würde.
In diesem Beispiel muss die Transaktionskontrolle der Datenbank dafür sorgen,
dass die Änderungen temporär einmal festgehalten sind, dass aber die
tatsächlichen Buchungen erst nach Abschluss der gesamten Transaktion ausgeführt
werden. Nur so ist die Integrität der Daten gesichert.
5-2
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
5.2.
Erstellen von Abfragen mit SQL
5.2.1. Struktur der DB
Damit mittels SQL Daten aus einer Datenbank abgefragt werden können, müssen
alle Tabellen sowie deren Attribute und Beziehungen bekannt sein. Es ist also
wichtig, dass die Darstellung des ERD bekannt ist und auch interpretiert werden
kann.
Abbildung 55 :
Beispiel eines ERD in Access
5.2.2. Grundkonzept von SQL-SELECT
SQL wurde so konzipiert, dass auch „nicht Informatiker“ die Sprache für den
Umgang mit Datenbanken nutzen können. Man hat daher eine Syntax gewählt, die
sich stark an der Umgangssprache orientiert.
Beispiel :
Zeige mir alle Hobbys von Edit Hasler, die sie erst seit dem Jahr 2000 ausübt.
Dieser Satz muss für die Anwendung mittels SQL noch ein wenig strukturiert
werden. So müssen
alle Spalten genannt werden, von denen Informationen gelesen werden
alle beteiligten Tabellen genannt werden
Verknüpfungen der Tabellen angegeben werden (über die Schlüssel-Paare)
Beispiel :
Zeige mir Name und Vorname sowie alle Hobbys der Tabellen Hobby, Person
und PersonHobby, bei denen der Name „Hasler“ und der Vorname „Edith“
lautet und bei denen das Datum grösser als 2000 ist.
In SQL wird das wie folgt angeschrieben:
SELECT Person.Name, Person.Vorname, Hobby.Bezeichnung
FROM Person, PersonHobby, Hobby
WHERE Person.Name = 'Hasler' AND Person.Vorname = 'Edith' AND
PersonHobby.seit > 2000 AND
Person.PID = PersonHobby.Person AND
Hobby.HID = PersonHobby.Hobby
ORDER BY Person.Name ASC
Dieses Beispiel zeigt den grundlegenden Aufbau des SELECT-Befehls. Er besteht
aus
Befehl
SELECT
Attributliste
Tabelle.Attribut oder * (für alle Attribute)
Quelle
FROM Tabelle
Bedingung
WHERE ............................
GROUP BY / ORDER BY
© René Probst
Jan 2004
5-3
Modul-100
Theorie
5.2.3. Alle Zeilen einer Tabelle abfragen
Natürlich wollen wir als erstes nicht gleich eine so komplexe Abfrage betrachten. Wir
lesen zuerst einmal alle bzw. definierte Spalten einer Tabelle aus.
Beispiel :
Gesucht sind alle Personen die in der DB gespeichert sind
SELECT * FROM Person;
Abbildung 56 :
Beispiel :
SELECT : alle Spalten
Gesucht sind Name und Vorname aller Personen
SELECT Person.Name, Person.PLZ FROM Person;
Abbildung 57 :
SELECT : benannte Spalten
Der „vollqualifizierte Name“ einer Spalte setzt sich aus
Tabellenname.Attributname
zusammen und garantiert, dass bei komplexen Abfragen keine Verwechslung bei
gleich benannten Spalten auftreten können.
5.2.4. Sortieren von Daten
Daten lassen sich einfacher auswerten, wenn sie sortiert sind. Dabei können die
Werte jeder beliebigen Spalte in absteigender (DESC15) oder aufsteigender (ASC16)
Form ausgegeben werden. Zudem ist es auch möglich, mehrere Sortierebenen zu
definieren.
Beispiel :
Zeige alle Personen aufsteigend sortiert nach Name und absteigend sortiert
nach Vorname
SELECT * FROM Person
ORDER BY Person.Name ASC, Person.Vorname DESC;
ASC
DESC
Abbildung 58 :
15
16
5-4
SELECT : sortiert nach Spalten (auf- und absteigend)
descending
ascending
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
5.2.5. Kombinieren von Daten aus mehreren Tabellen
Wir haben im bisherigen Verlauf bereits gesehen, dass bei einer Relationale
Datenbank die Daten meist auf mehrere – über Schlüsselwerte verknüpfte –
Tabellen verteilt abgespeichert sind. Wenn also mehrere Tabellen in einer Selektion
verwendet werden, so muss der Datenbank mitgeteilt werden, welche Kriterien für
die Verknüpfung massgebend sind.
Beispiel :
Zeige alle Personen sowie deren Hobbys aufsteigend nach Namen sortiert.
SELECT Person.Name, Person.Vorname,
Hobby.Bezeichnung
FROM Person, PersonHobby, Hobby
WHERE Person.PID = PersonHobby.Person AND
Hobby.HID = PersonHobby.Hobby
ORDER BY Person.Name;
Abbildung 59 :
SELECT über mehrer verknüpfte Tabellen
Für die Veknüpfungsbeziehung zwischen den Tabellen werden die Schlüsselpaare
einander gleichgesetzt. Dabei sind immer Anzahl Tabellen minus 1 Bedingungen
nötig.
Abbildung 60 :
© René Probst
Verknüpfung über Schlüsselwert-Paare
Jan 2004
5-5
Theorie
Modul-100
5.2.6. Verdichten von Daten
In manchen Fällen ergeben sich beim Verknüpfen von mehreren Tabellen
Datensätze mit identischen Werten. Wenn aber für die Auswertung lediglich die
Wertekombinationen wichtig sind, aber nicht die Anzahl des Auftretens, so kann
über den Zusatz DISTINCT beim SELECT-Befehl eine Verdichtung der Daten erzielt
werden, in dem die Mehrfachausgabe inhaltlich gleicher Datensätze unterdrückt
wird.
Beispiel :
Zeige alle Personen sowie die Art der Hobbys die sie ausüben.
SELECT DISTINCT Person.Name, Person.Vorname, Art.Art
FROM Person, PersonHobby, Hobby, Art
WHERE Person.PID = PersonHobby.Person AND
Hobby.HID = PersonHobby.Hobby AND
Art.HAID = Hobby.Art
ORDER BY Person.Name;
Abbildung 61 :
5-6
SELECT ohne (links) und mit (rechts) DISTINCT
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
5.3.
Filtern von Daten
5.3.1. Die WEHRE Klausel
Eine Tabelle kann bezüglich der Werte ihrer Attribute gefiltert werden. D.h. dass nur
die Daten angezeigt werden, deren Wert dem Suchbegriff entsprechen.
Die Werte lassen sich dabei mit den bekannten Operatoren vergleichen.
Operator Bedeutung
=
Gleich
<
Kleiner als
>
Grösser als
<>
Ungleich
<=
Kleiner gleich
>=
Grösser gleich
Die Bedingung wird mit der WHERE-Klausel eröffnet
Beispiel :
Gesucht sind alle Personen deren Nachname mit A, B ...G beginnt.
SELECT * FROM Person
WHERE Person.Name < 'H';
Abbildung 62 :
Beispiel :
Gesucht sind alle Personen die im Dorf mit der Postleitzahl 9991 wohnen.
SELECT * FROM Person
WHERE Person.PLZ = 9991;
Abbildung 63 :
Beispiel :
Filter auf Zahlen
Gesucht sind alle Personen, die Meier heissen
SELECT * FROM Person
WHERE Person.Name = 'Meier';
Abbildung 64 :
© René Probst
Filter auf Zeichen
Filter auf Zeichenkette
Jan 2004
5-7
Modul-100
Theorie
Beispiel :
Zeige Name und Vorname der Personen, die ein Hobby erst seit dem Jahr
2000 betreiben.
SELECT DISTINCT Person.Name, Person.Vorname
FROM Person, PersonHobby
WHERE Person.PID = PersonHobby.Person AND
PersonHobby.seit >= #2000-01-01#;
Abbildung 65 :
Filter auf ein Datum
Die Angabe von Daten ist in den unterschiedlichen SQL-Implementierungen
verschieden. So wird bei Acces das Datum wie gezeigt mit #Jahr-Monat-Tag#
angeschrieben, während bei DB2 von IBM ’Tag.Monat.Jahr’ in der Form
’01.12.1985’ und bei Oracle mit ’15-feb-1975’ angegeben werden.
5.3.2. Der LIKE Operator
Der Versuch alle Personen die mit dem Buchstaben M beginnen über den Vergleich
Person.Name = ’M’
zu erhalten wird scheitern.
Um nach einem Muster zu suchen wird statt der mathematischen Operatoren der
LIKE-Operator verwendet.
Beispiel :
Zeige alle Personen, deren Name mit dem Buchstaben M beginnt.
SELECT Person.Name, Person.Vorname FROM Person
WHERE Person.Name LIKE 'M%';
Abbildung 66 :
Filter mit dem LIKE-Operator
Um nach Muster zu suchen können die Platzhalter % (für beliebige Zeichen) und _
(für genau ein Zeichen) verwendet werden.
5-8
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
5.3.3. Filter auf mehrere Attribute
Mittels logischer Operationen (UND, ODER, NICHT) lassen sich beliebig komplexe
Abfragen gestalten.
UND-Verknüpfung
Es werden zwei oder mehr Bedingungen genant, die alle erfüllt sein müssen.
Beispiel :
Zeige alle Personen, die an der Hauptstrasse in Vorstadt wohnen.
SELECT * FROM Person
WHERE Person.Adresse LIKE 'Haupt%' AND
Person.Ort = 'Vorstadt';
Abbildung 67 :
Filter auf UND-Verknüpfte Attribute
ODER-Verknüpfung
Es werden zwei oder mehr Bedingungen genannt, von denen eine zutreffen muss.
Beispiel :
Zeige alle Personen, die an einer Strasse die mit Haupt- bzw. Dorf- beginnt
wohnen.
SELECT * FROM Person
WHERE Person.Adresse LIKE 'Haupt%' OR
Person.Adresse LIKE 'Dorf%';
Abbildung 68 :
Filter auf ODER-Verknüpfte Attribute
NICHT-Verknüpfung
Die Bedeutung der Aussage wird durch den NOT-Operator invertiert. Dabei ist es
wichtig, dass durch Klammerung der Teil der Aussage gekapselt wird, der nicht
zutreffen darf.
Beispiel :
Zeige alle Personen, die nicht an der Hauptstrasse in Vorstadt wohnen
SELECT * FROM Person
WHERE Person.Adresse NOT LIKE 'Haupt%' AND
Person.Ort = 'Vorstadt';
Abbildung 69 :
© René Probst
Filter mit NICHT-Operator
Jan 2004
5-9
Theorie
Modul-100
Operationsreihenfolge
Wie in der Mathematik die Regel Punkt-vor-Strich gilt, wird bei logischen
Operationen immer zuerst die UND-Funktion ausgewertet und erst dann die ODERFunktion.
Es ist daher wichtig, dass durch korrekte Klammerung die gewünschte Aussage
erzielt wird.
Beispiel :
Zeige alle Personen aus Winterdorf, die an einer Strasse die mit Haupt- bzw.
Dorf- beginnt wohnen.
SELECT * FROM Person
WHERE Person.Adresse LIKE 'Haupt%' OR
Person.Adresse LIKE 'Dorf%' AND
Person.Ort = 'Winterdorf';
Abbildung 70 :
UND/ODER-Verknüpfung ohne Klammerung
Offensichtlich ist das erzielte Ergebnis nicht korrekt, da nebst Winterdorf auch
weitere Ortschaften ausgegeben werden.
In diesem Fall wird die UND-Verknüpfung nur auf die ODER-Verknüpfung
Person.Adresse LIKE ’Dorf%’
angewendet, was sichtlich falsch ist.
Beispiel :
Korrekte Formulierung des SQL-Befehls
SELECT * FROM Person
WHERE( Person.Adresse LIKE 'Haupt%' OR
Person.Adresse LIKE 'Dorf%') AND
Person.Ort = 'Winterdorf';
Abbildung 71 :
5-10
UND/ODER-Verknüpfung mit Klammerung
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
5.3.4. Bereichsprüfungen
Auswahl
Wenn aus einer bestimmten Menge von Werten einige zutreffen sollen, so kann dies
durch eine Auflistung mit ODER-Operationen erfolgen. Einfacher und sicherer ist
aber der Einsatz des IN-Befehls.
Beispiel :
Zeige alle Personen die in Winterdorf bzw. Sportdorf wohnen
SELECT * FROM Person
WHERE Ort IN ('Winterdorf', 'Sportdorf');
Abbildung 72 :
IN-Befehl
Bereich
Anstelle eines Grösser-Kleiner-Vergleichs mit UND-Operation kann der BETWEENBefehl verwendet werden.
Beispiel :
Zeige alle Personen, mit Postleitzahlen zwischen 7000 und 9000.
SELECT * FROM Person
WHERE PLZ BETWEEN 6999 AND 9000;
Abbildung 73 :
© René Probst
BETWEEN-Befehl
Jan 2004
5-11
Theorie
5.4.
Modul-100
Schnitt- und Vereinigungsmenge zweier Tabellen
Union
Wenn es darum geht, aus einer Menge von unterschiedlichen Zuordnungen die
Vereinigungsmenge zu bilden, kann der UNION-Befehl verwendet werden.
Er verknüpft zwei Abfragen zu einer gemeinsamen Menge
Beispiel :
Aus zwei Tabellen „Fussball“ und „Handball“ sollen alle erfassten Personen in
einer gemeinsamen Auflistung erscheinen.
SELECT Name FROM Fussball
UNION
SELECT Name FROM Handball;
Die Union Operation zeigt – ähnlich dem DISTINCT-Befehl – identische Einträge nur
einmal an. Sollen trotzdem alle Datensätze gezeigt werden –also auch redundante
Einträge – wird der Befehl UNION ALL verwendet.
Fussball
Abbildung 74 :
Handball
Vereinigungsmenge
Intersect
Oft interessiert aber gerade, welche Datensätze in zwei Tabellen enthalten sind.
Hier geht es also um die Schnittmenge. Sie kann durch den INTERSECT-Befehl
erzeugt werden.
Beispiel :
Aus den beiden Tabellen „Handball“ und „Fussball“ sollen alle jene Personen
aufgelistet werden, die in beiden Tabellen erfasst sind.
SELECT Name FROM Fussball
INTERSECT
SELECT Name FROM Handball;
Fussball
Abbildung 75 :
5-12
Handball
Schnittmenge
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
Minus
Soll nun die Menge aller Datensätze gezeigt werden, die entweder in der einen oder
der andern Tabelle – aber eben nicht in beiden- zufinden sind, wird der MINUSBefehl verwendet.
Beispiel :
Zeige alle Personen die nur in der Tabelle Fussball aber nicht in der Tabelle
Handball stehen.
SELECT Name FROM Fussball
MINUS
SELECT Name FROM Handball;
Fussball
Abbildung 76 :
© René Probst
Handball
Differenzmenge
Jan 2004
5-13
Modul-100
Theorie
5.5.
Aggregierende Funktionen
5.5.1. GROUP BY-Klausel
Wenn die Daten nicht mehr in ihrer ursprünglichen tabellarischen Form sondern
durch (mathematische) Operationen verdichtet sind, spricht man bei RDB’s von
Aggregierung.
Damit Aggregats-Funktionen eingesetzt werden können, müssen bestimmte
Datenwert mehrfach (redundant) in der Abfrage vorkommen. Dabei ist es wichtig,
dass das Attribut bzw. die Attribute nach denen gruppiert wird in der GROUP BY
Klausel aufgelistet werden.
Regel :
Alle Attribute die in der SELECT Anweisung genannt sind und keine AggregatsFunktion zugewiesen haben, müssen in der GROUP BY-Klausel aufgelistet werden.
5.5.2. COUNT-Funktion
Werden zu einem Attributswert mehrere andere Werte aufgelistet (wie das z.B. bei
Person und Hobby der Fall ist), so kann nach der Anzahl der Nennungen gefragt
werden.
Beispiel :
Zeige die Anzahl der Hobbys jeder Person
SELECT Person.PID, Person.Name, Person.Vorname,
Count(PersonHobby.Hobby) AS Hobbyanzahl
FROM Person, PersonHobby
WHERE Person.PID=PersonHobby.Person
GROUP BY Person.PID, Person.Name, Person.Vorname
ORDER BY Person.Name;
Abbildung 77 :
COUNT-Funktion
Die AS-Klausel
Wird durch eine Aggregation ein neues Ergebnis erzielt, kann mit der AS-Klausel die
Beschriftung der Spalte festgelegt werden.
5-14
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
Schlüsselattribute
Damit bei der Gruppierung keine Fehler entstehen, ist es wichtig, dass immer auch
das Schlüsselattribut der Tabelle eingefügt wird, nach der gruppiert wird. In obigem
Beispiel würden sonst die beiden “Meier“ als eine Person mit 5 Hobbies
ausgegeben!
5.5.3. Summierung von Zahlenwerten
Nebst der Anzahl zutreffender Beziehungen ist die Summenbildung eine wichtige
Aggregatsfunktion. Nur so lässt sich z.B. mittels einer DB eine automatisierte
Rechnungsstellung realisieren.
Wichtig dabei ist, dass sich nebst der Spalte mit den zu addierenden Werten keine
weiteren abweichenden Daten in der Abfrage wiederfinden.
Beispiel :
Wieviel Aufwand betreibt jede Person für all ihre Hobbys?
SELECT Person.PID, Person.Name, Person.Vorname,
SUM(PersonHobby.Aufwand) AS Aufwand
FROM Person, PersonHobby
WHERE Person.PID = PersonHobby.Person
GROUP BY Person.PID, Person.Name, Person.Vorname
ORDER BY Person.Name;
Abbildung 78 :
SUM-Funktion
5.5.4. AVG-Funktion
Oft ist es interessant zu wissen, was ein bestimmtes Attribut im Mittel (Durchschnitt)
ergibt.
Beispiel :
Wieviele Stunden werden im Durchschnitt für das Hobby im Jahr
aufgewendet?
SELECT AVG(Aufwand) AS 'Aufwand im Durchschnitt'
FROM PersonHobby;
Abbildung 79 :
© René Probst
AVG-Funktion
Jan 2004
5-15
Theorie
Modul-100
5.5.5. MIN/MAX-Funktion
Nebst dem Durchschnitt ist auch das Minimum und das Maximum oft von Interesse,
weil sich dadurch gewisse Aussagen über die Streuung von Datenwerte bezüglich
dem Durchschnitt machen lassen.
Beispiel :
Wieviel Zeit wird im Minimum, im Maximum und im Durchschnitt für das
Hobby pro Jahr aufgewendet?
SELECT MIN(PersonHobby.Aufwand) AS Minimum,
AVG(PersonHobby.Aufwand) AS Durchschnitt,
MAX(PersonHobby.Aufwand) AS Maximum
FROM PersonHobby;
Abbildung 80 :
5-16
MIN- und MAX-Funktion
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
5.5.6. Ein Beispiel
Ein Lehrer führt alle seine Prüfungsdaten in einer Datenbank. Die Struktur sieht wie
folgt aus:
Abbildung 81 :
Datenbank „Noten“
Es sollen folgende Aussagen abgerufen werden:
1.
Minimum, Maximum und Durchschnitt einer Prüfung
2.
Minimum, Maximum und Durchschnitt eines Fachs
3.
Minimum, Maximum und Durchschnitt eines Fachs eines Schülers
4.
Minimum, Maximum und Durchschnitt aller Noten eines Schülers
Beispiel :
Es ist die Auswertung der ersten Mathe Prüfung vom 26.8.2003 zu erstellen!
SELECT MIN(Noten.Note) AS Min, AVG(Noten.Note) AS Avg,
MAX(Noten.Note) AS Max
FROM Noten, Test
WHERE Noten.Datum = Test.TID AND
Test.Datum = #2003-08-26#;
Abbildung 82 :
Beispiel :
Es ist die Auswertung für das Fach Französisch zu erstellen!
SELECT MIN(Noten.Note) AS Min, AVG(Noten.Note) AS
Schnitt,
Max(Noten.Note) AS Max
FROM Noten, Fach
WHERE Noten.Fach = Fach.FID AND
Fach.Bezeichnung = 'Franz';
Abbildung 83 :
© René Probst
Daten der Prüfung vom 26.8.2003
Daten des Fachs „Französisch“
Jan 2004
5-17
Modul-100
Theorie
Beispiel :
Es sind die Mathe-Noten für Bea Auf der Mauer auszuwerten!
SELECT Schueler.Name, Schueler.Vorname,
MIN(Noten.Note) AS Min, AVG(Noten.Note) AS
Schnitt,
Max(Noten.Note) AS Max
FROM Noten, Fach, Schueler
WHERE Noten.Fach = Fach.FID AND
Noten.Schuler = Schueler.PID AND
Fach.Bezeichnung = 'Mathe' AND
Schueler.Name LIKE "auf*"
GROUP BY Schueler.Name, Schueler.Vorname
Abbildung 84 :
Beispiel :
Es ist eine Statistik aller Fächer für Tomy Schneider zu erstellen
SELECT Schueler.Name, Schueler.Vorname, Fach.Bezeichnung,
MIN(Noten.Note) AS Min, AVG(Noten.Note) AS
Schnitt,
Max(Noten.Note) AS Max
FROM Noten, Fach, Schueler
WHERE Noten.Fach = Fach.FID AND
Noten.Schuler = Schueler.PID AND
Schueler.Name LIKE "schnei*"
GROUP BY Schueler.Name, Schueler.Vorname,
Fach.Bezeichnung
Abbildung 85 :
5-18
Daten von Bea Auf der Mauer in Mathe
Daten von Tomy Schneider
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
5.5.7. HAVING-Klausel
Wir haben zu beginn dieses Abschnitts gelernt, dass aggregierende Funktionen wie
COUNT,SUM usw. die GROUP BY-Klausel erfordern.
Wenn eine Aggregation aber auch noch ein Bedingung erfüllen muss, so genügt die
WHERE-Klausel nicht mehr. Für solche Fälle muss nach der GROUP BY-Klausel
die HAVING-Klausel folgen.
Beispiel :
Es sollen alle Personen aufgelistet werden, die für ihre Hobbys mehr als 250
Stunden im Jahr aufwenden.
SELECT Person.PID, Person.Name, Person.Vorname,
SUM(PersonHobby.Aufwand) AS Aufwand
FROM Person, PersonHobby
WHERE Person.PID = PersonHobby.Person
GROUP BY Person.PID, Person.Name, Person.Vorname
HAVING SUM(PersonHobby.Aufwand) > 250
ORDER BY Person.Name;
Abbildung 86 :
HAVING-Klausel
Die HAVING-Klausel kann auch eine komplexe Abfrage mit AND, OR, BETWEEN,
IN usw. enthalten.
Beispiel :
Zeige alle Personen, die jährlich zwischen 250 und 400 Stunden für ihre
Hobbys aufwenden.
SELECT.....
.....
HAVING SUM(PersonHobby.Aufwand) BETWEEN 250 AND 400
....
Abbildung 87 :
© René Probst
HAVING-Klausel mit komplexem Vergleich
Jan 2004
5-19
Theorie
5.6.
Modul-100
Bedeutung der Verknüpfung
Wir haben bereits im vorher gehende Kapitel die Bedeutung von Inner- und OuterJoin behandelt und gehen daher hier nicht mehr auf deren Bedeutung ein, sondern
schauen uns nur noch die Umsetzung in SQL an.
Sowohl Inner- als auch Outer-Join sind nicht Bestandteil des ANSI-Standards. Sie
sind wohl in irgendeiner Weise in den meisten Datenbanken implementiert, die
Schreibweise zwischen den Produkten variiert aber!
5.6.1. Inner-Join
Der Inner-Join verbindet zwei oder mehr Tabellen über die Schlüssel-Paare, wobei
auf die WHERE-Klausel - wie weiter oben gezeigt – verzichtet werden kann.
Beispiel :
Abfrage aller Personen die ein Beziehung zu Hobby haben, wobei über die
Entität ’PersonHobby’ ein Schlüssel-Fremdschlüssel Paar geprüft wird.
SELECT Person.PID, Person.Name, Person.Vorname
FROM Person
INNER JOIN PersonHobby ON Person.PID = PersonHobby.Person
GROUP BY Person.PID, Person.Name, Person.Vorname;
Abbildung 88 :
Resultat der Abfrage mit Inner-Join
Die Tabelle Person weist wohl 20 Einträge auf. Da aber offenbar eine Person keine
Hobbys hat, werden beim Inner-Join nur die 19 Datensätze angezeigt, die
mindestens einen korrespondierenden Eintrag in der Tabelle PersonHobby haben.
5-20
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
Bei mehr als zwei Tabellen wird die JOIN-Klausel durch Klammern geordnet als
„Kette“ von Verknüpfungen angeschrieben.
Beispiel :
Zeige die Hobbys aller Personen.
SELECT Person.PID, Person.Name, Person.Vorname,
Hobby.Bezeichnung
FROM Hobby
INNER JOIN (Person INNER JOIN PersonHobby
ON Person.PID = PersonHobby.Person)
ON Hobby.HID = PersonHobby.Hobby
GROUP BY Person.PID........
5.6.2. Outer-Join
Beim Outer-Join wird unterschieden, ob aus der erst bzw. zweit genannte Tabelle
alle Datensätze angezeigt werden sollen. Bei Access unterscheidet man daher den
• LEFT JOIN
• RIGHT JOIN
Beispiel :
Zeige alle Personen sowie den allfälligen Aufwand, den sie jährlich für ihre
Hobbys aufwenden.
SELECT Person.PID, Person.Name, Person.Vorname,
Sum(PersonHobby.Aufwand) AS [Summe von Aufwand]
FROM Person
LEFT JOIN PersonHobby ON Person.PID = PersonHobby.Person
GROUP BY Person.PID, Person.Name, Person.Vorname;
Abbildung 89 :
Outer-Join auf Tabelle Person
Das Ergebnis zeigt wie erwartet 20 Personen an, wobei für Grainer Hendrik kein
Eintrag in der Tabelle PersonHobby existiert und somit auch kein Aufwand
berechnet werden kann.
© René Probst
Jan 2004
5-21
Theorie
Modul-100
5.6.3. Outer-Join bei m:m-Beziehungen
Bei QBE haben Sie gelernt, dass die gewünschte Abfrage in zwei Schritten zu
realisieren ist. So ähnlich verhält es sich auch bei SQL, nur dass hier die eine
SELECT-Anweisung als Unterabfrage in einer andern SELECT-Anweisung stehen
kann.
Unterabfragen werden aber hier nicht weiter behandelt!
Beispiel :
Es sollen alle Personen und auch deren Hobbys gezeigt werden.
Dazu müssen die Tabellen ’Hobby’ und ’PersonHobby’ in einer Unterabfrage
verknüpft werden, so dass die Spalten
- Hobby.Bezeichnung und
- HobbyPerson.Person
verfügbar sind. .
SELECT Person.PID, Person.Name, Person.Vorname, T.Hobby
FROM Person
LEFT JOIN (
SELECT Bezeichnung AS Hobby, Person AS ID
FROM Hobby
INNER JOIN PersonHobby ON Hobby.HID = PersonHobby.Hobby
GROUP BY Hobby.Bezeichnung, PersonHobby.Person) T
ON Person.PID = T.ID
Damit die Spalten der Unterabfrage selektiert werden können, muss diese einen
Namen erhalten, in diesem Fall T. Über diesen Namen werden dann die selektierten
Spalten angesprochen, wobei auch diese einen Alias-Namen erhalten (über die ASKlausel)
Abbildung 90 :
5-22
Outer-Join mit Unterabfrage bei m:m-Beziehung
Modul 100 V1.2b.doc
Abteilung Informatik/Technik
Informationsbestände analysieren
Datenabfrage mit SQL
Kapitel 5 : Eigene Notizen
© René Probst
Jan 2004
5-23
Theorie
5-24
Modul 100 V1.2b.doc
Modul-100
Abteilung Informatik/Technik