Projekt:2021/InstaHub: Unterschied zwischen den Versionen

Zur Navigation springen Zur Suche springen

Projekt:2021/InstaHub (Quelltext anzeigen)

Version vom 15. September 2021, 21:05 Uhr

7.989 Bytes hinzugefügt ,  21:05, 15. Sep. 2021
keine Bearbeitungszusammenfassung
Keine Bearbeitungszusammenfassung
Keine Bearbeitungszusammenfassung
(39 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 1: Zeile 1:
{{Kasten|[[#Tabellen verknüpfen|'''Zur aktuellen Aufgabe springen''']]|class=text-center}}
== Projekt InstaHub ==
== Projekt InstaHub ==


Zeile 43: Zeile 45:
## <code>SELECT * FROM users LIMIT 5 OFFSET 5</code>
## <code>SELECT * FROM users LIMIT 5 OFFSET 5</code>
# Führe die Abfragen aus und analysiere die Funktion und Syntax des <code>ORDER BY</code> Schlüsselwortes.
# Führe die Abfragen aus und analysiere die Funktion und Syntax des <code>ORDER BY</code> Schlüsselwortes.
## <code>SELECT id,username,height FROM users</code>
## <code>SELECT id,username,centimeters FROM users</code>
## <code>SELECT id,username,height FROM users ORDER BY id</code>
## <code>SELECT id,username,centimeters FROM users ORDER BY id</code>
## <code>SELECT id,username,height FROM users ORDER BY username</code>
## <code>SELECT id,username,centimeters FROM users ORDER BY username</code>
## <code>SELECT id,username,height FROM users ORDER BY height</code>
## <code>SELECT id,username,centimeters FROM users ORDER BY centimeters </code>
## <code>SELECT id,username,height FROM users ORDER BY height ASC</code>
## <code>SELECT id,username,centimeters FROM users ORDER BY centimeters ASC</code>
## <code>SELECT id,username,height FROM users ORDER BY height DESC</code>
## <code>SELECT id,username,centimeters FROM users ORDER BY centimeters DESC</code>
# Führe die Abfragen aus und analysiere die Funktion und Syntax des <code>DISTINCT</code> Schlüsselwortes.
# Führe die Abfragen aus und analysiere die Funktion und Syntax des <code>DISTINCT</code> Schlüsselwortes.
## <code>SELECT city FROM users</code>
## <code>SELECT city FROM users</code>
Zeile 55: Zeile 57:
## Die fünf größten männlichen Nutzer.
## Die fünf größten männlichen Nutzer.
## Die acht kleinsten weiblichen Nutzer, beginnend ab der Drittkleinsten.
## Die acht kleinsten weiblichen Nutzer, beginnend ab der Drittkleinsten.
## 15 unterschiedliche aufsteigend sortierte Vornamen.
## 15 unterschiedliche aufsteigend sortierte Namen.
## Das zuletzt gepostete Photo.
## Das zuletzt gepostete Photo.
## Das vorletzte Photo.
## Das vorletzte Photo.
{{Aufgabe:End}}
{{Aufgabe:End}}


=== Bedingungen ===
{{Aufgabe:Start}}
{{Aufgabe:Start}}
* Mittels der <code>WHERE</code>-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 sixh unterschiedliche Bedingungen nutzen. Bedingungen sind entweder <code>TRUE</code> oder <code>FALSE</code>. Bedingungen können mit <code>AND</code> <code>OR</code> verknüpft, oder mit <code>NOT</code> negiert werden.  
Mittels der <code>WHERE</code>-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 <code>TRUE</code> oder <code>FALSE</code>. Bedingungen können mit <code>AND</code> <code>OR</code> verknüpft, oder mit <code>NOT</code> negiert werden.  


Lies im [https://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_WHERE-Klausel_im_Detail Wikibook Einführung in SQL] den Abschnitt [https://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_WHERE-Klausel_im_Detail WHERE-Klausel im Detail]. Studiere vor allem die Operatoren <code>BETWEEN</code>, <code>CONTAINS</code> und <code>IS NULL</code>.
Lies im [https://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_WHERE-Klausel_im_Detail Wikibook Einführung in SQL] den Abschnitt [https://de.wikibooks.org/wiki/Einf%C3%BChrung_in_SQL:_WHERE-Klausel_im_Detail WHERE-Klausel im Detail]. Studiere vor allem die Vergleichsoperatoren und die Schlüsselwörter <code>BETWEEN</code>, <code>LIKE</code> und <code>IS NULL</code>.


Entwirf dann Abfragen für folgende Datensätze:
Entwirf dann Abfragen für folgende Datensätze:


# Alle Kommentare, die zwischen erstellt wurden.  
# Alle Kommentare, die zwischen 2018-09-21 07:57:19 und 2018-09-21 09:57:19 erstellt wurden.  
# Zehn Photos, deren Beschreibung <code>#sonne</code>, <code>#strand</code> oder <code>#meer</code> enthalten.
# Zehn Photos, deren Beschreibung <code>#sonne</code>, <code>#strand</code> oder <code>#meer</code> enthalten.
# Alle Photos, die als Bildformat png haben.  
# Alle Photos, deren URL <code>animals</code> enthält.
# Alle Nutzer, die aus Frankfurt oder Stuttgart kommen und zwischen 120 und 133 Zentimeter groß sind.  
# Alle Photos, deren URL ''nicht'' <code>animals</code> enthält.
# Alle Nutzer, die aus Frankfurt oder Berlin kommen und zwischen 150 und 168 Zentimeter groß sind.
# Alle Nutzer, die keine Größe eingetragen haben.
{{Aufgabe:End}}
{{Aufgabe:End}}


Zeile 84: Zeile 89:
|}
|}


{{Aufgabe:End}}
{{Aufgabe:Start}}
Eine mächtige Bedingung ist das <code>IN</code> 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 <code>IN</code> Befehl aufgebaut?
* <code>SELECT * FROM users WHERE city = "Frankfurt" OR city = "Berlin" OR city = "Leipzig" OR city = "München"</code>
* <code>SELECT * FROM users WHERE city IN ("Frankfurt","Berlin","Leipzig","München")</code>
* <code>SELECT FROM users WHERE city NOT IN ("Frankfurt","Berlin","Leipzig","München")</code>
{{Aufgabe:End}}
{{Aufgabe:End}}


=== Funktionen ===
=== Funktionen ===
{{Aufgabe:Start}}
{{Aufgabe:Start}}
SQL bietet auch Möglichkeiten, die abgefragten Datensätze durxh die Anwendung von Rechenoperationen und Funktionen zu ''transformieren''.  
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:
Um zum Beispiel die Größe der Nutzer nicht in Zentimetern, sondern in Millimetern abzufragen, könnte die Abfrage so lauten:


<code>SELECT username, height*10 FROM users</code>
<code>SELECT username,centimeters*10 FROM users</code>


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


# <code>SELECT COUNT(*) FROM comments</code>
# <code>SELECT COUNT(*) FROM comments</code>
# <code>SELECT MIN(height), MAX(height),AVG(height),SUM(height) FROM users</code>
# <code>SELECT MIN(centimeters), MAX(centimeters),AVG(centimeters),ROUND(AVG(centimeters),2),ROUND(AVG(centimeters)),SUM(centimeters) FROM users</code>
# <code>SELECT height+100,4+3,AVG(height)*2,id%2 FROM users</code>
# <code>SELECT centimeters+100,4+3,centimeters*2,id%2 FROM users</code>
# <code>SELECT CONCAT(),UPPER(username), LOWER(city) FROM users</code>
# <code>SELECT UPPER(username),CONCAT(city,", ",country) FROM users</code>
# <code>SELECT CONCAT(first," ",last),UPPER(username), LOWER(city) FROM users</code>
# <code>SELECT CONCAT(name," aus ", city, " (",UPPER(LEFT(country,3)),")") FROM users</code>
# <code>SELECT DAY(created_at),MONTH(created_at),YEAR(created_at) FROM users ORDER BY created_at DESC LIMIT 1</code>
# <code>SELECT DAY(created_at),MONTH(created_at),YEAR(created_at) FROM users LIMIT 1 ORDER BY created_at DESC</code>
# <code>SELECT DAY(created_at),MONTH(created_at),YEAR(created_at) FROM users LIMIT 1 ORDER BY created_at DESC</code>
# <code>SELECT CONCAT(first," ", UPPER(last)," (",UPPER(LEFT(3,city)),")") FROM users</code>
{{Aufgabe:End}}
{{Aufgabe:End}}
{{Aufgabe:Start}}
Mit der Funktion <code>RAND()</code> können Zufallszahlen generiert werden.
<code>SELECT RAND()</code>
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 <code>ORDER BY</code> befehl benutzen:
<code>SELECT username FROM users ORDER BY RAND()</code>
Führe die Abfrage mehrmals aus und beobachte die Ergebnisse.
{{Aufgabe:End}}
=== Tabellen verknüpfen - Teil I ===
{{Aufgabe:Start}}
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 <code>IN</code> mit einer Liste vergleicht, kann das Ergebnis einer <code>SELECT</code> Abfrage als Argument für <code>IN</code> benutzt werden.
<pre>
SELECT *
FROM photos
WHERE user_id IN (
  SELECT id
  FROM users
  WHERE city = "Berlin"
)
</pre>
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:
<pre>
SELECT *
FROM photos
WHERE user_id IN (
  SELECT id,username
  FROM users
  WHERE city = "Berlin"
)
</pre>
Erstelle folgende Abfragen:
# Alle Kommentare zu Fotos, die vom Nutzer mit der ID <code>109</code> erstellt wurden.
# Alle Likes von Nutzern, die in München oder Berlin wohnen.
# Alle Likes zu Photos, die von Nutzern gepostet wurden, die in Mühldorf oder Biederitz wohnen.
{{Aufgabe:End}}
{{Aufgabe:Start}}
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 <code>UNION</code> erreicht.
<pre>
SELECT id,username,city FROM users WHERE centimeters BETWEEN 180 AND 200
UNION
SELECT id,username,city FROM users WHERE YEAR(birthday) = 2005
</pre>
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 <code>ALL</code>?
<pre>
SELECT city FROM users WHERE LEFT(city,1) = "W"
UNION
SELECT city FROM users WHERE LEFT(city,1) = "W"
</pre>
<pre>
SELECT city FROM users WHERE LEFT(city,1) = "W"
UNION ALL
SELECT city FROM users WHERE LEFT(city,1) = "W"
</pre>
Erstelle dann folgende Abfragen mit Hilfe von <code>UNION</code>:
# Alle created_at und updated_at Felder (auch doppelte) aus den Tabellen photos, comments und likes, sortiert nach created_at.
# Alle Städtenamen, die mit "W" oder "R" beginnen.
{{Aufgabe:End}}
<!--
=== Verschachtelte Abfragen ===
{{Aufgabe:Start}}
Hinter dem <code>FROM</code> Befehl haben wir bisher immer den Namen einer Tabelle geschrieben. SQL sucht in der Datenbank nach einer passenden Tabelle und durchsucht dessen Einträge nach den passenden Datensätzen. Im Allgemeinen erwartet SQL hinter <code>FROM</code> also eine Tabelle. Diese kann auch dynamisch mit einer weiteren Abfrage erstellt werden.
<pre>
SELECT * FROM (
  SELECT id,username,name
  FROM users
  WHERE created_at > "2017-09-22"
)
WHERE LENGTH(username) = 5
</pre>
{{Aufgabe:End}}
-->
=== Fallunterscheidungen ===
{{Aufgabe:Start}}
Manchmal möchte man verschiedene Fälle an Werten unterschieden und darauf basierend die zurückgelieferten Werte steuern. Dazu gibt es in SQL die <code>CASE</code> Anweisung.
<pre>
SELECT
  url,
  CASE
    WHEN
      description LIKE '%natur%'
      OR description LIKE '%landschaft%' 
      OR description LIKE '%berg%'
    THEN true
    ELSE false
  END
FROM photos
</pre>
Analysiere die Abfrage und ihre Bestandteile, probiere sie aus und beschriebe mit eigenen Worten, wie <code>CASE</code> funktioniert.
{{Aufgabe:End}}
=== Werbung ===
{{Aufgabe:Start}}
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:
<blockquote>
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.
</blockquote>
Teste die Abfrage in InstaHub.
Hinweis: Mit der <code>CASE</code>-Anweisung von oben kannst du zwischen true und false unterscheiden:
<pre>
SELECT
  CASE gender
    WHEN 'female'
      THEN true
    ELSE false
  END
FROM users
WHERE id=$user
</pre>
{{Aufgabe:End}}
{{Aufgabe:Start}}
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.)
[[Datei:InstaHub NewAd.png|center|400px]]
{{Aufgabe:End}}
=== Datenauswertung ===
{{Aufgabe:Start}}
Bearbeite den [https://lernpfad.ch/pfad/0osdqmncydgc Lernpfad SQL: Datenauswertung].
{{Aufgabe:End}}
=== Datenmanipulation ===
Abfragesprachen wie SQL arbeiten nach dem [[wikipedia:CRUD|CRUD]]-Prinzip: '''C'''reate, '''R'''etrieve, '''U'''pdate, '''D'''elete
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.
{{Aufgabe:Start}}
Bearbeite den [https://lernpfad.ch/pfad/43folj7iwnkc Lernpfad SQL:  INSERT, DELETE und UPDATE].
{{Aufgabe:End}}
=== Tabellen verknüpfen ===
{{Aufgabe:Start}}
Bearbeite den [https://lernpfad.ch/pfad/emmr5jvq0bgu Lernpfad SQL:  Tabellen verknüpfen].
{{Aufgabe:End}}
=== SQL-Island ===
{{Aufgabe:Start}}
Begib dich auf die [[SQL-Island]].
{{Aufgabe:End}}
<!--
=== Datenbanken modellieren ===
[https://erdplus.com/standalone ERDPlus]
=== Mehrere Abfagen hintereinander ausführen ===
{{Info:Start}}
Bei SQL werden Abfragen mit einem Semikolon beendet. Für einzelne Abfragen kann das Schlusszeichen allerdings weggelassen werden. Möächte man allerdings mehrere Abfragen hintereinander ausführen, dann wird das Semikolon wichtig.
{{Info:End}}
-->


[[Kategorie:Projekte:2021/22]]
[[Kategorie:Projekte:2021/22]]
Jneug
8.581

Bearbeitungen

Abgerufen von „http://ngb.schule/wiki/Spezial:Mobiler_Unterschied/7779...7864“

Navigationsmenü