{"id":763,"date":"2025-10-17T07:03:08","date_gmt":"2025-10-17T07:03:08","guid":{"rendered":"https:\/\/egco.org\/design\/?p=763"},"modified":"2025-10-24T06:25:56","modified_gmt":"2025-10-24T06:25:56","slug":"egco321-assignment-4-stored-procedures","status":"publish","type":"post","link":"https:\/\/egco.org\/design\/?p=763","title":{"rendered":"EGCO321: Assignment 4 &#8211; Stored Procedures"},"content":{"rendered":"\n<p>\u0e43\u0e2b\u0e49\u0e19\u0e31\u0e01\u0e28\u0e36\u0e01\u0e29\u0e32\u0e41\u0e15\u0e48\u0e25\u0e30\u0e01\u0e31\u0e19 Import \u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 <a href=\"https:\/\/egco.org\/design\/download\/company.sql\">company.sql <\/a>\u0e08\u0e32\u0e01 Course Material \u0e40\u0e02\u0e49\u0e32 DB \u0e02\u0e2d\u0e07\u0e41\u0e15\u0e48\u0e25\u0e30\u0e04\u0e19 \u0e41\u0e25\u0e30\u0e17\u0e33\u0e41\u0e1a\u0e1a\u0e1d\u0e36\u0e01\u0e2b\u0e31\u0e14\u0e14\u0e31\u0e07\u0e15\u0e48\u0e2d\u0e44\u0e1b\u0e19\u0e35\u0e49<\/p>\n\n\n\n<p>1) \u0e2a\u0e23\u0e49\u0e32\u0e07 Stored Function \u0e0a\u0e37\u0e48\u0e2d <strong>discount() <\/strong>\u0e15\u0e32\u0e21\u0e23\u0e32\u0e22\u0e25\u0e30\u0e40\u0e2d\u0e35\u0e22\u0e14\u0e14\u0e31\u0e07\u0e15\u0e48\u0e2d\u0e44\u0e1b\u0e19\u0e35\u0e49<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stored Function \u0e2a\u0e33\u0e2b\u0e23\u0e31\u0e1a\u0e04\u0e33\u0e19\u0e27\u0e13\u0e2a\u0e48\u0e27\u0e19\u0e25\u0e14 \u0e15\u0e32\u0e21\u0e40\u0e07\u0e37\u0e48\u0e2d\u0e19\u0e44\u0e02\u0e14\u0e31\u0e07\u0e19\u0e35\u0e49\n<ul class=\"wp-block-list\">\n<li>\u0e15\u0e31\u0e49\u0e07\u0e41\u0e15\u0e48 100 \u0e02\u0e36\u0e49\u0e19\u0e44\u0e1b \u0e25\u0e14 10% (purch_amt &gt;= 100)<\/li>\n\n\n\n<li>\u0e15\u0e31\u0e49\u0e07\u0e41\u0e15\u0e48 1,000 \u0e02\u0e36\u0e49\u0e19\u0e44\u0e1b \u0e25\u0e14 20% (purch_amt &gt;= 1000)<\/li>\n\n\n\n<li>\u0e15\u0e31\u0e49\u0e07\u0e41\u0e15\u0e48 3,000 \u0e02\u0e36\u0e49\u0e19\u0e44\u0e1b \u0e25\u0e14 30% (purch_amt &gt;= 3000)<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>\u0e42\u0e14\u0e22\u0e21\u0e35 Syntax \u0e14\u0e31\u0e07\u0e19\u0e35\u0e49\n<ul class=\"wp-block-list\">\n<li>discount(purch_amt)<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>\u0e17\u0e14\u0e2a\u0e2d\u0e1a\u0e01\u0e32\u0e23\u0e17\u0e33\u0e07\u0e32\u0e19\u0e42\u0e14\u0e22\u0e43\u0e0a\u0e49 SQL Command (SELECT) \u0e40\u0e1e\u0e37\u0e48\u0e2d\u0e41\u0e2a\u0e14\u0e07\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 ord_no, ord_date, purch_amt, discount(purch_amt) AS discount, purch_amt-discount(purch_amt) AS total<\/li>\n<\/ul>\n\n\n\n<p>2) \u0e2a\u0e23\u0e49\u0e32\u0e07 Trigger \u0e0a\u0e37\u0e48\u0e2d orders_insert \u0e17\u0e35\u0e48\u0e17\u0e33\u0e07\u0e32\u0e19\u0e17\u0e38\u0e01\u0e04\u0e23\u0e31\u0e49\u0e07 <strong>\u0e2b\u0e25\u0e31\u0e07\u0e01\u0e32\u0e23\u0e40\u0e1e\u0e34\u0e48\u0e21\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25<\/strong> \u0e43\u0e19\u0e15\u0e32\u0e23\u0e32\u0e07 orders \u0e42\u0e14\u0e22\u0e40\u0e1e\u0e34\u0e48\u0e21\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e43\u0e2b\u0e21\u0e48\u0e25\u0e07\u0e43\u0e19\u0e15\u0e32\u0e23\u0e32\u0e07 orders_audit \u0e14\u0e31\u0e07\u0e19\u0e35\u0e49<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>log_date (ord_date)<\/li>\n\n\n\n<li>ord_no<\/li>\n\n\n\n<li>customer_id<\/li>\n\n\n\n<li>salesman_id<\/li>\n\n\n\n<li>action = \u2018INSERT\u2019<\/li>\n<\/ul>\n\n\n\n<p>\u0e17\u0e31\u0e49\u0e07\u0e2a\u0e2d\u0e07\u0e02\u0e49\u0e2d\u0e43\u0e2b\u0e49\u0e17\u0e33\u0e1a\u0e19\u0e10\u0e32\u0e19\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e02\u0e2d\u0e07\u0e15\u0e19\u0e40\u0e2d\u0e07 (u6613xxx)<\/p>\n\n\n\n<p><strong><mark style=\"background-color:rgba(0, 0, 0, 0);color:#ff0000\" class=\"has-inline-color\">Deadline: 31 October 2025<\/mark><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u0e43\u0e2b\u0e49\u0e19\u0e31\u0e01\u0e28\u0e36\u0e01\u0e29\u0e32\u0e41\u0e15\u0e48\u0e25\u0e30\u0e01\u0e31\u0e19 Import \u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 company.sql \u0e08\u0e32\u0e01 Course Material \u0e40\u0e02\u0e49\u0e32 DB \u0e02\u0e2d\u0e07\u0e41\u0e15\u0e48\u0e25\u0e30\u0e04\u0e19 \u0e41\u0e25\u0e30\u0e17\u0e33\u0e41\u0e1a\u0e1a\u0e1d\u0e36\u0e01\u0e2b\u0e31\u0e14\u0e14\u0e31\u0e07\u0e15\u0e48\u0e2d\u0e44\u0e1b\u0e19\u0e35\u0e49 1) \u0e2a\u0e23\u0e49\u0e32\u0e07 Stored Function \u0e0a\u0e37\u0e48\u0e2d discount() \u0e15\u0e32\u0e21\u0e23\u0e32\u0e22\u0e25\u0e30\u0e40\u0e2d\u0e35\u0e22\u0e14\u0e14\u0e31\u0e07\u0e15\u0e48\u0e2d\u0e44\u0e1b\u0e19\u0e35\u0e49 2) \u0e2a\u0e23\u0e49\u0e32\u0e07 Trigger \u0e0a\u0e37\u0e48\u0e2d orders_insert \u0e17\u0e35\u0e48\u0e17\u0e33\u0e07\u0e32\u0e19\u0e17\u0e38\u0e01\u0e04\u0e23\u0e31\u0e49\u0e07 \u0e2b\u0e25\u0e31\u0e07\u0e01\u0e32\u0e23\u0e40\u0e1e\u0e34\u0e48\u0e21\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25 \u0e43\u0e19\u0e15\u0e32\u0e23\u0e32\u0e07 orders \u0e42\u0e14\u0e22\u0e40\u0e1e\u0e34\u0e48\u0e21\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e43\u0e2b\u0e21\u0e48\u0e25\u0e07\u0e43\u0e19\u0e15\u0e32\u0e23\u0e32\u0e07 orders_audit \u0e14\u0e31\u0e07\u0e19\u0e35\u0e49 \u0e17\u0e31\u0e49\u0e07\u0e2a\u0e2d\u0e07\u0e02\u0e49\u0e2d\u0e43\u0e2b\u0e49\u0e17\u0e33\u0e1a\u0e19\u0e10\u0e32\u0e19\u0e02\u0e49\u0e2d\u0e21\u0e39\u0e25\u0e02\u0e2d\u0e07\u0e15\u0e19\u0e40\u0e2d\u0e07 (u6613xxx) Deadline: 31 October 2025<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-763","post","type-post","status-publish","format-standard","hentry","category-assignments"],"_links":{"self":[{"href":"https:\/\/egco.org\/design\/index.php?rest_route=\/wp\/v2\/posts\/763","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/egco.org\/design\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/egco.org\/design\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/egco.org\/design\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/egco.org\/design\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=763"}],"version-history":[{"count":6,"href":"https:\/\/egco.org\/design\/index.php?rest_route=\/wp\/v2\/posts\/763\/revisions"}],"predecessor-version":[{"id":771,"href":"https:\/\/egco.org\/design\/index.php?rest_route=\/wp\/v2\/posts\/763\/revisions\/771"}],"wp:attachment":[{"href":"https:\/\/egco.org\/design\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=763"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/egco.org\/design\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=763"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/egco.org\/design\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=763"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}