Betrieb von Datenbanken Marco Skulschus & Marcus Wiederstein

1. Betrieb von DatenbankenMarco Skulschus & Marcus Wiederstein Komplexe Abfragen Lehrbuch, Kapitel 3

2.  Seminar-Inhalt Grundlagen Einfache Abfragen Komplexe Abfragen Datenmanipulation Grundlagen T-SQL Programm-Module in der DB Administration

3.  Modul-Inhalt Verknüpfungen Unterabfragen Verzweigungen

4. Verknüpfungen: Prinzip • Die Verbindungen zwischen den einzelnen Tabellen richtet die Primärschlüssel-Fremdschlüssel-Beziehung ein. • Dabei erscheint der Schlüsselwert einer Primärschlüsselspalte in einer anderen Tabelle als Fremdschlüssel und erlaubt über die Verknüpfung die zusätzlichen Informationen dieser anderen Tabelle abzurufen. • Das Prinzip des relationalen Modells liegt darin, dass die Objekte der realen Welt mit einzelnen Entitäten (Relationen) abgebildet werden. • Sie besitzen jeweils einen Primärschlüssel, der aus einem oder mehreren Feldern besteht.

5. Verknüpfungen: Prinzip • Beispiel mit den Tabellen Abteilung, Mitarbeiter, Kundenbetreuung und Kunde.

6. Verknüpfungen: Manuelle Verknüpfungen • Verknüpfungsbedingung, welche die ContactID-Spalten beider Tabellen gleich setzt, • Inhaltliche Filterung/Einschränkung wie er auch in einer gewöhnlichen Abfrage mit Blick auf eine Tabelle erscheinen würde. • Syntaktisch richtet man eine Verknüpfung manuell in der FROM- und WHERE-Klausel ein. • In der Tabellenliste der FROM-Klausel schreibt man die Tabellen, aus denen gleichzeitig Daten abgerufen werden sollen, in einer Komma-getrennten Liste. • Innerhalb der WHERE-Klausel sind dann zwei verschiedene Arten von Bedingungen:

7. Verknüpfungen: Manuelle Verknüpfungen FirstNameLastNameHireDate ------------- ------------- ----------- Greg Alderson 1999-01-03 Sean Alexander 1999-01-29 -- Angestellte mit Kontaktdaten SELECT FirstName, LastName, HireDate FROM HumanResources.Employee AS emp, Person.Contact AS con WHERE emp.ContactID = con.ContactID AND LastName < 'B' Tabellenliste Verknüpfungs-bedingung Inhaltlicher Filter

8. Verknüpfungen: Manuelle Verknüpfungen

9. Verknüpfungen: Qualifizierte Spaltennamen • Sollten durch die Verknüpfung zwei Spalten den gleichen Namen erhalten und beide in der Ergebnismenge ausgegeben oder sonstwie verarbeitet werden bzw. werden gleichnamige Spalten verknüpft, dann muss man angeben, aus welcher Tabelle diese Spalten stammen.

10. Verknüpfungen: Ungewollte Kreuzverknüpfung • Die ungewollte Kreuzverknüpfung entsteht, wenn in der WHERE-Klausel eine der inhaltlich notwendigen Verknüpfungsbedingungen fehlt. • Die syntaktisch richtige und logisch falsche Abfrage führt zur kreuzweisen Kombination von Datensätzen. SELECT edh.EmployeeID, edh.ShiftID, sh.ShiftID, sh.StartTime FROM HumanResources.EmployeeDepartmentHistory AS edh, HumanResources.Shift AS sh WHERE edh.EmployeeID <3 EmployeeIDShiftIDShiftIDStartTime ----------- ------- ------- ------------ 1 1 1 07:00:00.000 1 1 2 15:00:00.000 1 1 3 23:00:00.000 2 1 1 07:00:00.000 2 1 2 15:00:00.000 2 1 3 23:00:00.000

11. Verknüpfungen: ANSI SQL-Verknüpfungen • Die innere Verknüpfung übernimmt nur die Datensätze aus der einen Tabelle, die in der anderen Tabelle einen Partnerdatensatz finden, der über die Primärschlüssel-/Fremdschlüssel-Verknüpfung zu finden ist. • Die äußere Verknüpfung betrachtet auch die Datensätze, die gerade keinen Partnerdatensatz finden, und übernimmt diese in die Ergebnismenge. • Die besondere Verbesserung der ANSI-SQL-Verknüpfungen besteht daraus, dass • sowohl die Tabellen, aus denen die Daten übernommen werden sollen, • als auch die Verknüpfungsbedingungen komplett in der FROM-Klausel stehen.

12. Verknüpfungen: ANSI SQL-Verknüpfungen -- Verkäufer, Gebiete und Länder SELECT sp.SalesPersonID, Name, [Group] FROM Sales.SalesPerson AS sp INNER JOIN Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID • Innere Verknüpfung SalesPersonID Name Group ------------- -------------- ---------------- 275 Northeast North America 276 Southwest North America

13. Verknüpfungen: ANSI SQL-Verknüpfungen • Äußere Verknüpfung • Allgemeine Syntax tabelle [LEFT | RIGHT] OUTER JOIN • Die Angabe LEFT oder RIGHT legt fest, welche von beiden Tabellen die Wertedominanz erhalten soll, d.h. welche ihre Daten trotz fehlender Verknüpfung komplett in die Ergebnismenge übergeben soll. • Der nächste Schritt kann dann in vielen Fällen die Frage sein, welche Datensätze denn nun genau keinen Partner in der anderen Tabelle finden. Dies lässt sich mit Hilfe des IS [NOT] NULL-Operators einrichten.

14. Verknüpfungen: ANSI SQL-Verknüpfungen -- Alle Produkte und mögliche Lagerplätze SELECT p.ProductID AS [P-ID], piv.ProductID AS [PIV-ID], LocationID AS [Loc-ID], Shelf AS Regal, Bin AS Kasten FROM Production.Product AS p LEFT OUTER JOIN Production.ProductInventory AS piv ON p.ProductID = piv.ProductID ORDER BY piv.ProductID • Äußere Verknüpfung P-ID PIV-ID Loc-ID Regal Kasten ----------- ----------- ------ ---------- ------ 717 NULL NULLNULLNULL 718 NULL NULLNULLNULL ... 1 11 A 1 1 1 6 B 5

15. Verknüpfungen: ANSI SQL-Verknüpfungen -- Verkaufsgebiete mit verschiedenen Verkäufern (Rohdaten) SELECT sth1.TerritoryID AS [Terr-ID 1], sth2.TerritoryID AS [Terr-ID 2], sth1.SalesPersonID AS [P-ID 1], sth2.SalesPersonID AS [P-ID 2] FROM Sales.SalesTerritoryHistory AS sth1 INNER JOIN Sales.SalesTerritoryHistory AS sth2 ON sth1.TerritoryID = sth2.TerritoryID WHERE sth1.SalesPersonID != sth2.SalesPersonID ORDER BY sth1.TerritoryID • Selbstverknüpfung • Bei der Selbstverknüpfung verknüpft man die gleichen Tabelle mit Hilfe einer oder mehrere Spalten, die durch einen Aliasnamen unterschieden werden, miteinander und fügt einen Filter hinzu, der auf unterschiedliche Werte bei gleichem Schlüssel überprüfen soll.

16. Verknüpfungen: ANSI SQL-Verknüpfungen Terr-ID 1 Terr-ID 2 P-ID 1 P-ID 2 ----------- ----------- ----------- ----------- 1 1 283 280 1 1 287 280 1 1 280 287 1 1 283 287 1 1 280 283 1 1 287 283 ... 4 4 281 276 4 4 276 281 (18 Zeile(n) betroffen) • Selbstverknüpfung

17. Verknüpfungen: ANSI SQL-Verknüpfungen -- Verkaufsgebiete mit verschiedenen Verkäufern -- (ohne Duplikate) SELECT DISTINCT sth1.TerritoryID AS [Terr-ID 1], sth1.SalesPersonID AS [P-ID 1] FROM Sales.SalesTerritoryHistory AS sth1 INNER JOIN Sales.SalesTerritoryHistory AS sth2 ON sth1.TerritoryID = sth2.TerritoryID WHERE sth1.SalesPersonID != sth2.SalesPersonID ORDER BY sth1.TerritoryID • Selbstverknüpfung • Für diese endgültige Lösung nun wählt man nur die Daten einer der beiden Tabellenkopien aus und setzt noch ein DISTINCT vor die Spaltenliste, um die Duplikate auszublenden.

18. Verknüpfungen: ANSI SQL-Verknüpfungen Terr-ID 1 P-ID 1 ----------- ----------- 1 280 1 283 1 287 … 4 276 4 281 (12 Zeile(n) betroffen) • Selbstverknüpfung • Schließlich erhält man nur noch die Gebiete mit mehreren Verkäufern.

19.  Fragen...

20.  Modul-Inhalt Verknüpfungen Unterabfragen Verzweigungen

21. Unterabfragen: Prinzip • Unterabfragen sind eigenständige Abfragen, • die an Stelle von anderen Ausdrücken nach unterschiedlichen Operatoren, • innerhalb von FROM und sogar • innerhalb der Spaltenliste erscheinen können. • Varianten • Einfache Unterabfragen • Spaltenunterabfragen • Abgeleitete Tabellen • Korrelierte Unterabfragen

22. Unterabfragen: Einfache Unterabfrage • Als einfache Unterabfrage kann man solche bezeichnen, die einen einzelnen Wert oder eine ganze Werteliste zurückliefern und die daher innerhalb der WHERE-Klausel eingesetzt werden können. • Unterabfragen eignen sich also dazu, einzelne Werte, wie sie bspw. von einer Aggregatfunktion geliefert werden, direkt in die umschließende Abfrage hinein zu übermitteln. -- Produkte teurer als Durchschnitt SELECT ProductID, Name, ListPrice FROM Production.Product WHERE ListPrice > (SELECT AVG(ListPrice) AS Schnitt FROM Production.Product) ORDER BY ListPrice ASC -- Durchschnittspreis (= 438,6662) SELECT AVG(ListPrice) AS Schnitt FROM Production.Product

23. Unterabfragen: Einfache Unterabfrage • Der Operator [NOT] IN kann nicht nur mit einem einzigen Wert umgehen, sondern stattdessen mit einer ganzen Werteliste. • Man unterscheidet daher: • Unterabfragen mit einem Wert • Unterabfragen mit einer Werteliste

24. Unterabfragen: Spaltenunterabfrage • Eine Spaltenunterabfrage ist eine Unterabfrage, die innerhalb der Spaltenliste erscheint. • Mit dieser Technik lassen sich zusätzliche Werte in einer Spalte ausgeben, • die entweder mit anderen Daten korrelieren (korrelierte Spaltenunterabfragen) oder • die ganz einfach eine Übersichtsabfrage bilden. -- Ausgangssituation -- Anzahl von Männern und Frauen in Belegschaft SELECT Gender AS Geschlecht, COUNT(*) AS Anzahl FROM HumanResources.Employee GROUP BY Gender Geschlecht Anzahl ---------- ----------- F 84 M 206 (2 Zeile(n) betroffen)

25. Unterabfragen: Spaltenunterabfrage Anzahl M Anzahl F ----------- ----------- 206 84 -- Anzahl von Männern und Frauen in Belegschaft SELECT DISTINCT (SELECT COUNT(*) FROM HumanResources.Employee WHERE Gender = 'M') AS [Anzahl M], (SELECT COUNT(*) FROM HumanResources.Employee WHERE Gender = 'F') AS [Anzahl F] FROM HumanResources.Employee

26. Unterabfragen: Abgeleitete Tabelle • Bei einer abgeleiteten Tabelle befindet sich die Unterabfrage innerhalb der FROM-Klausel und stellt quasi die lokale Tabelle dar, welche die äußere Abfrage abfragt • Die bereits in den zu Grunde liegenden Tabellen vorhandenen Spaltennamen bzw. die optional vorhandenen Aliasnamen bilden von dieser lokalen temporären Tabellen die neuen Spaltennamen, welche in der äußeren Abfrage referenziert werden können. • Jede dieser abgeleiteten Tabellen benötigt einen Aliasnamen, der ganz einfach an die Unterabfrage angeschlossen wird.

27. Unterabfragen: Abgeleitete Tabelle -- Liste aller weiblichen Angestellten SELECT Name, Phone FROM (SELECT FirstName + ' ' + LastName AS Name, Phone, Gender FROM HumanResources.Employee AS emp INNER JOIN Person.Contact AS c ON emp.ContactID = c.ContactID) AS emp WHERE Gender = 'F'

28. Unterabfragen: Abgeleitete Tabelle

29. Unterabfragen: Korrelierte Unterabfragen • Mit Hilfe einer korrelierten Unterabfrage lassen sich zwei Mengen zueinander in Beziehung setzen • und Werte der äußeren Abfrage dazu verwenden, in der inneren Abfrage als eingehende Parameter für weitere Werteermittlung genutzt zu werden. • Nächstes Beispiel: Gesucht ist für jede Kategorie das billigste Produkt. Dabei sucht die MIN-Funktion aber gerade nicht das billigste Produkt in der ganzen Tabelle, sondern vergleicht in einer WHERE-Klausel den in der äußeren Abfrage gerade verarbeiteten Subkategorienamen.

30. Unterabfragen: Korrelierte Unterabfragen

31. Unterabfragen: Korrelierte Spaltenunterabfragen • Die Technik der korrelierten Unterabfrage ist auch beim Einsatz von Spaltenunterabfragen möglich. • Dabei verwendet man wiederum den gerade in der äußeren Abfrage bearbeiteten Datensatz als Filter für die Unterabfrage. • Da nun allerdings nicht ein Filter wie einer gewöhnlichen korrelierten Unterabfrage verwendet wird, sondern eine neue Spalte in die Ergebnismenge gelangen soll, befindet sich die Unterabfrage in der Spaltenliste, weist allerdings die gleiche Korrelationstechnik wie eine gewöhnliche korrelierte Unterabfrage auf.

32. Unterabfragen: Korrelierte Spaltenunterabfragen

33. Unterabfragen: Operatoren für Spaltenunterabfragen • Der Operator ALL führt einen Vergleich zwischen einem skalaren Wert und einer einzelnen Spalten durch und ähnelt damit dem [NOT] IN-Operator. • Man erhält dann den Wert TRUE, wenn der verwendete Vergleich für alle einzelnen Paare des skalaren Ausdrucks und der in der Unterabfrage ermittelten Werteliste. Anderenfalls wird FALSE zurückgegeben. • Die allgemeine Syntax hat die Form: skalarerAusdruck { = | <> | != | > | >= | !> | < | <= | !< } ALL ( unterabfrage )

34. Unterabfragen: Operatoren für Spaltenunterabfragen • Der Operator [NOT] EXISTS prüft darauf, ob in einer Unterabfrage Zeilen vorhanden sind. • Die allgemeine Syntax lautet : [NOT] EXISTS unterabfrage. • Auch hier lassen sich andere Operatoren wie [NOT] IN oder auch =ANY verwenden.

35. Unterabfragen: Operatoren für Spaltenunterabfragen • Auch die beiden Operatoren SOME und ANY führen einen Vergleich durch, bei dem ein skalarer Wert mit den in einer Unterabfrage ermittelten Werten einer einzigen Spalte paarweise in Beziehung gesetzt wird. • Beide Operatoren geben dann den Wert TRUE zurück, sobald irgendein (any) Paar bzw. einige (some) Paare bei diesem paarweisen Vergleich den Wert TRUE liefern. • Die allgemeine Syntax lautet: skalarerAusdruck { = | < > | ! = | > | > = | ! > | < | < = | ! < } { SOME | ANY } ( unterabfrage)

36.  Fragen...

37.  Modul-Inhalt Verknüpfungen Unterabfragen Verzweigungen

38. Verzweigungen: CASE mit Selektor • Die erste Variante dieser Fallunterscheidung mit Hilfe des CASE-Ausdrucks wird Case mit Selektor genannt. • Der Selektor ist dabei der Ausdruck, welcher in mehreren Fällen, die auf exakte Gleichheit prüfen, untersucht wird. • Trotz der komplexen Syntax handelt es sich bei der CASE-Anweisung um einen Spaltenausdruck, d.h. um die Erzeugung eines einzigen Spaltenwerts. • Die allgemeine Syntax hat folgendes Aussehen: CASE Selektor WHEN Testausdruck THEN Ergebnisausdruck [ ...n ] [ ELSE Standardausdruck ] END

39. Verzweigungen: CASE ohne Selektor • Bei einer selektorlosen Case-Anweisung folgen gewöhnliche Vergleiche nach den WHEN-Schlüsselwörtern. • Dies ermöglicht es, gewöhnliche if-/elseif-/else-Konstruktionen von gängigen Programmiersprachen abzubilden und erhöht den Nutzen der CASE-Anweisung um ein Vielfaches. • Die allgemeine Syntax hat die Form: CASE WHEN Vergleichsausdruck THEN Ergebnisausdruck [ ...n ] [ ELSE Standardausdruck ] END

40.  Fragen...