PC(model, speed, ram, hd, rd, price) - Hasso-Plattner

Übung Datenbanksysteme I
SQL
Thorsten Papenbrock
G-3.1.09, Campus III
Hasso Plattner Institut
Einleitung
Virtuelle Maschine
Zugriff:
Kopieren vom Share
Ausführen mit VMware
Zugangsdaten:
Nutzername: db2inst1
Passwort: ws2011
Arbeit mit SQL:
Kommandozeile
IBM Data Studio
Beachte:
VM ist schreibgeschützt
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 2
Übersicht
SQL
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 3
Übersicht
SQL
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
<Attributliste>
<Relationenliste>
<Bedingungen>
<Gruppierungsattribute>
<Bedingungen auf Gruppierungsattribute>
<Attributliste>;
Weitere Schlüsselwörter:
DISTINCT, AS, JOIN
AND, OR
MIN, MAX, AVG, SUM, COUNT
NOT, IN, LIKE, ANY, ALL, EXISTS
UNION, EXCEPT, INTERSECT
…
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 4
Übersicht:
Relationale Übungsschemata
Modellnummer
Prozessorgeschwindigkeit [MHz]
Product(maker, model, type)

Beispieltupel:
Festplattengröße [GB]
(‘Lenovo‘, 1005, ‘pc‘)
PC(model, speed, ram, hd, rd, price)

Beispieltupel:
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
Laptop(model, speed, ram, hd, screen, price)

Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
Printer(model, color, type, price)

Beispieltupel:
Geschwindigkeit
und Typ des
Laufwerks
(3005, true, ‘bubble‘, 200)
Typ (laser, ink-jet, bubble)
Preis [Euro]
Bildschirmauflösung
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 5
Aufgabe:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
PC(model, speed, ram, hd, rd, price)

Beispieltupel:
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
Laptop(model, speed, ram, hd, screen, price)

Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Welche PC-Modelle haben eine Geschwindigkeit von mindestens 1000 MHz?“
model(speed≥1000(PC))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 6
Lösung:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
PC(model, speed, ram, hd, rd, price)
SELECT model
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
FROM PC
Laptop(model,
hd, screen, price)
WHEREspeed,
speed ram,
>= 1000;

Beispieltupel:

Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Welche PC-Modelle haben eine Geschwindigkeit von mindestens 1000 MHz?“
model(speed≥1000(PC))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 7
Aufgabe:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
PC(model, speed, ram, hd, rd, price)

Beispieltupel:
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
Laptop(model, speed, ram, hd, screen, price)

Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Welche Hersteller bauen Laptops mit einer Harddisk von
mindestens 10 GB Größe?“
maker(hd≥10(Product ⋈ Laptop))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 8
Lösung:
Deutsch  SQL
Product(maker,
model, type)
a)
SELECT maker

Beispieltupel:
(‘Lenovo‘,
1005, ‘pc‘)
FROM Product,
Laptop
PC(model,
speed,
ram, hd, rd, price)
WHERE
Product.model
= Laptop.model

Beispieltupel:
AND hd
(1005,
>=
10;1000,
128, 20, ‘12xDVD‘, 1499)
Laptop(model, speed, ram, hd, screen, price)
b)

SELECT
Beispieltupel:
maker
(2008, 650, 64, 10, 12.1, 1249)
FROM Product NATURAL JOIN Laptop
Printer(model, color, type, price)
WHERE hd >= 10;

Beispieltupel:
NATURAL JOIN
geht allerdings
nicht in DB2!
(3005, true, ‘bubble‘, 200)
Anfrage: „Welche Hersteller bauen Laptops mit einer Harddisk von
mindestens 10 GB Größe?“
maker(hd≥10(Product ⋈ Laptop))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 9
Aufgabe:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
PC(model, speed, ram, hd, rd, price)

Beispieltupel:
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
Laptop(model, speed, ram, hd, screen, price)

Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde die Modellnummern aller Farblaserdrucker.“
DBSI - Übung
SQL
model(color=true ∧ type=‘laser‘ (Printer))
ThorstenPapenbrock
Chart 10
Lösung:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
SELECT
PC(model,
speed,model
ram, hd, rd, price)
FROM Printer
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
WHEREspeed,
color=true
Laptop(model,
ram, hd, screen, price)
AND type='laser';
 Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)

Beispieltupel:
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde die Modellnummern aller Farblaserdrucker.“
DBSI - Übung
SQL
model(color=true ∧ type=‘laser‘ (Printer))
ThorstenPapenbrock
Chart 11
Aufgabe:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
PC(model, speed, ram, hd, rd, price)

Beispieltupel:
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
Laptop(model, speed, ram, hd, screen, price)

Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde die Modellnummer und den Preis aller Produkte
(jeden Typs), die von Hersteller ‘Apple‘ gebaut werden.“
model,price(maker=‘Apple‘(Product ⋈ (
model,price(PC)  model,price(Laptop)  model,price(Printer))))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 12
Lösung:
Deutsch  SQL
Product(maker, model, type)
(SELECT Product.model, price
FROM Product
JOIN PC1005,
ON Product.model
= PC.model
Beispieltupel:
(‘Lenovo‘,
‘pc‘)
WHERE maker = 'Apple')
PC(model,
speed, ram, hd, rd, price)
UNION
(SELECT Product.model,
 Beispieltupel:
(1005, 1000,price
128, 20, ‘12xDVD‘, 1499)
FROM Product JOIN Laptop ON Product.model = Laptop.model
WHERE speed,
maker = 'Apple')
Laptop(model,
ram, hd, screen, price)
UNION
 Beispieltupel:
(2008, 650, price
64, 10, 12.1, 1249)
(SELECT Product.model,
FROM Product JOIN Printer ON Product.model = Printer.model
Printer(model,
color, type, price)
WHERE maker = 'Apple');
 Beispieltupel: (3005, true, ‘bubble‘, 200)

Anfrage: „Finde die Modellnummer und den Preis aller Produkte
(jeden Typs), die von Hersteller ‘Apple‘ gebaut werden.“
model,price(maker=‘Apple‘(Product ⋈ (
model,price(PC)  model,price(Laptop)  model,price(Printer))))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 13
Lösung:
Deutsch  SQL
Product(maker, model, type)
 Price
Beispieltupel:
SELECT Price.*
WITH
AS ( (‘Lenovo‘, 1005, ‘pc‘)
(SELECT model,
PC(model,
speed, price
ram,FROM
hd, rd,PC)
price)
FROM Product NATURAL JOIN Price
UNION
 Beispieltupel: (1005, 1000, 128, 20, ‘12xDVD‘, 1499)
WHERE maker = ‘Apple‘;
(SELECT model, price FROM Laptop)
Laptop(model,
speed, ram, hd, screen, price)
UNION
 Beispieltupel:
64, Printer)
10, 12.1, 1249)
(SELECT
model,(2008,
price 650,
FROM
);
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde die Modellnummer und den Preis aller Produkte
(jeden Typs), die von Hersteller ‘Apple‘ gebaut werden.“
model,price(maker=‘Apple‘(Product ⋈ (
model,price(PC)  model,price(Laptop)  model,price(Printer))))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 14
Aufgabe:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
PC(model, speed, ram, hd, rd, price)

Beispieltupel:
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
Laptop(model, speed, ram, hd, screen, price)

Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde alle Hersteller, die Laptops, aber keine PCs herstellen.“
DBSI - Übung
SQL
δ(maker(Product ⋈ Laptop)) – δ(maker(Product ⋈ PC))
ThorstenPapenbrock
Chart 15
Lösung:
Deutsch  SQL
Product(maker, model, type)
(SELECT DISTINCT maker
(‘Lenovo‘, 1005, ‘pc‘)
FROM Product, Laptop
PC(model, speed, ram, hd, rd, price)
WHERE Product.model = Laptop.model)
 Beispieltupel: (1005, 1000, 128, 20, ‘12xDVD‘, 1499)
EXCEPT
Laptop(model, speed, ram, hd, screen, price)
DISTINCTs sind
(SELECT DISTINCT maker
nicht nötig wegen
 Beispieltupel: (2008, 650, 64, 10, 12.1, 1249)
FROM Product, PC
Mengensemantik
Printer(model, color, type, price)
WHERE Product.model = PC.model);
von EXCEPT!

Beispieltupel:

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde alle Hersteller, die Laptops, aber keine PCs herstellen.“
DBSI - Übung
SQL
δ(maker(Product ⋈ Laptop)) – δ(maker(Product ⋈ PC))
ThorstenPapenbrock
Chart 16
Aufgabe:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
PC(model, speed, ram, hd, rd, price)

Beispieltupel:
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
Laptop(model, speed, ram, hd, screen, price)

Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde alle Harddisk-Größen, die in mehr als zwei PCs
vorkommen.“
hd(Anzahl>2(hd,count(model) → Anzahl(PC)))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 17
Lösung:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
SELECT
PC(model,
speed,hd
ram, hd, rd, price)
FROM PC
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
GROUPspeed,
BY hd
Laptop(model,
ram, hd, screen, price)
HAVING(2008,
COUNT(model)
2;
 Beispieltupel:
650, 64, 10, 12.1,>1249)

Beispieltupel:
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde alle Harddisk-Größen, die in mehr als zwei PCs
vorkommen.“
hd(Anzahl>2(hd,count(model) → Anzahl(PC)))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 18
Aufgabe:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
PC(model, speed, ram, hd, rd, price)

Beispieltupel:
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
Laptop(model, speed, ram, hd, screen, price)

Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde alle Paare von PCs mit gleicher Festplatten- und
Hauptspeichergröße. Vermeide dabei doppelte Paare.“
(PC1(PC)) ⋈PC1.HD=PC2.HD ∧ PC1.RAM=PC2.RAM ∧ PC1.model<PC2.model (PC2(PC))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 19
Lösung:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
SELECT *
FROM PC PC1, PC PC2
 Beispieltupel: (1005, 1000, 128, 20, ‘12xDVD‘, 1499)
WHERE PC1.hd = PC2.hd
Laptop(model, speed, ram, hd, screen, price)
AND PC1.ram = PC2.ram
 Beispieltupel: (2008, 650, 64, 10, 12.1, 1249)
AND PC1.model < PC2.model;
PC(model, speed, ram, hd, rd, price)
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde alle Paare von PCs mit gleicher Festplatten- und
Hauptspeichergröße. Vermeide dabei doppelte Paare.“
(PC1(PC)) ⋈PC1.HD=PC2.HD ∧ PC1.RAM=PC2.RAM ∧ PC1.model<PC2.model (PC2(PC))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 20
Aufgabe:
Deutsch  SQL
Product(maker, model, type)

Beispieltupel:
(‘Lenovo‘, 1005, ‘pc‘)
PC(model, speed, ram, hd, rd, price)

Beispieltupel:
(1005, 1000, 128, 20, ‘12xDVD‘, 1499)
Laptop(model, speed, ram, hd, screen, price)

Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
Printer(model, color, type, price)

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde alle Hersteller, die mindestens einen Laptop und einen
PC mit derselben Prozessorgeschwindigkeit anbieten.“
D.a(A.b=B.b ∧ A.a=C.g ∧ B.a=D.g ∧ C.a=D.a (
A(a,b,c,d,e,f)(Laptop)×B(a,b,c,d,e,f)(PC)×C(a,g,h)(Product)×D(a,g,h)(Product)))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 21
Lösung:
Deutsch  SQL
Product(maker,
model, type)
SELECT
D.a
FROM Beispieltupel: (‘Lenovo‘, 1005, ‘pc‘)
(SELECT model AS a, speed AS b, ram AS c, hd AS d, screen AS e, price AS f FROM Laptop) A,
PC(model, speed, ram, hd, rd, price)
(SELECT model AS a, speed AS b, ram AS c, hd AS d, rd AS e, price AS f FROM PC) B,
 Beispieltupel:
1499)
(SELECT
maker AS a,(1005,
model1000,
AS g,128,
type20,
AS ‘12xDVD‘,
h FROM Product)
C,
(SELECT maker AS a, model AS g, type AS h FROM Product) D
Laptop(model, speed, ram, hd, screen, price)
WHERE A.b = B.b
 Beispieltupel:
(2008, 650, 64, 10, 12.1, 1249)
AND A.a
= C.g
AND
B.a = D.g
Printer(model,
color, type, price)
AND C.a = D.a;

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde alle Hersteller, die mindestens einen Laptop und einen
PC mit derselben Prozessorgeschwindigkeit anbieten.“
D.a(A.b=B.b ∧ A.a=C.g ∧ B.a=D.g ∧ C.a=D.a (
A(a,b,c,d,e,f)(Laptop)×B(a,b,c,d,e,f)(PC)×C(a,g,h)(Product)×D(a,g,h)(Product)))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 22
Lösung:
Deutsch  SQL
Product(maker, model, type)
SELECT (‘Lenovo‘,
D.maker1005, ‘pc‘)
FROM
Laptop
A, PC
Product C, Product D
PC(model,
speed,
ram, hd,
rd, B,
price)
WHERE (1005,
A.speed
B.speed
 Beispieltupel:
1000, =
128,
20, ‘12xDVD‘, 1499)
AND A.model
= C.model
Laptop(model,
speed, ram,
hd, screen, price)
AND B.model
= D.model
 Beispieltupel:
(2008, 650,
64, 10, 12.1, 1249)
AND C.maker
= D.maker;
Printer(model,
color, type,
price)

Beispieltupel:

Beispieltupel:
(3005, true, ‘bubble‘, 200)
Anfrage: „Finde alle Hersteller, die mindestens einen Laptop und einen
PC mit derselben Prozessorgeschwindigkeit anbieten.“
D.a(A.b=B.b ∧ A.a=C.g ∧ B.a=D.g ∧ C.a=D.a (
A(a,b,c,d,e,f)(Laptop)×B(a,b,c,d,e,f)(PC)×C(a,g,h)(Product)×D(a,g,h)(Product)))
DBSI - Übung
SQL
ThorstenPapenbrock
Chart 23
Übung Datenbanksysteme I
SQL
Thorsten Papenbrock
G-3.1.09, Campus III
Hasso Plattner Institut