Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen der Seite angezeigt.

--- faecher:informatik:oberstufe:datenbanken:sql_abfrage:start [04.11.2020 19:35] – [Beispiele] sbel
+++ faecher:informatik:oberstufe:datenbanken:sql_abfrage:start [24.11.2024 16:40] (aktuell) – Frank Schiebel
@@ Zeile 3: / Zeile 3: @@
 ===== Einstieg =====
-Die Structured Query Language (SQL) ist eine standardisierte Datenbanksprache zur Nutzung auf relationalen Datenbanksystemen wie MySQL. Mit SQL können Abfragen, Manipulationen und Änderungen auf bzw. an den Tabellen der Datenbank vorgenommen werden.
+Die Structured Query Language (SQL) ist eine standardisierte Datenbanksprache zur Nutzung auf relationalen Datenbanksystemen wie MySQL oder MariaDB. Mit SQL können Abfragen, Manipulationen und Änderungen auf bzw. an den Tabellen der Datenbank vorgenommen werden.
-===== Anmelden am Webinterface =====
+===== Datenbankzugriff =====
-Wir haben ein zentrales DBMS zur Benutzung durch die Schülerinnen und Schüler eingerichtet:
+Um die folgenden Übungen zu bearbeiten, benötigt man Zugriff auf die als Vorlage zur Verfügung gestellte Datenbanken. Dieser Wiki-Bereich ist bislang nur unter Verwendung von mariaDB mit phypMaAdmin getestet - ich versuche die Datenbanken nach und nach auch als sqlite Datenbank zur Verfügung zu stellen, so dass man die Übungen auch mit dem [[https://sqlitebrowser.org/|SQLite-Browser]] lokal bearbeiten kann.
-<box 90% round #f4ffc3 #e7f5aa #e7f5aa #e7f5aa |**Aufgabe:** Anmeldung an mysql>
+Für Schülerinnen die Zugriff auf das DMBS von info-bw.de oder dem KMG Friedrichshaben haben, gehts hier zu den Anmeldeseiten von phpMyadmin, alle anderen folgen bitte den Anweisung ihrer Lehrerinnen.
-  * Öffne mit deinem Browser das phpmyadmin Interface: http://db.q-gym.de/phpmyadmin
-  * Infos zu Benutzern und Passwort bekommst du im Unterricht.
-</box>
+<tabs>
+  * [[#infbw|info-bw Datenbank Server]]
+  * [[#kmg|KMG Friedrichshafen]]
+<pane id="kmg">
+  <box 90% round #f4ffc3 #e7f5aa #e7f5aa #e7f5aa |**Aufgabe:** Anmeldung an mysql>
+    * Öffne mit deinem Browser das phpmyadmin Interface: https://dbms.kmg-fn.de
+    * Infos zu Benutzern und Passwort bekommst du im Unterricht.
+  </box>
+</pane>
+<pane id="infbw">
+  <box 90% round #f4ffc3 #e7f5aa #e7f5aa #e7f5aa |**Aufgabe:** Anmeldung an mysql>
+    * Öffne mit deinem Browser das phpmyadmin Interface: https://sql.info-bw.de
+    * Infos zu Benutzern und Passwort bekommst du im Unterricht/in der Fortbildung.
+  </box>
+</pane>
+</tabs>
 ===== Datenbankimport =====
+<tabs>
+  * [[#dbmariadb01|MariaDB/phpMyAdmin]]
+  * [[#dbsqlite01|SQLite]]
+<pane id="dbmariadb01">
 <box 90% round #f4ffc3 #e7f5aa #e7f5aa #e7f5aa |**Aufgabe:** Import der Datenbank>
   * Lade die Datei  {{ .:adressen.sql.zip |}} auf deinen Computer herunter.
   * Importiere die Datei in deine Datenbank. Du musst dabei darauf achten, dass du **in der linken Spalte zuerst deine Datenbank auswählst**, andernfalls scheitert der Import mit einem Fehler, da keine neuen Datenbanken angelegt werden können.
 </box>
+</pane>
+<pane id="dbsqlite01">
+<box 90% round #f4ffc3 #e7f5aa #e7f5aa #e7f5aa |**Aufgabe:** Import der Datenbank>
+Lade die {{ :faecher:informatik:oberstufe:datenbanken:sql_abfrage:adressen.sqlite.db.zip |gezippte Datenbank}} auf deinen Rechner herunter und entpacke sie. Öffne anschließend die Datei ''adressen.sqlite.db'' mit dem SQLite Browser.
+</box>
+</pane>
+</tabs>
-Du erhältst eine Tabelle: ''adressen'' in deiner Datenbank, in der ca. 20.000 Datensätze enthalten sind.
+Die Datenbank enthält jetzt eine Tabelle ''adressen'', in der ca. 20.000 fiktive Datensätze enthalten sind.
-Klicke die Tabelle an und mache dich mit den gespeicherten Daten etwas vertraut.
+Betrachte die Tabelle an und mache dich mit den gespeicherten Daten etwas vertraut.
 ===== Aufbau einer SQL Abfrage =====
@@ Zeile 41: / Zeile 64: @@
 ==== Ausführungsreihenfolge ====
+  SELECT (Spaltenauswahl bzw. Projektion)
   FROM (Tabellenauswahl)
   -> WHERE (Zeilenauswahl bzw. Selektion)
   -> GROUP BY (Gruppierung)
   -> HAVING (Gruppenauswahl)
-  -> SELECT (Spaltenauswahl bzw. Projektion)
   -> ORDER BY (Sortierung)
@@ Zeile 60: / Zeile 83: @@
 Die Abfrage
 <code sql>
-  SELECT * FROM artikel WHERE Id = 10
+  SELECT * FROM adressen WHERE Id = 10
 </code>
@@ Zeile 68: / Zeile 91: @@
 Führe die beiden Beispielabfragen auf deiner Datenbank aus.
 </box>
+===== Fehlerprotokoll =====
+Du wirst zu Beginn ziemlich sicher zahlreiche falsche Eingaben machen, die zu Fehlern führen, die nicht immer direkt verständlich sind. Um etwas Struktur in deine persönlichen Fehler zu bringen, solltest du ein Fehlerprotokoll führen, so dass du erkennen kannst, wenn du ähnliche Fehler immer wieder machst.
+Du kannst die folgende Datei verwenden, um die Fehler direkt hinein zu kopieren und zu vermerken, wie du sie lösen kannst. Alternativ kannst du aber natürlich auch dein Notizprogramm verwenden.
+{{ :faecher:informatik:oberstufe:datenbanken:sql_abfrage:fehlerprotokoll.odt |}}
+{{ :faecher:informatik:oberstufe:datenbanken:sql_abfrage:2024-11-22_12-02.png |Fehlerprotokoll}}
 ----
@@ Zeile 75: / Zeile 108: @@
 Arbeite mit der {{.:sql_befehle_a.pdf|Befehlsübersicht A}} in der Tabelle ''adressen''. Filtere mit einer geeigneten SQL-Abfrage die folgenden Informationen aus der Tabelle heraus.
-  - Alle Datensätze mit Name, Vorname und Wohnort.
+  - Alle Name, Vorname und Wohnort aller Datensätze.
-  - Die Vor- und Nachnamen aller gespeicherten Adressen.
+  - Die Vor- und Nachnamen aller Einträge.
   - Alle Postleitzahlen und Orte, deren PLZ größer oder gleich 80000 ist.
   - Vor- und Nachname aller Personen, die im Postleitzahlbereich 4xxxx bis 6xxxx wohnen.
@@ Zeile 86: / Zeile 119: @@
   - Alle Personen mit einem Faxgerät und einem Mobiltelefon, deren Nachnamen ein "y" enthält.
   - Alle Personen, die jünger als 45 Jahre sind, in einem Ort wohnen der mit "A" beginnt und mehr als 3333 Bonuspunkte gesammelt haben.
+[[.loesungen|Lösungen]]
 ----
@@ Zeile 91: / Zeile 127: @@
 === (A2) ===
-Denke dir mindestens 10 weitere Kriterien für die Filterung von Adressen aus, notiere diese und erstelle eine passende SQL Abfrage.
+Denke dir mindestens 2 weitere Kriterien für die Filterung von Adressen aus, notiere diese und erstelle eine passende SQL Abfrage.
 ----
 {{:aufgabe.png?nolink  |}}
 === (A3) ===
-Arbeite jetzt mit {{.:sql_befehle_b.pdf|Befehlsübersicht B}} in der Tabelle ''artikel''.
+Arbeite jetzt mit {{.:sql_befehle_b.pdf|Befehlsübersicht B}} in der Tabelle ''adressen''.
 Man kann mit den Werten, die von einer ''SELECT''-Abfrage zurückgegeben werden auch rechnen (lassen):
@@ Zeile 103: / Zeile 139: @@
 Filtere mit einer geeigneten SQL-Abfrage die folgenden Informationen aus der Tabelle heraus.
-  - Artikel mit Artikelnummer, Namen und Preis aus, aufsteigend sortiert nach Artikelnummer.
+  - Alle Personen, die mehr als 15 offene Rechnungen haben.
-  - Alle Artikel mit Nummer, Name, altem Preis und neuem Preis aus, wobei der neue Preis dem um eins erhöhten alten Preis entspricht.
+  - Die Anzahl der Personen, die mehr als 10 offene Rechnungen haben.
-  - Gib die Tabelle aus, die alle verschiedenen Preise der Artikel absteigend sortiert enthält.
+  - Die Anzahl der Personen, die mehr als 19 offene Rechnungen haben und kein Guthaben auf ihrem Konto.
-  - Gib die Tabelle mit dem Namen der Artikel aus, deren Preis genau 1,00 Euro beträgt und deren Bestand genau 70 Stück umfasst.
+  - Alle Vornamen, Nachnamen, Wohnorte und Kontostände bei denen der Kontostand mehr als 50EUR beträgt, aufsteigend Sortiert nach dem Kontostand und nach dem Nachnamen
-  - Gib die verschiedenen Preiskategorien der Artikel aus, in deren Namen „datenbank“ vorkommt.
+  - Alle Postleitzahlen in der Datenbank
-  - Gib Titel und Preis mit Mehrwertsteuer aus.
+  - Alle Geburtsdaten, die in der Datenbank vorkommen, ohne die Datensätze, bei deinen kein Geburtsdatum angegeben ist
+  - Den höchsten Kontostand
+  - Die durchschnittliche Zahl der Bonuspunkte aller Personen aus Aachen die am Bonusprogramm teilnehmen.
+  - Die durchschnittliche Zahl der offenen Rechnungen aller Personen, die jünger als 35 Jahre sind und nicht am Bonusprogramm teilnehmen.
+----
+{{:aufgabe.png?nolink  |}}
+=== (A4) ===
+Überlege dir 5 weitere verschachtelte Abfragen mit mehreren Kriterien, welche die Befehle aus Blatt B verwenden.
+----
+{{:aufgabe.png?nolink  |}}
+=== (Bonus) ===
+Möglicherweise ist hier eine kleine Recherche von nöten.
+  - Eine Liste aller Mailprovider
+  - Eine Liste aller Vorwahlen
+++++ Lösungshinweis I |
+Man muss den Inhalt einer Tabellenzelle aufteilen. Das geht zum Beispiel mit SELECT SUBSTRING_INDEX, Infos dazu z.B. dort:
+https://www.w3schools.com/sql/func_mysql_substring_index.asp
+<WRAP center round box 90%>
+**Hinweis**: Wir werden später sehen, dass ein solches Vorgehen eigentlich vermieden werden sollte - man möchte für gewöhnlich die Informationen so in der Tabelle ablegen, dass man die Inhalte nicht manipulieren muss, um an die Informationen zu gelangen.
+</WRAP>
+++++
+++++ Lösungshinweis II |
+Für die Maildomains sieht de Lösung so aus:
+<code sql>
+SELECT DISTINCT SUBSTRING_INDEX(EMail, "@", -1) FROM adressen;
+</code>
+Die Lösung für die Vorwahlen kannst du sicher selbst finden.
+++++
+==== Material ====
+{{simplefilelist>:faecher:informatik:oberstufe:datenbanken:sql_abfrage:*}}