Suppose you have created tables in assignment.
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.
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).
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.
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;