Lösungsvorschlag

Introduction to
Data and Knowledge Engineering
Sommersemester 2015
Robert Rehner
Tutorium 6: SQL
Lösungsvorschlag
Aufgabe T-6.1: Multiple Choice
Gegeben ist folgende SQL-Anfrage für die Tabellen A und B: SELECT * FROM A,B
Welche der folgenden Operationen wird hier ausgeführt?
o
o

o
o
o
Equijoin
Left Join
Kartesisches Produkt
Full Outer Join
Natural Join
Right Join
Begründung:
SELECT * FROM A, B kann auch geschrieben werden als SELECT A.*, B.* FROM A, B Dabei werden
also alle Spalten der Tabellen A und B in die Ergebnistabelle projiziert und keine weiteren
Selektionsbedingungen angewendet.
Um ein Equijoin zu erhalten muss zusätzlich in der WHERE-Klausel bestimmt werden, welche Spalten
gleich sein müssen. Bsp.: SELECT * FROM A, B WHERE A.id = B.id
Aufgabe T-6.2: SQL als DDL
Die Datenbank eines Schuh-Versandhauses besteht aus verschiedenen Tabellen. Für vier von ihnen
sind im Folgenden die Relationen-Schemata angegeben. Weitere Relationen wie etwa Kunde oder
Warenkorb gehören ebenfalls dazu, werden hier aber ausgelassen.
Hersteller
Name
PK
X
FK
Land
Stadt
Lager
ProduktName Größe Anzahl
PK
X
X
FK Produkt.Name
Stand: 30.04.2015 16:21
Produkt
Name
PK
X
FK
Hersteller
Preis
Hersteller.Name
Bestellposten
Bestellung Name
PK
X
X
FK
Produkt.Name
Größe Anzahl
X
1
a) Legen Sie mittels SQL Tabellen für diese vier Schemata an. Überlegen Sie selbst welche
Wertebereiche die Attribute jeweils erhalten.
Lösungsvorschlag:
CREATE TABLE Hersteller (
Name varchar(20) NOT NULL,
Land varchar(20) NOT NULL,
Stadt varchar(20) NOT NULL,
PRIMARY KEY (Name)
);
CREATE TABLE Produkt (
Name varchar(20) NOT NULL,
Hersteller varchar(20) NOT NULL,
Preis decimal(6,2) NOT NULL,
PRIMARY KEY (Name),
FOREIGN KEY ( Hersteller ) REFERENCES Hersteller ( Name )
);
CREATE TABLE Lager (
ProduktName varchar(20) NOT NULL,
Groesse int NOT NULL, Anzahl int NOT NULL,
PRIMARY KEY (ProduktName,Groesse),
FOREIGN KEY ( ProduktName ) REFERENCES Produkt( Name )
);
CREATE TABLE Bestellposten (
Bestellung int NOT NULL, Name varchar(20) NOT NULL,
Groesse int NOT NULL, Anzahl int NOT NULL,
PRIMARY KEY (Bestellung,Name,Groesse),
CONSTRAINT fk_ProduktName FOREIGN KEY ( Name ) REFERENCES
Produkt( Name )
);
Hinweis: Die Klausel CONSTRAINT vor FOREIGN KEY ist optional und erlaubt eine interne
Benennung des Fremdschlüssels (genauer gesagt sind Fremndschlüssel eine spezielle Form von
Constraints und man benennt den Constraint).
Stand: 30.04.2015 16:21
2
b) Entfernen Sie die Attribute des Primary-Key der Tabelle Bestellposten und legen Sie sie
anschließend wieder an
Lösungsvorschlag:
FK entfernen, PK entfernen, Attribute entfernen:
ALTER TABLE Bestellposten DROP FOREIGN KEY fk_ProduktName
ALTER TABLE Bestellposten DROP PRIMARY KEY
ALTER TABLE Bestellposten DROP COLUMN Bestellung
ALTER TABLE Bestellposten DROP COLUMN Name
ALTER TABLE Bestellposten DROP COLUMN Groesse
Attribute, PK, FK wieder anlegen:
ALTER TABLE Bestellposten ADD Bestellung INT NOT NULL
ALTER TABLE Bestellposten ADD Name VARCHAR( 20 ) NOT NULL
ALTER TABLE Bestellposten ADD Groesse INT NOT NULL
ALTER TABLE Bestellposten ADD PRIMARY KEY ( Bestellung , Name , Groesse)
ALTER TABLE Bestellposten ADD CONSTRAINT fk_ProduktName FOREIGN
KEY ( Name ) REFERENCES Produkt( Name )
Hinweis: ADD … NOT NULL ohne DEFAULT ist eigentlich nicht erlaubt (welcher Wert soll denn sonst
in den hinzugefügten „Zellen“ stehen?). Da es sich allerdings um das Hinzufügen des Primärschlüssels
handelt, muss man davon ausgehen, dass die Tabelle leer ist (sonst könnte man nämlich auch nicht
NULL Werte einfügen oder mit DEFAULT arbeiten).
Stand: 30.04.2015 16:21
3
Aufgabe T-6.3: SQL als DML
Die angelegten Tabellen des Online-Schuhgeschäfts enthalten nun folgende Daten:
Hersteller
Name
Bally
Camper
Reebok
Mephisto
Land
Schweiz
Spanien
USA
Frankreich
Lager
ProduktName
Kaisy 8200
Kaisy 8200
Evaluate Trainer
Evaluate Trainer
CA 226130990
Dianetten
Dianetten
Edesio 1965
Edesio 1965
Edesio 1965
Paris Runner
Delarosa
Delarosa
Delarosa
Größe
37
38
41
43
39
45
44
41
42
43
42
36
37
38
Stadt
Caslano
Inca
Canton MA
Sarrebourg
Anzahl
9
16
47
11
28
22
42
57
63
74
7
66
87
85
Produkt
Name
Kaisy 8200
Evaluate Trainer
CA 226130990
Dianetten
Edesio 1965
Paris Runner
CA 22225090
Delarosa
Bestellposten
Bestellung
3297
3297
7982
7982
Name
Evaluate Trainer
Dianetten
Kaisy 8200
Delarosa
Hersteller
Mephisto
Reebok
Camper
Bally
Mephisto
Reebok
Camper
Bally
Preis
99,90
49,95
135,00
220,00
135,00
74,95
139,90
350,00
Größe
43
44
37
37
Anzahl
1
1
1
1
Formulieren Sie in SQL folgende Anfragen:
a) Alle Produkte des Herstellers Reebok mit der Schuhgröße 42
SELECT P.* FROM Produkt P, Lager L WHERE L.ProduktName = P.Name AND P.Hersteller =
’Reebok’ AND L.Größe = 42
b) Schuhgrößen aller Produkte, von denen weniger als 12 Exemplare im Lager vorhanden sind.
SELECT DISTINCT Größe FROM Lager WHERE Anzahl < 12
c) Namen aller Hersteller, die nur Produkte unter 125 EUR anbieten (alle Preise werden in EUR
angegeben)
SELECT Hersteller FROM Produkt
GROUP BY Hersteller HAVING MAX(Preis) < 125
Stand: 30.04.2015 16:21
4
Aufgabe T-6.4: SQL als DML
Gegeben sind folgende Ergebnisse von SQL-Anfragen. Geben Sie die entsprechenden Anfragen in SQL
an, mit denen die Ergebnisse erzeugt werden können.
a)
ProduktName
Dianetten
Edesio 1965
Edesio 1965
Edesio 1965
Delarosa
Delarosa
Delarosa
Größe
44
41
42
43
36
37
38
b)
Name
Evaluate Trainer
Paris Runner
Hersteller
Reebok
Reebok
c)
Name
CA 226130990
Dianetten
Edesio 1965
CA 22225090
Delarosa
Preis
135,00
220,00
135,00
139,90
350,00
Anzahl
42
57
63
74
66
87
85
Preis
49,95
74,95
Lösungsvorschlag:
SELECT * FROM Lager
WHERE Anzahl > 30
AND ProduktName <> ’Evaluate Trainer’
Lösungsvorschlag:
SELECT * FROM Produkt
WHERE Preis < 80
Lösungsvorschlag:
SELECT Name, Preis FROM Produkt
WHERE Preis > 100
Hinweis: Die Lösungen sind keinesfalls eindeutig.
Aufgabe T-6.5: SQL als DML: Aggregation
Formulieren Sie in SQL folgende Anfragen:
a) Namen aller Produkte, gruppiert nach Hersteller
SELECT Name, Hersteller
FROM Produkt ORDER BY Hersteller
b) Gesamtwert aller verfügbaren Waren im Lager
SELECT SUM(L.Anzahl * P.Preis) AS LagerSumme
FROM Lager L, Produkt P
WHERE L.ProduktName = P.Name
c) Name aller Produkte, von denen mehr als zwei Größen lagern
SELECT ProduktName
FROM Lager
GROUP BY ProduktName
HAVING COUNT (DISTINCT Größe) > 2
Stand: 30.04.2015 16:21
5