PROGRAM 4

PROGRAM 4 /* program: program4.sql author: anthony f. ortiz */ /* this programs demonstrates an oracle sql view statement. */ /* a) insert a new row in the orders table: 1031, jul, c001, a01, p01, */ /* 1000, 450.00. now create a view called returns based on orders */ /* that shows cols ordno, month, cid, aid, pid, qty, dollars and, */ /* in addition, discnt (from the customers table), percent (from */ /* agents), and price (from products). show that returns exists */ /* as a view, using a terminal command. also use a command to see */ /* what the column names are. */ SQL> insert into orders (ordno, month, cid, aid, pid, qty, dollars) 2 values (1031, 'jul', 'c001', 'a01', 'p01', 1000, 450.00); 1 row created. SQL> create view returns (ordno, month, cid, aid, pid, qty, dollars, 2 discnt, percent, price) 3 as select orders.ordno, orders.month, orders.cid, orders.aid, 4 orders.pid, orders.qty, orders.dollars, customers.discnt, 5 agents.percent, products.price 6 from agents, customers, orders, products 7 where orders.aid = agents.aid and 8 orders.cid = customers.cid and 9 orders.pid = products.pid; View created. SQL> select * 2 from cat; TABLE_NAME TABLE_TYPE ------------------------------ ----------- AGENTS TABLE CUSTOMERS TABLE ORDERS TABLE PRODUCTS TABLE RETURNS VIEW SQL> desc returns; Name Null? Type ------------------------------- -------- ---- ORDNO NOT NULL NUMBER(6) MONTH CHAR(3) CID CHAR(4) AID CHAR(3) PID CHAR(3) QTY NUMBER(6) DOLLARS FLOAT(126) DISCNT FLOAT(63) PERCENT NUMBER(6) PRICE FLOAT(63) /* b) execute the below statement. then update the base table by */ /* changing the discnt for c001 to 13.0. now repeat the previous */ /* select statement from returns again. finally, change the */ /* discnt for c001 back to 10.0. */ SQL> select ordno, dollars, discnt, percent, price 2 from returns 3 where cid = 'c001'; ORDNO DOLLARS DISCNT PERCENT PRICE ---------- ---------- ---------- ---------- ---------- 1011 450 10 6 .5 1012 450 10 6 .5 1031 450 10 6 .5 1019 180 10 6 .5 1018 540 10 7 1 1023 450 10 6 1 1022 720 10 5 2 1025 720 10 5 1 1017 540 10 5 1 9 rows selected. SQL> update customers 2 set discnt = 13 3 where cid = 'c001'; 1 row updated. SQL> select ordno, dollars, discnt, percent, price 2 from returns 3 where cid = 'c001'; ORDNO DOLLARS DISCNT PERCENT PRICE ---------- ---------- ---------- ---------- ---------- 1011 450 13 6 .5 1012 450 13 6 .5 1031 450 13 6 .5 1019 180 13 6 .5 1018 540 13 7 1 1023 450 13 6 1 1022 720 13 5 2 1025 720 13 5 1 1017 540 13 5 1 9 rows selected. SQL> update customers 2 set discnt = 10 3 where cid = 'c001'; 1 row updated. SQL> select ordno, dollars, discnt, percent, price 2 from returns 3 where cid = 'c001'; ORDNO DOLLARS DISCNT PERCENT PRICE ---------- ---------- ---------- ---------- ---------- 1011 450 10 6 .5 1012 450 10 6 .5 1031 450 10 6 .5 1019 180 10 6 .5 1018 540 10 7 1 1023 450 10 6 1 1022 720 10 5 2 1025 720 10 5 1 1017 540 10 5 1 9 rows selected. /* c) test your ability to write expressions in terms of the returns */ /* view by selecting columns ordno, qty, dollars, discnt, percent, */ /* and price of all rows, with an extra column in the select */ /* statement calculating the customers cost (total). the extra */ /* expression should give the same value as dollars. */ SQL> select ordno, qty, dollars, discnt, percent, price, 2 qty * price - discnt / 100 * price * qty TOTAL 3 from returns; ORDNO QTY DOLLARS DISCNT PERCENT PRICE TOTAL ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1011 1000 450 10 6 .5 450 1012 1000 450 10 6 .5 450 1016 1000 500 0 6 .5 500 1031 1000 450 10 6 .5 450 1019 400 180 10 6 .5 180 1018 600 540 10 7 1 540 1013 1000 880 12 7 1 880 1015 1200 1104 8 7 1 1104 1020 600 600 0 7 1 600 1014 1200 1104 8 7 1 1104 1023 500 450 10 6 1 450 ORDNO QTY DOLLARS DISCNT PERCENT PRICE TOTAL ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1022 400 720 10 5 2 720 1025 800 720 10 5 1 720 1026 800 704 12 5 1 704 1017 600 540 10 5 1 540 1024 800 400 0 5 .5 400 1021 1000 460 8 5 .5 460 17 rows selected. /* d) create a new view, profits, based on the returns view, with columns */ /* ordno, cid, aid, pid, and profit, where the profit column has been */ /* calculated in the create view statement to equal qty times the */ /* price for that pid, minus 60% of this for wholesale cost, minus */ /* the percent royality for the particular agent and the discnt for */ /* customer in question. demonstrate the effectiveness of this by */ /* displaying the profits view. */ SQL> create view profits (ordno, cid, aid, pid, profit) 2 as select ordno, cid, aid, pid, 3 price * qty - .60 * price * qty - percent / 100 * price * qty - 4 discnt / 100 * price * qty 5 from returns; View created. SQL> select * 2 from profits; ORDNO CID AID PID PROFIT ---------- ---- --- --- ---------- 1011 c001 a01 p01 120 1012 c001 a01 p01 120 1016 c006 a01 p01 170 1031 c001 a01 p01 120 1019 c001 a02 p02 48 1018 c001 a03 p04 138 1013 c002 a03 p03 210 1015 c003 a03 p05 300 1020 c006 a03 p07 198 1014 c003 a03 p05 300 1023 c001 a04 p05 120 ORDNO CID AID PID PROFIT ---------- ---- --- --- ---------- 1022 c001 a05 p06 200 1025 c001 a05 p07 200 1026 c002 a05 p03 184 1017 c001 a06 p03 150 1024 c006 a06 p01 140 1021 c004 a06 p01 135 17 rows selected. BACK TO CS4660 PAGE.