Create a pl-sql procedure

Assignment Help Database Management System
Reference no: EM131278435

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;

Verified Expert

In this Project, I wrote a PL/SQL program for the following problems. Problem 1:created 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. Problem 2:created 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.

Reference no: EM131278435

Questions Cloud

How would the bonus ics affect the us trade deficit : In what way is Warren Buffett's plan the equivalent of a tariff? What would be its likely impact on American consumers?
Assume demand is steady throughout year : PBK uses 50 bags of whole bean coffee every month, assume demand is steady throughout year. PBK has signed contract to purchase coffee from Phis Roasters for price of $25 per bag and $85 fixed cost for every delivery independent of the order size. Th..
Something lawmakers should address : Do you think grievance procedures are something lawmakers should address? Or are they something that should remain between employees, their unions, and their employers? Justify your answer.
What our theories would say about your event : Your event should happen after the last current event you submitted (i.e., your third event cannot be older than your second). The first part of the current events assignment is summarizing the story in two or fewer paragraphs. Next, analyze the s..
Create a pl-sql procedure : Please write PL/SQL program for the following problems. You can use the attached SQL statements to create the tables -  create a PL/SQL procedure that given a category id, print out for each product under that category, the name of the product, the ..
Implement career management : As a manager, explain how you would implement career (talent) management. Justify your answer.
Find the pressure difference across the turbine : The turbine shown in Fig. P5.121 develops 100 hp when the flowrate of water is 20 ft3/s.
What is likely to happen to the value of the dollar : As the value of the U.S. dollar rises, what is likely to happen to the U.S. balance on current account? Explain.- What is likely to happen to the value of the dollar as the U.S. current-account deficit increases? Explain.-
Protected class of religion as compared : Explain the differences between an Employer's Title VII obligations for the protected class of "religion" as compared with all of the other protected classes under Title VII.

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd