131 lines
6.0 KiB
SQL
131 lines
6.0 KiB
SQL
USE project;
|
|
|
|
/*
|
|
---------------------------------- 1 ----------------------------------
|
|
Geben Sie mit SQL alle Menüs mit den darin enthaltenen Produkten menschenlesbar
|
|
als eine Art Speisekarte aus.
|
|
-----------------------------------------------------------------------
|
|
*/
|
|
SELECT Menuname, GROUP_CONCAT(Produktname SEPARATOR ', ') AS 'Produkte'
|
|
FROM Menu
|
|
JOIN Menucontent M on Menu.MenuID = M.MenuID
|
|
JOIN Menge M2 on M.MengenID = M2.MengenID
|
|
JOIN Produkt P on M2.ProduktID = P.ProduktID
|
|
GROUP BY Menuname;
|
|
|
|
|
|
/*
|
|
---------------------------------- 2 ----------------------------------
|
|
Erweitern Sie die Ausgabe aus der vorherigen Abfrage um hilfreiche Informationen
|
|
bezüglich der Gesamtnährwerte dieser Menüs.
|
|
-----------------------------------------------------------------------
|
|
*/
|
|
SELECT Menuname,
|
|
GROUP_CONCAT(Produktname SEPARATOR ', ') AS 'Produkte',
|
|
ROUND(SUM(Fett * (SELECT Menge FROM Menge WHERE MengenID = M.MengenID) / 100), 2) AS 'Fett',
|
|
ROUND(SUM(Kohlenhydrate * (SELECT Menge FROM Menge WHERE MengenID = M.MengenID) / 100), 2) AS 'Kohlenhydrate',
|
|
ROUND(SUM(Eiweiss * (SELECT Menge FROM Menge WHERE MengenID = M.MengenID) / 100), 2) AS 'Eiweiss',
|
|
ROUND(SUM(Brennwert * (SELECT Menge FROM Menge WHERE MengenID = M.MengenID) / 100), 2) AS 'Brennwert'
|
|
FROM Menu
|
|
JOIN Menucontent M on Menu.MenuID = M.MenuID
|
|
JOIN Menge M3 on M.MengenID = M3.MengenID
|
|
JOIN Produkt P on M3.ProduktID = P.ProduktID
|
|
JOIN Naehrwerte N on P.ProduktID = N.ProduktID
|
|
GROUP BY Menuname;
|
|
|
|
|
|
/*
|
|
---------------------------------- 3 ----------------------------------
|
|
Geben sie mit SQL alle Produkte mit für Kundinnen und Kunden relevanten Informationen für
|
|
eine bestimmte Person aus (betreffend Allergenen, Unverträglichkeiten, vegetarisch, etc.).
|
|
-----------------------------------------------------------------------
|
|
*/
|
|
SET @vorname := 'Florian';
|
|
SET @nachname := 'Kaiser';
|
|
|
|
# UNION von Nur Zusatzstoffe und Nur Allergene
|
|
SELECT Produktname, GROUP_CONCAT(Zusatzstoff) AS Zusatzstoff, GROUP_CONCAT(Allergen) AS Allergen
|
|
FROM (SELECT Produktname,
|
|
GROUP_CONCAT(enthaeltZusatzstoff.E_Nummer) as Zusatzstoff,
|
|
NULL Allergen
|
|
FROM Person
|
|
# Zusatzstoffe
|
|
JOIN Zusatzstoffunvertraeglichkeit on Person.PersonID = Zusatzstoffunvertraeglichkeit.PersonID
|
|
JOIN enthaeltZusatzstoff on enthaeltZusatzstoff.E_Nummer = Zusatzstoffunvertraeglichkeit.E_Nummer
|
|
LEFT JOIN Produkt on Produkt.ProduktID = enthaeltZusatzstoff.ProduktID
|
|
WHERE Personenname = @nachname
|
|
AND Personenvorname = @vorname
|
|
GROUP BY Produktname
|
|
UNION ALL
|
|
SELECT P.Produktname,
|
|
NULL Zusatzstoff,
|
|
GROUP_CONCAT(DISTINCT Allergenbezeichnung SEPARATOR ', ') AS Allergen
|
|
FROM Person
|
|
# Allergien
|
|
JOIN Allergie on Person.PersonID = Allergie.PersonID
|
|
JOIN Allergen on Allergen.AllergenID = Allergie.AllergenID
|
|
JOIN enthaeltAllergen on Allergen.AllergenID = enthaeltAllergen.AllergenID
|
|
JOIN Produkt P on P.ProduktID = enthaeltAllergen.ProduktID
|
|
WHERE Personenname = @nachname
|
|
AND Personenvorname = @vorname
|
|
GROUP BY Produktname) AS x
|
|
GROUP BY Produktname;
|
|
|
|
|
|
/*
|
|
---------------------------------- 4 ----------------------------------
|
|
Sortieren Sie alle enthaltenen Produkte absteigend nach Anzahl der enthaltenen Zusatzstoffe.
|
|
-----------------------------------------------------------------------
|
|
*/
|
|
SELECT Produktname,
|
|
LENGTH(GROUP_CONCAT(E_Nummer)) - LENGTH(REPLACE(GROUP_CONCAT(E_Nummer), ',', '')) + 1 AS 'Anzahl Zusatzstoffe'
|
|
FROM Produkt
|
|
JOIN enthaeltZusatzstoff eZ on Produkt.ProduktID = eZ.ProduktID
|
|
GROUP BY Produktname
|
|
ORDER BY LENGTH(GROUP_CONCAT(E_Nummer)) - LENGTH(REPLACE(GROUP_CONCAT(E_Nummer), ',', '')) + 1 DESC;
|
|
|
|
|
|
/*
|
|
---------------------------------- 5 ----------------------------------
|
|
Sortieren Sie alle [in der Datenbank (anm. d. St.)] enthaltenen Produkte aufsteigend nach Kalorienanzahl in der Portion, die verkauft wird.
|
|
Hinweis: Produkte ohne Brennwert werden nicht angezeigt
|
|
-----------------------------------------------------------------------
|
|
*/
|
|
SELECT Produktname,
|
|
ROUND(Brennwert * (M.Menge / 100), 1) AS Brennwert,
|
|
M.Menge,
|
|
M.Einheit
|
|
FROM Produkt
|
|
JOIN Naehrwerte N on Produkt.ProduktID = N.ProduktID
|
|
JOIN Menge M on Produkt.ProduktID = M.ProduktID
|
|
ORDER BY Brennwert;
|
|
|
|
/*
|
|
---------------------------------- 6 ----------------------------------
|
|
Geben Sie eine SQL Anfrage an, die alle Produkte ausgibt, die eine bestimmte Anzahl von Emulgatoren enthalten.
|
|
a. Keine Emulgatoren
|
|
b. 1 Emulgator
|
|
c. 2 Emulgatoren
|
|
d. Mehr
|
|
-----------------------------------------------------------------------
|
|
*/
|
|
|
|
SELECT Produktname,
|
|
IFNULL( # wenn feld NULL, Keine Emulgatoren
|
|
REPLACE( # wenn feld zero, Keine Emulgatoren
|
|
REPLACE( # wenn feld zwei, Zwei Emulgatoren
|
|
REPLACE( # wenn feld eins, Ein Emulgator
|
|
IF( # wenn feld größer als zwei, Mehr Emulgatoren, ansonsten setze den gleichen wert wieder ins feld
|
|
LENGTH(GROUP_CONCAT(eZ.Anwendungsgrund)) - LENGTH(REPLACE(GROUP_CONCAT(eZ.Anwendungsgrund), 'E', ''))
|
|
> 2, 'Mehr Emulgatoren',
|
|
LENGTH(GROUP_CONCAT(eZ.Anwendungsgrund)) - LENGTH(REPLACE(GROUP_CONCAT(eZ.Anwendungsgrund), 'E', '')))
|
|
, 1, 'Ein Emulgator')
|
|
, 2, 'Zwei Emulgatoren')
|
|
, 0, 'Keine Emulgatoren')
|
|
, 'Keine Emulgatoren')
|
|
AS Emgulagtoren,
|
|
GROUP_CONCAT(eZ.Anwendungsgrund) AS 'Anwendungsgrund'
|
|
FROM Produkt
|
|
LEFT JOIN enthaeltZusatzstoff eZ on Produkt.ProduktID = eZ.ProduktID
|
|
GROUP BY Produktname
|
|
ORDER BY LENGTH(GROUP_CONCAT(eZ.Anwendungsgrund)) - LENGTH(REPLACE(GROUP_CONCAT(eZ.Anwendungsgrund), 'E', '')); |