SQL – Structured Query Language
Werbung
SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 SQL – Structured Query Language LV Datenorientierte Systemanalyse, WS 2014/15 Dr. Walter Ebner, <[email protected]> Institut für Informationswirtschaft Wirtschaftsuniversität Wien SQL – Structured Query Language SQL-DDL: Definition eines Datenbankschemas SQL-Query Language : Abfragen in SQL SQL-DML: Mutationen in SQL (Insert, Update, Delete) Datenorientierte Systemanalyse – SQL Allgemeines zu SQL 5 Dr. Walter Ebner, Institut für Informationswirtschaft 1 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Allgemeines zu SQL Entwicklungsziele: möglichst einfach für den Endbenutzer; möglichst deskriptiv, nicht prozedural Benennung: ursprünglich: SEQUEL – „structured english query language" Entwicklung: (im IBM-Labor) basiert auf relationaler Algebra und Tupelkalkül erste Vorschläge 1974; weiterentwickelt 1976-77 Implementierungen 1977 im System R (Prototyp eines relationalen DBS) Erste kommerzielle Implementierungen: Oracle: 1980 Datenorientierte Systemanalyse – SQL SQL: „structured query language" IBM: SQL/DS (1981), DB2 (1983) 6 Allgemeines zu SQL Standardisierung SQL-86 (1986): erster Standardisierungsversuch SQL-89 (1989): Lücken (z.B.: Fremdschlüssel) von SQL-86 z.T geschlossen; SQL-99 (1999): Weiterentwicklung mit objekt-orientierten Ansätzen; Trigger; Gliederung in Core-SQL (Sprachkern) und verschiedenen Packages, die aus Features bestehen. SQL-03 (2003): „Bug fixes“ und Konsolidierung von SQL-99, aber auch ein neuer Teil (SQL/XML) und neue Sprachelemente (MERGE-Anweisung, BIGINT-Typ, MULTISET-Typen, …). Weitere Standards 2006 (XML), 2008 (Instead of Trigger) und 2011 (derzeit aktuelle Version: SQL:2011 ISO/IEC 9075:2011) Sprachumfang: SQL deckt die Aspekte der DDL und der DML (Mutationen) und Query Language (Abfragen) ab. Datenorientierte Systemanalyse – SQL SQL-92 (1992): Umfassender Standard; drei Standardisierungslevels (entry, intermediate, full level); 7 Dr. Walter Ebner, Institut für Informationswirtschaft 2 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Allgemeines zu SQL Schnittstellen: Terminologie: Table (Tabelle) – Relation Row (Zeile) – Tupel Column (Spalte) – Attribut relationales Modell: SQL ist eine Umsetzung des relationalen Modells, wobei es neben den eigentlichen Tabellen (set of rows) auch uneigentliche Tabellen (multi-set of rows) gibt. Bei diesen können Tupel mehrfach auftreten, was dem Mengenbegriff widerspricht. Datenorientierte Systemanalyse – SQL SQL kann sowohl interaktiv, als auch eingebettet in einer konventionellen Programmiersprache (z.B. C, FORTRAN, …) verwendet werden. 8 SQL – Structured Query Language Allgemeines zu SQL SQL-DDL: Definition eines Datenbankschemas Datentypen und benutzerdefinierte Domänen Ein Beispiel für ein Datenbankschema Definition von Sichten SQL-Query Language: Abfragen in SQL SQL-DML: Mutationen in SQL Datenorientierte Systemanalyse – SQL Definition von Basistabellen 9 Dr. Walter Ebner, Institut für Informationswirtschaft 3 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Definition eines Datenbankschemas (SQL-DDL) Datenbank Benutzersichten : Menge von Basistabellen : Views Übersicht: SQL-DDL (vereinfacht) Bedeutung TABLE TABLE DROP CREATE DROP CREATE DROP TABLE VIEW VIEW DOMAIN DOMAIN Definition einer Basistabelle Ändern einer Basistabelle (Hinzufügen von Attributen) Löschen einer Basistabelle Definition einer Sicht Löschen einer Sicht benutzerdefinierte „Domain“ anlegen benutzerdefinierte „Domain“ löschen Die CREATE TABLE Anweisung ist davon die wichtigste. Datenorientierte Systemanalyse – SQL Anweisungen CREATE ALTER 10 Definition von Basistabellen Definition einer Relation: CREATE TABLE table-name ({column-definition | table-constraint}[, {column-definition | table-constr.}…]); definiert einen Relationstyp und legt gleichzeitig eine entsprechende Relation (Instanz) an („Schema einer Relation“). Dr. Walter Ebner, Institut für Informationswirtschaft Datenorientierte Systemanalyse – SQL SQL kennt nicht den benannten Relationstyp Spaltendefinition: column-definition: column-name {data-type | domain-name} [column-constraint …] column-constraint: NOT NULL | UNIQUE | PRIMARY KEY | reference-constraint reference-constraint: REFERENCES table-name [(reference-column)] Constraints über mehrere Attribute: (table-constraint) UNIQUE (column-list) PRIMARY KEY (column-list) FOREIGN KEY (referencing-columns) REFERENCES table-name [(column-list)] 11 4 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Datentypen und benutzerdefinierte Domänen SQL bietet folgende (Basis-)Datentypen: (Auswahl) Datentyp Kurzform CHARACTER CHARACTER (n) CHARACTER VARYING (n) CHAR CHAR (n) ) VARCHAR (n) DECIMAL [(p,[,s])] NUMERIC [(p,[,s])] INTEGER SMALLINT DEC(p,s) DOUBLE PRECISION FLOAT (n) REAL DOUBLE INT BLOB CLOB (Oracle, DB2), TEXT (MySQL, PostgreSQL) XML Datenorientierte Systemanalyse – SQL Beschreibung Zeichen(ketten): Ein einzelnes Zeichen Kette fester Länge Kette variabler Länge Exakte Zahlen: mit Nachkommastellen dto. Ganzzahl kleine Ganzzahl Gleitkommazahlen: hohe Genauigkeit benutzerdefinierte Gen. geringere Genauigkeit Große-Datentypen:** Binary Large Object Character Large Object (sehr lange Zeichenketten) XML Dokumente *) n=integer, p, s ebenso. **) produktabhängig meinstens max. 2 GB 12 Datentypen und benutzerdefinierte Domänen Datentyp Kurzform DATE TIME TIMESTAMP INTERVAL f INTERVAL sf to ef f, sf, ef {YEAR, MONTH, DAY, HOUR, MINUTE, SECOND} („sf>ef“) In SQL können benutzerdefinierte Datentypen als Einschränkung der vorgegebenen Datentypen definiert werden. Beispiel 5-1: Prozentangaben sind Gleitkommawerte zwischen 0 und 100. CREATE DOMAIN ProzentAngabe AS REAL CONSTRAINT ProzAngabeConstraint CHECK ( VALUE >= 0.0 AND VALUE <= 100.0); Datenorientierte Systemanalyse – SQL Beschreibung Zeit, Datum, Zeitintervall: Datum (y,m,d) Zeitpunkt (h,min,sec) Zeitpunkt (y,m,d,h, min, sec) Zeitintervall 13 Dr. Walter Ebner, Institut für Informationswirtschaft 5 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Ein Beispiel für ein Datenbankschema CREATE TABLE (ANGNR NAME WOHNORT ABTNR angestellte INTEGER PRIMARY KEY, VARCHAR (30), VARCHAR (30), INTEGER); CREATE TABLE (PNR PNAME PFILIALE PLEITER projekt INTEGER PRIMARY KEY, VARCHAR (15), VARCHAR (30), INTEGER REFERENCES angestellte(ANGNR)); CREATE TABLE angpro (PNR INTEGER REFERENCES projekt (PNR), ANGNR INTEGER REFERENCES angestellte (ANGNR), PROZARBZEIT ProzentAngabe, PRIMARY KEY (PNR, ANGNR)). Datenorientierte Systemanalyse – SQL CREATE DOMAIN ProzentAngabe AS REAL CONSTRAINT ProzAngabeConstraint CHECK ( VALUE >= 0.0 AND VALUE <= 100.0); 14 Definition von Sichten Beispiel: CREATE VIEW angestellteAusKarlsruhe AS SELECT ANGNR, NAME, WOHNORT, ABTNR FROM angestellte WHERE WOHNORT = ‘Karlsruhe‘; Datenorientierte Systemanalyse – SQL CREATE VIEW table-name [(column-name [,column-name] …)] AS query expression 15 Dr. Walter Ebner, Institut für Informationswirtschaft 6 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 SQL – Structured Query Language Allgemeines zu SQL SQL-DDL: Definition eines Datenbankschemas SQL-Query Language: Abfragen in SQL Die Syntax der SELECT-Abfrage Abfragen mit Bedingungen Datenorientierte Systemanalyse – SQL eine Tabelle Spezielle Abfragen an eine Relation Aggregatfunktionen (COUNT, SUM, …) Bildung von Gruppen (GROUP) Verknüpfung von Relationen (Join) mehrere Tabellen Join-Operationen ab SQL-92 Verknüpfung von Relationen mit Subqueries Abfragen mit Existenz-Quantor Mengenoperationen SQL-DML: Mutationen in SQL (Insert, Update, Delete) 16 Abfragen in SQL (Query Language) angestellte ANGNR 3115 3207 2814 3190 2314 1324 1435 2412 2244 1237 3425 2454 NAME Meyer Müller Klein Maus Groß Schmitt Mayerlein Müller Schulz Krämer Meier Schuster (#=n) WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 projekt PNAME P-1 P-2 P-3 P-4 PNR 761235 770008 770114 770231 (#=k) PFILIALE Karlsruhe Karlsruhe Heidelberg Mannheim PLEITER 3115 3115 1324 2814 Datenorientierte Systemanalyse – SQL Tabellen zu den folgenden Beispielen: 17 Dr. Walter Ebner, Institut für Informationswirtschaft 7 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 angpro (#=m) ANGNR 3207 3115 3190 1435 3425 2244 1237 2814 2454 2814 1435 1237 2454 3425 2412 3190 2314 2244 3115 1324 PNR 761235 761235 761235 761235 761235 770008 770008 770008 770008 770114 770114 770114 770114 770114 770114 770231 770231 770231 770231 770231 PROZARBZEIT 100 50 50 40 50 20 40 70 40 30 60 60 60 50 100 50 100 80 50 100 Datenorientierte Systemanalyse – SQL Abfragen in SQL (Query Language) 18 Die Syntax der SELECT-Abfrage … … … Auswahl von Spalten (π) Relation(en)/Tabelle(n) Selektionsbedingung (σ) SELECT-Abfrage ::= SELECT [DISTINCT] derived column(s) FROM table(s) [WHERE search condition] [GROUP BY column(s) [HAVING search condition]] [ORDER BY column(s)]; Mengenoperationen: SELECT-Abfrage op SELECT-Abfrage op := {EXCEPT | INTERSECT | UNION} [ALL] Ab SQL-92 können SELECT-Abfragen außerdem durch explizite JoinOperationen verknüpft werden. Dr. Walter Ebner, Institut für Informationswirtschaft Datenorientierte Systemanalyse – SQL Die SELECT-Abfrage Grundform: SELECT FROM WHERE 19 8 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Abfragen mit Bedingungen () WHERE <search condition> Die search condition ist ein logischer Ausdruck, der - Vergleichsoperationen: =, <, >, <> oder !=, >=, <=, BETWEEN, LIKE, IN - Boolesche Operatoren: AND, OR, NOT (d.h. Bezugnahme auf eine Zeile, wie -Operation) Datenorientierte Systemanalyse – SQL folgendes enthalten kann: 20 Abfragen mit Bedingungen () 1. „Name und Abteilungsnummer aller Angestellten mit Wohnort Karlsruhe“ NAME, ABTNR Ergebnis: angestellte WOHNORT=‘Karlsruhe’; angestellte ANGNR 3115 3207 2814 3190 2314 1324 1435 2412 2244 1237 3425 2454 NAME Meyer Müller Klein Maus Groß Schmitt Mayerlein Müller Schulz Krämer Meier Schuster WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms NAME Meyer Maus Groß Müller ABTNR 35 30 35 32 ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 Datenorientierte Systemanalyse – SQL SELECT FROM WHERE 21 Dr. Walter Ebner, Institut für Informationswirtschaft 9 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Abfragen mit Bedingungen () 2. „Name und Abteilungsnummer aller Angestellten, die in Karlsruhe wohnen oder in Abteilung 30 arbeiten“ NAME ABTNR Ergebnis: NAME, ABTNR SELECT Meyer 35 Müller 30 FROM angestellte Maus 30 WHERE Groß 35 WOHNORT=‘Karlsruhe’ Müller 32 OR ABTNR=30; Meier 30 angestellte ANGNR 3115 3207 2814 3190 2314 1324 1435 2412 2244 1237 3425 2454 NAME Meyer Müller Klein Maus Groß Schmitt Mayerlein Müller Schulz Krämer Meier Schuster WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 Datenorientierte Systemanalyse – SQL Zusammengesetzte Suchbedingung 22 Abfragen mit Bedingungen () Abfragen mit Bereichsgrenzen 3. „Alle Angestellten (Nummer und Name) mit Nummer zwischen 1435 und 2314 (jeweils einschließlich)“ SELECT FROM WHERE angestellte ANGNR 3115 3207 2814 3190 2314 1324 1435 2412 2244 1237 3425 2454 ANGNR, NAME Ergebnis: angestellte ANGNR BETWEEN 1435 AND 2314; NAME Meyer Müller Klein Maus Groß Schmitt Mayerlein Müller Schulz Krämer Meier Schuster Dr. Walter Ebner, Institut für Informationswirtschaft WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms ANGNR 2314 1435 2244 NAME Groß Mayerlein Schulz ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 Datenorientierte Systemanalyse – SQL „ x BETWEEN a AND b“ entspricht „x >= a AND x <= b“ 23 10 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Abfragen mit Bedingungen () Abfragen mit Wertaufzählung 4. „Alle Angestellten-Nummern der Abteilungen 30 und 35 aus Karlsruhe und Mannheim“ ANGNR Ergebnis: SELECT ANGNR 3115 angestellte ABTNR IN (30, 35) AND WOHNORT IN (’Karlsruhe’, ’Mannheim’); angestellte ANGNR NAME WOHNORT 3115 Meyer Karlsruhe 3207 Müller Mannheim 2814 Klein Mannheim 3190 Maus Karlsruhe 2314 Groß Karlsruhe 1324 Schmitt Heidelberg 1435 Mayerlein Bruchsal 2412 Müller Karlsruhe 2244 Schulz / NOT INBruchsal Negation: NOT BETWEEN 1237 Krämer Ludwigshafen 3425 Meier Pforzheim 2454 Schuster Worms 3207 3190 2314 ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 Datenorientierte Systemanalyse – SQL FROM WHERE 24 Abfragen mit Bedingungen () Abfrage mit Teilstring-Suche (Wildcards) Das Zeichen "%" im Suchstring steht für eine beliebige Folge von Characters, "passt" also auf jeden String. SELECT FROM WHERE projekt PNAME P-1 P-2 P-3 P-4 Ergebnis: PNR, PFILIALE projekt PFILIALE LIKE ‘%ei%’; PNR 761235 770008 770114 770231 PFILIALE Karlsruhe Karlsruhe Heidelberg Mannheim PNR 770114 770231 PFILIALE Heidelberg Mannheim PLEITER 3115 3115 1324 2814 Bemerkungen: - "like", nicht "=", nach "=" steht ein Wert, z.B. "=‘Heidelberg’" - Groß/Kleinschreibung beachten - Anderes Beispiel: Telefonnummer in Telefonliste Dr. Walter Ebner, Institut für Informationswirtschaft Datenorientierte Systemanalyse – SQL 5. „Nummer und PFILIALE aller Projekte, deren P-Filialen in Städten liegen, deren Namen ein „ei“ enthalten.“ 25 11 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Abfragen mit Bedingungen () SELECT FROM WHERE angestellte ANGNR 3115 3207 2814 3190 2314 1324 1435 2412 2244 1237 3425 2454 ANGNR, NAME angestellte NAME LIKE ‘Me_er’; NAME Meyer Müller Klein Maus Groß Schmitt Mayerlein Müller Schulz Krämer Meier Schuster Ergebnis: ANGNR 3115 3425 WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms NAME Meyer Meier ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 Datenorientierte Systemanalyse – SQL Der Unterstrich "_" im Suchstring steht für genau 1 beliebiges Zeichen. 6. „Nummer und Name aller Angestellten, deren Namen mit ‘Me’ beginnen, mit ‘er’ aufhören und dazwischen nur einen einzelnen Buchstaben beinhalten.“ 26 Abfragen mit Bedingungen () Kombination von „_“ und „%“ 7. „Alle Angestellten-Namen, deren Anfang wie ‘Maier’ klingt.“ angestellte ANGNR 3115 3207 2814 3190 2314 1324 1435 2412 2244 1237 3425 2454 NAME angestellte NAME LIKE ‘M_ _er%’; NAME Meyer Müller Klein Maus Groß Schmitt Mayerlein Müller Schulz Krämer Meier Schuster Ergebnis: WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms NAME Meyer Meyerlein Meier ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 Datenorientierte Systemanalyse – SQL SELECT FROM WHERE 27 Dr. Walter Ebner, Institut für Informationswirtschaft 12 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Abfragen mit Bedingungen () Retrieval mit Sortierung auf / absteigend ([ASC] / DESC) nach Wert einer Spalte oder mehrerer Spalten (zusammengesetztes Sortierkriterium, ASC - DESC beliebig mischbar) SELECT FROM WHERE ORDER BY ANGNR 3115 3207 2814 3190 2314 1324 1435 2412 2244 1237 3425 2454 ANGNR, NAME, angestellte ABTNR=30 NAME; -- NAME ASC nicht nötig da default NAME WOHNORT ABTNR Meyer Karlsruhe 35 Müller Mannheim 30 Klein Mannheim 32 Maus Karlsruhe 30 Groß Karlsruhe 35 Schmitt Heidelberg 35 Mayerlein Bruchsal 32 Müller Karlsruhe 32 Schulz Bruchsal 31 Krämer Ludwigshafen 31 Meier Pforzheim 30 Schuster Worms 31 Unsortiert: ANGNR 3207 3190 3425 Ergebnis: NAME Müller Maus Meier ANGNR 3190 3425 3207 WOHNORT Mannheim Karlsruhe Pforzheim ABTNR 30 30 30 NAME Maus Meier Müller Datenorientierte Systemanalyse – SQL 8. „Nummer und Name aller Angestellten, die zur Abteilung 30 gehören, aufsteigend sortiert nach dem Namen.“ 28 Spezielle Abfragen an eine Relation Auswahl von allen Zeilen 9. „Name aller Projekte“ PNAME projekt; Ergebnis: projekt PNAME P-1 P-2 P-3 P-4 PNR 761235 770008 770114 770231 PFILIALE Karlsruhe Karlsruhe Heidelberg Mannheim PLEITER 3115 3115 1324 2814 Datenorientierte Systemanalyse – SQL SELECT FROM 29 Dr. Walter Ebner, Institut für Informationswirtschaft 13 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Spezielle Abfragen an eine Relation Auswahl von allen Spalten SELECT FROM WHERE projekt PFILIALE=‘Karlsruhe‘; Ergebnis: projekt PNAME P-1 P-2 P-3 P-4 PNR 761235 770008 770114 770231 PFILIALE Karlsruhe Karlsruhe Heidelberg Mannheim PLEITER 3115 3115 1324 2814 Datenorientierte Systemanalyse – SQL 10. „alle Projekte in Karlsruhe“ 30 Spezielle Abfragen an eine Relation Keine Auswahl von Spalten und von Zeilen (Gesamte Relation ausgeben) 11. „Alle Daten der Relation angestellte“ angestellte; Ergebnis: vollständige Kopie von angestellte angestellte ANGNR 3115 3207 2814 3190 2314 1324 1435 2412 2244 1237 3425 2454 NAME Meyer Müller Klein Maus Groß Schmitt Mayerlein Müller Schulz Krämer Meier Schuster Dr. Walter Ebner, Institut für Informationswirtschaft WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 Datenorientierte Systemanalyse – SQL SELECT FROM 31 14 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Spezielle Abfragen an eine Relation Elimination von Duplikaten: 12. „Die Orte aller P-Filialen ohne Duplikate“ DISTINCT PFILIALE projekt; Ergebnis: eine einzelne Spalte ohne Duplikation. PFILIALE Karlsruhe Heidelberg Mannheim projekt PNAME P-1 P-2 P-3 P-4 PNR 761235 770008 770114 770231 PFILIALE Karlsruhe Karlsruhe Heidelberg Mannheim PLEITER 3115 3115 1324 2814 Datenorientierte Systemanalyse – SQL SELECT FROM 32 Spezielle Abfragen an eine Relation Rechnen mit Attributwerten 13. „(Zusammengehörige) Projekt- und Angestelltennummern mit relativer Arbeitszeit“ PNR, ANGNR, PROZARBZEIT 0.01 angpro; Ergebnis: angpro PNR 761235 761235 761235 761235 761235 770008 ... ANGNR 3207 3115 3190 1435 3425 2244 ... PROZARBZEIT 0.01 PROZARBZEIT 1.00 100 0.50 50 0.50 50 0.40 40 0.50 50 0.20 20 ... Datenorientierte Systemanalyse – SQL SELECT FROM 33 Dr. Walter Ebner, Institut für Informationswirtschaft 15 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Spezielle Abfragen an eine Relation Integration von Konstanten in die Antwort 14. „(Zusammengehörige) Projekt- und Angestelltennummern mit relativer Arbeitszeit; vor der Arbeitszeit soll stehen: Anteil =.“ PNR, ANGNR,‘Anteil=‘, PROZARBZEIT/100.0 angpro; Ergebnis: angpro PNR PNR 761235 761235 761235 761235 761235 761235 761235 761235 761235 761235 770008 770008 ... ... ANGNR ANGNR 3207 3207 3115 3115 3190 3190 1435 1435 3425 3425 2244 2244 ... ... Anteil = Anteil = Anteil = Anteil = Anteil = Anteil = Anteil = ... PROZARBZEIT/100 PROZARBZEIT PROZARBZEIT 100 1.00 100 500.50 50 500.50 50 40 0.40 40 50 0.50 50 20 0.20 ... 20 ... Datenorientierte Systemanalyse – SQL SELECT FROM 34 Aggregatfunktionen (COUNT, SUM, ...) Bedeutung Anzahl der Werte in Spalte (Name) bzw. Tabelle (*) Summe der Werte in Spalte Durchschnitt der Werte in Spalte Größter Wert in Spalte Kleinster Wert in Spalte 15. Funktion, auf Tabelle bezogen „Anzahl aller Angestellten“ SELECT FROM COUNT(*) angestellte; 12 Ergebnis: 12 Ohne Duplikation: SELECT COUNT(DISTINCT NAME) FROM angestellte; Ergebnis: 11 Dr. Walter Ebner, Institut für Informationswirtschaft ANGNR NAME WOHNORT ABTNR 3115 Meyer Karlsruhe 35 3207 Müller Mannheim 30 2814 Klein Mannheim 32 3190 Maus Karlsruhe 30 2314 Groß Karlsruhe 35 1324 Schmitt Heidelberg 35 11 1435 Mayerlein Bruchsal 32 2412 Müller Karlsruhe 32 2244 Schulz Bruchsal 31 1237 Krämer Ludwigshafen 31 3425 Meier Pforzheim 30 2454 Schuster Worms 31 Datenorientierte Systemanalyse – SQL Funktion COUNT SUM AVG MAX MIN 35 16 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Aggregatfunktionen (COUNT, SUM, ...) 16. Funktion, auf Spalte bezogen „Höchster vorkommender Arbeitszeitanteil“ angpro PNR 761235 761235 761235 761235 761235 770008 770008 770008 770008 770114 770114 770114 770114 770114 770114 770231 770231 770231 770231 770231 MAX(PROZARBZEIT) AS PROZARBEIT_MAX angpro; ANGNR 3207 3115 3190 1435 3425 2244 1237 2814 2454 2814 1435 1237 2454 3425 2412 3190 2314 2244 3115 1324 PROZARBZEIT 100 50 50 40 50 20 40 70 40 30 60 60 60 50 100 50 100 80 50 100 Qualifikation mit Aliasnamen Bemerkung: Das Schlüsselwort AS bei der Einführung von Aliasnamen ist optional. Ergebnis: PROZARBZEIT_MAX 100 Datenorientierte Systemanalyse – SQL SELECT FROM 36 Aggregatfunktionen (COUNT, SUM, ...) 17. Funktion, auf Spalte bezogen, mit zusätzlicher Selektion angpro PNR 761235 761235 761235 761235 761235 770008 770008 770008 770008 770114 770114 770114 770114 770114 770114 770231 770231 770231 770231 770231 ANGNR 3207 3115 3190 1435 3425 2244 1237 2814 2454 2814 1435 1237 2454 3425 2412 3190 2314 2244 3115 1324 PROZARBZEIT 100 50 50 40 50 20 40 70 40 30 60 60 60 50 100 50 100 80 50 100 Dr. Walter Ebner, Institut für Informationswirtschaft Ergebnis: PROZARBZEIT_MAX_761235 100 Datenorientierte Systemanalyse – SQL „Höchster Arbeitszeitanteil am Projekt Nr. 761235“ SELECT MAX(PROZARBZEIT) AS PROZARBEIT_MAX_761235 FROM angpro WHERE PNR = 761235; 37 17 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Aggregatfunktionen (COUNT, SUM, ...) 18. Funktion, auf Spalte bezogen, mit zusätzlicher Selektion „Gesamtarbeitsanteil von Projekt Nr. 770008“ angpro PNR 761235 761235 761235 761235 761235 770008 770008 770008 770008 770114 770114 770114 770114 770114 770114 770231 770231 770231 770231 770231 SUM (PROZARBZEIT) AS PROZARBEIT_SUM_770008 angpro PNR = 770008; ANGNR 3207 3115 3190 1435 3425 2244 1237 2814 2454 2814 1435 1237 2454 3425 2412 3190 2314 2244 3115 1324 PROZARBZEIT 100 50 50 40 50 20 40 70 40 30 60 60 60 50 100 50 100 80 50 100 Ergebnis: PROZARBZEIT_SUM_770008 170 170 Datenorientierte Systemanalyse – SQL SELECT FROM WHERE 38 Bildung von Gruppen (GROUP BY) SELECT PNR, SUM(PROZARBZEIT) AS PROZARBEIT_PROJEKT PNR ANGNR PROZARBZEIT FROM angpro 770008 1237 40 761235 3207 100 GROUP BY PNR; 770114 1237 60 761235 3115 50 Gruppe 761235 Ergebnis: PNR 761235 770008 770114 770231 PROZARBZEITPROJE KT 290 170 360 380 Gruppe 770008 Gruppe 770114 Gruppe 770231 Dr. Walter Ebner, Institut für Informationswirtschaft 770231 761235 770114 761235 761235 770231 770008 770008 770231 770008 770114 770008 770008 770114 770114 770008 770114 770114 770231 770114 761235 770114 770231 761235 770231 761235 770231 770114 770231 761235 770231 1324 3190 1435 1435 3425 2244 2244 1237 2314 2814 2412 2454 2454 2814 2454 1435 2814 1237 2814 2454 3115 3425 3115 2412 3190 3190 2314 3207 2244 3425 3115 3425 1324 100 50 60 40 40 50 80 20 20 40 100 70 100 40 40 30 60 70 60 30 60 50 50 100 50 50 100 100 80 50 50 100 SUM 290 SUM 170 SUM 360 Datenorientierte Systemanalyse – SQL Zusammenfassung von Zeilen • mit demselben Wert in einer oder mehreren vorgegebenen Spalten • zum Zweck der Anwendung einer Standardfunktion auf diese Gruppe o.ä. 19. „Gib den Gesamtarbeitszeitanteil jedes Projekts an. SUM 380 39 18 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Bildung von Gruppen (GROUP BY) Auswahl von Gruppen mit HAVING-Bedingung: (WHERE für Auswahl einzelner Zeilen) SELECT FROM PNR HAVING MAX(PROZARBZEIT) =100; angpro GROUP BY PNR PNR 761235 761235 761235 761235 761235 770008 770008 770008 770008 770114 770114 770114 770114 770114 770114 770231 770231 770231 770231 770231 Gruppe 761235 Gruppe 770008 Ergebnis: PNR 761235 770114 770231 Gruppe 770114 Gruppe 770231 ANGNR 3207 3115 3190 1435 3425 2244 1237 2814 2454 2814 1435 1237 2454 3425 2412 3190 2314 2244 3115 1324 PROZARBZEIT 100 50 50 40 50 20 40 70 40 30 60 60 60 50 100 50 100 80 50 100 MAX 100 MAX 70 MAX 100 MAX 100 Datenorientierte Systemanalyse – SQL 20. „Gib die Projekte an, in denen der maximale Arbeitszeitanteil 100% erreicht.“ 40 Bildung von Gruppen (GROUP BY) Ergebnis: PNR 761235 770114 770231 Gruppe 761235 Gruppe 770008 Gruppe 770114 Gruppe 770231 PNR 761235 761235 761235 761235 761235 770008 770008 770008 770008 770114 770114 770114 770114 770114 770114 770231 770231 770231 770231 770231 Dr. Walter Ebner, Institut für Informationswirtschaft ANGNR 3207 3115 3190 1435 3425 2244 1237 2814 2454 2814 1435 1237 2454 3425 2412 3190 2314 2244 3115 1324 PROZARBZEIT 100 50 COUNT 50 540 50 20 40 COUNT 4 70 40 30 60 60 COUNT 6 60 50 100 50 100 COUNT 80 5 50 100 Datenorientierte Systemanalyse – SQL 21. „Gib alle Projekte an, an denen mind. 5 Angestellte beteiligt sind.“ PNR SELECT FROM angpro GROUP BY PNR HAVING COUNT (DISTINCT ANGNR)>=5; 41 19 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Mehrere Tabellen Verknüpfung von Relationen (Join) Ergebnis: siehe Anhang 1 Join mit Joinbedingung 23. „Alle Projekt-Nummern mit den Namen derjenigen Angestellten, die zu 50% mitarbeiten.“ SELECT FROM WHERE PNR, NAME angestellte, angpro angestellte.ANGNR=angpro.ANGNR AND PROZARBZEIT=50; Ergebnis: siehe Anhang 2 Datenorientierte Systemanalyse – SQL Abfragen mit Join („Natural Join“) 22. „Alle Projekte (PNR) mit den Informationen der zugehörigen Angestellten.“ SELECT PNR, angestellte.ANGNR, NAME, WOHNORT, ABTNR, PROZARBZEIT Qualifikation mit qualifiziertem Namen angestellte, angpro FROM WHERE angestellte.ANGNR=angpro.ANGNR; 42 Verknüpfung von Relationen (Join) Ergebnis: siehe Anhang 3 Join mit 3 Relationen 25. „Gib für jeden Angestellten seinen Namen, sowie die Projekt-Filialen der Projekte an, an denen er mitarbeitet.“ SELECT FROM WHERE NAME, PFILIALE angestellte, angpro, projekt angestellte.ANGNR=angpro.ANGNR AND angpro.PNR=projekt.PNR; Datenorientierte Systemanalyse – SQL Abfragen mit Join (Spezialfall der Joinbedingung: -Join) 24. „Gib für jedes Projekt die Projektnummer und die Namen aller Angestellten an, die dort nicht Projektleiter sind.“ (Angestellter muss nicht an Projekt mitarbeiten) SELECT PNR, NAME angestellte, projekt FROM WHERE ANGNR <> PLEITER; Ergebnis: siehe Anhang 4 43 Dr. Walter Ebner, Institut für Informationswirtschaft 20 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Verknüpfung von Relationen (Join) SELECT FROM WHERE ANGNR 3115 3207 2814 a.ANGNR ANR, b.ANGNR BNR Qualifikation mit Aliasnamen angestellte a, angestellte b a.WOHNORT=b.WOHNORT AND a.ANGNR < b.ANGNR; NAME Meyer Müller Klein a ANGNR NAME 3115 Meyer 3115 Meyer Meyer 3115 Müller 3207 Müller 3207 3207 Müller Klein 2814 Klein 2814 Klein 2814 WOHNORT Karlsruhe Mannheim Mannheim ABTNR 35 30 32 (kartesisches Produkt) b WOHNORT ABTNR ANGNR Karlsruhe 35 3115 Karlsruhe 35 3207 Karlsruhe 35 2814 Mannheim 30 3115 Mannheim 30 3207 Mannheim 30 2814 Mannheim 32 3115 Mannheim 32 3207 Mannheim 32 2814 NAME Meyer Müller Klein Meyer Müller Klein Meyer Müller Klein ANGNR 3115 3207 2814 NAME Meyer Müller Klein WOHNORT ABTNR Karlsruhe 35 Mannheim 30 Mannheim 32 Karlsruhe 35 Mannheim 30 Mannheim 32 Karlsruhe 35 Mannheim 30 Mannheim 32 WOHNORT Karlsruhe Mannheim Mannheim ABTNR 35 30 32 Ergebnis: ANR 2814 BNR 3207 Datenorientierte Systemanalyse – SQL Join mit derselben Relation Verwendung von Alias-Namen & Qualifikation 26. „Suche Paare von (unterschiedlichen) Angestellten(Ang-Nr), die in der selben Stadt wohnen.“ (Das Ergebnis sollte keine redundanten Angaben enthalten) 44 Join-Operationen ab SQL-92 Ab SQL-92 gibt es explizite Join-Operationen. Es können Basis-Tabellen bzw. Views oder durch einen Join-Ausdruck (im vollen Sprachumfang auch durch einen beliebigen Abfrage-Ausdruck) definierte Tabellen miteinander verbunden werden: Typischerweise wird ein Join-Ausdruck in der FROM-Klausel einer SELECT-Abfrage verwendet: SELECT … FROM table join-op table [join-spec] WHERE … Syntax einer Join-Operation: join-op := CROSS JOIN | [NATURAL] [INNER] JOIN | [NATURAL] {LEFT | RIGHT | FULL} [OUTER] JOIN | UNION JOIN Dr. Walter Ebner, Institut für Informationswirtschaft Datenorientierte Systemanalyse – SQL table join-op table [join-spec] 45 21 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Join-Operationen ab SQL-92 Kartesisches Produkt: table1 CROSS JOIN table2 berechnet das Kartesische Produkt und entspricht Natural Join: Die Verwendung des Schlüsselworts NATURAL führt zur Berechnung des Natural Join. Wenn nicht alle gemeinsamen Attribute verschmolzen werden sollen, kann mit join-spec := USING (column-list) auf die angegebenen Attribute eingeschränkt werden. In diesem Fall darf das Schlüsselwort NATURAL nicht angegeben werden. Datenorientierte Systemanalyse – SQL SELECT * FROM table1, table2 46 Join-Operationen ab SQL-92 Join mit Joinbedingung: Ein Join mit Joinbedingung wird mit INNER JOIN (oder nur JOIN) durchgeführt, wobei die Join-Bedingung mit angegeben wird (search condition entspricht der Bedingung, die nach WHERE in einer SELECT-Abfrage angegeben werden kann). Beim Join mit Joinbedingung kommt es zu keiner Verschmelzung gemeinsamer Attribute (wie bei der gleichnamigen Operation der relationalen Algebra). Outer-Join: Sämtliche Variationen des Outer-Join (FULL, LEFT, RIGHT) können formuliert werden. Dabei kann entweder ein – Natural-Join (NATURAL, wenn opt. USING-Klausel, muss Schlüsselwort NATURAL weggelassen werden) oder ein – Join mit Joinbedingung (ON-Klausel) durchgeführt werden. Das Schlüsselwort OUTER ist optional, sollte aber aus Gründen der Leserlichkeit benutzt werden. Dr. Walter Ebner, Institut für Informationswirtschaft Datenorientierte Systemanalyse – SQL join-spec := ON search condition 47 22 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Join-Operationen ab SQL-92 Union-Join: Der Union-Join kann mit Hilfe des Schlüsselworts UNION JOIN durchgeführt werden. Damit kann man Tabellen vereinigen, die gar nicht Tabelle werden ergänzt und mit NULL-Werten ausgefüllt. Bemerkung: Die expliziten Join-Operationen können zwar auch mit früheren SQLVersionen formuliert werden, allerdings z.T. nur sehr umständlich. Die neuen Schlüsselwörter dienen der verbesserten Lesbarkeit; insbesondere die WHERE-Klausel wird dadurch entlastet. Datenorientierte Systemanalyse – SQL vereinigungskompatibel sind. Die privaten Attribute der jeweils anderen 48 Join-Operationen ab SQL-92 Beispiel 5-3 (Umformulierung der Bsp. 22-26) SELECT PNR, angestellte.ANGNR, NAME, WOHNORT, ABTNR, PROZARBZEIT FROM angestellte, angpro WHERE angestellte.ANGNR=angpro.ANGNR; Ab SQL-92: SELECT * FROM angestellte NATURAL JOIN angpro; Datenorientierte Systemanalyse – SQL 27. (Beispiel 22, S.41, „Natural Join“) „Alle Projektmitarbeiter mit den zugehörigen Angestellteninfos.“ 49 Dr. Walter Ebner, Institut für Informationswirtschaft 23 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Join-Operationen ab SQL-92 Beispiel 5-3 (Umformulierung der Bsp. 22-26) SELECT PNR, NAME FROM angestellte, angpro WHERE angestellte.ANGNR=angpro.ANGNR AND PROZARBZEIT=50; Ab SQL-92: SELECT PNR, NAME FROM angestellte NATURAL JOIN angpro WHERE PROZARBZEIT = 50; oder SELECT PNR, NAME FROM angestellte INNER JOIN angpro ON (angestellte.ANGNR=angpro.ANGNR AND PROZARBZEIT=50); Datenorientierte Systemanalyse – SQL 28. (Beispiel 23, S.41, Join mit Joinbedingung) „Alle Projekt-Nummern mit den Namen derjenigen Angestellten, die zu 50% mitarbeiten.“ 50 Join-Operationen ab SQL-92 Beispiel 5-3 (Umformulierung der Bsp. 22-26) SELECT PNR, NAME FROM angestellte, projekt WHERE ANGNR <> PLEITER; Ab SQL-92: SELECT PNR, NAME FROM angestellte JOIN projekt ON (ANGNR <> PLEITER); Datenorientierte Systemanalyse – SQL 29. (Beispiel 24, S.42, Spezialfall der Joinbedingung: -Join) „Gib für jedes Projekt die Projektnummer und die Namen aller Angestellten an, die dort nicht Projektleiter sind.“ (Angestellter muss nicht an Projekt mitarbeiten) 51 Dr. Walter Ebner, Institut für Informationswirtschaft 24 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Join-Operationen ab SQL-92 Beispiel 5-3 (Umformulierung der Bsp. 22-26) SELECT NAME, PFILIALE FROM angestellte, angpro, projekt WHERE angestellte.ANGNR=angpro.ANGNR AND angpro.PNR=projekt.PNR; Ab SQL-92: SELECT NAME, PFILIALE FROM angestellte NATURAL JOIN angpro NATURAL JOIN projekt; Datenorientierte Systemanalyse – SQL 30. (Beispiel 25, S.42, Join mit 3 Relationen) „Gib für jeden Angestellten seinen Namen, sowie die Projekt-Filialen der Projekte an, an denen er mitarbeitet.“ 52 Join-Operationen ab SQL-92 Beispiel 5-3 (Umformulierung der Bsp. 22-26) SELECT a.ANGNR ANR, b.ANGNR BNR FROM angestellte a, angestellte b WHERE a.WOHNORT=b.WOHNORT AND a.ANGNR<b.ANGNR; Ab SQL-92 SELECT a.ANGNR, b.ANGNR FROM (angestellte a) JOIN (angestellte b) USING (WOHNORT) WHERE a.ANGNR < b.ANGNR; Datenorientierte Systemanalyse – SQL 31. (Beispiel 26, S.43, Join mit derselben Relation) „Suche Paare von (unterschiedlichen) Angestellten, die in der selben Stadt wohnen.“ (Das Ergebnis sollte keine redundanten Angaben enthalten) 53 Dr. Walter Ebner, Institut für Informationswirtschaft 25 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Die Varianten des OUTER Joins 1. LEFT (OUTER) JOIN Ident mit einem natural join. Jedoch werden jene Zeile aus der Linken Tabelle, die beim natural Join nicht zum Zug gekommen sind zum Ergebnis hinzugefügt. Fehlende Attribute werden mit NULL aufgefüllt. 2. RIGHT (OUTER) JOIN So wie LEFT JOIN. Jedoch werden die Zeilen aus der Rechten Tabelle, die beim natural Join nicht zum Zug gekommen sind zum Ergebnis hinzugefügt. 3. FULL (OUTER) JOIN Kombination aus Left und Right Join. Zuerst wird ein natural Join gemacht. Dann werden jene Zeilen aus der rechten und der linke Tabelle, die beim Join nicht zum Zug gekommen sind zum Ergebnis hinzugefügt. Datenorientierte Systemanalyse – SQL Kommen NULL-Werte bei Fremdschlüsseln vor (bzw. auch bei m:n Beziehungen, wenn nicht alle Entities eine Beziehung haben), können die folgenden Join Varianten sinnvoll sein. 54 Dabei handelt es sich um geschachtelte Queries. 32. „Namen aller Angestellten, die mit 100 % ihrer Arbeitszeit an einem Projekt mitarbeiten.“ 1. Schritt: Bestimme aus angpro alle ANGNR, die zu PROZARBZEIT=100 gehören (Query a) SELECT ANGNR FROM angpro WHERE PROZARBZEIT=100; angpro PNR 761235 761235 761235 761235 761235 770008 770008 770008 770008 770114 770114 770114 770114 770114 770114 770231 770231 770231 770231 770231 (#=m) ANGNR 3207 3115 3190 1435 3425 2244 1237 2814 2454 2814 1435 1237 2454 3425 2412 3190 2314 2244 3115 1324 PROZARBZEIT 100 50 50 40 50 20 40 70 40 30 60 60 60 50 100 50 100 80 50 100 Dr. Walter Ebner, Institut für Informationswirtschaft Ergebnis: ANGNR 3207 2412 2314 1324 Datenorientierte Systemanalyse – SQL Verknüpfung von Relationen mit Subqueries 55 26 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Verknüpfung von Relationen mit Subqueries 2. Schritt: Wähle in angestellte alle Tupel aus, deren ANGNR in der oben gegebenen Ergebnismenge liegt: (Query b) NAME angestellte ANGNR IN (3207, 2412, 2314,1324); angestellte (#=n) ANGNR NAME WOHNORT ABTNR 3115 Meyer Karlsruhe 35 3207 Müller Mannheim 30 2814 Klein Mannheim 32 3190 Maus Karlsruhe 30 2314 Groß Karlsruhe 35 1324 Schmitt Heidelberg 35 1435 Mayerlein Bruchsal 32 2412 Müller Karlsruhe 32 2244 Schulz Bruchsal 31 1237 Krämer Ludwigshafen 31 3425 Meier Pforzheim 30 2454 Schuster Worms 31 Ergebnis: NAME Müller Groß Schmitt Müller Datenorientierte Systemanalyse – SQL SELECT FROM WHERE 56 Verknüpfung von Relationen mit Subqueries {äußere Query b} SELECT NAME FROM angestellte WHERE ANGNR IN {Subquery a} (SELECT ANGNR FROM angpro WHERE PROZARBZEIT = 100); Vorgehensweise des Systems: a) Abarbeitung Subquery (a) b) Übergabe Ergebnis an übergeordnete (äußere) Query (b) c) Abarbeitung Query b mit dem übergebenen Ergebnis Datenorientierte Systemanalyse – SQL Zusammenfassung zu geschachtelter Query: 57 Dr. Walter Ebner, Institut für Informationswirtschaft 27 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Verknüpfung von Relationen mit Subqueries 33. Äquivalente Abfrage mit Join: Ergebnis von 32. SELECT NAME FROM angestellte WHERE ANGNR IN (SELECT ANGNR FROM angpro WHERE PROZARBZEIT = 100); Vergleich 32. mit 33.: (vereinfachte Betrachtung!) Man betrachte Anzahl der notwendigen DB-Zugriffe! bei 32.: n+m bei 33.: nm Datenorientierte Systemanalyse – SQL SELECT NAME FROM angestellte, angpro WHERE PROZARBZEIT = 100 AND angpro.ANGNR = angestellte.ANGNR; 58 Verknüpfung von Relationen mit Subqueries b) in angpro: Auswahl ANGNR, deren PNR in der obigen Ergebnismenge liegen c) in angestellte: Auswahl NAMEn, deren ANGNR in der o.E. liegen c) SELECT NAME FROM angestellte WHERE ANGNR IN b) (SELECT ANGNR FROM angpro WHERE PNR IN a) (SELECT PNR FROM projekt WHERE PFILIALE =‘Karlsruhe‘)); Ergebnis: siehe Anhang 5 Dr. Walter Ebner, Institut für Informationswirtschaft Datenorientierte Systemanalyse – SQL Mehrfach geschachtelte Queries 34. „Namen aller Angestellten, die an mindestens einem Projekt in Karlsruhe mitarbeiten.“ Vorgehensweise: a) in projekt: Auswahl PNR der Projekte in Karlsruhe 59 28 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Verknüpfung von Relationen mit Subqueries SELECT angestellte.NAME FROM angestellte, angpro, projekt WHERE angestellte.ANGNR = angpro.ANGNR AND angpro.PNR=projekt.PNR AND projekt.PFILIALE=‘Karlsruhe‘; Datenorientierte Systemanalyse – SQL 35. Äquivalente Formulierung mit 2-fach Join: 60 Mengenoperationen Verknüpfung von Abfragen mit Mengenoperationen: Ergebnismengen müssen „vereinigungskompatibel“ sein (Namen und Wertebereiche korrespondierender Spalten müssen übereinstimmen). Operationen: Wird ALL verwendet, dann arbeiten die Operationen mit „uneigentlichen Tabellen“, sonst mit „eigentlichen Tabellen“ (keine Duplikate). 38. „Nummern aller Angestellten, die in Karlsruhe wohnen oder dort Leiter eines Projektes sind“ SELECT ANGNR FROM angestellte WHERE WOHNORT = ‘Karlsruhe‘ UNION SELECT PLEITER as ANGNR FROM projekt WHERE PFILIALE = ‘Karlsruhe‘; Ergebnis: siehe Anhang 6 Dr. Walter Ebner, Institut für Informationswirtschaft Datenorientierte Systemanalyse – SQL {UNION | INTERSECT | EXCEPT} [ALL] 61 29 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Mengenoperationen 39„Nummern aller Angestellten, die an mehr als einem Projekt arbeiten und SELECT ANGNR FROM angestellte WHERE WOHNORT = ‘Karlsruhe‘ INTERSECT SELECT ANGNR FROM angpro GROUP BY ANGNR HAVING COUNT (ANGNR) > 1; Ergebnis: siehe Anhang 7 Datenorientierte Systemanalyse – SQL in Karlsruhe wohnen.“ 62 Mengenoperationen 40. „Die Nummern aller Angestellten, deren Name mit ‘M‘ anfängt, die nicht Projektleiter sind.“ EXCEPT SELECT PLEITER as ANGNR FROM projekt; Ergebnis: siehe Anhang 8 Datenorientierte Systemanalyse – SQL SELECT ANGNR FROM angestellte WHERE NAME like ‘M%‘ 63 Dr. Walter Ebner, Institut für Informationswirtschaft 30 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 SQL – Structured Query Language Allgemeines zu SQL SQL- Query Language : Abfragen in SQL SQL-DML: Mutationen in SQL (Insert, Update, Delete) Datenorientierte Systemanalyse – SQL SQL-DLL: Definition eines Datenbankschemas 64 Die Mutationen Beispiel: UPDATE projekt SET PLEITER = 3207, PFILIALE = ’Mannheim’ WHERE PNR = 761235; DELETE DELETE FROM table [WHERE search condition]; Beispiel: DELETE FROM angpro WHERE ANGNR = 3190; Datenorientierte Systemanalyse – SQL UPDATE UPDATE table SET column = expression [, column = expression …] [WHERE search condition]; 65 Dr. Walter Ebner, Institut für Informationswirtschaft 31 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Die Mutationen INSERT INSERT INTO table [(column [, column …])] VALUES (constant [, constant …]); query; Beispiel: INSERT INTO angestellte (ANGNR, NAME, WOHNORT, ABTNR) VALUES (1717, ’Häberle’,’Stuttgart’, 30); Annahme: Es existiert eine Tabelle ‘angestellte_in_ka‘ mit dem gleichen Aufbau wie ‘angestellte‘: INSERT INTO angestellte_in_ka SELECT * FROM angestellte WHERE WOHNORT = ‘Karlsruhe‘; Datenorientierte Systemanalyse – SQL oder INSERT INTO table [(column [, column …])] 66 Datenorientierte Systemanalyse – SQL ENDE 67 Dr. Walter Ebner, Institut für Informationswirtschaft 32 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Anhang 1: Beispiel für „Natural Join“ FROM WHERE ANGNR 3115 2814 1324 PNR, angestellte.ANGNR, NAME, WOHNORT, ABTNR, PROZARBZEIT angestellte, angpro a.ANGNR=ap.ANGNR; NAME Meyer Klein Schmitt WOHNORT Karlsruhe Mannheim Heidelberg ABTNR 35 32 35 kartesisches Produkt PNR 761235 770008 770114 770231 770231 ANGNR NAME WOHNORT ABTNR PNR ANGNR PROZARBZEIT 3115 Meyer Karlsruhe 35 761235 3115 50 3115 Meyer Karlsruhe 35 770008 2814 70 35 3115 Meyer Karlsruhe 770114 2814 30 35 3115 Meyer Karlsruhe 770231 3115 50 3115 Meyer Karlsruhe 35 770231 1324 100 2814 Klein Mannheim 32 761235 3115 50 2814 Klein Mannheim 32 770008 2814 70 2814 Klein Mannheim 32 770114 2814 30 2814 Klein Mannheim 32 770231 3115 50 2814 Klein Mannheim 32 770231 1324 100 1324 Schmitt Heidelberg 35 761235 3115 50 1324 Schmitt Heidelberg 35 770008 2814 70 1324 Schmitt Heidelberg 35 770114 2814 30 1324 Schmitt Heidelberg 35 770231 3115 50 1324 Schmitt Heidelberg 35 770231 1324 100 ANGNR 3115 2814 2814 3115 1324 PROZARBZEIT 50 70 30 50 100 Ergebnis: PNR 761235 770231 770008 770114 770231 ANG NR 3115 3115 2814 2814 1324 NAME WOHNORT Meyer Meyer Klein Klein Schmitt Karlsruhe Karlsruhe Mannheim Mannheim Heidelberg Datenorientierte Systemanalyse – SQL SELECT ABT PROZARB NR ZEIT 35 50 35 50 32 70 32 30 35 100 68 Anhang 2: Beispiel für Join mit Joinbedingung ANGNR 3115 2814 1324 ANGNR 3115 3115 3115 3115 3115 2814 2814 2814 2814 2814 1324 1324 1324 1324 1324 PNR, NAME angestellte, angpro angestellte.ANGNR= angpro.ANGNR AND PROZARBZEIT=50; NAME Meyer Klein Schmitt NAME Meyer Meyer Meyer Meyer Meyer Klein Klein Klein Klein Klein Schmitt Schmitt Schmitt Schmitt Schmitt WOHNORT Karlsruhe Mannheim Heidelberg ABTNR 35 32 35 (kartesisches Produkt) WOHNORT ABTNR PNR Karlsruhe 35 761235 Karlsruhe 35 770008 Karlsruhe 35 770114 Karlsruhe 35 770231 Karlsruhe 35 770231 Mannheim 32 761235 Mannheim 32 770008 Mannheim 32 770114 Mannheim 32 770231 Mannheim 32 770231 Heidelberg 35 761235 Heidelberg 35 770008 Heidelberg 35 770114 Heidelberg 35 770231 Heidelberg 35 770231 ANGNR 3115 2814 2814 3115 1324 3115 2814 2814 3115 1324 3115 2814 2814 3115 1324 PNR 761235 770008 770114 770231 770231 ANGNR 3115 2814 2814 3115 1324 PROZARBZEIT 50 70 30 50 100 50 70 30 50 100 50 70 30 50 100 PROZARBZEIT 50 70 30 50 100 Ergebnis: PNR 761235 770231 NAME Meyer Meyer Datenorientierte Systemanalyse – SQL SELECT FROM WHERE 69 Dr. Walter Ebner, Institut für Informationswirtschaft 33 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Anhang 3: Beispiel für Abfragen mit Join PNR, NAME angestellte, projekt ANGNR <> PLEITER; WOHNORT Karlsruhe Mannheim Heidelberg ABTNR 35 32 35 PNAME P-1 P-2 P-3 P-4 PNR 761235 770008 770114 770231 ANGNR 3115 2814 1324 NAME Meyer Klein Schmitt ANGNR 3115 3115 3115 3115 2814 2814 2814 2814 1324 1324 1324 1324 NAME WOHNORT ABTNR PNAME PNR PFILIALE PLEITER Meyer Karlsruhe 35 p-1 761235 Karlsruhe 3115 Meyer Karlsruhe 35 p-2 770008 Karlsruhe 3115 Meyer Karlsruhe 35 p-3 770114 Heidelberg 1324 Meyer Karlsruhe 35 P-4 770231 Mannheim 2814 Klein Mannheim 32 p-1 761235 Karlsruhe 3115 Klein Mannheim 32 p-2 770008 Karlsruhe 3115 Klein Mannheim 32 p-3 770114 Heidelberg 1324 Klein Mannheim 32 P-4 770231 Mannheim 2814 Schmitt Heidelberg 35 p-1 761235 Karlsruhe 3115 Schmitt Heidelberg 35 p-2 770008 Karlsruhe 3115 Schmitt Heidelberg 35 p-3 770114 Heidelberg 1324 Schmitt Heidelberg 35 P-4 770231 Mannheim 2814 (kartesisches Produkt) PFILIALE Karlsruhe Karlsruhe Heidelberg Mannheim PLEITER 3115 3115 1324 2814 Ergebnis: PNR 770114 770231 761235 770008 770114 761235 770008 770231 NAME Meyer Meyer Klein Klein Klein Schmitt Schmitt Schmitt Datenorientierte Systemanalyse – SQL SELECT FROM WHERE 70 Anhang 4: Beispiel für Join mit 3 Relationen ANGNR 3115 2814 NAME Meyer Klein NAME, PFILIALE angestellte, angpro, projekt angestellte.ANGNR= angpro.ANGNR AND angpro.PNR= projekt.PNR; WOHNORT Karlsruhe Mannheim ANGNR NAME WOHNORT 3115 3115 2814 2814 Meyer Meyer Klein Klein Karlsruhe Karlsruhe Mannheim Mannheim ANGNR NAME WOHNORT 3115 3115 3115 3115 2814 2814 2814 2814 Meyer Meyer Meyer Meyer Klein Klein Klein Klein Karlsruhe Karlsruhe Karlsruhe Karlsruhe Mannheim Mannheim Mannheim Mannheim ABTN R 35 35 32 32 ABT NR 35 35 35 35 32 32 32 32 ABTNR 35 32 (kartesisches Produkt) PNR ANGNR 761235 770008 761235 770008 3115 2814 3115 2814 PNR ANGNR 761235 761235 770008 770008 761235 761235 770008 770008 3115 3115 2814 2814 3115 3115 2814 2814 PROZAR BZEIT 50 70 50 70 PNR 761235 770008 ANGNR PROZARBZEIT 3115 50 2814 70 (kartesische Produkt) PNAM PFILIAL PLEITE PNR E E R P-1 761235 Karlsruhe 3115 P-2 770008 Karlsruhe 3115 PROZAR PNAM PLEITE PNR PFILIALE BZEIT E R 50 p-1 761235 Karlsruhe 3115 50 p-2 770008 Karlsruhe 3115 70 p-1 761235 Karlsruhe 3115 70 p-2 770008 Karlsruhe 3115 50 p-1 761235 Karlsruhe 3115 50 p-2 770008 Karlsruhe 3115 70 p-1 761235 Karlsruhe 3115 70 p-2 770008 Karlsruhe 3115 Ergebnis: NAME PFILIALE Meyer Karlsruhe Klein Karlsruhe Datenorientierte Systemanalyse – SQL SELECT FROM WHERE 71 Dr. Walter Ebner, Institut für Informationswirtschaft 34 PI Datenorientierte Systemanalyse, WS 2014/15 Anhang 5: Mehrfach geschachtelte Subqueries Ergebnis: NAME SELECT NAME FROM angestellte WHERE ANGNR IN (SELECT ANGNR FROM angpro WHERE PNR IN (SELECT PNR FROM projekt WHERE PFILIALE =‘Karlsruhe‘)); projekt PNAME P-1 P-2 P-3 P-4 PNR 761235 770008 770114 770231 angestellte ANGNR 3115 3207 2814 3190 2314 1324 1435 2412 2244 1237 3425 2454 Meyer Müller Klein Maus Mayerlein Krämer Meier Schuster (#=k) PFILIALE PLEITER Karlsruhe 3115 Karlsruhe 3115 Heidelberg 1324 Mannheim 2814 NAME Meyer Müller Klein Maus Groß Schmitt Mayerlein Müller Schulz Krämer Meier Schuster WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms angpro PNR 761235 761235 761235 761235 761235 770008 770008 770008 770008 770114 770114 770114 770114 770114 770114 770231 770231 770231 770231 770231 (#=n) ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 ANGNR 3207 3115 3190 1435 3425 2244 1237 2814 2454 2814 1435 1237 2454 3425 2412 3190 2314 2244 3115 1324 (#=m) PROZARBZEIT 100 50 50 40 50 20 40 70 40 30 60 60 60 50 100 50 100 80 50 100 Datenorientierte Systemanalyse – SQL SQL – Structured Query Language 72 Anhang 6: Beispiel für Mengenoperationen (Union) Ergebnis: SELECT ANGNR FROM angestellte WHERE WOHNORT = ‘Karlsruhe‘ UNION SELECT PLEITER as ANGNR FROM projekt WHERE PFILIALE = ‘Karlsruhe‘; angestellte ANGNR NAME 3115 Meyer 3207 Müller 2814 Klein 3190 Maus 2314 Groß 1324 Schmitt 1435 Mayerlein 2412 Müller 2244 Schulz 1237 Krämer 3425 Meier 2454 Schuster WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 Zwischenergebnis: Dr. Walter Ebner, Institut für Informationswirtschaft ANGNR 3115 3190 2314 2412 projekt PNAME P-1 P-2 P-3 P-4 Zwischenergebnis: ANGNR 3115 3115 PNR 761235 770008 770114 770231 PFILIALE PLEITER Karlsruhe 3115 Karlsruhe 3115 Heidelberg 1324 Mannheim 2814 Datenorientierte Systemanalyse – SQL ANGNR 3115 3190 2314 2412 3115 3115 73 35 SQL – Structured Query Language PI Datenorientierte Systemanalyse, WS 2014/15 Anhang 7: Beispiel für Mengenoperationen (Intersect) Ergebnis: SELECT ANGNR FROM angestellte WHERE WOHNORT = ‘Karlsruhe‘ INTERSECT SELECT ANGNR FROM angpro GROUP BY ANGNR HAVING COUNT (ANGNR) > 1; angestellte ANGNR NAME 3115 Meyer 3207 Müller 2814 Klein 3190 Maus 2314 Groß 1324 Schmitt 1435 Mayerlein 2412 Müller 2244 Schulz 1237 Krämer 3425 Meier 2454 Schuster Zwischenergebnis: ANGNR 1237 1435 2244 2454 2814 3115 3190 3425 Zwischenergebnis: WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 ANGNR 2314 2412 3115 3190 angpro PNR 761235 761235 761235 761235 761235 770008 ... ANGNR 3207 3115 3190 1435 3425 2244 ... PROZARBZEIT 100 50 50 40 50 20 ... Datenorientierte Systemanalyse – SQL ANGNR 3115 3190 74 Anhang 8: Beispiel für Mengenoperationen (EXCEPT) SELECT ANGNR FROM angestellte WHERE NAME like ‘M%‘ EXCEPT ANGNR 3207 3190 1435 2412 3425 SELECT PLEITER as ANGNR FROM projekt; angestellte ANGNR NAME 3115 Meyer 3207 Müller 2814 Klein 3190 Maus 2314 Groß 1324 Schmitt 1435 Mayerlein 2412 Müller 2244 Schulz 1237 Krämer 3425 Meier 2454 Schuster WOHNORT Karlsruhe Mannheim Mannheim Karlsruhe Karlsruhe Heidelberg Bruchsal Karlsruhe Bruchsal Ludwigshafen Pforzheim Worms ABTNR 35 30 32 30 35 35 32 32 31 31 30 31 projekt PNAME P-1 P-2 P-3 P-4 PNR 761235 770008 770114 770231 PFILIALE PLEITER Karlsruhe 3115 Karlsruhe 3115 Heidelberg 1324 Mannheim 2814 Datenorientierte Systemanalyse – SQL Ergebnis: 75 Dr. Walter Ebner, Institut für Informationswirtschaft 36
