Teil 12 - Abfragen, Joins.key
Werbung
Einführung in die Wirtschaftsinformatik Teil 12 - SQL - Abfragen und Joins Wintersemester 2015/2016 Lehrstuhl für Wirtschaftsinformatik und Electronic Government Universität Potsdam Chair of Business Information Systems and Electronic Government University of Potsdam Univ.-Prof. Dr.–Ing. habil. Norbert Gronau Lehrstuhlinhaber | Chairholder August-Bebel-Str. 89 | 14482 Potsdam | Germany Tel Fax +49 331 977 3322 +49 331 977 3406 E-Mail [email protected] Web lswi.de Inhalt Alternative Ausdrücke Daten aus mehreren Tabellen Datenauswahl mit Joins Quelle: Alternative Ausdrücke Daten aus mehreren Tabellen Datenauswahl mit Joins Bedingte Ausdrücke Bereitstellen von IF-THEN-ELSE-Logik innerhalb der SQL-Anweisung Verwendung von zwei Methoden CASE-Ausdrücke CASE ausdruck WHEN comp_ausdruck1 THEN return_ausdruck1 [WHEN comp_ausdruck2 THEN return_ausdruck WHEN comp_ausdruckn THEN return_ausdruckn ELSE else_ausdruck] END Funktion DECODE DECODE(col|ausdruck, such1, ergebnis1 [, such2, ergebnis2,...,] [, default]) Anwendung von CASE-Ausdrücken Bedingte Abfragen in der Form von IF-THEN-ELSE-Anweisung SELECT name, position, gehalt, CASE position WHEN 'Konstrukteur' THEN gehalt*1.10 WHEN 'Monteur' THEN gehalt*1.15 WHEN 'Meister' THEN gehalt*1.05 ELSE gehalt END Gehaltserhöhung FROM mitarbeiter ORDER BY position; NAME ... Klein Plenk ... Ascheid Krause ... Thal Möhlmann ... Quelle: Greenberg 2002, S. 176 POSITION ... Konstrukteur Konstrukteur ... Meister Meister ... Monteur Monteur ... GEHALT ... 4100 4700 ... 3990 3680 ... 2400 2740 ... GEHALTSERHÖHUNG ... 4510 5170 ... 4189,5 3864 ... 2760 3151 ... Anwendung der Funktion DECODE Wirkung der Anweisung ähnlich der IF-THEN-ELSE-Anweisung SELECT name, position, gehalt, DECODE(position, 'Konstrukteur', gehalt*1.10, 'Monteur', gehalt*1.15, 'Meister', gehalt*1.05, gehalt) Gehaltserhöhung FROM mitarbeiter ORDER BY position; NAME ... Klein Plenk ... Ascheid Krause ... Thal Möhlmann ... Quelle: Greenberg 2002, S. 178 POSITION ... Konstrukteur Konstrukteur ... Meister Meister ... Monteur Monteur ... GEHALT ... 4100 4700 ... 3990 3680 ... 2400 2740 ... GEHALTSERHÖHUNG ... 4510 5170 ... 4189,5 3864 ... 2760 3151 ... Abfragereihen mit der Funktion DECODE Anzeige des Steuersatzes aller Angestellten der Abteilung 310T SELECT name, position, gehalt, DECODE (TRUNC(gehalt/1000, 0), 0, 0.00, 1, 0.20, 2, 0.25, 3, 0.30, 4, 0.35, 5, 0.40, 0.45) Steuersatz FROM mitarbeiter WHERE abt_nr = '310T'; NAME Kettler Klein Plenk Berg Quelle: Greenberg 2002, S. 179 POSITION Abteilungsleiter Konstrukteur Konstrukteur Sekretärin GEHALT 8080 4100 4000 2250 STEUERSATZ ,45 ,35 ,35 ,25 Alternative Ausdrücke Daten aus mehreren Tabellen Datenauswahl mit Joins Fähigkeiten der Anweisung SELECT n A n n Auswahl (Selektion) Projektion B C D A E B Join - Verbinden von Tabellen A B C D E A2 B2 C2 D C D E Kartesische Produkte Bildung eines kartesischen Produktes, wenn... ... alle Zeilen aus der ersten Tabelle mit allen Zeilen aus der zweiten Tabelle verknüpft werden ... eine Join-Bedingung fehlt ... eine Join-Bedingung ungültig ist Vermeidung kartesischer Produkte WHERE-Klausel enthält gültige Join-Bedingung Voraussetzung - Beziehung über Fremd- und Primärschlüssel bzw. Felder mit identischen Datentypen Daten abfragen - Verbinden von Tabellen Direkte Verknüpfung mehrerer Tabellen Verknüpfung erfolgt in Ausgabeliste durch kartesisches Produkt (Kreuzprodukt) Grundprinzip Verknüpfung jeder Zeile einer Tabelle mit jeder Zeile der anderen Tabelle(n) Beispiel auf den folgenden Seiten - kartesisches Produkt aus Tabellen mitarbeiter x abteilung --> 139 Zeilen x 30 Zeilen --> 4170 Zeilen --> 14 Spalten + 4 Spalten --> 18 Spalten Ergebnistabelle --> Ergebnis inhaltlich unsinnig --> Logischer Zusammenhang nur zwischen einzelnen Datenfeldern Das formale Vorgehen beim Kreuzprodukt entspricht dem beim Vektorprodukt in der Mathematik. Kreuzprodukt aus mehreren Tabellen - Struktur abteilung mitarbeiter ABT_NR PERS_NR NAME VORNAME ANREDE AKAD_TITEL LEITER POSITION GEBURTSTAG EINSTELLUNG GEHALT ABT_NR PROJ_NR KONTO BLZ x ABT_NAME BETR_TEIL BEMERKUNG = Ergebnis der Abfrage PERS_NR NAME VORNAME ANREDE AKAD_TITEL LEITER POSITION GEBURTSTAG EINSTELLUNG GEHALT ABT_NR PROJ_NR KONTO BLZ ABT_NR ABT_NAME BETR_TEIL BEMERKUNG Bei einem Kreuzprodukt werden alle Spalten der betreffenden Tabellen miteinander "gekreuzt". Kreuzprodukt aus mehreren Tabellen - Inhalt PERS_NR ... 4170 Zeilen ... NAME VORNAME ABT_NR PROJ_NR ABT_NR ABT_NAME BETR_TEIL 101001Büchner Edgar 260F 100VVorstandsstab GL 101001Büchner Edgar 260F 105CControlling GL 101001Büchner Edgar 260F 106QQualitätssicherung GL 101001Büchner Edgar 260F 107RInterne Revision GL ... ... ... 101001Büchner Edgar 260F 101001Büchner Edgar 260F ... ... ... ...... 260EElektro-Werkstatt 260FProduktion-Fertigung ...... ... PO PO ... 101002Martens Eugen 260F 100VVorstandsstab GL 101002Martens Eugen 260F 105CControlling GL ... ... 101002Martens ... Eugen ... ...... 260F 260FProduktion-Fertigung ... PO 18 Spalten Die umrahmten Bereiche beinhalten Ergebnisse des Kreuzproduktes mit logischem Zusammenhang Ein kartesisches Produkt mehrerer Tabellen erzeugt (in der Regel) keine sinnvolle Ergebnistabelle. Kreuzprodukt - Problemstellung Namensgleichheit Mehrdeutigkeit bei Namensgleichheit von Spalten Zuordnung Spaltenname zu Tabellenname in Abfrage notwendig A SELECT pers_nr, name, abt_nr, abt_name FROM mitarbeiter, abteilung; Spalte "abt_nr" - keine Zuordnung möglich B SELECT pers_nr, name, m.abt_nr, abt_name FROM mitarbeiter m, abteilung a; Zuordnung der Spalte "abt_nr" erfolgt aus Tabelle "mitarbeiter" C SELECT pers_nr, name, a.abt_nr, abt_name FROM mitarbeiter m, abteilung a; Zuordnung der Spalte "abt_nr" erfolgt aus Tabelle "abteilung" Ergebnistabellen (Ausgabe) für Beispiel B und C sind identisch Alternative Ausdrücke Daten aus mehreren Tabellen Datenauswahl mit Joins Arten von Joins Oracle-spezifische Joins SQL: 1999-kompatible Joins Equi-Join Cross-Joins Non-Equi-Join Natural-Joins Outer-Join USING-Klausel Self-Join Vollständige (zweiseitige) Outer-Joins Beliebige Join-Bedingungen für Outer-Joins Quelle: Greenberg 2002, S. 194 Verknüpfung von Tabellen - Grundlagen, Syntax Daten aus mehreren Tabellen abfragen mit Join Join-Bedingung in der WHERE-Klausel Tabellenname steht vor Spaltennamen durch Punkt getrennt Syntax: tabellenname.spaltenname SELECT tabelle1.spalte, tabelle2.spalte FROM tabelle1, tabelle2 WHERE tabelle1.spalte1 = tabelle2.spalte2; Kann - unterschiedliche Spaltennamen in den Tabellen Muss - dieselben Spaltennamen in verschiedenen Tabellen Quelle: Greenberg 2002, S. 195 Voraussetzungen für Verknüpfung von Tabellen Tabelle 1 -> Fremdschlüssel = Primärschlüssel <- Tabelle 2 Notwendig - Identische Eigenschaften (Datentyp) der Spalten Sinnvoll aber nicht zwingend - Identische Spaltennamen mitarbeiter PERS_NR NAME VORNAME ANREDE AKAD_TITEL LEITER POSITION GEBURTSTAG EINSTELLUNG GEHALT ABT_NR PROJ_NR KONTO BLZ abteilung ABT_NR ABT_NAME BETR_TEIL BEMERKUNG projekt PROJ_NR PROJ_NAME PROJ_LEITER PROJ_BEGINN PERSONALKOSTEN PROJEKTBESCHREIBUNG PROJ_ENDE Equi-Joins (Joins, Inner-Joins, Inner-Equi-Joins) Tabelle „mitarbeiter“ Tabelle „abteilung“ ABT_NR ABT_NAME 100V Vorstandsstab PERS_NR 101026 ABT_NR 105C 101025 210E 105C Controlling 101085 107R 106Q Qualitätssicherung 101017 260Z 107R Interne Revision 101032 230P 210E Einkauf 101130 550S 101050 101129 540P 101082 107R ..... ..... 220L Logistik 230P Produktionsplanung 240P Prozessplanung ... ... Beispiele für Übereinstimmung Fremdschlüssel Quelle: Elmasri 2002, S. 251 Primärschlüssel Aufruf von Datensätzen über Equi-Joins SELECT ma.pers_nr, ma.name, ma.abt_nr, abt.abt_nr, A B abt.abt_name FROM mitarbeiter ma, abteilung abt WHERE ma.abt_nr = abt.abt_nr; A PERS_NR NAME 101068 Adler 101093 Sonntag 101066 Grimm ... ... 101058 Müller 101038 Michalke ... ... 101045 Krause 101074 Kohl ABT_NR 100V 100V 100V ... 220L 230P ... 640S 640S B ABT_NR ABT_NAME 100V Vorstandsstab 100V Vorstandsstab 100V Vorstandsstab ... ... 220L Logistik 230P Produktionsplanung ... ... 640S Technik, Service, Support 640S Technik, Service, Support Die WHERE-Klausel verknüpft Tabellen so, dass nur sinnvolle und logische Ergebnisse entstehen. Quelle: Greenberg 2002, S. 197 Übersicht: Mehrdeutige Spaltennamen mitarbeiter.name abteilung.name Kennzeichnung Eindeutige Kennzeichnung von Spaltennamen bei Vorkommen in mehreren Tabellen --> Einsatz von Präfixen Name AS Mitarbeitername Geschwindigkeit Performance- Verbesserung durch Angabe von Präfixen Zuordnung Eindeutigkeit bei Namensgleichheit Unterscheidung bei identischem Namen in unterschiedlichen Tabellen über Spalten-Alias Eindeutige Kennzeichnung von Spaltennamen bei Vorkommen in mehreren Tabellen --> Einsatz von Präfixen Vereinfachung von Anfragen - Aliasnamen Kürzen Verlängern Kürzen langer und zusammengesetzter Tabellennamen Lesbarmachen kryptischer oder missverständlicher Tabellenkürzel Beispiele Beispiel ... FROM artikelstammdaten artikel ... FROM abt abteilung, ma mitarbeiter ... FROM abteilung a, mitarbeiter m ... FROM btl betriebsteil Erweiterung der Suchkriterien Einschränkungen von Datensätzen in der Abfrage Verwendung des Operators AND SELECT ma.pers_nr, ma.name, ma.abt_nr, ma.position, abt.abt_name FROM mitarbeiter ma, abteilung abt WHERE ma.abt_nr = abt.abt_nr AND position = 'Sekretärin'; pers_nr name abt_nr position abt_name 101048 Jörgensen 105C Sekretärin Controlling 101081 Wolff 106Q Sekretärin Qualitätssicherung 101022 Brauer 210E Sekretärin Einkauf ... ... ... ... ... 101118 Groß 340R Sekretärin Konstruktion Research Verknüpfung - mehrere Tabellen Zusätzliche Suchkriterien in WHERE-Klausel Verknüpfung von n Tabellen - mindestens n-1 Join-Bedingungen mitarbeiter NAME Johansson Schmiedel Martens Büchner Kettler Thal Remus Möhlmann Köhler Grauer ….. abteilung ABT_NR 410R 260D 310T 260F 410R 260F 250A 520G ABT_NR 100V 105C 106Q 107R 210E 220L 230P 240P 250A BETR_TEIL GL GL GL GL PO PO PO PO PO betriebsteil BETR_TEIL BEREICHSNAME GL Geschäftsleitung PO Produktion, Organisation KE Konstruktion, Entwicklung FR Finanzbuchhaltung, Rechnungswesen HR Personal IT Technik ... ... ….. ... WHERE m.abt_nr = a.abt_nr AND a.betr_teil = b.betr_teil; Non-Equi-Joins Join-Bedingung, die nicht den Gleich-Operator verwendet Beispiel: Ungleichheitszeichen (<> oder !=) gratifikation mitarbeiter NAME GEHALT 56000 35000 Reinhard Johansson ... ... 3610 3600 3480 Hofmann Fuchs Riekhoff ... Schuster Assmann Kohl Schuster ... 700 500 500 500 MINGEHALT 0 2501 3501 5001 7501 MAXGEHALT 2500 3500 5000 7500 19999 GRATIF 65 55 45 35 25 Prüfung der Werte aus "GEHALT" mit Spalten "MINGEHALT" und "MAXGEHALT" über WHERE-Klausel auf Ungleichheit Zuordnung des jeweiligen Werte aus Spalte "GRATIF", wenn Ungleichheitsbedingung erfüllt ist Quelle: Greenberg 2002, S. 202 Aufruf eines Non-Equi-Joins SELECT m.name, m.gehalt, g.gratif FROM mitarbeiter m, gratifikation g WHERE m.gehalt BETWEEN g.mingehalt AND g.maxgehalt; NAME GEHALT 500 500 Assmann Schuster ... 55 45 45 3480 3600 3610 Riekhoff Fuchs Hofmann Michalke Schulz-Niemeyer Büchner 65 65 ... ... ... GRATIF ... Anzeige von Datensätzen nur bei erfüllter Bedingung. ... 8440 8900 10430 25 25 25 Die Verwendung der Tabellen-Aliasnamen erfolgt oft aus Performance-Gründen. Outer-Joins Anzeige der in direkter Beziehung stehenden Datensätze und... Anzeige von Datensätzen, ohne direkten Bezug zu Datensätzen anderer Tabellen mitarbeiter abteilung ABT_NR ABT_NAME 100V Vorstandsstab 105C Controlling 106Q Qualitätssicherung .......... 530A Personal Leiharbeit 540P Personalentwicklung 550S Arbeitssicherheit ...... Abteilung 550S noch ohne Mitarbeiter Quelle: Greenberg 2002, S. 204 PERS_NR 101001 101002 101003 101004 101005 101006 101007 101008 NAME Büchner Martens Dost Fuchs Rösch Beyer Kettler Schneider …..….. 101134 Ludwig 101135 Fritzsche 101136 Bode ABT_NR 260D 210E 260D 510L 320M 310T 260K ….. 630E 630E 630E keiner Abteilung zugeordnet Outer-Joins - Syntax RIGHT: Aufruf aller Zeilen der rechten Tabelle nach ON SELECT tabelle1.spalte, tabelle2.spalte, ... FROM tabelle1 RIGHT OUTER JOIN tabelle2 ON tabelle1.spalte = tabelle2.spalte; LEFT: Aufruf aller Zeilen der linken Tabelle nach ON SELECT tabelle1.spalte, tabelle2.spalte, ... FROM tabelle1, LEFT OUTER JOIN tabelle2 ON tabelle1.spalte = tabelle2.spalte; Mit einem Right- bzw. Left-Outer-Join wird eine sogenannte rechte bzw. linke Inklusionsverknüpfung erstellt. Aufruf eines Outer-Joins SELECT m.name, m.abt_nr, a.abt_name FROM mitarbeiter m RIGHT OUTER JOIN abteilung a ON m.abt_nr = a.abt_nr; NAME Adler Sonntag Grimm Walther ABT_NR 100V 100V 100V 100V ... Vorstandsstab Vorstandsstab Vorstandsstab Vorstandsstab ... ... 260D Werkstatt Drehteilefert. 260D Werkstatt Drehteilefert. 260D Werkstatt Drehteilefert. Elektro-Werkstatt 260F Produktion-Fertigung Martens Tänzer Fuchs Büchner ... ABT_NAME ... ... keine Daten in Tabelle "mitarbeiter" Übersicht: Inner- und Outer-Joins Join Join Inner-Join (Equi-Join) Verknüpfung zwischen zwei Tabellen Rückgabe nur von übereinstimmenden Zeilen Left(Right)-Outer-Join Ergebnisrückgabe des Inner-Joins sowie Zeilen ohne Übereinstimmungen in der linken (bzw. rechten) Tabelle Join Full-Outer-Join Ergebnisrückgabe des Inner-Joins sowie eines Left-Outer-Joins und Right-Outer-Joins Verknüpfung zwischen zwei Tabellen - Rückgabe nur von übereinstimmenden Zeilen Quelle: Kemper 2000, S. 90 f. Full-Outer-Join Kombination von Left-Outer-Join und Right-Outer-Join Anzeige aller in den verknüpften Basistabellen vorhandenen Zeilen SELECT m.name, m.abt_nr, a.abt_name FROM mitarbeiter m FULL OUTER JOIN abteilung a ON m.abt_nr = a.abt_nr; NAME Büchner Martens Dost ... ABT_NR ABT_NAME 260F Produktion-Fertigung 260D Werkstatt Drehteilefertigung 210E Einkauf ...... Krajcsir Walker ... Brauer Quelle: Elmasri 2002, S.258 ...... 210E Einkauf Elektro-Werkstatt Self-Joins Tabelle wird mit sich selbst verbunden Zusätzliche Bezeichner/Alias-Namen in der FROM-Klausel zur Unterscheidung mitarbeiter (angestellte) PERS_NR NAME LEITER 101001 Büchner 101002 Martens 101003 Dost 101004 Fuchs 101005 Rösch 101006 Beyer 101007 Kettler 101008 Schneider 101009 Beyerke ….. mitarbeiter (leiter) ….. 101060 101060 101059 101060 101047 101070 101070 101038 PERS_NR NAME ….. ….. 101041 Büchner 101042 Schmiedel ….. ….. ….. ….. Ein Self-Join ermöglicht die Herstellung von Verbindungen innerhalb einer Tabelle mit einer einzigen Abfrage. Syntax für Self-Joins SELECT tab1.spalte, ..., tab2.spalte FROM tabelle1 tab1, tabelle1 tab2 WHERE tab1.spalte1 = tab2.spalte1; Wer ist der/die Vorgesetzte von Mitarbeiter Beyerke? SELECT angestellte.name || ' ist Untergebene/r von ' || leiter.name FROM mitarbeiter angestellte, mitarbeiter leiter WHERE angestellte.leiter = leiter.pers_nr AND angestellte.name = 'Beyerke'; ANGESTELLTE.NAME||'ISTUNTERGEBENE/RVON'||LEITER.NAME Beyerke ist Untergebene/r von Schmiedel Kreuzprodukt aus zwei Tabellen Entspricht kartesischem Produkt Verbinden mehrerer Tabellen ohne Verknüpfungskriterium Liefert meist sinnlose Ergebnisse Vorteil für Test von Datenbanken - Einfaches Erzeugen großer Datenmengen SELECT spalte1, spalte2, ... FROM tabelle1 CROSS JOIN tabelle2; entspricht der simplen Syntax SELECT spalte1, spalte2, ... FROM tabelle1, tabelle2; Natural-Join NATURAL JOIN-Klausel - basiert auf allen Spalten mit demselben Spaltentitel in beiden Tabellen Auswahl der Zeilen mit übereinstimmenden Werten in allen gemeinsamen Spalten Rückgabe einer Fehlermeldung bei Namensgleichheit aber unterschiedlichen Datentypen der Spalten Verknüpfung über NATURAL JOIN - automatisches Einbinden aller Spalten mit gleichen Namen und gleichem Datentyp Einsatz eines Präfix vor verknüpften Spaltennamen nicht zulässig - Fehlermeldung Quelle: Greenberg 2002, S. 212 Datensatzaufruf über Natural-Joins Alternative Formulierung über Equi-Join SELECT abt_nr, abt_name, betr_teil, bereichsname FROM abteilung NATURAL JOIN bereich; ABT_NR ABT_NAME BETR_TEIL BEREICHSNAME 420F Finanzbuchhaltung FR Finanzbuchhaltung, Rechnungswesen 410R Rechnungswesen FR FInanzbuchhaltung, Rechnungswesen 107R Interne Revision GL Geschäftsleitung 106Q Qualitätssicherung GL Geschäftsleitung ... ... ... ... 240P Prozessplanung PO Produktion, Organisation SELECT abt_nr, abt_name, a.betr_teil, bereichsname FROM abteilung a, bereich b WHERE a.betr_teil = b.betr_teil; Quelle: Greenberg 2002, S. 213 On-Klausel in Natural-Joins Erhöhung der Anweisungsverständlichkeit Wirkung der Join-Bedingung für den Natural-Join analog einem Equi-Join aller Spalten mit gleichem Namen On-Klausel dient der Festlegung von beliebigen Bedingungen oder der Angabe zu verknüpfender Spalten SELECT pers_nr, name, vorname, abt_name FROM mitarbeiter m JOIN abteilung a ON m.abt_nr = a.abt_nr; PERS_NR NAME VORNAME ABT_NAME 101068 Adler Jana Vorstandsstab 101093 Sonntag Christof Vorstandsstab 101066 Grimm Bernd Vorstandsstab 101086 Walther Stefanie Vorstandsstab ... ... .. ... 101108 Haustein Reiner Endmontage ... ... ... ... 101090 Kohl Melanie Technik Service, Support Quelle: Greenberg 2002, S. 217 Selektion, Projektion und Join - Fazit Zusammenfassung SELECT mitarbeiter.name, abteilung.name Projektion FROM mitarbeiter, abteilung Join* WHERE mitarbeiter.abt_nr = abteilung.abt_nr AND abteilung.abt_nr = XXX ** Selektion * Verbund (Kreuz-, Kartesisches Produkt) ** Konkreter Wert Kontrollfragen Wann werden CASE-Ausdrücke in SQL-Anweisungen verwendet? Nach der Normalisierung sind ursprüngliche Tabellen oftmals in mehrere neue Tabellen aufgeteilt. Mit welcher Funktion können die Daten daraus wieder miteinander verbunden werden? Unter welchen Bedingungen wird ein kartesisches Produkt erzeugt? Was bewirkt die Verwendung eines LEFT OUTER JOIN in einer Anweisung? Literatur Kemper, A./Eickler, A.: Datenbanksysteme; 6. Auflage, 2006, Oldenbourg Verlag Elmazri, R./Navathe, S. B.: Grundlagen von Datenbanksystemen; 3. Auflage, 2002, Addison-Wesley Greenberg, N./Nathan, P: Professioneller Einstieg in Oracle9i SQL - Band 1; 2002, Oracle Zum Nachlesen Kontakt Univ.-Prof. Dr.-Ing. Norbert Gronau Universität Potsdam August-Bebel-Str. 89 | 14482 Potsdam Germany Tel. +49 331 977 3322 E-Mail [email protected] Gronau, N., Gäbler, A.: Einführung in die Wirtschaftsinformatik GITO Verlag Berlin, 5. Auflage 2014 ISBN 978-3-95545-096-0
