Changes between Version 5 and Version 6 of Application Design – Use Cases and Database Access Scenarios – SQL View

Timestamp:

08/27/25 08:29:55 (2 months ago)

Author:

221531

Comment:

--

Application Design – Use Cases and Database Access Scenarios – SQL View

@@ -194 +194 @@
 \\
 ==== Most important use cases
+1. View My Food Orders
+\\
+This SQL statement selects the order_id, order_date, status, and the total price from the ''order'' table together with the ordered items from the ''order_items'' table and their names from the ''item'' table. A join is made between ''order_items'', ''item'', and ''order''. It filters only the orders of the user with user_id = 1 and displays them ordered by date (order_date) in descending order (newest first).
+\\
+{{{#!td
+SELECT
+    o.order_id, o.order_date, o.status, 
+    i.name AS item_name, oi.quantity, oi.total_price
+FROM "ORDERS" o\\
+JOIN ORDER_ITEMS oi ON o.order_id = oi.order_id\\
+JOIN ITEM i ON oi.item_id = i.item_id\\
+WHERE o.user_id = 1\\
+ORDER BY o.order_date DESC;
+}}}
+2. Manage Restaurant's Menu
+This SQL statement selects all items item_id, name, price and description of a given restaurant. A join is made between the ''menu'' table and the ''item'' table through item_id. It filters by restaurant_id = 2 to show only the menu for that restaurant.
+
+{{{#!td
+SELECT 
+    i.item_id, i.name, i.price, i.description, i.image_url
+FROM MENU m\\
+JOIN ITEM i ON m.item_id = i.item_id\\
+WHERE m.restaurant_id = 2\\
+ORDER BY i.name ASC;\\
+}}}
+3. Track Order Status
+This SQL statement selects the order_id, order_date, status, and the restaurant name from which the order was placed. A join is made between the ''order'', ''restaurant'', and ''restaurant_owners'' tables. It filters for the order with order_id = 1.
+{{{#!td
+SELECT 
+    o.order_id, o.order_date, o.status, r.name AS restaurant_name
+FROM "ORDERS" o
+JOIN RESTAURANT r ON o.address_id = r.address_id
+JOIN RESTAURANT_OWNERS ro ON r.restaurant_id = ro.restaurant_id
+WHERE o.order_id = 1;
+
+}}}