Assignment title: Information


Suppose you have created tables in assignment 1. Please write an anonymous PL/SQL program for each the following problems. You can use the attached SQL statements to create the tables. Problem 1: Write an anonymous PL/SQL program to compute the sum of 1, 3, 5, 7, 9. You must use a loop. Tip: consider how to update your loop variable. [30 points] Problem 2: Use an implicit cursor to print out the name of category of product with pid = 1. Please handle exceptions. Note that your code needs to work for any data in the database (e.g., you cannot manually look up category id). [30 points] Problem 3: Use an explicit cursor to print out the names of products under the category TV and with screen size (the feature size) of at least 42 inches. [40 points] Sample code to create the tables. drop table review; drop table product_feature; drop table product; drop table feature; drop table category; drop table users; create table users( userid integer, uname varchar(50), password varchar(50), primary key (userid)); create table category( cid integer, cname varchar(50), primary key (cid)); create table feature ( fid integer, fname varchar(50), primary key (fid)); create table product ( pid integer, cid integer, pname varchar(50), brand varchar(50), price number, primary key(pid), foreign key (cid) references category(cid) ); create table product_feature( pid integer, fid integer, fvalue number, primary key(pid,fid), foreign key(pid) references product, foreign key(fid) references feature); create table review( rid integer, userid integer, pid integer, rdate date, score integer, rcomment varchar(200), primary key(rid), foreign key (userid) references users(userid), foreign key (pid) references product(pid) ); ---- insert into users values (1,'user1','xyz0376'); insert into users values (2,'user2','87ujh6'); insert into users values (3,'user3','po954jn'); --------- insert into category values(1,'TV'); insert into category values(2,'Cell Phone'); insert into category values(3,'Laptop'); ------- -- size represents screen size in inches insert into feature values(1,'size'); insert into feature values(2,'weight'); insert into feature values(3,'memory'); insert into feature values(4,'harddisk'); insert into feature values(5,'storage'); -------------- insert into product values(1,1,'Vizio M65-C1','Vizio',1500); insert into product values(2,1,'TCL 40FS3800','TCL',250); insert into product values(3,2,'Apple iPhone 6 Plus','Apple',874); insert into product values(4,2,'Samsung Galaxy S7 Edge','Samsung',769); insert into product values(5,3,'Apple MacBook MLHE2LL/A','Apple',1200); insert into product values(6,3,'Dell XPS 13', 'Dell',999); ---------- insert into product_feature values(1,1,65); insert into product_feature values(1,2,67); insert into product_feature values(2,1,40); insert into product_feature values(2,2,16); insert into product_feature values(3,1,5.5); insert into product_feature values(3,5,64); insert into product_feature values(4,1,5.5); insert into product_feature values(4,5,32); insert into product_feature values(5,1,12); insert into product_feature values(5,2,3.2); insert into product_feature values(5,3,8); insert into product_feature values(5,4,256); insert into product_feature values(6,1,13); insert into product_feature values(6,2,2.9); insert into product_feature values(6,3,8); insert into product_feature values(6,4,128); ------- insert into review values(1,1,1,date '2016-1-1',5.0,'great price for screen size'); insert into review values(2,2,1,date '2016-1-1',5.0,null); insert into review values(3,3,1,date '2016-8-1',4.0,'good quality picture'); insert into review values(4,1,2,date '2016-5-12',3.0,'too small for me'); insert into review values(5,2,2,date '2016-6-1',5.0,null); insert into review values(6,3,2,date '2016-8-12',5.0,'cheap but very good quality'); insert into review values(7,1,3,date '2016-6-12',5.0,'screen size, battery life are great'); insert into review values(8,2,4,date '2016-6-1',5.0,null); insert into review values(9,3,4,date '2016-8-12',3.0,'not enough storage'); insert into review values(10,1,5,date '2016-7-12',5.0,'large flash drive, sharp image'); insert into review values(11,2,5,date '2016-8-1',5.0,null); insert into review values(12,3,6,date '2016-8-30',4.0,'good product, storage a bit small'); commit;