gf2:datenbanken:sql [DokuWiki CSC]

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
====== Einführung in SQL ======
Stuctured Quere Language (SQL) ist eine standardisierte Abfrage-Sprache um Daten in Datenbanken zu speichern, zu manipulieren und herauszufiltern (zu erhalten). \\
Im folgenden lernen Sie ein paar Grundzüge dieser Abfragesprache kennen, die sie für verschiedenste Datenbanksysteme (wie MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, u.v.m.) verwenden können.\\

Schauen wir SQL-Beispiele in Instahub an. \\ 

Beispielaufgabe: //Suchen Sie alle Usernamen der Benutzerinnen aus Ihrer Datenbank.//
Exakt formuliert bedeutet dies, es muss eine Abfrage formuliert werden, welche die Tabelle users nach allen Nutzerinnen durchsucht und den Benutzernamen dieser Nutzerinnen zurückgibt. \\ Hier kann über die Suche auch die SQL-Abfrage angezeigt werden.

Die SQL-Abfrage ist somit:
<code SQL>
SELECT username
FROM users
WHERE gender = "female"
</code>
{{ :gf2:datenbanken:datenbanken_sql3.png?direct&1000 |}}
<WRAP nicebox grey>
Die SQL Befehle sind alle am Seitenende beschrieben!
</WRAP>
<WRAP nicebox green>
**Auftrag 1** \\ 
Bearbeiten Sie die folgenden kurzen Aufträge mithilfe Ihres Instahubs. Dabei nutzen Sie wieder die Datenbank-Suche (SQL) oben rechts in Instahub. Notieren Sie anschliessend die SQL-Abfragen.
Ziel ist es, in einem zweiten Schritt nicht mehr über die Suche, sondern direkt eine SQL-Abfrage formulieren zu können.
  - Alle Benutzer:innen, deren Usernamen mit dem Buchstaben "S" beginnt.
  - Alle Usernamen der männlichen Benutzer.
  - Die Benutzer:innen, welche das Wort „Johan“ im Benutzernamen haben.
  - Die Benutzer, die noch nicht 18 Jahre alt sind.

**Auftrag 2** \\
Was genau wird durch die folgenden Abfragen genau als Ergebnis von Instahub herausgefiltert? Beschreiben Sie in eigenen Worten und besprechen dies Sie zu zweit.\\ Nachdem Sie dies besprochen haben, geben Sie die Abfragen (Kopieren und Einfügen) in Instahup beim SQL-Feld ein und kontrollieren Sie, was dabei herausgegeben wird!
<code SQL>
SELECT name, birthday
FROM users
WHERE username LIKE "%S%" AND birthday < "2010-09-13 00:00:03"</code>
<code SQL>
SELECT name, birthday
FROM users
WHERE centimeters > 160 AND (username NOT LIKE "%K%" AND centimeters < 180) </code> 
<code SQL> 
SELECT id, name, password
FROM users
WHERE gender = "male" OR created_at < "2017-09-15 00:00:03"
</code>

**Auftrag 3** \\  
Was genau passiert durch diesen SQL-Befehl? Besprechen Sie dies zu zweit, testen Sie und generieren Sie einen weiteren Befehl dieser Art.\\
//Erste Abfrage//
<code SQL>
INSERT INTO users (id, username, email, password, name, gender, birthday, city, country, centimeters)
VALUES ("300", "jeanpaul66", "jean.paul@instahub.test", "123456", "papaux", "male", "2066-01-31 00:00:00", "Fribourg", "Schweiz", "197") </code>
//Zweite Abfrage//
<code SQL>
INSERT INTO users (id, username, email, password, name, gender, birthday, city, country, centimeters)
VALUES ("301", "mathilde", "mathilde@instahub.test", "123456", "mathilde", "female", "2067-01-31 00:00:00", "Fribourg", "Schweiz", "160")
</code>
**Auftrag 4**\\ 
  - Was genau wird hier im folgenden Beispiel mit dem Befehl ''COUNT'' gezählt? Testen und erklären Sie.\\ <code SQL> SELECT COUNT(photo_id)FROM comments </code>
  - Was genau wird hier im folgenden Beispiel mit dem Befehl ''COUNT (DISTINCT...)'' gezählt? Testen und erklären Sie. \\Was ist der Unterschied zur ersten Aufgabe? \\ <code SQL>SELECT COUNT(DISTINCT photo_id)FROM comments</code> 
  - Wie viele verschiedene Fotos wurden "geliked"? Bestimmen Sie dies durch eine Abfrage wie bei der obigen Aufgabe.

** Auftrag 5 ** \\ 
Testen Sie die Befehle ''MIN()'',''MAX()'', ''AVG()'',''SUM()'' gleich wie den Count-Befehl oben. Was ist der Zweck dieser Abfragebefehle? Wie werden Sie genau genutzt?
<code SQL>SELECT MIN(birthday)
FROM users </code>

** Auftrag 6 ** \\ 
a) Was genau passiert bei dieser Abfrage?
<code SQL>
SELECT users.username, photos.description 
FROM users JOIN photos on photos.user_id=users.id
WHERE users.gender = "female" AND photos.created_at>"2020-09-15 00:00:03"
</code> 
b) Wie kann diese Abfrage erklärt werden?
<code SQL>
SELECT users.username
FROM users Join likes on users.id = likes.user_id
WHERE likes.updated_at > "2017-11-05"
</code>
<code SQL>
SELECT users.username, tags.name
FROM users 
Join likes on users.id = likes.user_id
Join tags on tags.photo_id = likes.photo_id
WHERE likes.updated_at > "2017-11-05"
</code>
c) Formulieren Sie mindestens zwei weitere Abfragen, die ein ''JOIN'' beinhalten. Achten Sie darauf, dass Sie die Tabellen durch einen gemeinsamen Schlüssel verbinden!
\\ 
d) Wie können Sie einem interessierten Passanten auf der Strasse erklären, was die Stärke von Relationalen Datenbanken ist und welche wichtige Rolle dabei ein ''JOIN'' spielt.

** Auftrag 7 ** \\ 
a) Führen Sie den Befehl aus(Kopieren des SQL - Befehls in die Eingabezeile der Datenbankabfrage.)

<code SQL>
SELECT *
FROM analytics
</code>

b) Rufen Sie mehrere Bilder bzw. Fotos in Instahub in der Einzelansicht auf (mindestens 6 oder 7 Fotos). Wiederholen Sie danach die Aufgabe a). Was hat sich verändert?

c) Was ist der Sinn und Zweck der Tabelle //anaytics// von Instahub?\\
''analytics: id, ip, device, brand_family, brand_model, browser_family, browser_version, platform_family, platform_version, user_id, photo_id, created_at, updated_at''

d) Diskutieren Sie zu zweit die folgenden drei Aussagen:
  * Aussage 1: "Instahub (bzw.Instagram) kann sehr viele Daten der Nutzer analysieren."
  * Aussage 2: "Sogar der Browser, mit welchem auf die Fotos zugegriffen wurde, kann ausgelesen werden."
  * Aussage 3: "Als Nutzer muss man sich jedoch keine Sorgen um den Datenschutz machen."

Nützlicher Link: [[https://www.edoeb.admin.ch/edoeb/de/home/datenschutz/internet_technologie/tracking.html| EDÖB - Tracking]]

</WRAP>
===== Einige wichtige Befehle von SQL =====

^SQL-Klausel ^Zweck ^Erklärung ^
|SELECT |Gibt die Felder mit den gewünschten Daten an. |Damit lässt sich entscheiden, welche Felder (Spalten) wir anzeigen wollen. Möchte man alle Spalten einer Tabelle auswählen, dann braucht man nicht zwingend alle Felder der Tabelle nach der SELECT-Klausel manuell anzugeben. Man kann einfach das Asterikszeichen * verwenden. 
''SELECT *'' bedeutet also, dass alle Spalten einer Tabelle angewählt werden. |
|FROM |Enthält die Tabellen mit den Feldern, die in der SELECT-Klausel angegeben sind |In welchen Tabellen liegen die Felder (Spalten), die wir erhalten wollen? |
|JOIN|Verbindet zwei verschiedene Tabellen via einen gemeinsam existenten Schlüssel.| Wie kann eine Abfrage über mehr als eine Tabelle gemacht werden? --> Durch das Verbinden von Tabellen anhand eines gemeinsam existierenden Feldes. |
|WHERE |Gibt Feldkriterien an, die jeder Datensatz erfüllen muss, um in die Ergebnisse einbezogen zu werden|Hier lassen sich Datensätze herausfiltern (z.B. wenn wir nur gewisse Daten einer Tabelle anzeigen wollen). |
|INSERT INTO ...VALUES ...|So kann die Datenbank durch einen weiteren Datensatz mithilfe von SQL erweitert werden. | INSERT INTO: Durch diesen Befehl kann ein weiterer Datensatz in die Tabelle eingefügt werden. Es müssen die vordefinierten Fehler durch die genaue Bezeichnung genannt und nach VALUES die Werte in "Anführungsstrichen" aufgelistet werden. |
^Funktionen^ Die untenstehenden Funktionen berechnen aus mehreren Datensätzen einen bestimmten Wert, und geben diesen dann zurück.^^
|COUNT | Zählt die Anzahl der gefundenen Datensätze||
|MIN | Gibt den Minimalwert der gefilterten Datensätze zurück||
|MAX | Gibt den Maximalwerte der gefilterten Datensätze zurück||
|AVG | Berechnet den Durchschnittswert der Datensätze||
|SUM | Berechnet die Summe der Datensätze||
|DISTINCT| Gibt nur alle verschiedenen Datensätze zurück, gleiche werden nicht doppelt aufgelistet, gezählt etc||
|GROUP BY | Resultate der obigen Funktionen gruppieren | ''GROUP BY'' Gibt an, nach welcher Spalte das Resultat gruppiert werden soll. Beispielsweise gibt die Anweisung ''SELECT city, count(city) FROM users GROUP BY city'' für jede Stadt an, wie viele Nutzer/innen aus dieser Stadt im Netzwerk sind|| 
=== Operatoren ===
^Zeichen bzw. Bezeichnung ^Bedeutung ^
|= |Gleich wie |
|> |Grösser als |
|>= |Grösser oder gleich gross wie |
|< |Kleiner als |
|%%<=%% |Kleiner oder gleich gross wie |
|<> |Ungleich wie (unterschiedlich) |
|BETWEEN a AND b |Zwischen a und b |
|logischer Operator AND | Beide genannten Bedingungen müssen erfüllt sein.|
|logischer Operator OR | mindestens eine der beiden genannten Bedingungen muss erfüllt sein|

[[gf2:datenbanken:herausforderungen|Verschiedene Datenbanktypen]] \\
[[gf2:datenbanken:lernziele|Lernziele]]