dbis

Praktikum DBS I

SQL – Teil 2

dbis

Praktikum DBS I

Übersicht • Fortgeschrittene SQL-Konstrukte – – – – –

GROUP BY HAVING UNION / INTERSECT / EXCEPT SOME / ALL / ANY IN / EXISTS

• CREATE TABLE

• INSERT / UPDATE / DELETE

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

2

dbis

Praktikum DBS I

SELECT • Syntax SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY

expression table-reference search-condition] grouping-expression] search-condition] sort-key]

[…]: optional

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

3

dbis

Praktikum DBS I

GROUP-BY-Klausel – Syntax • Syntax – GROUP BY grouping-expression {, grouping-expression}

• Bedeutung – Tupel mit gleichen Werten in den Gruppierungsausdrücken werden zu Gruppen zusammengefasst – Die SELECT-Klausel referenziert die gruppierten Ausdrücke • Nur Gruppierungsattribute und Aggregatfunktionen dürfen in SELECT-Klausel stehen

GROUP BY Genre

Film Titel

Genre

Mietpreis

Hängt ihn höher

Western

1,50

Hängt ihn höher

Western

1,50

Krieg der Sterne Sci-Fi

2

Django

Western

1

1984

Sci-Fi

4

Krieg der Sterne Sci-Fi

2

Django

Western

1

1984

4

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

Sci-Fi

4

dbis

Praktikum DBS I

GROUP-BY-Klausel – Beispiele Film Titel

Genre

Mietpreis

• Durchschnittspreise für jedes Filmgenre – SELECT genre, AVG(mietpreis) FROM film GROUP BY genre

• Anzahl der Filme je Genre – SELECT genre, COUNT(*) FROM film GROUP BY genre – Hinweis

Genre Western

1,25

Sci-Fi

3,00

Genre Western

2

Sci-Fi

2

• COUNT(*) bezieht sich auf jede Gruppe, jedes Tupel • COUNT(titel) bezieht sich dagegen nur auf Tupel, die einen Wert für das Attribut „titel“ haben Lehr- und Forschungseinheit Datenbanken und Informationssysteme

5

dbis

Praktikum DBS I

HAVING-Klausel • Syntax – HAVING search-condition • Logischer Ausdruck, NOT/AND/OR/Klammern

• Filtert Gruppen, die mit GROUP BY definiert wurden • HAVING ohne GROUP-BY-Klausel – Das Ergebnis des vorherigen SELECT wird als eine Gruppe angesehen – ohne Gruppierungsattribute. –  es dürfen nur Aggregatfunktionen in der SELECTKlausel stehen

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

6

dbis

Praktikum DBS I

HAVING-Klausel – Beispiele (1) • Durchschnittspreise für Western oder teure Filme – SELECT genre, AVG(mietpreis) FROM film GROUP BY genre HAVING genre = 'Western' OR AVG(mietpreis) > 5

• Der (Gesamt-)Durchschnittspreis wird zusammen mit der Anzahl der Filme nur ausgegeben, wenn er größer als 5 ist – SELECT AVG(mietpreis), COUNT(*) FROM film HAVING AVG(mietpreis) > 5

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

7

dbis

Praktikum DBS I

HAVING-Klausel – Beispiele (2) • Preis der billigsten Filme pro Genre für die Genre „Western“ oder „Krimi“, wenn der Durchschnittspreis des jeweiligen Genres größer als 3 ist. Es werden nur Filme nach 2002 berücksichtigt. – SELECT genre, MIN(mietpreis) FROM film WHERE jahr > 2002 GROUP BY genre HAVING (genre = 'Western' OR genre = 'Krimi') AND AVG(mietpreis) > 3 – „genre = 'Western' OR genre = 'Krimi'“ könnte auch in der WHERE-Klausel stehen Lehr- und Forschungseinheit Datenbanken und Informationssysteme

8

dbis

Praktikum DBS I

UNION • Syntax – subselect | (fullselect) UNION [ALL] subselect | (fullselect) – Kammerung bei fullselect (= Kombination von subselect mit Mengenoperatoren)

• UNION – vereinigt das Ergebnis zweier SELECT-Anfragen – führt Duplikatelimination durch, UNION ALL nicht

• Übereinstimmung der Ergebnistypen der Teilanfragen erforderlich • Alle Tabellen im UNION müssen gleiche Spaltenanzahl haben – Gleiches Schema

• Beispiel – SELECT titel, mietpreis, genre FROM film UNION SELECT titel, mietpreis, stil FROM musik Lehr- und Forschungseinheit Datenbanken und Informationssysteme

9

dbis

Praktikum DBS I

INTERSECT • Syntax

– subselect | (fullselect) INTERSECT [ALL] subselect | (fullselect)

• INTERSECT

– bildet den Durchschnitt zweier SELECT-Anfragen, d. h., es gibt die Tupel aus, die in beiden Relationen vorkommen – führt Duplikatelimination durch, INTERSECT ALL nicht

• Übereinstimmung der Ergebnistypen der Teilanfragen erforderlich • Alle Tabellen beim INTERTSECT müssen gleiche Spaltenanzahl haben – Gleiches Schema

• Beispiel

– SELECT titel FROM film INTERSECT SELECT titel FROM musik

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

10

dbis

Praktikum DBS I

EXCEPT • Syntax – subselect | (fullselect) EXCEPT [ALL] subselect | (fullselect)

• EXCEPT – bildet die Differenz zweier SELECT-Anfragen, d. h., alle Tupel der linken Relation werden ausgewählt, es sei denn, sie erscheinen in der rechten Relation – führt Duplikatelimination durch, EXCEPT ALL nicht

• Übereinstimmung der Ergebnistypen der Teilanfragen erforderlich • Alle Tabellen müssen gleiche Spaltenanzahl haben – Gleiches Schema Lehr- und Forschungseinheit Datenbanken und Informationssysteme

11

dbis

Praktikum DBS I

EXCEPT – Beispiele • Beispiel 1 – (SELECT titel FROM film UNION ALL SELECT titel FROM film) EXCEPT ALL SELECT titel FROM film – Ergebnis entspricht: SELECT titel FROM film

• Beispiel 2 – (SELECT titel FROM film UNION ALL SELECT titel FROM film) EXCEPT SELECT titel FROM film – Ergebnis: kein Tupel Lehr- und Forschungseinheit Datenbanken und Informationssysteme

12

dbis

Praktikum DBS I

Quantifizierende Bedingungen SOME / ANY / ALL • Quantifizierende Bedingung besteht aus einem der Vergleichsoperatoren (=, ) und ALL, ANY oder SOME (SOME äquivalent zu ANY) • Syntax – WHERE expression = | | < | > | = SOME | ANY | ALL (fullselect)

• Auswertung – ANY liefert TRUE, wenn es mindestens ein Element im Ergebnis der Unteranfrage gibt, für das der Vergleich erfüllt wird – ALL liefert TRUE, falls alle Ergebnisse der Unteranfrage den Vergleich erfüllen

• Achtung: ALL ≠ Allquantor (in SQL nicht vorhanden) – ALL führt Vergleich eines Wertes mit einer Menge durch Lehr- und Forschungseinheit Datenbanken und Informationssysteme

13

dbis

Praktikum DBS I

Quantifizierende Bedingungen SOME / ANY / ALL – Beispiele • Alle Filme, deren Mietpreis teurer ist als der Mietpreis irgendwelcher Stücke von „Elvis Presley“ – SELECT titel FROM film WHERE mietpreis > SOME (SELECT mietpreis FROM musik WHERE interpret = 'Elvis Presley')

• Alle Filme, die mindestens so teuer sind wie die Musik von „Ludwig van Beethoven“ – SELECT titel FROM film WHERE mietpreis >= ALL (SELECT mietpreis FROM musik WHERE interpret = 'Ludwig van Beethoven')

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

14

dbis

Praktikum DBS I

IN • Testen auf Mitgliedschaft • Äquivalent zu „= ANY“ • Syntax – WHERE expression [NOT] IN (fullselect)

• Beispiele – SELECT titel FROM film • WHERE titel IN (SELECT titel FROM filmstars WHERE name = 'Fonda') • WHERE titel NOT IN ('Lethal Weapon', 'Matrix 3')

– SELECT tiefe FROM see WHERE (name, flaeche) IN (VALUES ('Aralsee', 17160), ('Viktoriasee', 68870)) Lehr- und Forschungseinheit Datenbanken und Informationssysteme

15

dbis

Praktikum DBS I

EXISTS • EXISTS überprüft, ob die Unteranfrage Tupel produziert hat (ohne den Inhalt anzusehen) • Syntax – WHERE EXISTS (fullselect)

• Auswertung – Liefert FALSE, wenn die Menge leer ist, sonst TRUE

• Beispiel – SELECT 1 FROM one_row_table WHERE EXISTS (SELECT titel FROM film WHERE darsteller = 'Lee')

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

16

dbis

Praktikum DBS I

Beispiele (1) Fluss Name

See Länge

Name

fließt_durch Fluss

Stadt

Größe liegt_in

See

Stadt

• Annahme – Namen von Flüssen, Seen und Städten seien jeweils eindeutig

• Aufgabe: Geben Sie SQL-Anfragen an! – Anzahl der Städte, die am „Nil“ liegen. – Alle Flüsse, die nicht durch „Berlin“ fließen und alle Seen, die nicht in „Berlin“ liegen. Der Typ des Gewässers soll mit ausgegeben werden. Lehr- und Forschungseinheit Datenbanken und Informationssysteme

17

dbis

Praktikum DBS I

Beispiele (2) Stadt Name

Land

Bevölkerung

• Aufgabe: Geben Sie SQL-Anfragen an! – Alle Länder, die laut Städte-Statistik mehr als 50 Mio. EW haben. – Alle Städte, die in Ländern liegen, die laut StädteStatistik mehr als 50 Mio. EW haben.

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

18

dbis

Praktikum DBS I

CREATE TABLE (1) • Tabelle anlegen

– CREATE TABLE person( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, vorname VARCHAR(50), telefon INTEGER REFERENCES telefonbuch(nr), persnr INTEGER DEFAULT 1 ) – CREATE TABLE angestellter( id INTEGER NOT NULL, buero INTEGER NOT NULL, gehalt INTEGER, PRIMARY KEY(id), FOREIGN KEY(id) REFERENCES person(id), UNIQUE(buero, gehalt) )

• Tabelle löschen

– DROP TABLE person – DROP TABLE angestellter

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

19

dbis

Praktikum DBS I

CREATE TABLE (2) • NULL-Werte – Default: zugelassen – NOT NULL für Primärschlüssel und UNIQUE muss explizit angegeben werden

• Datentypen – INTEGER, SMALLINT, BIGINT, DECIMAL, REAL, DOUBLE, VARCHAR, CHAR, TIME, TIMESTAMP, DATE, GRAPHIC, DATALINK, …

• Tabellen auflisten (nur Name) – LIST TABLES [FOR SCHEMA ]

• Struktur von Tabellen/Views – DESCRIBE TABLE Lehr- und Forschungseinheit Datenbanken und Informationssysteme

20

dbis

Praktikum DBS I

INSERT • Syntax – INSERT INTO | [ ( { , } ) ] VALUES {, expression} |

• Beispiele – INSERT INTO person VALUES (0, 'Meyer', 'Hans', 1234567, 13), (1, 'Müller', 'Franz', 1234568, DEFAULT) – INSERT INTO person(id, name, vorname) VALUES (1, 'Müller', 'Franz') • Default-Werte: telefon = NULL (implizit), persnr = 1

– INSERT INTO film VALUES SELECT titel, genre FROM angebot WHERE jahr > 2000 Lehr- und Forschungseinheit Datenbanken und Informationssysteme

21

dbis

Praktikum DBS I

UPDATE • Syntax – UPDATE table-name SET attribut1 = wert1 {, attribut2 = wert2} [WHERE where-Klausel]

• Beispiel – UPDATE student SET kontostand=kontostand–500, fachsemester=fachsemester+1 WHERE fachsemester>10

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

22

dbis

Praktikum DBS I

DELETE • Syntax – DELETE FROM table-name [WHERE where-condition]

• Beispiel – DELETE FROM politiker – DELETE FROM unis WHERE stadt='München'

Lehr- und Forschungseinheit Datenbanken und Informationssysteme

23

dbis

Praktikum DBS I

DELETE, UPDATE – Beispiele Professor ID name vorname

geburtsjahr

anz_wimis fakultät familienstand

geschlecht

Vorlesung prof_id

name

zeit

• Aufgabe: Geben Sie SQL-Anfragen an! – Löschen Sie alle Professoren der Landwirtschaftlichen Fakultät. – Wir befinden uns im Jahr 2020. Löschen Sie alle Professoren über 60! – Reduzieren Sie die Anzahl aller WiMis auf die Hälfte. – „Verheiraten“ Sie alle Professoren! Lehr- und Forschungseinheit Datenbanken und Informationssysteme

24