Ubung Datenbanksysteme I - Praktische Informatik

Universität Siegen
Fachgruppe Praktische Informatik
Übung Datenbanksysteme I
Blatt 6 — Ausgabe am 21.11.2011, Abgabe der Lösungen bis 26.11.2011
Aufgabe 6.1:
Abfragen in SQL
[ einfach / Klausur-Bearbeitungszeit ca. 25 Min.;
tatsächlich:
; vorbereitende Lektüre:
]
Formulieren Sie die folgenden Anfragen auf den
Tabellen fahrzeuge, vertreter, produkte und
verkauft der vorigen Aufgabenblätter in SQL.
a) Welche Fahrzeuge werden mit Diesel betrieben?
b) Welche Vertreter machen mehr als 30000
EUR Jahresumsatz?
c) Welche Diesel-Fahrzeuge haben mehr als
30000 km?
d) Welche Fahrzeuge wurden vor 2001 angemeldet oder haben mehr als 30000 km?
e) Welche PLZ-Gebiete werden zur Zeit betreut?
a)
SELECT AVG(saldo) AS durchschnitt, ort.plz
FROM person, konto, ort
WHERE person.plz = ort.plz
AND person.pers_nr = konto.pers_nr
GROUP BY ort.plz
ORDER BY durchschnitt desc
b)
SELECT person.vorname, person.nachname,
ort.name, SUM(aktien_position.anzahl)
FROM aktien_position, depot, person, ort
WHERE depot.pers_nr = person.pers_nr
AND aktien_position.depot_nr =
depot.depot_nr
AND person.plz = ort.plz
AND aktien_position.aktien_name =
’MercST European Opps’
GROUP BY person.pers_nr, person.vorname,
person.nachname, ort.name
HAVING SUM(aktien_position.anzahl) > 0
f) Zeige die Personal-Nummern, Vor- und
Nachnamen aller Vertreter mit einer
Personal-Nummer kleiner 2000 an.
Aufgabe 6.3:
g) Wie viele km hat Herr Müllers Wagen?
h) Welche Produkte (Bezeichn, ArtikelNr)
werden von Vertreter Müller verkauft?
Benutzen Sie für die folgenden Aufgaben die
Übungsdatenbank, die über die WWW-Seite zu
den Übungen erreichbar ist.
i) Welche Vertreter (Vorname, Name) verkaufen
Produkte, die teurer als 500,00 sind?
a) Ermitteln Sie die Nachnamen aller Kunden,
die eine Bonitaet von 1 haben.
j) Zeige Name und Umsatz aller Vertreter an,
die das Produkt ’EasyHoover’ verkaufen.
b) Ermitteln Sie alle Orte im Postleitzahlgebiet
6.
k) Welche Produkte verkauft der Vertreter, dessen Wagen die Fahrgestell-Nummer 2735BAH
hat?
c) Ermitteln Sie die Vor- und Nachnamen aller
Kunden, die im PLZ-Gebiet 6 wohnen.
Aufgabe 6.2:
SQL-Abfragen lesen
[ mittel / Klausur-Bearbeitungszeit ca. 10 Min.;
tatsächlich:
; vorbereitende Lektüre:
]
Über die WWW-Seite zu den Übungen (und
über die WWW-Seite zum Modul SQL1) erreichen Sie ein Formular, mit dem man Abfragen auf einer Beispieldatenbank ausführen kann.
Die Datenbank wurde mit den im Anhang angegebenen CREATE TABLE-Anweisungen eingerichtet.
Beschreiben Sie verbal, welches Ergebnis folgende Abfragen auf den obigen Tabellen liefern.
SQL-Abfragen schreiben
[ mittel / Klausur-Bearbeitungszeit ca. 20 Min.;
tatsächlich:
; vorbereitende Lektüre:
]
d) Ermitteln Sie die Personen-Nummer des Mitarbeiters mit dem Nachnamen Porta
e) Ermitteln Sie die Nachnamen aller Kunden,
die von dem Mitarbeiter mit der PersonenNummer 10001 betreut werden.
f) Ermitteln Sie die Nachnamen aller Kunden,
die von einem Mitarbeiter mit Nachnamen
Porta betreut werden.
Hinweis: Personen tauchen hier zweimal in
der FROM-Klausel auf.
Tabellen-Schemata für SQL-Aufgabe
CREATE TABLE ort (
plz
int,
name
varchar(20) not null,
bu_land
varchar(25) not null,
primary key(plz) );
CREATE TABLE person (
pers_nr
int,
plz
int,
anrede
varchar(5),
vorname
varchar(30),
nachname
varchar(30) not null,
strasse
varchar(30) not null,
hausnummer int not null,
primary key(pers_nr) );
CREATE TABLE mitarbeiter (
pers_nr
int,
arbeitszeit int default 100,
status
varchar(10),
foreign key (pers_nr)
references person (pers_nr) );
CREATE TABLE kunde (
pers_nr
int,
bonitaet
int default 2,
betreuer
int
references mitarbeiter(pers_nr),
foreign key(pers_nr)
references person(pers_nr) );
CREATE TABLE konto (
pers_nr
int,
kto_nr
int,
saldo
real,
kredit_limit real,
primary key (pers_nr, kto_nr),
foreign key (pers_nr)
references person (pers_nr) );
CREATE TABLE einzahlung (
pers_nr
int,
kto_nr
int,
betrag
real not null,
datum
date default current_date,
foreign key (pers_nr, kto_nr)
references konto (pers_nr, kto_nr) );
CREATE TABLE auszahlung (
pers_nr
int,
kto_nr
int,
betrag
real not null,
datum
date default current_date,
foreign key (pers_nr, kto_nr)
references konto (pers_nr, kto_nr) );
CREATE TABLE ueberweisung (
von_pers_nr int,
von_kto_nr int,
an_pers_nr int,
an_kto_nr
int,
verwendungszweck varchar(27),
betrag
real not null,
datum
date default current_date,
foreign key (von_pers_nr, von_kto_nr)
references konto (pers_nr, kto_nr),
foreign key (an_pers_nr, an_kto_nr)
references konto (pers_nr, kto_nr) );
CREATE TABLE depot (
pers_nr
int,
depot_nr
int,
primary key (pers_nr, depot_nr),
foreign key (pers_nr)
references person (pers_nr) );
CREATE TABLE aktie (
name
varchar(50) primary key,
kurs
real );
CREATE TABLE aktien_position (
pos_nr
int,
depot_nr
int,
aktien_name varchar(50),
anzahl
int default 0,
primary key (pos_nr),
foreign key (aktien_name)
references aktie (name),
foreign key (depot_nr)
references depot (depot_nr) );
CREATE TABLE kauf (
depot_pers_nr int,
depot_nr
int,
pos_nummer int,
kto_pers_nr int,
kto_nr
int,
anzahl
int NOT NULL,
datum
date default current_date,
foreign key (depot_pers_nr, depot_nr,
pos_nummer) references
aktien_position (pers_nr, depot_nr, nummer),
foreign key (kto_pers_nr, kto_nr)
references konto (pers_nr, kto_nr) );
CREATE TABLE verkauf (
depot_pers_nr int,
depot_nr
int,
pos_nummer int,
kto_pers_nr int,
kto_nr
int,
kurs
real,
anzahl
int not null,
datum
date default current_date,
foreign key (depot_pers_nr, depot_nr,
pos_nummer) references
aktien_position (pers_nr, depot_nr, nummer),
foreign key (kto_pers_nr, kto_nr)
references konto (pers_nr, kto_nr) );