Begleitmaterial zum Unterricht M104 «Abfragen/SQL»

Tabellen mit SQL-Abfragen

1. Einf├╝hrung Abfragen und SQL-Basics

Lernziele:

  • Sie können in OpenOffice Base Abfragen erstellen
  • Sie verstehen einfache SQL-Statements und können die Abfragen im SQL-Editor von OpenOfficeBase bearbeiten

Hinweis: SQL gehört eigentlich nicht in dieses Modul, sondern in das ÜK-Modul 105 - Datenbanken mit SQL bearbeiten oder das M104-Folgemodul M141 - Datenbanksystem in Betrieb nehmen. Um aber in M104 eine Datenbank testen zu können, sind Abfragen nötig, die wiederum auf SQL-Statements basieren. Auch bei gewissen Formularen bzw. Unterformularen (z.B. bei n-m Relationen) sind rudimentäre SQL-Kenntnisse von Vorteil.

Erarbeiten sie die Theorie zu folgendem Thema:

  • Einfache SQL-Abfragen mit dem «select»-Befehl

und erstellen sie eine kurze Zusammenfassung. (Sie finden z.B. auf dieser Webseite die entsprechenden Fachbeiträge)

2. Abfragen analysieren (1)

Hinweis zu den folgenden Aufgaben: Dokumentieren sie ihre Arbeiten und stellen sie diese mit den entsprechenden Datenbanken dem Dozenten elektronisch zur Verfügung.

Die folgende Aufgabe bezieht sich auf die OpenOfficeBase-Datenbanken Bsp1.odb.
Hier Bsp1.odb herunterladen

2.1 Aufgabe: Bsp1.odb - Abfrage1 analysieren

Starten sie die Bsp1.odb-OpenOfficeBase-Datenbank. Die beiden Tabellen T_Autotyp und T_Person wurden für sie bereits erstellt. Überprüfen sie diese. Nun wählen sie unter«Datenbank» die «Abfragen» aus (Linkes Fenster in OOBase). Es werden hier sieben Abfragen angezeigt. Öffnen sie nun die erste Abfrage (Abfrage1). Es sind drei Varianten zum Öffnen der Abfrage von Interesse: (Rechte Maustaste klicken!)

  • Bearbeiten (Die Abfrage wird in der Entwurfsansicht geöffnet)
  • In SQL-Ansicht bearbeiten (Der SQL-Befehl kann direkt eingesehen und allenfalls bearbeitet werden)
  • Öffnen (Die Abfrage wird ausgeführt und das Resultat in einem separaten Fesnter angezeigt)

Was sollen sie nun tun?

  1. Öffnen sie die Abfrage und begutachten sie das Abfrageresultat. Welche Kombinationen werden angezeigt?
  2. Wählen sie die Entwurfsansicht (Bearbeiten). Im oberen Fensterteil finden sie das ERD und unten die angezeigten Felder. Können sie sich nun erklären, warum vorher so viele Kombinationen angezeigt wurden? (Tipp: Beziehungen, Kartesisches Produkt)
  3. Öffnen sie die Abfrage nun in der SQL-Ansicht und schauen sie sich den SQL-Befehl an.

2.2 Aufgabe: Bsp1.odb - Abfrage2 analysieren

  1. Öffnen sie die Abfrage und begutachten sie das Abfrageresultat. Welche Kombinationen werden angezeigt? Wie unterscheiden sich diese von der Abfrage1?
  2. Wählen sie die Entwurfsansicht (Bearbeiten). Im oberen Fensterteil finden sie das ERD und unten die angezeigten Felder. Können sie sich nun erklären, warum bei Abfrage1 so viele Kombinationen angezeigt wurden und jetzt nicht mehr? (Tipp: Beziehungen)
  3. Öffnen sie die Abfrage nun in der SQL-Ansicht und schauen sie sich den SQL-Befehl an.

2.3 Aufgabe: Bsp1.odb - Abfrage3 analysieren

  1. Öffnen sie die Abfrage und begutachten sie das Abfrageresultat. Welche Kombinationen werden angezeigt? Wie unterscheiden sich diese von der Abfrage2? (Tipp: Anzahl Einträge überprüfen)
  2. Wählen sie die Entwurfsansicht (Bearbeiten). Im oberen Fensterteil finden sie das ERD und unten die angezeigten Felder. Können sie sich nun erklären, warum sich Abfrage3 von Abfrage 2 unterscheidet? (Tipp: Im ERD die Beziehungslinie mit rechter Maustaste anklicken und «Bearbeiten» wählen. Danach «Optionen-Typ» untersuchen.)
  3. Öffnen sie die Abfrage nun in der SQL-Ansicht und schauen sie sich den SQL-Befehl an. Was stellen sie fest?

2.4 Aufgabe: Bsp1.odb - Abfrage4/5/6 analysieren

  1. Öffnen sie die Abfragen und begutachten sie die Abfrageresultate. Was wird angezeigt? Wie unterscheiden sie sich? (Tipp: Anzahl Einträge überprüfen)
  2. Wählen sie jeweils die Entwurfsansicht (Bearbeiten). Im oberen Fensterteil finden sie das ERD und unten die angezeigten Felder. Was ist jeweils verschieden? (Tipp: Im unteren Fensterteil das «Kriterium» für das Feld «Name» untersuchen.)
  3. Öffnen sie die Abfrage nun in der SQL-Ansicht und schauen sie sich den SQL-Befehl an. Welcher Teil im SQL-Kommando bewirkt das nun beobachtete Verhalten?

2.5 Aufgabe: Bsp1.odb - Abfrage7 analysieren

  1. Öffnen sie die Abfrage und begutachten sie das Abfrageresultat. Was wird nun angezeigt? (Tipp: Anzahl Einträge überprüfen)
  2. Wählen sie jeweils die Entwurfsansicht (Bearbeiten). Im oberen Fensterteil finden sie das ERD und unten die angezeigten Felder. Was ist jeweils verschieden? (Tipp: Im unteren Fensterteil das «Kriterium» für das Feld «PLZ» untersuchen.)
  3. Öffnen sie die Abfrage nun in der SQL-Ansicht und schauen sie sich den SQL-Befehl an. Welcher Teil im SQL-Kommando bewirkt das nun beobachtete Verhalten?

2.6 Aufgabe: Eigene Abfrage in Bsp1.odb erstellen

Nun sollen sie eine eigene Abfrage erstellen. Dazu gibt es drei Möglichkeiten:

  • Abfrage in Entwurfsansicht erstellen (Hier bevorzugte Methode!)
  • Abfrage unter Verwendung des Assistenten erstellen (Selbsterklärend!)
  • Abfrage in SQL-Ansicht erstellen (Dafür bräuchte es noch etwas mehr SQL-Kenntnisse)

Was sollen sie nun tun?

  1. Erstellen sie mit der Entwurfsansicht eine Abfrage aller Einträge in der Tabelle T_Autotyp. Dazu müssen sie zuerst die Tabelle hinzufügen, die erforderlich ist. Danach klicken sie im Kästchen T_Autotyp auf das Attribut bzw. Feld, dass sie gerne angezeigt haben möchten. Nach Anklicken erscheint das Attribut/Feld im unteren Fensterteil. Sie können nacheinander mehrere Attribute/Felder anklicken. Diese werden im unteren Fenster nach rechts aneinandergereiht.
  2. Modifizieren sie die soeben erstellte Abfrage derart, dass nur die Autotypen angezeigt werden, deren Namen mindestens ein «e» beinhaltet.
  3. Untersuchen sie den SQL-Befehl, der duch OOBase erzeugt wurde.

2.7 Hinweis zu den vorangegangenen Aufgaben

Was sie in diesen Aufgaben gesehen haben, sind «SQL-Abfragen mit verknüpften Tabellen». Das heisst, dass bei vielen Abfragen die beim Datenbank-Design so aufwändig separierten Tabellen (Normalformen) wieder zusammengefügt werden müssen. Die einfachste Art ist das sog. Kartesische Produkt (auch Kreuzprodukt) (Mathematischer Begriff für «Jeder mit jedem») von zwei Tabellen – einmal gefilter (Aufgabe 2.2) und einmal nicht Aufgabe 2.1). In Aufgabe 2.3 wird der etwas effektivere Join verwendet. Warum ist dieser effizienter?  Stellen sie sich nun vor, jede Tabelle hätte 10'000’000 Einträge. Wie viele Kombination müssten da bei der Variante «Kreuzprodukt» vom Datenbanksystem errechnet werden? Wie schaut es da mit der Performance der DB aus?

3. Abfragen analysieren (2)

Hinweis zu den folgenden Aufgaben: Dokumentieren sie ihre Arbeiten und stellen sie diese mit den entsprechenden Datenbanken dem Dozenten elektronisch zur Verfügung.

Die folgende Aufgabe bezieht sich auf die OpenOfficeBase-Datenbanken Bsp2.odb.
Hier Bsp2.odb herunterladen

3.1 Aufgabe: Bsp2.odb - Abfrage1 analysieren

Starten sie die Bsp2.odb-OpenOfficeBase-Datenbank. Die drei Tabellen T_Autotyp, T_Person und T_Kontrollschild wurden für sie bereits erstellt. Überprüfen sie diese. Nun wählen sie unter«Datenbank» die «Abfragen» aus (Linkes Fenster in OOBase). Es werden hier fünf Abfragen angezeigt. Öffnen sie nun die erste Abfrage (Abfrage1). Es sind drei Varianten zum Öffnen der Abfrage von Interesse: (Rechte Maustaste klicken!)

  • Bearbeiten (Die Abfrage wird in der Entwurfsansicht geöffnet)
  • In SQL-Ansicht bearbeiten (Der SQL-Befehl kann direkt eingesehen und allenfalls bearbeitet werden)
  • Öffnen (Die Abfrage wird ausgeführt und das Resultat in einem separaten Fesnter angezeigt)

Was sollen sie nun tun?

  1. Öffnen sie die Abfrage und begutachten sie das Abfrageresultat. Welche Kombinationen werden angezeigt und ist die Information dieser Abfrage nützlich und sinnvoll?
  2. Wählen sie die Entwurfsansicht (Bearbeiten). Im oberen Fensterteil finden sie das ERD und unten die angezeigten Felder. Ergänzen sie im unteren Fensterteil beim Feld «Kontrollschild» den «Alias» mit dem Begriff «Autokennzeichen». Speichern sie die Abfrage und führen sie diese anschliessend aus. Was hat sich nun beim Abfrageresultat geändert? (Tipp: Überschrift)
  3. Öffnen sie die Abfrage nun in der SQL-Ansicht und schauen sie sich den SQL-Befehl an.

3.2 Aufgabe: Bsp2.odb - Abfrage2 analysieren

Die erste Abfrage war offenbar nicht so sinnvoll. Ist nun diese nützlicher?

  1. Öffnen sie die Abfrage und begutachten sie das Abfrageresultat. Welche Kombinationen werden angezeigt?
  2. Wählen sie die Entwurfsansicht (Bearbeiten). Im oberen Fensterteil finden sie das ERD und unten die angezeigten Felder. Können sie sich nun erklären, warum so viele Kombinationen angezeigt werden? (Tipp: Beziehungen)
  3. Öffnen sie die Abfrage nun in der SQL-Ansicht und schauen sie sich den SQL-Befehl an.

3.3 Aufgabe: Bsp2.odb - Abfrage3 analysieren

  1. Öffnen sie die Abfrage und begutachten sie das Abfrageresultat. Welche Kombinationen werden angezeigt? Wie unterscheiden sich diese von der Abfrage2?
  2. Wählen sie die Entwurfsansicht (Bearbeiten). Im oberen Fensterteil finden sie das ERD und unten die angezeigten Felder. Können sie sich nun erklären, warum bei Abfrage2 so viele Kombinationen angezeigt wurden und jetzt nicht mehr? (Tipp: Beziehungen)
  3. Bleiben sie in der Entwurfsansicht und ändern sie im unteren Fensterteil beim Feld «Name» die «Sortierung» auf «aufsteigend». Speichern sie die Abfrage und führen sie diese anschliessend aus. Was hat sich nun beim Abfrageresultat geändert? (Tipp: Reihenfolge)
  4. Öffnen sie die Abfrage nun in der SQL-Ansicht und schauen sie sich den SQL-Befehl an.

3.4 Aufgabe: Bsp2.odb - Abfrage4 analysieren

  1. Öffnen sie die Abfrage und begutachten sie das Abfrageresultat. Welche Kombinationen werden angezeigt? Wie unterscheiden sich diese von der Abfrage3?
  2. Wählen sie die Entwurfsansicht (Bearbeiten). Im oberen Fensterteil finden sie das ERD. Klicken sie mit der rechten Maustaste auf die Beziehungslinie zwischen «T_Person» und «T_Kontrollschild» und wählen sie «Bearbeiten». Es öffnet sich das Fenster «Verbundeigenschaften». Wie unterscheidet sich die Einstellung bei «Option» bei dieser Abfrage zu der vorangegangenen Abfrage (Abfrage3)? Überprüfen sie dies auch bei der Beziehungslinie zwischen «T_Kontrollschild» und «T_Autotyp».
    Können sie sich nun erklären, warum bei Abfrage3 im Vergleich zu dieser Abfrage gewisse Zeilen fehlen?
  3. Bleiben sie in der Entwurfsansicht und ändern sie im ERD folgendes:
    Bei der Beziehung «T_Person» zu «T_Kontrollschild» den Optionstyp auf «Rechter Verbund» und bei der Beziehung «T_Kontrollschild» zu «T_Autotyp» den Optionstyp auf «Linker Verbund». Speichern sie die Abfrage und führen sie diese anschliessend aus. Was hat sich nun beim Abfrageresultat geändert? (Tipp: Fremdschlüssel-Nullwerte)
  4. Öffnen sie die Abfrage nun in der SQL-Ansicht und schauen sie sich den SQL-Befehl an.

3.5 Aufgabe: Bsp2.odb - Abfrage5 analysieren

  1. Öffnen sie die Abfrage und begutachten sie das Abfrageresultat. Was wird nun angezeigt? (Tipp: Anzahl Einträge überprüfen)
  2. Wählen sie jeweils die Entwurfsansicht (Bearbeiten). Im oberen Fensterteil finden sie das ERD und unten die angezeigten Felder. Was ist jeweils verschieden? (Tipp: Im unteren Fensterteil das «Kriterium» für das Feld «Kontrollschild» untersuchen.)
  3. Öffnen sie die Abfrage nun in der SQL-Ansicht und schauen sie sich den SQL-Befehl an. Welcher Teil im SQL-Kommando bewirkt das nun beobachtete Verhalten?

4. Eigene Abfragen erstellen

4.1 Aufgabe: Abfragen in einer Hitparaden-DB erstellen

Sie erhalten eine Excel-Datei, die aus 100 nach ihrer Beliebtheit sortierten Musiktiteln besteht. Hier die Excel-Datei Hitparade.xls herunterladen

Ihre erste Aufgabe besteht darin, das Excel-Tabellenwerk in die Datenbank zu importieren. Üblicherweise exportiert bzw. importiert man die Daten im csv-Format. Bei OpenOffice funktioniert dies allerdings auf eine andere Art, die im folgenden beschrieben wird:

  • Erstellen sie eine neue OpenOfficeBase Datenbank mit dem Namen Hitparade.odb
  • Nun widmen sie sich der Excel-Datei Hitparade.xls. Da dieses Datei aus drei Tabellenblätter besteht, müssen diese zuerst in drei leere Excel-Dateien kopiert werden.
  • Starten sie nun OpenOfficeCalc (Dies ist die Excel-Entsprechung von OpenOffice) und öffnen sie die Excel-Dateien nacheinander.
  • Wählen sie jeweils die Kolonnen aus, die sie in die Datenbank kopieren möchten und wählen sie Bearbeiten/Kopieren (oder Ctrl-C)
  • Wechseln sie zu OpenOfficeBase und wählen Bearbeiten/Einfügen (oder Ctrl-V).
    Es erscheint ein Dialog bei der die Option «Definition und Daten» und «Erste Zeile als Spaltennamen verwenden» selektiert sein soll.
  • Im nächsten Dialogfenster wählen sie alle vorhandenen Spalten aus.
  • Im Dialogfenster «Typformatierung» überprüfen sie die Datentypen. Achtung: Zahlen als Integer und nicht als Text!
  • Wenn nun der fehlende Primärschlüssel reklamiert wird, hindern sie die Applikation nicht daran, dies zu beheben. Sie können das später immer noch korrigieren.

Nachdem sie die Tabellen in der DB angelegt und die Beziehungen definiert haben, sollen sie folgende Abfragen erstellen:

  1. Liste aller Rangierungen von 1 bis 100 unter Angabe von Musiktitel, Interpret und Nationalität.
  2. Anzeigen aller Titel sortiert nach Nationalität der Interpreten.
  3. Anzeigen aller Titel, die von Interpreten aus UK stammen.
  4. Anzeigen aller Titel, die von der Band Queen stammen.
  5. Anzeigen aller Titel, die von der Band Beatles und dem Interpreten John Lennon stammen.
  6. Anzeigen aller Titel, die nicht von der Band Abba stammen.
  7. Anzeigen aller Titel von Interpreten, deren Namen mit einem «A» beginnt.
  8. Anzeigen, wieviele Teilnehmer jede Nation aufweist. (Tipp: Es werden nur zwei Tabellen benötigt. Die T_Nationalitaet.Bezeichnung muss gruppiert werden und von der T_Interpret.Bezeichnung muss die Anzahl bestimmt werden. Beides sind übrigens Funktionen.