Datenbanken

Heute dreht sich alles um SQL-Abfragen mit JOINS 



Bevor du loslegst...

... setze bitte die Datenbank zurück, indem du auf Restore Database klickst. 

Dann werden alle Änderungen, die du bisher vorgenommen hast, gelöscht.  

 

Aufgabe

Nimm dir kurz Zeit, um die SQL-Abfrage nachzuvollziehen. 

  • Welche Ausgabe erwartest du? 
  • Wieso ist es wichtig, den Tabellennamen zusätzlich zum Spaltennamen anzugeben? Was passiert, wenn du nur den Spaltennamen angibst? Probiere mal aus, was dann passiert.  

Auf der nächsten Seiten findest du eine Antwort. Überlege aber zuerst selbst, bevor du weiter klickst.

Schauen wir uns zunächst noch einmal die Syntax an...

Bisher haben wir nur Spalten innerhalb einer Tabelle abgefragt. Wenn wir uns nun auch Spalten aus mehreren Tabellen gleichzeitig anzeigen lassen möchten, brauchen wir einen JOIN: 

SELECT Products.Productname, Suppliers.SupplierName FROM Products 
JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID

Hier findest du in der Tabelle "Products" den Produktnamen und den Fremdschlüssel zur SupplierID.

Hier findest du in der Tabelle "Suppliers" den Suppliernamen und den Primärschlüssel zur SupplierID.

Aufgabe

Spiele noch etwas mit der Query rum...

  • Was passiert, wenn du versuchst, auf die Spalte einer anderen Tabelle zuzugreifen (also z.B. SupplierName), ohne den Join zu bilden?

Auf der nächsten Seiten findest du mögliche Antworten. Überlege aber zuerst selbst, bevor du weiter klickst.

Schauen wir uns zunächst noch einmal die Syntax an...

SELECT Products.Productname, Suppliers.SupplierName 
FROM Products 
JOIN Suppliers 
ON Products.SupplierID = Suppliers.SupplierID

Hier siehst du die SQL-Abfrage und die passende Ausgabe:

Und was passiert, wenn du die Tabellennamen weglässt? Hast du es ausprobiert? 

Solange es keine Spalte mit dem gleichen Namen gibt, besteht kein Problem, doch sobald das der Fall ist (wie bei Primär- und Fremdschlüsseln), wird ein Fehler angezeigt. 

 

Woher soll SQL auch wissen, welche Spalte aus welcher Tabelle du nun meinst? 

Aufgabe

Überlege dir, was ohne den Filter mit "ON" passiert.

  • Welches Schlüsselwort hast du vor "ON" kennengelernt, um deine Ausgabe zu filtern?
  • Schaue dir die Infobox Kreuzprodukt an.

Auf den nächsten Folien findest du weitere Beispiele und Übungen. Viel Spaß mit SQL und JOINS.

Schauen wir uns zunächst noch einmal die Syntax an...

SELECT Products.Productname, Suppliers.SupplierName FROM Products 

Versuche diese SQL-Abfrage:

Ohne JOIN meldet SQL, dass es keine Spalte SupplierName gibt. Du kannst also nur auf Spalten aus anderen Tabellen zugreifen, wenn du auch den passenden JOIN bildest.

SELECT Products.Productname, Suppliers.SupplierName 
FROM Products 
JOIN Suppliers 

Und was ist mit dieser Query?

Funktioniert, jedoch ist die Ausgabe ohne den Filter mit "ON" sehr groß und wir können nicht ohne weiteres nachvollziehen, welcher Lieferant das Produkt liefert.

Wenn ein Kreuzprodukt gebildet wird, bedeutet das, dass alle möglichen Kombinationen in der Ausgabetabelle angezeigt werden. Jedem Produkt wird also jeder Lieferant zugewiesen, egal ob dieser das Produkt liefert oder nicht. Mit "ON" kann die Ausgabe gefiltert werden. "ON" ist also quasi die "WHERE"-Filterfunktion für JOINS.

Mit JOINS kannst du Tabellen miteinander verknüpfen...

Wir schauen uns zunächst die Tabelle mit den Produkten (Products) und den Lieferanten (Suppliers) an. 

 

Aufgaben

  • Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an. 
  • Schreibe eine Query, die anzeigt, aus wie vielen unterschiedlichen Ländern Lieferanten kommen, die Produkte liefern.
  • Wie viele Lieferanten kommen aus einer Stadt, die mit "B" beginnt? Macht es einen Unterschied, ob du das "b" groß oder klein schreibst?

Probiere weitere Abfragen aus. Der SQL Spickzettel hilft dir dabei.  

SELECT COUNT(DISTINCT Suppliers.City) 
FROM Products
JOIN Suppliers 
ON Products.SupplierID=Suppliers.SupplierID

Zeigt an, wie viele Lieferanten aus unterschiedlichen Städten Produkte liefern. 

SELECT Suppliers.SupplierName, Suppliers.City, 
Products.ProductName
FROM Products
JOIN Suppliers 
ON Products.SupplierID=Suppliers.SupplierID
WHERE Suppliers.City NOT LIKE "L%";

Zeigt den Lieferantennamen, die Stadt  aus der der Lieferant kommt und den Produktnamen an, wenn die Lieferantenstadt nicht mit L beginnt. 

Mit JOINS kannst du Tabellen miteinander verknüpfen...

Wir schauen uns zunächst die Tabelle mit den Produkten (Products), den Lieferanten (Suppliers) und den Kategorien (Categories) an. 

 

Aufgaben

  • Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an. 
  • Erstelle eine Query, in der du dir den Produktnamen, den Kategorienamen und die Beschreibung der Kategorie mit ausgeben lässt.

Probiere weitere Abfragen aus. Der SQL Spickzettel  hilft dir dabei.  

SELECT Products.ProductName, Categories.CategoryName 
AS "Categories sorted list"
FROM Products
JOIN Categories 
ON Products.CategoryID=Categories.CategoryID
ORDER BY Categories.CategoryName

Erstellt einen Join zwischen den Tabellen Products und Categories. Es werden nur die Spalten mit den Produktnamen und mit dem Namen der Kategorie ausgeben. Die Ausgabe wird alphabetisch nach der Kategorie sortiert und die Spalte der Kategorie in "Categories sorted list" umbenannt. 

SELECT Products.Productname, 
Suppliers.SupplierName, Categories.CategoryName 
FROM Products 
JOIN Suppliers 
ON Products.SupplierID = Suppliers.SupplierID
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID

Erstellt von Products eine Verknüpfung zu Suppliers und Categories.

Zeigt den Produktnamen, den Lieferantennamen und die Kategorie an.

Mit JOINS kannst du Tabellen miteinander verknüpfen...

Wir schauen uns zunächst die Tabelle mit den Produkten (Products), den Lieferanten (Suppliers) und den Kategorien (Categories) an. 

 

Aufgaben

  • Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an. 
  • Überprüfe die Ausgabe der letzten Query. Könnte es sein, dass jemand der diese Ausgabe sieht, einen falschen Eindruck bekommt? Versuche nachzuvollziehen, wie die Ausgabe zustande kommt.

Auf der nächsten Seite findest du mögliche Antworten. Überlege zuerst selbst, bevor du weiter klickst.

SELECT Products.ProductName, 
Categories.CategoryName, Products.Price 
FROM Products
JOIN Categories 
ON Products.CategoryID=Categories.CategoryID
WHERE Products.Price BETWEEN 20 AND 30

Zeigt den Produktnamen und die Kategorie für alle Produkte mit einem Preis zwischen 20 und 30.

SELECT Products.ProductName, 
Categories.CategoryName, MAX(Products.Price) 
FROM Products
JOIN Categories 
ON Products.CategoryID=Categories.CategoryID

Gibt den Produktnamen, den Kategorienamen und den Preis für das Produkt mit dem höchsten Preis aus.

SELECT Products.ProductName, 
Categories.CategoryName, AVG(Products.Price) 
FROM Products
JOIN Categories 
ON Products.CategoryID=Categories.CategoryID

Gibt einen Produktnamen, den passenden Kategorienamen und den Durchschnittspreis aller Produkte aus.

Mit JOINS kannst du Tabellen miteinander verknüpfen...

Wir schauen uns die Tabelle mit den Produkten (Products) und den Kategorien (Categories) an. Hier siehst du die SQL Abfrage von der vorherigen Seite mit dem passenden Output:

 

Aufgaben

  • Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an. 
  • Wieso wird die Ausgabe so angezeigt? Versuche nachzuvollziehen, wieso hier mit dem durchschnittlichen Preis ausgerechnet die Frankfurter Soße angezeigt wird.
SELECT ProductID, ProductName, Price 
FROM [Products] 
WHERE ProductName LIKE "Original%"
SELECT Products.ProductName, 
Categories.CategoryName, AVG(Products.Price) 
FROM Products
JOIN Categories 
ON Products.CategoryID=Categories.CategoryID

Daraus könnte man verschiedenes schließen...

  • dass das Produkt "Original Frankfurter grüne Soße" den durchschnittlichen Preis von 28.87 hat
  • dass der durchschnittliche Preis der Produkte bei 28.87 liegt und die Soße genau 28.87 kostet

Schauen wir doch mal nach, welcher Preis/ welche Preise bei dem Produkt stehen:

Das Produkt kostet 13. Es stimmt also nichts von den vorherigen Vermutungen... Was lernen wir daraus?

  • SQL gibt uns das aus, was wir sagen. Egal ob es Sinn macht oder nicht. 
  • Schaue dir die Ausgabe immer genau an. Wie wahrscheinlich ist ein Preis von 28.87 ?

Mit JOINS kannst du Tabellen miteinander verknüpfen...

Wir schauen uns die Tabelle mit den Produkten (Products) und den Kategorien (Categories) an. 


Aufgaben

  • Gib den hier aufgeführten SQL Befehl selbst ein und schau dir das Ergebnis an. 

Auf den nächsten Folien findest du weitere Beispiele und Übungen. Viel Spaß mit SQL und JOINS.

SELECT Products.ProductName, 
Categories.CategoryName, Products.Price
FROM Products
JOIN Categories 
ON Products.CategoryID=Categories.CategoryID

Wieso wird hier mit dem durchschnittlichen Preis ausgerechnet die Frankfurter Soße angezeigt? Lassen wir uns die Liste mal komplett ausgeben, ohne den durchschnittlichen Preis anzuzeigen:

Die Soße ist der letzte Eintrag in der Liste. Der durchschnittliche Preis wird also berechnet und zusätzlich der Produktname und der passende Kategoriename der letzten Spalte angegeben. Macht das Sinn? Naja nicht wirklich... aber was soll SQL stattdessen ausgeben, wenn der Produktname, der Kategoriename und der durchschnittliche Preis angefordert wird?

Also:

Immer hinterfragen, nachdenken und lieber kurz überprüfen, ob die Ausgabe stimmen kann.

Mit JOINS kannst du Tabellen miteinander verknüpfen...

Wir schauen uns die Tabelle mit den Bestellungen (Orders), den Spediteuren (Shippers) und den Mitarbeitern (Employees) an.

Aufgaben

  • Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an. 
  • Was passiert, wenn du bei der ersten Abfrage nicht nach den unterschiedlichen Spediteuren gruppierst? Macht die Ausgabe dann noch Sinn?
  • Lass dir bei der zweiten Ausgabe nur die Mitarbeiter anzeigen, deren Vorname mit M beginnt. Der SQL Spickzettel  hilft dir dabei.  

 

  • Mache dir Gedanken dazu, ob es vielleicht auch andere Arten von JOINS geben könnte. Eine sehr schwierige Frage, zerbrich dir nicht den Kopf ... aber Folgendes soll dir als Anregung dienen:
    • Angenommen nicht jedem Produkt ist ein Lieferant zugewiesen. Wird das Produkt dann angezeigt?

Auf der nächsten Seiten findest du mögliche Antworten. Überlege aber zuerst selbst, bevor du weiter klickst.

SELECT COUNT(Orders.OrderID), Shippers.ShipperName FROM Orders
JOIN Shippers 
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY Orders.ShipperID

Zeigt, wie viele Bestellungen jeder einzelne Spediteur hat.

SELECT Employees.LastName, COUNT(Orders.OrderID) 
AS "Anzahl_Bestellungen" FROM Orders JOIN Employees 
ON Orders.EmployeeID = Employees.EmployeeID
GROUP BY Employees.LastName
HAVING COUNT("Anzahl Bestellungen") > 10;

Zeigt alle Mitarbeiter, die mehr als 10 Bestellungen verwalten.

Aufgabe

Nimm dir etwas Zeit, um dir die verschiedenen Arten von JOINS anzuschauen und nachzuvollziehen.

  • Warum gibt es so viele verschiedene Arten von Joins? Wofür kann das nützlich sein?
  • Was denkst du nun bezüglich der Produkte ohne Lieferant? Werden diese bei der Abfrage mit dem INNER JOIN angezeigt? Füge zwei Produkte ohne SupplierID hinzu und probiere es aus.

Auf der nächsten Seiten findest du Beispiele mit SQL. Überlege aber zuerst selbst, bevor du weiter klickst.

Schauen wir uns verschiedene Arten von JOINS an...

Es gibt viele verschieden Arten von JOINS, wir betrachten nun eine Auswahl genauer... bisher haben wir nur den INNER JOIN kennengelernt 

Verknüpft zwei Tabellen und zeigt die Werte an, die in beiden Tabellen vorkommen

Verknüpft zwei Tabellen und zeigt alle Werte an, die in Tabelle 1 vorkommen und die entsprechenden Werte aus Tabelle 2 oder NULL

Verknüpft zwei Tabellen und zeigt alle Werte an, die in Tabelle 2 vorkommen und die entsprechenden Werte aus Tabelle 1 oder NULL

Verknüpft zwei Tabellen und zeigt alle Werte aus Tabelle 1 und Tabelle 2 an

Mit JOINS kannst du Tabellen verknüpfen.

Wir schauen uns die Tabelle mit den Produkten (Products) und den Lieferanten (Suppliers) an. 

 

Aufgaben

  • Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an. 
  • Ob du bei SQL JOIN oder INNER JOIN in die Abfrage tippst, ist das Gleiche. Probiere es gleich mal aus!
  • Kannst du auch einen JOIN von der Tabelle Suppliers zu Products, statt wie hier von Products zu Suppliers, herstellen? Erhälst du bei einem Left Join dann das gleiche Ergebnis wie zuvor? 
INSERT INTO Products (ProductName)  
VALUES  ("Cheers"), ("Chinese Mandarin") 

Fügt zwei neue Produkte (ausschließlich Produktname und generierte ID) in die Datenbank ein. Eine Angabe zu dem Lieferanten etc. erfolgt nicht.

SELECT Products.Productname, 
Suppliers.SupplierName
FROM Products 
LEFT JOIN Suppliers 
ON Products.SupplierID = Suppliers.SupplierID
ORDER BY Products.ProductName

Zeigt den Produktnamen (alphabetisch sortiert) und den Lieferantennamen an. Wenn kein Lieferant (= null) angegeben ist, wird das Produkt trotzdem angezeigt.

SELECT Products.Productname, 
Suppliers.SupplierName
FROM Products 
JOIN Suppliers 
ON Products.SupplierID = Suppliers.SupplierID
ORDER BY Products.ProductName

Zeigt den Produktnamen (alphabetisch sortiert) und den Lieferantennamen an. Wenn kein Lieferant (= null) angegeben ist, wird das Produkt nicht angezeigt.

Beachte! Alle Beispielabfragen, die du zu INNER JOINS kennengelernt hast oder auch andere Schlüsselwörter (siehe Spickzettel), kannst du ebenfalls für die anderen Arten von JOINS verwenden.

Mit JOINS kannst du Tabellen verknüpfen.

Wir schauen uns die Tabelle mit den Produkten (Products) und den Lieferanten (Suppliers) an. 

 

Aufgaben

  • Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an. 
  • Mit welcher Art von JOIN könntest du dir hier auch die Produkte ohne Lieferantenangabe ausgeben lassen? Bitte probiere es noch nicht in SQL aus - überlege zunächst nur. Als Hilfestellung kannst du die Folie mit der Übersicht zu den verschiedenen Arten von JOINS heranziehen. Auf der nächsten Folie findest du mögliche Antworten.
SELECT Products.Productname, Suppliers.SupplierName 
FROM Suppliers 
JOIN Products 
ON Products.SupplierID = Suppliers.SupplierID

Erstellt von Suppliers eine Verknüpfung zu Products. Zeigt den Produktnamen und den Namen des Lieferanten an.

Die ausfürliche Schreibweise mit "INNER JOIN" führt zu exakt dem gleichem Ergebnis. Wieso? SQL verwendet standardmäßig den INNER JOIN, wenn nur "JOIN" in der Anfrage steht.

SELECT Products.Productname, Suppliers.SupplierName 
FROM Suppliers 
LEFT JOIN Products 
ON Products.SupplierID = Suppliers.SupplierID

Erstellt von Suppliers eine Verknüpfung zu Products. Zeigt den Produktnamen und den Namen des Lieferanten an. Wenn es einen Lieferanten gibt, dem kein Produkt zugewiesen ist, dann wird dieser trotzdem angezeigt und das Produkt hat den Wert NULL.

SELECT Products.Productname, Suppliers.SupplierName 
FROM Suppliers 
INNER JOIN Products 
ON Products.SupplierID = Suppliers.SupplierID

Mit JOINS kannst du Tabellen verknüpfen...

...doch leider gibt es auf w3schools.com ein Problem. Theoretisch kannst du dir die Produkte über einen RIGHT oder einen FULL OUTER anzeigen lassen. Folgend siehst du die passenden Anfragen:

Aufgaben

  • Probiere die RIGHT JOIN Query unter diesem Link
  • Was fällt dir auf? Ist das Ergebnis wie erwartet?

Auf der nächsten Seiten findest du eine Antwort. Überlege aber zuerst selbst, bevor du weiter klickst.

SELECT Products.Productname, Suppliers.SupplierName 
FROM Suppliers 
RIGHT JOIN Products 
ON Products.SupplierID = Suppliers.SupplierID

Erstellt von Suppliers eine Verknüpfung zu Products. Zeigt den Produktnamen und den Namen des Lieferanten an. Wenn einem Produkt kein Lieferant (= NULL) zugewiesen ist, wird das Produkt trotzdem angezeigt.

SELECT Products.Productname, Suppliers.SupplierName 
FROM Suppliers 
FULL OUTER JOIN Products 
ON Products.SupplierID = Suppliers.SupplierID

Erstellt von Suppliers eine Verknüpfung zu Products. Zeigt den Produktnamen und den Namen des Lieferanten an.  Es werden alle Produkte und alle Lieferanten angezeigt, auch wenn jeweils kein Lieferant oder kein Produkt zugewiesen ist.

Wenn wir die SQL-Abfragen bei w3schools eintippen, bekommen wir leider folgende Fehlermeldung:

Mit JOINS kannst du Tabellen verknüpfen...

...doch leider gibt es auf w3schools.com ein Problem mit RIGHT und FULL OUTER JOINS. 

Aufgaben

  • Schaue dir die Links zu Right und Full Outer Joins an.
  • Schaue dir den Link zu Wildcards an.

Auf der nächsten Seite geht es weiter mit dem Quiz.

Über den Link funktioniert die Abfrage, jedoch werden uns nicht wie erwartet auch die Produkte ohne Lieferant angezeigt... Woran liegt das? 

Unter dem Link können wir leider nur mit der Ausgangsversion der Datenbank arbeiten. Sobald wir versuchen etwas an der Datenbank zu ändern, kriegen wir folgende Fehlermeldung: 

Wir können also nur mit der Ausgangsversion der Datenbank arbeiten. Weitere Informationen zu RIGHT und FULL JOINS und die Möglichkeit zum testen findest du hier:

Selbiges Problem besteht übrigens bei verschiedenen Platzhaltern/ Wildcards. Weitere Informationen zu verschiedenen Wildcards und die Möglichkeit zum testen findest du hier:

... das Quiz startest du

HIER

Viel Spaß!

Hast du alle Aufgaben erledigt?

Super! Dann geht es jetzt weiter mit dem Quiz! Bevor du das Quiz beginnst, setze die Datenbank bitte zuerst zurück. 

 

Datenbanken Sitzung 3

By appcamps

Datenbanken Sitzung 3

  • 1,180

More from appcamps