Use the relational schema of Pine Valley Furniture (PVF) database system to crea

Question

Use the relational schema of Pine Valley Furniture (PVF) database system to create and execute SQL queries. Here is the relational schema:

pvf_cust (cid, name, address, city, state, zipcode)

pvf_order (oid, orderdate, cid)

     cid is a foreign key references customer(cid)

pvf_product (pid, prod_desc, prod_finish, price, prod_line_id)

pvf_order_item (oid, pid, quantity)

     oid is a foreign key references pvf_order(oid)

     pid is a foreign key references pvf_prod(pid)

1. Display each product’s pid, prod_desc, and total number of pieces being sold.

Explanation / Answer

Create statements :

create table pvf_cust

(

cid int primary key, name varchar(30), address varchar(70), city varchar(15), state varchar(20), zipcode varchar(6)

);

Note : Zipcode is just a number and is not used in mathematical calculations. Thus, it is just taken as string by declaring it varchar

create table pvf_order

(

oid int primary key, orderdate date, cid int foreign key references pvf_cust(cid)

);

create table pvf_product

(

pid int primary key, prod_desc varchar(70), prod_finish varchar(20), price int, prod_line_id int

);

create table pvf_order_item

(

oid int foreign key references pvf_order(oid), pid int foreign key references pvf_product(pid), quantity int)

);

Required query on the database :

select pid,(select p.prod_desc
        from pvf_product p,pvf_order_item oi
        where p.pid = oi.pid) as prod_desc, sum(quantity)
from pvf_order_item
group by oi.pid

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.