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

ID: 3698096 • Letter: U

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. Find out if there is any product that has never been sold.

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 * from pvf_product

where NOT EXISTS ( select * from pvf_order_item

where pvf_order_item.pid = pvf_product.pid ) ;

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