Datenbanken
Heute dreht sich alles um SQL-Abfragen mit JOINS
Bevor du loslegst...
...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.
- 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 Seite 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.SupplierIDHier 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
1 SELECT Products.ProductName, Suppliers.SupplierName FROM Products
2 JOIN Suppliers ON Products.SupplierID = Suppliers.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 Seite findest du mögliche Antworten. Überlege aber zuerst selbst, bevor du weiter klickst.
Schauen wir uns zunächst noch einmal die Syntax an...
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
1 SELECT Products.ProductName, Suppliers.SupplierName
2 FROM Products
3 JOIN Suppliers
4 ON Products.SupplierID = Suppliers.SupplierID
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...
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.
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
1 SELECT Products.ProductName, Suppliers.SupplierName
2 FROM Products
3 JOIN Suppliers
1 SELECT Products.ProductName, Suppliers.SupplierName FROM Products
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.
Zeigt an, wie viele Lieferanten aus unterschiedlichen Städten Produkte liefern.
Zeigt den Lieferantennamen, die Stadt, aus der der Lieferant kommt und den Produktnamen an, wenn die Lieferantenstadt nicht mit L beginnt.
www.appcamps.de
1 SELECT COUNT (DISTINCT Suppliers.City)
2 FROM Products
3 JOIN Suppliers
4 ON Products.SupplierID = Suppliers.SupplierID
1 SELECT Suppliers.SupplierName, Suppliers.City, 2 Products.ProductName
3 FROM Products 4 JOIN Suppliers 5 ON Products.SupplierID = Suppliers.SupplierID 6 WHERE Suppliers.City NOT LIKE 'L%' ;
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.
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.
Erstellt von Products eine Verknüpfung zu Suppliers und Categories.
Zeigt den Produktnamen, den Lieferantennamen und die Kategorie an.
www.appcamps.de
1 SELECT Products.ProductName, Categories.CategoryName, 2 AS 'Categories sorted list '
3 FROM Products 4 JOIN Categories 5 ON Products.CategoryID = Categories.CategoryID 6 ORDER BY Categories.CategoryName
1 SELECT Products.ProductName, Suppliers.SupplierName, Categories.CategoryName 2 FROM Products 3 JOIN Suppliers 4 ON Products.SupplierID = Suppliers.SupplierID 5 INNER JOIN Categories 6 ON Products.CategoryID = Categories.CategoryID
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.
Zeigt den Produktnamen und die Kategorie für alle Produkte mit einem Preis zwischen 20 und 30.
Gibt den Produktnamen, den Kategorienamen und den Preis für das Produkt mit dem höchsten Preis aus.
Gibt einen Produktnamen, den passenden Kategorienamen und den Durchschnittspreis aller Produkte aus.
www.appcamps.de
1 SELECT Products.ProductName, Categories.CategoryName, Products.Price 2 FROM Products 3 JOIN Categories 4 ON Products.CategoryID = Categories.CategoryID 5 WHERE Products.Price BETWEEN 20 AND 30
1 SELECT Products.ProductName, Categories.CategoryName, MAX (Products.Price) 2 FROM Products 3 JOIN Categories 4 ON Products.CategoryID = Categories.CategoryID
1 SELECT Products.ProductName, Categories.CategoryName, AVG (Products.Price) 2 FROM Products 3 JOIN Categories 4 ON Products.CategoryID = Categories.CategoryID
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 "Chais" angezeigt werden.
Daraus könnte man verschiedenes schließen...
- dass das Produkt "Chais" den durchschnittlichen Preis von 28.87 hat
- dass der durchschnittliche Preis der Produkte bei 28.87 liegt und "Chais" genau 28.87 kosten
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?
- SQL gibt uns das aus, was wir sagen. Egal ob es Sinn ergibt oder nicht.
- Schaue dir die Ausgabe immer genau an. Wie wahrscheinlich ist ein Preis von 28.87 ?
www.appcamps.de
1 SELECT Products.ProductName, Categories.CategoryName, AVG (Products.Price) 2 FROM Products 3 JOIN Categories 4 ON Products.CategoryID = Categories.CategoryID
1 SELECT ProductsID, ProductName, Price 2 FROM [Products ] 3 WHERE ProductName LIKE 'Chais'
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.
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
1 SELECT Products.ProductName, Categories.CategoryName, Products.Price 2 FROM Products 3 JOIN Categories 4 ON Products.CategoryID = Categories.CategoryID
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? Ergibt die Ausgabe dann noch Sinn?
- Lass dir bei der zweiten Ausgabe nur die MitarbeiterInnen 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 Seite findest du mögliche Antworten. Überlege aber zuerst selbst, bevor du weiter klickst.
Zeigt, wie viele Bestellungen jeder einzelne Spediteur hat.
Zeigt alle MitarbeiterInnen, die mehr als 10 Bestellungen verwalten.
www.appcamps.de
1 SELECT Employees.LastName, COUNT (Orders.OrderID)
2 AS 'Anzahl_Bestellungen' FROM Orders JOIN Employees
3 ON Orders.EmployeeID = Employees.EmployeeID
4 GROUP BY Employees.LastName
5 HAVING COUNT ('Anzahl Bestellungen') > 10 ;
1 SELECT COUNT (Orders.OrderID), Shippers.ShipperName FROM Orders
2 JOIN Shippers
3 ON Orders.ShipperID = Shippers.ShipperID 4 GROUP BY Orders.ShipperID
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
www.appcamps.de
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?
Fügt zwei neue Produkte (ausschließlich Produktname und generierte ID) in die Datenbank ein. Eine Angabe zu dem Lieferanten etc. erfolgt nicht.
Zeigt den Produktnamen (alphabetisch sortiert) und den Lieferantennamen an. Wenn kein Lieferant (= null) angegeben ist, wird das Produkt trotzdem angezeigt.
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
1 SELECT Products.ProductName, Suppliers.SupplierName 2 FROM Products 3 LEFT JOIN Suppliers 4 ON Products.SupplierID = Suppliers.SupplierID 5 ORDER BY Products.ProductName
1 SELECT Products.ProductName, Suppliers.SupplierName 2 FROM Products 3 JOIN Suppliers 4 ON Products.SupplierID = Suppliers.SupplierID 5 ORDER BY Products.ProductName
1 INSERT INTO Products (ProductName) 2 VALUES ('Cheers'), ('Chinese Mandarin')
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.
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.
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.
www.appcamps.de
1 SELECT Products.ProductName, Suppliers.SupplierName 2 FROM Suppliers 3 JOIN Products 4 ON Products.SupplierID = Suppliers.SupplierID
1 SELECT Products.ProductName, Suppliers.SupplierName 2 FROM Suppliers 3 INNER JOIN Products 4 ON Products.SupplierID = Suppliers.SupplierID
1 SELECT Products.ProductName, Suppliers.SupplierName 2 FROM Suppliers 3 LEFT JOIN Products 4 ON Products.SupplierID = Suppliers.SupplierID
Mit JOINS kannst du Tabellen verknüpfen.
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
- Gib die hier aufgeführten SQL Befehle selbst ein und schau dir die Ergebnisse an.
Auf der nächsten Seite geht es nun weiter mit dem Quiz.
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.
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
1 SELECT Products.ProductName, Suppliers.SupplierName 2 FROM Suppliers 3 RIGHT JOIN Products 4 ON Products.SupplierID = Suppliers.SupplierID
1 SELECT Products.ProductName, Suppliers.SupplierName 2 FROM Suppliers 3 FULL OUTER JOIN Products 4 ON Products.SupplierID = Suppliers.SupplierID
Hast du alle Aufgaben erledigt?
Super! Dann geht es jetzt weiter mit dem Quiz!
www.appcamps.de
Datenbanken Sitzung 3
By appcamps
