Heute dreht sich alles um SQL-Abfragen mit JOINS
...verwende die Ursprungs Datei der Datenbank, die du zu Beginn des Kurses von deiner Lehrkraft bekommen hast.
Lokale Kopien, mit gelöschten oder veränderten Datenbank-Einträgen kannst du nicht verwenden, um die kommenden Übungen zu machen.
www.appcamps.de
Aufgabe
Nimm dir kurz Zeit, um die SQL-Abfrage nachzuvollziehen.
Auf der nächsten Seite findest du eine Antwort. Überlege aber zuerst selbst, bevor du weiter klickst.
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.
www.appcamps.de
Aufgabe
Spiele noch etwas mit der Query rum...
Auf der nächsten Seite findest du mögliche Antworten. Überlege aber zuerst selbst, bevor du weiter klickst.
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?
www.appcamps.de
Aufgabe
Überlege dir, was ohne den Filter mit "ON" passiert.
Auf den nächsten Folien findest du weitere Beispiele und Übungen. Viel Spaß mit SQL und JOINS.
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.
www.appcamps.de
Wir schauen uns zunächst die Tabelle mit den Produkten (Products) und den Lieferanten (Suppliers) an.
Aufgaben
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.
www.appcamps.de
Wir schauen uns zunächst die Tabelle mit den Produkten (Products), den Lieferanten (Suppliers) und den Kategorien (Categories) an.
Aufgaben
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.
www.appcamps.de
Wir schauen uns zunächst die Tabelle mit den Produkten (Products), den Lieferanten (Suppliers) und den Kategorien (Categories) an.
Aufgaben
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.
www.appcamps.de
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
SELECT ProductID, ProductName, Price
FROM [Products]
WHERE ProductName LIKE "Chais"
SELECT Products.ProductName,
Categories.CategoryName, AVG(Products.Price)
FROM Products
JOIN Categories
ON Products.CategoryID=Categories.CategoryID
Daraus könnte man verschiedenes schließen...
Schauen wir doch mal nach, welcher Preis/ welche Preise bei dem Produkt stehen:
Das Produkt kostet 18. Es stimmt also nichts von den vorherigen Vermutungen... Was lernen wir daraus?
www.appcamps.de
Wir schauen uns die Tabelle mit den Produkten (Products) und den Kategorien (Categories) an.
Aufgaben
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 "Chais" angezeigt? Lassen wir uns die Liste mal komplett ausgeben, ohne den durchschnittlichen Preis anzuzeigen:
"Chais" ist der erste Eintrag in der Liste. Der durchschnittliche Preis wird also berechnet und zusätzlich der Produktname und der passende Kategoriename der ersten Spalte angegeben. Ergibt 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.
www.appcamps.de
Wir schauen uns die Tabelle mit den Bestellungen (Orders), den Spediteuren (Shippers) und den Mitarbeitern (Employees) an.
Aufgaben
Auf der nächsten Seite 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 MitarbeiterInnen, die mehr als 10 Bestellungen verwalten.
www.appcamps.de
Aufgabe
Nimm dir etwas Zeit, um dir die verschiedenen Arten von JOINS anzuschauen und nachzuvollziehen.
Auf der nächsten Seiten findest du Beispiele mit SQL. Überlege aber zuerst selbst, bevor du weiter klickst.
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
www.appcamps.de
Wir schauen uns die Tabelle mit den Produkten (Products) und den Lieferanten (Suppliers) an.
Aufgaben
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.
www.appcamps.de
Wir schauen uns die Tabelle mit den Produkten (Products) und den Lieferanten (Suppliers) an.
Aufgaben
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
www.appcamps.de
Du kannst dir die Produkte auch über einen RIGHT oder einen FULL OUTER anzeigen lassen, sodass auch die Produkte ohne Lieferantenangabe ausgegeben werden. Folgend siehst du die passenden Anfragen:
Aufgaben
Auf der nächsten Seite geht es nun weiter mit dem Quiz.
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.
www.appcamps.de
Super! Dann geht es jetzt weiter mit dem Quiz!
www.appcamps.de