63 067
Assignments Done
99,1%
Successfully Done
In July 2018

Answer to Question #48248 in Databases | SQL | Oracle | MS Access for Asker31

Question #48248
For the follow tables:
create table customer(
cid integer, --- customer ID
cname varchar(50), --- customer name
primary key (cid));

create table product(
pid integer, --- product ID
pname varchar(50), ---- Product name
price number, --- price of product
primary key (pid));


create table orders(
oid integer, --- order ID
cid integer,--- customer ID
primary key (oid),
foreign key (cid) references customer(cid));

create table order_line(
oid integer, --- order ID
pid integer, --- product ID
quantity integer, --- quantity of product in the order
primary key (oid, pid),
foreign key (oid) references orders(oid),
foreign key (pid) references product(pid));


Write a PL/SQL procedure to print out the names of customers who have bought a certain product. The product name is the input parameter
Expert's answer
Question 1.
 
Create or Replace ProcedureCustomer_List(Product_name varchar)
Is
  v_customer_name varchar(50);
  Cursor c_Customer Is
    Select Distinct
      c.CNAME
     From
      order_line ol
    Left Join
      product p
    On
      ol.PID = p.PID
    Left Join
      orders o
    On
      ol.OID = o.OID
    Left Join
      customer c
    On
      o.CID = c.CID
    Where
      p.PNAME = Product_name;
Begin
  open c_Customer;
  LOOP
    Fetch c_Customer Into v_customer_name;
    Exit When c_Customer%NOTFOUND;
   
    dbms_output.put_line(v_customer_name);
  End LOOP;
  Close c_Customer;
End;

Need a fast expert's response?

Submit order

and get a quick answer at the best price

for any assignment or question with DETAILED EXPLANATIONS!

Comments

No comments. Be first!

Leave a comment

Ask Your question

Submit
Privacy policy Terms and Conditions