User:Ecc100/sandbox

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 END;

CREATE OR REPLACE procedure sp_Validation ( 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

END;

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

END;

1. procedure sp_Validation with customerid,giftid,quantity and shippingcity as IN parameter and p_outvar as OUT parameter and perform the following

. check the existence of customerid in the customertable set p_outvar to -1 and return if customerid is not p[resent in customer table

. check the existence of giftid in the gift table set p_outvar to -2 and return if giftid is not present in gift table

. check if the accepted quantity is greater than zero set p_outvar to -3 and return if quantity is not greater than zero

. check the value of availableitem for the given giftid is greater than zero set p_outvar to -4 and return if available for the given giftid is not greater than zero

.check that the value of quantity is less than present available value set p_outvar t0 -5 and return if quantity is not less than present availableitem value

. check shippingcity has value in "Bangalore", Delhi","Mysore","Chandigarh","Chennai", or "Mumbai". set p_outvar to -6 and return if shipping is not one of the cities mentioned above

if all valid set p_outvar to 0 and return

3)create stored procedure sp_Insert_Update which accepts customerid,giftid,quantity,shippingcity as IN parameter and p_outvar as OUT parameter. 1. if no record is present in the giftorder table,then start with a value of 1001 foe orderid.otherwise generate the next unique orderid by incrementing the last orderid by 1.

2.identify the netpayableamount by calling the function sf_CalculateAmount

3.insert the parameter in the appropriate order into the giftorder table

4.update the gift table by decreasing the availableitem value by the quantity accepted for the accepted giftid.

5.set p_outvar to 0 and return, from theprocedure after successful insertion and update of records in the appropriate tables

guys this is d solution

(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);

procedures 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;