im_q2

2)kay mart
Kaymart Departmental Store has many products. A customer can buy many products
from the store on a particular date.
Product has product ID, product name, type, price.
Customer has customer ID, name, contact number, address, email ID.

Draw ERD for the above case study
Create tables according to ERD
Write a SQL query to display the customer details for the given contact number.
Write a SQL query display  the customer details along with the details of the products bought for the given start date and end date.
Write a Procedure to display the customer details and the total amount paid by the customers.
create table customer(
customer_id number(10)primary key,
customer_name varchar2(20),
customer_contact number(10),
customer_email varchar2(20),
customer_address varchar2(50)
)
insert into customer values (101,'Manikanta',9701641946,'mkottakota@gmail.com','Chennai');
insert into customer values (102,'Hari',9988776655,'hari@gmail.com','Vizag');
insert into customer values (103,'Kartik',7788991122,'kartik@gmail.com','Guntur');
insert into customer values (104,'Sravs',6644558899,'sravs@gmail.com','Tamilnadu');

create table product (
product_id number(10),
product_name varchar2(20),
product_type varchar2(20),
product_price number(10),
buy_date date,
product_customer_id number(10) references customer(customer_id) );

insert into product values (1,'Pencil','Stationary',5,TO_DATE('19-01-2018','dd-mm-yyyy'),101);
insert into product values (2,'Rexona','Soap',29,TO_DATE('23-01-2018','dd-mm-yyyy'),101);
insert into product values (3,'Colgate','Paste',46,TO_DATE('01-02-2018','dd-mm-yyyy'),102);
insert into product values (4,'Bag','General',230,TO_DATE('20-02-2018','dd-mm-yyyy'),103);
insert into product values (5,'LorialParice','Shampoo',550,TO_DATE('28-03-2018','dd-mm-yyyy'),104);


select * from product;
PRODUCT_ID	PRODUCT_NAME	PRODUCT_TYPE	PRODUCT_PRICE	BUY_DATE	PRODUCT_CUSTOMER_ID
1	Pencil	Stationary	5	19-JAN-18	101
2	Rexona	Soap	29	23-JAN-18	101
3	Colgate	Paste	46	01-FEB-18	102
4	Bag	General	230	20-FEB-18	103
5	LorialParice	Shampoo	550	28-MAR-18	104
Select * from customer;
CUSTOMER_ID	CUSTOMER_NAME	CUSTOMER_CONTACT	CUSTOMER_EMAIL	CUSTOMER_ADDRESS
101	Manikanta	9701641946	mkottakota@gmail.com	Chennai
102	Hari	9988776655	hari@gmail.com	Vizag
103	Kartik	7788991122	kartik@gmail.com	Guntur
104	Sravs	6644558899	sravs@gmail.com	Tamilnadu
3)Write a SQL query to display the customer details for the given contact number.
SQL> select * from customer where customer_contact=9701641946;
CUSTOMER_ID	CUSTOMER_NAME	CUSTOMER_CONTACT	CUSTOMER_EMAIL	CUSTOMER_ADDRESS
101	Manikanta	9701641946	mkottakota@gmail.com	Chennai

4) Write a SQL query display  the customer details along with the details of the products bought for the given start date and end date.
SQL>select product.*,customer_name,customer_email,customer_address
from product
join customer
on product_customer_id=customer_id
where buy_date between TO_DATE('01-01-2018','dd-mm-yyyy') and TO_DATE('28-02-2018','dd-mm-yyyy');
PRODUCT_ID	PRODUCT_NAME	PRODUCT_TYPE	PRODUCT_PRICE	BUY_DATE	PRODUCT_CUSTOMER_ID	CUSTOMER_NAME	CUSTOMER_EMAIL	CUSTOMER_ADDRESS
1	Pencil	Stationary	5	19-JAN-18	101	Manikanta	mkottakota@gmail.com	Chennai
2	Rexona	Soap	29	23-JAN-18	101	Manikanta	mkottakota@gmail.com	Chennai
3	Colgate	Paste	46	01-FEB-18	102	Hari	hari@gmail.com	Vizag
4	Bag	General	230	20-FEB-18	103	Kartik	kartik@gmail.com	Guntur
5)Write a Procedure to display the customer details and the total amount paid by the customers.

create or replace procedure manik008
is
cursor mycur is select customer_id,customer_name,sum(product_price) as total_amount
from customer,product where customer_id = product_customer_id group by customer_id,customer_name;
myrow  mycur%rowtype;
begin
open mycur;
loop
fetch mycur into myrow;
exit when mycur%notfound;
dbms_output.put_line(myrow.customer_id||'  '|| myrow.customer_name||'  '||myrow.total_amount);
end loop;
close mycur;
end manik008;
/
exec manik008;
Procedure created.
104 Sravs 550
101 Manikanta 34
103 Kartik 230
102 Hari 46
CUSTOMER_ID	CUSTOMER_NAME	TOTAL_AMOUNT
104	Sravs	550
101	Manikanta	34
103	Kartik	230
102	Hari	46