Solutions for Assignment 5

University of Konstanz
Databases & Information Systems Group
Prof. M. H. Scholl / Jens Teubner / Svetlana Vinnik
Information Systems
Solutions
Winter 2003/04
Solutions for Assignment 5
Exercise 1
SQL
-- (a)
SELECT part_id, name, piece_wght
FROM Parts
WHERE piece_wght > 20;
-- (b)
SELECT OrderPositions.order_id, Parts.part_id
FROM OrderPositions
WHERE OrderPositions.order_id > 10000;
-- (c)
SELECT DISTINCT Orders.order_id, Orders.order_date, Orders.customer
FROM Orders, OrderPositions, Parts
WHERE Orders.order_id = OrderPositions.order_id
AND OrderPositions.part_id = Parts.part_id
AND Parts.piece_wght > 20;
-- alternativ
SELECT order_id, order_date, customer
FROM Orders
WHERE EXISTS (SELECT *
FROM OrderPositions, Parts
WHERE OrderPositions.order_id = Orders.order_id
AND OrderPositions.part_id = Parts.part_id
AND Parts.piece_wght > 20);
-- (d)
SELECT order_id, order_date, customer
FROM Orders
EXCEPT
SELECT Orders.order_id, Orders.order_date, Orders.customer
FROM Orders, OrderPositions, Parts
WHERE Orders.order_id = OrderPositions.order_id
AND OrderPositions.part_id = Parts.part_id
AND Parts.piece_wght > 20;
-- alternativ
SELECT order_id, order_date, customer
FROM Orders
WHERE NOT EXISTS (SELECT *
FROM OrderPositions, Parts
WHERE OrderPositions.order_id = Orders.order_id
AND OrderPositions.part_id = Parts.part_id
AND Parts.piece_wght > 20);
-- (e)
9 Pts
SELECT Orders.order_id, COUNT(*) AS num_pos
FROM Orders, OrderPositions
WHERE Orders.order_id = OrderPositions.order_id
AND Orders.customer = 4711
GROUP BY Orders.order_id;
-- (f)
SELECT Orders.order_id, SUM(OrderPositions.amount * Parts.piece_wght)
FROM Orders, OrderPositions, Parts
WHERE Orders.order_id = OrderPositions.order_id
AND OrderPositions.part_id = Parts.part_id
AND Orders.customer = 4711
GROUP BY Orders.order_id;
Exercise 2
Library Database
a) SELECT notation
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort = ’ALGORITHMUS’
b) SELECT notation
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort LIKE ’INFORM%’
c) SELECT notation
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND (Schlagwort.suchwort = ’INFORMATIK’
OR Schlagwort.suchwort = ’ENTSCHEIDUNGSTHEORIE’)
oder
SELECT notation
FROM Systemstelle
WHERE EXISTS ( SELECT *
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort = ’INFORMATIK’
AND SW_Syst.notation = Systemstelle.notation )
OR EXISTS ( SELECT *
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort = ’ENTSCHEIDUNGSTHEORIE’
AND SW_Syst.notation = Systemstelle.notation )
d) SELECT notation
FROM Systemstelle
WHERE EXISTS ( SELECT *
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort = ’INFORMATIK’
AND SW_Syst.notation = Systemstelle.notation )
AND EXISTS ( SELECT *
6 Pts
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort = ’ENTSCHEIDUNGSTHEORIE’
AND SW_Syst.notation = Systemstelle.notation )
e) SELECT notation
FROM Systemstelle
WHERE EXISTS ( SELECT *
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort = ’ALGORITHMUS’
AND SW_Syst.notation = Systemstelle.notation )
AND NOT EXISTS ( SELECT *
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort = ’LEHRBUCH’
AND SW_Syst.notation = Systemstelle.notation )
f) SELECT notation
FROM Systemstelle
WHERE ( EXISTS ( SELECT *
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort = ’ALGORITHMUS’
AND SW_Syst.notation = Systemstelle.notation )
AND EXISTS ( SELECT *
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort = ’LEHRBUCH’
AND SW_Syst.notation = Systemstelle.notation )
)
OR EXISTS ( SELECT *
FROM SW_Syst, Schlagwort
WHERE SW_Syst.sw_id = Schlagwort.sw_id
AND Schlagwort.suchwort = ’ENTSCHEIDUNGSTHEORIE’
AND SW_Syst.notation = Systemstelle.notation )
Exercise 3
Query Unnesting
5 Pts
Die Query ist im Prinzip nur ein Join zwischen den drei Relationen. Sie laesst sich als korrelierte oder
unkorrelierte Subquery ausdruecken, oder aber auch ganz ohne Subqueries.