Datenbanken

Datenbanken – Übung – Ü00
SQL Abf
SQL-Abfragen
Dr.-Ing. Peter Steininger
Steinbuch Centre for Computing (SCC)
www.kit.edu
KIT – die Kooperation von Forschungszentrum Karlsruhe GmbH und Universität Karlsruhe (TH)
Datenbankschema – Supermarkt
tbl_Waren
tbl_Verkauf
Spaltenname
Datentyp
NULL-Werte zulassen
Spaltenname
Datentyp
id
bigint
id
bigint
strWarenName
nvarchar(100)
dtDate
datetime
mPreis
money
fk_Waren
bigint
fk_Warengruppe
bigint
intMenge
int
NULL-Werte zulassen
tbl_warengruppe
Spaltenname
Datentyp
id
bigint
strWarengruppenName
nvarchar(50)
13709030 DBMS
Dr. Peter Steininger
NULL-Werte zulassen
20090405
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
1
Supermarkt – Tabellendaten I/II
tbl_Waren
id
----------1
2
3
4
5
6
7
strWarenName
----------------------------Papiertaschentücher
Erdbeer
Erbsen
Bohnen
Elmex
Birne
Apfel
mPreis
--------------------1,99
0,99
1,59
2,35
3,12
3,34
1,29
fk_Warengruppe
-------------1
2
6
6
5
1
2
tbl_Verkauf
id
----------1
2
3
4
5
6
7
13709032 DBMS
dtDate
----------------------2009-01-10 19:47:29.227
2009-01-10 19:47:36.680
2009-01-10 19:47:46.403
2009-01-10 19:47:54.440
2009-01-10 19:48:04.110
2009-01-10 19:48:13.113
2009-01-10 19:48:24.153
Dr. Peter Steininger
fk_Waren
----------3
2
7
3
2
5
1
intMenge
----------2
5
10
2
10
2
3
20090405
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
Supermarkt – Tabellendaten II/II
tbl_Warengruppe
id
----------1
2
3
4
5
6
13709033 DBMS
strWarengruppenName
-------------------------------------------------Papier
Obst
Marmelade
Honig
Zahnpasta
Tiefkühlprodukt
Dr. Peter Steininger
20090405
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
2
Datenbankschema – Supermarkt (erweitert)
tbl_Waren
tbl_Verkauf
Spaltenname
Datentyp
NULL-Werte zulassen
Spaltenname
Datentyp
id
bigint
id
bigint
strWarenName
tW
N
nvarchar(100)
h (100)
dtD t
dtDate
d t ti
datetime
mPreis
money
fk_Waren
bigint
fk_Warengruppe
bigint
intMenge
int
fk_Mitarbeiter
bigint
tbl_warengruppe
tbl_Mitarbeiter
Spaltenname
Datentyp
NULL-Werte zulassen
Spaltenname
Datentyp
id
bi i t
bigint
id
bi i t
bigint
strWarengruppenName
nvarchar(50)
StrVerkaeufer
nchar(100)
13709031 DBMS
NULL-Werte zulassen
Dr. Peter Steininger
NULL-Werte zulassen
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
20090405
Supermarkt (erweitert) – Tabellendaten I/II
tbl_Verkauf
id
----------1
2
3
4
5
6
7
dtDate
----------------------2009
01 10 19:47:29.227
2009-01-10
2009-01-10 19:47:36.680
2009-01-10 19:47:46.403
2009-01-10 19:47:54.440
2009-01-10 19:48:04.110
2009-01-10 19:48:13.113
2009-01-10 19:48:24.153
fk_Waren
----------3
2
7
3
2
5
1
intMenge
----------2
5
10
2
10
2
3
fk_Mitarbeiter
-------------101
103
100
105
100
101
102
tbl_Mitarbeiter
id
----------100
101
102
103
104
105
13709034 DBMS
strVerkaeufer
-------------------------------------------------Claudia
Hans
Dieter
Maria
Simone
Elly
Dr. Peter Steininger
20090405
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
3
SQL-Abfragen schreiben I/V
Ausgabe der Produkte, Preise und der Warengruppe sortiert nach Preis, wie im Bsp.:
Produkt
Preis
Warengruppe
------------------------------------------ -------------------------------------Birne
3,34
3 34
Papier
Elmex
3,12
Zahnpasta
Bohnen
2,35
Tiefkühlprodukt
Papiertaschentücher
1,99
Papier
Erbsen
1,59
Tiefkühlprodukt
Apfel
1,29
Obst
Erdbeer
0,99
Obst
13709035 DBMS
Dr. Peter Steininger
20090405
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
SQL-Abfragen schreiben I/V – Lösung
select
tbl_Waren.strWarenName as Produkt,
tbl_Waren.mPreis as Preis,
tbl Warengruppe.strWarengruppenName as Warengruppe
from tbl_Waren, tbl_Warengruppe
where tbl_Waren.fk_Warengruppe = tbl_Warengruppe.id
order by mPreis desc
13709040 DBMS
Dr. Peter Steininger
20090531
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
4
SQL-Abfragen schreiben II/V
Ausgabe der Warengruppen mit deren Umsätzen, sortiert nach Warengruppen, wie im Bsp.:
strWarengruppenName
-------------------------------------------------Obst
Papier
Tiefkühlprodukt
Zahnpasta
13709036 DBMS
Dr. Peter Steininger
Umsatz
--------------------27,75
27 75
5,97
6,36
6,24
20090405
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
SQL-Abfragen schreiben II/V – Lösung
select
tbl_Warengruppe.strWarengruppenName,
sum(tbl_Verkauf.intMenge*tbl_Waren.mPreis) as Umsatz
from tbl_Warengruppe, tbl_Verkauf, tbl_Waren
where tbl_Warengruppe.id = tbl_Waren.fk_Warengruppe
and tbl_Verkauf.fk_Waren = tbl_Waren.id
group by tbl_Warengruppe.strWarengruppenName
order by tbl_Warengruppe.strWarengruppenName
13709041 DBMS
Dr. Peter Steininger
20090531
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
5
SQL-Abfragen schreiben III/V
Die Filialleitung will wissen welche Artikelgruppen noch nicht verkauft wurden. Schreiben Sie die
SQL-Anweisung, die folgendes Ergebnis liefert:
Warengruppen ohne Umsatz
-------------------------------------------------Honig
Marmelade
13709037 DBMS
Dr. Peter Steininger
20090405
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
SQL-Abfragen schreiben III/V – Lösung I/II
select
tbl_Warengruppe.strWarengruppenName as 'Warengruppen ohne Umsatz'
from tbl Warengruppe
except
select
distinct tbl_Warengruppe.strWarengruppenName
from tbl_Verkauf
join tbl_Waren on tbl_Verkauf.fk_Waren = tbl_Waren.id
join tbl_Warengruppe on tbl_Waren.fk_Warengruppe = tbl_Warengruppe.id
13709042 DBMS
Dr. Peter Steininger
20090531
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
6
SQL-Abfragen schreiben III/V – Lösung II/II
select
tbl_Warengruppe.strWarengruppenName
from tbl Warengruppe
where tbl_Warengruppe.strWarengruppenName not in
(
select
tbl_Warengruppe.strWarengruppenName
from tbl_Waren
join tbl_Verkauf on tbl_Waren.id = tbl_Verkauf.fk_Waren
join tbl_Warengruppe on tbl_Waren.fk_Warengruppe = tbl_Warengruppe.id
group by tbl_Warengruppe.strWarengruppenName
)
13709043 DBMS
Dr. Peter Steininger
20090531
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
SQL-Abfragen schreiben IV/V
Schreiben Sie eine SQL-Anweisung, die alle Verkäufe in folgender Art ausgibt:
intMenge
----------2
5
10
2
10
2
3
13709038 DBMS
strWarenName
mPreis
Warenwert
----------------------------------------- --------------------Erbsen
1,59
3,18
1 59
3 18
Erdbeer
0,99
4,95
Apfel
1,29
12,90
Erbsen
1,59
3,18
Erdbeer
0,99
9,90
Elmex
3,12
6,24
Papiertaschentücher 1,99
5,97
Dr. Peter Steininger
20090405
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
7
SQL-Abfragen schreiben IV/V – Lösung
select
tbl_Verkauf.intMenge,
tbl_Waren.strWarenName,
tbl Waren.mPreis,
(tbl_Waren.mPreis * tbl_Verkauf.intMenge) as Warenwert
from tbl_Verkauf
join tbl_Waren on tbl_Verkauf.fk_Waren = tbl_Waren.id
13709044 DBMS
Dr. Peter Steininger
20090531
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
SQL-Abfragen schreiben V/V
Schreiben Sie eine SQL-Anweisung, die für einen vorgegebenen Mitarbeiter alle Verkäufe wie im
folgenden Beispiel ausgibt:
strWarenName
strWarengruppenName
intMenge
------------------------------------------------------------------ ----------Apfel
Obst
10
Erdbeer
Obst
10
13709039 DBMS
Dr. Peter Steininger
20090405
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
8
SQL-Abfragen schreiben V/V – Lösung
select
tbl_Waren.strWarenName,
tbl_Warengruppe.strWarengruppenName,
tbl Verkauf.intMenge
from tbl_Verkauf
join tbl_Mitarbeiter on tbl_Verkauf.fk_Mitarbeiter = tbl_Mitarbeiter.id
join tbl_Waren on tbl_Waren.id = tbl_Verkauf.fk_Waren
join tbl_Warengruppe on tbl_Warengruppe.id = tbl_Waren.fk_Warengruppe
where tbl_Mitarbeiter.strVerkaeufer = 'MITARBEITER';
13709045 DBMS
Dr. Peter Steininger
20090531
KIT - die Kooperation von
Forschungszentrum Karlsruhe GmbH
und Universität Karlsruhe (TH)
9