Einführung in die Wirtschaftsinformatik
06: SQL – Data Manipulation Language und Query Language
Isabella Seeber
Die Datenbank eines Fahrradhändlers
Dr. Isabella Seeber2
Alle folgenden Abfragen bauen auf diesem Modell auf.Die dazugehörige Datenbank finden Sie in OLAT unter:Vorlesung (VO) -> Vorlesungsunterlagen -> AdventureWorks.accdb
ER Diagramme – SQL DDL
Dr. Isabella Seeber3
Welche der folgenden CREATE-Table Statement(s ) setzt/setzen die N:M Beziehung korrekt um?
CREATE TABLE Kunde_Adresse (ID counter,fs_kundeID integer,fs_adresseID integer,Hauptadresse yesno,primary key (ID));
create table Kunde_Adresse(ID counter,fs_kundeID integer,fs_adresseID integer,Hauptadresse yesno,primary key (ID),foreign key (fs_kundeID) references Kunde (kundeID),foreign key (fs_adresseID) references Adresse (adresseID));
create table Kunde_Adresse(ID,fs_kundeID integer,Hauptadresse yesno,primary key (ID),foreign key (fs_kundeID) references Kunde (kundeID),foreign key (fs_adresseID) references Adresse (adresseID));
A
B
C
http://pingo.upb.de/969824
Informationssysteme erforderlich, die in der Lage sind die zunehmenden Datenmengen in der notwendigen Qualität zu managen und auszuwerten (vgl. SAP HANA).
Quellen: Buhl et al. (2013), IBM Global Business Services (2012)
Volume
Große Datenmengen
eBay analysiert täglich 100 Terabyte anKundentransaktionsdaten
Variety
Daten in vielen Formaten
In Facebook werden monatlich ca. 30Mrd. Inhalte (z.B. Texte, Videos) geteilt
Velocity
Datenströme
Autos sind mit ca. 100 Sensorenausgestattet (z.B. Tankinhaltsmessung)
Veracity
Datenunsicherheit
Aufgrund schlechter Datenqualität entsteht der US Wirtschaftjährlich ein Schaden von ca. 1,3 Bio. Euro
Die wesentlichen Charakteristika (V‘s) von Big Data
Dr. Isabella Seeber4
Big Data – Große Datenmengen
Beispiele
eBay.com analysiert täglich ca. 100TB an Transaktionsdaten, um die Suche, die Empfehlungen für Kunden und den Verkauf zu verbessern.
Walmart analysiert mehr als 1 Mio. Kundentransaktionen pro Stunde, um die Nachfrage für seine Produkte zu prognostizieren.
Die Immobilienfirma Windermere Real Estate analysiert die Routen von fast 100 Mio. Fahrern in den USA, um die Fahrzeiten für neue Hausbesitzer zu bestimmen.
Amazon, Facebook, Morgan Stanley, etc.
Globale Datenmenge
[in Zettabytes]
2009 2011 2015 2020
35,0
7,9
1,90,8
Quelle: [Reuters 2012]
Dr. Isabella Seeber5
Big Data – Große Datenmengen (cont‘d)
Dr. Isabella Seeber6
http://www.ibmbigdatahub.com/infographic/four-vs-big-data
Big Data – Große Datenmengen (cont‘d)
Dr. Isabella Seeber7
http://www.ibmbigdatahub.com/infographic/four-vs-big-data
Big Data – Große Datenmengen (cont‘d)
Dr. Isabella Seeber8
http://www.ibmbigdatahub.com/infographic/four-vs-big-data
Big Data – Große Datenmengen (cont‘d)
Dr. Isabella Seeber9
http://www.ibmbigdatahub.com/infographic/four-vs-big-data
Von ERP zu Big Data
Dr. Isabella Seeber10
https://blogs.msdn.microsoft.com/data__knowledge__intelligence/2013/02/18/big-data-big-deal/
Welche Informationen könnten für Geschäftsentscheidungen wesentlich sein?
Dr. Isabella Seeber11
Aus Sicht der/des:
Geschäftsleitung Marketing
VerkaufEinkauf
SQL Kommandos
Dr. Isabella Seeber12
https://www.w3schools.in/mysql/ddl-dml-dcl/
DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database. •CREATE - to create a database and its objects like (table, index, views, store procedure, function, and triggers)•ALTER - alters the structure of the existing database•DROP - delete objects from the database
DML is short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database. •SELECT - retrieve data from a database•INSERT - insert data into a table•UPDATE - updates existing data within a table•DELETE - Delete all records from a database table
Einfügen von Daten
INSERT INTO tabelleVALUES (wert_1, …, wert_n);
bzw.
INSERT INTO tabelle (attribut_1, …, attribut_n) VALUES (wert_1, …, wert_n);
• Werte von zeichenbasierten Datentypen müssen in Anführungszeichen stehen, von zahlenbasierten Datentypen nicht
• Werte und deren Reihenfolge lässt sich mit der zweiten Variante ändern
Dr. Isabella Seeber13
Einfügen von Daten (cont‘d)
Neue Kategorie „Shoes“ mit der ID 42 einfügen
INSERT INTO Kategorie VALUES (42, "Shoes");
oder
INSERT INTO Kategorie (kategorieId, name)
VALUES (42, "Shoes");
kategorieId ist vom Typ LONGName ist vom Typ VARCHAR
kategorieID name
1 Bikes
2 Components
3 Clothing
4 Accessories
5 Mountain Bikes
6 Road Bikes
7 Touring Bikes
8 Handlebars
9 Bottom Brackets
10 Brakes
11 Chains
12 Cranksets
13 Derailleurs
14 Forks
15 Headsets
16Mountain Frames
17 Pedals
18 Road Frames
19 Saddles
20 Touring Frames
… …
Dr. Isabella Seeber14
Löschen von Daten
DELETE
FROM tabelle;
bzw.
DELETE
FROM tabelle
WHERE bedingungen;
• Mit WHERE Bedingungen werden nur jene Entitäten (Zeilen) gelöscht, die die Bedingungen erfüllen, ansonsten alle
• Bedingungen können mittels AND bzw. OR verknüpft werden
Dr. Isabella Seeber15
Löschen von Daten (cont‘d)
• Alle Kunden löschen
DELETE FROM Kunde;
• Alle Kunden mit einer Id kleiner als 10 löschen
DELETE FROM KundeWHERE kundeID < 10;
Dr. Isabella Seeber16
Tabelle Kunde
kundeID vorname mittelname nachname firma betreuer email telefon1Orlando N. Gee A Bike Store adventure-works\pamela0 [email protected] 245-555-01732Keith Harris Progressive Sports adventure-works\david8 [email protected] 170-555-01273Donna F. Carreras Advanced Bike Components adventure-works\jillian0 [email protected] 279-555-01304Janet M. Gates Modular Cycle Systems adventure-works\jillian0 [email protected] 710-555-01735Lucy Harrington Metropolitan Sports Supply adventure-works\shu0 [email protected] 828-555-01866Rosmarie J. Carroll Aerobic Exercise Company adventure-works\linda3 [email protected] 244-555-01127Dominic P. Gash Associated Bikes adventure-works\shu0 [email protected] 192-555-0173
10Kathleen M. Garza Rural Cycle Emporium adventure-works\josé1 [email protected] 150-555-012711Katherine Harding Sharp Bikes adventure-works\josé1 [email protected] 926-555-015912Johnny A. Caprio Bikes and Motorbikes adventure-works\garrett1 [email protected] 112-555-019116Christopher R. Beck Bulk Discount Store adventure-works\jae0 [email protected] 1 (11) 500 555-013218David J. Liu Catalog Store adventure-works\michael9 [email protected] 440-555-013219John A. Beaver Center Cycle Shop adventure-works\pamela0 [email protected] 521-555-019520Jean P. Handley Central Discount Store adventure-works\david8 [email protected] 582-555-011321Jinghao Liu Chic Department Stores adventure-works\jillian0 [email protected] 928-555-011622Linda E. Burnett Travel Systems adventure-works\jillian0 [email protected] 121-555-012123Kerim Hanif Bike World adventure-works\shu0 [email protected] 216-555-012224Kevin Liu Eastside Department Store adventure-works\linda3 [email protected] 926-555-016425Donald L. Blanton Coalition Bike Company adventure-works\shu0 [email protected] 357-555-016128Jackie E. Blackwell Commuter Bicycle Store adventure-works\josé1 [email protected] 972-555-016329Bryan Hamilton Cross-Country Riding Supplies adventure-works\josé1 [email protected] 344-555-014430Todd R. Logan Cycle Merchants adventure-works\garrett1 [email protected] 783-555-011034Barbara J. German Cycles Wholesaler & Mfg. adventure-works\jae0 [email protected] 1 (11) 500 555-018137Jim Geist Two Bike Shops adventure-works\pamela0 [email protected] 724-555-016138Betty M. Haines Finer Mart adventure-works\david8 [email protected] 867-555-0114
39 Sharon J. Looney Fitness Hotel adventure-works\jillian0 [email protected] 377-555-0132
40 Darren Gehring Journey Sporting Goods adventure-works\jillian0 [email protected] 417-555-0182
41 Erin M. Hagens Distant Inn adventure-works\shu0 [email protected] 244-555-0127
42 Jeremy Los Healthy Activity Store adventure-works\linda3 [email protected] 911-555-0165
43 Elsa Leavitt Frugal Bike Shop adventure-works\shu0 [email protected] 482-555-0174
46 David O Lawrence Gear-Shift Bikes Limited adventure-works\josé1 [email protected] 653-555-0159
47 Hattie J. Haemon Greater Bike Store adventure-works\josé1 [email protected] 141-555-0172
48 Anita R. Lucerne Grand Industries adventure-works\garrett1 [email protected] 164-555-0118
52 Rebecca Laszlo Instruments and Parts Company adventure-works\jae0 [email protected] 1 (11) 500 555-0155
… … … … … … … …
17
Ändern von Daten
UPDATE tabelle
SET attribut_1 = wert_1,…, attribut_n = wert_n;
bzw.
UPDATE tabelle
SET attribut_1 = wert_1,…, attribut_n = wert_n
WHERE bedingungen;
• Mit WHERE Bedingungen werden nur die Attribute jener Entitäten (Zeilen) geändert, die die Bedingungen erfüllen, ansonsten alle
• Bedingungen können mittels AND bzw. OR verknüpft werden
Dr. Isabella Seeber18
Ändern von Daten (cont‘d)
• Den Verkaufspreis aller Produkte um 10% erhöhen
UPDATE ProduktSET verkaufspreis = verkaufspreis * 1.1;
• Den Verkaufspreis aller Produkte, die zwischen 100 € und 500 € kosten, um 10% erhöhen
UPDATE ProduktSET verkaufspreis = verkaufspreis * 1.1WHERE verkaufspreis > 100AND verkaufspreis < 500;
Dr. Isabella Seeber19
Tabelle ProduktproduktID name produktnummer farbe einkaufspreis verkaufspreis gewicht startverkauf endeverkauf FS_kategorieID
680 HL Road Frame - Black, 58 FR-R92B-58 Black 1 059,31 € 1 431,50 € 1016,04 01.06.1998 18
706 HL Road Frame - Red, 58 FR-R92R-58 Red 1 059,31 € 1 431,50 € 1016,04 01.06.1998 18
707 Sport-100 Helmet, Red HL-U509-R Red 13,09 € 34,99 € 01.07.2001 35
708 Sport-100 Helmet, Black HL-U509 Black 13,09 € 34,99 € 01.07.2001 35
709 Mountain Bike Socks, M SO-B909-M White 3,40 € 9,50 € 01.07.2001 30.06.2002 27
710 Mountain Bike Socks, L SO-B909-L White 3,40 € 9,50 € 01.07.2001 30.06.2002 27
711 Sport-100 Helmet, Blue HL-U509-B Blue 13,09 € 34,99 € 01.07.2001 35
712 AWC Logo Cap CA-1098 Multi 6,92 € 8,99 € 01.07.2001 23
713 Long-Sleeve Logo Jersey, S LJ-0192-S Multi 38,49 € 49,99 € 01.07.2001 25
714 Long-Sleeve Logo Jersey, M LJ-0192-M Multi 38,49 € 49,99 € 01.07.2001 25
715 Long-Sleeve Logo Jersey, L LJ-0192-L Multi 38,49 € 49,99 € 01.07.2001 25
716 Long-Sleeve Logo Jersey, XL LJ-0192-X Multi 38,49 € 49,99 € 01.07.2001 25
717 HL Road Frame - Red, 62 FR-R92R-62 Red 868,63 € 1 431,50 € 1043,26 01.07.2001 18
718 HL Road Frame - Red, 44 FR-R92R-44 Red 868,63 € 1 431,50 € 961,61 01.07.2001 18
719 HL Road Frame - Red, 48 FR-R92R-48 Red 868,63 € 1 431,50 € 979,75 01.07.2001 18
720 HL Road Frame - Red, 52 FR-R92R-52 Red 868,63 € 1 431,50 € 997,9 01.07.2001 18
721 HL Road Frame - Red, 56 FR-R92R-56 Red 868,63 € 1 431,50 € 1016,04 01.07.2001 18
722 LL Road Frame - Black, 58 FR-R38B-58 Black 204,63 € 337,22 € 1115,83 01.07.2001 18
723 LL Road Frame - Black, 60 FR-R38B-60 Black 204,63 € 337,22 € 1124,9 01.07.2001 18
724 LL Road Frame - Black, 62 FR-R38B-62 Black 204,63 € 337,22 € 1133,98 01.07.2001 18
725 LL Road Frame - Red, 44 FR-R38R-44 Red 187,16 € 337,22 € 1052,33 01.07.2001 30.06.2003 18
726 LL Road Frame - Red, 48 FR-R38R-48 Red 187,16 € 337,22 € 1070,47 01.07.2001 30.06.2003 18
727 LL Road Frame - Red, 52 FR-R38R-52 Red 187,16 € 337,22 € 1088,62 01.07.2001 30.06.2003 18
728 LL Road Frame - Red, 58 FR-R38R-58 Red 187,16 € 337,22 € 1115,83 01.07.2001 30.06.2003 18
729 LL Road Frame - Red, 60 FR-R38R-60 Red 187,16 € 337,22 € 1124,9 01.07.2001 30.06.2003 18
730 LL Road Frame - Red, 62 FR-R38R-62 Red 187,16 € 337,22 € 1133,98 01.07.2001 30.06.2003 18
731 ML Road Frame - Red, 44 FR-R72R-44 Red 352,14 € 594,83 € 1006,97 01.07.2001 30.06.2002 18
732 ML Road Frame - Red, 48 FR-R72R-48 Red 352,14 € 594,83 € 1025,11 01.07.2001 30.06.2002 18
733 ML Road Frame - Red, 52 FR-R72R-52 Red 352,14 € 594,83 € 1043,26 01.07.2001 30.06.2002 18
734 ML Road Frame - Red, 58 FR-R72R-58 Red 352,14 € 594,83 € 1070,47 01.07.2001 30.06.2002 18
735 ML Road Frame - Red, 60 FR-R72R-60 Red 352,14 € 594,83 € 1079,54 01.07.2001 30.06.2002 18
… … … … … … … … … …
20
SQL Kommandos
Dr. Isabella Seeber21
https://www.w3schools.in/mysql/ddl-dml-dcl/
DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database. •CREATE - to create a database and its objects like (table, index, views, store procedure, function, and triggers)•ALTER - alters the structure of the existing database•DROP - delete objects from the database
DML is short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database. •SELECT - retrieve data from a database•INSERT - insert data into a table•UPDATE - updates existing data within a table•DELETE - Delete all records from a database table
Abfrage von Daten aus einer Tabelle – SELECT, FROM
• Ids und Namen aller Kategorien ausgeben lassen
SELECT kategorieID, name
FROM Kategorie;
ODER
SELECT *
FROM Kategorie;
kategorieID name
1 Bikes
2 Components
3 Clothing
4 Accessories
5 Mountain Bikes
6 Road Bikes
7 Touring Bikes
8 Handlebars
9 Bottom Brackets
10 Brakes
… …
Ergebnis
Dr. Isabella Seeber22
Bedingungen - WHERE
SELECT attribut_1, …, attribut_n
FROM tabelle
WHERE bedingungen;
• Statt attribut_1, …, attribut_n kann * als Platzhalter für alle Attribute angegeben werden
• Mehrere Bedingungen können mittels AND oder ORverbunden werden
Dr. Isabella Seeber23
Projektion vs. Selektion
SelektionAuswahl einer Untermenge aller Entitäten (Zeilen) einer Tabelle
WHERE
Dr. Isabella Seeber24
ProjektionAuswahl einer Untermenge der Attribute (Spalten) einer oder mehrerer TabellenSELECT FROM
Projektion (cont‘d)
• Nur den Vornamen, Nachnamen und E-Mail Adresse aller Kunden ausgeben lassen
SELECT vorname, nachname, emailFROM Kunde;
vorname nachname email
Orlando Gee [email protected]
Keith Harris [email protected]
Donna Carreras [email protected]
Janet Gates [email protected]
Lucy Harrington [email protected]
Rosmarie Carroll [email protected]
Dominic Gash [email protected]
Kathleen Garza [email protected]
Katherine Harding [email protected]
Johnny Caprio [email protected]
… … …
Ergebnis
Dr. Isabella Seeber25
Auswahl von Attributen (Projektion)
Selektion (cont‘d)
• Alle Produkte ausgeben lassen, die weniger als1000 € kosten
SELECT * FROM ProduktWHERE verkaufspreis < 1000;
produktID name produktnummer farbe einkaufspreis verkaufspreis gewicht startverkauf endeverkauf FS_kategorieID
707 Sport-100 Helmet, Red HL-U509-R Red 13,09 € 34,99 € 01.07.2001 00:00 35
708 Sport-100 Helmet, Black HL-U509 Black 13,09 € 34,99 € 01.07.2001 00:00 35
709 Mountain Bike Socks, M SO-B909-M White 3,40 € 9,50 € 01.07.2001 00:00 30.06.2002 00:00 27
710 Mountain Bike Socks, L SO-B909-L White 3,40 € 9,50 € 01.07.2001 00:00 30.06.2002 00:00 27
711 Sport-100 Helmet, Blue HL-U509-B Blue 13,09 € 34,99 € 01.07.2001 00:00 35
712 AWC Logo Cap CA-1098 Multi 6,92 € 8,99 € 01.07.2001 00:00 23
… … … … … … … … … …
Ergebnis
Dr. Isabella Seeber26
Auswahl von Entitäten (Selektion)
Selektion (cont‘d)
• Alle Produkte ausgeben lassen, die rot sind undderen Gewicht größer oder gleich als 1000 Gramm ist.
SELECT * FROM ProduktWHERE farbe = "Red" AND Gewicht >= 1000;
produktID name produktnummer farbe einkaufspreis verkaufspreis gewicht startverkauf endeverkauf FS_kategorieID
706 HL Road Frame - Red, 58 FR-R92R-58 Red 1 059,31 € 1 431,50 € 1016,04 01.06.1998 18
717 HL Road Frame - Red, 62 FR-R92R-62 Red 868,63 € 1 431,50 € 1043,26 01.07.2001 18
721 HL Road Frame - Red, 56 FR-R92R-56 Red 868,63 € 1 431,50 € 1016,04 01.07.2001 18
725 LL Road Frame - Red, 44 FR-R38R-44 Red 187,16 € 337,22 € 1052,33 01.07.2001 30.06.2003 18
726 LL Road Frame - Red, 48 FR-R38R-48 Red 187,16 € 337,22 € 1070,47 01.07.2001 30.06.2003 18
727 LL Road Frame - Red, 52 FR-R38R-52 Red 187,16 € 337,22 € 1088,62 01.07.2001 30.06.2003 18
… … … … … … … … … …
Ergebnis
Dr. Isabella Seeber27
SQL Operatoren für Berechnungen und Vergleiche
• Meist verwendete Operatoren
– Addieren (+), Subtrahieren (-), Multiplizieren (*), Dividieren (/)
– < kleiner, > großer, = gleich
• Erlös aus dem Verkauf eines Produkts für jedesProdukt ausgeben lassen
SELECT name, verkaufspreis-einkaufspreis AS ErloesFROM Produkt; name Erloes
HL Road Frame - Black, 58 372,19 €
HL Road Frame - Red, 58 372,19 €
Sport-100 Helmet, Red 21,90 €
Sport-100 Helmet, Black 21,90 €
Mountain Bike Socks, M 6,10 €
Mountain Bike Socks, L 6,10 €
Sport-100 Helmet, Blue 21,90 €
AWC Logo Cap 2,07 €
Ergebnis
Dr. Isabella Seeber28
https://www.w3schools.com/sql/sql_operators.asp
SQL logische Operatoren
Operator Beschreibung
AND WAHR wenn alle Bedingungen die durch AND getrennt sind wahr sind
BETWEEN WAHR wenn die Rechengröße innerhalb eines gewissen Vergleichsbereichs liegt
EXISTS WAHR wenn die Unterabfrage 1 oder mehrere Einträge liefert
IN WAHR wenn die Rechengröße gleich eine Liste von Ausdrucke ist
LIKE WAHR wenn die Rechengröße einem Muster entspricht
NOT Zeigt den Eintrag wenn die Bedingung(en) NICHT WAHR sind
OR WAHR wenn eine beliebige Bedingung von Mehreren, die getrennt sind durch OR, WAHR ist
Dr. Isabella Seeber29
https://www.w3schools.com/sql/sql_operators.asp
Sortierung von Entitäten – ORDER BY
• Angabe der Reihenfolge durch ASC (standardmäßig) für eine aufsteigende bzw. DESC für eine absteigende Sortierung
SELECT attribut_1, …, attribut_n
FROM tabelle
WHERE bedingungen
ORDER BY attribut_1, …, attribut_n;
Dr. Isabella Seeber30
Sortierung von Entitäten – ORDER BY (cont‘d)
• Produktnummer, Name und Verkaufspreis allerProdukte, absteigend sortiert nach dem Verkaufspreis ausgeben lassen
SELECT produktnummer, name, verkaufspreisFROM produktORDER BY verkaufspreis DESC; produktnummer name verkaufspreis
BK-R93R-62 Road-150 Red, 62 3 578,27 €
BK-R93R-44 Road-150 Red, 44 3 578,27 €
BK-R93R-48 Road-150 Red, 48 3 578,27 €
BK-R93R-52 Road-150 Red, 52 3 578,27 €
BK-R93R-56 Road-150 Red, 56 3 578,27 €
BK-M82S-38 Mountain-100 Silver, 38 3 399,99 €
BK-M82S-48 Mountain-100 Silver, 48 3 399,99 €
BK-M82S-44 Mountain-100 Silver, 44 3 399,99 €
Ergebnis
Dr. Isabella Seeber31
In Liste von Werten suchen - IN/NOT IN
• Mittels IN bzw. NOT IN kann nach einer Liste von Werten gesucht werden
SELECT attribut_1, …, attribut_n
FROM tabelle
WHERE attribut IN (wert_1, …, wert_n)
Dr. Isabella Seeber32
In Liste von Werten suchen - IN/NOT IN (cont‘d)
• Alle Produkte ausgeben lassen, die entweder rot oder schwarz sind
SELECT *FROM ProduktWHERE farbe IN ("Red", "Black");
produktID name produktnummer farbe einkaufspreis verkaufspreis gewicht startverkauf endeverkauf FS_kategorieID
680 HL Road Frame - Black, 58 FR-R92B-58 Black 1 059,31 € 1 431,50 € 1016,04 01.06.1998 18
706 HL Road Frame - Red, 58 FR-R92R-58 Red 1 059,31 € 1 431,50 € 1016,04 01.06.1998 18
707 Sport-100 Helmet, Red HL-U509-R Red 13,09 € 34,99 € 01.07.2001 35
708 Sport-100 Helmet, Black HL-U509 Black 13,09 € 34,99 € 01.07.2001 35
717 HL Road Frame - Red, 62 FR-R92R-62 Red 868,63 € 1 431,50 € 1043,26 01.07.2001 18
… … … … … … … … … …
Ergebnis
Dr. Isabella Seeber33
Abfrage nach Mustern - LIKE
• Mittels LIKE Befehl kann nach Mustern gesucht werden
SELECT attribut_1, …, attribut_n
FROM tabelle
WHERE attribut LIKE muster
• * (mehrere Zeichen) und ? (ein Zeichen) können als Platzhalter in einem Muster verwendet werden
Dr. Isabella Seeber34
Abfrage nach Mustern – LIKE (cont‘d)
• Alle Kunden ausgeben lassen, deren Vorname mit Al beginnt
SELECT *
FROM Kunde
WHERE vorname LIKE "Al*";
• Alle Produkte ausgeben lassen, bei denen der dritteBuchstabe des Names ein u ist
SELECT *FROM KundeWHERE vorname LIKE “??u*";
Dr. Isabella Seeber35
Selbstevaluation
Dr. Isabella Seeber36
3) Ein SQL Statement, welches alle Filme ausgibt, die vor dem 01.01.2005 ins Kino gekommen sind.
2) Ein SQL Statement, welches alle Filme ausgibt, welche mit „Batman“ beginnen.
1) Ein SQL Statement, welches alle Filme dem Titel aufsteigend sortiert.
SELECT
FROM
WHERE
SELECT
FROM
WHERE
SELECT
FROM
WHERE
Tabelle “Film”
Aggregatsfunktionen
• Eingebaute Aggregatsfunktionen geben ein Ergebnis zurück, berechnet mittels Werten eines Attributs
– AVG(attribut) – Durchschnittswert eines Attributs
– COUNT(attribut) – Anzahl der Werte eines Attributs
– MAX(attribut) – Höchster Wert eines Attributs
– MIN(attribut) – Kleinster Wert eines Attributs
– SUM(attribut) – Summe der Werte eines Attributs
• Durchschnittlichen Verkaufspreis aller Produkte ausgeben lassen
SELECT AVG (verkaufspreis) AS DurchschnittspreisFROM Produkt;
Dr. Isabella Seeber37
Durchschnittspreis
744,60 €
Ergebnis
Abfrage von Daten aus mehreren Tabellen - JOIN
• Daten aus zwei oder mehreren Tabellen werden miteinander verknüpft (JOIN)
SELECT attribut_1, …, attribut_n
FROM tabelle_1, tabelle_2
WHERE primärschlüssel = fremdschlüssel;
oder
SELECT attribut_1, …, attribut_n
FROM tabelle_1 INNER JOIN tabelle_2 ONprimärschlüssel = fremdschlüssel;
Dr. Isabella Seeber38
Abfrage von Daten aus mehreren Tabellen – JOIN (cont‘d)
• Name und Fremdschlüssel aller Produkte, und die Namen und Primärschlüssel, der dazugehörigen Kategorie ausgeben lassen
SELECT Produkt.name, Produkt.FS_kategorieID, Kategorie.name, Kategorie.kategorieIDFROM Produkt, KategorieWHERE Produkt.FS_kategorieID = Kategorie.kategorieID
oder
SELECT Produkt.name, Produkt.FS_kategorieID, Kategorie.name, Kategorie.kategorieIDFROM Produkt INNER JOIN Kategorie ON Produkt.FS_kategorieID = Kategorie.kategorieID
Dr. Isabella Seeber39
Tabellen Produkt und Kategorie im Kreuzprodukt
Dr. Isabella Seeber40
Abfrage von Daten aus mehreren Tabellen (cont‘d)
Produkt.name FS_kategorieID Kategorie.name kategorieIDMountain-100 Silver, 38 5Mountain Bikes 5Mountain-100 Silver, 42 5Mountain Bikes 5Mountain-100 Silver, 44 5Mountain Bikes 5Mountain-100 Silver, 48 5Mountain Bikes 5Mountain-100 Black, 38 5Mountain Bikes 5Mountain-100 Black, 42 5Mountain Bikes 5Mountain-100 Black, 44 5Mountain Bikes 5Mountain-100 Black, 48 5Mountain Bikes 5Mountain-200 Silver, 38 5Mountain Bikes 5Mountain-200 Silver, 42 5Mountain Bikes 5Mountain-200 Silver, 46 5Mountain Bikes 5Mountain-200 Black, 38 5Mountain Bikes 5Mountain-200 Black, 42 5Mountain Bikes 5Mountain-200 Black, 46 5Mountain Bikes 5Mountain-300 Black, 38 5Mountain Bikes 5Mountain-300 Black, 40 5Mountain Bikes 5Mountain-300 Black, 44 5Mountain Bikes 5Mountain-300 Black, 48 5Mountain Bikes 5Mountain-400-W Silver, 38 5Mountain Bikes 5Mountain-400-W Silver, 40 5Mountain Bikes 5Mountain-400-W Silver, 42 5Mountain Bikes 5Mountain-400-W Silver, 46 5Mountain Bikes 5Mountain-500 Silver, 40 5Mountain Bikes 5… …… …
Ergebnis
Dr. Isabella Seeber41
Gruppierung von Daten – GROUP BY
SELECT attribut_1, …, attribut_n
FROM tabellen
WHERE bedingungen
GROUP BY attribut_1, …, attribut_n;
• Mittels GROUP BY können Entitäten nach den angegebenen Attributen gruppiert werden (Subgruppen)
• Aggregatfunktionen werden an den Subgruppen angewandt
Dr. Isabella Seeber42
Gruppierung von Daten – GROUP BY (cont‘d)
• Anzahl der Produkte pro Kategorie ausgeben lassen
SELECT name, COUNT(*) AS ProdukteFROM Produkt INNER JOIN Kategorie ON Produkt.FS_kategorieID = Kategorie.kategorieIDGROUP BY name;
name Produkte
Bib-Shorts 3
Bike Racks 1
Bike Stands 1
Bottles and Cages 3
Bottom Brackets 3
Brakes 2
Caps 1
Chains 1
Cleaners 1
… …
Ergebnis
Dr. Isabella Seeber43
Bedingung auf Subgruppen - HAVING
SELECT attribut_1, …, attribut_n
FROM tabellen
WHERE bedingungen
GROUP BY attribut_1, …, attribut_n HAVING bedingungen;
• Mittels HAVING werden nur Subgruppen ausgegeben, die die angegebenen Bedingungen erfüllen
Dr. Isabella Seeber44
Bedingung auf Subgruppen – HAVING (cont‘d)
• Nur jene Kategorie ausgeben lassen, denen mehrals 10 Produkte zugewiesen sind
SELECT name, COUNT(*) AS ProdukteFROM Produkt INNER JOIN Kategorie ON Produkt.FS_kategorieID = Kategorie.kategorieIDGROUP BY nameHAVING COUNT(*) > 10;
name Produkte
Mountain Bikes 32
Mountain Frames 28
Road Bikes 43
Road Frames 33
Tires and Tubes 11
Touring Bikes 22
Touring Frames 18
Wheels 14
Ergebnis
Dr. Isabella Seeber45
Unterabfragen
• SQL erlaubt es, Unterabfragen in einer Abfrageanstelle von Werten zu definieren
• Unterabfragen muss in runden Klammern geschrieben werden
• Wird häufig in Verbindung mit IN/NOT IN verwendet
Dr. Isabella Seeber46
Unterabfragen (cont‘d)
• Alle Produkte, die noch nie verkauft wurden,ausgeben lassen
SELECT *FROM ProduktWHERE produktID NOT IN (SELECT FS_produktIDFROM Bestellposition);
produktID name produktnummer farbe einkaufspreis verkaufspreis gewicht startverkauf endeverkauf FS_kategorieID
680 HL Road Frame - Black, 58 FR-R92B-58 Black 1 059,31 € 1 431,50 € 1016,04 01.06.1998 18
706 HL Road Frame - Red, 58 FR-R92R-58 Red 1 059,31 € 1 431,50 € 1016,04 01.06.1998 18
709 Mountain Bike Socks, M SO-B909-M White 3,40 € 9,50 € 01.07.2001 30.06.2002 27
710 Mountain Bike Socks, L SO-B909-L White 3,40 € 9,50 € 01.07.2001 30.06.2002 27
713 Long-Sleeve Logo Jersey, S LJ-0192-S Multi 38,49 € 49,99 € 01.07.2001 25
719 HL Road Frame - Red, 48 FR-R92R-48 Red 868,63 € 1 431,50 € 979,75 01.07.2001 18
720 HL Road Frame - Red, 52 FR-R92R-52 Red 868,63 € 1 431,50 € 997,9 01.07.2001 18
721 HL Road Frame - Red, 56 FR-R92R-56 Red 868,63 € 1 431,50 € 1016,04 01.07.2001 18
723 LL Road Frame - Black, 60 FR-R38B-60 Black 204,63 € 337,22 € 1124,9 01.07.2001 18
… … … … … … … … …
Ergebnis
Dr. Isabella Seeber47
Unterabfragen (cont‘d)
• Alle Produkte, die teuer als der durchschnittliche Verkaufspreis sind, ausgeben lassen
SELECT *FROM ProduktWHERE verkaufspreis > (SELECT AVG(verkaufspreis) FROM Produkt);
produktID name produktnummer farbe einkaufspreis verkaufspreis gewicht startverkauf endeverkauf FS_kategorieID
680 HL Road Frame - Black, 58 FR-R92B-58 Black 1 059,31 € 1 431,50 € 1016,04 01.06.1998 18
706 HL Road Frame - Red, 58 FR-R92R-58 Red 1 059,31 € 1 431,50 € 1016,04 01.06.1998 18
717 HL Road Frame - Red, 62 FR-R92R-62 Red 868,63 € 1 431,50 € 1043,26 01.07.2001 18
718 HL Road Frame - Red, 44 FR-R92R-44 Red 868,63 € 1 431,50 € 961,61 01.07.2001 18
719 HL Road Frame - Red, 48 FR-R92R-48 Red 868,63 € 1 431,50 € 979,75 01.07.2001 18
720 HL Road Frame - Red, 52 FR-R92R-52 Red 868,63 € 1 431,50 € 997,9 01.07.2001 18
721 HL Road Frame - Red, 56 FR-R92R-56 Red 868,63 € 1 431,50 € 1016,04 01.07.2001 18
739 HL Mountain Frame - Silver, 42 FR-M94S-42 Silver 747,20 € 1 364,50 € 1233,76 01.07.2001 16
740 HL Mountain Frame - Silver, 44 FR-M94S-44 Silver 706,81 € 1 364,50 € 1251,91 01.07.2001 30.06.2002 16
… … … … … … … … … …
Ergebnis
Dr. Isabella Seeber48
Selbstevaluation
Dr. Isabella Seeber49
Selbstevaluation
Dr. Isabella Seeber50
1) Ein SQL Statement, welches einen JOIN über alle oben angegebenen Tabellen erzeugt
film film_hauptdarsteller hauptdarsteller
SELECT *
FROM film as f, film_hauptdarsteller as fh, hauptdarsteller as h
WHERE
AND
Selbstevaluation
Dr. Isabella Seeber51
3) Ein SQL Statement, welches alle Filme mit „Sylvester Stallone“ ausgibt.
SELECT
FROM
WHERE
AND
Selbstevaluation
Dr. Isabella Seeber52
3) Ein SQL Statement, welches die Anzahl der Auszeichnungen pro Film ausgibt.
SELECT
FROM
WHERE
AND