Abfrageformulierung mit SQL

Abfrageformulierung mit SQL (DQL)
Beispiel-Datenbank
Abfrageformulierung (DQL) mit SQL
Beispiel-Datenbank: Fortbildungen
Die Beispiele auf den folgenden Seiten beziehen sich auf die Datenbank Fortbildungen mit den Tabellen
Teilnehmer, Kurs und Ort
Teilnehmer
Teiln_Nr
100
101
102
103
104
105
106
107
108
109
Name
Hirsch
Nicks
Unterländer
Peters
Wallung
Pfeiffer
Hauer
Hofmann
Sorglos
Hirsch
Vorname
Harry
Steffi
Elke
Paul
Walther
Claudia
Hans
Helma
Susi
Wolfgang
Strasse
Baumgartenstr. 2
Holzweg 8
Max-Weber-Str. 12
Am Markt 1
Panoramapfad 33
Mozartweg 6
Im Winkel 16a
Am Bächle 3
Hauptstr. 143
Jordanstr. 5
Wohnort
75175 Pforzheim
73124 Oberndorf
81023 München
53522 Köln
09663 Grünstadt
74121 Ludwigshafen
12329 Talhausen
66822 Heidelberg
40210 Sonnstetten
35039 Marburg
Kurs
Kurs_Nr
Kurs
1.0
Geschäftsprozess, betriebl.
Organisation
2.0
Projektmanagement
3.1
Kostenrechnung
3.2
IT-Marketing
4.1
Grundlagen PC-Technik
4.3
Vertiefung PC-Technik
5.1b
Java Fortgeschrittenen-kurs
5.1c
C++ Anfängerkurs
5.1d
C++ Fortgeschrittenen-kurs
5.3
Vertiefung Java
5.4
Dynamische Web-Seiten
5.6
Grundlagen Datenbanken
Dauer
3 keine
5 keine
2 keine
1 Teilnahme am Modul Kostenrechnung
2 keine
2 Modul 4.1
9 Programmiererfahrung
14 keine
10 Programmiererfahrung
1 Teilnahme am Fortgeschrittenenkurs oder vergleichbare
Kenntnisse
Kenntnisse einer höheren Programmiersprache
5 keine
Ort
Ort_Nr
10
11
12
13
14
15
Voraussetzungen
Ort
Schule
Ulm
Robert-Bosch-Schule
Pforzheim Fritz-Erler-Schule
Ehingen
Kfm. Schule
Pforzheim Heinrich-Wieland-Schule
Marburg
Adolf-Reichwein-Schule
Sindelfingen Gottlieb-Daimler-Schule II
1
Ort_Nr
12
10
11
11
13
13
14
14
14
15
10
13
Abfrageformulierung mit SQL (DQL)
1. Projektion und Formatierung
1 Projektion und Formatierung
Auswahl aller Spalten einer Tabelle
Syntax:
SELECT
*
FROM
<Tabelle>
Beispiel:
Gesucht wird die Tabelle "Kurs".
SELECT
*
FROM
Kurs
Auswahl einer Spalte einer Tabelle
Syntax:
SELECT
<Spalte>
FROM
<Tabelle>
Beispiel:
Gesucht werden die Nachnamen aller Teilnehmer der Kurse.
SELECT
Name
FROM
Teilnehmer
Auswahl mehrerer Spalten einer Tabelle
Syntax:
SELECT
<Spalte1> , <Spalte2> , ...
FROM
<Tabelle>
Beispiel:
Gesucht werden die Vor- und Nachnamen aller Teilnehmer der Kurse.
SELECT
Vorname, Name
FROM
Teilnehmer
Hinweis:
In SQL kann ein Anfrageergebnis mehrere identische Tupel enthalten.
Auswahl ohne mehrfaches Auftreten desselben Tupels
Syntax:
SELECT
DISTINCT
<Spalte>
FROM
<Tabelle>
Beispiel:
Gesucht werden die verschiedenen Nachnamen der Teilnehmer der Kurse. Tragen zwei
Teilnehmer den gleichen Nachnamen, so wird dieser nur einmal aufgeführt.
SELECT
DISTINCT
Name
FROM
Teilnehmer
Formatierte Ausgabe und Berechnungen in einer Selektion
Beispiel:
Es soll für jeden Kurs berechnet werden, wie viel Stunden dieser dauert.
SELECT
’Die Fortbildung ’,Kurs,’dauert ’,Dauer*8,’ Stunden’
FROM
Kurs
Umbenennen von Spalten
Syntax:
SELECT
<Spalte> AS <neuer Spaltenname>
FROM
<Tabelle>
Beispiel:
Unter der Bezeichnung “Schulorte“ sind alle Schulen gesucht, die in der Tabelle "Ort"
gespeichert sind.
SELECT
DISTINCT
Ort AS Schulorte
FROM
Ort
Sortierung
Syntax:
Beispiel:
SELECT
<Spalte>
FROM
<Tabelle>
ORDER BY
<Spalte> {DESC | ASC}
Gesucht wird für jeden Teilnehmer der Name und sein Wohnort. Dabei ist die Ergebnistabelle
absteigend (DESC) nach dem Namen sortiert auszugeben.
SELECT
Name, Wohnort
FROM
Teilnehmer
ORDER BY
Name DESC
2
Abfrageformulierung mit SQL (DQL)
2. Selektion
2 Selektion
Syntax:
SELECT
FROM
WHERE
<Spalte>
<Tabelle>
<Bedingung>
Selektion mit einfachem Vergleich
Beispiel:
Gesucht werden die Kurse, bei denen keine Voraussetzungen notwendig sind.
SELECT
Kurs
FROM
Kurs
WHERE
Voraussetzungen = 'keine'
Beispiel:
Gesucht werden die Kurse, bei denen Voraussetzungen gefordert werden.
SELECT
Kurs, Voraussetzungen
FROM
Kurs
WHERE
NOT (Voraussetzungen = 'keine')
Beispiel:
Gesucht werden die Kurse, die mindestens 3 Tage dauern.
SELECT
Kurs, Dauer
FROM
Kurs
WHERE
Dauer >= 3
Selektion mit mehreren Bedingungen
Beispiel:
Gesucht werden alle Kurse, die Programmiererfahrung erfordern und weniger als 10
Tage dauern.
SELECT
Kurs
FROM
Kurs
WHERE
Voraussetzungen = 'Programmiererfahrung' AND
Dauer < 10
Beispiel:
Gesucht werden die Namen und Wohnorte aller Teilnehmer, deren Vorname Harry oder
Susi ist.
SELECT
Name, Wohnort
FROM
Teilnehmer
WHERE
Vorname = 'Harry' OR
Vorname = 'Susi'
Selektion mit dem Operator IN
Beispiel:
Gesucht werden die Namen aller Schulen, die in Pforzheim oder Marburg sind.
SELECT
Schule
FROM
Ort
WHERE
Ort IN ('Pforzheim', 'Marburg')
Beispiel:
Gesucht werden die Namen aller Schulen, die nicht in Sindelfingen, Ulm und Freiburg
sind.
SELECT
Schule
FROM
Ort
WHERE
Ort NOT IN ('Sindelfingen', 'Ulm', 'Freiburg')
Selektion mit dem Operator LIKE
Der LIKE-Operator ermöglicht den Vergleich eines Strings mit einem Muster. Muster werden aus beliebigen Zeichen
eines Strings und den beiden Sonderzeichen '_' und '%' gebildet. '_' steht für genau ein beliebiges Zeichen, während
'%' für eine beliebig große (evtl. leere) Kette von beliebigen Zeichen steht.
Achtung: In IS0-SQL: '_' = '?' und '%' = '*'.
Beispiel:
Gesucht werden die Vornamen aller Teilnehmer, deren Postleitzahl mit "3" endet.
SELECT
Vorname
FROM
Teilnehmer
3
Abfrageformulierung mit SQL (DQL)
WHERE
Beispiel:
2. Selektion
Wohnort LIKE '____3%'
Gesucht werden die Vornamen aller Teilnehmer, deren Name mit "H"- und deren
Wohnort nicht mit "H" beginnt.
SELECT
Name, Vorname, Wohnort
FROM
Teilnehmer
WHERE
Name LIKE 'H%' AND Wohnort NOT LIKE '______H%' (6*_)
Selektion und NULL-Werte
NULL wird i.a. interpretiert als ein Platzhalter für die Aussage "Information/Attribut ist nicht vorhanden oder nicht
bekannt oder nicht anwendbar". SQL betrachtet daher im Vergleich zwei NULL-Werte immer als unterschiedlich.
Möglich ist es allerdings abzufragen, ob die Aussage "Spalten-Wert ist NULL" für ein Tupel gilt.
Beispiel:
Gesucht werden die Kurse, die keine Dauer angegeben haben (oder deren Dauer nicht
bekannt ist)
SELECT
Kurs
FROM
Kurs
WHERE
Dauer IS NULL
Übungsaufgaben 1:
Öffne mit dem Query Tool die Datei Schuldatenbank.mdb
Erarbeiten Sie in 2-3er Gruppen an einem Computer folgende Fragen:
1. Geben Sie die Tabelle schueler aus.
2. Man ermittele alle Schüler, die den Nachnamen Müller haben.
3. Man ermittele alle Schüler, die in der 10. Jahrgangsstufe sind und gebe deren Vor- und Nachnamen aus.
4. Geben Sie eine Liste aller Nachnamen an der Schule aus (von Seiten der Schüler). Achten Sie darauf, dass
kein Nachname in der Liste doppelt vorkommt.
5. Man ermittele alle Schüler, die „Eva“ als Namensbestandteil haben.
6. Man ermittele alle Schüler, die zwischen dem 18.08.82 und dem 28.08.82 geboren sind. (Tipp: Das Datum
28.8.1982 entspricht in SQL #8/28/1982# )
7. Gesucht ist eine Liste mit den Namen aller männlichen Oberstudienräte (OSTR)
8. Gesucht ist eine Liste aller Deutschkurse, die Herr Hoppe hält.
9. Gesucht sind alle Schülernummern (SchNr), deren 2. Klausur mind. 9 Punkte besser ist als die erste Klausur
10. Gesucht sind alle Schülernummern, deren Punktzahlen der beiden Klausuren zusammengerechnet eine
ungerade Zahl ergeben
11. Gesucht sind alle Schülernummern, deren Punktzahlen der beiden Klausuren zusammengerechnet eine gerade
Zahl ergeben
12. Gesucht ist eine Liste mit allen Schülernummern und Kursnummern, deren Durchschnittswert der beiden
Noten negativ ist. Die Liste soll nach KursNr sortiert sein.
13. Gesucht ist eine Liste aller Kurse, bei der ein Schüler 15 Punkte in beiden Klausuren geschrieben hat. Die
Liste soll aufsteigend sortiert sein und die Spaltenbeschriftung „15-Punkte-Kurse“ tragen.
14. * Was bewirkt die SQL-Eingabe SELECT * FROM fakultas, lehrer . Können Sie diese Eingabe
sinnvoll manipulieren?
4
Abfrageformulierung mit SQL (DQL)
3. Verbund von Tabellen
3 Verbund von Tabellen
Einfacher Equijoin mit zwei Tabellen
Syntax:
SELECT
<Spalte1> , <Spalte2>, ...
FROM
<Tabelle1> , <Tabelle2>
WHERE
<Join-Bedingung>
Hinweis:
Wenn die Tabellen, die miteinander zu verbinden sind, Spalten mit gleichem
Spaltennamen aufweisen, dann muss jeweils spezifiziert werden, welche Spalte
welcher Tabelle gemeint ist.
Beispiel:
Zur Verkürzung des Anfragetextes können für die Tabellen in der FROM-Komponente
auch Alias-Namen vergeben werden.
SELECT
FROM
WHERE
Hinweis:
Schule, Kurs
Ort O, Kurs K
O.Ort_Nr = K.Ort_Nr ORDER BY Schule ASC, Kurs ASC
Die Alias-Namen können bereits in der SELECT-Komponente verwendet werden, auch wenn
sie erst in der FROM-Komponente definiert werden.
Einfacher Equijoin über n>2 Tabellen
Beispiel:
Welche Kurse belegt Frau Hofmann in Ulm?
SELECT
FROM
WHERE
ORDER BY
Kurs
Teilnehmer T, TeilnehmerKursZuordnung TK, Kurs K, Ort O
T.Name = 'Hofmann')
AND (O.Ort = 'Ulm')
AND (T.Teiln_Nr = TK.Teiln_Nr)
AND (K.Kurs_Nr = TK.Kurs_Nr)
AND (O.Ort_Nr = K.Ort_Nr)
Kurs ASC
Equijoin mit Umbenennen der Spalte
Hinweis:
Es ist oft sinnvoll, Spalten umzubenennen, um Missverständnisse auszuschließen.
Beispiel:
Für jeden Teilnehmer wird die Schulungsdauer in Stunden gesucht.
SELECT
Name, Vorname, Kurs, Dauer*8 AS Stunden
FROM
Teilnehmer T, TeilnehmerKursZuordnung TK, Kurs K
WHERE
(T.Teiln_Nr = TK.Teiln_Nr) AND (K.Kurs_Nr = TK.Kurs_Nr)
ORDER BY
Name
Vereinigung und Durchschnitt mit UNION
Die Datensätze von Tabellen, die identische Spalten enthalten, können durch UNION zusammengefasst werden.
Beispiel:
Gewünscht wird eine Tabelle mit den Ortsnummern Ort_Nr, der Orte, an denen das Modul 4.1
Voraussetzung ist oder deren Ort Freiburg oder Ulm ist.
SELECT
Ort_Nr
FROM
Kurs K
WHERE
K.Voraussetzungen = 'Modul 4.1'
UNION
SELECT
Ort_NR
FROM
Ort O
WHERE
(O.Ort = 'Freiburg') OR (O.Ort = 'Ulm')
5
Abfrageformulierung mit SQL (DQL)
4. Aggregatfunktionen und Gruppen
4 Aggregatfunktionen und Gruppen
Hinweis: NULL-Werte werden vor der Auswertung einer Aggregatfunktion eliminiert.
Zählfunktion
Syntax:
SELECT
FROM
COUNT ([DISTINCT] <Spaltenliste|*>)
<Tabelle>
Beispiel:
Gesucht wird die Anzahl aller Fortbildungsteilnehmer.
SELECT
COUNT (*)
FROM
Teilnehmer
Beispiel:
Wie viel verschiedene Schulen existieren?
SELECT
COUNT (Schule) AS Schulorte
FROM
Ort
Arithmetische Funktionen
Syntax:
SELECT
FROM
SUM
({numerische Spalte|Arithmetischer Ausdruck mit num. Sp.})
<Tabelle>
Beispiel:
Gesucht wird die Gesamtdauer aller Fortbildungen, die in Freiburg stattfinden.
SELECT
SUM (Dauer) AS Gesamtdauer_in_Freiburg
FROM
Kurs K, Ort O
WHERE
(K.Ort_Nr = O.Ort_Nr) AND (O.Ort = 'Pforzheim‘)
Syntax:
SELECT
FROM
Beispiel:
Gesucht wird die Durchschnittsdauer aller Fortbildungsveranstaltungen.
SELECT
AVG (Dauer) AS Durchschnittsdauer
FROM
Kurs
Min-/Max-Funktionen
Syntax:
SELECT
FROM
Syntax:
SELECT
FROM
Beispiel:
AVG
({numerische Spalte|Arithmetischer Ausdruck mit num. Sp.})
<Tabelle>
MAX
({numerische Spalte|Arithmetischer Ausdruck mit num. Sp.})
<Tabelle>
MIN
({numerische Spalte|Arithmetischer Ausdruck mit num. Sp.})
<Tabelle>
Gesucht wird die kürzeste Kursdauer.
SELECT
MIN (Dauer) AS Minimale_Kursdauer
FROM
Kurs
6
Abfrageformulierung mit SQL (DQL)
4. Aggregatfunktionen und Gruppen
Gruppenbildung in SQL-Anfragen
In den vorangegangenen Beispielen wurden die Aggregatfunktionen immer auf eine ganze Tabelle angewandt.
Daher bestand das Abfrageergebnis immer nur aus einem Tupel. In SQL ist es aber auch möglich,
eine Tabelle in Gruppen "aufzuteilen", d.h. die Tupel einer Tabelle in Gruppen einzuteilen, und dann die
Aggregatfunktionen jeweils auf die Gruppen anzuwenden.
Syntax:
SELECT
<Spalte> , <Aggregatfunktion ...>
FROM
<Tabelle>
GROUP BY
<Spalte>
Hinweis:
Die in der group by-Komponente spezifizierten Spalten müssen auch in der
SELECT-Komponente spezifiziert sein, da Basis für die Gruppierung die "ZwischenErgebnis"-Tabelle ist, die durch Select ... From ... Where ... spezifiziert
wurde. Andererseits müssen alle Spalten der Selektionsliste, die nicht durch
eine Aggregatfunktion gebunden sind, in der group by-Komponente aufgeführt
werden. Die Reihenfolge der Spaltenspezifikation in der GROUP BY Komponente
hat keinen Einfluss auf das Resultat der Abfrage.
Beispiel:
Es sind 2 Gruppen auszugeben: Alle verschiedenen Voraussetzungen und deren GesamtKursdauer nach Gesamt-Kursdauer absteigend sortiert.
SELECT
Voraussetzungen, SUM (Dauer) AS Gesamtdauer
FROM
Kurs
GROUP BY
Voraussetzungen ORDER BY Sum (Dauer) DESC
Beispiel:
Für jeden Kursort soll die Anzahl der Veranstaltungen ermittelt werden.
SELECT
Ort, COUNT(Ort) AS Veranstaltungen
FROM
Ort O, Kurs K
WHERE
K.Ort_Nr= O.Ort_Nr
GROUP BY
Ort ORDER BY COUNT(Ort) DESC, Ort ASC
Auswahl von Gruppen
Syntax:
SELECT
FROM
GROUP BY
HAVING
Beispiel:
<Spalte> , <Aggregatfunktion ...>
<Tabelle>
<Spalte>
<Bedingung>
Es sind alle Orte zusammen mit der Gesamtdauer auszugeben, wenn die Gesamtdauer
mindestens 10 Tage lang ist.
SELECT
Ort, SUM (Dauer) AS Gesamtdauer
FROM
Kurs K, Ort O
WHERE
K.Ort_Nr = O.Ort_Nr
GROUP BY
Ort
HAVING
SUM (Dauer) >= 10
7
Abfrageformulierung mit SQL (DQL)
5. Unterabfragen (Subqueries)
5 Unterabfragen (Subqueries)
Eine Unterabfrage ist eine Abfrage, die in einer Abfrage eingebettet ist.
Unteranfragen mit dem IN-Operator
Syntax:
SELECT
<Spalte1>
FROM
<Tabelle>
WHERE
<Spalte2> IN (SELECT <Spalte3>)
FROM
<Tabelle>
[WHERE
<Bedingung>])
Hinweis:
Spalte 1, Spalte 2 und Spalte 3 müssen nicht unterschiedlich sein.
Beispiel:
Gesucht wird jede Schule an der ein Kurs stattfindet, der Programmiererfahrung verlangt.
SELECT
Ort, Schule
FROM
Ort
WHERE
Ort_Nr IN
(SELECT
Ort_Nr
FROM
Kurs
WHERE
Voraussetzungen = 'Programmiererfahrung')
Hinweis :
Jede Anfrage, die eine Unteranfrage mit dem IN-Operator enthält, ist als Equijoin formulierbar (gilt nicht für NOT IN). So ist die folgende Abfrage äquivalent
zur vorhergehenden.
SELECT
DISTINCT
Ort, Schule
FROM
Kurs K, Ort O
WHERE
(K.Ort_Nr = O.Ort_Nr) AND
(Voraussetzungen = ‘Programmiererfahrung‘)
Hinweis:
Um das Ergebnis einer Unterabfrage zu negieren, kann der NOT-Operator benutzt
werden.
Beispiel:
Gesucht werden alle Kurse, die nicht in Sindelfingen stattfinden.
SELECT
Kurs
FROM
Kurs
WHERE
Ort_Nr NOT IN
(SELECT
Ort_Nr
FROM
Ort
WHERE
Ort = ‘Sindelfingen‘)
Identisch wäre:
SELECT
FROM
WHERE
Kurs
Kurs
Ort_Nr IN
(SELECT
FROM
WHERE
Ort_Nr
Ort
Ort <> 'Sindelfingen')
8