Ubung Datenbanksysteme I - Praktische Informatik

Universität Siegen
Fachgruppe Praktische Informatik
Übung Datenbanksysteme I
Blatt 5 – Ausgabe am 23.11.2015, Abgabe der Lösung bis 29.11.2015
Aufgabe 5.1
Abfragen in SQL
[ mittel / Klausur-Bearbeitungszeit ca. 20 Min.; tatsächlich:
[ einfach / Klausur-Bearbeitungszeit ca. 22 Min.; tatsächlich:
; vorbereitende Lektüre:
]
; vorbereitende Lektüre:
Benutzen Sie für die folgenden Aufgaben Ihre in
5.2 eingespielte BankenDB und geben Sie Ihre Anfragen als Lösung ab. (Nur die Anfragen - nicht die
Ergebnisse!). Die SQL-Ausdrücke sollen wie zuvor in
einer separaten SQL-Datei abgegeben werden.
Formulieren Sie die folgenden Anfragen auf den
Tabellen fahrzeuge, vertreter, produkte und
verkauft der vorigen Aufgabenblätter in SQL.
Die SQL-Ausdrücke sollen in einer separaten
SQL-Datei abgegeben werden (Das ist eine Textdatei mit der Endung *.sql). Innerhalb der sql-Datei
schreiben Sie bitte in Kommentaren ihre Namen/Matrikelnummern und Kennzeichnen auch zu jedem
Ausdrück, um welche Aufgabennummer bzw. Teilaufgabe es sich handelt. Kommentare können in SQL so
verfasst werden: /* Aufgabe 5.1 a) */
a. Ermitteln Sie die Nachnamen aller Kunden, die
eine Bonität von 1 haben.
b. Ermitteln Sie die Namen aller Orte im Postleitzahlgebiet 6.
c. Ermitteln Sie die Vor- und Nachnamen aller
Kunden, die im PLZ-Gebiet 4 wohnen.
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 Personen-Nummer
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.
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?
f. Zeige die Personal-Nummern, Vor- und Nachnamen aller Verteter, die eine Personal-Nummer
kleiner 2000 haben.
g. Wie viele km hat Herr Müllers Wagen gefahren?
h. Welche Produkte (Bezeichn, ArtikelNr) werden von dem Vertreter Müller verkauft?
i. Welche Vertreter (Vorname, Name) verkaufen
Produkte, die teurer als 500,00 EUR sind?
j. Zeige Namen und Umsatz aller Vertreter an, die
das Produkt ‘EasyHoover’ verkaufen.
k. Welche Produkte verkauft der Vertreter, dessen
Wagen die Fahrgestell-Nummer 2735BAH hat?
Vertiefungsaufgabe 5.4
SQL-Abfragen lesen
[ mittel / Klausur-Bearbeitungszeit ca. 10 Min.; tatsächlich:
; vorbereitende Lektüre:
]
Die folgenden Anfragen wurden auf der BankenDB ausgeführt, die mit den im Anhang angegebenen CREATE TABLE-Anweisungen eingerichtet wurde.
(Sie haben diese in Aufgabe 5.2 eingespielt.)
Wie lauten die Arbeitsaufträge, die der Programmierer dieser Anfragen erhalten hat?
Aufgabe 5.2
Einrichten von SQLite und Einspielen der BankenDB
a.
SELECT
FROM
WHERE
AND
GROUP BY
ORDER BY
AVG(saldo) AS durchschnitt, ort.plz
person, konto, ort
person.plz = ort. plz
person.pers nr = konto.pers nr
ort.plz
durchschnitt desc
b.
SELECT
person.vorname, person.nachname,
ort.name, SUM(aktien position.anzahl)
aktien position, depot, person, ort
depot.pers nr = person.pers nr
aktien position.depot nr
= depot.depot nr
person.plz = ort.plz
aktien position.aktien name
= ’MercST European Opps’
person.pers nr, person.vorname,
person.nachname, ort.name
SUM(aktien position.anzahl) > 0
[ einfach / Klausur-Bearbeitungszeit ca. 0 Min.; tatsächlich:
; vorbereitende Lektüre:
]
Wir werden für die praktischen Übungen SQLite und DBeaver verwenden. Installieren Sie SQLite
und DBeaver und spielen Sie die BankenDB, die
Ihnen über die WWW-Seiten zu den Übungen zur
Verfügung steht, ein. Ein Tutorial, das Sie bei dieser Aufgabe unterstützen soll, steht ebenfalls dort zur
Verfügung. Sie dürfen natürlich statt DBeaver auch
andere Tools verwenden.
Aufgabe 5.3
]
FROM
WHERE
AND
AND
AND
GROUP BY
HAVING
SQL-Abfragen schreiben
1
. . . . . . . . . . Tabellen der BankenDB . . . . . . . . . .
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 ort (
plz int ,
name varchar (20) not null ,
bu_land varchar (25) not null ,
primary key ( plz )
);
);
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 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 aktie (
name varchar (50) , kurs real ,
primary key ( name )
);
CREATE TABLE a kt ie n_ p os it io n (
pos_nr int ,
depot_nr int ,
aktien_name varchar (50) ,
anzahl int default 0 ,
primary key ( pos_nr , depot_nr ) ,
foreign key ( aktien_name )
references aktie ( name ) ,
foreign key ( depot_nr )
references depot ( depot_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 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 )
references
a kt ie n_ p os it io n ( pos_nr , depot_nr ) ,
foreign key ( kto_pers_nr , kto_nr )
references konto ( pers_nr , kto_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 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 )
references
a kt ie n_ p os it io n ( pos_nr , depot_nr ) ,
foreign key ( kto_pers_nr , kto_nr )
references konto ( pers_nr , kto_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 (
ID integer primary key autoincrement ,
gebucht int (1) not null default ’0 ’ ,
von_pers_nr int ,
von_kto_nr int ,
an_pers_nr int ,
an_kto_nr int ,
v e r w e n d u n g s z w ec k varchar (27) ,
2