SQL-Anfragen

Online-Kurs 'Datenbanken und
Datenmodellierung'
SQL - Anfragen
Print-Version - 15.04.2002
(c) StR S. Winter - Universität Passau
Inhaltsverzeichnis
1
Der Aufbau von SQL-Anfragen im Überblick
1.1 Der Aufbau einer SQL-Anfrage
2
Einfache SQL-Anfragen
2.1 SELECT - FROM - Anfrage
2.2 Auswahl aller Attribute
2.3 Vermeidung von Duplikaten
2.4 Umbenennung der Ergebnisspalten - Alias
2.5 Übungen
3
SQL-Anfragen mit Bedingungen
3.1 Anfrage mit Bedingung
3.2 Syntaxdiagramm
3.3 Übungen
4
Sortierung der Ergebnismenge
4.1 Die ORDER BY- Klausel
4.2 Aufsteigende und absteigende Sortierung
4.3 Übungen
5
Gruppieren von Datensätzen
5.1 Die GROUP BY- Klausel
5.2 Aggregatfunktionen
5.3 Aggregatfunktion ohne GROUP BY- Klausel
5.4 Rechenoperationen in SQL
5.5 Übungen
6
Gruppierungen mit Bedingungen
6.1 Die HAVING - Klausel
6.2 Übungen
7
SQL-Anfragen über mehrere Tabellen
7.1 Verknüpfung von Tabellen - Join
7.2 Bedeutung der WHERE-Klausel
7.3 Self-Join
7.4 Übungen
8
Geschachtelte SQL-Anfragen mit Vergleichsoperatoren
8.1 Teilanfragen (Subqueries) mit einem Ergebnis
8.2 Übungen
9
Geschachtelte SQL-Anfragen mit speziellen Operatoren
9.1 Teilanfragen mit mehr als einem Ergebnis
9.2 Eine einfache Möglichkeit mit dem IN - Operator
9.3 Erweiterte Möglichkeiten
9.4 Übungen
10 Unteranfragen in der SELECT-Klausel, Korreliertheit
10.1 Unteranfragen in der SELECT-Klausel
10.2 Korrelierte Unteranfragen
10.3 Übungen
11 Verknüpfen mehrerer Ergebnismengen
11.1 UNION-, INTERSECT- und EXCEPT- Operation
11.2 Übungen
12 Weitere Sprachkonstrukte
12.1 Der LIKE-Operator
12.2 Übungen
13 Das Erstellen von Sichten (Views)
13.1 Was sind Sichten (Views)
13.2 Die CREATE VIEW - Anweisung
13.3 Syntaxdiagramm
13.4 Arbeiten mit Views
13.5 Übungen
14 Das Löschen von Sichten (Views)
14.1 Die DROP VIEW - Anweisung
14.2 Syntaxdiagramm
15 SQL mit MS ACCESS
15.1 Ansichten in MS ACCESS
15.2 Die SQL-Ansicht in MS ACCESS
15.3 Aufruf der SQL-Ansicht
1 Der Aufbau von SQL-Anfragen im Überblick
1.1 Der Aufbau einer SQL-Anfrage
Anfragen (Queries) in SQL orientieren sich grundsätzlich an folgendem Schema:
Reihenfolge
SQL-Klausel
Bedeutung
Angabe notwendig
1
SELECT
Auswahl der gewünschten Attribute
ja
2
FROM
Angabe der Tabelle(n), die die gewünschte Information
enthalten
ja
3
WHERE
Angabe von Bedingungen für die gesuchte Information
nein
4
GROUP BY (HAVING) Gruppieren der Daten nach bestimmten Kategorien
nein
5
ORDER BY
nein
Sortierung des Ergebnisses
Die Klauseln mit den Schlüsselwörtern WHERE, GROUP BY, HAVING, ORDER BY sind optional. Die Reihenfolge der
einzelnen Klauseln ist aber verbindlich.
Wird eine Anfrage an die Datenbank gestellt, so erhält man die gewünschte Information in Form einer Tabelle, der
Ergebnistabelle, zurück.
Das untenstehende Syntaxdiagramm zeigt alle Möglichkeiten einer SQL-Anfrage.
Bemerkung:
Im Rahmen dieses Kapitels werden nur die wichtigsten - aber in der Regel ausreichenden - Möglichkeiten hinsichtlich der
SQL-Anfragen besprochen. Die Syntaxdiagramme dagegen zeigen alle Möglichkeiten auf. Vertiefende Informationen zu den
nicht näher erläuterten Konstrukte finden Sie beispielsweise bei [DD98] .
Bemerkung:
Die nachfolgenden Übungsbeispiele können auf der Offline-Datenbank nachvollzogen werden. Achten Sie darauf, dass Sie
auf dem vollständigen Datenbestand arbeiten. Eventuell müssen Sie die Datenbank füllen.
2 Einfache SQL-Anfragen
2.1 SELECT - FROM - Anfrage
Die einfachste Form einer SQL-Anfrage besteht aus der SELECT - und der FROM - Klausel.
Beispiel: Welche Lehrerinnen und Lehrer - unter Angabe des Namens - gibt es im Kollegium?
SQL - Anfrage
Ergebnis - Tabelle
SELECT Name
FROM Lehrkraft ;
Das SQL-Statement wird vom System folgendermaßen interpretiert:
Nimm die Spalte Name der Tabelle Lehrkraft und zeige alle in dieser Spalte eingetragenen Daten an.
Als Ergebnis liefert die Datenbank eine einspaltige Ergebnistabelle mit der Spaltenüberschrift Name zurück.
Um mehrere Attribute auszuwählen, werden in der SELECT - Klausel die gewünschten Spaltennamen, durch Kommata
getrennt, angegeben.
Beispiel: Welche Lehrerinnen und Lehrer - unter Angabe von Personalnummer und Namen - gibt es im Kollegium?
SQL - Anfrage
Ergebnis - Tabelle
SELECT PersNr, Name
FROM Lehrkraft ;
Die Reihenfolge der Spaltennamen in der SELECT- Klausel legt dabei die Anordnung der Ergebnisspalten fest.
Beispiel: Welche Lehrerinnen und Lehrer - unter Angabe Namen und Personalnummer - gibt es im Kollegium?
SQL - Anfrage
Ergebnis - Tabelle
SELECT Name, PersNr
FROM Lehrkraft ;
2.2 Auswahl aller Attribute
Will man alle Spalten einer Tabelle anzeigen lassen, gibt man hinter dem SELECT - Schlüsselwort einen Stern * an.
Beispiel: Welche Daten sind über die Schülerinnen und Schüler gespeichert?
SQL Anfrage
Ergebnis - Tabelle
SELECT *
FROM
Schueler ;
2.3 Vermeidung von Duplikaten
In zahlreichen Fällen kommt derselbe Wert in einer gegebenen Spalte mehrmals vor.
Beispiel: Aus welchen Orten kommen die Mitglieder des Lehrerkollegiums?
SQL - Anfrage
Ergebnis - Tabelle
SELECT Wohnort
FROM Lehrkraft ;
Duplikate werden also nicht automatisch ausgesondert. Um Mehrfachnennungen zu vermeiden, bietet SQL das
Schlüsselwort DISTINCT an.
Beispiel: Welche unterschiedlichen Wohnorte haben die Mitglieder des Lehrerkollegiums?
SQL - Anfrage
Ergebnis - Tabelle
SELECT DISTINCT Wohnort
FROM Lehrkraft ;
Das Schlüsselwort ALL erzwingt die Ausgabe aller Datensätze inklusive Duplikaten. Die Angabe ist aber nicht unbedingt
notwendig. Die Queries
SELECT Wohnort
FROM Lehrkraft ;
bzw.
SELECT ALL Wohnort
FROM Lehrkraft ;
liefern identische Ergebnisse.
Das Schlüsselwort ALL erzwingt die Ausgabe aller Datensätze inklusive Duplikaten. Die Angabe ist aber nicht unbedingt
notwendig. Die Queries
SELECT Wohnort
FROM Professor ;
bzw.
SELECT ALL Wohnort
FROM Professor ;
liefern identische Ergebnisse.
2.4 Umbenennung der Ergebnisspalten - Alias
Zum besseren Verständnis der Ergebnistabelle ist es oft zweckmäßig, die Ergebnisspalten anders zu bezeichnen. Diese
Umbenennung nennt man ALIAS. Dazu ist nur die Angabe des Schlüsselwortes AS und des gewünschten Namens in der
SELECT - Klausel erforderlich.
Beispiel: Welche Lehrerinnen und Lehrer gibt es im Kollegium? Gewünscht sind nur die Namen. Die ausgegebene
Ergebnisspalte soll "Lehrerkollegium" heißen.
SQL - Anfrage
Ergebnis - Tabelle
SELECT Name AS Lehrerkollegium
FROM Lehrkraft ;
Bemerkung:
●
Das Schlüsselwort AS ist neu in SQL/92 und wird nicht von allen Systemen akzeptiert. MS ACCESS verlangt die in
SQL/92 vorgegebene Schreibweise. Bei ORACLE kann das Wort AS weggelassen werden, dafür muss der Aliasname
in Anführungsstriche gesetzt werden. Die SELECT - Klausel lautet damit
SELECT Name "Lehrerkollegium" ;
●
Der neue Spaltenname darf keine Leer- oder Sonderzeichen enthalten.
2.5 Übungen
Aufgabe:
Welche Lehrkräfte - unter Angabe von Personalnummer, Name und Geschlecht - gibt es im Kollegium?
Lösungsvorschlag:
SELECT PersNr, Name, Geschlecht
FROM Lehrkraft ;
Aufgabe:
Welche Schülerinnen und Schüler - unter Angabe von Eintrittsjahr, Nummer und Name - sind an der Schule?
Lösungsvorschlag:
SELECT Eintrittsjahr, Nr, Name
FROM Schueler ;
Aufgabe:
Welche Fächer werden an der Schule unterrichtet? Die Ausgabespalte soll den Namen "Angebotene Faecher" erhalten.
Lösungsvorschlag:
SELECT Name AS Angebotene_Faecher
FROM Fach ;
Da beim Spaltenname kein Leerzeichen zulässig ist, wurde das Leerzeichen durch einen Unterstrich ersetzt.
Aufgabe:
Welche Schülernamen existieren an der Schule? Duplikate sollen nicht ausgegeben werden.
Lösungsvorschlag:
SELECT DISTINCT Name
FROM Schueler ;
3 SQL-Anfragen mit Bedingungen
3.1 Anfrage mit Bedingung
Im WHERE- Teil können Bedingungen angegeben werden, um Datensätze nach bestimmten Kriterien auszuwählen.
Beispiel: Welche Lehrerinnen und Lehrer wohnen in Passau?
SQL - Anfrage
Ergebnis - Tabelle
SELECT PersNr, Name
FROM Lehrkraft
WHERE Wohnort = 'Passau' ;
Die Anfrage ist folgendermaßen zu interpretieren:
Wähle aus der Tabelle Lehrkraft alle Datentupel aus, bei denen der Wert des Attributs Wohnort Passau ist und gib
dann von diesen jeweils PersNr und Name aus.
Dabei gilt:
● Einfache Bedingungen können durch Vergleiche erstellt werden. Dazu stehen die Vergleichsoperatoren
und = zur Verfügung.
,
,
●
Bei Vergleichen mit einer Zeichenkette muss diese in Hochkommata gesetzt werden.
(MS ACCESS verwendet statt Hochkommata Anführungszeichen.)
●
Durch den Operator NOT (...) kann eine Bedingung bestimmt werden, die nicht erfüllt werden soll.
●
Durch den Operator BETWEEN <wert1> AND <wert2> kann ein Bereich angegeben werden, in dem sich der
gesuchte Attributwert befinden muss. Attributwerte, die gleich den Bereichsgrenzen <wert1> und <wert2> sind,
gehören zum ausgewählten Bereich. Der BETWEEN-Operator kann nur auf Attribute angewendet werden, auf deren
Domäne eine Reihenfolge vordefiniert ist. Beispielsweise ist das bei Zahlen immer der Fall.
Mehrere Bedingungen können durch
❍ OR, d.h. mindestens eine der Bedingungen muss erfüllt sein, oder
●
❍
AND, d.h. jede der Bedingungen muss erfüllt sein,
miteinander verknüpft werden.
,
●
Durch Setzen von Klammern kann festgelegt werden, welche Bedingungen "zusammengehören". Werden keine
Klammer gesetzt, so gelten folgende Regeln:
❍
NOT bindet stärker als AND, d.h. der NOT - Operator wird vor dem AND- Operator ausgewertet.
❍
AND bindet stärker als OR.
Beispiel: Welche Daten sind über die Lehrerinnen gespeichert, die zwischen 1949 und 1960 geboren sind?
SQL Anfrage
Ergebnis - Tabelle
SELECT *
FROM
Lehrkraft
WHERE
Geschlecht
= 'w' AND
Geburtsjahr
BETWEEN
1949 AND
1960 ;
Beispiel: Welche Lehrerinnen und Lehrer - unter Angabe von Name, PersNr, Wohnort - wohnen nicht in Passau?
SQL - Anfrage
SELECT Name, PersNr, Wohnort
FROM Lehrkraft
WHERE NOT (Wohnort = 'Passau')
;
3.2 Syntaxdiagramm
Ergebnis - Tabelle
3.3 Übungen
Aufgabe:
Welche Lehrer haben eine dreistellige Personalnummer?
Lösungsvorschlag:
SELECT Name, PersNr
FROM Lehrkraft
WHERE Geschlecht = 'm' AND PersNr BETWEEN 100 AND 999 ;
Aufgabe:
Welche Lehrerinnen - unter Angabe von Personalnummer und Name - wohnen in Berlin oder Muenchen?
Lösungsvorschlag:
SELECT PersNr, Name
FROM Lehrkraft
WHERE Geschlecht = 'w' AND (Wohnort = 'Berlin' OR Wohnort = 'Muenchen') ;
Aufgabe:
Welche Schülernamen gibt es bei den Bekenntnislosen in der fünften Klasse?
Lösungsvorschlag:
SELECT DISTINCT Name
FROM Schueler
WHERE Konfession = 'bk' AND gehoert_zu = 5 ;
Aufgabe:
Welche Ergebnisse liefern folgende Anfragen?
1. SELECT Name
FROM Lehrkraft
WHERE Geschlecht = 'm' OR Wohnort = 'Passau' AND Geburtsjahr
2. SELECT Name
FROM Lehrkraft
WHERE (Geschlecht = 'm' OR Wohnort = 'Passau' ) AND Geburtsjahr
Lösungsvorschlag:
Tabelle 1. Anfrage
= 1945 ;
=1945 ;
Tabelle 2. Anfrage
Im ersten Fall sind alle männlichen Lehrkräfte sowie alle nach 1945 geborenen Lehrkräfte, die aus Passau kommen,
gesucht. Die zweite Anfrage filtert die nach 1945 geborenen Lehrkräfte heraus, die männlich sind oder aus Passau kommen.
4 Sortierung der Ergebnismenge
4.1 Die ORDER BY- Klausel
Um sortierte Ergebnisse zu erhalten, verwendet man das Schlüsselwort ORDER BY. Die ORDER BY- Klausel beinhaltet die
Spalte, nach der sortiert werden soll. Dabei gibt es folgende Sortiermöglichkeiten:
● bei Zeichenketten alphabetisch,
● bei Zahlen nach der Größe bzw.
●
bei expliziter Angabe einer Domäne nach der aufgeschriebenen Reihenfolge.
Beispiel: Gewünscht ist eine alphabetisch geordnete Liste des Lehrerkollegiums mit Angabe der Personalnummer.
SQL - Anfrage
Ergebnis - Tabelle
SELECT Name, PersNr
FROM Lehrkraft
ORDER BY Name ;
Es können auch mehrere Sortierungsspalten, durch Kommata getrennt, angegeben werden. Dann bestimmt die erste Spalte
das Hauptsortierungskriterium. Sind Datensätze bezüglich dieser Spalte identisch, erfolgt eine weitere Sortierung nach dem
zweiten Attribut usw.
Beispiel: Gewünscht ist eine Liste mit Klasse und Schülernamen, sortiert nach Klasse und Alphabet.
SQL - Anfrage
Ergebnis - Tabelle
SELECT gehoert_zu AS Klasse, Name
FROM Schueler
ORDER BY gehoert_zu, Name ;
4.2 Aufsteigende und absteigende Sortierung
Die bisherigen Beispiele waren aufsteigend sortiert. Durch Angabe des Schlüsselwortes DESC kann eine absteigende
Sortierung erreicht werden.
Bemerkung:
Für die aufsteigende Sortierung steht auch das Schlüsselwort ASC zur Verfügung. Die Angabe dieses Schlüsselwortes ist
optional. Wird die ORDER BY- Klausel ohne zusätzliche Angabe verwendet, wird das Ergebnis automatisch aufsteigend
sortiert.
Beispiel: Gewünscht ist eine Lehrerliste mit Personalnummer und Name, absteigend sortiert nach der Personalnummer.
SQL - Anfrage
Ergebnis - Tabelle
SELECT PersNr, Name
FROM Lehrkraft WHERE Geschlecht = 'm'
ORDER BY PersNr DESC ;
4.3 Übungen
Aufgabe:
Welche Fächer, alphabetisch sortiert, gibt es an der Schule?
Lösungsvorschlag:
SELECT Name
FROM Fach
ORDER BY Name ASC ;
bzw.
SELECT Name
FROM Fach
ORDER BY Name ;
Aufgabe:
Gewünscht ist Klasse, Eintrittsjahr und Name aller katholischen Schülerinnen und Schüler, sortiert nach der Klasse und
innerhalb der Klasse nach dem Eintrittsjahr.
Lösungsvorschlag:
SELECT gehoert_zu AS Klasse, Eintrittsjahr, Name
FROM Schueler
WHERE Konfession = 'rk'
ORDER BY gehoert_zu ASC , Eintrittsjahr ASC ;
bzw.
SELECT gehoert_zu AS Klasse, Eintrittsjahr, Name
FROM Schueler
WHERE Konfession = 'rk'
ORDER BY gehoert_zu , Eintrittsjahr ;
Der Attributname gehoert_zu wurde mit dem AS-Operator in Klasse umbenannt.
Aufgabe:
Gesucht sind Personalnummer und Name der Lehrerinnen, die in Passau wohnen, absteigend sortiert nach dem
Geburtsjahr.
Lösungsvorschlag:
SELECT PersNr, Name
FROM Lehrkraft
WHERE Geschlecht = 'w' AND Wohnort = 'Passau'
ORDER BY Geburtsjahr DESC ;
5 Gruppieren von Datensätzen
5.1 Die GROUP BY- Klausel
SQL erlaubt das Gruppieren von Datensätzen unter Berücksichtigung bestimmter Eigenschaften. Dafür steht die GROUP BY Klausel zur Verfügung. Pro Gruppe kann mit Hilfe der Aggregatfunktionen ein Informationswert berechnet werden.
5.2 Aggregatfunktionen
Folgende Aggregatfunktionen können verwendet werden:
Aggregatfunktion
Bedeutung
COUNT (Ausdruck) Anzahl der Datensätze je Gruppe
SUM (Ausdruck)
Summe der Werte von (Ausdruck) je Gruppe
AVG (Ausdruck)
Durchschnitt der Werte von (Ausdruck) je Gruppe
MIN (Ausdruck)
Minimum aller Werte von (Ausdruck) je Gruppe
MAX (Ausdruck)
Maximum aller Werte von (Ausdruck) je Gruppe
Diese Gruppierungsausdrücke werden in der SELECT - Klausel verwendet. Mehrfach auftretende Werte werden innerhalb
der Aggregatfunktionen mehrfach gezählt. Duplikatzählung verhindert man durch Verwendung des Schlüsselwort DISTINCT
innerhalb der Aggregatfunktion.
Bemerkung:
Ist das Ergebnis einer Aggregatfunktion, angewendet auf eine Spalte mit der Domäne INTEGER, eine Dezimalzahl, so hängt
es von der verwendeten Datenbank ab, ob als das Ergebnis als INTEGER- Zahl, also unter Vernachlässigung der
Nachkommastellen, oder als Dezimalzanl angezeigt wird.
Beispiel: Aus welchen Wohnorten stammt das Lehrerkollegium? Wie viele Personen kommen jeweils aus einem bestimmten
Ort?
SQL - Anfrage
Ergebnis - Tabelle
SELECT Wohnort, count(*) AS Anzahl
FROM Lehrkraft
GROUP BY Wohnort ;
Bei Ausführung des obigen Befehls gruppiert das System in einem ersten Schritt die Datentupel aller Lehrkräfte nach dem
Wohnort, in einem zweiten Schritt werden die Datensätze pro Wohnort gezählt und dann ausgegeben.
Die Angabe eines Sterns innerhalb der COUNT- Klausel weist das System an, jeweils alle Datensätze innerhalb einer
Gruppe zu zählen.
Bemerkung:
ACHTUNG: Außer Aggregaten dürfen in der SELECT - Klausel nur Attribute vorkommen, die in der GROUP BY - Klausel
aufgeführt sind.
5.3 Aggregatfunktion ohne GROUP BY- Klausel
Wird die GROUP BY - Klausel weggelassen und trotzdem eine Aggregatfunktion in der SELECT - Klausel verwendet, so
werden alle Datensätze der Tabelle zu einer einzigen Gruppe zusammengefasst.
Beispiel: Was ist das durchschnittliche Geburtsjahr im Lehrerkollegium?
SQL - Anfrage
Ergebnis - Tabelle
SELECT AVG (Geburtsjahr) AS Durchschnitt
FROM Lehrkraft ;
5.4 Rechenoperationen in SQL
In der SELECT- und der WHERE- Klausel können auch Rechenoperationen durchgeführt werden. Grundsätzlich möglich ist
die Verwendung von +, -, / und * sowie die Verwendung von Klammern. Daneben ist auch die Verwendung von
Funktionen, wie beispielsweise die Wurzelfunktion, möglich, sofern sie von der Datenbank zur Verfügung gestellt werden.
Als Operanden können Zahlen und Ergebnisse von Aggregatfunktionen verwendet werden.
Beispiel: Wie viele Stunden Unterricht werden durchschnittlich pro Fach und pro Klasse erteilt?
SQL - Anfrage
Ergebnis - Tabelle
SELECT SUM (Stundenzahl) / COUNT
(*) AS Durchschnittsstundenzahl
FROM ist_Fachlehrkraft_von ;
Hinweis: Das Ergebnis erhält man einfacher durch Ausnutzung der AVG- Funktion.
Bemerkung:
zum obigen Beispiel
Die Onlinedatenbank schneidet bei der Ergebnisausgabe die Nachkommastellen ab, d.h. das Ergebnis der Anfrage ist 3!
5.5 Übungen
Aufgabe:
Wie viele Lehrerinnen bzw. der Lehrer aus Passau gibt es?
Lösungsvorschlag:
SELECT Geschlecht, COUNT (Geschlecht) AS Anzahl
FROM Lehrkraft
WHERE Wohnort = 'Passau'
GROUP BY Geschlecht ;
bzw.
SELECT Geschlecht, COUNT (*) AS Anzahl
FROM Lehrkraft
WHERE Wohnort = 'Passau'
GROUP BY Geschlecht ;
Aufgabe:
Wie viele Schülerinnen und Schüler pro Konfession gibt es in der elften Klasse?
Lösungsvorschlag:
SELECT Konfession, COUNT (*) AS Anzahl
FROM Schueler
WHERE gehoert_zu = 11
GROUP BY Konfession ;
Aufgabe:
Wie groß sind die Klassenstärken?
Lösungsvorschlag:
SELECT gehoert_zu AS Klasse, COUNT (*) AS Klassenstaerke
FROM Schueler
GROUP BY gehoert_zu ;
Aufgabe:
Zu wie vielen Fächern gibt es eine Fachbetreuung?
Lösungsvorschlag:
SELECT COUNT ( DISTINCT Fach)
FROM hat_Fachbetreuung_in ;
MS ACCESS und die Übungsdatenbank "verstehen" diese Anweisung nicht!
Aufgabe:
Gesucht ist die höchste laufende Nummer innerhalb eines Eintrittsjahres! Zusätzlich ausgegeben werden soll dazu das
jeweilige Eintrittsjahr.
Lösungsvorschlag:
SELECT Eintrittsjahr, MAX (Nr)
FROM Schueler
GROUP BY Eintrittsjahr ;
Aufgabe:
Wie viele Mathematikstunden sind von Lehrkräften zu halten, wenn der Stundenansatz pro Klasse um 2 erhöht wird?
Lösungsvorschlag:
SELECT SUM (Stundenzahl) + (2 * COUNT (*))
FROM ist_Fachlehrkraft_von
WHERE Fach = 'Mathematik' ;
Der SELECT-Ausdruck berechnet die Gesamtanzahl der Mathematikstunden und addiert dazu die Anzahl der Zusatzstunden.
Da pro Klasse die Stundenzahl um 2 erhöht werden muss, berechnet sich diese Anzahl beispielsweise wie oben realisiert: 2
* Anzahl der Datensätze ( = Klassen), bei denen das Attribut Fach den Wert Mathematik besitzt.
6 Gruppierungen mit Bedingungen
6.1 Die HAVING - Klausel
Oft werden bei einer Gruppierung nur die Gruppen gewünscht, die bestimmte Bedingungen erfüllen. Entsprechende
Bedingungen werden in der HAVING - Klausel formuliert. Dabei dürfen Gruppierungsausdrücke verwendet werden.
Beispiel: In welchen Jahren traten mindestens zwei Schülerinnen bzw. Schüler in die Schule ein?
SQL - Anfrage
Ergebnis - Tabelle
SELECT Eintrittsjahr
FROM Schueler
GROUP BY Eintrittsjahr
HAVING COUNT (*) > 1 ;
Das System gruppiert zuerst alle Datentupel gemäß dem Eintrittsjahr und überprüft im Anschluss, welche dieser Gruppen
mehr als ein Tupel enthält. Die entsprechenden Gruppen werden dann ausgegeben.
Grundsätzlich werden Anfragen mit einer GROUP BY - Klausel in folgender Reihenfolge bearbeitet:
1. Auswahl der Tupel durch die WHERE - Klausel
2. Bildung der Gruppen durch die GROUP BY - Klausel
3. Auswahl der Gruppen, die die HAVING - Klausel erfüllen.
6.2 Übungen
Aufgabe:
Zu welchem Fach gibt es nur eine Lehrkraft?
Lösungsvorschlag:
SELECT Fach
FROM hat_Lehrbefaehigung_in
GROUP BY Fach
HAVING COUNT (*) = 1 ;
Aufgabe:
Welche Klassen haben derzeit mehr als 10 Stunden Unterricht? Auszugeben sind die Klasse und deren Gesamtstundenzahl.
Lösungsvorschlag:
SELECT Klasse, SUM (Stundenzahl)
FROM ist_Fachlehrkraft_von
GROUP BY Klasse
HAVING SUM (Stundenzahl) > 10 ;
Aufgabe:
Ist folgende Anfrage vernünftig?
SELECT Konfession
FROM Schueler
WHERE gehoert_zu = 11
GROUP BY Konfession
HAVING COUNT(*) = 0 ;
Lösungsvorschlag:
Es werden nur Konfessionen gruppiert, die auch in der Basistabelle vorkommen. Damit muss wenigstens eine Schülerin oder
ein Schüler der entsprechenden Konfession zugehören. Es gibt also keine Gruppe mit 0 Tupeln. Die Anfrage liefert damit
eine leere Tabelle. Anfragen mit der HAVING - Klausel HAVING COUNT(*) = 0 besitzen keine Aussagekraft.
7 SQL-Anfragen über mehrere Tabellen
7.1 Verknüpfung von Tabellen - Join
Relationale Datenbanken bestehen in der Regel aus sehr vielen Tabellen. Oft werden bei einer SQL-Anfrage Daten
gewünscht, die aus verschiedenen Tabellen stammen. Dazu müssen die Tabellen verknüpft werden. Solche Verknüpfungen
nennt man Joins.
Beispiel: Gewünscht ist eine Liste mit den Klassenleitern und der jeweiligen Klasse.
Die Tabelle Lehrkraft liefert zwar den Namen der in Frage kommenden Personen, aber keine Information über die
Klassenleitung. In der Tabelle Klasse findet man zwar die Personalnummer, nicht aber den Namen des jeweiligen
Klassenleiters.
Die Verknüpfung der Tabellen erfolgt durch Angabe der beteiligten Tabellen in der FROM - Klausel. Häufig haben Attribute,
die aus verschiedenen Tabellen stammen, identische Namen. Wird ein solches Attribut in der SQL-Anfrage verwendet, muss
zusätzlich noch der Tabellenname angegeben werden. Man schreibt: Tabellenname.Attributname.
Beispiel: Der Attributname Name ist zweideutig. Er beschreibt einerseits die Lehrernamen, andererseits die Klasse. In
unserem Bespiel muss der Name der Klasse mit Klasse.Name und der Name der Lehrkraft mit Lehrkraft.Name
angesprochen werden.
Bemerkung:
Ist der Spaltenname eindeutig, kann auf das Voranstellen des Tabellennamen verzichtet werden.
Folgende Query zeigt das Prinzip. Die Bedeutung der WHERE - Klausel wird nachfolgend genauer erklärt.
Beispiel: Gewünscht ist eine Liste der Klassenleiter mit den jeweiligen Klassen.
SQL - Anfrage
Ergebnis - Tabelle
SELECT Klasse.Name,
Lehrkraft.Name
FROM Lehrkraft, Klasse
WHERE PersNr =
Klassenleitung ;
Innerhalb der FROM - Klausel ist eine Umbenennung der Tabellennamen möglich. Dazu stellt SQL das Schlüsselwort AS zur
Verfügung, das aber nicht unbedingt verwendet werden muss. Damit ist beispielsweise folgende Formulierung der obigen
Anfrage möglich:
SELECT l.Name, k.Name
FROM Lehrkraft AS l, Klasse AS k
WHERE l.PersNr = k.Klassenleitung ;
bzw.
SELECT l.Name, k.Name
FROM Lehrkraft l, Klasse k
WHERE l.PersNr = k.Klassenleitung ;
Da PersNr und Klassenleitung eindeutige Attributnamen sind, kann die WHERE -Klausel auch lauten:
WHERE PersNr = Klassenleitung
Innerhalb der FROM - Klausel ist eine Umbenennung der Tabellennamen möglich. Dazu stellt SQL das Schlüsselwort AS zur
Verfügung, das aber nicht unbedingt verwendet werden muss. Damit ist beispielsweise folgende Formulierung der obigen
Anfrage möglich:
SELECT p.Name, d.RaumNr
FROM Professor AS p, Dienstzimmer AS d
WHERE p.PersNr = d.PersNr ;
bzw.
SELECT p.Name, d.Raumnr
FROM professor p, Dienstzimmer d
WHERE p.PersNr = d.PersNr ;
Da name und RaumNr eindeutige Attributnamen sind, kann die SELECT -Klausel auch lauten:
SELECT Name, RaumNr
Bemerkung:
●
ORACLE lässt die Verwendung von AS in der FROM - Klausel nicht zu! Der Aliasname wird einfach hinter den
Tabellennamen geschrieben.
●
MS ACCESS bzw. die Übungsdatenbank akzeptiert beide Möglichkeiten.
7.2 Bedeutung der WHERE-Klausel
Das Weglassen der WHERE - Klausel in obigem Beispiel führt zu nachfolgendem Ergebnis:
Beispiel:
Die Entstehung dieses Ergebnisses kann man sich folgendermaßen vorstellen. Bei einem Join wird jeder Datensatz der
einen Tabelle mit jedem Datensatz der anderen Tabelle verknüpft. Das Ergebnis wird in einer virtuellen Tabelle "abgelegt",
die sowohl die Spalten der einen wie auch der anderen Tabellen enthält.
Aus dieser virtuellen Tabelle werden dann die Spalten l.Name und k.Name ausgewählt und angezeigt. Bis auf die erste und
zwölfte Datenzeile der obigen Tabellen sind aber alle Ausgabezeilen unsinnig, da bei diesen die Werte der Personalnummer
und der Klassenleitung nicht übereinstimmen. Im obigen Beispiel muss deshalb gelten:
l.PersNr = k.Klassenleitung.
Diese Bedingung wird in der WHERE - Klausel angegeben.
Mit Hilfe der WHERE - Klausel können Gleichheitsbedingungen angegeben werden, die unsinnige bzw. nicht aussagekräfte
Kombinationen ausschließen.
7.3 Self-Join
Manchmal wird eine Tabelle mit sich selbst verknüpft. Dieser spezielle Join heißt Self-Join.
Beispiel: Welche Lehrkräfte (Angabe der PersNr) haben die Lehrbefähigung für mehr als ein Fach?
SQL - Anfrage
Ergebnis - Tabelle
SELECT DISTINCT l1.Lehrkraft
FROM hat_Lehrbefaehigung_in l1,
hat_Lehrbefaehigung_in l2
WHERE l1.Lehrkraft = l2.Lehrkraft AND NOT
(l1.Fach = l2.Fach) ;
In diesem Beispiel wird ein Selfjoin verwendet. Zur Auswertung der Anfrage wird eine (virtuelle) Tabelle mit den vier Spalten
l1.Lehrkraft, l1.Fach, l2.Lehrkraft und l2.Fach berechnet, aus der die Datensätze ausgewählt werden, bei
denen die Lehrkraft- Attribute übereinstimmen und die Fach- Attribute verschieden sind. Anschließend wird von diesen
Datensätzen die erste Spalte unter Entfernung von Duplikaten ausgegeben.
7.4 Übungen
Aufgabe:
Welche Fächer dürfen die einzelnen Lehrkräfte unterrichten? Gewünscht ist die Ausgabe der Namen von Lehrkraft und
Fach.
Lösungsvorschlag:
SELECT Name, Fach
FROM Lehrkraft, hat_Lehrbefaehigung_in
WHERE PersNr = Lehrkraft ;
Aufgabe:
Welche Lehrerin bzw. welcher Lehrer (Angabe des Namens) ist Fachbetreuer in Deutsch?
Lösungsvorschlag:
SELECT Name
FROM Lehrkraft, hat_Fachbetreuung_in
WHERE PersNr = Lehrkraft AND Fach= 'Deutsch' ;
Aufgabe:
Gesucht sind die Namen der Lehrkräfte, die die Schülerin Falbala unterrichten?
Lösungsvorschlag:
SELECT l.Name
FROM Lehrkraft l, Schueler s, ist_Fachlehrkraft_von i
WHERE i.Klasse = s.gehoert_zu AND i.Lehrkraft = l.PersNr AND s.Name = 'Falbala' ;
Aufgabe:
Gibt es Lehrkräfte (Angabe der PersNr), die mehr als ein Fach in derselben Klasse unterrichten?
Lösungsvorschlag:
SELECT a.Lehrkraft
FROM ist_Fachlehrkraft_von a, ist_Fachlehrkraft_von b
WHERE a.Lehrkraft = b.Lehrkraft AND a.Klasse = b.Klasse AND NOT (a.Fach = b.Fach) ;
Hier handelt es sich um einen Self-Join, da die Tabelle ist_Fachlehrkraft_von mit sich selbst gejoint wird.
8 Geschachtelte SQL-Anfragen mit Vergleichsoperatoren
8.1 Teilanfragen (Subqueries) mit einem Ergebnis
In der WHERE- Klausel kann sich eine Bedingung auch auf eine untergeordnete SELECT- Anfrage beziehen.
In vielen Fällen liefert die untergeordnete Anfrage genau ein Ergebnis zurück.
Beispiel: Gesucht ist der Klassenleiter der fünften Klasse.
SQL - Anfrage
Ergebnis - Tabelle
SELECT Name
FROM Lehrkraft
WHERE PersNr = (
SELECT Klassenleitung
FROM Klasse
WHERE Name = 5
) ;
Bemerkung:
●
●
Die Domäne (Definitionsmenge) des Attributs Klasse.Name ist INTEGER, weshalb keine Hochkommata - die bei
Zeichenketten benötigt werden - verwendet werden.
Anfragen, die mittels einer Unteranfrage gelöst werden, können in der Regel auch mittels eines Joins berechnet
werden und umgekehrt.
Zur Ermittlung der Ergebnistabellen wird dazu im ersten Schritt die untergeordnete Anfrage
SELECT Klassenleitung
FROM Klasse
WHERE Name = 5 ;
ausgewertet. Die Zwischenergebnistabelle enthält den Wert der Spalte Klassenleitung im Datensatz der Klasse 5, also
die Personalnummer 245. Mit diesem Zwischenergebnis wird die übergeordnete Query beantwortet, die dann der Anfrage
SELECT Name
FROM Lehrkraft
WHERE PersNr = 15 ;
entspricht. Diese Anfrage funktioniert aufgrund der Verwendung des Gleichheitszeichens nur dann, wenn die Ergebnistabelle
der untergeordneten Anfrage genau einen Wert liefert.
Zur Ermittlung der Ergebnistabellen wird dazu im ersten Schritt die untergeordnete Anfrage
SELECT PersNr
FROM Professor
WHERE Name = 'Zuse' ;
ausgewertet. Die Zwischenergebnistabelle enthält den Wert der Spalte PersNr im Datensatz des Professors Zuse, also die
Personalnummer 36. Mit diesem Zwischenergebnis wird die übergeordnete Query beantwortet, die dann der Anfrage
SELECT RaumNr
FROM Dienstzimmer
WHERE PersNr = 36 ;
entspricht. Diese Anfrage funktioniert aufgrund der Verwendung des Gleichheitszeichens nur dann, wenn die Ergebnistabelle
der untergeordneten Anfrage genau einen Wert liefert.
Als Vergleichsoperatoren können neben dem Gleichheitszeichen auch
ist aber immer, dass die Unteranfrage nur einen Wert zurückliefert!
8.2 Übungen
,
;,
und
verwendet werden. Voraussetzung
Aufgabe:
Welche Lehrkraft ist Fachbetreuer in Sport?
Lösungsvorschlag:
SELECT Name
FROM Lehrkraft
WHERE PersNr = (
SELECT Lehrkraft
FROM hat_Fachbetreuung_in
WHERE Fach = 'Sport'
) ;
Achtung: Die Anfrage funktioniert natürlich nur dann, wenn man weiß, dass es nur eine Fachbetreuerin bzw. einen
Fachbetreuer in Sport gibt. Ist man sich nicht sicher, ist der Operator IN zu wählen,
Aufgabe:
Welche Lehrkräfte sind älter als der Durchschnitt des Kollegiums?
Lösungsvorschlag:
SELECT Name
FROM Lehrkraft
WHERE Geburtsjahr < (
SELECT AVG (Geburtsjahr)
FROM Lehrkraft
) ;
Aufgrund der Aggregatfunktion AVG ist es sicher, dass die Unteranfrage nur einen Wert zurückliefert!
9 Geschachtelte SQL-Anfragen mit speziellen Operatoren
9.1 Teilanfragen mit mehr als einem Ergebnis
In vielen Fällen kann man nicht garantieren, dass in der Unteranfrage nur ein Ergebnis zurückgeliefert wird.
Bemerkung:
Im Zweifelsfall sollte man auf geschachtelte Anfragen mit
,
,
,
und = verzichten!
Für untergeordnete Anfragen, die mehrere Ergebnisse, also eine Ergebnismenge, zurückliefern, stellt SQL spezielle
Operatoren zur Verfügung.
9.2 Eine einfache Möglichkeit mit dem IN - Operator
Der IN - Operator eignet sich auch zur Auswahl von Datensätzen, wenn das zu vergleichende Attribut aus einer kleinen,
diskreten Menge stammt. Die Auswahlmenge wird in Form ihrer Elemente hinter dem IN - Operator angegeben.
Beispiel: Welche Schülerinnen und Schüler - unter Angabe von Name und Klasse - sind katholisch oder evangelisch?
SQL - Anfrage
Ergebnis - Tabelle
SELECT Name, gehoert_zu AS Klasse
FROM Schueler
WHERE Konfession IN ('rk', 'ev') ;
Diese Verwendung ersetzt eine (in der Regel längere) Formulierung mit OR- Operatoren.
9.3 Erweiterte Möglichkeiten
Die Auswahlmenge kann auch auf Grund einer untergeordneten Query erzeugt werden. Dazu stehen folgende Operatoren
zur Verfügung:
Name des Operators
Bedeutung
IN
Test auf Mengenmitgliedschaft
NOT IN
Test, ob ein Element nicht in einer Menge ist.
( =,
, ...) ANY
Test, ob es mindestens ein Element in der Unteranfrage gibt, das den Vergleich mit dem
Vergleichswert erfüllt.
( =,
, ...) ALL
Test, ob alle Elemente in der Unteranfrage den Vergleich mit dem Vergleichswert erfüllen.
EXISTS
Test, ob die von der Unteranfrage bestimmte Menge nicht leer ist.
NOT EXISTS
Test, ob die von der Unteranfrage bestimmte Menge leer ist.
UNIQUE
Test, ob das Ergebnis duplikatfrei ist.
Beispiel: Welche Lehrkräfte haben eine Fachbetreuung?
SQL - Anfrage
Ergebnis - Tabelle
SELECT Name
FROM Lehrkraft
WHERE PersNr IN (
SELECT Lehrkraft
FROM hat_Fachbetreuung_in
) ;
Beispiel: Welche Lehrkraft ist am jüngsten?
SQL - Anfrage
SELECT Name
FROM Lehrkraft
WHERE Geburtsjahr >= ALL (
SELECT Geburtsjahr
FROM Lehrkraft
) ;
9.4 Übungen
Aufgabe:
Welche Lehrerinnen sind 1950, 1952, 1956 oder 1957 geboren?
Ergebnis - Tabelle
Lösungsvorschlag:
SELECT Name
FROM Lehrkraft
WHERE Geburtsjahr IN (1950, 1952, 1956, 1957) AND Geschlecht = 'w' ;
Aufgabe:
Welche Lehrkräfte haben die Fachbetreuung in Mathematik?
Lösungsvorschlag:
SELECT Name
FROM Lehrkraft
WHERE PersNr IN (
SELECT Lehrkraft
FROM hat_Fachbetreuung_in
WHERE Fach = 'Mathematik')
;
Aufgabe:
Welchen Lehrkräften, alphabetisch absteigend sortiert, wurden bereits Klassen zugeteilt?
Lösungsvorschlag:
SELECT Name
FROM Lehrkraft
WHERE PersNr = ANY (
SELECT Lehrkraft
FROM ist_Fachlehrkraft_von
)
ORDER BY Name DESC ;
10 Unteranfragen in der SELECT-Klausel, Korreliertheit
10.1 Unteranfragen in der SELECT-Klausel
Auch in der SELECT- Klausel ist die Verwendung von Unteranfragen möglich.
10.2 Korrelierte Unteranfragen
Unteranfragen können sich auch auf Attribute der übergeordneten Anfragen beziehen. In diesem Fall spricht man von
einer korrelierten Unteranfrage. Bei namensgleichen Attributen in der Ober- und Unteranfrage müssen wieder die
Tabellennamen bzw. deren Aliasnamen verwendet werden.
Beispiel: Welche Fachbetreuer unter Angabe des Faches gibt es?
SQL - Anfrage
Ergebnis - Tabelle
SELECT Fach, ( SELECT Name
FROM Lehrkraft
WHERE Lehrkraft = PersNr ) AS
Fachbetreuung
FROM hat_Fachbetreuung_in ;
Bemerkung:
●
Im obigen Beispiel ist das Attribut Name mehrdeutig, damit muss der entsprechende Tabellenname oder Aliasname
zusätzlich angegeben werden. Bei eindeutigen Attributen wie PersNr ist das nicht notwendig.
●
Die obige SQL-Anfrage soll lediglich das Prinzip korrelierter Unteranfragen zeigen. Die Fragestellung kann mit Hilfe
eines Joins einfacher formuliert werden!
SELECT Fach, Name AS Fachbetreuung
FROM Lehrkraft , hat_Fachbetreuung_in
WHERE Lehrkraft = PersNr ;
10.3 Übungen
Aufgabe:
Welche Lehrkräfte haben die Lehrbefähigung im Fach Informatik?
Lösungsvorschlag:
SELECT Name
FROM Lehrkraft
WHERE PersNr = (
SELECT Lehrkraft
FROM hat_Lehrbefaehigung_in
WHERE Fach = 'Informatik' AND PersNr = Lehrkraft)
;
Die Unteranfrage bezieht sich in PersNr = Lehrkraft auf das Attribut PersNr der übergeordneten Anfrage.
Aufgabe:
Welche Lehrkräfte haben noch keine Unterrichtstunden?
Lösungsvorschlag:
SELECT Name
FROM Lehrkraft
WHERE NOT EXISTS (
SELECT *
FROM ist_Fachlehrkraft_von
WHERE PersNr = Lehrkraft)
;
Die Unteranfrage bezieht sich in PersNr = Lehrkraft auf das Attribut PersNr der übergeordneten Anfrage.
Aufgabe:
Gesucht sind die Personalnummern der Lehrkräfte, die gleichzeitig in Fachbetreuung und Klassenleitung eingebunden sind.
Lösungsvorschlag:
SELECT Lehrkraft
FROM hat_Fachbetreuung_in
WHERE Lehrkraft IN (
SELECT Klassenleitung
FROM Klasse
WHERE Klassenleitung = Lehrkraft)
;
Die Unteranfrage bezieht sich in Klassenleitung = Lehrkraft auf das Attribut Lehrkraft der übergeordneten
Anfrage.
11 Verknüpfen mehrerer Ergebnismengen
11.1 UNION-, INTERSECT- und EXCEPT- Operation
Die klassischen Operationen der Mengenlehre werden auch von SQL zur Verfügung gestellt.
Mengenoperation
SQL - Schlüsselwort
Vereinigung von Mengen UNION
Schnitt von Mengen
INTERSECT
Differenz von Mengen
EXCEPT
Damit können die Ergebnismengen mehrerer SQL-Anfragen mit Mengenoperationen verknüpft werden.
Beispiel: Welche Kolleginnen bzw. Kollegen haben gleichzeitig Fachbetreuung und Klassenleitung?
SELECT Lehrkraft AS Doppelfunktion
FROM hat_Fachbetreuung_in
INTERSECT
SELECT Klassenleitung
FROM Klasse ;
Bei der Verwendung der Mengenoperatoren ist zu beachten:
●
Die Ergebnisse beider SELECT - Anweisungen müssen bezüglich Anzahl und Typ der Attribute identisch sein.
●
Ohne das Schlüsselwort ALL werden vor der Mengenoperation Duplikate aus den beiden Teilergebnissen entfernt.
●
Die beteiligten SELECT - Anweisungen dürfen nicht in Klammern geschrieben werden.
Bemerkung:
●
Statt dem Schlüsselwort EXCEPT verwendet ORACLE die Bezeichnung MINUS.
●
In MS ACCESS und der Übungsdatenbank stehen die Mengenoperatoren INTERSECT und EXCEPT nicht zur
Verfügung.
11.2 Übungen
Aufgabe:
Gesucht sind alle Schülerinnen und Schüler mit Ausnahme der Bekenntnislosen.
Lösungsvorschlag:
SELECT gehoert_zu AS Klasse, Name
FROM Schueler
EXCEPT
SELECT gehoert_zu, Name
FROM Schueler
WHERE Konfession = 'bk' ;
Aufgabe:
Gesucht sind die Namen des Lehrerkollegiums und der Schülerschaft.
Lösungsvorschlag:
SELECT Name
FROM Lehrkraft
UNION
SELECT Name
FROM Schueler ;
Aufgabe:
Welchen Lehrkräften (Angabe der Personalnummer) wurden bereits Klassen zugeteilt?
Lösungsvorschlag:
SELECT PersNr
FROM Lehrkraft
INTERSECT
SELECT Lehrkraft
FROM ist_Fachlehrkraft_von ;
12 Weitere Sprachkonstrukte
12.1 Der LIKE-Operator
Bei der Suche nach ähnlichen oder nicht genau bekannten Begriffen erweist sich oft die Konstruktion LIKE als hilfreich. SQL
stellt dabei zwei Hilfen zur Verfügung:
Zeichen
Bedeutung
%
Platzhalter für beliebig viele Zeichen
_
Platzhalter für genau ein Zeichen
Bemerkung:
Entsprechende Möglichkeiten werden auch in MS ACCESS zur Verfügung gestellt:
● * als Platzhalter für beliebig viele Zeichen,
●
? als Platzhalter für genau ein Zeichen
Beispiel: Welche Namen der Lehrkräfte enthalten die Buchstabenfolge "us"?
SQL - Anfrage
SELECT Name
FROM Lehrkraft
WHERE Name LIKE '%us%' ;
Ergebnis - Tabelle
Beispiel: Welche Schülernamen haben an der vorletzten Stelle den Buchstaben i?
SQL - Anfrage
Ergebnis - Tabelle
SELECT DISTINCT Name
FROM Schueler
WHERE Name LIKE '%i_' ;
Um Duplikate bei den Schülernamen auszuschließen, wurde das Schlüsselwort DISTINCT verwendet.
12.2 Übungen
Aufgabe:
Welche Fächer mit dem Anfangsbuchstaben M gibt es?
Lösungsvorschlag:
SELECT Name
FROM Fach
WHERE Name LIKE 'M%' ;
Aufgabe:
Welche Lehrernamen haben an der zweiten Stelle den Buchstaben u?
Lösungsvorschlag:
SELECT Name
FROM Lehrkraft
WHERE Name LIKE '_u%' ;
Aufgabe:
Heißt die Schülerin aus der 11. Klasse Fabala oder Falbala?
Lösungsvorschlag:
SELECT Name
FROM Schueler
WHERE Name LIKE 'Fa%bala' ;
13 Das Erstellen von Sichten (Views)
13.1 Was sind Sichten (Views)
Um Benutzern eines Datenbanksystems eine Sicht auf die für sie relevanten Daten zu ermöglichen, verwendet man
sogenannte Sichten oder Views . Views kann man als virtuelle Tabellen auffassen, die ihre Einträge nicht selbst speichern,
sondern bei Bedarf nach einer vorher festgelegten Vorschrift aus Basistabellen oder anderen bereits existierenden Views
berechnen.
Bemerkung:
Views werden im Drei-Schichten-Modell von ANSI/SPARC als eigene Schicht eingeführt.
Beispiel: Die Schuldatenbank soll zum Informationssystem für Eltern erweitert werden. Diese dürfen zwar beispielsweise die
Namen der Fachbetreuer einsehen, nicht aber deren persönlichen Daten. Mittels einer View können die benötigten
Informationen aus den Tabellen Lehrkraft und hat_Fachbetreuung_in berechnet werden.
13.2 Die CREATE VIEW - Anweisung
Der CREATE VIEW-Befehl dient zur Festlegung einer Sicht.
Beispiel: Die zulässigen Daten des obigen Beispiels kann man durch die SQL-Abfrage
SELECT Fach, Name AS Betreuung
FROM Lehrkraft, hat_Fachbetreuung_in
WHERE PersNr = Lehrkraft ;
herausfiltern. Mit Hilfe von CREATE VIEW kann dieser Anfrage ein Name, beispielsweise Fachbetreuung, zugeordnet
werden und dann unter diesem Namen abgespeichert. Die entsprechende SQL-Anweisung lautet dann
CREATE VIEW Fachbetreuung AS
SELECT Fach, Name AS Betreuung
FROM Lehrkraft, hat_Fachbetreuung_in
WHERE PersNr = Lehrkraft ;
Views sind damit nichts anderes als benannte SQL-Abfragen . Die Ergebnistabellen von Views werden beim Aufruf der
View immer neu berechnet.
Bemerkung:
In MS ACCESS können Datenbankanfragen unter Verwendung von Namen abgespeichert werden. Diese Anfragen sind
damit nichts anderes als Views.
13.3 Syntaxdiagramm
13.4 Arbeiten mit Views
Views können bzgl. Anfragen genauso behandelt werden wie Basistabellen.
Beispiel: Welche Lehrkräfte haben die Fachbetreuung im Fach Mathematik?
SQL - Anfrage
Ergebnis - Tabelle
SELECT Betreuung AS
Mathematikfachbetreuung
FROM Fachbetreuung
WHERE Fach = 'Mathematik' ;
Beispiel: Gibt es einen Fachbetreuer von Wahlfächern?
SQL - Anfrage
Ergebnis - Tabelle
SELECT Fach, Betreuung
FROM Fachbetreuung, Fach
WHERE Fach = Name AND Pflichtfach =
'nein' ;
13.5 Übungen
Aufgabe:
Es soll eine View mit dem Namen Klasse_5 erstellt werden, die die Namen aller Schüler der fünften Klasse zeigt.
Lösungsvorschlag:
CREATE VIEW Klasse_5 AS
SELECT Name
FROM Schueler
WHERE gehoert_zu = 5 ;
Aufgabe:
Gibt es in der fünften Klasse einen Schüler mit dem Namen Asterix? Erstellen Sie eine View Schuelersuche unter
Ausnutzung der Lösung der Vorgängeraufgabe.
Lösungsvorschlag:
CREATE VIEW Schuelersuche AS
SELECT Name
FROM Klasse_5
WHERE Name = 'Asterix' ;
Aufgabe:
Gewünscht ist eine Sicht mit dem Namen Lehrkraefte_5, die eine Liste mit den Lehrkräften, die in der Klasse 5
unterrichten, und dem entsprechenden Fach berechnet.
Lösungsvorschlag:
CREATE VIEW Lehrkraefte_5 AS
SELECT Name, Fach
FROM Lehrkraft, ist_Fachlehrkraft_von
WHERE PersNr = Lehrkraft AND Klasse = 5 ;
14 Das Löschen von Sichten (Views)
14.1 Die DROP VIEW - Anweisung
Der DROP VIEW - Befehl erlaubt das Löschen einer Sicht.
Beispiel: Die Sicht Fachbetreuung wird nicht mehr benötigt.
DROP VIEW Fachbetreuung;
Bemerkung:
Bei MS ACCESS und der Übungsdatenbank erfolgt das Löschen von Views (leider) über den DROP TABLE- Befehl.
14.2 Syntaxdiagramm
15 SQL mit MS ACCESS
MS ACCESS stellt zum Erstellen von Tabellen, Abfragen usw. im Allgemeinen Assistenten zur Verfügung. Trotzdem ist auch
das direkte Arbeiten mit SQL möglich!
15.1 Ansichten in MS ACCESS
MS ACCESS unterscheidet bei Tabellen und Anfragen grundsätzlich:
● die Datenblattansicht: Damit kann die Instanz einer Basistabelle bzw. die Ergebnistabelle einer Anfrage angezeigt
werden. Diese Ansicht erlaubt auch die Eingabe, die Änderung und das Löschen von Daten in einer Basistabelle.
●
die Entwurfsansicht: Hier können Tabellen bzw. Anfragen "ohne"" SQL erzeugt werden.
●
die SQL-Ansicht: Diese erlaubt das direkte Arbeiten mit SQL.
15.2 Die SQL-Ansicht in MS ACCESS
Die SQL-Ansicht erlaubt
● die Definition von Tabellen (CREATE TABLE),
●
das Ändern von Tabellen (ALTER TABLE),
●
das Löschen von Tabellen (DROP TABLE),
●
das Einfügen von Daten in Tabellen (INSERT INTO),
●
das Ändern von Daten in Tabellen (UPDATE),
●
das Löschen von Daten in Tabellen (DELETE) und
●
die Erstellung von SQL-Anfragen (SELECT).
15.3 Aufruf der SQL-Ansicht
1. Wählen Sie als Objekt Abfragen .
2. Klicken Sie auf Neu in der Symbolleiste des Datenbankfensters.
3. Klicken Sie im Dialogfeld Neue Abfrage auf Entwurfsansicht und dann auf OK.
4. Klicken Sie im Dialogfeld Tabelle anzeigen auf Schließen, ohne Tabellen oder Abfragen hinzuzufügen.
5. Die SQL-Ansicht erhält man durch
❍ Auswahl des Punktes SQL-Ansicht im Menüpunkt Ansicht Ansicht oder
❍
❍
Auswahl des Punktes SQL-Ansicht im Kontextmenü (rechte Maustaste), falls sich die Maus im
Abfragefenster befindet oder
Auswahl des des Punktes SQL-Ansicht in der Icon-Leiste
6. Geben Sie den SQL-Befehl in das SQL-Fenster ein.
7. Um die Abfrage auszuführen, klicken Sie in der Symbolleiste auf Ausführen. Unter dem Menüpunkt Abfrage finden
Sie den Punkt Ausführen ebenfalls.
Bemerkung:
Der eingegebene SQL-Befehl kann unter einem frei wählbaren Namen abgespeichert werden. Es gilt:
● Anfragen an die Datenbank können durch Doppelklicken des Namens ohne Probleme mehrmals ausgeführt werden,
da dabei keine Daten hinzugefügt, verändert oder gelöscht werden.
●
Einmalige Aktionen, z.B. Ausführen von create table oder insert into, sollten nur einmal ausgeführt werden!
Ist nämlich beispielsweise eine Tabelle mit dem Namen Beispiel bereits erstellt und mit Daten gefüllt, wird durch den
Befehl create table Beispiel ... die Tabelle samt Inhalt gelöscht und eine neue (leere) Tabelle Beispiel erstellt!