47 lines
No EOL
1.7 KiB
Text
47 lines
No EOL
1.7 KiB
Text
# Find ingredients included in recipe
|
|
SELECT ingredient.designation AS Ingredient FROM RecipeContainsIngredients WHERE recipe.name = 'Tomato Basil Salad';
|
|
|
|
# get Ingredients where carbohydrates are lower than 40
|
|
SELECT designation FROM Ingredient WHERE carbohydrates <= 40;
|
|
|
|
# find user that ingredients
|
|
SELECT order.customer.first_name AS Name FROM OrderContainsIngredients WHERE ingredient.designation = 'Schalotte';
|
|
|
|
# Get total amount spend by each customer
|
|
SELECT string::concat(customer.first_name, ' ', customer.last_name) as customer, math::sum(invoice_amount) AS total_spent
|
|
FROM Order
|
|
GROUP BY customer;
|
|
|
|
# Total number of deliver cost per supplyer
|
|
SELECT supplier.name AS supplier, math::sum(delivery_cost) AS total_delivery_cost
|
|
FROM SupplierContainsIngredients
|
|
GROUP BY supplier;
|
|
|
|
# Get cheapest delivery price
|
|
SELECT ingredient.designation AS ingredient , math::min(delivery_cost) AS min_delivery_price
|
|
FROM SupplierContainsIngredients
|
|
GROUP BY ingredient;
|
|
|
|
# Amount of Customers per city
|
|
SELECT address.city AS city, COUNT() AS customer_count
|
|
FROM Customer
|
|
GROUP BY city;
|
|
|
|
# Stock low warning
|
|
DEFINE EVENT ingredient_stock_low ON TABLE Ingredient
|
|
WHEN $after.stock < 10 THEN (
|
|
CREATE alert SET message = "Stock for ingredient " || $after.designation || " is low.",
|
|
ingredient_id = $value.id,
|
|
created_at = time::now()
|
|
);
|
|
|
|
# User Data Exporting
|
|
LET $customer = 'd.foede@web.de';
|
|
SELECT *, address.*, (SELECT * FROM Order WHERE customer.email = $customer) AS orders FROM Customer WHERE email = $customer;
|
|
|
|
# Delete User Data
|
|
LET $customer = SELECT id FROM Customer WHERE email = 'd.foede@web.de';
|
|
DELETE $customer.address;
|
|
DELETE OrderContainsIngredients WHERE order.customer = $customer;
|
|
DELETE Order WHERE customer = $customer;
|
|
DELETE $customer; |