SQL Abfragen und QBE Unterrichtsskript

Wirtschaftsinformatik
SQL Abfragen und QBE
Unterrichtsskript
Version:
Autor:
Datum:
2.0
Jonas Lanz
02.06.2006
FHNW⏐Hochschule für Wirtschaft
2 / 29
Inhaltsverzeichnis
1. Einführung
3
1.1 Relationale Operationen
4
1.1.1 Selektion
4
1.1.2 Projektion
4
1.1.3 Verbund
4
2. Datenabfrage (Query)
5
2.1 Einfache Abfragen
6
2.1.1 Duplikate ausblenden
7
2.1.2 Berechnungen
8
2.1.3 Aggregatsfunktionen
8
2.2 Abfragen mit Bedingungen
10
2.2.1 Operatoren
10
2.3 Datensätze sortieren
14
2.4 Datensätze gruppieren
16
2.4.1 Bedingungen für Gruppierungen
17
2.5 Unterabfragen
19
2.6 Tabellen verknüpfen (Joining)
22
2.6.1 Verknüpfungseigenschaften
24
2.7 Kombinationen
26
2.8 Parameterabfragen
27
Anhang
29
Tabellen mit Daten
29
SQL Abfragen und QBE
Datum: 02.06.2006
3 / 29
FHNW⏐Hochschule für Wirtschaft
1. Einführung
In diesem Kapitel werden die Grundlagen der Datenbanksprache "SQL" (Structured
Query Language) für Datenabfragen vermittelt. Diese Sprache wird von vielen
Datenbanksystemen (z.B. ORACLE, DBase IV, Microsoft Access usw.) unterstützt. Sie
ermöglicht dem Benutzer das Kommunizieren mit dem eigentlichen Datenbanksystem.
SQL wurde vom American National Standard Institut (ANSI) als Standardsprache für
relationale Datenbanken erklärt. Allerdings haben viele Datenbankhersteller den
Sprachumfang für ihre eigenen Systeme erweitert, wodurch die Kompatibilität mit anderen
Datenbanksystemen nicht vollständig gewährleistet ist.
Der Befehlssatz von SQL lässt sich folgende drei Gruppen unterteilen:
ƒ
Datendefinitionsbefehle: Datenbeschreibung DDL (Data Definition Language)
ƒ
Datenmanipulationsbefehle Datenmanipulationssprache DML (Data Manipulation
Language).
ƒ
Datenabfragebefehle: Query
Vorliegendes Skript behandelt nur die Datenabfrage. Alle Beispiele beziehen sich auf die
Tabellen im Anhang zum Thema "Projektarbeit"1.
Die Befehle basieren auf dem von MS Access verwendeten Befehlssatz von SQL.
Darüber hinaus bietet MS Access - wie viele andere Produkte auch - eine grafische
Oberfläche zur Erstellung von Datenabfragen an. Diese wird als QBE (Query By Example)
bezeichnet. Bei den Beispielen sind jeweils beide Varianten gegenüber gestellt.
1
In Anlehnung an: Abts/Mülder 2000, Aufbaukurs Wirtschaftsinformatik. Braunschweig/Wiesbaden:
vieweg
SQL Abfragen und QBE
Datum: 02.06.2006
4 / 29
FHNW⏐Hochschule für Wirtschaft
1.1 Relationale Operationen
Im Zusammenhang mit der Datenabfrage sind für Tabellen spezifische Operationen
definiert, die als Resultat wiederum Tabellen liefern. Diese relationalen Operationen
werden durch entsprechende SQL-Anweisungen umgesetzt.
1.1.1 Selektion
Der Selektion entspricht die Ausgabe aller Datensätze einer Tabelle, die bestimmten
Kriterien entsprechen.
Beispiel: Alle Mitarbeiter mit Ausbildung Informatiker.
MITARBEITER
PersNr
00100010
00100020
00100030
00100040
00100050
00100060
00100070
00100080
00100090
AbtNr
S001
S002
S001
S002
S002
R001
A001
S003
V001
Name
Maier
Schulz
Müller
Neumann
Moser
Kunze
Meyer
Peters
Otto
Vorname
Hugo
Franz
Wolfgang
Peter
Heinz
Otto
Emil
Heinz
Karl Heinz
Ausbildung
Informatiker
DVK
Systemplaner
Programmierer
Programmierer
Informatiker
Systemplaner
Systemplaner
Informatiker
Geburtsdatum
01.01.1960
05.03.1959
22.05.1952
13.09.1962
01.01.1970
02.03.1955
22.07.1965
01.07.1950
13.12.1949
Stundensatz
100.00
80.00
100.00
80.00
85.00
100.00
100.00
100.00
110.00
1.1.2 Projektion
Durch die Projektion werden bestimmte Attribute einer Tabelle ausgewählt.
Beispiel: PersNr, Name und Vorname aller Mitarbeiter.
MITARBEITER
PersNr
00100010
00100020
00100030
00100040
00100050
00100060
00100070
00100080
00100090
AbtNr
S001
S002
S001
S002
S002
R001
A001
S003
V001
Name
Maier
Schulz
Müller
Neumann
Moser
Kunze
Meyer
Peters
Otto
Vorname
Hugo
Franz
Wolfgang
Peter
Heinz
Otto
Emil
Heinz
Karl Heinz
Ausbildung
Informatiker
DVK
Systemplaner
Programmierer
Programmierer
Informatiker
Systemplaner
Systemplaner
Informatiker
Geburtsdatum
01.01.1960
05.03.1959
22.05.1952
13.09.1962
01.01.1970
02.03.1955
22.07.1965
01.07.1950
13.12.1949
Stundensatz
100.00
80.00
100.00
80.00
85.00
100.00
100.00
100.00
110.00
1.1.3 Verbund
Der Verbund (Join) verknüpft zwei Tabellen miteinander.
Beispiel: Vgl. Kapitel "Tabellen verknüpfen (Joning)"
SQL Abfragen und QBE
Datum: 02.06.2006
5 / 29
FHNW⏐Hochschule für Wirtschaft
2. Datenabfrage (Query)
Die grundlegende Syntax eines SQL-Abfragebefehls sieht folgendermassen aus: 2
SELECT [DISTINCT] {*|Attributliste|mathematische Ausdrücke} [Bezeichner]
FROM Tabellenliste [Bezeichner]
[WHERE Bedingungen]
[GROUP BY Attributliste] [HAVING Bedingungen]
[ORDER BY Attributliste] [ASC|DESC];
ƒ
Mit SELECT kann angegeben werden, welche Attribute angezeigt werden sollen und
wie diese allenfalls in Berechnungen und Funktionen einzusetzen sind. Falls
DISTINCT verwendet wird, werden mehrfach auftretende, identische Datensätze nur
einmal angezeigt.
ƒ
Mit FROM wird angegeben, aus welchen Tabellen Datensätze abgefragt bzw. zu
neuen Datensätzen kombiniert werden.
ƒ
Mit WHERE wird angegeben, welche Bedingungen ein Datensatz erfüllen muss, damit
er weiterverarbeitet wird.
ƒ
Mit GROUP BY können Datensätze zu Gruppen zusammengefasst und mit speziellen
Gruppenfunktionen weiterverarbeitet werden.
ƒ
HAVING gibt an, welche Bedingungen eine Gruppe aus Datensätzen erfüllen muss,
damit sie weiterverwendet wird.
ƒ
Mit ORDER BY können die resultierenden Datensätze vor der Ausgabe nach
bestimmten Attributen aufsteigend (ASC für "ascending") bzw. absteigend (DESC für
"descending") sortiert werden.
2
Ausdrücke, welche in eckigen Klammern ("[", "]") stehen sind optional, d.h. sie können auch
weggelassen werden. Bei Ausdrücken, welche in geschweiften Klammern ("{", "}") stehen und
durch einen senkrechten Strich ("|") verbunden sind, kann nur einer dieser Ausdrücke auf einmal
verwendet werden.
SQL Abfragen und QBE
Datum: 02.06.2006
FHNW⏐Hochschule für Wirtschaft
6 / 29
2.1 Einfache Abfragen
Die einfachste Datenabfrage mit SQL ist nachstehend aufgeführt. Damit werden alle
Datensätze einer Tabelle mit den entsprechenden Attributnamen aufgelistet. In diesem
Fall alle Datensätze der Tabelle ABTEILUNG. Die Punktschreibweise ("ABTEILUNG.")
gibt explizit an, dass es sich um die Tabelle ABTEILUNG handelt. Diese Schreibweise
dient der Unterscheidung von gleichnamigen Attributen in unterschiedlichen Tabellen und
ist in Fällen, wo diese Unterscheidung nicht vorgenommen werden muss, nicht zwingend
notwendig. Im Sinne einer einheitlichen Schreibweise sollte sie jedoch konsequent
angewandt werden.
Beispiel 1
SELECT ABTEILUNG.*
FROM ABTEILUNG;
AbtNr
AbtName
Kostenstelle
A001
Organisation
04711
R001
Rechnerbetrieb
05000
S001
Systemplanung
03030
S002
Anwendungsentwicklung 03020
S003
Benutzerservice
03030
V001
Vertrieb
08010
Im nächsten Beispiel werden von allen Abteilungen "AbtNr" und "AbtName" angezeigt.
Beispiel 2
SELECT ABTEILUNG.AbtNr, ABTEILUNG.AbtName
FROM ABTEILUNG;
SQL Abfragen und QBE
Datum: 02.06.2006
FHNW⏐Hochschule für Wirtschaft
AbtNr
7 / 29
AbtName
A001
Organisation
R001
Rechnerbetrieb
S001
Systemplanung
S002
Anwendungsentwicklung
S003
Benutzerservice
V001
Vertrieb
2.1.1 Duplikate ausblenden
Es gibt Fälle, in denen gleiche Werte mehrmals vorkommen. Beispielsweise kommen in
der Tabelle MITARBEITER dieselben AbtNr mehrmals vor. Wenn wir nun wissen
möchten, welche verschiedenen AbtNr vorkommen, muss der Befehl DISTINCT
verwendet werden:
Beispiel 3
SELECT DISTINCT MITARBEITER.AbtNr
FROM MITARBEITER;
AbtNr
A001
R001
S001
S002
S003
V001
SQL Abfragen und QBE
Datum: 02.06.2006
8 / 29
FHNW⏐Hochschule für Wirtschaft
2.1.2 Berechnungen
Es können auch Berechnungen durchgeführt werden. Mit folgender Abfrage wird das
Budget inkl. MWSt zu einem Satz von 7.6% basierend auf dem (Netto-) Budget
berechnet.
Beispiel 4
SELECT PROJEKT.ProjNr, PROJEKT.Budget, Budget*1.076 AS Budget_inkl_MWSt
FROM PROJEKT;
ProjNr
Budget
Budget_inkl_MWSt
4711
100'000.00
107600
4712
80'000.00
86080
5000
60'000.00
64560
An diesem Beispiel ist ersichtlich, dass man jedem Attribut für die Ausgabe einen eigenen
Namen geben kann ("Budget_inkl_MWSt" statt dem Formelausdruck). Hätten wir auf
diesen Ausdruck im SELECT-Teil verzichtet, so wäre die ganze Formel oder alternativ ein
systeminterner Ausdruck als Spaltenname angezeigt worden.
2.1.3 Aggregatsfunktionen
Für Berechnungen, insbesondere statistische Berechnungen, gibt es diverse sog.
Aggregatsfunktionen. Die wichtigsten sind folgende:
Funktion
Name
Beschreibung
COUNT()
Anzahl
Berechnet die Anzahl der von einer Abfrage zurückgegebenen
Datensätze.
AVG()
Mittelwert
Berechnet den arithmetischen Mittelwert einer Menge von Werten.
MIN(), MAX()
Minimum
Geben den kleinsten bzw. grössten Wert aus einer Menge von Werten
Maximum
zurück.
Summe
Gibt die Summe einer Menge von Werten zurück.
SUM()
SQL Abfragen und QBE
Datum: 02.06.2006
9 / 29
FHNW⏐Hochschule für Wirtschaft
Mit der folgenden Abfrage erhält man die Anzahl Projekte, das höchste Budget, das
niedrigste Budget und das Durchschnittsbudget.
Beispiel 5
SELECT Count(PROJEKT.ProjNr) AS AnzahlvonPersNr, MAX(PROJEKT.Budget) AS
MaxvonBudget, MIN(PROJEKT.Budget) AS MinvonBudget, AVG(PROJEKT.Budget) AS
MittelwertvonBudget
FROM PROJEKT;
AnzahlvonPersNr MaxvonBudget MinvonBudget MittelwertvonBudget
3
100000
SQL Abfragen und QBE
60000
80000
Datum: 02.06.2006
10 / 29
FHNW⏐Hochschule für Wirtschaft
2.2 Abfragen mit Bedingungen
Meistens möchte man nicht alle Datensätze einer Tabelle anzeigen lassen, sondern eine
Auswahl treffen. Dazu müssen Bedingungen eingegeben werden.
In folgendem Beispiel werden "Name" und "Vorname" derjenigen Mitarbeiter aufgelistet,
die Informatiker sind.
Beispiel 6
SELECT MITARBEITER.Vorname, MITARBEITER.Name
FROM MITARBEITER
WHERE MITARBEITER.Ausbildung="Informatiker";
Vorname Name
Hugo
Maier
Otto
Kunze
Karl Heinz Otto
2.2.1 Operatoren
Für Vergleiche von Werten werden Operatoren benötigt. Folgende Vergleichsoperatoren
sind zulässig:
=
Test auf Gleichheit
NOT
Ungleich
>
Grösser als ...
LIKE
Ähnlich
<
Kleiner als ...
IS NULL
Unbesetzt (leer)
>=
Grösser oder gleich
IS NOT NULL
Nicht unbesetzt (nicht leer)
<=
Kleiner oder gleich
IN
In einer Liste von Werten
<>
Ungleich
enthalten
SQL Abfragen und QBE
Datum: 02.06.2006
11 / 29
FHNW⏐Hochschule für Wirtschaft
Mit den logischen Operatoren AND (logisches UND), OR (logisches ODER) und NOT
(Negation) können weitere Bedingungen konstruiert werden:
Für die Verknüpfung der Schlüsselwörter AND, OR und NOT gelten folgende Regeln:
ausdruck1 AND ausdruck2 = resultat
ausdruck1
ausdruck2
resultat
03
0
0
X
0
0
0
X
0
X
X
X
ausdruck1 OR ausdruck2 = resultat
ausdruck1
ausdruck2
resultat
0
0
0
X
0
X
0
X
X
X
X
X
NOT ausdruck = resultat
ausdruck
resultat
0
X
X
0
In folgendem Beispiel werden diejenigen Mitarbeiter aufgelistet werden, die sowohl
Informatiker sind als auch einen Stundensatz kleiner oder gleich 100 aufweisen.
Beispiel 7
SELECT MITARBEITER.Vorname, MITARBEITER.Name
FROM MITARBEITER
WHERE MITARBEITER.Ausbildung="Informatiker" AND
MITARBEITER.Stundensatz<=100;
3
0
X
trifft nicht zu
trifft zu
ist nicht erfüllt
ist erfüllt
SQL Abfragen und QBE
falsch
wahr
off
on
aus
an
Datum: 02.06.2006
12 / 29
FHNW⏐Hochschule für Wirtschaft
Vorname Name
Hugo
Maier
Otto
Kunze
Während in folgendem Beispiel diejenigen Mitarbeiter aufgelistet werden, die entweder
Informatiker sind oder einen Stundensatz kleiner oder gleich 100 aufweisen.
Beispiel 8
SELECT MITARBEITER.Vorname, MITARBEITER.Name
FROM MITARBEITER
WHERE MITARBEITER.Ausbildung="Informatiker" OR
MITARBEITER.Stundensatz<=100;
Vorname
Name
Hugo
Maier
Franz
Schulz
Wolfgang Müller
Peter
Neumann
Heinz
Moser
Otto
Kunze
Emil
Meyer
Heinz
Peters
Karl Heinz Otto
SQL Abfragen und QBE
Datum: 02.06.2006
13 / 29
FHNW⏐Hochschule für Wirtschaft
Es sind auch Kombinationen von AND, OR und NOT denkbar, wobei Folgendes gilt:
ƒ
AND bindet stärker als OR, d.h. Ausdrücke, die mit AND verknüpft sind, werden zuerst
abgearbeitet.
ƒ
Dies kann durch Setzen von Klammern übersteuert werden, d.h. die Reihenfolge der
Abarbeitung wird durch die Klammern bestimmt.
ƒ
Unter Beachtung dieser Punkte lassen sich umfangreiche Kombinationen schrittweise
auf den Vergleich zweier Ausdrücke reduzieren.
In folgendem Beispiel werden zunächst alle Projekte gewählt, die entweder "PersNr"
00100030 oder 00100070 zugeordnet sind. Dies sind die Projekte 4711 und 5000. Das
Endresultat muss nun sowohl eines dieser Projekte sein, als auch ein Budget < 80'000
aufweisen. Dies ist nur für Projekt 5000 der Fall.
Beispiel 9
SELECT PROJEKT.ProjNr, PROJEKT.PersNr, PROJEKT.Budget
FROM PROJEKT
WHERE (PROJEKT.PersNr="00100030" OR PROJEKT.PersNr="00100070") AND
PROJEKT.Budget<80000;
ProjNr PersNr
5000
Budget
00100070 60'000.00
SQL Abfragen und QBE
Datum: 02.06.2006
14 / 29
FHNW⏐Hochschule für Wirtschaft
2.3 Datensätze sortieren
Um die Lesbarkeit einer Ergebnistabelle zu verbessern, ist es häufig sinnvoll, die
einzelnen Datensätze nach bestimmten Kriterien zu sortieren (aufsteigend, absteigend).
In nachstehendem Beispiel werden alle Mitarbeiter nach "Ausbildung" und - innerhalb
derselben Ausbildung - nach "Name" aufsteigend sortiert. Das Schlüsselwort ASC muss
im SQL-Befehl nicht zwingend eingegeben werden, da als Standardwert ASC verwendet
wird.
Beispiel 10
SELECT MITARBEITER.Ausbildung, MITARBEITER.Name, MITARBEITER.Vorname
FROM MITARBEITER
ORDER BY MITARBEITER.Ausbildung, MITARBEITER.Name;
Ausbildung
Name
Vorname
DVK
Schulz
Franz
Informatiker
Kunze
Otto
Informatiker
Maier
Hugo
Informatiker
Otto
Karl Heinz
Programmierer Moser
Heinz
Programmierer Neumann Peter
Systemplaner
Meyer
Emil
Systemplaner
Müller
Wolfgang
Systemplaner
Peters
Heinz
In folgendem Beispiel werden die Datensätze nach "Geburtsdatum" absteigend sortiert.
Beispiel 11
SELECT MITARBEITER.Name, MITARBEITER.Vorname, MITARBEITER.Geburtsdatum
FROM MITARBEITER
ORDER BY MITARBEITER.Geburtsdatum DESC;
SQL Abfragen und QBE
Datum: 02.06.2006
FHNW⏐Hochschule für Wirtschaft
Name
15 / 29
Vorname Geburtsdatum
Moser
Heinz
01.01.1970
Meyer
Emil
22.07.1965
Neumann Peter
13.09.1962
Maier
Hugo
01.01.1960
Schulz
Franz
05.03.1959
Kunze
Otto
02.03.1955
Müller
Wolfgang
22.05.1952
Peters
Heinz
01.07.1950
Otto
Karl Heinz
13.12.1949
SQL Abfragen und QBE
Datum: 02.06.2006
16 / 29
FHNW⏐Hochschule für Wirtschaft
2.4 Datensätze gruppieren
Mit Hilfe der Gruppierungsfunktion werden Datensätze, die in der angegebenen
Attributliste dieselben Werte enthalten, zu einem einzelnen Datensatz zusammengefasst.
Folgendes
Beispiel
gruppiert
alle
Mitarbeiter
nach
"Ausbildung",
wobei
Mehrfachnennungen des Attributs "Ausbildung" zu einem Datensatz zusammengefasst
werden.
SELECT MITARBEITER.Ausbildung
FROM MITARBEITER
GROUP BY MITARBEITER.Ausbildung;
Ausbildung
DVK
Informatiker
Programmierer
Systemplaner
Häufig werden Gruppierungen in Kombination mit Aggregatsfunktionen eingesetzt.
Folgendes Beispiel gruppiert alle Mitarbeiter nach "Ausbildung" und gibt die Anzahl
Mitarbeiter für jede Ausbildungsart an.
Beispiel 12
SELECT MITARBEITER.Ausbildung, Count(MITARBEITER.PersNr) AS
AnzahlvonPersNr
FROM MITARBEITER
GROUP BY MITARBEITER.Ausbildung;
SQL Abfragen und QBE
Datum: 02.06.2006
17 / 29
FHNW⏐Hochschule für Wirtschaft
Ausbildung AnzahlvonPersNr
DVK
1
Informatiker
3
Programmierer
2
Systemplaner
3
2.4.1 Bedingungen für Gruppierungen
In Verbindung mit der Gruppierungsfunktion existiert die Möglichkeit zur Definition von
Gruppenbedingungen. Im Gegensatz zur einfachen Abfrage mit Bedingungen ("WHERE"Befehl) werden die auf Gruppierungen angegebenen Bedingungen ("HAVING"-Befehl)
nicht auf einzelne Datensätze, sondern auf Datensatzgruppen angewendet.
In folgendem Beispiel wird der minimale, maximale und durchschnittliche Stundensatz für
jede Ausbildungsart berechnet, wobei der durchschnittliche Stundensatz grösser oder
gleich 80 sein soll.
Beispiel 13
SELECT MITARBEITER.Ausbildung, MIN(MITARBEITER.Stundensatz) AS
MinvonStundensatz, MAX(MITARBEITER.Stundensatz) AS MaxvonStundensatz,
AVG(MITARBEITER.Stundensatz) AS MittelwertvonStundensatz
FROM MITARBEITER
GROUP BY MITARBEITER.Ausbildung
HAVING AVG(MITARBEITER.Stundensatz)>=80;
SQL Abfragen und QBE
Datum: 02.06.2006
18 / 29
FHNW⏐Hochschule für Wirtschaft
Ausbildung MinvonStundensatz MaxvonStundensatz MittelwertvonStundensatz
DVK
Informatiker
Programmierer
Systemplaner
SQL Abfragen und QBE
80
80
80
100
110
103.33
80
85
82.5
100
100
100
Datum: 02.06.2006
19 / 29
FHNW⏐Hochschule für Wirtschaft
2.5 Unterabfragen
Oft tritt der Fall auf, dass eine Abfrage auf dem Ergebnis einer oder mehrerer vorgängiger
Abfragen aufbaut. Dies kann mit so genannten Unterabfragen bewerkstelligt werden.
Dabei kann sich eine Unterabfrage auf andere aber auch dieselbe Tabelle wie die Abfrage
selbst beziehen. Eine Abfrage mit Unterabfragen stellt eine hierarchische Struktur dar,
wobei die einzelnen Abfragen "von unten nach oben" abgearbeitet werden. D.h. die auf
der hierarchisch tiefsten Ebene stehende Abfrage wird zuerst abgearbeitet, diese gibt ein
Resultat zurück, welches mit der nächsthöheren Abfrage verglichen wird etc. In der
Ausgabe erscheinen nur die in der obersten Stufe festgelegten Attribute.
Abfrage
Vergleich
Unterabfrage 1
Vergleich
Unterabfrage 2
Vergleich
Unterabfrage n
Richtung der Abarbeitung
Ausgabe des Endresultats
Abfragen und Unterabfragen müssen Attribute aufweisen, deren Werte miteinander
verglichen werden können, d.h. die Datentypen müssen zueinander kompatibel sein.
Für
den
Vergleich
von
Abfragen
und
Unterabfragen
können
wiederum
Vergleichsoperatoren verwendet werden:
In folgendem Beispiel sollen alle Projekte des Mitarbeiters "Wolfgang Müller" ausgegeben
werden, in denen er Projektleiter ist. Dabei wird anhand des Namens und des Vornamens
die "PersNr" aus der Tabelle MITARBEITER geholt, welche dann mit der "PersNr" aus der
Tabelle PROJEKT verglichen wird.
SQL Abfragen und QBE
Datum: 02.06.2006
FHNW⏐Hochschule für Wirtschaft
20 / 29
Beispiel 14
SELECT PROJEKT.ProjNr, PROJEKT.PersNr, PROJEKT.ProjName
FROM PROJEKT
WHERE PROJEKT.PersNr=4
(SELECT MITARBEITER.PersNr
FROM MITARBEITER
WHERE MITARBEITER.Name = "Müller" AND MITARBEITER.Vorname = "Wolfgang");
ProjNr PersNr
4711
ProjName
00100030 Kraftwerk Entenhausen
In folgendem Beispiel werden "Name", "Vorname" und "Stundensatz" aller Mitarbeiter
aufgelistet, deren Stundensatz unter dem Durchschnitt liegt.
Beispiel 15
SELECT MITARBEITER.Vorname, MITARBEITER.Name, MITARBEITER.Stundensatz
FROM MITARBEITER
WHERE MITARBEITER.Stundensatz <
(SELECT AVG(MITARBEITER.Stundensatz)
FROM MITARBEITER);
4
Annahme: Es gibt nur einen Wolfgang Müller.
SQL Abfragen und QBE
Datum: 02.06.2006
FHNW⏐Hochschule für Wirtschaft
Vorname
Name
21 / 29
Stundensatz
Franz
Schulz
80.00
Peter
Neumann
80.00
Heinz
Moser
85.00
SQL Abfragen und QBE
Datum: 02.06.2006
22 / 29
FHNW⏐Hochschule für Wirtschaft
2.6 Tabellen verknüpfen (Joining)
Bisher konnten wir nur die Attribute einer einzigen Tabelle anzeigen. Meistens möchte
man aber in einer Liste Attribute von verschiedenen Tabellen darstellen. Dies ist durch
das Verknüpfen von Tabellen möglich.
Folgende Abfrage verknüpft die Tabellen PROJEKT und MITARBEITER über das Attribut
"PersNr". Als Resultat werden alle Projekte und deren Projektleiter ausgegeben.
Beispiel 16
SELECT PROJEKT.ProjName, MITARBEITER.Name
FROM MITARBEITER INNER JOIN PROJEKT ON MITARBEITER.PersNr =
PROJEKT.PersNr
ORDER BY PROJEKT.ProjName;
ProjName
Name
Bestellsystem für Firma XYZ Kunze
Info-System Anlagebau
Meyer
Kraftwerk Entenhausen
Müller
Dasselbe Resultat erhält man auch mit folgendem Befehl:
Beispiel 17
SELECT PROJEKT.ProjName, MITARBEITER.Name
FROM MITARBEITER, PROJEKT
WHERE MITARBEITER.PersNr=PROJEKT.PersNr
ORDER BY PROJEKT.ProjName;
SQL Abfragen und QBE
Datum: 02.06.2006
FHNW⏐Hochschule für Wirtschaft
ProjName
23 / 29
Name
Bestellsystem für Firma XYZ Kunze
Info-System Anlagebau
Meyer
Kraftwerk Entenhausen
Müller
Folgendes Beispiel zeigt was passiert, wenn man keine Verknüpfung angibt. Die
Datensätze der beteiligten Tabellen können dann in keinerlei Beziehung zueinander
gebracht werden. Das System kombiniert nun alle Datensätze der einen Tabelle mit allen
Datensätzen der anderen Tabelle. In diesem Fall Tabelle PROJEKT (3 Datensätze) mit
Tabelle MITARBEITER (9 Datensätze), was als Resultat 27 Datensätze ergibt (3 x 9 =
27).
Beispiel 18
SELECT PROJEKT.ProjName, MITARBEITER.Name
FROM MITARBEITER, PROJEKT;
ProjName
Kraftwerk Entenhausen
Name
Maier
Bestellsystem für Firma XYZ Maier
Info-System Anlagebau
Maier
Kraftwerk Entenhausen
Schulz
Bestellsystem für Firma XYZ Schulz
Info-System Anlagebau
Schulz
Kraftwerk Entenhausen
Müller
Bestellsystem für Firma XYZ Müller
SQL Abfragen und QBE
Datum: 02.06.2006
24 / 29
FHNW⏐Hochschule für Wirtschaft
ProjName
Name
Info-System Anlagebau
Müller
Kraftwerk Entenhausen
Neumann
Bestellsystem für Firma XYZ Neumann
Info-System Anlagebau
Neumann
Kraftwerk Entenhausen
Moser
Bestellsystem für Firma XYZ Moser
Info-System Anlagebau
Moser
Kraftwerk Entenhausen
Kunze
Bestellsystem für Firma XYZ Kunze
Info-System Anlagebau
Kunze
Kraftwerk Entenhausen
Meyer
Bestellsystem für Firma XYZ Meyer
Info-System Anlagebau
Meyer
Kraftwerk Entenhausen
Peters
Bestellsystem für Firma XYZ Peters
Info-System Anlagebau
Peters
Kraftwerk Entenhausen
Otto
Bestellsystem für Firma XYZ Otto
Info-System Anlagebau
Otto
2.6.1 Verknüpfungseigenschaften
Bei der Verknüpfung von Tabellen muss man sich darüber im Klaren sein, welche
Informationen man genau erhalten will. Dies kann durch die Festlegung von
Verknüpfungseigenschaften genauer spezifiziert werden.
Folgende drei Optionen stehen zur Verfügung.
INNER JOIN
Datensätze aus zwei Tabellen werden kombiniert, sobald
Gleichheitsverknüpfung
übereinstimmende
Werte
in
einem
Feld
gefunden
werden, das in beiden Tabellen vorhanden ist.
LEFT JOIN
Schliesst alle Datensätze aus der ersten (linken) Tabelle
Linke Inklusionsverknüpfung
von zwei Tabellen ein, auch wenn keine entsprechenden
Werte für Datensätze in der zweiten (rechten) Tabelle
vorhanden sind.
RIGHT JOIN
Schliesst alle Datensätze aus der zweiten (rechten)
Rechte
Tabelle von zwei Tabellen ein, auch wenn keine
Inklusionsverknüpfung
übereinstimmenden Werte für Datensätze in der ersten
(linken) Tabelle vorhanden sind.
In folgendem Beispiel werden alle Mitarbeiter ausgegeben und bei denjenigen, denen ein
Projekt zugeordnet ist, noch die entsprechenden Projektangaben.
SQL Abfragen und QBE
Datum: 02.06.2006
25 / 29
FHNW⏐Hochschule für Wirtschaft
Beispiel 19
SELECT MITARBEITER.PersNr, MITARBEITER.Name, MITARBEITER.Vorname,
PROJEKT.ProjNr, PROJEKT.ProjName
FROM MITARBEITER LEFT JOIN PROJEKT ON MITARBEITER.PersNr =
PROJEKT.PersNr;
PersNr
Name
Vorname ProjNr
00100010 Maier
Hugo
00100020 Schulz
Franz
00100030 Müller
Wolfgang 4711
ProjName
Kraftwerk Entenhausen
00100040 Neumann Peter
00100050 Moser
Heinz
00100060 Kunze
Otto
4712
Bestellsystem für Firma XYZ
00100070 Meyer
Emil
5000
Info-System Anlagebau
00100080 Peters
Heinz
00100090 Otto
Karl Heinz
SQL Abfragen und QBE
Datum: 02.06.2006
26 / 29
FHNW⏐Hochschule für Wirtschaft
2.7 Kombinationen
Entsprechend der Syntax des SQL-Abfragebefehls lassen sich alle bisher behandelten
Befehlsmöglichkeiten kombinieren.
In folgendem Beispiel werden alle geleisteten Stunden pro Mitarbeiter summiert und dort,
wo die Summe > 10 ist, zusammen mit den Mitarbeiterdaten und dem Abteilungsnamen
angezeigt, absteigend sortiert nach der Summe geleisteter Stunden (die höchste
zuoberst).
Beispiel 20
SELECT MITARBEITER.PersNr, MITARBEITER.Name, MITARBEITER.Vorname,
SUM(PROJEKTSTUNDEN.Stunden) AS SummevonStunden, ABTEILUNG.AbtName
FROM (ABTEILUNG INNER JOIN MITARBEITER ON ABTEILUNG.AbtNr =
MITARBEITER.AbtNr) INNER JOIN PROJEKTSTUNDEN ON MITARBEITER.PersNr =
PROJEKTSTUNDEN.PersNr
GROUP BY MITARBEITER.PersNr, MITARBEITER.Name, MITARBEITER.Vorname,
ABTEILUNG.AbtName
HAVING SUM(PROJEKTSTUNDEN.Stunden)>10
ORDER BY SUM(PROJEKTSTUNDEN.Stunden) DESC;
PersNr
Name
00100030 Müller
Vorname SummevonStunden
Wolfgang
AbtName
44 Systemplanung
00100040 Neumann Peter
16 Anwendungsentwicklung
00100020 Schulz
Franz
16 Anwendungsentwicklung
00100010 Maier
Hugo
16 Systemplanung
SQL Abfragen und QBE
Datum: 02.06.2006
27 / 29
FHNW⏐Hochschule für Wirtschaft
2.8 Parameterabfragen
In den vorangehenden Beispielen, in denen Bedingungen vorkamen, mussten die
Vergleichswerte als fixe Werte im Abfragebefehl eingegeben werden. Wollte man andere
Vergleichswerte verwenden, so müsste jeweils eine separate Abfrage erstellt bzw. eine
bestehende Abfrage angepasst werden. Dies ist umständlich und kann auch nicht allen
Endbenutzern zugemutet werden.
Als Lösung dafür bieten sich so genannte Parameterabfragen. Eine Parameterabfrage ist
eine Abfrage, die bei der Ausführung ein Dialogfeld zur Eingabe von Werten anzeigt.
Folgendes Beispiel liefert eine Stundenübersicht eines Projekts, wobei der Wert des
Feldes "ProjNr" über den Parameter Projektnummer eingegeben werden kann. Der
Abfrageparameter muss in eckigen Klammern stehen.
Beispiel 21
SELECT PROJEKTSTUNDEN.ProjNr, PROJEKTSTUNDEN.Datum,
PROJEKTSTUNDEN.PersNr, PROJEKTSTUNDEN.Aufgabe,
SUM(PROJEKTSTUNDEN.Stunden) AS SummevonStunden
FROM PROJEKTSTUNDEN
GROUP BY PROJEKTSTUNDEN.ProjNr, PROJEKTSTUNDEN.Datum,
PROJEKTSTUNDEN.PersNr, PROJEKTSTUNDEN.Aufgabe
HAVING PROJEKTSTUNDEN.ProjNr=[Projektnummer]
ORDER BY PROJEKTSTUNDEN.Datum, PROJEKTSTUNDEN.PersNr;
ProjNr
Aufgabe
SummevonStunden
4711
12.01.1995 00100030 Abstimmung
Datum
PersNr
8
4711
13.01.1995 00100030 Abstimmung
10
4711
20.01.1995 00100010 Konzept
4711
21.01.1995 00100010 Konzept
8
4711
28.01.1995 00100030 Planung
10
4711
02.02.1995 00100030 Planung
10
4711
10.02.1995 00100020 Programmierung
8
4711
10.02.1995 00100040 Programmierung
8
SQL Abfragen und QBE
8
Datum: 02.06.2006
28 / 29
FHNW⏐Hochschule für Wirtschaft
ProjNr
Datum
PersNr
Aufgabe
SummevonStunden
4711
11.02.1995 00100020 Programmierung
8
4711
11.02.1995 00100040 Programmierung
8
SQL Abfragen und QBE
Datum: 02.06.2006
29 / 29
FHNW⏐Hochschule für Wirtschaft
Anhang
Tabellen mit Daten
ABTEILUNG
AbtNr
A001
R001
S001
S002
S003
V001
AbtName
Organisation
Rechnerbetrieb
Systemplanung
Anwendungsentwicklung
Benutzerservice
Vertrieb
Kostenstelle
04711
05000
03030
03020
03030
08010
MITARBEITER
PersNr
00100010
00100020
00100030
00100040
00100050
00100060
00100070
00100080
00100090
AbtNr
S001
S002
S001
S002
S002
R001
A001
S003
V001
Name
Maier
Schulz
Müller
Neumann
Moser
Kunze
Meyer
Peters
Otto
Vorname
Hugo
Franz
Wolfgang
Peter
Heinz
Otto
Emil
Heinz
Karl Heinz
Ausbildung
Informatiker
DVK
Systemplaner
Programmierer
Programmierer
Informatiker
Systemplaner
Systemplaner
Informatiker
Geburtsdatum
01.01.1960
05.03.1959
22.05.1952
13.09.1962
01.01.1970
02.03.1955
22.07.1965
01.07.1950
13.12.1949
Stundensatz
100.00
80.00
100.00
80.00
85.00
100.00
100.00
100.00
110.00
PROJEKT
ProjNr
4711
4712
5000
ProjName
Kraftwerk Entenhausen
Bestellsystem für Firma XYZ
Info-System Anlagebau
PersNr
00100030
00100060
00100070
Budget
100'000.00
80'000.00
60'000.00
Starttermin
05.01.1995
01.03.1995
05.04.1995
Endtermin
01.12.1995
01.08.1995
01.08.1995
PROJEKTSTUNDEN
Datum
20.01.1995
21.01.1995
10.02.1995
11.02.1995
12.01.1995
13.01.1995
28.01.1995
02.02.1995
05.03.1995
10.02.1995
11.02.1995
04.03.1995
ProjNr
4711
4711
4711
4711
4711
4711
4711
4711
4712
4711
4711
4712
SQL Abfragen und QBE
PersNr
00100010
00100010
00100020
00100020
00100030
00100030
00100030
00100030
00100030
00100040
00100040
00100060
Aufgabe
Konzept
Konzept
Programmierung
Programmierung
Abstimmung
Abstimmung
Planung
Planung
Abstimmung
Programmierung
Programmierung
Planung
Stunden
8.00
8.00
8.00
8.00
8.00
10.00
10.00
10.00
6.00
8.00
8.00
8.00
Datum: 02.06.2006