lab task adbms

task.sql

create table sales (
 id number(5) primary key,
 customer_id number(10),
 product_id number(5),
 quantity number(5)
)

drop table sales;
drop table products;

create table products(
 id number(5) primary key,
 product_id number(10),
 price number(5)
)

INSERT ALL
  INTO sales(id,customer_id, product_id, quantity) VALUES (1,1, 1, 100)
  INTO sales(id,customer_id, product_id, quantity) VALUES (2,1, 2, 200)
  INTO sales(id,customer_id, product_id, quantity) VALUES (3,2, 2, 300)
SELECT * FROM dual;

INSERT ALL
  INTO products(id, product_id, price) VALUES (1,1, 100)
  INTO products(id, product_id, price) VALUES (2,2,200)
  INTO products(id, product_id, price) VALUES (3,3,300)
SELECT * FROM dual;

-- 1
DECLARE
 customer_id number(5) := :input;
 cursor c1 is 
   SELECT distinct product_id from sales where customer_id = customer_id;

BEGIN
 dbms_output.put_line(customer_id);

 FOR customer in c1
 LOOP 
  dbms_output.put_line(customer.product_id);
 END LOOP; 

END;

-- 2
DECLARE
 customer_id number(5) := :input;
 total number(5) := 0;
 cursor c1 is 
   SELECT quantity from sales where customer_id = customer_id;

BEGIN
 dbms_output.put_line(customer_id);


 FOR customer in c1
 LOOP 
  total := total + customer.quantity;
 END LOOP; 
 
 dbms_output.put_line(total);
  
END;


-- 3
DECLARE
 cursor c1 is 
   SELECT product_id, price from products;

BEGIN
 FOR product in c1
 LOOP 
   dbms_output.put_line(product.product_id || ' ' || product.price);
 END LOOP; 
  
END;


-- 4

DECLARE
 cursor c1 is 
   SELECT c.customer_id id, sum(p.price) total from sales c join products p on c.product_id = p.product_id group by c.customer_id;

BEGIN
 FOR res in c1
 LOOP 
   dbms_output.put_line(res.id || ' total: ' || res.total);
 END LOOP; 
  
END;