Suppose you have created tables in assignment 1.
Please write PL/SQL program for the following problems. You can use the attached SQL statements to create the tables.
Problem 1: Please create a PL/SQL procedure that given a category id, print out for each product under that category, the name of the product, the average, the highest, and the lowest review score. Test your procedure with some category id.
Problem 2: Please create a PL/SQL function that returns the average rating of a product given a product ID. Please write an anonymous PL/SQL program to call this function.
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;