Praktikumsanleitung (Lösung)

Datenbanken

Bei den Ergebnissen kommt es primär auf die zurückgegebenen Zeilen an. Diese müssen übereinstimmen.

Einfache Abfragen (zur Wiederholung)

Ermitteln Sie…

1. …alle Bestellungen (Orders)

SELECT *
FROM Orders

2. …alle Kunden (Customers) sortiert nach Firmenname (CompanyName)

SELECT *
FROM Customers
ORDER BY CompanyName

3. …alle Kunden mit Firmensitz (Country) in Deutschland (Germany)

SELECT *
FROM Customers
WHERE Country = 'Germany'

4. …alle Bestellungen die als Zielland (ShipCountry) die USA haben.

SELECT *
FROM Orders
WHERE ShipCountry = 'USA'

5. …alle Bestellungen mit einem Frachtgewicht (Freight) größer als 200 und kleiner als 300, sortiert nach Frachtgewicht.

SELECT *
FROM Orders
WHERE Freight > 200 AND Freight < 300
ORDER BY Freight

Aggregatfunktionen

Ermitteln Sie…

1. …die Anzahl an Bestellungen je Zielland (ShipCountry)

SELECT ShipCountry, COUNT(*)
FROM Orders
GROUP BY ShipCountry

2. Sortieren Sie die Ausgabe der vorherigen Abfrage absteigend nach der Anzahl an Bestellungen.

SELECT ShipCountry, COUNT(*) Anzahl
FROM Orders
GROUP BY ShipCountry
ORDER BY Anzahl DESC

3. …vom Frachtgewicht Summe, Maximum, Minimum und Durchschnitt je Zielland (ShipCountry).

SELECT ShipCountry, SUM(Freight), MAX(Freight), MIN(Freight), AVG(Freight)
FROM Orders
GROUP BY ShipCountry

4. Filtern Sie die Ausgabe der vorherigen Abfrage, sodass nur noch Zielländer mit einem durchschnittlichen Frachtgewicht > 50 (Freight) angezeigt werden.

SELECT ShipCountry, SUM(Freight), MAX(Freight), MIN(Freight), AVG(Freight) Durchschnitt
FROM Orders
GROUP BY ShipCountry
HAVING Durchschnitt > 50

Joins

Über welche Spalte stehen die Produkt- (Products) und die Kategorietabelle (Categories) in Relation? Lösung: Über die CategoryID.

1. Erzeugen Sie eine Tabelle, die je Zeile den Produktnamen (ProductName) und den dazugehörigen Kategorienamen (CategoryName) enthält.

SELECT p.ProductName, c.CategoryName
FROM Products p
INNER JOIN Categories c ON p.CategoryID = c.CategoryID

2. In der gerade erstellten Tabelle soll zudem auch der Firmenname des Lieferanten (CompanyName) in einer Spalte angezeigt werden.

SELECT p.ProductName, s.CompanyName, c.CategoryName
FROM Products p
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID

3. In der gerade erstellten Tabelle sollen nur noch Produkte gelistet werden, deren Lieferant aus Frankreich (France) stammt.

SELECT p.ProductName, s.CompanyName, c.CategoryName
FROM Products p
INNER JOIN Categories c ON p.CategoryID = c.CategoryID
INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID
WHERE s.Country = 'France'

Bestellungen

Über welche Spalten stehen Kunde (Customer) und dessen Bestellung (Orders) in Relation? Lösung: Über die CustomerID.

1. Erzeugen Sie eine Tabelle bestehend aus Firmenname (CompanyName) des Kunden und der Anzahl von Bestellungen je Kunde.

SELECT CompanyName, COUNT(*) Bestellungen
FROM Orders o
INNER JOIN Customers c on o.CustomerID = c.CustomerID
GROUP BY CompanyName

2. Welcher Kunde hat die wenigsten Bestellungen? (ohne SQL beantworten, nur ablesen)

Centro comercial Moctezuma

3. In der gerade erstellten Tabelle sollen nur noch Firmen mit mindestens 10 Bestellungen aufgelistet werden. Sortieren Sie absteigend nach der Anzahl.

SELECT CompanyName, COUNT(*) Bestellungen
FROM Orders o
INNER JOIN Customers c on o.CustomerID = c.CustomerID
GROUP BY CompanyName
HAVING Bestellungen > 10
ORDER BY Bestellungen DESC

Bestelldetails

In der Tabelle OrderDetails stehen je Bestellung die bestellten Produkte.

1. Ermitteln Sie den Gesamtpreis je Bestellung (Produktpreis * Anzahl, also UnitPrice * Quantity). Der Einfachheit halber kann der Rabatt (Discount) ignoriert werden.

SELECT OrderID, SUM(UnitPrice*Quantity)
FROM "Order Details"
GROUP BY OrderID

2. Fügen Sie bei der gerade erstellten Tabelle auch den Firmennamen (CompanyName) des Kunden hinzu.

SELECT od.OrderID, c.CompanyName, SUM(UnitPrice*Quantity)
FROM "Order Details" od
INNER JOIN Orders o on od.OrderID = o.OrderID
INNER JOIN Customers c on o.CustomerID = c.CustomerID
GROUP BY od.OrderID

3. Ermitteln Sie, wie viel Geld jeder Kunde insgesamt über alle Bestellungen ausgegeben hat.

SELECT c.CompanyName, SUM(UnitPrice*Quantity)
FROM "Order Details" od
INNER JOIN Orders o on od.OrderID = o.OrderID
INNER JOIN Customers c on o.CustomerID = c.CustomerID
GROUP BY c.CompanyName

4. Ermitteln Sie, welches Produkt den größten Umsatz erzeugt hat und sortieren Sie es absteigend nach dem Umsatz. (Der Einfachheit halber kann der Rabatt (Discount) ignoriert werden.)

SELECT p.ProductName, SUM(od.UnitPrice*Quantity) Umsatz
FROM "Order Details" od
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY od.ProductID
ORDER BY Umsatz DESC

5. Welches Produkt hat den größten Umsatz generiert? (ohne SQL beantworten, nur ablesen)

Côte de Blaye (149984.2)

6. Ermitteln Sie, wie oft jedes Produkt bestellt wurde und sortieren Sie es absteigend nach der Häufigkeit der Bestellungen.

SELECT p.ProductName, COUNT(*) Anzahl
FROM "Order Details" od
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY od.ProductID
ORDER BY Anzahl DESC

7. Welches Produkt ist das beliebteste? (ohne SQL beantworten, nur ablesen)

Raclette Courdavault (54 Bestellungen)

Views

1. Erzeugen Sie aus der zuletzt erstellten Tabelle (also 6. von Bestelldetails) eine View mit Namen ProductPopularity

CREATE VIEW ProductPopularity AS
SELECT p.ProductName, COUNT(*) Anzahl
FROM "Order Details" od
INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY od.ProductID
ORDER BY Anzahl DESC

2. Ermitteln Sie in dieser View alle Produkte mit mindestens 10 und höchstens 50 Bestellungen.

SELECT *
FROM ProductPopularity
WHERE Anzahl >= 10 AND Anzahl <= 50

3. Löschen Sie die View wieder.

DROP VIEW ProductPopularity

Normalisierung

1. Welche Probleme können auftreten, wenn Daten nicht korrekt normalisiert wurden?

Lösung:

Es können sogenannte Anomalien auftreten weil Daten redundant vorliegen. Wenn beispielsweise eine Firmenadresse in zwei Tabellen abgespeichert ist und nur eine Adresse aktualisiert wird, ist die Datenbank im Anschluss inkonsistent. Eine Normalisierung vermeidet solche Datenbankprobleme.

Sie sehen in einer Tabelle die Spalte “Anschrift”. Nachfolgend zwei Zeilen aus dieser Spalte:

• Technikumplatz 17, 09648 Mittweida
• Str. der Nationen 62, 09111 Chemnitz

Teilen Sie diese Spalte in mehrere Spalten auf, sodass eine sinnvolle Verarbeitung in der Datenbank möglich ist (siehe auch: Normalform in der Vorlesung)

Lösung:

Die Adresse kann in Ihre Komponenten zerlegt werden. Eine mögliche Zerlegung wäre:

• Straße
• Hausnummer
• Postleitzahl
• Stadt