Betrieb von Datenbanken Marco Skulschus & Marcus Wiederstein Komplexe Abfragen Lehrbuch, Kapitel 3

Betrieb von DatenbankenMarco Skulschus & Marcus Wiederstein

Komplexe Abfragen

Lehrbuch, Kapitel 3

Seminar-Inhalt

•Grundlagen•Einfache Abfragen•Komplexe Abfragen•Datenmanipulation•Grundlagen T-SQL•Programm-Module in der DB•Administration

Modul-Inhalt

1. Verknüpfungen

2. Unterabfragen

3. Verzweigungen

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.

Verknüpfungen: Prinzip

MA_Nr MA_Name MA_Leiter A_Nr

1 Ebenhof NULL 11 2 Hülzemann 1 11

A_Nr A_Name 11 Seminare

K_Nr K_Name 111 XY GmbH

KB_Nr MA_Nr K_Nr KB_Funktion

22 1 111 Kundenberater 23 21 111 Kundenberater

Mitarbeiter

Abteilung

Kundenbetreuung(Beziehungstabelle)

Kunde

1:n

1:n

1:n

Primär-schlüssel

Fremd-schlüssel

Rekursiver Fremd-Schlüssel auf die eigene Tabelle (Hierarchie)

Alternative: zusammengesetzter Primärschlüssel (aus zwei

Fremdschlüsseln)

Referentielle Integrität: nur Primärschlüssel-Werte der

Eltern-Tabelle können Werte von Fremdschlüsseln sein

Beispiel mit den Tabellen Abteilung, Mitarbeiter, Kundenbetreuung und Kunde.

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:


FirstName LastName HireDate------------- ------------- -----------Greg Alderson 1999-01-03Sean Alexander 1999-01-29

-- Angestellte mit KontaktdatenSELECT 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


ContactID HireDate----------- -----------------------1076 1998-02-07 00:00:00.0001173 1999-01-03 00:00:00.0001270 1999-01-29 00:00:00.0001050 1999-01-30 00:00:00.000

ContactID FirstName LastName----------- ------------------------ ---------------------1173 Greg Alderson1270 Sean Alexander1304 Sean Alexander

FirstName LastName HireDate------------------------ ------------------------ ----------------------------------Greg Alderson 1999-01-03 00:00:00.000Sean Alexander 1999-01-29 00:00:00.000

Verknüpfungen: Qualifizierte Spaltennamen

ContactID Title----------- --------------------------------------------------1005 Design Engineer1006 Design Engineer1035 Marketing Specialist1032 Marketing Specialist1088 Production Technician - WC201008 Tool Designer1025 Sales Representative

Title LastName ContactID-------- -------------------------- -----------Ms. Erickson 1005Mr. Goldberg 1006Ms. Galvin 1008Mr. Welcker 1010NULL Saraiva 1020NULL Brown 1030Ms. Williams 1035Mr. Ting 1088

Title Title ContactID LastName-------- -------------------------------------------------- ------------- ---------------------------Ms. Design Engineer 1005 EricksonMr. Design Engineer 1006 GoldbergMs. Marketing Specialist 1035 WilliamsMr. Production Technician - WC20 1088 TingMs. Tool Designer 1008 GalvinMr. Vice President of Sales 1010 WelckerMr. Sales Representative 1027 Mensa-AnnanMr. Pacific Sales Manager 1012 Abbas

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.

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

EmployeeID ShiftID ShiftID StartTime----------- ------- ------- ------------1 1 1 07:00:00.0001 1 2 15:00:00.0001 1 3 23:00:00.0002 1 1 07:00:00.0002 1 2 15:00:00.0002 1 3 23:00:00.000

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.


-- Verkäufer, Gebiete und LänderSELECT 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 America276 Southwest North America


Ä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.


-- Alle Produkte und mögliche LagerplätzeSELECT 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 NULL NULL NULL718 NULL NULL NULL NULL...1 1 1 A 11 1 6 B 5


-- 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.SalesPersonIDORDER 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.


Terr-ID 1 Terr-ID 2 P-ID 1 P-ID 2----------- ----------- ----------- -----------1 1 283 2801 1 287 2801 1 280 2871 1 283 2871 1 280 2831 1 287 283...4 4 281 2764 4 276 281(18 Zeile(n) betroffen)

Selbstverknüpfung


-- 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.SalesPersonIDORDER 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.


Terr-ID 1 P-ID 1----------- -----------1 2801 2831 287…4 2764 281(12 Zeile(n) betroffen)

Selbstverknüpfung

Schließlich erhält man nur noch die Gebiete mit mehreren Verkäufern.

Fragen...

Modul-Inhalt

1. Verknüpfungen

2. Unterabfragen

3. Verzweigungen

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

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 DurchschnittSELECT 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

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

SELECT ProductID, Name, ListPrice

FROM Production.Product

WHERE ListPrice > (SELECT AVG(ListPrice) AS Schnitt

FROM Production.Product)

ORDER BY ListPrice ASC

438,6662

SELECT ProductID, Name, ListPrice

FROM Production.Product

WHERE ProductID IN (SELECT DISTINCT ProductID

FROM Sales.SpecialOfferProduct)

680

706

707

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 BelegschaftSELECT Gender AS Geschlecht, COUNT(*) AS Anzahl FROM HumanResources.Employee GROUP BY Gender

Geschlecht Anzahl---------- -----------F 84M 206(2 Zeile(n) betroffen)

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

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.


-- Liste aller weiblichen AngestelltenSELECT 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'


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'

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.

Unterabfragen: Korrelierte Unterabfragen

SELECT psc.Name, a.Name, ListPrice

FROM Production.Product AS a INNER JOIN Production.ProductSubcategory AS psc

ON a.ProductSubcategoryID = psc.ProductSubcategoryID

WHERE ListPrice = (SELECT MIN(ListPrice)

FROM Production.Product AS b

WHERE a.ProductSubcategoryID = b.ProductSubcategoryID)

ORDER BY ListPrice

Tires and TubesBottles and Cages

Cleaners

2,294,997,95

Patch Kit/8 PatchesWater Bottle - 30 oz.Bike Wash - Dissolver

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.

Unterabfragen: Korrelierte Spaltenunterabfragen

367

Roberto TamburelloDavid BradleyJoLynn DobneySELECT EmployeeID,

(SELECT FirstName + ' ' + LastName

FROM Person.Contact AS c

WHERE c.ContactID = emp.ContactID) AS Name

FROM HumanResources.Employee AS emp

WHERE EmployeeID IN (SELECT DISTINCT ManagerID AS EmployeeID

FROM HumanResources.Employee

WHERE ManagerID IS NOT NULL)

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 )


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.


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)

Fragen...

Modul-Inhalt

1. Verknüpfungen

2. Unterabfragen

3. Verzweigungen

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

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-/else if-/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

Fragen...

Documents

Betrieb von Datenbanken Marco Skulschus & Marcus Wiederstein Komplexe Abfragen Lehrbuch, Kapitel 3