Aufgabe 1: Fensteranfragen (1 P.)

Datenbanksysteme WS 2015/16
Prof. Dr.-Ing. Sebastian Michel
MSc. Manuel Hoffmann
TU Kaiserslautern, FB Informatik – Lehrgebiet Informationssysteme
Übungsblatt 9: Ausgabe 05.01.2016, Präsentation 12.01.2016
http://dbis.informatik.uni-kl.de
Aufgabe 1: Fensteranfragen
(1 P.)
Beantworten Sie unten stehende Anfragen mit SQL auf der folgenden Tabelle land 1 :
CREATE TABLE land (
lnr VARCHAR (4) PRIMARY KEY ,
name VARCHAR (50) ,
einwohner DECIMAL (20 ,2) , -- in Millionen
flaeche INT ,
hauptstadt VARCHAR (30) ,
kontinent VARCHAR (15) ) ;
1. Geben Sie die Namen aller Länder zusammen mit deren Einwohnerzahlen aus.
2. Geben Sie die Namen aller Kontinente zusammen mit deren Gesamt-Einwohnerzahlen aus.
3. Geben Sie die Namen aller Länder zusammen mit deren Einwohnerzahlen aus sowie dem Namen
des Kontinents, in dem das Land liegt, und wie viele Einwohner insgesamt in diesem Kontinent
liegen.
4. Geben Sie die Namen aller Länder zusammen mit deren Einwohnerzahl aus sowie dem Namen
des Kontinents, in dem das Land liegt, und den weltweiten Rang gemessen an der Einwohnerzahl.
Sortieren Sie die Ausgabe nach dem Rang, sodass z.B. China mit Rang 1 ganz oben steht, weil es
das Land mit weltweit den meisten Einwohnern ist.
5. Wie bei der vorherigen Aufgabe, bloß dass der Rang innerhalb des Kontinents angegeben wird. Nun
stehen also Australien, Brasilien, Deutschland, die vereinigten Staaten, China und Nigeria jeweils
mit Rang 1 ganz oben, weil sie die einwohnerstärksten Länder in ihrem Kontinent sind.
6. Was bedeuten die Werte in der vierten Spalte der folgenden Fensteranfrage?
SELECT name , einwohner , flaeche ,
SUM ( flaeche ) OVER ( ORDER BY einwohner )
FROM land ORDER BY einwohner
1 Auf
der Vorlesungswebseite finden Sie das Tabellenschema samt Beispieldaten zum Download.
1
Datenbanksysteme WS 2015/16
Prof. Dr.-Ing. Sebastian Michel
MSc. Manuel Hoffmann
TU Kaiserslautern, FB Informatik – Lehrgebiet Informationssysteme
Übungsblatt 9: Ausgabe 05.01.2016, Präsentation 12.01.2016
http://dbis.informatik.uni-kl.de
Aufgabe 2: Implementierung von Fensteranfragen
(1 P.)
(a) Geben Sie für die folgende Anfrage über die Relation t(a, b, c) ein äquivalentes SQL-Statement
ohne Fensterfunktionen an:
SELECT a , b ,
AVG ( c ) OVER ( PARTITION BY a ORDER BY b ASC )
FROM t ORDER BY a ;
(b) Beschreiben Sie algorithmisch, wie die folgende Anfrage vom Datenbanksystem direkt ausgewertet
werden kann, d.h., ohne die Anfrage zuvor in eine äquivalente Form zu transformieren:
SELECT a ,
AVG ( c ) OVER ( ORDER BY a ASC
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW )
FROM t ;
Aufgabe 3: Rekursion mit SQL
(1 P.)
Beantworten Sie unten stehende Anfrage mit einer rekursiven SQL-Anfrage auf der folgenden Tabelle
fernbus 1 :
CREATE TABLE fernbus (
busnr INT PRIMARY KEY ,
von VARCHAR (31) ,
nach VARCHAR (31) ,
dauer INT ,
-- in Minuten
preis INT ) ;
-- in Euro
Was ist der günstigste Weg, mit Fernbussen von Kaiserslautern nach Hamburg zu kommen? Die Fahrt
soll insgesamt nicht länger als 1200 Minuten (20h) dauern. Wie oft und wo muss umgestiegen werden?
2