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;