User:Ecc1234/sandbox

kiran.. Fast guys..

CREATE OR REPLACE FUNCTION sf_CalculateAmount (p_customerid customer.customerid%TYPE, p_giftid gift.giftid%TYPE, p_quantity number) RETURN NUMBER IS

END;

create stored function sf_CalculateAmount which accept customerid,giftid and quantity to calculate the NetPayableamount.

1.Identify the price for the particular gift which has been ordered. 2.Find the numbercount as the number of times a customer has placed orders before. 3.calculate the amount as fallows:

amount=price*quantity

4.Fetch the discount from the gift table for the accepted giftid. 5. the numbercount determines loyalitydiscount percentage as shown below

table: numbercount	 loyalitydiscount 3-5			5 6-8			10 more than 8		15

NetPayableAmount=amount-(amount*(discount+loyalitydiscount)/100) Return NetPayableAmount, after rounding off to an integer (sure) 1)select g.customerid,customername from customer c inner join giftorder g on c.customerid=g.customerid group by g.customerid,customername having count(g.customerid)= (select max(count(customerid)) from giftorder group by customerid);

(sure) 2)SELECT giftid,giftname FROM gift WHERE giftid IN (SELECT giftid FROM gift WHERE price= (SELECT MIN(price) FROM gift) AND discount= (SELECT MAX(discount) FROM gift));

(sure) 3)select ORDERID,c.CUSTOMERID,BILLINGCITY from customer c inner join giftorder g on c.customerid=g.customerid where BILLINGCITY='Delhi';

(sure) 4)select ORDERID,c.CUSTOMERID,CUSTOMERNAME,GIFTNAME,NETPAYABLEAMOUNT from customer c join giftorder g on c.customerid=g.customerid join gift g1 on g.giftid=g1.giftid and c.billingcity=g.shippingcity group by ORDERID,c.CUSTOMERID,CUSTOMERNAME,GIFTNAME,NETPAYABLEAMOUNT;

(not sure) 5)select go.shippingcity,c.customerid,c.customername,go.netpayableamount from giftorder go, customer c where c.customerid=go.customerid and go.netpayableamount<= (select avg(g1.netpayableamount) from giftorder g1 where g1.shippingcity=go.shippingcity);

procedure CREATE OR REPLACE procedure sp_Insert_Update ( p_customerid IN customer.customerid%TYPE, p_giftid IN gift.giftid%TYPE, p_quantity IN number, p_shippingcity IN giftorder.shippingcity%TYPE, p_outvar OUT NUMBER) IS v_count NUMBER; v_id NUMBER; v_amt NUMBER; v_val NUMBER; BEGIN SELECT COUNT(orderid) INTO v_count FROM giftorder; IF v_count=0 THEN v_id:=1001; ELSE SELECT MAX(orderid) INTO v_id FROM giftorder; v_id:=v_id+1; END IF; v_amt:=sf_CalculateAmount(p_customerid,p_giftid,p_quantity); INSERT INTO giftorder(orderid,customerid,giftid,quantity,shippingcity,netpayableamount) VALUES( v_id,p_customerid,p_giftid,p_quantity,p_shippingcity,v_amt); SELECT availableitem INTO v_val FROM gift WHERE giftid=p_giftid; v_val:=v_val-p_quantity; UPDATE gift SET availableitem=v_val WHERE giftid=p_giftid; IF SQL%ROWCOUNT>=1 THEN p_outvar:=0; RETURN; END IF; END; CREATE OR REPLACE FUNCTION sf_CalculateAmount (p_customerid customer.customerid%TYPE, p_giftid gift.giftid%TYPE, p_quantity number) RETURN NUMBER IS v_dis NUMBER; v_price NUMBER; v_count NUMBER; v_amt NUMBER; v_loyal NUMBER; v_net NUMBER; BEGIN SELECT price INTO v_price FROM gift WHERE giftid=p_giftid; SELECT COUNT(customerid) INTO v_count FROM giftorder; v_amt:=v_price*p_quantity; SELECT discount INTO v_dis FROM gift WHERE giftid=p_giftid; IF v_count<=5 AND v_count>=3 THEN v_loyal:=5; END IF; IF v_count<=8 AND v_count>=6 THEN v_loyal:=10; END IF; IF v_count<8 THEN v_loyal:=15; END IF; v_net:=ROUND(v_amt-(v_amt*(v_dis+v_loyal)/100)); RETURN v_net; END;