SQL: Unterschied zwischen den Versionen

Aus SibiWiki
Zur Navigation springen Zur Suche springen
 
(53 dazwischenliegende Versionen von 2 Benutzern werden nicht angezeigt)
Zeile 3: Zeile 3:
[[Kategorie:Informatik]]
[[Kategorie:Informatik]]


<font color='red'>'''''Diese Seite ist für die Oberstufe (Klasse 11+12). Für SQL in der 9. Klasse hier klicken: [[SQL-09]]'''''</font>
<font color='red'>'''''Diese Seite ist für die Oberstufe (Jahrgang 11-13). Für SQL in der 10. Klasse hier klicken: [[SQL-09]]'''''</font>


Hier finden sich Erläuterungen zu allen SQL-Techniken, die für das Zentralabitur wichtig sind.
Hier finden sich Erläuterungen zu allen SQL-Techniken, die für das Zentralabitur wichtig sind.
Zeile 9: Zeile 9:
Zu diesen Techniken gibt es auch '''[[SQL-Aufgaben|Aufgaben]]''', die man zur Übung selber bearbeiten kann.
Zu diesen Techniken gibt es auch '''[[SQL-Aufgaben|Aufgaben]]''', die man zur Übung selber bearbeiten kann.


=Erklärvideos=
* [https://youtu.be/J425xx6PVxs Abfragen mit GROUP BY (06:15min)]


Ein großer Teil der SQL-Abfragen realisiert die sog. '''Relationenalgebra''', welche hier im Detail erklärt wird.
* [https://youtu.be/ZPna0CmGuQg Abfragen über mehrere Tabellen]


Um einen verständlichen Aufbau dieser Seite hinzukriegen, wird die Relationenalgebra in zwei Teile geteilt:
* [https://youtu.be/rUkVjvlX2Eg Abfragen mit NOT IN bzw. mit IN]
* Relationenalgebra 1
* Relationenalgebra 2
Dazwischengeschoben werden die Aggregatfunktionen, GROUP BY und mehrere Bedingungen (AND, OR, BETWEEN).


==Grundlegende SQL-Befehle==
* [https://youtu.be/TZjt6gvRfl8 Abfrage über selbstdefinierte Tabellen]
* Informationen des Zentralabiturs (PDF): [[Datei:Datenbanken-Abi-2012.pdf]]
 
* '''SQL-Sprachelemente im Zentralabitur: '''
* [https://youtu.be/GohWF9SnhWM ChatGPT nutzen, um SQL zu schreiben]
** SELECT, SELECT DISTINCT, FROM, WHERE, GROUP BY, ORDER BY, ASC, DESC,  JOIN, LEFT JOIN, RIGHT JOIN, ON, UNION, AS
 
** Vergleichsoperatoren: =, !=, >, <, >=, <=, LIKE, BETWEEN, IN, NOT IN, IS NULL
=Fachbegriffe=
** Logische Operatoren: AND, OR, NOT
Vereinigung, Differenz, kartesisches Produkt, Abgleich der Tabellen über..., geschachtelte Abfrage, äußere Abfrage, innere Abfrage<br/>
** Funktionen: COUNT, SUM, MAX, MIN, AVG
 
* '''Was man noch können muss:'''
Außerdem die SQL-Sprachelemente (s.u.)
** Abfragen über mehrere Tabellen (mit kartesischem Produkt und Abgleich bzw. mit JOIN)
 
** [[SQL#Geschachtelte_SQL-Ausdrücke|Geschachtelte SQL-Ausdrücke]]
==SQL-Sprachelemente im Zentralabitur==
 
Informationen des Zentralabiturs (PDF): [[Datei:Datenbanken-Abi-2012.pdf]]
 
'''Die folgenden SQL-Befehle muss man im Zentralabitur beherrschen:'''
* SELECT, SELECT DISTINCT, FROM, WHERE, GROUP BY, ORDER BY, ASC, DESC,  JOIN, LEFT JOIN, RIGHT JOIN, ON, UNION, AS
* Vergleichsoperatoren: =, !=, >, <, >=, <=, LIKE, BETWEEN, IN, NOT IN, IS NULL
* Logische Operatoren: AND, OR, NOT
* Funktionen: COUNT, SUM, MAX, MIN, AVG
 
* sonstiges: Die Joker % und *
 
'''Was man noch können muss:'''
* '''[[SQL#Abfragen_über_mehrere_Tabellen|Abfragen über mehrere Tabellen]]''' formulieren:
** mit '''[[SQL#Kartesisches_Produkt|kartesischem Produkt]]''' und '''[[SQL#Abgleich_zwischen_mehreren_Tabellen|Abgleich]]'''
** mit '''[[SQL#Join|JOIN]]''' bzw. '''LEFT JOIN''' und '''RIGHT JOIN'''
** mit '''[[SQL#Differenz_(NOT_IN)|Differenz (NOT IN)]]'''
* '''[[SQL#Geschachtelte_SQL-Ausdrücke|Geschachtelte SQL-Ausdrücke]]'''
* '''[[SQL#SQL_analysieren|SQL analysieren]]''': Häufig sind das geschachtelte SQL-Abfragen.


==Beispieldatenbank Schule==
==Beispieldatenbank Schule==
* An der Beispieldatenbank Schule können alle möglichen Datenbankabfragen durchprobiert werden.
* An der Beispieldatenbank Schule können alle möglichen Datenbankabfragen durchprobiert werden.
* Die Beispieldatenbank ist mit Absicht sehr schmal gehalten, damit man die Ergebnisse von SQL-Abfragen noch überprüfen kann.
* Die Beispieldatenbank ist mit Absicht sehr schmal gehalten, damit man die Ergebnisse von SQL-Abfragen noch überprüfen kann.
* Testen kann man SQL-Abfragen auf der Datenbank Schule '''[http://sibiwiki.de/informatikag/videodb/index.php hier]'''. Die Zugangsdaten gibt's bei Herrn Kaibel
* Testen kann man SQL-Abfragen auf der Datenbank Schule: '''[http://sibiwiki.de/sql/ hier klicken]'''.<br/>Die Zugangsdaten gibt's bei Herrn Kaibel
** ''Wer nicht am SIBI ist, kann hier einen SQL-Dump der Beispieldatenbank Schule herunterladen: <br/>[[Datei:Beispieldatenbank-schule.zip]]''


[[Datei:Beispieldatenbank-schule.jpg]]
[[Datei:Beispieldatenbank-schule.jpg]]
<br/>
===relationales Datenmodell der Datenbank Schule===
<code>
  schueler(<u>id</u>,↑klasse_id,name,vorname)
  klasse(<u>id</u>,↑klassenlehrer_id,name)
  lehrer(<u>id</u>,name,vorname)
  unterricht(<u>id</u>,↑klasse_id,↑lehrer_id,↑raum_id,fach,stunden)
  raum(<u>id</u>,nummer,etage,plaetze)
  ag(<u>id</u>,↑lehrer_id,name)
  teilnahme(<u>↑schueler_id</u>,<u>↑ag_id</u>)
</code>


==Aufbau von SQL-Abfragen==
==Aufbau von SQL-Abfragen==
Für den Aufbau von SQL-Abfragen gilt folgende Reihenfolge:
Für den Aufbau von SQL-Abfragen gilt folgende Reihenfolge:
<code>
<code>
  '''SELECT ...'''
  '''SELECT ...'''
  '''FROM ...'''
  '''FROM ...'''
Zeile 44: Zeile 73:
  ORDER BY ...
  ORDER BY ...
  LIMIT ...
  LIMIT ...
</code>
</code>
<code>SELECT</code> und <code>FROM</code> müssen auf jeden Fall dabei sein, die anderen Teile sind optional.
<code>SELECT</code> und <code>FROM</code> müssen auf jeden Fall dabei sein, die anderen Teile sind optional.


Zeile 52: Zeile 82:
Bei Datenbanken versteht man unter einer '''Relationenalgebra''' eine formale Sprache, mit der sich Abfragen über eine oder mehrere Tabellen (=Relationen) formulieren lassen. Sie erlaubt es, Tabellen miteinander zu verknüpfen, zu reduzieren oder komplexere Informationen daraus herzuleiten.
Bei Datenbanken versteht man unter einer '''Relationenalgebra''' eine formale Sprache, mit der sich Abfragen über eine oder mehrere Tabellen (=Relationen) formulieren lassen. Sie erlaubt es, Tabellen miteinander zu verknüpfen, zu reduzieren oder komplexere Informationen daraus herzuleiten.


===Selektion===
===Projektion (SELECT) ===
Unter Projektion versteht man die Reduktion der Informationen in dem Ergebnis auf ein oder mehrere Attribute (=Spalten). <br/>
In unserem Beispiel werden zusätzlich doppelte Zeilen werden entfernt. Dafür braucht man das Schlüsselwort <code>DISTINCT</code>, das hat aber mit der eigentlichen Projektion auf einzelne Spalten nichts zu tun.
 
'''Beispiel:'''
 
Formuliere für die Tabelle <code>raum</code> eine Projektionsabfrage, die die Etagen mit Projektion wiedergibt.
 
<code>
  <u>'''SELECT'''</u> DISTINCT r.etage
  FROM raum r
</code>
 
''Beachte: Entscheidend für die Projektion ist es, dass man bei <code>SELECT</code> einzelne Attribute angibt.''
 
===Selektion (WHERE)===
Selektion bedeutet, dass Datensätze (=Zeilen) ausgewählt werden, die eine bestimmte Bedingung erfüllen.
Selektion bedeutet, dass Datensätze (=Zeilen) ausgewählt werden, die eine bestimmte Bedingung erfüllen.


Formuliere eine Selektionsabfrage, die alle Nummern der Räume mit mindestens 30 Plätzen zurückgibt.
Formuliere eine Selektionsabfrage, die alle Nummern der Räume mit mindestens 30 Plätzen zurückgibt.


<code>
<code>
   SELECT r.nummer
   SELECT r.nummer
   FROM raum r
   FROM raum r
   <u>'''WHERE'''</u> r.plaetze >= 30
   <u>'''WHERE'''</u> r.plaetze >= 30
</code>
</code>


''Beachte: Das entscheidende Schlüsselwort für die Selektion ist <code>WHERE</code>, denn hier wird die Bedingung festgelegt.''
''Beachte: Das entscheidende Schlüsselwort für die Selektion ist <code>WHERE</code>, denn hier wird die Bedingung festgelegt.''
Zeile 67: Zeile 112:
Formuliere eine Selektionsabfrage, die <u>alle</u> Informationen zu Räumen mit mindestens 30 Plätzen zurückgibt.
Formuliere eine Selektionsabfrage, die <u>alle</u> Informationen zu Räumen mit mindestens 30 Plätzen zurückgibt.


<code>
<code>
   SELECT <u>*</u>
   SELECT <u>*</u>
   FROM raum r
   FROM raum r
   <u>'''WHERE'''</u> r.plaetze >= 30
   <u>'''WHERE'''</u> r.plaetze >= 30
</code>
</code>


''Beachte: Mit dem Joker * im SELECT-Teil der Abfrage werden alle Attribute ausgewählt.''
''Beachte: Mit dem Joker * im SELECT-Teil der Abfrage werden alle Attribute ausgewählt.''


===Projektion===
Unter Projektion versteht man die Reduktion der Informationen in dem Ergebnis auf ein oder mehrere Attribute (=Spalten). Doppelte Zeilen werden dabei entfernt.


Formuliere für die Tabelle raum eine Projektionsabfrage, die die Etagen mit Projektion wiedergibt.
=Erläuterung einzelner SQL-Befehle=
Im folgenden werden einzelne SQL-Befehle jeweils am Beispiel erläutert.


<code>
== Aggregat-Funktionen (COUNT, MAX, MIN, SUM, AVG) ==
  SELECT <u>'''DISTINCT r.etage'''</u>
Mit den sog. Aggregat-Funktionen kann man Attributwerte aus mehreren Zeilen zusammenfassen.
  FROM raum r
</code>
 
''Beachte: Entscheidend für die Projektion ist es, dass man bei <code>SELECT</code> einzelne Attribute angibt (anstelle von <code>*</code>).''


''Mithilfe des Schlüsselwortes <code>DISTINCT</code> erreicht man, dass die doppelten Zeilen entfernt werden.''
=== Kurze Erklärung ===
* '''COUNT''': <u>zählt</u>
* '''SUM''': <u>addiert</u>
* '''AVG''': berechnet den Durchschnitt (average)
* '''MAX''': bestimmt das Maximum
* '''MIN''': bestimmt das Minimum


== Aggregat-Funktionen (COUNT, MAX, MIN, SUM, AVG) ==
===Beispiele===
Mit den sog. Aggregat-Funktionen kann man Attributwerte aus mehreren Zeilen zusammenfassen.


'''Beispiel 1:'''
'''Beispiel 1:'''


Mit der folgenden Abfrage kann man ermitteln, wie viele Schüler es gibt.
Mit der folgenden Abfrage kann man ermitteln, wie viele Schüler es gibt.<br/>
Man <u>zählt</u> die Zeilen; deswegen braucht man COUNT (und nicht SUM).


<code>
<code>
   SELECT <u>'''COUNT(*)'''</u>
   SELECT <u>'''COUNT(*)'''</u>
   FROM schueler s
   FROM schueler s
</code>
</code>


Hier werden die Zeilen <u>gezählt</u>. Mit <code>COUNT(<u>'''*'''</u>)</code> wird nicht ein Attribut gezählt, sondern jede Zeile, die überhaupt irgendein Attribut enthält.
Hinweis:<br/>  
COUNT(*) übersetzt sich am einfachsten als "Zähle die Zeilen". <br/>
Wenn man stattdessen COUNT(s.name) nehmen würde, dann würden nur die Zeilen gezählt, wo beim Namen ein Eintrag vorhanden ist. (Das sind hier aber alle).


'''Beispiel 2:'''
'''Beispiel 2:'''


Mit der folgenden Abfrage kann man ermitteln, wie viele Stunden Sport unterrichtet werden.
Mit der folgenden Abfrage kann man ermitteln, wie viele Stunden Sport unterrichtet werden.<br/>
Hier muss man die Attributwerte <code>stunden</code> <u>addieren</u>, deswegen braucht man SUM.


<code>
<code>
   SELECT <u>'''SUM(u.stunden)'''</u>
   SELECT <u>'''SUM(u.stunden)'''</u>
   FROM unterricht u
   FROM unterricht u
   WHERE u.fach = 'Sport'
   WHERE u.fach = 'Sport'
</code>
</code>
 
'''Beispiel 3:'''
 
Mit der folgenden Abfrage ermittelt man...
* die maximale Zahl der Plätze in einem Raum,
* die minimale Zahl der Plätze in einem Raum,
* die durchschnittliche Zahl der Plätze in allen Räumen.
 
<code>
  SELECT <u>'''MAX(r.plaetze)''', '''MIN(r.plaetze)''', '''AVG(r.plaetze)'''</u>
  FROM raum r
</code>
 
=== Wann braucht man COUNT(...) und wann braucht man SUM(...) ?! ===
COUNT und SUM werden leicht verwechselt!
 
Als Faustregel kann man sagen:
* COUNT, wenn <u>gezählt</u> wird,
* SUM, wenn <u>addiert</u> wird.
 
Das ist aus der Aufgabenstellung nicht immer offensichtlich!
 
Beispiel 1:<br/>
Man hätte gerne die Anzahl der Plätze in allen Räumen.
 
Beispiel 2:<br/>
Man hätte gerne die Anzahl der Räume.
 
Lösung:
* in Beispiel 1 wird die Anzahl der Plätze (im Attribut plaetze) <u>addiert</u>, deswegen SUM(r.plaetze)
* in Beispiel 2 wird die Anzahl der Räume <u>gezählt</u>, deswegen COUNT(r.id) oder COUNT(*)
 
===COUNT(DISTINCT ...)===
'''Beispiel:'''<br/>
Wie viele Fächer gibt es?
<code>
  SELECT COUNT(DISTINCT u.fach)
  FROM unterrricht u
</code>


Hier werden die Attributwerte <code>stunden</code> <u>summiert</u> (d.h. zusammenaddiert).
Durch DISTINCT wird jedes Fach nur einmal berücksichtigt!


==LIKE und der %-Joker==
==LIKE und der %-Joker==
Zeile 124: Zeile 211:
Eine Liste aller Schüler, die ein Doppel-n ('nn') im Vornamen haben.
Eine Liste aller Schüler, die ein Doppel-n ('nn') im Vornamen haben.


<code>
<code>
  SELECT s.vorname AS vorname, s.name AS name
  SELECT s.vorname AS vorname, s.name AS name
  FROM schueler s
  FROM schueler s
  WHERE s.vorname <u>LIKE '%nn%'</u>
  WHERE s.vorname <u>LIKE '%nn%'</u>
</code>
</code>


''Beachte: Vor und hinter dem nn steht ein %-Joker, d.h. hier können beliebige Buchstaben stehen. "Beliebig" kann auch heißen: Kein Buchstabe! D.h. "Johann" wäre auch ein akzeptierter Vorname.
''Beachte: Vor und hinter dem nn steht ein %-Joker, d.h. hier können beliebige Buchstaben stehen. "Beliebig" kann auch heißen: Kein Buchstabe! D.h. "Johann" wäre auch ein akzeptierter Vorname.
Zeile 139: Zeile 226:
Mit der folgenden Abfrage erhält man eine Liste der Etagen, und für jede Etage wird notiert, wie viele Plätze in ihr insgesamt enthalten sind.
Mit der folgenden Abfrage erhält man eine Liste der Etagen, und für jede Etage wird notiert, wie viele Plätze in ihr insgesamt enthalten sind.


<code>
<code>
   SELECT r.etage, SUM(plaetze)
   SELECT r.etage, SUM(plaetze)
   FROM raum r
   FROM raum r
   <u>'''GROUP BY'''</u> r.etage
   <u>'''GROUP BY'''</u> r.etage
</code>
</code>


''Beachte: Durch <code>GROUP BY r.etage</code> werden alle Zeilen, die bei <code>r.etage</code> den Wert 'unten' haben, in einer Zeile zusammengefasst. Genauso alle Zeilen, die bei <code>r.etage</code> den Wert 'Mitte' (bzw. 'oben') haben.''
''Beachte: Durch <code>GROUP BY r.etage</code> werden alle Zeilen, die bei <code>r.etage</code> den Wert 'unten' haben, in einer Zeile zusammengefasst. Genauso alle Zeilen, die bei <code>r.etage</code> den Wert 'Mitte' (bzw. 'oben') haben.''


''Dadurch, dass man so zusammenfasst, kann jetzt für jede Etage die Summe der Plätze berechnet werden.''
''Dadurch, dass man so zusammenfasst, kann jetzt für jede Etage die Summe der Plätze berechnet werden.''
<p>
<b>[https://youtu.be/J425xx6PVxs Erklärvideo zu GROUP BY (06:15min)]</b>
</p>


==Umbenennung==
==Umbenennung (AS)==
Attribute können umbenannt werden.  
Attribute können umbenannt werden.  


Zeile 156: Zeile 246:
Formuliere eine Abfrage über die Attribute klasse_id, lehrer_id, raum_id, fach und stunden der Tabelle unterricht. Dabei soll klasse_id in klassen-nr, lehrer_id in lehrer_nr und raum_id in raum-nr umbenannt werden.
Formuliere eine Abfrage über die Attribute klasse_id, lehrer_id, raum_id, fach und stunden der Tabelle unterricht. Dabei soll klasse_id in klassen-nr, lehrer_id in lehrer_nr und raum_id in raum-nr umbenannt werden.


<code>
<code>
  SELECT u.klasse_id '''AS''' klassennr,u.lehrer_id '''AS''' Lehrernr,u.raum_id '''AS''' Raumnr,u.fach,u.stunden '''AS''' stunden
  SELECT u.klasse_id '''AS''' klassennr,u.lehrer_id '''AS''' Lehrernr,u.raum_id '''AS''' Raumnr,u.fach,u.stunden '''AS''' stunden
  FROM unterricht u
  FROM unterricht u
</code>
</code>


'''Beispiel 2:'''
'''Beispiel 2:'''
Zeile 166: Zeile 256:
<u>Die Liste soll nach der Anzahl der Plätze sortiert sein</u>.
<u>Die Liste soll nach der Anzahl der Plätze sortiert sein</u>.


<code>
<code>
   SELECT r.etage, SUM(plaetze) <u>AS PlaetzeInEtage</u>
   SELECT r.etage, SUM(plaetze) <u>AS PlaetzeInEtage</u>
   FROM raum r
   FROM raum r
   GROUP BY r.etage
   GROUP BY r.etage
   <u>ORDER BY PlaetzeInEtage</u>
   <u>ORDER BY PlaetzeInEtage</u>
</code>
</code>


''Beachte: Für <code>SUM(plaetze)</code> wird ein Alias (<code>PlaetzeInEtage</code>) vergeben. Mithilfe des Alias kann man am Ende nach der Anzahl der Plätze sortieren!''
''Beachte: Für <code>SUM(plaetze)</code> wird ein Alias (<code>PlaetzeInEtage</code>) vergeben. Mithilfe des Alias kann man am Ende nach der Anzahl der Plätze sortieren!''
Zeile 183: Zeile 273:
'''Beispiel:'''
'''Beispiel:'''


<code>
<code>
   SELECT r.nummer, r.plaetze
   SELECT r.nummer, r.plaetze
   FROM raum r
   FROM raum r
   WHERE r.etage = 'unten' <u>'''OR'''</u> r.etage = 'Mitte'
   WHERE r.etage = 'unten' <u>'''OR'''</u> r.etage = 'Mitte'
</code>
</code>


===BETWEEN===
===BETWEEN===
Zeile 194: Zeile 284:
'''Beispiel:'''
'''Beispiel:'''


<code>
<code>
   SELECT r.nummer, r.plaetze
   SELECT r.nummer, r.plaetze
   FROM raum r
   FROM raum r
   WHERE r.plaetze <u>'''BETWEEN'''</u> 30 AND 50
   WHERE r.plaetze <u>'''BETWEEN'''</u> 30 '''AND''' 50
</code>
</code>


===LIMIT===
===LIMIT===
Zeile 209: Zeile 299:
(d.h. der Raum mit den meisten Plätzen) wird ausgegeben.
(d.h. der Raum mit den meisten Plätzen) wird ausgegeben.


<code>
<code>
   SELECT r.nummer, r.plaetze
   SELECT r.nummer, r.plaetze
   FROM raum r
   FROM raum r
   ORDER BY r.plaetze DESC
   ORDER BY r.plaetze DESC
   <u>'''LIMIT 1'''</u>
   <u>'''LIMIT 1'''</u>
</code>
</code>


=Abfragen über <u>mehrere</u> Tabellen=
=Abfragen über <u>mehrere</u> Tabellen=


===Vereinigung===
===Vereinigung (UNION)===
Vereinigung bedeutet, dass die Ergebnis-Datensätze von zwei Abfragen zu einem Ergebnis zusammengefügt werden. Voraussetzung dafür ist, dass die Attribute gleich sind.
Vereinigung bedeutet, dass die Ergebnis-Datensätze von zwei Abfragen zu einem Ergebnis zusammengefügt werden. <br/>Voraussetzung dafür ist, dass beide Abfragen <u>gleich viele</u> Attribute abfragen.


Formuliere eine Abfrage, die Name und Vorname von allen Lehrern UND Schülern wiedergibt.
Formuliere eine Abfrage, die Name und Vorname von allen Lehrern UND Schülern wiedergibt.


<code>
<code>
  SELECT l.name, l.vorname
  SELECT l.name, l.vorname
  FROM lehrer l
  FROM lehrer l
Zeile 229: Zeile 319:
  SELECT s.name, s.vorname
  SELECT s.name, s.vorname
  FROM schueler s
  FROM schueler s
</code>
</code>


===Differenz===
===Differenz (NOT IN) ===
Differenz bedeutet, dass die Ergebnis-Datensätze einer Abfrage a1 von den Ergebnis-Datensätzen einer Abfrage a0 abgezogen werden - es bleibt dann ein Rest übrig.
Differenz bedeutet, dass die Ergebnis-Datensätze einer Abfrage a1 von den Ergebnis-Datensätzen einer Abfrage a0 abgezogen werden - es bleibt dann ein Rest übrig.


Formuliere eine Abfrage, die alle Klassen ausgibt, die KEIN Mathematik haben.
Formuliere eine Abfrage, die alle Klassen ausgibt, die KEIN Mathematik haben.


Idee: Die Schüler, die Mathematik haben, von allen Schülern abziehen.
Idee: Die Klassen, die Mathematik haben, von allen Klassen abziehen.


<code>
<code>
  SELECT k.name
  SELECT k.name
  FROM klasse k
  FROM klasse k
  WHERE k.id <u>'''NOT IN'''</u>(SELECT u.klasse_id  
  WHERE k.id <u>'''NOT IN'''</u>
                  FROM unterricht u  
(
                  WHERE u.fach='Mathe')
    SELECT u.klasse_id  
</code>
    FROM unterricht u  
    WHERE u.fach='Mathe'
)
</code>
 
===IN===
Das logische Gegenteil zu NOT IN ist entsprechend IN.
Dieses Schlüsselwort kann man u.a. verwenden, wenn man in einer Abfrage zwei Datensätze einer Entitätsmenge hat.
Beispiel: Welche Schüler sind in der selben Klasse wie Anne Ebert?
 
Idee: In der äußeren Abfrage werden die Schüler gesucht; mit IN kann man dann die klasse_id von Anne Ebert angeben.
 
<code>
SELECT s.name, s.vorname
FROM schueler s
WHERE s.klasse_id <u>'''IN'''</u>
(
    SELECT s2.klasse_id
    FROM schueler s2
    WHERE s2.name = 'Ebert'
)
</code>


===Kartesisches Produkt===
===Kartesisches Produkt===
Zeile 253: Zeile 364:
Formuliere eine Abfrage für das kartesische Produkt von den Tabellen klasse und schueler.  
Formuliere eine Abfrage für das kartesische Produkt von den Tabellen klasse und schueler.  


<code>
<code>
  SELECT *
  SELECT *
  FROM <u>'''klasse k, schueler s'''</u>  
  FROM <u>'''klasse k, schueler s'''</u>  
</code>
</code>


Diese Abfrage liefert als Ergebnis "Jeder mit jedem". Inhaltlich ist das nicht sinnvoll.
Diese Abfrage liefert als Ergebnis "Jeder mit jedem". Inhaltlich ist das nicht sinnvoll.
Zeile 263: Zeile 374:




===SQL-Abfrage über mehrere verknüpfte Tabellen===
===Abgleich zwischen mehreren Tabellen===
Formuliere eine Abfrage, die angibt, in welchen Räumen die Klasse 8a unterrichtet wird.
Formuliere eine Abfrage, die angibt, in welchen Räumen die Klasse 8a unterrichtet wird.
Die Abfrage läuft über die Tabellen klasse, unterricht und raum.
Die Abfrage läuft über die Tabellen klasse, unterricht und raum.


<code>
<code>
  SELECT r.nummer
  SELECT r.nummer
  FROM klasse k, unterricht u, raum r   
  FROM klasse k, unterricht u, raum r   
Zeile 273: Zeile 384:
  <u>'''AND u.klasse_id=k.id'''</u>
  <u>'''AND u.klasse_id=k.id'''</u>
  AND k.name = '8a'
  AND k.name = '8a'
</code>
</code>


'''Wichtig:''' bei Abfragen über mehrere Tabellen braucht man einen Abgleich weniger als man Tabellen abfragt.
'''Wichtig:''' bei Abfragen über mehrere Tabellen braucht man einen Abgleich weniger als man Tabellen abfragt.
Zeile 279: Zeile 390:
Im Beispiel werden drei Tabellen abgefragt; deswegen braucht man zwei Abgleiche.
Im Beispiel werden drei Tabellen abgefragt; deswegen braucht man zwei Abgleiche.


===Join===
===JOIN / INNER JOIN===
Join ist die Bildung eines [[SQL#Kartesisches_Produkt|kartesischen Produktes]] gefolgt von einer Selektion, die den (richtigen) Abgleich zwischen den beiden Tabellen leistet.
Join ist die Bildung eines [[SQL#Kartesisches_Produkt|kartesischen Produktes]] gefolgt von einer Selektion, die den (richtigen) Abgleich zwischen den beiden Tabellen leistet.


Beispiel: Eine Liste der Schüler, in der für jeden Schüler vermerkt wird, in welcher Klasse er ist.
Beispiel: Eine Liste der Schüler, in der für jeden Schüler vermerkt wird, in welcher Klasse er ist.


<code>
<code>
  SELECT s.name AS schuelername, k.name AS klassename
  SELECT s.name AS schuelername, k.name AS klassename
  FROM schueler s <u>'''JOIN'''</u> klasse k
  FROM schueler s <u>'''JOIN'''</u> klasse k
  '''ON''' s.klasse_id = k.id
  '''ON''' s.klasse_id = k.id
</code>
</code>


Mit einem JOIN werden nur VOLLSTÄNDIGE Datensätze angezeigt. D.h. im Beispiel fallen Schüler, die keine Klasse haben, und Klassen, die keine Schüler haben, raus.
Mit einem JOIN werden nur VOLLSTÄNDIGE Datensätze angezeigt. D.h. im Beispiel fallen Schüler, die keine Klasse haben, und Klassen, die keine Schüler haben, raus.
Zeile 294: Zeile 405:
Der Join entspricht der [[SQL#SQL-Abfrage über mehrere verknüpfte Tabellen|SQL-Abfrage über mehrere verknüpfte Tabellen]]
Der Join entspricht der [[SQL#SQL-Abfrage über mehrere verknüpfte Tabellen|SQL-Abfrage über mehrere verknüpfte Tabellen]]


===Left-Join===
Statt <code>JOIN</code> kann man auch ausführlicher <code>INNER JOIN</code> schreiben. Dies wird in den Analyseaufgaben der Abiturklausuren häufig verwendet.
 
===LEFT JOIN===
Beim '''Left''' Join werden auch die Zeilen der '''linken''' Tabelle aufgeführt, die keinen Partner in der rechten Tabelle haben. In der rechten Tabelle wird ggf. mit ''NULL'' aufgefüllt.
Beim '''Left''' Join werden auch die Zeilen der '''linken''' Tabelle aufgeführt, die keinen Partner in der rechten Tabelle haben. In der rechten Tabelle wird ggf. mit ''NULL'' aufgefüllt.


Zeile 300: Zeile 413:
Bewerte das Ergebnis inhaltlich: In welchen Zusammenhängen braucht man Left-Join?
Bewerte das Ergebnis inhaltlich: In welchen Zusammenhängen braucht man Left-Join?


<code>
<code>
   SELECT s.name AS schuelername, k.name AS klassename
   SELECT s.name AS schuelername, k.name AS klassename
   FROM schueler s <u>'''LEFT JOIN'''</u> klasse k
   FROM schueler s <u>'''LEFT JOIN'''</u> klasse k
   <u>'''ON'''</u> s.klasse_id = k.id
   <u>'''ON'''</u> s.klasse_id = k.id
</code>
</code>


Beim LEFT JOIN werden ALLE Daten der LINKEN Entitätsmenge angezeigt. Im Beispiel werden also auch die Schüler angezeigt, die keine Klasse haben.
Beim LEFT JOIN werden ALLE Daten der LINKEN Entitätsmenge angezeigt. Im Beispiel werden also auch die Schüler angezeigt, die keine Klasse haben.


===Right-Join===
===RIGHT JOIN===
Beim '''Right''' Join werden auch die Zeilen der '''rechten''' Tabelle aufgeführt, die keinen Partner in der linken Tabelle haben. In der linken Tabelle wird ggf. mit ''NULL'' aufgefüllt.
Beim '''Right''' Join werden auch die Zeilen der '''rechten''' Tabelle aufgeführt, die keinen Partner in der linken Tabelle haben. In der linken Tabelle wird ggf. mit ''NULL'' aufgefüllt.


Formuliere eine Abfrage, die einen Right-Join zwischen den Tabellen klasse und schueler durchführt.  
Formuliere eine Abfrage, die einen Right-Join zwischen den Tabellen klasse und schueler durchführt.  


<code>
<code>
   SELECT s.name AS schuelername, k.name AS klassename
   SELECT s.name AS schuelername, k.name AS klassename
   FROM schueler s <u>'''RIGHT JOIN'''</u> klasse k
   FROM schueler s <u>'''RIGHT JOIN'''</u> klasse k
   <u>'''ON'''</u> s.klasse_id = k.id
   <u>'''ON'''</u> s.klasse_id = k.id
</code>
</code>


Beim RIGHT JOIN werden ALLE Daten der RECHTEN Entitätsmenge angezeigt. Im Beispiel werden also auch die Klassen angezeigt, die keine Schüler haben.
Beim RIGHT JOIN werden ALLE Daten der RECHTEN Entitätsmenge angezeigt. Im Beispiel werden also auch die Klassen angezeigt, die keine Schüler haben.


===LEFT JOIN (bzw. RIGHT JOIN) über 3 oder mehr Tabellen ===
===LEFT JOIN (bzw. RIGHT JOIN) über 3 oder mehr Tabellen ===
Wenn man einen LEFT JOIN über insgesamt 3 Tabellen machen will, die anderen Tabellen in einer Klammer zusammenfassen. <br/>
Wenn man einen LEFT JOIN über insgesamt 3 Tabellen machen will, verwendet man am einfachsten zwei LEFT JOIN. <br/>
Weil man über 3 Tabellen abfragt, braucht man beim ON zwei Vergleiche.


Beispiel: Eine Liste <u>aller</u> Schüler, in der steht, welche AGs sie belegen.
Beispiel: Eine Liste <u>aller</u> Schüler, in der steht, welche AGs sie belegen.
<code>
<code>
   SELECT s.name as schueler, a.name as ag
   SELECT s.name as schueler, a.name as ag
   FROM schueler s LEFT JOIN '''<u>(teilnahme t, ag a)</u>'''
   FROM schueler s
   '''<u>ON s.id = t.schueler_id</u>'''
  LEFT JOIN teilnahme t
   '''<u>AND t.ag_id = a.id</u>'''
   ON s.id = t.schueler_id
</code>
   LEFT JOIN ag a
  ON t.ag_id = a.id
</code>


===IS NULL===
===IS NULL===
Formuliere eine Abfrage, die alle Schüler zurückgibt, die keine Klasse haben.
Formuliere eine Abfrage, die alle Schüler zurückgibt, die keine Klasse haben.


<code>
<code>
   SELECT s.name AS schuelername, k.name AS klassename
   SELECT s.name AS schuelername, k.name AS klassename
   FROM schueler s LEFT JOIN klasse k
   FROM schueler s LEFT JOIN klasse k
   ON s.klasse_id = k.id
   ON s.klasse_id = k.id
   WHERE k.id <u>'''IS NULL'''</u>
   WHERE k.id <u>'''IS NULL'''</u>
</code>
</code>


Beim LEFT JOIN werden ALLE Daten der LINKEN Entitätsmenge angezeigt, also auch die Schüler, die keine Klasse haben.
Beim LEFT JOIN werden ALLE Daten der LINKEN Entitätsmenge angezeigt, also auch die Schüler, die keine Klasse haben.
Zeile 353: Zeile 467:
* ''Außerdem hat man den <u>gesuchten</u> Raum r2, der mehr Plätze hat als Raum r1.''
* ''Außerdem hat man den <u>gesuchten</u> Raum r2, der mehr Plätze hat als Raum r1.''


<code>
<code>
   SELECT r2.nummer
   SELECT r2.nummer
   FROM <u>'''raum r1'''</u>, <u>'''raum r2'''</u>
   FROM <u>'''raum r1'''</u>, <u>'''raum r2'''</u>
   WHERE r1.nummer = "R203"
   WHERE r1.nummer = "R203"
   AND r2.plaetze > r1.plaetze
   AND r2.plaetze > r1.plaetze
</code>
</code>


==Geschachtelte SQL-Ausdrücke==
=Abfragen über selbstdefinierte Tabellen=
Mit ''Geschachtelte SQL-Ausdrücke'' (so heißt das im Zentralabitur...) ist folgendes gemeint:


'''Man kann die Tabellen, die man bei FROM angibt, durch eigene SELECT-Anfrage selber definieren.'''
'''Man kann die Tabellen, die man bei FROM angibt, durch eigene SELECT-Anfrage selber definieren.'''
Zeile 371: Zeile 484:
''Eine Liste der Klassen, in der steht, wieviel Prozent Sportunterricht sie haben.''
''Eine Liste der Klassen, in der steht, wieviel Prozent Sportunterricht sie haben.''


<code>
<code>
  SELECT gesamtstunden.klasse AS klasse, sportstunden.stunden/gesamtstunden.stunden * 100 AS prozent
  SELECT gesamtstunden.klasse AS klasse, sportstunden.stunden/gesamtstunden.stunden * 100 AS prozent
  FROM
  FROM
Zeile 387: Zeile 500:
   ) <u>AS sportstunden</u>'''
   ) <u>AS sportstunden</u>'''
  <u>'''ON sportstunden.klasse = gesamtstunden.klasse'''</u>
  <u>'''ON sportstunden.klasse = gesamtstunden.klasse'''</u>
</code>
</code>




Zeile 410: Zeile 523:
'''SQL-Statement:'''
'''SQL-Statement:'''


<code>
<code>
  SELECT gesamtstunden.klasse AS klasse, sportstunden.stunden/gesamtstunden.stunden * 100 AS prozent
  SELECT gesamtstunden.klasse AS klasse, sportstunden.stunden/gesamtstunden.stunden * 100 AS prozent
  FROM
  FROM
Zeile 426: Zeile 539:
   ) <u>AS sportstunden</u>'''
   ) <u>AS sportstunden</u>'''
  <u>'''ON sportstunden.klasse = gesamtstunden.klasse'''</u>
  <u>'''ON sportstunden.klasse = gesamtstunden.klasse'''</u>
</code>
</code>


'''Aufgaben:'''
'''Aufgaben:'''
Zeile 468: Zeile 581:
''Lösen lässt sich das mit <code>HAVING</code>:
''Lösen lässt sich das mit <code>HAVING</code>:


<code>
<code>
  SELECT u.fach, SUM(u.stunden) AS stunden
  SELECT u.fach, SUM(u.stunden) AS stunden
  FROM unterricht u
  FROM unterricht u
Zeile 474: Zeile 587:
  <u>HAVING stunden > 7</u>
  <u>HAVING stunden > 7</u>
  ORDER BY stunden DESC
  ORDER BY stunden DESC
</code>
</code>


=Mit Java auf eine SQL-Datenbank zugreifen=
=Mit Java auf eine SQL-Datenbank zugreifen=
Das wird hier erklärt: [[Java-SQL]]
Das wird hier erklärt: [[Java-SQL]]
=ChatGPT nutzen um SQL zu schreiben=
''Last not least will ich nicht verheimlichen, dass ChatGPT ein großartiges Hilfsmittel ist, um SQL zu schreiben!''
==Erklärvideo==
[https://youtu.be/GohWF9SnhWM ChatGPT nutzen, um SQL zu schreiben]
==Best practices==
''Bei unseren "Gehversuchen" mit der SQL-Programmierung mit ChatGPT haben wir festgestellt:''
* ''ChatGPT kann extrem cool SQL programmieren!!<br/>Es verfügt über einen SQL-Wortschatz, von dem selbst die Informatik-Lehrer noch nie gehört haben!''
* '''''ABER:''' ChatGPT ist ziemlich begriffsstutzig! Man muss sehr genau erklären, was man will.''
'''Empfehlungen:'''
# Zu Anfang: '''Kontext herstellen!''' (-> Datenbankschema!)
# '''Genau''' mitteilen, was man will, mit '''Fachbegriffen'''.
# '''Testen''':<br/>Natürlich macht ChatGPT auch beim Programmieren Fehler. <br/>Der große Vorteil ist aber, dass man das - mit gut gewählten Beispielen - direkt überprüfen kann!<br/>Und wenn der SQL-Code einen Fehler hat, dann hilft nur...
# '''„Nachkarten“:'''<br/>''Das ist sehr wichtig! Denn mit etwas Hartnäckigkeit kann man ChatGPT dazu bringen, das Richtige zu tun!''<br/>Für das "Nachkarten" empfiehlt sich:
## '''Fehlermeldungen''' von SQL an ChatGPT weiter geben.
## '''Fachbegriffe''' verwenden
## '''Beziehungen klären''' <br/>z.B.: "Die Tabelle schueler ist über die Tabelle klasse mit der Tabelle unterricht verknüpft."<br/>z.B.: "Fach ist ein Attribut der Tabelle unterricht."
## '''Das Datenbankmodell nochmal mit Copy&Paste eintragen.''' <br/>Informationen "verblassen" im Laufe der Zeit.
## Gegebenenfalls '''SQL-Schlüsselwörter vorgeben''': <br/>z.B. "Verwende NOT IN"
# Wenn alles nichts hilft:
## eine '''neue Unterhaltung anfangen''', d.h. einfach nochmal von vorne.<br/>''Die Antworten von ChatGPT beruhen auf Wahrscheinlichkeiten, d.h. es kann gut sein, dass man bei genau der selben Anfrage eine andere Lösung bekommt.''
## '''Auf Englisch fragen.'''<br/>''ChatGPT wurde mit wesentlich mehr englischen Daten trainiert, d.h. die Erfolgschancen sind auf Englisch größer.''
==Vorteile==
* ChatGPT steht ein sehr breites SQL-Vokabular zur Verfügung!<br/>Da sind Schlüsselwörter dabei, die man selber höchstens zum "passiven" Wortschatz zählen würde.
* ChatGPT kann (wenn alles gut geht) SQL-Statements von ziemlicher Eleganz entwickeln.
* Wenn man das Datenbankschema mitteilt und die Tabellen und Attribute verständlich benannt hat, dann kann sich ChatGPT auf einen beliebigen Kontext einstellen.
* Außerdem geht es natürlich extrem schnell.
==Datenbankschema für den Test==
''Das Datenbankschema muss man direkt am Anfang mitteilen!''
<code>
schueler(<u>id</u>,↑klasse_id,name,vorname)
klasse(<u>id</u>,↑klassenlehrer_id,name)
lehrer(<u>id</u>,name,vorname)
unterricht(<u>id</u>,↑klasse_id,↑lehrer_id,↑raum_id,fach,stunden)
raum(<u>id</u>,nummer,etage,plaetze)
ag(<u>id</u>,↑lehrer_id,name)
teilnahme(<u>↑schueler_id</u>,<u>↑ag_id</u>)
</code>
Testen kann man wie üblich hier:
[https://sibiwiki.de/sql/ sibiwiki.de/sql]
(Zugangsdaten beim Informatik-Lehrer am SIBI.)
Wer nicht am SIBI ist, kann sich einen Dump der Datenbank Schule hier herunterladen:<br/>[[Datei:Beispieldatenbank-schule.zip]]

Aktuelle Version vom 3. Mai 2024, 09:48 Uhr


Diese Seite ist für die Oberstufe (Jahrgang 11-13). Für SQL in der 10. Klasse hier klicken: SQL-09

Hier finden sich Erläuterungen zu allen SQL-Techniken, die für das Zentralabitur wichtig sind.

Zu diesen Techniken gibt es auch Aufgaben, die man zur Übung selber bearbeiten kann.

Erklärvideos

Fachbegriffe

Vereinigung, Differenz, kartesisches Produkt, Abgleich der Tabellen über..., geschachtelte Abfrage, äußere Abfrage, innere Abfrage

Außerdem die SQL-Sprachelemente (s.u.)

SQL-Sprachelemente im Zentralabitur

Informationen des Zentralabiturs (PDF): Datei:Datenbanken-Abi-2012.pdf

Die folgenden SQL-Befehle muss man im Zentralabitur beherrschen:

  • SELECT, SELECT DISTINCT, FROM, WHERE, GROUP BY, ORDER BY, ASC, DESC, JOIN, LEFT JOIN, RIGHT JOIN, ON, UNION, AS
  • Vergleichsoperatoren: =, !=, >, <, >=, <=, LIKE, BETWEEN, IN, NOT IN, IS NULL
  • Logische Operatoren: AND, OR, NOT
  • Funktionen: COUNT, SUM, MAX, MIN, AVG
  • sonstiges: Die Joker % und *

Was man noch können muss:

Beispieldatenbank Schule

  • An der Beispieldatenbank Schule können alle möglichen Datenbankabfragen durchprobiert werden.
  • Die Beispieldatenbank ist mit Absicht sehr schmal gehalten, damit man die Ergebnisse von SQL-Abfragen noch überprüfen kann.
  • Testen kann man SQL-Abfragen auf der Datenbank Schule: hier klicken.
    Die Zugangsdaten gibt's bei Herrn Kaibel

Beispieldatenbank-schule.jpg

relationales Datenmodell der Datenbank Schule


 schueler(id,↑klasse_id,name,vorname)
 klasse(id,↑klassenlehrer_id,name)
 lehrer(id,name,vorname)
 unterricht(id,↑klasse_id,↑lehrer_id,↑raum_id,fach,stunden)
 raum(id,nummer,etage,plaetze)
 ag(id,↑lehrer_id,name)
 teilnahme(↑schueler_id,↑ag_id)

Aufbau von SQL-Abfragen

Für den Aufbau von SQL-Abfragen gilt folgende Reihenfolge:


SELECT ...
FROM ...
WHERE ...
GROUP BY ...
ORDER BY ...
LIMIT ...


SELECT und FROM müssen auf jeden Fall dabei sein, die anderen Teile sind optional.

Abfragen über eine Tabelle

Begriffsklärung: Relation: (hier:) Tabelle

Bei Datenbanken versteht man unter einer Relationenalgebra eine formale Sprache, mit der sich Abfragen über eine oder mehrere Tabellen (=Relationen) formulieren lassen. Sie erlaubt es, Tabellen miteinander zu verknüpfen, zu reduzieren oder komplexere Informationen daraus herzuleiten.

Projektion (SELECT)

Unter Projektion versteht man die Reduktion der Informationen in dem Ergebnis auf ein oder mehrere Attribute (=Spalten).
In unserem Beispiel werden zusätzlich doppelte Zeilen werden entfernt. Dafür braucht man das Schlüsselwort DISTINCT, das hat aber mit der eigentlichen Projektion auf einzelne Spalten nichts zu tun.

Beispiel:

Formuliere für die Tabelle raum eine Projektionsabfrage, die die Etagen mit Projektion wiedergibt.


 SELECT DISTINCT r.etage
 FROM raum r

Beachte: Entscheidend für die Projektion ist es, dass man bei SELECT einzelne Attribute angibt.

Selektion (WHERE)

Selektion bedeutet, dass Datensätze (=Zeilen) ausgewählt werden, die eine bestimmte Bedingung erfüllen.

Formuliere eine Selektionsabfrage, die alle Nummern der Räume mit mindestens 30 Plätzen zurückgibt.


 SELECT r.nummer
 FROM raum r
 WHERE r.plaetze >= 30

Beachte: Das entscheidende Schlüsselwort für die Selektion ist WHERE, denn hier wird die Bedingung festgelegt.

Formuliere eine Selektionsabfrage, die alle Informationen zu Räumen mit mindestens 30 Plätzen zurückgibt.


 SELECT *
 FROM raum r
 WHERE r.plaetze >= 30

Beachte: Mit dem Joker * im SELECT-Teil der Abfrage werden alle Attribute ausgewählt.


Erläuterung einzelner SQL-Befehle

Im folgenden werden einzelne SQL-Befehle jeweils am Beispiel erläutert.

Aggregat-Funktionen (COUNT, MAX, MIN, SUM, AVG)

Mit den sog. Aggregat-Funktionen kann man Attributwerte aus mehreren Zeilen zusammenfassen.

Kurze Erklärung

  • COUNT: zählt
  • SUM: addiert
  • AVG: berechnet den Durchschnitt (average)
  • MAX: bestimmt das Maximum
  • MIN: bestimmt das Minimum

Beispiele

Beispiel 1:

Mit der folgenden Abfrage kann man ermitteln, wie viele Schüler es gibt.
Man zählt die Zeilen; deswegen braucht man COUNT (und nicht SUM).


  SELECT COUNT(*)
  FROM schueler s

Hinweis:
COUNT(*) übersetzt sich am einfachsten als "Zähle die Zeilen".
Wenn man stattdessen COUNT(s.name) nehmen würde, dann würden nur die Zeilen gezählt, wo beim Namen ein Eintrag vorhanden ist. (Das sind hier aber alle).

Beispiel 2:

Mit der folgenden Abfrage kann man ermitteln, wie viele Stunden Sport unterrichtet werden.
Hier muss man die Attributwerte stunden addieren, deswegen braucht man SUM.


  SELECT SUM(u.stunden)
  FROM unterricht u
  WHERE u.fach = 'Sport'

Beispiel 3:

Mit der folgenden Abfrage ermittelt man...

  • die maximale Zahl der Plätze in einem Raum,
  • die minimale Zahl der Plätze in einem Raum,
  • die durchschnittliche Zahl der Plätze in allen Räumen.

  SELECT MAX(r.plaetze), MIN(r.plaetze), AVG(r.plaetze)
  FROM raum r

Wann braucht man COUNT(...) und wann braucht man SUM(...) ?!

COUNT und SUM werden leicht verwechselt!

Als Faustregel kann man sagen:

  • COUNT, wenn gezählt wird,
  • SUM, wenn addiert wird.

Das ist aus der Aufgabenstellung nicht immer offensichtlich!

Beispiel 1:
Man hätte gerne die Anzahl der Plätze in allen Räumen.

Beispiel 2:
Man hätte gerne die Anzahl der Räume.

Lösung:

  • in Beispiel 1 wird die Anzahl der Plätze (im Attribut plaetze) addiert, deswegen SUM(r.plaetze)
  • in Beispiel 2 wird die Anzahl der Räume gezählt, deswegen COUNT(r.id) oder COUNT(*)

COUNT(DISTINCT ...)

Beispiel:
Wie viele Fächer gibt es?


 SELECT COUNT(DISTINCT u.fach)
 FROM unterrricht u

Durch DISTINCT wird jedes Fach nur einmal berücksichtigt!

LIKE und der %-Joker

Der %-Joker kann in einer Abfrage beliebige Zeichen ersetzen.

Den %-Joker darf man nicht mit dem exakten Vergleich (=) einsetzen; stattdessen nimmt man für den Vergleich das Schlüsselwort LIKE.

Beispiel:

Eine Liste aller Schüler, die ein Doppel-n ('nn') im Vornamen haben.


SELECT s.vorname AS vorname, s.name AS name
FROM schueler s
WHERE s.vorname LIKE '%nn%'

Beachte: Vor und hinter dem nn steht ein %-Joker, d.h. hier können beliebige Buchstaben stehen. "Beliebig" kann auch heißen: Kein Buchstabe! D.h. "Johann" wäre auch ein akzeptierter Vorname.

GROUP BY

Mit GROUP BY kann man Zeilen, die einen gemeinsamen Attributwert aufweisen, zusammenfassen.

Beispiel:

Mit der folgenden Abfrage erhält man eine Liste der Etagen, und für jede Etage wird notiert, wie viele Plätze in ihr insgesamt enthalten sind.


  SELECT r.etage, SUM(plaetze)
  FROM raum r
  GROUP BY r.etage

Beachte: Durch GROUP BY r.etage werden alle Zeilen, die bei r.etage den Wert 'unten' haben, in einer Zeile zusammengefasst. Genauso alle Zeilen, die bei r.etage den Wert 'Mitte' (bzw. 'oben') haben.

Dadurch, dass man so zusammenfasst, kann jetzt für jede Etage die Summe der Plätze berechnet werden.

Erklärvideo zu GROUP BY (06:15min)

Umbenennung (AS)

Attribute können umbenannt werden.

Beispiel 1:

Formuliere eine Abfrage über die Attribute klasse_id, lehrer_id, raum_id, fach und stunden der Tabelle unterricht. Dabei soll klasse_id in klassen-nr, lehrer_id in lehrer_nr und raum_id in raum-nr umbenannt werden.


SELECT u.klasse_id AS klassennr,u.lehrer_id AS Lehrernr,u.raum_id AS Raumnr,u.fach,u.stunden AS stunden
FROM unterricht u

Beispiel 2:

Formuliere eine Abfrage, die eine Liste der Etagen zurückgibt. Für jede Etage soll angegeben werden, wie viele Plätze es in der Etage gibt. Die Liste soll nach der Anzahl der Plätze sortiert sein.


  SELECT r.etage, SUM(plaetze) AS PlaetzeInEtage
  FROM raum r
  GROUP BY r.etage
  ORDER BY PlaetzeInEtage

Beachte: Für SUM(plaetze) wird ein Alias (PlaetzeInEtage) vergeben. Mithilfe des Alias kann man am Ende nach der Anzahl der Plätze sortieren!

Mehrere Bedingungen: AND, OR, BETWEEN

In SQL-Abfragen kann man mehrere Bedingungen kombinieren. Dafür gibt es die Schlüsselwörter AND, OR und BETWEEN

AND / OR

Mit AND / OR kann man zwei Bedingungen verknüpfen.

Beispiel:


  SELECT r.nummer, r.plaetze
  FROM raum r
  WHERE r.etage = 'unten' OR r.etage = 'Mitte'

BETWEEN

Mit BETWEEN ... AND kann man Intervalle angeben.

Beispiel:


  SELECT r.nummer, r.plaetze
  FROM raum r
  WHERE r.plaetze BETWEEN 30 AND 50

LIMIT

Mit LIMIT kann man angeben, wie viele Zeilen ausgegeben werden.

Beispiel: Die folgende SQL-Anweisung gibt den Raum (Nummer, Platzanzahl) mit den meisten Plätzen aus.

Es wird eine Liste von allen Räumen erstellt, aber nur die erste Zeile der Liste (d.h. der Raum mit den meisten Plätzen) wird ausgegeben.


  SELECT r.nummer, r.plaetze
  FROM raum r
  ORDER BY r.plaetze DESC
  LIMIT 1

Abfragen über mehrere Tabellen

Vereinigung (UNION)

Vereinigung bedeutet, dass die Ergebnis-Datensätze von zwei Abfragen zu einem Ergebnis zusammengefügt werden.
Voraussetzung dafür ist, dass beide Abfragen gleich viele Attribute abfragen.

Formuliere eine Abfrage, die Name und Vorname von allen Lehrern UND Schülern wiedergibt.


SELECT l.name, l.vorname
FROM lehrer l
UNION
SELECT s.name, s.vorname
FROM schueler s

Differenz (NOT IN)

Differenz bedeutet, dass die Ergebnis-Datensätze einer Abfrage a1 von den Ergebnis-Datensätzen einer Abfrage a0 abgezogen werden - es bleibt dann ein Rest übrig.

Formuliere eine Abfrage, die alle Klassen ausgibt, die KEIN Mathematik haben.

Idee: Die Klassen, die Mathematik haben, von allen Klassen abziehen.


SELECT k.name
FROM klasse k
WHERE k.id NOT IN
(
   SELECT u.klasse_id 
   FROM unterricht u 
   WHERE u.fach='Mathe'
)

IN

Das logische Gegenteil zu NOT IN ist entsprechend IN. Dieses Schlüsselwort kann man u.a. verwenden, wenn man in einer Abfrage zwei Datensätze einer Entitätsmenge hat. Beispiel: Welche Schüler sind in der selben Klasse wie Anne Ebert?

Idee: In der äußeren Abfrage werden die Schüler gesucht; mit IN kann man dann die klasse_id von Anne Ebert angeben.


SELECT s.name, s.vorname
FROM schueler s
WHERE s.klasse_id IN
(
   SELECT s2.klasse_id
   FROM schueler s2
   WHERE s2.name = 'Ebert'
)

Kartesisches Produkt

In der Mathematik bezeichnet man als kartesisches Produkt (nach René Descartes) zweier Mengen A und B die Menge aller geordneten Paare (a,b), wobei a aus A und b aus B ist. (Kombination: „Jedes mit jedem“.) Geschrieben wird es als A x B gelesen als A kreuz B.

Kurz: Jeder mit jedem.

Formuliere eine Abfrage für das kartesische Produkt von den Tabellen klasse und schueler.


SELECT *
FROM klasse k, schueler s 

Diese Abfrage liefert als Ergebnis "Jeder mit jedem". Inhaltlich ist das nicht sinnvoll.

Damit das kartesische Produkt sinnvoll wird, braucht man einen Abgleich zwischen den Tabellen; vgl. SQL-Abfrage über mehrere verknüpfte Tabellen


Abgleich zwischen mehreren Tabellen

Formuliere eine Abfrage, die angibt, in welchen Räumen die Klasse 8a unterrichtet wird. Die Abfrage läuft über die Tabellen klasse, unterricht und raum.


SELECT r.nummer
FROM klasse k, unterricht u, raum r  
WHERE r.id=u.raum_id
AND u.klasse_id=k.id
AND k.name = '8a'

Wichtig: bei Abfragen über mehrere Tabellen braucht man einen Abgleich weniger als man Tabellen abfragt.

Im Beispiel werden drei Tabellen abgefragt; deswegen braucht man zwei Abgleiche.

JOIN / INNER JOIN

Join ist die Bildung eines kartesischen Produktes gefolgt von einer Selektion, die den (richtigen) Abgleich zwischen den beiden Tabellen leistet.

Beispiel: Eine Liste der Schüler, in der für jeden Schüler vermerkt wird, in welcher Klasse er ist.


SELECT s.name AS schuelername, k.name AS klassename
FROM schueler s JOIN klasse k
ON s.klasse_id = k.id

Mit einem JOIN werden nur VOLLSTÄNDIGE Datensätze angezeigt. D.h. im Beispiel fallen Schüler, die keine Klasse haben, und Klassen, die keine Schüler haben, raus.

Der Join entspricht der SQL-Abfrage über mehrere verknüpfte Tabellen

Statt JOIN kann man auch ausführlicher INNER JOIN schreiben. Dies wird in den Analyseaufgaben der Abiturklausuren häufig verwendet.

LEFT JOIN

Beim Left Join werden auch die Zeilen der linken Tabelle aufgeführt, die keinen Partner in der rechten Tabelle haben. In der rechten Tabelle wird ggf. mit NULL aufgefüllt.

Formuliere eine Abfrage, die einen Left-Join zwischen den Tabellen klasse und schueler durchführt. Bewerte das Ergebnis inhaltlich: In welchen Zusammenhängen braucht man Left-Join?


 SELECT s.name AS schuelername, k.name AS klassename
 FROM schueler s LEFT JOIN klasse k
 ON s.klasse_id = k.id

Beim LEFT JOIN werden ALLE Daten der LINKEN Entitätsmenge angezeigt. Im Beispiel werden also auch die Schüler angezeigt, die keine Klasse haben.

RIGHT JOIN

Beim Right Join werden auch die Zeilen der rechten Tabelle aufgeführt, die keinen Partner in der linken Tabelle haben. In der linken Tabelle wird ggf. mit NULL aufgefüllt.

Formuliere eine Abfrage, die einen Right-Join zwischen den Tabellen klasse und schueler durchführt.


 SELECT s.name AS schuelername, k.name AS klassename
 FROM schueler s RIGHT JOIN klasse k
 ON s.klasse_id = k.id

Beim RIGHT JOIN werden ALLE Daten der RECHTEN Entitätsmenge angezeigt. Im Beispiel werden also auch die Klassen angezeigt, die keine Schüler haben.

LEFT JOIN (bzw. RIGHT JOIN) über 3 oder mehr Tabellen

Wenn man einen LEFT JOIN über insgesamt 3 Tabellen machen will, verwendet man am einfachsten zwei LEFT JOIN.

Beispiel: Eine Liste aller Schüler, in der steht, welche AGs sie belegen.


 SELECT s.name as schueler, a.name as ag
 FROM schueler s
 LEFT JOIN teilnahme t
 ON s.id = t.schueler_id
 LEFT JOIN ag a
 ON t.ag_id = a.id

IS NULL

Formuliere eine Abfrage, die alle Schüler zurückgibt, die keine Klasse haben.


 SELECT s.name AS schuelername, k.name AS klassename
 FROM schueler s LEFT JOIN klasse k
 ON s.klasse_id = k.id
 WHERE k.id IS NULL

Beim LEFT JOIN werden ALLE Daten der LINKEN Entitätsmenge angezeigt, also auch die Schüler, die keine Klasse haben. Diese Schüler werden durch k.id IS NULL ausgewählt.

Abfragen, bei denen man mehrere Variablen einer Entitätsmenge braucht

Formuliere eine Abfrage, die alle Räume wiedergibt, die mehr Plätze haben als Raum R203.

Idee:

  • Man hat den bekannten Raum r1 mit der Nummer "R203".
  • Außerdem hat man den gesuchten Raum r2, der mehr Plätze hat als Raum r1.

 SELECT r2.nummer
 FROM raum r1, raum r2
 WHERE r1.nummer = "R203"
 AND r2.plaetze > r1.plaetze

Abfragen über selbstdefinierte Tabellen

Man kann die Tabellen, die man bei FROM angibt, durch eigene SELECT-Anfrage selber definieren.

Die selbstdefinierten Tabellen müssen mit einem Alias benannt und durch ein Komma voneinander getrennt werden.
Dann können sie wie "normale" Tabellen genutzt werden.


Beispiel: Eine Liste der Klassen, in der steht, wieviel Prozent Sportunterricht sie haben.


SELECT gesamtstunden.klasse AS klasse, sportstunden.stunden/gesamtstunden.stunden * 100 AS prozent
FROM
  (SELECT k.name AS klasse, SUM(u.stunden) AS stunden
   FROM klasse k LEFT JOIN unterricht u
   ON k.id = u.klasse_id
   GROUP BY k.name
  ) AS gesamtstunden
  LEFT JOIN
  (SELECT k.name AS klasse, SUM(u.stunden) AS stunden
   FROM klasse k, unterricht u
   WHERE k.id = u.klasse_id
   AND u.fach = 'Sport'
   GROUP BY k.name
  ) AS sportstunden
ON sportstunden.klasse = gesamtstunden.klasse


Erläuterung:

  • In FROM werden zwei Tabellen definiert: Die Tabelle gesamtstunden und die Tabelle sportstunden.
  • Diese Tabellen werden mit einem LEFT JOIN ... ON verknüpft.
  • Außerdem erhalten sie mithilfe von AS Aliasnamen: AS gesamtstunden (bzw. AS sportstunden).
  • Mithilfe dieser Aliasnamen kann die äußere Abfrage auf die beiden Tabellen zugreifen.


WICHTIG: Man muss jeder inneren SELECT-Abfrage mithilfe von AS einen Namen geben - nur dann ist sie in der äußeren SELECT-Abfrage ansprechbar!

SQL analysieren

In Klausuren (auch im Abi) muss SQL analysiert werden.

Meistens muss man dabei ein SQL-Statement auf vorliegende Tabellen anwenden; es kann aber auch vorkommen, dass man ein SQL-Statement erläutern muss.

Dafür wird hier ein Beispiel gegeben.

SQL-Statement:


SELECT gesamtstunden.klasse AS klasse, sportstunden.stunden/gesamtstunden.stunden * 100 AS prozent
FROM
  (SELECT k.name AS klasse, SUM(u.stunden) AS stunden
   FROM klasse k LEFT JOIN unterricht u
   ON k.id = u.klasse_id
   GROUP BY k.name
  ) AS gesamtstunden
  LEFT JOIN
  (SELECT k.name AS klasse, SUM(u.stunden) AS stunden
   FROM klasse k, unterricht u
   WHERE k.id = u.klasse_id
   AND u.fach = 'Sport'
   GROUP BY k.name
  ) AS sportstunden
ON sportstunden.klasse = gesamtstunden.klasse

Aufgaben:

  1. Erläutern Sie die Funktionsweise dieser SQL-Abfrage.
  2. Erläutern Sie, welchem Zweck diese SQL-Abfrage dient.

Folgendermaßen könnte eine Bearbeitung aussehen; wichtige Schlüsselwörter und Passagen sind unterstrichen.

Funktionsweise:

Die SQL-Abfrage besteht aus einer äußeren Abfrage, die auf zwei selbstdefinierte Tabellen zugreift. Der ersten selbstdefinierten Tabelle wurde mit "AS gesamtstunden" der Name gesamtstunden gegeben, der zweiten selbstdefinierten Tabelle der Name sportstunden.

In der Tabelle gesamtstunden wird mithilfe von GROUP BY eine Liste der Klassen-Namen erstellt, in der für jede Klasse die Gesamtzahl der unterrichteten Stunden angegeben wird. In der Tabelle sportstunden geschieht dasselbe für die Sportstunden.

In der äußeren Abfrage werden diese beiden selbstdefinierten Tabellen mithilfe eines LEFT JOIN über die Spalten sportstunden.klasse = gesamtstunden.klasse verknüpft.

So werden in der Ergebnistabellen alle Zeilen der Tabelle gesamtstunden berücksichtigt, aber nicht unbedingt alle Zeilen der Tabelle sportstunden.

Die äußere Abfrage übernimmt im SELECT die Spalte klasse aus der Tabelle gesamtstunden und berechnet dann aus sportstunden.stunden und gesamtstunden.stunden, wie viel Prozent Sport erteilt wird.

Zweck:

Die SQL-Abfrage gibt eine Liste aller Klassen zurück, die Unterricht haben. Für jede Klasse wird angegeben, wie viel Prozent ihrer Unterrichtsstunden Sportstunden sind. Die 8D hat zwar keinen Unterricht, durch die (zweifache) Verwendung von LEFT JOIN wird sie dennoch angezeigt, in der rechten Spalte steht dann NULL statt einer Prozentzahl.

HAVING

Nicht abiturrelevant, aber sehr nützlich!

Manchmal hat man eine Zahl gerade erst mit einer Aggregatfunktion (z.B. SUM) ermittelt und möchte für diese Zahl direkt eine Bedingung festlegen.

Das kann man mit WHERE nicht tun, denn WHERE funktioniert nur für "fest" definierte Zahlen!

Für diesen Fall wurde das Schlüsselwort HAVING entwickelt.


Beispiel: Eine Liste der Fächer, in der die Gesamtzahl der Stunden vermerkt ist, die sie unterrichtet werden. Es sollen nur Fächer aufgeführt werden, die mehr als 7 Stunden unterrichtet werden.

Beachte: Die Gesamtzahl der Stunden muss mit einer Aggregatfunktion (SUM) ermittelt werden. Deswegen kann man für die Gesamtzahl keine WHERE-Bedingung formulieren - das würde zu einem Syntax-Fehler führen.

Lösen lässt sich das mit HAVING:


SELECT u.fach, SUM(u.stunden) AS stunden
FROM unterricht u
GROUP BY u.fach
HAVING stunden > 7
ORDER BY stunden DESC

Mit Java auf eine SQL-Datenbank zugreifen

Das wird hier erklärt: Java-SQL

ChatGPT nutzen um SQL zu schreiben

Last not least will ich nicht verheimlichen, dass ChatGPT ein großartiges Hilfsmittel ist, um SQL zu schreiben!

Erklärvideo

ChatGPT nutzen, um SQL zu schreiben

Best practices

Bei unseren "Gehversuchen" mit der SQL-Programmierung mit ChatGPT haben wir festgestellt:

  • ChatGPT kann extrem cool SQL programmieren!!
    Es verfügt über einen SQL-Wortschatz, von dem selbst die Informatik-Lehrer noch nie gehört haben!
  • ABER: ChatGPT ist ziemlich begriffsstutzig! Man muss sehr genau erklären, was man will.

Empfehlungen:

  1. Zu Anfang: Kontext herstellen! (-> Datenbankschema!)
  2. Genau mitteilen, was man will, mit Fachbegriffen.
  3. Testen:
    Natürlich macht ChatGPT auch beim Programmieren Fehler.
    Der große Vorteil ist aber, dass man das - mit gut gewählten Beispielen - direkt überprüfen kann!
    Und wenn der SQL-Code einen Fehler hat, dann hilft nur...
  4. „Nachkarten“:
    Das ist sehr wichtig! Denn mit etwas Hartnäckigkeit kann man ChatGPT dazu bringen, das Richtige zu tun!
    Für das "Nachkarten" empfiehlt sich:
    1. Fehlermeldungen von SQL an ChatGPT weiter geben.
    2. Fachbegriffe verwenden
    3. Beziehungen klären
      z.B.: "Die Tabelle schueler ist über die Tabelle klasse mit der Tabelle unterricht verknüpft."
      z.B.: "Fach ist ein Attribut der Tabelle unterricht."
    4. Das Datenbankmodell nochmal mit Copy&Paste eintragen.
      Informationen "verblassen" im Laufe der Zeit.
    5. Gegebenenfalls SQL-Schlüsselwörter vorgeben:
      z.B. "Verwende NOT IN"
  5. Wenn alles nichts hilft:
    1. eine neue Unterhaltung anfangen, d.h. einfach nochmal von vorne.
      Die Antworten von ChatGPT beruhen auf Wahrscheinlichkeiten, d.h. es kann gut sein, dass man bei genau der selben Anfrage eine andere Lösung bekommt.
    2. Auf Englisch fragen.
      ChatGPT wurde mit wesentlich mehr englischen Daten trainiert, d.h. die Erfolgschancen sind auf Englisch größer.

Vorteile

  • ChatGPT steht ein sehr breites SQL-Vokabular zur Verfügung!
    Da sind Schlüsselwörter dabei, die man selber höchstens zum "passiven" Wortschatz zählen würde.
  • ChatGPT kann (wenn alles gut geht) SQL-Statements von ziemlicher Eleganz entwickeln.
  • Wenn man das Datenbankschema mitteilt und die Tabellen und Attribute verständlich benannt hat, dann kann sich ChatGPT auf einen beliebigen Kontext einstellen.
  • Außerdem geht es natürlich extrem schnell.

Datenbankschema für den Test

Das Datenbankschema muss man direkt am Anfang mitteilen!

schueler(id,↑klasse_id,name,vorname)
klasse(id,↑klassenlehrer_id,name)
lehrer(id,name,vorname)
unterricht(id,↑klasse_id,↑lehrer_id,↑raum_id,fach,stunden)
raum(id,nummer,etage,plaetze)
ag(id,↑lehrer_id,name)
teilnahme(↑schueler_id,↑ag_id)

Testen kann man wie üblich hier:

sibiwiki.de/sql

(Zugangsdaten beim Informatik-Lehrer am SIBI.)

Wer nicht am SIBI ist, kann sich einen Dump der Datenbank Schule hier herunterladen:
Datei:Beispieldatenbank-schule.zip