Inf 12 - Brichzin

Inf 12
SQL Abfragen
Gymnasium
Ottobrunn
Eine Kaufhaus-Datenbank besteht aus folgenden Tabellen
Artikel
Abteilung Abteilungsname
Kosmetik
Lebensmittel
Textilien
Hinweis:
ArtNr
95
97
507
1056
1401
2045
2046
Stockwerk
I
EG
II
Bezeichnung
Kamm
Kamm
Seife
Zwieback
Räucherlachs
Herrenhose
Herrenhose
Abteilungsleiter
Josef Kunz
Monika Stiehl
Monika Stiehl
Verkaufspreis
1.25
0.99
3.93
1.20
4.90
37.25
20.00
Einkaufspreis
0.80
0.75
2.45
0.90
3.60
24.45
17.00
Bestand Abteilungsname
ArtNr
Lebensmittel
1056
Lebensmittel
1401
Textilien
2045
Vorrat
129
200
14
Basistabellen (Tabellen, die in der Datenbank definiert sind), werden graphisch dadurch gekennzeichnet,
dass es eine "nullte" Spalte gibt, in der der Tabellenname angegeben wird. Die Realisierung der Tabellen
in der Datenbank enthält diese Spalte natürlich nicht.
1. Geben Sie ein mögliches ER-Modell an, auf dem diese Datenbank basieren könnte.
2. Was fällt Ihnen auf, wenn Sie sich die Tabelleneinträge ansehen? Wie würden Sie
korrigieren?
3.
Formulieren Sie folgende Anfragen in SQL:
a) Gesucht sind Artikelnummer und Vorrat aller Artikel aus der Textil-Abteilung.
b) Gesucht sind alle Informationen über die Abteilungen, die im zweiten Stock platziert sind
oder von Frau Stiehl geleitet werden.
c) Gesucht ist der Name des Abteilungsleiters, der für Zwieback zuständig ist.
d) Gesucht sind alle Bezeichnung, Artikelnummer und Name des verantwortlichen
Abteilungsleiters von Artikeln, deren Vorrat kleiner als 100 ist
e) Welche Artikel (Artikelnummer, Abteilungsname) werden in den Abteilungen angeboten?
Die Ausgabe soll absteigend nach der Artikelnummer sortiert werden. Bei gleicher
Artikelnummer sollen die betroffenen Abteilungen alphabetisch aufgelistet werden.
f) Wie viele verschiedene Waren werden in der Lebensmittelabteilung verkauft?
g) Wie viele verschiedene Waren werden in den einzelnen Abteilungen verkauft?
h) Wie viel kostet der billigste, wie viel der teuerste Artikel?
Achtung: Es sind NICHT einzelne Tabelleneinträge entsprechend dem obigen Beispiel
gefragt! Die Antwort muss eine vollständige SQL Anfrage sein, die sich auf alle
Datenbanken der gegebenen Struktur anwenden lässt.
3. Formulieren Sie folgende SQL-Anfragen umgangssprachlich
SELECT DISTINCT Abteilungsleiter FROM Abteilung
WHERE NOT(Abteilungsname = Kosmetik);
b) SELECT ArtNr FROM Bestand
WHERE Abteilungsname = ‘Lebensmittel’ AND Vorrat <= 100;
a)
1.
Hinweis: -
-
Die obige Festlegung der n:m-Funktionalität ist aufgrund der vorgegebenen drei Tabellen
naheliegend, aber nicht zwingend. Falls man annimmt, dass ein Artikel nur in einer
Abteilung vertrieben wird, handelt es sich bei Bestand um einen 1:n-Relationship-Typen.
In diesem Fall ist aber dann die Verwendung von drei Tabellen unzweckmäßig
(Verfeinerung des Relationenschemas!).
Die jeweiligen Primärschlüssel sind aus den in der Angabe vorgegebenen Tabellen nicht
ablesbar. ArtNr und Abteilungsname erscheinen aber als Primärschlüssel geeignet. Als
Folge dessen ergibt sich natürlich für die Relation Bestand in Abhängigkeit von der
festgelegten Funktionalität der entsprechende Primärschlüssel.
Aufgabe 3
a)
SELECT ArtNr, Vorrat
FROM Bestand
WHERE Abteilungsname = ‘Textilien’;
b)
SELECT * FROM Abteilung
WHERE Stockwerk = ‘II’
OR Abteilungsleiter = ‘Monika Stiehl’;
c)
SELECT Abteilungsleiter
FROM Abteilung, Artikel, Bestand
WHERE Bezeichnung = ‘Zwieback’
AND Bestand.Artikelnummer = Artikel. Artikelnummer
AND Bestand.Abteilungsname = Abteilung.Abteilungsname
d)
SELECT Bezeichnung, Artikelnummer, Abteilungsleiter
FROM Abteilung, Artikel, Bestand
WHERE Bestand < 100
AND Bestand.Artikelnummer = Artikel. Artikelnummer
AND Bestand.Abteilungsname = Abteilung.Abteilungsname
e)
SELECT ArtNr, Abteilungsname
FROM Bestand
ORDER BY ArtNr DESC, Abteilungsname;
SELECT COUNT(*)
FROM Bestand
WHERE Abteilungsname = 'Lebensmittel';
f)
Hinweise: - Neben Aggregatfunktionen dürfen in der SELECT-Klausel nur die Attribute aufgeführt werden,
die in der GROUP By-Klausel vorkommen!
Alternative zu b:
SELECT Abteilungsname, COUNT(*) FROM Bestand
WHERE Abteilungsname = 'Lebensmittel'
GROUP BY Abteilungsname;
- Man kann „verschiedene Waren“ einerseits auf die Artikelnummer, andererseits auf die
Bezeichnung beziehen. Diese Lösungsvorschläge gehen von den Artikelnummern aus.
g)
h)
SELECT Abteilungsname, COUNT(*)
FROM Bestand
GROUP BY Abteilungsname;
SELECT MIN(Verkaufspreis), MAX(Verkaufspreis)
FROM Artikel;
Aufgabe 4
a)
Gesucht sind die Namen aller Abteilungsleiter mit Ausnahme
der Kosmetik-Abteilung? Duplikate sollen eliminiert werden.
b)
Gesucht sind die Nummern der Artikel, von denen in der
Lebensmittelabteilung maximal 100 vorrätig sind.