111 lines
4.1 KiB
Markdown
111 lines
4.1 KiB
Markdown
# Aufgabe 3
|
|
Übung. Datenbank: Anfragen. Erstellen Sie die folgenden Anfragen in SQL und führen Sie diese auf der angelegten Datenstruktur durch (z.B. in HeidiSQL oder PHPStorm). Geben Sie sowohl das Statement als auch die Ergebnismenge (z.B. als Screenshot) in Ihrem Dossier mit ab.
|
|
|
|
## Abfragen
|
|
|
|
1) Alle Daten aus gericht
|
|
``` sql
|
|
SELECT * FROM gericht
|
|
```
|
|
2) Das Erfassungsdatum aller Gerichte.
|
|
``` sql
|
|
SELECT erfasst_am FROM gericht
|
|
```
|
|
3) Das Erfassungsdatum sowie den Namen (als Attributname Gerichtname) aller Gerichte absteigend sortiert nach Gerichtname.
|
|
``` sql
|
|
SELECT name, erfasst_am FROM gericht ORDER BY name DESC
|
|
```
|
|
4) Den Namen sowie die Beschreibung der Gerichte aufsteigend sortiert nach Namen, wobei nur 5 Datensätze dargestellt werden sollen.
|
|
``` sql
|
|
SELECT name, beschreibung FROM gericht ORDER BY name ASC LIMIT 5
|
|
```
|
|
5) Ändern Sie die vorherige Abfrage so ab, so dass 10 Datensätze dargestellt werden, die nach den ersten 5 Datensätzen folgen. (Die ersten 5 Datensätze werden übersprungen)
|
|
``` sql
|
|
SELECT name,beschreibung FROM gericht ORDER BY name ASC LIMIT 10 OFFSET 5
|
|
```
|
|
6) Zeigen Sie alle möglichen Allergen-Typen (typ), wobei Sie keine doppelten Einträge darstellen.
|
|
``` sql
|
|
SELECT DISTINCT typ FROM allergen
|
|
```
|
|
7) Namen von Gerichten, deren Name mit einem klein- oder großgeschriebenen „K“ beginnt.
|
|
``` sql
|
|
SELECT name FROM gericht WHERE name LIKE 'k%'
|
|
```
|
|
8) Ids und Namen von Gerichten, deren Namen ein „suppe“ an beliebiger Stelle enthält.
|
|
``` sql
|
|
SELECT id, name FROM gericht WHERE name LIKE '%suppe%'
|
|
```
|
|
9) Alle Kategorien, die keine Elterneinträge besitzen.
|
|
``` sql
|
|
SELECT * FROM kategorie WHERE eltern_id IS NULL
|
|
```
|
|
10) Korrigieren Sie den Wert „Dinkel“ in der Tabelle allergen mit dem code a6 zu „Kamut“.
|
|
``` sql
|
|
UPDATE allergen SET name = 'Kamut' WHERE code = 'a6';
|
|
```
|
|
11) Fügen Sie das Gericht „Currywurst mit Pommes“ hinzu und tragen Sie es in
|
|
der Kategorie „Hauptspeise“ ein.
|
|
``` sql
|
|
INSERT INTO gericht VALUES (21, 'Currywurst mit Pommes', '', '2023-11-22', 0, 0, 1.0, 2.0);
|
|
|
|
INSERT INTO gericht_hat_kategorie VALUES (21, 3);
|
|
```
|
|
|
|
|
|
# Aufgabe 6
|
|
|
|
## Abfragen
|
|
|
|
1) Alle Gerichte mit allen zugehörigen Allergenen
|
|
``` sql
|
|
SELECT DISTINCT name, GROUP_CONCAT(code) FROM gericht g JOIN gericht_hat_allergen a ON g.id = a.gericht_id GROUP BY name ASC;
|
|
```
|
|
2) Ändern Sie die vorherige Abfrage so ab, dass alle existierenden Gerichte dargestellt werden (auch wenn keine Allergene enthalten sind).
|
|
``` sql
|
|
SELECT DISTINCT name, GROUP_CONCAT(code) FROM gericht g LEFT JOIN gericht_hat_allergen a ON g.id = a.gericht_id GROUP BY name ASC;
|
|
```
|
|
3) Ändern Sie die vorherige Abfrage so ab, so dass im Ergebnis alle existierenden Allergene dargestellt werden (auch wenn diese nicht einem Gericht zugeordnet sind).
|
|
``` sql
|
|
SELECT DISTINCT allergen.code, GROUP_CONCAT(gericht.name) AS gericht FROM allergen LEFT JOIN gericht_hat_allergen on allergen.code = gericht_hat_allergen.code LEFT JOIN gericht ON gericht_hat_allergen.gericht_id = gericht_id
|
|
GROUP BY allergen.name
|
|
ORDER BY code ASC;
|
|
```
|
|
4) Die Anzahl der Gerichte pro Kategorie aufsteigend sortiert nach Anzahl.
|
|
``` sql
|
|
SELECT kategorie.name, COUNT(kategorie_id) AS anzahl FROM gericht_hat_kategorie, kategorie
|
|
WHERE kategorie.id = gericht_hat_kategorie.kategorie_id
|
|
GROUP BY kategorie_id
|
|
ORDER BY anzahl ASC;
|
|
```
|
|
5) Ändern Sie die vorherige Abfrage so ab, dass dabei nur die Kategorien dargestellt werden, die mehr als 2 Gerichte besitzen.
|
|
``` sql
|
|
SELECT kategorie.name, COUNT(kategorie_id) AS anzahl FROM gericht_hat_kategorie, kategorie
|
|
WHERE kategorie.id = gericht_hat_kategorie.kategorie_id AND kategorie_id > 2
|
|
GROUP BY kategorie_id
|
|
ORDER BY anzahl ASC;
|
|
```
|
|
|
|
|
|
# Aufgabe 7
|
|
elter_kategorie:
|
|
``` sql
|
|
CONSTRAINT FOREIGN KEY (id) REFERENCES kategorie(id),
|
|
```
|
|
|
|
gericht_hat_allergen:
|
|
``` sql
|
|
code char(4),
|
|
CONSTRAINT FOREIGN KEY (code) REFERENCES allergen(code),
|
|
gericht_id int8 not null,
|
|
CONSTRAINT FOREIGN KEY (gericht_id) REFERENCES gericht(id)
|
|
|
|
```
|
|
|
|
gericht_hat_kategorie:
|
|
``` sql
|
|
gericht_id int8 not null,
|
|
CONSTRAINT FOREIGN KEY (gericht_id) REFERENCES gericht(id),
|
|
kategorie_id int8 not null,
|
|
CONSTRAINT FOREIGN KEY (kategorie_id) REFERENCES kategorie(id)
|
|
|
|
``` |