Projekt:2021/InstaHub

Zur aktuellen Aufgabe springen

Projekt InstaHub

Arbeitsauftrag

Erstelle dir einen Zugang zu InstaHub. Das Video zeigt dir, wie du vorgehen musst: https://youtu.be/6DnmVjtF3UU

Grundlagen von Datenabfragen

Arbeitsauftrag

Benutze in InstaHub die Suchfunktion (Datenbanksymbol -> Suche), um die folgenden Abfragen zu stellen. Setze dabei den Haken bei "Zeige SQL" und vergleiche die SQL-Abfrage mit der Suchmaske und der Ergebnistabelle. Notiere dir wichtige Befehle.

1. Alle Nutzer.
2. Alle männlichen / alle weiblichen Nutzer.
3. Alle Nutzer, die sich nach dem 23.09.2017 registriert haben.
4. Alle Nutzer aus München.
5. Alle Nutzer sortiert nach Geburtstag.
6. Nutzernamen und Passwort aller Nutzer.
7. Nutzernamen und Passwort aller Nutzer, die kleiner als 1,60 Meter sind.
8. Alle Männer, die größer als 1,80 Meter sind, sortiert nach Datum der Registrierung.
9. Alle Frauen, deren Nutzername die Ziffern 45 enthält.

Arbeitsauftrag

Benutze in InstaHub die SQL Abfragefunktion (Datenbanksymbol -> SQL), um SQL-Abfragen für die folgenden Informationen zu erstellen. Nutze dazu die Befehlsreferenz SQL im Wiki und die gedruckte Befehlsübersicht SQL als Hilfe.

1. Alle Photos.
2. Alle Photos des Nutzers mit der ID 42.
3. Alle Photos, sortiert nach Erstellungsdatum.
4. Die ID des Nutzers und die URL aller Photos.
5. Alle Photos, deren Beschreibung die Zeichenkette #sonne enthält.

Denk dir weitere Abfragen aus, mit der du die Syntax des SELECT-Befehls ausprobieren kannst.

Abfragen einschränken und sortieren

Arbeitsauftrag

SELECT Abfragen lassen sich mit den Schlüsselwörtern LIMIT, OFFSET, ORDER BY und DISTINCT weiter präzisieren.

1. Führe die Abfragen aus und analysiere die Funktion und Syntax der LIMIT und OFFSET Schlüsselwörter.
   1. SELECT * FROM users LIMIT 5
   2. SELECT * FROM users LIMIT 10
   3. SELECT * FROM users LIMIT 5 OFFSET 5
2. Führe die Abfragen aus und analysiere die Funktion und Syntax des ORDER BY Schlüsselwortes.
   1. SELECT id,username,centimeters FROM users
   2. SELECT id,username,centimeters FROM users ORDER BY id
   3. SELECT id,username,centimeters FROM users ORDER BY username
   4. SELECT id,username,centimeters FROM users ORDER BY centimeters
   5. SELECT id,username,centimeters FROM users ORDER BY centimeters ASC
   6. SELECT id,username,centimeters FROM users ORDER BY centimeters DESC
3. Führe die Abfragen aus und analysiere die Funktion und Syntax des DISTINCT Schlüsselwortes.
   1. SELECT city FROM users
   2. SELECT DISTINCT city FROM users
4. Kombiniere nun die neuen Schlüsselwörter miteinander und entwickele Abfragen zu folgenden Datensätzen:
   1. Die fünf größten männlichen Nutzer.
   2. Die acht kleinsten weiblichen Nutzer, beginnend ab der Drittkleinsten.
   3. 15 unterschiedliche aufsteigend sortierte Namen.
   4. Das zuletzt gepostete Photo.
   5. Das vorletzte Photo.

Bedingungen

Arbeitsauftrag

Mittels der WHERE-Klausel lassen sich Datensätze gezielt auf bestimmte Bedingungen einschränken. Jedes Datenfeld hat einen festgelegten Datentyp (im Wesentlichen Zahl, Text und Datum). Je nach Datentyp lassen sich unterschiedliche Bedingungen nutzen. Bedingungen sind entweder TRUE oder FALSE. Bedingungen können mit AND OR verknüpft, oder mit NOT negiert werden.

Lies im Wikibook Einführung in SQL den Abschnitt WHERE-Klausel im Detail. Studiere vor allem die Vergleichsoperatoren und die Schlüsselwörter BETWEEN, LIKE und IS NULL.

Entwirf dann Abfragen für folgende Datensätze:

1. Alle Kommentare, die zwischen 2018-09-21 07:57:19 und 2018-09-21 09:57:19 erstellt wurden.
2. Zehn Photos, deren Beschreibung #sonne, #strand oder #meer enthalten.
3. Alle Photos, deren URL animals enthält.
4. Alle Photos, deren URL nicht animals enthält.
5. Alle Nutzer, die aus Frankfurt oder Berlin kommen und zwischen 150 und 168 Zentimeter groß sind.
6. Alle Nutzer, die keine Größe eingetragen haben.

Arbeitsauftrag

Lege dir auf einem Blatt ein Tabelle im unten gezeigten Format an. Trage links die bisher kennengelernten Schlüsselwörter ein und rechts daneben eine kurze Beschreibung der Funktion.

SQL-Befehl	Bedeutung
SELECT	Fragt Datensätze nach bestimmten Kriterien aus der Datenbank ab.

Arbeitsauftrag

Eine mächtige Bedingung ist das IN Schlüsselwort. Mit ihm kann ein Wert mit einer ganzen Liste von Werten verglichen werden. Der Operator prüft also, ob ein Wert in einer Liste vorkommt, oder nicht.

Betrachte beispielsweise folgende Abfragen und vergleiche sie miteinander. Was fragen sie ab und wie ist der IN Befehl aufgebaut?

• SELECT * FROM users WHERE city = "Frankfurt" OR city = "Berlin" OR city = "Leipzig" OR city = "München"
• SELECT * FROM users WHERE city IN ("Frankfurt","Berlin","Leipzig","München")
• SELECT FROM users WHERE city NOT IN ("Frankfurt","Berlin","Leipzig","München")

Funktionen

Arbeitsauftrag

SQL bietet auch Möglichkeiten, die abgefragten Datensätze durch die Anwendung von Rechenoperationen und Funktionen zu transformieren.

Um zum Beispiel die Größe der Nutzer nicht in Zentimetern, sondern in Millimetern abzufragen, könnte die Abfrage so lauten:

SELECT username,centimeters*10 FROM users

Einen Überblick der wichtigsten Funktionen gibt es in der Befehlsreferenz SQL. Analysiere mit deren Hilfe folgende Abfragen und notiere ihre Bedeutung, ohne sie vorher auszuführen. Prüfe dann deine Vermutung in InstaHub.

1. SELECT COUNT(*) FROM comments
2. SELECT MIN(centimeters), MAX(centimeters),AVG(centimeters),ROUND(AVG(centimeters),2),ROUND(AVG(centimeters)),SUM(centimeters) FROM users
3. SELECT centimeters+100,4+3,centimeters*2,id%2 FROM users
4. SELECT UPPER(username),CONCAT(city,", ",country) FROM users
5. SELECT CONCAT(name," aus ", city, " (",UPPER(LEFT(country,3)),")") FROM users
6. SELECT DAY(created_at),MONTH(created_at),YEAR(created_at) FROM users ORDER BY created_at DESC LIMIT 1
7. SELECT DAY(created_at),MONTH(created_at),YEAR(created_at) FROM users LIMIT 1 ORDER BY created_at DESC

Arbeitsauftrag

Mit der Funktion RAND() können Zufallszahlen generiert werden.

SELECT RAND()

In der Regel wird sie nur selten benötigt, aber für einen Fall ist sie recht nützlich, denn man kann sie als Argument für den ORDER BY befehl benutzen:

SELECT username FROM users ORDER BY RAND()

Führe die Abfrage mehrmals aus und beobachte die Ergebnisse.

Tabellen verknüpfen - Teil I

Arbeitsauftrag

Eine der wichtigsten Eigenschaften von relationalen Datenbanken ist die Verknüpfung der in den verschiedenen Tabellen gespeicherten Daten.

Das ergebnis einer Abfrage ist eine Liste von Datensätzen. Da IN mit einer Liste vergleicht, kann das Ergebnis einer SELECT Abfrage als Argument für IN benutzt werden.

SELECT * 
FROM photos 
WHERE user_id IN (
  SELECT id
  FROM users
  WHERE city = "Berlin"
)

Analysiere die Abfrage und ihre Bestandteile, probiere sie aus und beschriebe mit eigenen Worten, welche Datensätze abgefragt werden.

Teste dann folgende Abfrage und erkläre die Fehlermeldung:

SELECT * 
FROM photos 
WHERE user_id IN (
  SELECT id,username
  FROM users
  WHERE city = "Berlin"
)

Erstelle folgende Abfragen:

1. Alle Kommentare zu Fotos, die vom Nutzer mit der ID 109 erstellt wurden.
2. Alle Likes von Nutzern, die in München oder Berlin wohnen.
3. Alle Likes zu Photos, die von Nutzern gepostet wurden, die in Mühldorf oder Biederitz wohnen.

Arbeitsauftrag

In seltenen Fällen möchte man die Ergebnisse von zwei Abfragen zu einem Ergebnis zusammenfassen. Dazu müssen die Spalten der Abfragen aber genau übereinstimmen. Die Vereinigung von zwei Ergebnissen wird mit dem Schlüsselwort UNION erreicht.

SELECT id,username,city FROM users WHERE centimeters BETWEEN 180 AND 200
UNION
SELECT id,username,city FROM users WHERE YEAR(birthday) = 2005

Analysiere die Abfrage und ihre Bestandteile, probiere sie aus und beschriebe mit eigenen Worten, welche Datensätze abgefragt werden.

Probiere dann die folgenden beiden Abfragen aus und vergleiche ihre Ergebnisse. Was ändert das Schlüsselwort ALL?

SELECT city FROM users WHERE LEFT(city,1) = "W"
UNION
SELECT city FROM users WHERE LEFT(city,1) = "W"

SELECT city FROM users WHERE LEFT(city,1) = "W"
UNION ALL
SELECT city FROM users WHERE LEFT(city,1) = "W"

Erstelle dann folgende Abfragen mit Hilfe von UNION:

1. Alle created_at und updated_at Felder (auch doppelte) aus den Tabellen photos, comments und likes, sortiert nach created_at.
2. Alle Städtenamen, die mit "W" oder "R" beginnen.

Fallunterscheidungen

Arbeitsauftrag

Manchmal möchte man verschiedene Fälle an Werten unterschieden und darauf basierend die zurückgelieferten Werte steuern. Dazu gibt es in SQL die CASE Anweisung.

SELECT 
  url,
  CASE 
    WHEN 
      description LIKE '%natur%' 
      OR description LIKE '%landschaft%'  
      OR description LIKE '%berg%' 
    THEN true 
    ELSE false 
  END 
FROM photos 

Analysiere die Abfrage und ihre Bestandteile, probiere sie aus und beschriebe mit eigenen Worten, wie CASE funktioniert.

Werbung

Arbeitsauftrag

Auf einer Photoseite werden unterhalb der Photos Werbebanner eingeblendet. Welche Werbung gezeigt wird, wird durch eine SQL-Abfrage gesteuert. Die Werbung kann auf einen Nutzer zugeschnitten sein, oder auf das Photo, unter dem sie angezeigt wird.

Überlege dir eine Werbeanzeige, die du in InstaHub schalten möchtest und für welche Zielgruppe diese geeignet ist. Wie könntest du Nutzer der Zielgruppe in InstaHub möglichst genau identifizieren? (Um deine Anzeige später zu testen ist es einfacher, die Werbung anhand des Photos zu schalten.)

Entwirf dann eine SQL-Abfrage, die so genau es geht auf die Zielgruppe zugeschnitten ist. Die Vorgaben für eine Werbe-Abfrage lauten:

Die Abfrage muss true oder false ergeben. Alternativ kann auch eine Liste zurückgegeben werden, in welcher die Benutzer-ID gesucht wird. $user kann als Platzhalter für den angemeldeten Benutzer und $photo für das aktuelle Photo verwendet werden.

Teste die Abfrage in InstaHub.

Hinweis: Mit der CASE-Anweisung von oben kannst du zwischen true und false unterscheiden:

SELECT 
  CASE gender 
    WHEN 'female' 
      THEN true 
    ELSE false 
  END 
FROM users 
WHERE id=$user

Arbeitsauftrag

Erstellte ein neues Werbebanner in InstaHub. Gehe dazu auf den Kiosk oben und Wähle "Kampagnen". Klicke das grüne Plus und gib die relevanten Daten ein. (Name und SQL-Anfrage sind notwendig. Wenn du möchtest, kannst du ein eigenes Bild nutzen. Der Rest kann so bleiben wie er ist.)

Datenauswertung

Arbeitsauftrag

Bearbeite den Lernpfad SQL: Datenauswertung.

Datenmanipulation

Abfragesprachen wie SQL arbeiten nach dem CRUD-Prinzip: Create, Retrieve, Update, Delete

Bisher haben wir uns nur mit dem R befasst und Daten aus der Datenbank abgefragt. Die drei anderen Teile des Akronyms sind in SQL zum Glück weniger komplex.

Arbeitsauftrag

Bearbeite den Lernpfad SQL: INSERT, DELETE und UPDATE.

Tabellen verknüpfen

Arbeitsauftrag

Bearbeite den Lernpfad SQL: Tabellen verknüpfen.

SQL-Island

Arbeitsauftrag

Begib dich auf die SQL-Island.