Hier geht's zu dieser Seite
Die vorliegenden Materialien wurden von Daniel Hoherz und André Tempel erstellt. Sollten andere Editoren die Materialien erstellt haben, werden diese explizit genannt.
In der Praxis möchte man nicht nur Daten in Tabellen strukturiert anlegen. Eine Person, die z. B. bei einem Online-Versandhandel Bücher und Filme sucht, muss in der Lage sein, die dahinterliegende Datenbank nach bestimmten Autoren, einem Genre oder einer Preiskategorie zu filtern. Dies ist eine wichtige Aufgabe eines Datenbankmanagementsystems. So eine automatisierte Vorgehensweise hat natürlich viele Vorteile im Vergleich zu einer manuellen Suche.
Wir haben unsere vorläufige Klassenfahrt-Datenbank angelegt und können mit einem Datenbankmanagementsystem, kurz DBMS, Informationen aus der Datenbank gewinnen.
| Teilnehmer | |||
|---|---|---|---|
| Name | Rolle | Telefon | ↑UName |
| Max Müller | Schüler | 0176 12345678 | Jugendherberge Berlin Mitte |
| Lisa Schmidt | Schüler | 0176 87654321 | Jugendherberge Berlin Mitte |
| Frau Meier | Begleitung | 0176 11223344 | Jugendherberge Berlin Mitte |
| Tom Becker | Schüler | 0176 22334455 | Jugendherberge Berlin Mitte |
| Herr Fischer | Begleitung | 0176 33445566 | Jugendherberge Berlin Mitte |
| … | … | … | … |
| Aktivität | ||||
|---|---|---|---|---|
| Bezeichnung | Datum | Beginn | Ende | Kosten (€) |
| Kletterwald-Abenteuer | 15.06.2025 | 10:00 | 13:00 | 15 |
| Freizeitbad-Besuch | 17.06.2025 | 14:00 | 17:00 | 12 |
| Museumsführung | 17.06.2025 | 09:30 | 12:30 | 10 |
| Naturwanderung | 18.06.2025 | 11:00 | 14:00 | 8 |
| Abenteuerpark | 19.06.2025 | 09:00 | 15:00 | 20 |
| … | … | … | … | … |
| Unterkunft | |||
|---|---|---|---|
| UName | Adresse | Kapazität | Kosten |
| Jugendherberge Berlin Mitte | Alexanderstraße 5, 10178 Berlin | 120 | 25 |
| Hostel Hamburg Hafen | Hafenstraße 12, 20359 Hamburg | 80 | 30 |
| Gästehaus München City | Sonnenstraße 20, 80331 München | 100 | 28e |
| Landgasthof Waldesruh | Waldweg 3, 37412 Herzberg am Harz | 40 | 20 |
| Berghütte Alpenblick | Bergstraße 8, 87561 Oberstdorf | 30 | 35 |
| … | … | … | … |
| AktivitätenTeilnahme | |
|---|---|
| ↑Teilnehmer.Name | ↑Aktivität.Bezeichnung |
| Max Müller | Kletterwald-Abenteuer |
| Lisa Schmidt | Naturwanderung |
| Lisa Schmidtg | Freizeitbad-Besuch |
| … | … |
Da die Kosten möglichst niedrig sein sollen, sind zunächst alle Aktivitäten gesucht, deren Kosten weniger als 15 € betragen. Vergleichen Sie die beiden folgenden Texte miteinander:
Gib diejenigen Bezeichnungen von der Tabelle Aktivitätg aus, für welche die Kosten kleiner als 15 € sind.
SELECT Bezeichnung
FROM Aktivität
WHERE Kosten < 15;
Die Abfragesprache SQL (Structured Query Language) ist ein Standard für die hier betrachteten relationalen Datenbanksysteme.
Eine Abfragesprache wie SQL ist eine deklarative Sprache. Im Gegensatz zu prozeduralen Sprachen, wie z. B. Java, Python oder C++ wird hier nicht schrittweise im Code angegeben, wie der Computer das gewüchste Ziel erreichen soll. Man gibt nur das Ziel an und das Datenbankmanagementsystementscheidet, wie es dieses erreicht.
In unserem Beispiel oben haben wir gesehen, wie eine solche SQL-Abfrage formuliert wird:
Gib diejenigen Bezeichnung von der Tabelle Aktivitätg aus, für welche die Kosten kleiner als 15 € sind.
SELECT Bezeichnung
FROM Aktivität
WHERE Kosten < 15;
Umgangssprachlich kann man folgende Fragen stellen:
- Was soll ausgegeben werden?
- Aus welcher Tabelle/welchen Tabellen sollen die Dtaen genommen werden?
- Welche zusätzliche Bedingung/zusätzlichen Bedingungen gelten?
Formale Fragen sind:
- Welche Attribute sind auszugeben?
- Welche Tabelle/Tabellen werden genutzt?
- Welche zusätzliche Bedingung/zusätzlichen Bedingungen gelten?
Daraus ergibt sich das folgende Grundgerüst jeder SQL-Abfrage ist:
SELECT Spalte(n)
FROM Tabelle(n)
WHERE Bedingun(en);
← Projektion
← Selektion (optional)
Die Groß-/Kleinschreibung der Schlüsselworte dient nur zur besseren Lesbarkeit. Sie ist in SQL nicht notwendig.
Mit einer Projektion werden gewünschte Spalten herausgefiltert. Die übrigen werden nicht angezeigt.
Beispiel:
| Buch | |||
|---|---|---|---|
| ISBN | Titel | Autor | Preis (€) |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 |
Um aus der Tabelle nun nur die Autoren zu erhalten, formuliert man folgenden SQL-Abfrage:
SELECT Autor
FROM Buch;→
| Autor |
|---|
| J.R.R. Tolkien |
| Isaac Asimov |
| Charles Dickens |
Bei der Ausgabe werden die gewünschten Attributswerte und der Name der Spalten angezeigt.
Dieser kann, wie wir später noch sehen werden, angepasst werden.
Möchte man sich eine komplette Tabelle anzeigen lassen, schreibt man hinter SELECT ein *.
SELECT *
FROM Buch;→
| ISBN | Titel | Autor | Preis (€) |
|---|---|---|---|
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 |
Mit einer Selektion können Datensätze herausgefiltert werden, die bestimmten Bedingungen genügen.
So sollen aus der folgenden Tabelle alle Datensätzer ermittelt werden, wo der Titel „Der Hobbit‘ ist.
| Buch | |||
|---|---|---|---|
| ISBN | Titel | Autor | Preis (€) |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 |
SELECT *
FROM Buch
WHERE Titel = 'Der Hobbit';→
| ISBN | Titel | Autor | Preis (€) |
|---|---|---|---|
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 |
Mithilfe einer Kombination von Projektion und Selektion kann man Datensätze nach Attributswerten
durchsuchen, die bestimmten Bedingungen genügen.
Soll nun bspw. ermittelt werden, welcher Autor den Hobbit geschrieben hat, dann lautet die SQL-Abfrage:
SELECT Autor
FROM Buch
WHERE Titel = 'Der Hobbit';→
| Autor |
|---|
| J.R.R. Tolkien |
Liegen zwei Relationen mit identischem Schema, also gleichen Attributen und Attributstypen, vor, kann man mit einer Vereinigung alle Datensätze der Relationen zusammenführen. Hierbei werden Mehrfachnennungen vermieden.
| Buch1 | |||
|---|---|---|---|
| ISBN | Titel | Autor | Preis (€) |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 |
| Buch2 | |||
|---|---|---|---|
| ISBN | Titel | Autor | Preis (€) |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 |
| 978-3-596-11266-1 | Der Tod in Venedig | Thomas Mann | 12,95 |
SELECT *
FROM Buch1
UNION
SELECT *
FROM Buch2↓
| ISBN | Titel | Autor | Preis (€) |
|---|---|---|---|
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 |
| 978-3-596-11266-1 | Der Tod in Venedig | Thomas Mann | 12,95 |
Komplexe Suchanfragen sind mit einfachen Projektionen und Selektionen nicht umzusetzen. Oft
möchte man Daten sortiert ausgeben oder Berechnungen innerhalb der Attributswerte durchführen.
Hinter der WHERE-Klausel wurde bisher mit dem Gleichheitszeichen jeweils ein einzelner Attributswert
verglichen. Es sind jedoch weitaus mehr Operationen möglich, von denen die wichtigsten nun
gezeigt werden.
| Operator | Erläuterung |
|---|---|
| < | kleiner |
| > | größer |
| <= | kleiner oder gleich |
| >= | größer oder gleich |
| <> | ungleich |
| BETWEEN | Ist der Wert innerhalb eines bestimmten Wertebereichs? |
| LIKE | Enthalten die durchsuchten Attributswerte einen vorgegebenen Teil? Das Zeichen % meint beliebig viele und _ genau ein Zeichen. |
| IN | Alle Attributswerte werden mit einer vorgegebenen Liste überprüft. |
| IS NULL | Ist der Attributswert mit einem Wert gefüllt oder leer? |
Betrachten wir einige der Operatoren an unseren Beispieltabellen Buch und Film:
| Buch | |||
|---|---|---|---|
| ISBN | Titel | Autor | Preis (€) |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 |
| Film | ||||
|---|---|---|---|---|
| EAN | Titel | FSK | Preis (€) | Buch |
| 5051890300027 | Der Hobbit | 12 | 44,99 | 978-3-608-93800-5 |
| 4045167015739 | James Bond – Spectre | 12 | 29,99 | |
| 4020628814274 | Great Barrier Reef | 0 | 13,29 | |
Zeige alle Autoren an, deren Bücher mit einem Buchstaben von A bis D beginnen.
SELECT Autor
FROM Buch
WHERE Titel < 'E';→
| Autor |
|---|
| J.R.R. Tolkien |
| Charles Dickensn |
Zeige alle Filme an, die zwischen 25 und 50 Euro kosten.
SELECT Titel
FROM Film
WHERE Preis BETWEEN 25 AND 50;→
| Titel |
|---|
| Der Hobbit |
| James Bond - Spectre |
Zeige alle Bücher an, deren Titel 'er' beinhaltet.
SELECT Titel
FROM Buch
WHERE Titel LIKE '%er%';→
| Titel |
|---|
| Der Hobbit |
| Ich, der Roboter |
Schreibt man %er, werden alle Titel ausgegeben, welche am Ende er haben, also nur Ich, der Roboter. Schreibt man er%, werden alle ausgegeben, die er am Anfang haben, also keine. Schreibt man %er%, werden alle Titel ausgegeben, die irgendwo die Zeichenkette er beinhalten.
Zeige alle Filme an, die 13,29 oder 29,99 kosten.
SELECT Titel
FROM Film
WHERE Preis IN (13.29, 29.99);→
| Titel |
|---|
| James Bond - Spectre |
| Great Barrier Reef |
Zeige alle Filme an, deren Attributswerte von Buch leer sind.
SELECT Titel
FROM Film
WHERE Buch IS NULL;→
| Titel |
|---|
| James Bond - Spectre |
| Great Barrier Reef |
Wie auch in Programmiersprachen wie JAVA können auch in SQL logische Operatoren (AND, OR, NOT) genutzt werden. Damit lassen sich verschiedene Bedingungen hinter der WHERE-Klausel verbinden.
Zeige alle Bücher der Datenbank an, die von Isaac Asimov oder Charles Dickens geschrieben wurden.
SELECT Titel
FROM Buch
WHERE Autor = 'Isaac Asimov' OR Autor = 'Charles Dickens';→
| Titel |
|---|
| Ich, der Roboter |
| A Tale of Two Cities |
Mit SQL kann man die Ausgabe der Daten auf verschiedenste Weisen anpassen. Soll ein Spaltenname geändert werden, nutzt man den Operator 'AS'. Möchte man die Daten sortieren, mit dies mit 'ORDER BY' möglich. Mit 'DISTINCT' verhindert man Doppelnennungen.
Zeige alle Filme an, deren Attributswerte von Buch leer sind.
SELECT Titel AS Buchname
FROM Buch
WHERE Autor = 'J.R.R. Tolkien';→
| Buchname |
|---|
| Der Hobbit |
Zeige die Bücher und Autoren an, aber nach den Autoren sortiert.
SELECT Titel, Autor
FROM Buch
ORDER BY Autor;→
| Titel | Autor |
|---|---|
| A Tale of Two Cities | Charles Dickens |
| Ich, der Roboter | Isaac Asimov |
| Der Hobbit | J.R.R. Tolkien |
Zeige alle verschiedenen FSK Einstufungen an.
SELECT DISTINCT FSK
FROM Film;→
| FSK |
|---|
| 12 |
| 0 |
Möchte man alle Datensätze einer Tabelle auswerten, werden sogenannte Aggregatfunktionen genutzt. Diese werden hinter die SELECT-Klausel geschrieben.
| Aggregatfunktion | Erläuterung |
|---|---|
| COUNT | Ermittelt die Anzahl der Datensätze |
| SUM | Liefert die Summe aller Werte |
| MAX, MIN | Liefert den grüßten bzw. kleinsten Wert der Datenreihe |
| AVG | Berechnet das arithmetische Mittel der Datenreihe |
| +, -, *, / | Grundrechenarten |
Die folgenden Beispiele beziehen sich immer auf die untenstehende Tabelle.
| Buch | |||
|---|---|---|---|
| ISBN | Titel | Autor | Preis |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 |
| 978-3-423-21413-1 | Der Hobbit | J.R.R. Tolkien | 9,95 |
| 978-3-518-10001-1 | Eine Weihnachtsgeschichte | Charles Dickens | 12,00 |
| 978-0-358-65303-5 | The Lord of the Rings | J.R.R. Tolkien | 57,99 |
Anzahl der Bücher von J.R.R. Tolkien
SELECT COUNT(*) AS Anzahl_Tolkien
FROM Buch
WHERE Autor = 'J.R.R. Tolkien';→
| Anzahl_Tolkien |
|---|
| 3 |
Anzahl der Autoren
SELECT COUNT(DISTINCT Autor) AS Anzahl_Autoren
FROM Buch;→
| Anzahl_Autoren |
|---|
| 3 |
Preis des günstigsten Buches von J.R.R. Tolkien.
SELECT MIN(Preis) AS günstigstes_Tolkien
FROM Buch
WHERE Autor = 'J.R.R. Tolkien';→
| günstigstes_Tolkien |
|---|
| 9,95 |
Durchschnittspreis aller Bücher.
SELECT AVG(Preis) AS Durchschnitt
FROM Buch;→
| Durchschnitt |
|---|
| 20,49 |
Preise der Bücher ohne Mehrwertsteuer.
SELECT Titel, Preis, Preis*100/119 AS ohne_Steuer
FROM Buch1;↓
| Titel | Preis | ohne_Steuer |
|---|---|---|
| Der Hobbit | 24,00 | 20,17 |
| Ich, der Roboter | 10,00 | 8,40 |
| A Tale of Two Cities | 11,99 | 10,08 |
| Der Hobbit | 9,95 | 8,36 |
| Eine Weihnachtsgeschichte | 12,00 | 10,08 |
| The Lord of the Rings | 57,99 | 48,73 |
- Im Folgenden werden wir uns mit dem SQL-Tutorial beschäftigen. Dort können bezogen auf eine vorgefertigte Datenbank direkt SQL-Abfragen erstellt werden.
Machen Sie sich zunächst mit der Struktur der Datenbank vertraut.
Bearbeiten Sie die unten bereitgestellte Lektion 1 des SQL-Tutorials.
Bearbeiten Sie die unten bereitgestellte Lektion 2 des SQL-Tutorials.
In den meisten Datenbanksystemen sind die Daten auf mehrere Tabellen verteilt, welche durch
entsprechende Attribute miteinander verknüpft sind.
Möchte ein Nutzer Datensätze aus mehreren Tabellen miteinander verknüpfen, kann dies über genau
diese gemeinsamen Attribute passieren. In SQL geht dies mit einem Join (Verbund).
Es werden hier zwei Möglichkeiten für einen Join vorgestellt.
Beides ist gleichwertig und man kann sich aussuchen, welche Variante man bevorzugt.
Wenn es zu einem Datensatz keinen zu dem gemeinsamen Attribut passenden in einer anderen
Tabelle gibt, werden diese Ergebnisse nicht aufgelistet.
Zunächst ein konkretes Beispiel.
| Buch | ||||
|---|---|---|---|---|
| ISBN | Titel | Autor | Preis | VNr |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 | 8110 |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 |
| Verlag | ||
|---|---|---|
| VNr | Name | Ort |
| 101 | Klett-Cotta | Stuttgart |
| 585 | Chartwell | New York |
| 416 | Schöningh | Paderborn |
SELECT *
FROM Buch INNER JOIN Verlag
ON Buch.VNr = Verlag.VNr;oder
SELECT *
FROM Buch, Verlag
WHERE Buch.VNr = Verlag.VNr;↓
| ISBN | Titel | Autor | Preis | Buch.VNr | Verlag.VNr | Name | Ort |
|---|---|---|---|---|---|---|---|
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 | 101 | Klett-Cotta | Stuttgart |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 | 585 | Chartwell | New York |
Das Datenbankmanagementsystem geht bei einer Verbundabfrage über mehrere Tabellen wie folgt vor. Bei
SELECT *
FROM Buch, Verlag;wird zunächst ein sogenanntes Kreuzprodukt aus den beteiligten Tabellen gebildet. Das bedeutet, dass jeder Datensatz aus der einen Tabelle mit allen aus der anderen zusammengefügt wird.
| ISBN | Titel | Autor | Preis | Buch.VNr | Verlag.VNr | Name | Ort |
|---|---|---|---|---|---|---|---|
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 | 101 | Klett-Cotta | Stuttgart |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 | 585 | Chartwell | New York |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 | 416 | Schöningh | Paderborn |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 | 8110 | 101 | Klett-Cotta | Stuttgart |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 | 8110 | 585 | Chartwell | New York |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 | 8110 | 416 | Schöningh | Paderborn |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 | 101 | Klett-Cotta | Stuttgart |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 | 585 | Chartwell | New York |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 | 416 | Schöningh | Paderborn |
Achtung: Die obige Tabelle existiert nicht in der eigentlichen Datenbank. Das DBMS kopiert sich nur die Datensätze aus der Datenebene heraus.
Nun kommt die Bedingung "WHERE Buch.Vnr = Verlag.VNr" Das DBMS prüft nun, bei welchen Datensätzen des Kreuzproduktes diese Bedingung erfüllt ist:
| ISBN | Titel | Autor | Preis | Buch.VNr | Verlag.VNr | Name | Ort |
|---|---|---|---|---|---|---|---|
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 | 101 | Klett-Cotta | Stuttgart |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 | 585 | Chartwell | New York |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 | 416 | Schöningh | Paderborn |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 | 8110 | 101 | Klett-Cotta | Stuttgart |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 | 8110 | 585 | Chartwell | New York |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 | 8110 | 416 | Schöningh | Paderborn |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 | 101 | Klett-Cotta | Stuttgart |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 | 585 | Chartwell | New York |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 | 416 | Schöningh | Paderborn |
Daraus ergibt sich nun die Ausgabe:
| ISBN | Titel | Autor | Preis | Buch.VNr | Verlag.VNr | Name | Ort |
|---|---|---|---|---|---|---|---|
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 | 101 | Klett-Cotta | Stuttgart |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 | 585 | Chartwell | New York |
Möchte man auch alle Datensätze ohne passenden Partner bzgl. der Filterung angezeigt bekommen, kann dies mit einem einseitigen Join (Left- oder Right-Join) geschehen. Hierbei werden, je nachdem ob man einen Left- oder Right-Join anweist, die Datensätze der linken Tabelle um jene der rechten erweitert beziehungsweise andersherum.
| Buch | ||||
|---|---|---|---|---|
| ISBN | Titel | Autor | Preis | VNr |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 | 8110 |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 |
| Verlag | ||
|---|---|---|
| VNr | Name | Ort |
| 101 | Klett-Cotta | Stuttgart |
| 585 | Chartwell | New York |
| 416 | Schöningh | Paderborn |
SELECT *
FROM Buch LEFT JOIN Verlag
ON Buch.VNr = Verlag.VNr;↓
| ISBN | Titel | Autor | Preis | Buch.VNr | Verlag.VNr | Name | Ort |
|---|---|---|---|---|---|---|---|
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 | 101 | Klett-Cotta | Stuttgart |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 | 585 | Chartwell | New York |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 | 8110 |
| Buch | ||||
|---|---|---|---|---|
| ISBN | Titel | Autor | Preis | VNr |
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 |
| 978-3-453-52842-0 | Ich, der Roboter | Isaac Asimov | 10,00 | 8110 |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 |
| Verlag | ||
|---|---|---|
| VNr | Name | Ort |
| 101 | Klett-Cotta | Stuttgart |
| 585 | Chartwell | New York |
| 416 | Schöningh | Paderborn |
SELECT *
FROM Buch RIGHT JOIN Verlag
ON Buch.VNr = Verlag.VNr;↓
| ISBN | Titel | Autor | Preis | Buch.VNr | Verlag.VNr | Name | Ort |
|---|---|---|---|---|---|---|---|
| 978-3-608-93800-5 | Der Hobbit | J.R.R. Tolkien | 24,00 | 101 | 101 | Klett-Cotta | Stuttgart |
| 978-0-785-83983-5 | A Tale of Two Cities | Charles Dickens | 11,99 | 585 | 585 | Chartwell | New York |
| 416 | Schöningh | Paderborn |
-
Zusätzlich zu den Büchern bietet der Onlinehändler auch Filme an, Diese werden ebenfalls in einer Tabelle verwaltet.
Buch ISBN Titel Autor Preis (€) 978-3-608-93800-5 Der Hobbit J.R.R. Tolkien 24,00 978-3-453-52842-0 Ich, der Roboter Isaac Asimov 10,00 978-0-785-83983-5 A Tale of Two Cities Charles Dickens 11,99
Film EAN Titel FSK Preis (€) Buch 5051890300027 Der Hobbit 12 44,99 978-3-608-93800-5 4045167015739 James Bond - Spectre 12 29,99 4020628814274 Great Barrier Reef 0 13,29 Ermitteln Sie die Ergebnisrelation, welche bei den unten stehenden SQL-Abfragen ausgegeben werden. Die Abfragen beziehen sich auf die Tabellen Buch und Film.
SELECT Titel, FSK FROM Film WHERE FSK = 12;SELECT Titel, Preis FROM Buch UNION SELECT Titel, Preis FROM Film;SELECT Buch.Titel, Buch.Preis, Film.Titel, Film.Preis FROM Buch INNER JOIN Film ON Buch.ISBN=Film.Buch;SELECT Buch.Titel, Buch.Preis, Film.Titel, Film.Preis FROM Buch RIGHT JOIN Film ON Buch.ISBN = Film.Buch WHERE Film.Preis < 12;
-
Erstellen Sie jeweils zwei SQL-Abfragen, die zu den unten angegebenen Ergebnissen führen.
ISBN 978-3-453-52842-0 Titel Der Hobbit James Bond - Spectre Great Barrier Reef Autor Preis Charles Dickens 11,99
-
In einem DBMS wurde eine Tabelle Country mit den folgenden Attributen angelegt.
Erstellen Sie entsprechende SQL-Abfragen für die folgenden Probleme:Country country area (km²) population internet Users ... ... ... ... Es sollen die Namen aller Länder ausgegeben werden.
Es sollen alle Länder deren Fläche größer als 8.000.000 km² ist ausgegeben werden.
Die Einwohnerzahl von Frankreich ist gesucht.
-
Gegeben sind drei Relationen, die das Ausgeh- und Musikverhalten einiger Jugendlicher darstellen.
Besucht Name Musik Tina Pop Lisa Latin Tina Rock Maxi Pop Flo Rock Hört Name Lokal Maxi A Maxi C Lisa B Tina B Flo A Spielt Lokal Musik A Rock A Rock B Latin C Pop D Rock EStellen sie den Join der Tabellen Hört und Spielt dar. Beschreiben Sie, welche Informationen diese Ergebnisrelation liefert.
Entwickeln Sie eine SQL-Abfrage, die alle Lokale liefert, in denen Lisas Lieblingsmusikrichtungen gespielt werden.
Analysieren Sie die folgenden SQL-Abfragen und geben Sie ihre Ausgaben an.
SELECT Musik FROM Hört INNER JOIN Besucht ON Hört.Name = Besucht.Name WHERE Lokal = 'A';SELECT Musik FROM Besucht INNER JOIN Spielt ON Besucht.Lokal = Spielt.Lokal WHERE Name = 'Maxi';SELECT Hört.Name, Spielt.Lokal FROM Hört INNER JOIN Spielt ON Hört.Musik = Spielt.Musik INNER JOIN Besucht ON Hört.Name = Besucht.Name WHERE Spielt.Lokal = Besucht.Lokal;
Betrachten wir ein anderes Szenario. Sie spielen mit ihren kleinen Geschwistern und ihren Eltern ein Reiseziele-Quartett. Hier ist ein Auszug der einzelnen Speilkarten.
Ort: Sydney
Land: Australien
Kontinent: Australien
Entfernung von Deutschland: 16.500 km
Durchschnittstemperatur im Sommer: 22°C
Beliebteste Sehenswürdigkeit: Sydney Opera House
Mittlere Hotelkosten pro Nacht: 150 €
Bestes Reisezeitfenster: September bis November
Besondere Aktivität: Surfen am Bondi Beach
Ort: Paris
Land: Frankreich
Kontinent: Europa
Entfernung von Deutschland: 1.000 km
Durchschnittstemperatur im Sommer: 20°C
Beliebteste Sehenswürdigkeit: Eiffelturm
Mittlere Hotelkosten pro Nacht: 150 €
Bestes Reisezeitfenster: April bis Juni
Besondere Aktivität: Bootsfahrt auf der Seine
Ort: Rio de Janeiro
Land: Brasilien
Kontinent: Südamerika
Entfernung von Deutschland: 9.700 km
Durchschnittstemperatur im Sommer: 30°C
Beliebteste Sehenswürdigkeit: Christusstatue
Mittlere Hotelkosten pro Nacht: 90 €
Bestes Reisezeitfenster: Dezember bis März
Besondere Aktivität: Besuch des Karnevals
- (Partnerarbeit) Erläutern Sie, inwiefern das Reiseziel-Quartett als Datenbank betrachtet werden kann. Geben Sie auch geeignete Attribute für einen Primärschlüssel an.
Häufig möchte man nicht alle Datensätze miteinander, sondern Gruppen von Datensätzen vergleichen.
Zum Beispiel könnte man sich in unserem Quartett für den Durchschnittspreis aller Reiseziele
der einzelnen Kontinente interessieren.
Dies kann mit "GROUP BY" durchgeführt werden. "GROUP BY" erstellt aus den Datensätzen zunächst
Gruppen nach einem vorgegebenen Kriterium. "GROUP BY Kontinent" teilt unsere Quartettkarten
in die folgenden Gruppen ein.
| Gruppe "Europa" |
|---|
| Edinburgh |
| Schwarzwald |
| Santorini |
| ... |
| Gruppe "Südamerika" |
|---|
| Machu Picchu |
| Buenos Aires |
| Rio de Janeiro |
| ... |
| Gruppe "Nordamerika" |
|---|
| Vancouver |
| Honolulu |
| New York City |
| ... |
| Gruppe "Afrika" |
|---|
| Kapstadt |
| Marrakesch |
| Kairo |
| ... |
| Gruppe "Asien" |
|---|
| Tokio |
| Bali |
| Seoul |
| ... |
| Gruppe "Australien" |
|---|
| Queenstown |
| Auckland |
| Sydney |
| ... |
Es werden also für jeden Kontinent zunächst Stapel gebildet. Jetzt können nur noch die Datensätze innerhalb einer Gruppe betrachtet werden.
- (arbeitsteilige Partnerarbeit) Wir betrachten nun die folgenden beiden SQL-Abfragen:
Person 1
SELECT Kontinent, AVG(Mittlere Hotelkosten pro Nacht) FROM Reiseziele GROUP BY Kontinent;Person 2
SELECT Kontinent, MAX(Durchschnittstemperatur im Sommer) FROM Reiseziele GROUP BY Kontinent;Teilen Sie in ihrer Zweiergruppe die beiden SQL-Abfragen untereinander auf.
Nehmen Sie sich je einen Stapel des Reiseziele-Quartetts und versuchen Sie, durch das Bilden einzelner Stapel die Rückgabe der jeweiligen SQL-Abfrage zu ermitteln.Erläutern Sie der zweiten Person ihrer Partnergruppe ihre SQL-Abfrage, ihren Lösungsweg und das Ergebnis.
Hinweis: Nun können Aggregatfunktionen nur noch auf die Gruppen angewendet werden. Es ist mit GROUP BY nicht mehr möglich, alle Datensätze miteinander zu vergleichen. Nur innerhalb der einzelnen Gruppen können Datensätze untereinander verglichen werden.
- Wir betrachten die folgende Situation. Die Datensätze mit den Karten wurden wieder mit "GROUP BY Kontinent" in die folgenden Gruppen eingeteilt.
| Gruppe "Europa" |
|---|
| Edinburgh |
| Schwarzwald |
| Santorini |
| ... |
| Gruppe "Südamerika" |
|---|
| Machu Picchu |
| Buenos Aires |
| Rio de Janeiro |
| ... |
| Gruppe "Nordamerika" |
|---|
| Vancouver |
| Honolulu |
| New York City |
| ... |
| Gruppe "Afrika" |
|---|
| Kapstadt |
| Marrakesch |
| Kairo |
| ... |
| Gruppe "Asien" |
|---|
| Tokio |
| Bali |
| Seoul |
| ... |
| Gruppe "Australien" |
|---|
| Queenstown |
| Auckland |
| Sydney |
| ... |
Erstellen Sie eine SQL-Abfrage, mit welcher die Namen der Kontinente mit ihrer jeweiligen maximalen Durchschnittstemperatur ausgegeben werden.
Untersuchen Sie, welche Ausgabe nach der folgenden SQL-Anweisung getätigt wird.
SELECT Kontinent, MAX(Durchschnittstemperatur im Sommer) AS MaxTemperatur
FROM Reiseziele
GROUP BY Kontinent
HAVING MaxTemperatur > 25
ORDER BY MaxTemperatur ASC;Datensätze wurden bisher mit "WHERE" nach bestimmten Kriterien gefiltert. Sobald man mit
"GROUP BY" eine Gruppierung der Datensätze vorgenommen hat, kann man allerdings nicht weiter
mit WHERE filtern, da man auf die einzelnen Datensätze in ihrer Gesamtheit keinen Zugriff mehr
hat.
Dies kann man allerdings mit der Anweisung "HAVING" tun, welches die Ergebnismenge nach
SELECT ...
FROM ...
WHERE ...
GROUP BY ...;mit Hilfe unterschiedlicher Aggregatsfunktionen einschränkt. "HAVING" löst das Problem, dass "WHERE" nicht mit Aggregatsfunktionen wie AVG(), COUNT(), MAX(), MIN(), SUM() usw. interagieren kann. "HAVING" wird hinter die Anweisungen "WHERE" (falls vorhanden) und "GROUP BY", aber vor "ORDER BY" gesetzt.
- Geben Sie an, was nach den folgenden SQL-Abfragen ausgegebn wird.
SELECT Land, AVG(Hotelkosten) AS DurchschnittlicheHotelkosten, COUNT(*) AS Anzahl-Reiseziele
FROM Reiseziele
GROUP BY Land
HAVING COUNT(*) > 2;SELECT Kontinent, AVG(Entfernung) AS DurchschnittlicheEntfernung, MAX(Entfernung) AS MaximaleEntfernung
FROM Reiseziele
GROUP BY Kontinent
HAVING AVG(Hotelkosten) < 130;SELECT Kontinent, AVG(Entfernung) AS DurchschnittlicheEntfernung
FROM Reiseziele GROUP
BY Kontinent HAVING COUNT(*) >= 3;SELECT Kontinent, AVG(Hotelkosten) AS DurchschnittlicheHotelkosten, COUNT(*) AS Anzahl-Reiseziele
FROM Reiseziele
GROUP BY Kontinent
HAVING AVG(Entfernung) > 5000;SELECT Kontinent, AVG(Durchschnittstemperatur)
AS DurchschnittlicheTemperatur, MAX(Durchschnittstemperatur) AS Maximale-Temperatur
FROM Reiseziele
GROUP BY Kontinent
HAVING MAX(Durchschnittstemperatur) > 35;SELECT Kontinent, AVG(Hotelkosten) AS DurchschnittlicheHotelkosten
FROM Reiseziele
GROUP BY Kontinent HAVING MAX(Durchschnittstemperatur) > 20;- Erstellen Sie jeweils eine SQL-Abfrage, welche die folgenden Informationen liefert.
Durchschnittliche Hotelkosten pro Kontinent, nur für Kontinente mit mindestens 4 Reisezielen
Kontinente mit einer durchschnittlichen Entfernung von Deutschland über 5.000 km
Durchschnittliche Hotelkosten pro Kontinent, nur für Kontinente mit maximaler Durchschnittstemperatur über 30°C
Durchschnittliche Entfernung und Anzahl der Reiseziele pro Kontinent, nur für Kontinente mit durchschnittlichen Hotelkosten unter 150 €
Kontinente mit mindestens einem Reiseziel, dessen Hotelkosten über 200 € liegen und einer durchschnittlichen Entfernung unter 8.000 km
Kontinente mit durchschnittlichen Hotelkosten unter 150 € und einer maximalen Durchschnittstemperatur über 25°C
Das Ergebnis einer SQL-Abfrage wird oft in einer weiteren, äußeren SQL-Abfrage genutzt. Man erhält
so geschachtelte Abfragen.
Möchte man zum Beispiel alle Bücher finden, die vom Autor von "The Lord of the Rings" geschrieben
wurden, kann dies zweistufig geschehen.
SELECT Autor
FROM Buch
WHERE Titel = 'The Lord of the Rings';→
| Autor |
|---|
| J.R.R. Tolkien |
Dieses Ergebnis kann nun genutzt werden.
SELECT Titel, Autor
FROM Buch
WHERE Autor = (
SELECT Autor
FROM Buch
WHERE Titel = 'The Lord of the Rings');→
| Titel | Autor |
|---|---|
| Der Hobbit | J.R.R. Tolkien |
| Der Hobbit | J.R.R. Tolkien |
| THe Lord of the Rings | J.R.R. Tolkien |
Zeige alle Bücher an, die günstiger sind als A Tale of Two Cities.
SELECT Titel, Autor, Preis
FROM Buch
WHERE Preis < (
SELECT Preis
FROM Buch
WHERE Titel = 'A Tale of Two Cities');→
| Titel | Autor | Preis |
|---|---|---|
| Ich, der Roboter | Isaac Asimov | 10,00 |
| Der Hobbit | J.R.R. Tolkien | 9,95 |
Die innere Abfrage bei Schachtelungen muss immer auf ein einzelnes Attribut projizieren. In den obigen Beispielen einmal auf J.R.R. Tolkien und 11,95.
- Im Folgenden werden wir uns mit dem SQL-Tutorial beschäftigen. Bearbeiten Sie die Lektionen 3 bis 7.
Machen Sie sich zunächst mit der Struktur der Datenbank vertraut.
- Eine Schülerfirma verkauft an ihrer Schule Pausensnacks und verwaltet die Produkte in einer Tabelle.
Entwickeln Sie bezogen auf diese Tabelle die folgenden SQL-Abfragen. Sollten Sie bei einer Aufgaben nicht weiterkommen, überspringen Sie diese einfach.Produkte ArtikelNr Artikel Rubrik Menge Einkaufspreis Verkaufspreis 100 Apfel Obst 16 0,44 0,80 101 Birne Obst 5 0,44 0,80 200 Käsebrötchen Snack 21 0,86 1,20 300 Tomaten Gemüse 18 1,05 1,50 301 Minigurke Gemüse 13 0,69 1,00 400 Wasser Getränke 48 0,23 0,50 102 Orange Obst 35 0,78 1,10 201 Pizzaschnecke Snack 52 1,12 1,50 401 OSaft Getränke 64 0,95 1,30 500 Müsliriegel Süßigkeiten 81 0,89 1,15 501 Schokoriegel Süßigkeiten 29 1,20 1,80
Wenn Sie Ihre Ergebnisse testen wollen, können Sie das auf der Website myCompiler machen. Dort ist die Datenbank "Produkte" angelegt und Sie können ganz unten Ihre SQL-Abfragen eingeben.
Welche Artikel kosten 0,8-1,20 €?
Bei welchem Obst ist der Bestand auf unter 20 zurückgegangen?
Bei welchen Artikeln (Angabe von ArtikelNr, Artikel, Verkaufspreis, Gewinn) verdient die Schülerfirma mehr als 40 Cent?
Wie viele Rubriken werden angeboten?
Wie viele verschiedene Artikel werden angeboten?
Welche Artikel sind im Einkauf günstiger als Orangen?
Welcher Artikel wirft den meisten Gewinn ab?
Aus welcher Rubrik werden die meisten unterschiedlichen Artikel angeboten?
Berechne den Gewinn für jedes Produkt (Verkaufspreis - Einkaufspreis) und liste die ArtikelNr, Artikel und den Gewinn auf.
Finde alle Produkte, deren Menge weniger als 10 beträgt.
Berechne den durchschnittlichen Einkaufspreis und den durchschnittlichen Verkaufspreis für alle Produkte.
Finde die Rubrik mit der höchsten Anzahl an Produkten.
Wenn alle Produkte verkauft wurden, die nun noch da sind, wie groß wäre der Gesamtumsatz (Verkaufspreis * Menge) für alle Produkte?
Liste alle Produkte auf, bei denen die Gewinnmarge (Gewinn / Verkaufspreis) mehr als 20% beträgt.
Ermitteln Sie den teuersten/günstigsten Artikel bezogen auf den Verkaufspreis, wenn Sie können auch beides in einer Abfrage. Es soll nur der Artikel angezeigt werden, sonst nichts.
Mögliche Lösungen sehen Sie hier:
SELECT *
FROM Produkte
WHERE Verkaufspreis BETWEEN 0.8 AND 1.20;SELECT *
FROM Produkte
WHERE Rubrik = 'Obst' AND Menge < 20;SELECT ArtikelNr, Artikel, Verkaufspreis, (Verkaufspreis - Einkaufspreis) AS Gewinn
FROM Produkte
WHERE (Verkaufspreis - Einkaufspreis) > 0.40;SELECT COUNT(DISTINCT Rubrik) AS Anzahl_Rubriken
FROM Produkte;SELECT COUNT(DISTINCT Artikel) AS Anzahl_Artikel
FROM Produkte;SELECT *
FROM Produkte
WHERE Einkaufspreis < (
SELECT Einkaufspreis
FROM Produkte
WHERE Artikel = 'Orange');SELECT ArtikelNr, Artikel, (Verkaufspreis - Einkaufspreis) AS Gewinn
FROM Produkte
ORDER BY Gewinn DESC
LIMIT 1;SELECT Rubrik, COUNT(DISTINCT Artikel) AS Anzahl_Artikel
FROM Produkte
GROUP BY Rubrik
ORDER BY Anzahl_Artikel DESC
LIMIT 1;SELECT ArtikelNr, Artikel, (Verkaufspreis - Einkaufspreis) AS Gewinn
FROM Produkte;SELECT *
FROM Produkte
WHERE Menge < 10;SELECT AVG(Einkaufspreis) AS Durchschnitt_Einkaufspreis,
AVG(Verkaufspreis) AS Durchschnitt_Verkaufspreis
FROM Produkte;SELECT Rubrik, COUNT(*) AS Anzahl_Produkte
FROM Produkte
GROUP BY Rubrik
ORDER BY Anzahl_Produkte DESC
LIMIT 1;SELECT SUM(Verkaufspreis * Menge) AS Gesamtumsatz
FROM Produkte;SELECT ArtikelNr, Artikel, (Verkaufspreis - Einkaufspreis) / Verkaufspreis AS Gewinnmarge
FROM Produkte
HAVING Gewinnmarge > 0.2;(SELECT ArtikelNr, Artikel, Verkaufspreis
FROM Produkte)
UNION
(SELECT ArtikelNr, Artikel, Verkaufspreis
FROM Produkte);