SQL-Abfragen: Unterschied zwischen den Versionen

Aus IV1
← Zum vorherigen Versionsunterschied
Hans4mido (Diskussion | Beiträge)
633 Bearbeitungen
K Textersetzung - „mussswiki.idv.edu“ durch „mussswiki.idb.edu“
 
(102 dazwischenliegende Versionen von 6 Benutzern werden nicht angezeigt)
Zeile 1: Zeile 1:
<!--<yambe:breadcrumb>SQL|SQL</yambe:breadcrumb>-->
<div class='noprint'><yambe:breadcrumb>SQL|SQL</yambe:breadcrumb></div>
{{Kurzform|Die Lerneinheit "SQL-Abfragen" dient der Erläuterung unterschiedlicher Anwendungsfälle der SQL-Anweisung "SELECT", die jeweils mit interaktiv ausführbaren Beispielen hinterlegt sind. Die interaktiv ausführbaren Beispiele beziehen sich auf die Trainingsdatenbank [http://sql.idv.edu].}}
{{Kurzform|Die Lerneinheit "SQL-Abfragen" dient der Erläuterung unterschiedlicher Anwendungsfälle der SQL-Anweisung "SELECT", die jeweils mit interaktiv ausführbaren Beispielen hinterlegt sind. Diese Beispiele beziehen sich auf die Trainingsdatenbank [https://sql.idb.edu].}}




Zeile 7: Zeile 7:
==Die SELECT Anweisung==
==Die SELECT Anweisung==


Relationale Datenbanken können mit Hilfe der '''SELECT'''-Anweisung ausgewertet werden, wenn die entsprechenden Zugriffsrechte vorliegen. Nachfolgende Beispiele beziehen sich auf die Implementierung eines SQL-Trainingsservers, welcher als interaktive Anwendung auch unter [http://sql.idv.edu/ http://sql.idv.edu] erreichbar ist. Die Zugriffsrechte auf die Lehr- und Übungsdatenbank erlauben lediglich die Anwendung der SELECT-Anweisung. Die Ausführung von Anweisungen, welche die Struktur oder den Inhalt der Datenbank ändern könnten, werden abgewiesen.
Relationale Datenbanken können mit Hilfe der '''SELECT'''-Anweisung ausgewertet werden, wenn die entsprechenden Zugriffsrechte vorliegen. Nachfolgende Beispiele beziehen sich auf die Implementierung eines SQL-Trainingsservers, welcher als interaktive Anwendung auch unter [https://sql.idb.edu/ https://sql.idb.edu] erreichbar ist. Die Zugriffsrechte auf die Lehr- und Übungsdatenbank über die vorliegende Schnittstelle erlauben lediglich die Anwendung der SELECT-Anweisung. Das Ausführun von Anweisungen, welche die Struktur oder den Inhalt der Datenbank ändern könnten, werden abgewiesen.


Den interaktiven Abfragen liegt folgendes Datenmodell zu Grunde:
Den interaktiven Abfragen liegt folgendes Datenmodell zu Grunde:
Zeile 13: Zeile 13:
[[Datei:Datenmodell beispieldatenbank.gif]]
[[Datei:Datenmodell beispieldatenbank.gif]]


== Ausgeben von Datensätzen ==
== Auswählen und Ausgeben von Datensätzen ==


Mit dem SELECT-Schlüsselwort lassen sich bestimmte Datensätze aus einer Datenbank auslesen, die dann später anderwärtig (z. B. durch eine Script- oder Programiersprache) weiterverarbeitet werden. Als Beispieltabelle kommt hier die Tabelle ''Kunde'' (und später auch die Tabelle ''PLZ'') aus der Trainings-Datenbank zum Einsatz.
Bei der Formulierung von Abfragen mittels SELECT-Anweisung ist die Anwendung von mengentheoretischen Grundkenntnissen sehr hilfreich. Grundsätzlich sind mittels SELECT alle Datensätze aller Tabellen einer Datenbank auswählbar (selektierbar). Die Kunst der richtigen Anwendung besteht aber darin, durch das Formulieren von Schnitt- und Vereinigungsmengen das richtige Ergebnis (= der gestellten Aufgabe entsprechende Menge) zu bilden. Beispiele für Anfragen an die vorliegenden Übungs-Datenbank sind:
 
* Die vollständigen Adressen aller Kunden in Nieder- und Oberösterreich.
* Eine Liste aller Chemiebücher des Verlages 'manz'.
* Die kumulierten Umsätze des Jahres 2000 aller Kunden, aufgelistet nach Bundesländern.
 
Die vorliegende Lerneinheit soll Sie befähigen, die soeben formulierten Fragen zu beantworten.
 
Alle Ergebnisse einer SELECT-Anweisung werden in Form von Listen (bzw. Tabellen) erzielt. In der Praxis sind die erzielten Ergebnisse Teil einer (Computer)Anwendung, welche aus dem unermesslichen Repertoire an Anwendungen stammt und an dieser Stelle keine weitere Behandlung mehr bedarf. Siehe: [[Datenbank-Grundlagen#Was_sind_und_wozu_dienen_Datenbanken.3F | Datenbankgrundlagen]].
 
Die Idee der Auswertung dieser Übungs-Datenbank ist die Weiterverarbeitung in Tabellenkalkulationsprogrammen. Zu diesem Zwecke wird am Ende jedes Ergebnisses (jeder Ausgabe) die Funktion "Download as CSV" angeboten.
 
== SELECT- Die Grundform ==
 
Das erste Beispiel erzeugt eine Liste aller Vornamen der Kunden (aus der Tabelle ''Kunde''). Die Grundform verlangt mindestens die Komponenten SELECT und FROM. Nach der Komponente SELECT wollen die Namen der Spalten (Felder) genannt werden, nach dem FROM jene Tabelle(n) aus denen die gesuchten Felder stammen. Das Einstiegsbeispiel such alle Vornamen der Kunden.


Eine Liste aller Vornamen aus der Tabelle ''Kunde'' kann noch über einen sehr einfachen Befehl erstellt werden.
<pre>SELECT Vorname
<pre>SELECT Vorname
FROM Kunde</pre>
FROM Kunde</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="POST" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Vorname FROM Kunde">
<input type="hidden" name="select" value="SELECT Vorname FROM Kunde">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
Zeile 28: Zeile 41:
</html>
</html>


Möchte man mehr als ein Feld aufgelistet haben, so werden diese durch Komma getrennt nacheinander angeführt. Nachfolgendes Beispiel sucht nach den Inhalten der Felder ''Vorname'', ''Nachname'' und ''Plz'' der Kunden (aus der Tabelle ''Kunde'').


Um mehrere Spalten auflisten zu lassen, müssen diese (mit Komma getrennt) in der Abfrage angegeben werden. Folgend werden nur die Spalten ''Vorname'', ''Nachname'' und ''Plz'' aller Datensätze der Tabelle ''Kunde'' ausgegeben.
<pre>SELECT Vorname, Nachname, Plz
<pre>SELECT Vorname, Nachname, Plz
FROM Kunde</pre>
FROM Kunde</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Vorname, Nachname, Plz FROM Kunde">
<input type="hidden" name="select" value="SELECT Vorname, Nachname, Plz FROM Kunde">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
Zeile 40: Zeile 53:
</html>
</html>


Bemerkenswert ist, dass alle Datensätze der Tabelle "Kunde" ausgewählt und ausgegeben werden und diese prinzipiell unsortiert sind.
Ist die Anzahl der Felder, bzw. deren Namen unbekannt, so führt folgendes SELECT zur Auswahl aller Datensätze mit all ihren Feldern. Der gesamte Inhalt der Tabelle "Kunde" wird demnach selektiert und ausgegeben. 999 Kunden sollen gefunden und angezeigt werden.


Oft ist aber unbekannt, wie alle Felder in einer Tabelle heißen. In so einem Fall, oder um einfach alle Felder in die Abfrage einzubeziehen, kann man anstatt der Spalten einfach den sogenannten Joker (das Multiplikationszeichen "*") einsetzen.
<pre>SELECT *
<pre>SELECT *
FROM Kunde</pre>
FROM Kunde</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT * FROM Kunde">
<input type="hidden" name="select" value="SELECT * FROM Kunde">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
Zeile 52: Zeile 67:
</html>
</html>


== Sortieren der Datensätze und Ausschließen von Wiederholungen ==
Gleiche Inhalte von Feldern in unterschiedlichen Datensätzen ist keine Seltenheit. Es würde überraschen, wenn bei 999 Kunden manche Vornamen nicht mehrfach vorkämen. Eine einfache Methode dies zu überprüfen ist, die Reihenfolge der Vornamen der Kunden alphabetisch zu sortieren. Das SELECT-FROM wird um die Komponente ORDER BY ergänzt. "ASC" steht für ascending, was aufsteigend sortiert bedeutet.
<pre>SELECT Vorname
FROM Kunde
ORDER BY Vorname ASC</pre>
<html>
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Vorname FROM Kunde ORDER BY Vorname ASC">
<input type="submit" value="Ausführen">
</form>
</html>


== Ausschließen von Wiederholungen ==
Haben Sie das Ergebnis überprüft? Wer hätte gedacht, dass gleich zu Beginn der Liste 9 Achims angeführt werden? Sollen alle Duplikate aus der Liste entfernt werden, so kommt die Komponente DISTINCT zum Einsatz. Jeder Vorname wird nur eine einziges Mal angeführt.


Öfters kommt es nun vor, dass Inhalte gewisser Spalten mehrfach in den Datensätzen vorkommen. Soll z. B. eine Liste aller Nachnamen erstellt werden, so könnte es durchaus passieren, dass mehrere Personen in der Tabelle den selben Nachnamen haben. Damit jeder Nachname aber wirklich nur einmal vorkommt, wird der Term '''DISTINCT''' hinter SELECT gestellt.
<pre>SELECT DISTINCT Vorname
<pre>SELECT DISTINCT Nachname
FROM Kunde
FROM Kunde</pre>
ORDER BY Vorname ASC</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT DISTINCT Nachname FROM Kunde">
<input type="hidden" name="select" value="SELECT DISTINCT Vorname FROM Kunde ORDER BY Vorname ASC">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
</form>
</form>
</html>
</html>


== Selektieren von Datensätzen - Bilden von Teil- und Vereinigungsmengen ==


== Auswählen von Datensätzen ==
Zu Beginn dieser Lerneinheit war von mengentheoretischen Überlegungen die Rede. Die Komponente DISTINCT hat zwar zur Bildung einer Teilmenge aus der Menge aller Vornamen bereits beigetragen, aber diese Methode ist für die meisten Bedarfe nicht ausreichend.


Um bestimmte Zeilen (Datensätze) einer Tabelle auszuwählen, muss angegeben werden, welche Bedingung für diese Zeilen gelten soll. Die Bedingungen werden in der '''WHERE'''-Komponente definiert. Sie enthalten Operatoren, für die nachfolgende Reihenfolge anzuwenden ist.
Die WHERE-Komponente in Verbindung mit den nachfolgend angeführten Operatoren ermöglicht das Bilden von Vereinigungs- und Teilmengen unter Verwendung von Klammern, Vergleichsperatoren, Rechenoperationen und logischen Verbindungen.


# ( )
# ( )
# * /
# * /
# + -
# + -
# = <> > <
# = <> > < like
# NOT AND OR
# NOT AND OR


Die folgende Anweisung gibt die Spalten ''Vorname'' und ''Nachname'' all jener Datensätze in der Tabelle ''Kunde'' aus, die im Feld ''Vorname'' "Michael" entsprechen.
Neun Kunden mit dem Vornamen Achim befinden sich in der Datenbank. Gesucht sind nun der Vorname, der Nachname und die Postleitzahl jener Kunden, deren Vornamen auf Achim lauten. Die Treffer sollten aufsteigend nach dem Nachnamen aufgelistet werden.
<pre>SELECT Vorname, Nachname
 
<pre>SELECT Vorname, Nachname, PLZ
FROM Kunde
FROM Kunde
WHERE Vorname='Michael'</pre>
WHERE Vorname='Achim'
ORDER BY Nachname ASC</pre>
 
<html>
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Vorname, Nachname, PLZ FROM Kunde WHERE Vorname='Achim' ORDER BY Nachname ASC">
<input type="submit" value="Ausführen">
</form>
</html>
 
Wie wäre es nun mit der eingangs erwähnten Auflistung aller Chemiebücher, des Verlages 'manz' ? Die Lösung soll in kleinen Schritten erfolgen. Zuerst werden die Chemiebücher gesucht. Chemiebücher enthalten im Titel den Text "Chemie". Das "%-Zeichen" steht jeweils für einen beliebigen Text vor und nach dem gesuchten Textteil, was der Formulierung "Der Titel enthält den Textteil 'Chemie'" entspricht.
 
<pre>SELECT Titel, Preis, Autor, Verlag
FROM Buch
WHERE Titel like '%Chemie%'
ORDER BY Verlag</pre>
 
<html>
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Titel, Preis, Autor, Verlag FROM Buch WHERE Titel like '%Chemie%' ORDER BY Verlag">
<input type="submit" value="Ausführen">
</form>
</html>
 
Im zweiten Schritt wird den Chemiebüchern die Einschränkung auf Bücher des Verlages 'manz' unter Nutzung des logischen Operators "AND" hinzugefügt. Die Menge aller Chemiebücher wird mit der Menge der Bücher des Verlages 'manz' geschnitten.
 
<pre>SELECT Titel, Preis, Autor, Verlag
FROM Buch
WHERE Titel like '%Chemie%'
AND Verlag = 'manz'
ORDER BY Verlag</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Vorname, Nachname FROM Kunde WHERE Vorname='Michael'">
<input type="hidden" name="select" value="SELECT Titel, Preis, Autor, Verlag FROM Buch WHERE Titel like '%Chemie%' AND Verlag = 'manz' ORDER BY Verlag">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
</form>
</form>
</html>
</html>


Eine Erweiterung gefällig? Das Ergebnis der Chemiebücher des Verlages 'manz' zeigt gerade 6 Treffer. Als passionierter Buchhändler kennt man die Bedeutung des Verlages 'oev' und vermutet dort auch ein umfassendes Angebot. Der Sinn der gegenständlichen Einschränkung ist jedoch, Angebote kleinerer Verlage zu finden. Es liegt daher nahe, das Angebot an Chemie-Büchern des Verlages 'manz' um gleichnamige Angebote der Verlage 'ha' und 'wpr' zu erweitern. Eine, im Kern mengentheoretische Aufgabe:


Bei Zeit/Zahlenwerten als Bedingung ist nicht nur das "="-Zeichen erlaubt, sondern auch <, >, <>, >= und <=. Im Gegensatz zu Textwerten müssen diese dann auch nicht in einfache Anführungszeichen gesetzt werden.
<pre>Schneiden Sie die Menge aller Chemiebücher
mit der Vereinigungsmenge aller Bücher der Verlage: 'manz', 'ha' und 'wpr'. </pre> Auf SQL übersetzt:


Das Auswahlverfahren könnte nun mithilfe von Boolschen Operatoren (AND, OR, NOT usw.) verfeinert werden. Die Abfrage unterhalb gibt die Spalten ''Vorname'', ''Nachname'' und ''Plz'' der Datensätze in der Tabelle ''Kunde'' aus, die im Feld ''Vorname'' "Michael" entsprechen und eine ''Plz'' kleiner 4030 besitzen.
<pre>SELECT Titel, Preis, Autor, Verlag
<pre>SELECT Vorname, Nachname, Plz
FROM Buch
FROM Kunde
WHERE Titel like '%Chemie%'
WHERE Vorname='Michael' AND Plz<4030</pre>
AND (Verlag = 'manz'
OR Verlag = 'ha'
OR Verlag = 'wpr')
ORDER BY Verlag</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Vorname, Nachname, Plz FROM Kunde WHERE Vorname='Michael' AND Plz<4030">
<input type="hidden" name="select" value="SELECT Titel, Preis, Autor, Verlag FROM Buch WHERE Titel like '%Chemie%' AND (Verlag = 'manz' OR Verlag = 'ha' OR Verlag = 'wpr') ORDER BY Verlag">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
</form>
</form>
</html>
</html>


'AND', 'OR' mit Klammern richtig strukturiert erzeugen des Rätsels Lösung. Sollten Sie sich an dieser Stelle an die [http://stubber.math-inf.uni-greifswald.de/analysis/schimming/analysis.pdf Grundlagen der Analysis] erinnern, so liegen Sie keineswegs falsch!
== Verbinden von Tabellen ==
Zur Unterstützung fast aller Aufgaben aus dem Datenbank-Alltag werden Daten benötigt, die über mehrere Tabellen hinweg verteilt sind. Das Zusammenführen von Feldern aus mehreren Tabellen heißt auch ''Join'' und wird ebenfalls in der WHERE-Komponente formuliert.
Bei Abfragen, die über mehrere Tabellen gehen, ist es ratsam, die Feldnamen in Verbindung mit dem Tabellennamen anzuführen. Aus ''Vorname'' wird ''Kunde.Vorname'', aus Preis wird Buch.Preis usw. Diese Maßnahme ist dann unerlässlich, wenn in ein und derselben Abfrage idente Feldbezeichnungen auftauchen. Das Feld ''Kunde.Nr'' muss z. B. von der ''Artikel.Nr'' und diese von der ''Buch.Nr'' unterschieden werden können.
Die Verbindung der beteiligten Tabellen erfolgt über die Schlüsselfelder. Da im vorliegenden Beispiel ausschließlich 1:n Verbindungen zur Anwendung kommen '''wird immer der Primärschlüssel einer Tabelle einem Fremdschlüssel einer weiteren Tabelle gleich gesetzt'''. Folgen Sie den gerichteten Kanten in der [https://sql.idb.edu/skalierbar/datenmodell_3.swf Grafik], diese weisen den Weg der Verbindungen (Joins).
Die augenfälligste Forderung nach einer Verbindung von Tabellen ergibt sich aus der Notwenigkeit vollständiger Kundenadressen. Da die Ortsnamen der 3. Normalform wegen in eine eigene Tabelle mit der Bezeichnung PLZ ausgelagert wurden, ist die Verbindung der Tabellen PLZ und Kunde unerlässlich. Nachfolgend unspektakulär scheint dann die Lösung:


Diese Abfrage gibt die Spalten ''Vorname'', ''Nachname'' und ''Plz'' jeder Zeile der Tabelle ''Kunde'' aus, die eine ''Plz'' zwischen 1010 und 4030 besitzt.
<pre>SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Strasse, Kunde.Plz, PLZ.Ort
<pre>SELECT Vorname, Nachname, Plz
FROM Kunde, PLZ
FROM Kunde
WHERE Kunde.Plz=PLZ.Plz
WHERE Plz BETWEEN 1010 AND 4030</pre>
ORDER BY Ort, Nachname ASC</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Vorname, Nachname, Plz FROM Kunde WHERE Plz BETWEEN 1010 AND 4030">
<input type="hidden" name="select" value="SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Strasse, Kunde.Plz, PLZ.Ort FROM Kunde, PLZ WHERE Kunde.Plz=PLZ.Plz ORDER BY Ort, Nachname ASC">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
</form>
</form>
</html>
</html>


NICHT dass Sie nun denken: entweder Joins - oder Bilden von Teil- und Vereinigungsmengen. Die WHERE-Komponente verträgt beides. Sind jetzt die vollständigen Adressen der Kunden aus Nieder- und Oberösterreich gefällig?


Ist nur ein Teil des Suchbegriffs innerhalb eines Feldes bekannt, so kann nach diesen Schlüsselwörtern gesucht werden. Das "%"-Zeichen dient als Platzhalter, welcher beliebige (und beliebig viele) Zeichen davor bzw. danach zulässt. Folglich gibt die nächste Abfrage die Spalten ''Vorname'' und ''Nachname'' der Datensätze in der Tabelle ''Kunde'' aus, die im Feld Nachname "Muster" an beliebiger Stelle enthalten.
<pre>SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Strasse, Kunde.Plz, PLZ.Ort
<pre>SELECT Vorname, Nachname
FROM Kunde, PLZ
FROM Kunde
WHERE Kunde.Plz=PLZ.Plz
WHERE Nachname LIKE '%Muster%'</pre>
AND (PLZ.Region = 'o'
OR PLZ.Region = 'n')
ORDER BY Nachname ASC</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Vorname, Nachname FROM Kunde WHERE Nachname LIKE '%Muster%'">
<input type="hidden" name="select" value="SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Strasse, Kunde.Plz, PLZ.Ort FROM Kunde, PLZ WHERE Kunde.Plz=PLZ.Plz AND (PLZ.Region = 'o' OR PLZ.Region = 'n') ORDER BY Nachname ASC">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
</form>
</form>
</html>
</html>


== Rechenoperationen mit SQL ==


== Sortieren von Datensätzen ==
Die Datenbanksprache SQL kann auch '''Rechenoperationen auf Suchergebnisse in relationalen Datenbanken''' durchführen und bildet damit in gewisser Weise Konkurrenz zu Tabellenkalkulationsprogrammen. Wie in den Letztgenannten auch, werden mit Ausnahme der Grundrechnungsarten (+, -, *, /) komplexe Operationen als Funktion formuliert; mit vorangestelltem Funktionsnamen, dem die Argumente in Klammern gesetzt folgen. Der Ausdruck: MAX(Buch.Preis) liefert demnach den höchsten Preis der im SELECT definierten Menge, was mit € 116,90 für ein Schulbuch stolz ausfällt.


Selbstverständlich können die auszugebenden Datensätze im Ergebnis auch nach bestimmten Kriterien gereiht werden. Diese Sortierung geschieht durch den Zusatz '''ORDER BY''', gefolgt von den Feldern, nach denen sortiert werden soll (im Beispiel zuerst nach dem Nachnamen und danach nach dem Vornamen). Weiters kann noch bestimmt werden, ob dies aufsteigend ('''ASC''') oder absteigend ('''DESC''') geschehen soll.
<pre>SELECT MAX(Preis)
<pre>SELECT Vorname, Nachname
FROM Buch</pre>
FROM Kunde
ORDER BY Nachname, Vorname ASC</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Vorname, Nachname FROM Kunde ORDER BY Nachname, Vorname ASC">
<input type="hidden" name="select" value="SELECT MAX(Preis) FROM Buch">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
</form>
</form>
</html>
</html>


== Verbinden von Tabellen ==
Der Maximalwert wurde in diesem Fall aus der Menge aller in der Tabelle gespeicherten Preise ermittelt und es wäre vermessen nach dem Titel oder Autor des Buches mit dem höchsten Preis zu fragen. Theoretisch besteht die Möglichkeit, dass viele, im Grenzfall alle Bücher denselben hohen Preis aufweisen. Welches Buch würde man dann als das höchstpreisigste ausweisen?
 
Nicht viel anders ist das Ergebnis aus einer eingeschränkten Menge an Büchern zu interpretieren: Was ist der höchste Preis der Bücher des Verlages 'manz'?


Zur Unterstützung zahlreicher Aufgaben aus dem betrieblichen Alltag werden Daten benötigt, die über mehrere Tabellen hinweg verteilt sind. Das Zusammenführen von Feldern aus mehreren Tabellen heißt auch '''Join''' und wird ebenfalls in der WHERE-Komponente definiert.
<pre>SELECT MAX(Preis)
FROM Buch
WHERE Verlag = 'manz'</pre>


Bei Abfragen, die über mehrere Tabellen gehen, ist es ratsam, die Feldnamen in Verbindung mit dem Tabellennamen anzuführen. Aus ''Vorname'' wird ''Kunde.Vorname''. Diese Maßnahme ist dann notwendig, wenn in ein und derselben Abfrage idente Feldbezeichnungen auftauchen. Die ''Kunde.Nr'' muss z. B. von der ''Artikel.Nr'' und diese von der ''Buch.Nr'' unterschieden werden können.
<html>
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT MAX(Preis) FROM Buch WHERE Verlag = 'manz'">
<input type="submit" value="Ausführen">
</form>
</html>


Die Verbindung der benötigten Tabellen erfolgt über die Schlüsselfelder. In der Regel wird ein Primärschlüssel einer Tabelle mit einem Fremdschlüssel einer weiteren Tabelle verbunden. Dazu ist es hilfreich, das normalisierte Datenmodell zu verwenden. Die gerichteten Kanten weisen den Weg der Verbindungen (Joins).
Mit € 93,52 liegt dieser etwas unter dem absoluten Höchstpreis aller geführten Bücher.
<pre>SELECT Tabelle1.Feld1, Tabelle2.Feld1, Tabelle2.Feld2
FROM Tabelle1, Tabelle2
WHERE (Tabelle1.Feld2=Tabelle2.Feld2)</pre>


Diese Art der Abfrage wird dazu verwendet, um Daten aus zwei verschiedenen Tabellen zu einem Datensatz zu kombinieren. Somit wird das WHERE hier nicht mehr zur Selektion verwendet (so wie in den vorangegangenen Beispielen), sondern zur '''Projektion''' von Daten. Sobald also mit einer etwas komplexeren Datenbank mit mehreren verbundenen Tabellen gearbeitet wird, ergibt sich die Notwendigkeit, auf diese Art und Weise an die benötigten Daten zu kommen.
Führen gegenständliche Überlegungen nicht dazu, sich systematisch-vergleichende Auswertungen zu wünschen? Z.B. als Auflistung der höchsten und niedrigsten Buchpreise, gruppiert (bezogen) auf die jeweiligen Verlage? Für diesen, weitaus mächtigeren Anwendungsfall komplexer Rechenoperation steht die GROUP BY Komponente zur Verfügung:


Für das Beispiel wird nun eine zweite Tabelle (die Tabelle ''PLZ'') benötigt. Die Informationen, die zu einer Postleitzahl gehören, wurden in diese ausgelagert. Die beiden Tabellen ''PLZ'' und ''Kunde'' sind über das Feld ''Plz'' verbunden. Achtung: Bei diesem Beispiel darf man sich nicht verwirren lassen, dass der Tabellenname ''PLZ'' genauso lautet wie das Feld ''Plz''!
<pre>SELECT Verlag, MAX(Preis), MIN(Preis)
<pre>SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Plz, PLZ.Ort
FROM Buch
FROM Kunde, PLZ
GROUP BY Verlag
WHERE (Kunde.Plz=PLZ.Plz)</pre>
ORDER BY MAX(Preis) DESC</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Plz, PLZ.Ort FROM Kunde, PLZ WHERE (Kunde.Plz=PLZ.Plz)">
<input type="hidden" name="select" value="SELECT Verlag, MAX(Preis), MIN(Preis) FROM Buch GROUP BY Verlag ORDER BY MAX(Preis) DESC">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
</form>
</form>
</html>
</html>


== Gruppieren von Datensätzen ==
Es ist zu erwarten, dass komplexe Rechenoperationen nicht nur auf Maxima und Minima begrenzt sind. Eine Auswahl, weiterer, geläufiger findet sich nachfolgend:


Die '''GROUP BY'''-Klausel bewirkt, dass die aus einer Abfrage resultierende Tabelle in '''Gruppen''' gegliedert wird. Dazu muss in der GROUP BY-Klausel angegeben werden, nach welchem Kriterium die Gruppierung erfolgen soll. Dieses Kriterium, der '''Gruppierungsschlüssel''', ist im einfachsten Fall ein Attribut der Ausgangstabelle. Alle Zeilen mit demselben Wert des Gruppierungsschlüssels fallen in dieselbe Gruppe. Eine Gruppe ist also nichts anderes als eine Teiltabelle. Im Ergebnis wird jede Gruppe durch die Anwendung von Gruppierungsfunktionen auf eine einzelne Zeile zusammengefasst.
Als typische Gruppenfunktionen bzw. '''Aggregatsfunktionen''' gelten:
* COUNT
* COUNT
* SUM
* SUM
Zeile 181: Zeile 263:
* MIN
* MIN


Die Gruppenfunktionen werden analog zu gängigen Funktionen in Tabellenkalkulationsprogrammen angewandt und notiert, z. B. SUM(Auftragspos.Menge * Buch.Preis).
Es ist an der Zeit, sich der letzten, der eingangs erwähnten Anfragen an die Übungsdatenbank zuzuwenden: Die kumulierten Umsätze aller Kunden des Jahres 2000, aufgelistet nach Bundesländern zuzuwenden.


Diese Abfrage gibt das gruppierte Feld ''Region'' und die Anzahl der Orte, die "kirche" enthalten, aus der Tabelle ''PLZ'' aus und sortiert diese neue Tabelle absteigend anhand der zweiten Spalte.
Die Lösung erfordert etwas mehr Joins (Verbindungen zwischen Tabellen) als bis jetzt, eine Multiplikation (Preis x Mange) und die Anwendung der Summenbildung statt Minimum und Maximum. Die nachfolgende Anweisung ist zwar komplexer, aber enthält keine grundsätzlich neuen Aspekte:
<pre>SELECT Region, COUNT(*)
FROM PLZ
WHERE Ort LIKE '%kirche%'
GROUP BY Region
ORDER BY 2 DESC</pre>


<html>
* Der Ausdruck "AS" ändert lediglich die Spaltenüberschrift in der ausgegebenen Tabelle,
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
* die Funktion "YEAR" extrahiert aus einem Datumswert die Jahreszahl
<input type="hidden" name="select" value="SELECT Region, COUNT(*) FROM PLZ WHERE Ort LIKE '%kirche%' GROUP BY Region ORDER BY 2 DESC">
* und die Funktion "ROUND" rundet die scheinbar unerklärlich vielen Nachkommastellen auf zwei. Wie bei Tabellenkalkulationsprogrammen auch, können wie hier geübt, Funktionen geschachtelt angewandt werden.
<input type="submit" value="Ausführen">
</form>
</html>


Mit der Gruppenfunktion generierte Spalten können mit '''AS''' benannt und dadurch auch für weitere Aktionen (z. B. ORDER BY) innerhalb derselben Abfrage verwendet werden. Die folgende Abfrage stellt bereits eine eher komplexe Abfrage mit einigen Joins dar. Sie gibt die Spalten ''Kunde.Nr'', ''Kunde.Nachname'', ''PLZ.Ort'' und den errechneten Umsatz aus den Tabellen ''PLZ'', ''Kunde'', ''Auftrag'', ''Auftragspos'' und ''Buch'' aus, gruppiert das Ergebnis nach der Kundennummer und sortiert es am Nachnamen.
<pre>SELECT PLZ.Region, ROUND(SUM(Auftragspos.Menge*Buch.Preis),2) AS Umsatz
 
<pre>SELECT Kunde.Nr, Kunde.Nachname, PLZ.Ort, SUM(Auftragspos.Menge*Buch.Preis) AS Umsatz
FROM PLZ, Kunde, Auftrag, Auftragspos, Buch
FROM PLZ, Kunde, Auftrag, Auftragspos, Buch
WHERE PLZ.Plz=Kunde.Plz
WHERE PLZ.Plz = Kunde.Plz
AND Kunde.Nr=Auftrag.Kundennummer
AND Kunde.Nr=Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftragspos.Buchnummer=Buch.Nr
AND Auftragspos.Buchnummer=Buch.Nr
GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Ort
AND YEAR(Auftrag.Datum) = '2000'
ORDER BY Kunde.Nachname</pre>
GROUP BY PLZ.Region
ORDER BY SUM(Auftragspos.Menge*Buch.Preis) DESC</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Kunde.Nr, Kunde.Nachname, PLZ.Ort, SUM(Auftragspos.Menge*Buch.Preis) AS Umsatz FROM PLZ, Kunde, Auftrag, Auftragspos, Buch WHERE PLZ.Plz=Kunde.Plz AND Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Ort ORDER BY Kunde.Nachname">
<input type="hidden" name="select" value="SELECT PLZ.Region, ROUND(SUM(Auftragspos.Menge*Buch.Preis),2) AS Umsatz FROM PLZ, Kunde, Auftrag, Auftragspos, Buch WHERE PLZ.Plz = Kunde.Plz AND Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr AND YEAR(Auftrag.Datum) = '2000'  GROUP BY PLZ.Region ORDER BY SUM(Auftragspos.Menge*Buch.Preis) DESC">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
</form>
</form>
</html>
</html>


==Selektion (Einschränkung) auf eine Gruppierung (Aggregierung) von Datensätzen==
Die Gruppierungsfunktion "GROUP BY" wird manchmal auch als Aggregationsfunktion bezeichnet. '''Aggregieren''' bedeutet in diesem Zusammenhang auch '''Verdichten''' oder '''Kumulieren'''. Am Beispiel Umsatz können verschiedene Stufen des Verdichtens oder Aggregierens gut verdeutlicht werden. Die unterste Stufe des Verdichtens von Umsätzen wäre der Auftrag. Dann folgt der Kunde und schließlich die Region. Mit der Erhöhung der Verdichtungsstufe steigen jeweils die Beträge, aber es verringert sich die Anzahl der Treffer. Die Lerneinheit [[SQL-Beispielvariation]] beschäftigt sich mit den unterschiedlichen Aggregationsstufen.
 
==Bilden von Teilmengen mit aggregierten Werten==


Besteht der Bedarf, das Ergebnis einer Aggregatsfunktion einzuschränken, so geschieht dies nicht in der WHERE Klausel, sondern in der HAVING Klausel, welche nach dem GROUP BY zur Anwendung kommt.
Aggregieren von Kennzahlen wie Umsätze, liefern wichtige Entscheidungsgrundlagen, wie im vorliegenden Fall zur Führung des Unternehmens. Die auf Regionen (Bundesländer) aggregierten Umsätze zeigen, dass der Absatzmarkt keineswegs gleich auf die Regionen verteilt ist, suggeriert aber gleichzeitig die Frage nach dem Bestand von umsatzstarken Kunden. Regionsumsätze könnten prinzipiell von vielen, kleinen Kunden stammen, oder aber auch von wenig Kunden mit entsprechend hohen Umsätzen. Die nächste und letzte Anfrage in dieser Lerneinheit richtet sich daher nach den Kunden mit hohen Umsätzen in der betrachteten Periode, wobei € 80.000 und mehr als hoch bezeichnet werden.


Beispiel: Der ermittelte Kundenumsatz aus obigem Beispiel soll auf jene Kunden eingeschränkt werden, deren Umsatz den Betrag von 100000 erreicht oder übersteigt. Erwartet wird eine Liste der Kundenumsätze mit den Merkmalen: Nummer, Nachname, Ort und Summe, sortiert nach dem Nachnamen des Kunden.
Ermittelt wird eine Liste der kumulierten Kundenumsätze mit den Merkmalen: Kunde.Nummer, Kunde,Nachname, PLZ.Region die im Jahr 2000 erzielt wurden, wenn dieser Wert mehr als € 80.000 je Kunde ausmacht. Das Bilden jener Teilmenge, auf welche die Einschränkung "hohe Umsätze"  zutrifft kann nur mit Anwendung der HAVING-Komponente erzeugt werden. Die Einschränkung auf das Umsatzjahr 2000 bleibt davon unberührt.


<pre>SELECT Kunde.Nr, Kunde.Nachname, PLZ.Ort, SUM(Auftragspos.Menge*Buch.Preis) AS Umsatz
<pre>SELECT Kunde.Nr, Kunde.Nachname, PLZ.Region, ROUND(SUM(Auftragspos.Menge*Buch.Preis),2) AS Umsatz
FROM PLZ, Kunde, Auftrag, Auftragspos, Buch
FROM PLZ, Kunde, Auftrag, Auftragspos, Buch
WHERE PLZ.Plz=Kunde.Plz
WHERE PLZ.Plz=Kunde.Plz
Zeile 227: Zeile 302:
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftragspos.Buchnummer=Buch.Nr
AND Auftragspos.Buchnummer=Buch.Nr
GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Ort
AND YEAR(Auftrag.Datum) = '2000'
HAVING SUM(Auftragspos.Menge*Buch.Preis >= 100000
GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Region
ORDER BY Kunde.Nachname</pre>
HAVING SUM(Auftragspos.Menge*Buch.Preis) >= 80000
ORDER BY SUM(Auftragspos.Menge*Buch.Preis) DESC</pre>


<html>
<html>
<form method="post" action="http://sql.idv.edu/thema/work/sql.php" target="_blank">
<form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank">
<input type="hidden" name="select" value="SELECT Kunde.Nr, Kunde.Nachname, PLZ.Ort, SUM(Auftragspos.Menge*Buch.Preis) AS Umsatz
<input type="hidden" name="select" value="SELECT Kunde.Nr, Kunde.Nachname, PLZ.Region, ROUND(SUM(Auftragspos.Menge*Buch.Preis),2) AS Umsatz
FROM PLZ, Kunde, Auftrag, Auftragspos, Buch
FROM PLZ, Kunde, Auftrag, Auftragspos, Buch
WHERE PLZ.Plz=Kunde.Plz
WHERE PLZ.Plz=Kunde.Plz
Zeile 239: Zeile 315:
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftragspos.Buchnummer=Buch.Nr
AND Auftragspos.Buchnummer=Buch.Nr
GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Ort
AND YEAR(Auftrag.Datum) = '2000'
HAVING SUM(Auftragspos.Menge*Buch.Preis) >= 100000
GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Region
ORDER BY Kunde.Nachname">
HAVING SUM(Auftragspos.Menge*Buch.Preis) >= 80000
ORDER BY SUM(Auftragspos.Menge*Buch.Preis) DESC">
<input type="submit" value="Ausführen">
<input type="submit" value="Ausführen">
</form>
</form>
Zeile 253: Zeile 330:


== Zitiervorschlag ==
== Zitiervorschlag ==
''Mittendorfer'' in ''Pils'', Informationsverarbeitung I (24. 2. 2013), SQL-Abfragen  (mussswiki.idv.edu/iv1)
''Mittendorfer'' in ''Höller'', Informationsverarbeitung I, SQL-Abfragen  (mussswiki.idb.edu/iv1)

Aktuelle Version vom 1. Oktober 2018, 14:09 Uhr

<yambe:breadcrumb>SQL|SQL</yambe:breadcrumb>
Die Lerneinheit "SQL-Abfragen" dient der Erläuterung unterschiedlicher Anwendungsfälle der SQL-Anweisung "SELECT", die jeweils mit interaktiv ausführbaren Beispielen hinterlegt sind. Diese Beispiele beziehen sich auf die Trainingsdatenbank [1].


Die SELECT Anweisung

Relationale Datenbanken können mit Hilfe der SELECT-Anweisung ausgewertet werden, wenn die entsprechenden Zugriffsrechte vorliegen. Nachfolgende Beispiele beziehen sich auf die Implementierung eines SQL-Trainingsservers, welcher als interaktive Anwendung auch unter https://sql.idb.edu erreichbar ist. Die Zugriffsrechte auf die Lehr- und Übungsdatenbank über die vorliegende Schnittstelle erlauben lediglich die Anwendung der SELECT-Anweisung. Das Ausführun von Anweisungen, welche die Struktur oder den Inhalt der Datenbank ändern könnten, werden abgewiesen.

Den interaktiven Abfragen liegt folgendes Datenmodell zu Grunde:

Auswählen und Ausgeben von Datensätzen

Bei der Formulierung von Abfragen mittels SELECT-Anweisung ist die Anwendung von mengentheoretischen Grundkenntnissen sehr hilfreich. Grundsätzlich sind mittels SELECT alle Datensätze aller Tabellen einer Datenbank auswählbar (selektierbar). Die Kunst der richtigen Anwendung besteht aber darin, durch das Formulieren von Schnitt- und Vereinigungsmengen das richtige Ergebnis (= der gestellten Aufgabe entsprechende Menge) zu bilden. Beispiele für Anfragen an die vorliegenden Übungs-Datenbank sind:

  • Die vollständigen Adressen aller Kunden in Nieder- und Oberösterreich.
  • Eine Liste aller Chemiebücher des Verlages 'manz'.
  • Die kumulierten Umsätze des Jahres 2000 aller Kunden, aufgelistet nach Bundesländern.

Die vorliegende Lerneinheit soll Sie befähigen, die soeben formulierten Fragen zu beantworten.

Alle Ergebnisse einer SELECT-Anweisung werden in Form von Listen (bzw. Tabellen) erzielt. In der Praxis sind die erzielten Ergebnisse Teil einer (Computer)Anwendung, welche aus dem unermesslichen Repertoire an Anwendungen stammt und an dieser Stelle keine weitere Behandlung mehr bedarf. Siehe: Datenbankgrundlagen.

Die Idee der Auswertung dieser Übungs-Datenbank ist die Weiterverarbeitung in Tabellenkalkulationsprogrammen. Zu diesem Zwecke wird am Ende jedes Ergebnisses (jeder Ausgabe) die Funktion "Download as CSV" angeboten.

SELECT- Die Grundform

Das erste Beispiel erzeugt eine Liste aller Vornamen der Kunden (aus der Tabelle Kunde). Die Grundform verlangt mindestens die Komponenten SELECT und FROM. Nach der Komponente SELECT wollen die Namen der Spalten (Felder) genannt werden, nach dem FROM jene Tabelle(n) aus denen die gesuchten Felder stammen. Das Einstiegsbeispiel such alle Vornamen der Kunden. 

SELECT Vorname
FROM Kunde

<html> <form method="POST" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Vorname FROM Kunde"> <input type="submit" value="Ausführen"> </form> </html>

Möchte man mehr als ein Feld aufgelistet haben, so werden diese durch Komma getrennt nacheinander angeführt. Nachfolgendes Beispiel sucht nach den Inhalten der Felder Vorname, Nachname und Plz der Kunden (aus der Tabelle Kunde). 

SELECT Vorname, Nachname, Plz
FROM Kunde

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Vorname, Nachname, Plz FROM Kunde"> <input type="submit" value="Ausführen"> </form> </html>

Bemerkenswert ist, dass alle Datensätze der Tabelle "Kunde" ausgewählt und ausgegeben werden und diese prinzipiell unsortiert sind.

Ist die Anzahl der Felder, bzw. deren Namen unbekannt, so führt folgendes SELECT zur Auswahl aller Datensätze mit all ihren Feldern. Der gesamte Inhalt der Tabelle "Kunde" wird demnach selektiert und ausgegeben. 999 Kunden sollen gefunden und angezeigt werden. 

SELECT *
FROM Kunde

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT * FROM Kunde"> <input type="submit" value="Ausführen"> </form> </html>

Sortieren der Datensätze und Ausschließen von Wiederholungen

Gleiche Inhalte von Feldern in unterschiedlichen Datensätzen ist keine Seltenheit. Es würde überraschen, wenn bei 999 Kunden manche Vornamen nicht mehrfach vorkämen. Eine einfache Methode dies zu überprüfen ist, die Reihenfolge der Vornamen der Kunden alphabetisch zu sortieren. Das SELECT-FROM wird um die Komponente ORDER BY ergänzt. "ASC" steht für ascending, was aufsteigend sortiert bedeutet. 

SELECT Vorname
FROM Kunde
ORDER BY Vorname ASC

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Vorname FROM Kunde ORDER BY Vorname ASC"> <input type="submit" value="Ausführen"> </form> </html>

Haben Sie das Ergebnis überprüft? Wer hätte gedacht, dass gleich zu Beginn der Liste 9 Achims angeführt werden? Sollen alle Duplikate aus der Liste entfernt werden, so kommt die Komponente DISTINCT zum Einsatz. Jeder Vorname wird nur eine einziges Mal angeführt. 

SELECT DISTINCT Vorname
FROM Kunde
ORDER BY Vorname ASC

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT DISTINCT Vorname FROM Kunde ORDER BY Vorname ASC"> <input type="submit" value="Ausführen"> </form> </html>

Selektieren von Datensätzen - Bilden von Teil- und Vereinigungsmengen

Zu Beginn dieser Lerneinheit war von mengentheoretischen Überlegungen die Rede. Die Komponente DISTINCT hat zwar zur Bildung einer Teilmenge aus der Menge aller Vornamen bereits beigetragen, aber diese Methode ist für die meisten Bedarfe nicht ausreichend.

Die WHERE-Komponente in Verbindung mit den nachfolgend angeführten Operatoren ermöglicht das Bilden von Vereinigungs- und Teilmengen unter Verwendung von Klammern, Vergleichsperatoren, Rechenoperationen und logischen Verbindungen.

  1. ( )
  2. * /
  3. + -
  4. = <> > < like
  5. NOT AND OR

Neun Kunden mit dem Vornamen Achim befinden sich in der Datenbank. Gesucht sind nun der Vorname, der Nachname und die Postleitzahl jener Kunden, deren Vornamen auf Achim lauten. Die Treffer sollten aufsteigend nach dem Nachnamen aufgelistet werden. 

SELECT Vorname, Nachname, PLZ
FROM Kunde
WHERE Vorname='Achim'
ORDER BY Nachname ASC

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Vorname, Nachname, PLZ FROM Kunde WHERE Vorname='Achim' ORDER BY Nachname ASC"> <input type="submit" value="Ausführen"> </form> </html>

Wie wäre es nun mit der eingangs erwähnten Auflistung aller Chemiebücher, des Verlages 'manz' ? Die Lösung soll in kleinen Schritten erfolgen. Zuerst werden die Chemiebücher gesucht. Chemiebücher enthalten im Titel den Text "Chemie". Das "%-Zeichen" steht jeweils für einen beliebigen Text vor und nach dem gesuchten Textteil, was der Formulierung "Der Titel enthält den Textteil 'Chemie'" entspricht. 

SELECT Titel, Preis, Autor, Verlag
FROM Buch
WHERE Titel like '%Chemie%'
ORDER BY Verlag

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Titel, Preis, Autor, Verlag FROM Buch WHERE Titel like '%Chemie%' ORDER BY Verlag"> <input type="submit" value="Ausführen"> </form> </html>

Im zweiten Schritt wird den Chemiebüchern die Einschränkung auf Bücher des Verlages 'manz' unter Nutzung des logischen Operators "AND" hinzugefügt. Die Menge aller Chemiebücher wird mit der Menge der Bücher des Verlages 'manz' geschnitten. 

SELECT Titel, Preis, Autor, Verlag
FROM Buch
WHERE Titel like '%Chemie%'
AND Verlag = 'manz'
ORDER BY Verlag

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Titel, Preis, Autor, Verlag FROM Buch WHERE Titel like '%Chemie%' AND Verlag = 'manz' ORDER BY Verlag"> <input type="submit" value="Ausführen"> </form> </html>

Eine Erweiterung gefällig? Das Ergebnis der Chemiebücher des Verlages 'manz' zeigt gerade 6 Treffer. Als passionierter Buchhändler kennt man die Bedeutung des Verlages 'oev' und vermutet dort auch ein umfassendes Angebot. Der Sinn der gegenständlichen Einschränkung ist jedoch, Angebote kleinerer Verlage zu finden. Es liegt daher nahe, das Angebot an Chemie-Büchern des Verlages 'manz' um gleichnamige Angebote der Verlage 'ha' und 'wpr' zu erweitern. Eine, im Kern mengentheoretische Aufgabe: 

Schneiden Sie die Menge aller Chemiebücher
mit der Vereinigungsmenge aller Bücher der Verlage: 'manz', 'ha' und 'wpr'.

Auf SQL übersetzt: 

SELECT Titel, Preis, Autor, Verlag
FROM Buch
WHERE Titel like '%Chemie%'
AND (Verlag = 'manz'
OR Verlag = 'ha'
OR Verlag = 'wpr')
ORDER BY Verlag

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Titel, Preis, Autor, Verlag FROM Buch WHERE Titel like '%Chemie%' AND (Verlag = 'manz' OR Verlag = 'ha' OR Verlag = 'wpr') ORDER BY Verlag"> <input type="submit" value="Ausführen"> </form> </html>

'AND', 'OR' mit Klammern richtig strukturiert erzeugen des Rätsels Lösung. Sollten Sie sich an dieser Stelle an die Grundlagen der Analysis erinnern, so liegen Sie keineswegs falsch!

Verbinden von Tabellen

Zur Unterstützung fast aller Aufgaben aus dem Datenbank-Alltag werden Daten benötigt, die über mehrere Tabellen hinweg verteilt sind. Das Zusammenführen von Feldern aus mehreren Tabellen heißt auch Join und wird ebenfalls in der WHERE-Komponente formuliert.

Bei Abfragen, die über mehrere Tabellen gehen, ist es ratsam, die Feldnamen in Verbindung mit dem Tabellennamen anzuführen. Aus Vorname wird Kunde.Vorname, aus Preis wird Buch.Preis usw. Diese Maßnahme ist dann unerlässlich, wenn in ein und derselben Abfrage idente Feldbezeichnungen auftauchen. Das Feld Kunde.Nr muss z. B. von der Artikel.Nr und diese von der Buch.Nr unterschieden werden können.

Die Verbindung der beteiligten Tabellen erfolgt über die Schlüsselfelder. Da im vorliegenden Beispiel ausschließlich 1:n Verbindungen zur Anwendung kommen wird immer der Primärschlüssel einer Tabelle einem Fremdschlüssel einer weiteren Tabelle gleich gesetzt. Folgen Sie den gerichteten Kanten in der Grafik, diese weisen den Weg der Verbindungen (Joins).

Die augenfälligste Forderung nach einer Verbindung von Tabellen ergibt sich aus der Notwenigkeit vollständiger Kundenadressen. Da die Ortsnamen der 3. Normalform wegen in eine eigene Tabelle mit der Bezeichnung PLZ ausgelagert wurden, ist die Verbindung der Tabellen PLZ und Kunde unerlässlich. Nachfolgend unspektakulär scheint dann die Lösung: 

SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Strasse, Kunde.Plz, PLZ.Ort
FROM Kunde, PLZ
WHERE Kunde.Plz=PLZ.Plz
ORDER BY Ort, Nachname ASC

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Strasse, Kunde.Plz, PLZ.Ort FROM Kunde, PLZ WHERE Kunde.Plz=PLZ.Plz ORDER BY Ort, Nachname ASC"> <input type="submit" value="Ausführen"> </form> </html>

NICHT dass Sie nun denken: entweder Joins - oder Bilden von Teil- und Vereinigungsmengen. Die WHERE-Komponente verträgt beides. Sind jetzt die vollständigen Adressen der Kunden aus Nieder- und Oberösterreich gefällig? 

SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Strasse, Kunde.Plz, PLZ.Ort
FROM Kunde, PLZ
WHERE Kunde.Plz=PLZ.Plz
AND (PLZ.Region = 'o'
OR PLZ.Region = 'n')
ORDER BY Nachname ASC

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Kunde.Vorname, Kunde.Nachname, Kunde.Strasse, Kunde.Plz, PLZ.Ort FROM Kunde, PLZ WHERE Kunde.Plz=PLZ.Plz AND (PLZ.Region = 'o' OR PLZ.Region = 'n') ORDER BY Nachname ASC"> <input type="submit" value="Ausführen"> </form> </html>

Rechenoperationen mit SQL

Die Datenbanksprache SQL kann auch Rechenoperationen auf Suchergebnisse in relationalen Datenbanken durchführen und bildet damit in gewisser Weise Konkurrenz zu Tabellenkalkulationsprogrammen. Wie in den Letztgenannten auch, werden mit Ausnahme der Grundrechnungsarten (+, -, *, /) komplexe Operationen als Funktion formuliert; mit vorangestelltem Funktionsnamen, dem die Argumente in Klammern gesetzt folgen. Der Ausdruck: MAX(Buch.Preis) liefert demnach den höchsten Preis der im SELECT definierten Menge, was mit € 116,90 für ein Schulbuch stolz ausfällt. 

SELECT MAX(Preis)
FROM Buch

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT MAX(Preis) FROM Buch"> <input type="submit" value="Ausführen"> </form> </html>

Der Maximalwert wurde in diesem Fall aus der Menge aller in der Tabelle gespeicherten Preise ermittelt und es wäre vermessen nach dem Titel oder Autor des Buches mit dem höchsten Preis zu fragen. Theoretisch besteht die Möglichkeit, dass viele, im Grenzfall alle Bücher denselben hohen Preis aufweisen. Welches Buch würde man dann als das höchstpreisigste ausweisen?

Nicht viel anders ist das Ergebnis aus einer eingeschränkten Menge an Büchern zu interpretieren: Was ist der höchste Preis der Bücher des Verlages 'manz'? 

SELECT MAX(Preis)
FROM Buch
WHERE Verlag = 'manz'

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT MAX(Preis) FROM Buch WHERE Verlag = 'manz'"> <input type="submit" value="Ausführen"> </form> </html>

Mit € 93,52 liegt dieser etwas unter dem absoluten Höchstpreis aller geführten Bücher.

Führen gegenständliche Überlegungen nicht dazu, sich systematisch-vergleichende Auswertungen zu wünschen? Z.B. als Auflistung der höchsten und niedrigsten Buchpreise, gruppiert (bezogen) auf die jeweiligen Verlage? Für diesen, weitaus mächtigeren Anwendungsfall komplexer Rechenoperation steht die GROUP BY Komponente zur Verfügung: 

SELECT Verlag, MAX(Preis), MIN(Preis)
FROM Buch
GROUP BY Verlag
ORDER BY MAX(Preis) DESC

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Verlag, MAX(Preis), MIN(Preis) FROM Buch GROUP BY Verlag ORDER BY MAX(Preis) DESC"> <input type="submit" value="Ausführen"> </form> </html>

Es ist zu erwarten, dass komplexe Rechenoperationen nicht nur auf Maxima und Minima begrenzt sind. Eine Auswahl, weiterer, geläufiger findet sich nachfolgend:

  • COUNT
  • SUM
  • AVG
  • MAX
  • MIN

Es ist an der Zeit, sich der letzten, der eingangs erwähnten Anfragen an die Übungsdatenbank zuzuwenden: Die kumulierten Umsätze aller Kunden des Jahres 2000, aufgelistet nach Bundesländern zuzuwenden.

Die Lösung erfordert etwas mehr Joins (Verbindungen zwischen Tabellen) als bis jetzt, eine Multiplikation (Preis x Mange) und die Anwendung der Summenbildung statt Minimum und Maximum. Die nachfolgende Anweisung ist zwar komplexer, aber enthält keine grundsätzlich neuen Aspekte:

  • Der Ausdruck "AS" ändert lediglich die Spaltenüberschrift in der ausgegebenen Tabelle,
  • die Funktion "YEAR" extrahiert aus einem Datumswert die Jahreszahl
  • und die Funktion "ROUND" rundet die scheinbar unerklärlich vielen Nachkommastellen auf zwei. Wie bei Tabellenkalkulationsprogrammen auch, können wie hier geübt, Funktionen geschachtelt angewandt werden.
SELECT PLZ.Region, ROUND(SUM(Auftragspos.Menge*Buch.Preis),2) AS Umsatz
FROM PLZ, Kunde, Auftrag, Auftragspos, Buch
WHERE PLZ.Plz = Kunde.Plz
AND Kunde.Nr=Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftragspos.Buchnummer=Buch.Nr
AND YEAR(Auftrag.Datum) = '2000' 
GROUP BY PLZ.Region
ORDER BY SUM(Auftragspos.Menge*Buch.Preis) DESC

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT PLZ.Region, ROUND(SUM(Auftragspos.Menge*Buch.Preis),2) AS Umsatz FROM PLZ, Kunde, Auftrag, Auftragspos, Buch WHERE PLZ.Plz = Kunde.Plz AND Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr AND YEAR(Auftrag.Datum) = '2000' GROUP BY PLZ.Region ORDER BY SUM(Auftragspos.Menge*Buch.Preis) DESC"> <input type="submit" value="Ausführen"> </form> </html>

Die Gruppierungsfunktion "GROUP BY" wird manchmal auch als Aggregationsfunktion bezeichnet. Aggregieren bedeutet in diesem Zusammenhang auch Verdichten oder Kumulieren. Am Beispiel Umsatz können verschiedene Stufen des Verdichtens oder Aggregierens gut verdeutlicht werden. Die unterste Stufe des Verdichtens von Umsätzen wäre der Auftrag. Dann folgt der Kunde und schließlich die Region. Mit der Erhöhung der Verdichtungsstufe steigen jeweils die Beträge, aber es verringert sich die Anzahl der Treffer. Die Lerneinheit SQL-Beispielvariation beschäftigt sich mit den unterschiedlichen Aggregationsstufen.

Bilden von Teilmengen mit aggregierten Werten

Aggregieren von Kennzahlen wie Umsätze, liefern wichtige Entscheidungsgrundlagen, wie im vorliegenden Fall zur Führung des Unternehmens. Die auf Regionen (Bundesländer) aggregierten Umsätze zeigen, dass der Absatzmarkt keineswegs gleich auf die Regionen verteilt ist, suggeriert aber gleichzeitig die Frage nach dem Bestand von umsatzstarken Kunden. Regionsumsätze könnten prinzipiell von vielen, kleinen Kunden stammen, oder aber auch von wenig Kunden mit entsprechend hohen Umsätzen. Die nächste und letzte Anfrage in dieser Lerneinheit richtet sich daher nach den Kunden mit hohen Umsätzen in der betrachteten Periode, wobei € 80.000 und mehr als hoch bezeichnet werden.

Ermittelt wird eine Liste der kumulierten Kundenumsätze mit den Merkmalen: Kunde.Nummer, Kunde,Nachname, PLZ.Region die im Jahr 2000 erzielt wurden, wenn dieser Wert mehr als € 80.000 je Kunde ausmacht. Das Bilden jener Teilmenge, auf welche die Einschränkung "hohe Umsätze" zutrifft kann nur mit Anwendung der HAVING-Komponente erzeugt werden. Die Einschränkung auf das Umsatzjahr 2000 bleibt davon unberührt. 

SELECT Kunde.Nr, Kunde.Nachname, PLZ.Region, ROUND(SUM(Auftragspos.Menge*Buch.Preis),2) AS Umsatz
FROM PLZ, Kunde, Auftrag, Auftragspos, Buch
WHERE PLZ.Plz=Kunde.Plz
AND Kunde.Nr=Auftrag.Kundennummer
AND Auftrag.Nr=Auftragspos.Auftragsnummer
AND Auftragspos.Buchnummer=Buch.Nr
AND YEAR(Auftrag.Datum) = '2000'
GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Region
HAVING SUM(Auftragspos.Menge*Buch.Preis) >= 80000
ORDER BY SUM(Auftragspos.Menge*Buch.Preis) DESC

<html> <form method="post" action="https://sql.idb.edu/thema/work/sql.php" target="_blank"> <input type="hidden" name="select" value="SELECT Kunde.Nr, Kunde.Nachname, PLZ.Region, ROUND(SUM(Auftragspos.Menge*Buch.Preis),2) AS Umsatz FROM PLZ, Kunde, Auftrag, Auftragspos, Buch WHERE PLZ.Plz=Kunde.Plz AND Kunde.Nr=Auftrag.Kundennummer AND Auftrag.Nr=Auftragspos.Auftragsnummer AND Auftragspos.Buchnummer=Buch.Nr AND YEAR(Auftrag.Datum) = '2000' GROUP BY Kunde.Nr, Kunde.Nachname, PLZ.Region HAVING SUM(Auftragspos.Menge*Buch.Preis) >= 80000 ORDER BY SUM(Auftragspos.Menge*Buch.Preis) DESC"> <input type="submit" value="Ausführen"> </form> </html>

Literatur

Weiterführende Links

Zitiervorschlag

Mittendorfer in Höller, Informationsverarbeitung I, SQL-Abfragen (mussswiki.idb.edu/iv1)

Abgerufen von „https://mussswiki.idb.edu/iv1wiki/index.php?title=SQL-Abfragen&oldid=6488“