Changes between Version 7 and Version 8 of Application Design – Use Cases and Database Access Scenarios – SQL View

Timestamp:

08/28/25 22:44:03 (5 days ago)

Author:

221531

Comment:

--

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

@@ -200 +200 @@
 {{{#!td
 SELECT
-    o.order_id, o.order_date, o.status, 
-    i.name AS item_name, oi.quantity, oi.total_price
-FROM "ORDERS" o\\
+    o.order_id, 
+    o.order_status AS status, 
+    o.total_amount,
+    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;
+ORDER BY o.order_id DESC;\\
 }}}
 2. Manage Restaurant's Menu
@@ -212 +216 @@
 
 {{{#!td
-SELECT 
-    i.item_id, i.name, i.price, i.description, i.image_url
+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\\
+JOIN MENU_ITEM mi ON m.menu_id = mi.menu_id\\
+JOIN ITEM i ON mi.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;
+SELECT
+    o.order_id,
+    o.order_status AS status,
+    o.total_amount,
+    r.name AS restaurant_name
+FROM ORDERS o\\
+JOIN RESTAURANT r ON o.restaurant_id = r.restaurant_id\\
+WHERE o.order_id = 1;\\
 }}}