task PLSQL

TABLE CREATION :-
-------------------------

1)  CREATE TABLE Customer
    (
        Customer_id VARCHAR(6) PRIMARY KEY,
        Fname VARCHAR2(15) NOT NULL,
        Mname VARCHAR2(15),
        Lname VARCHAR2(15) NOT NULL,
        Address VARCHAR2(30),
        City VARCHAR2(20),
        Pincode VARCHAR(6),
        State VARCHAR2(15) DEFAULT 'Gujarat',
        Gender CHAR(1) NOT NULL CHECK(Gender IN('M','F')),
        DOB DATE
    );

2)  CREATE TABLE Product1
    (
        Product_id VARCHAR(5) PRIMARY KEY,
        Description VARCHAR2(30),
        QOH NUMBER(3) CHECK(QOH>0),
        Reorder_lvl NUMBER(5) CHECK(Reorder_lvl>0),
        Cost_price NUMBER(7) CHECK(Cost_price>0),
        Sell_price NUMBER(7) CHECK(Sell_price>0)
    );


3)  CREATE TABLE Order_
    (
        Order_id VARCHAR(13) PRIMARY KEY CHECK(Order_id LIKE 'O_%'),
        Customer_id VARCHAR(6) REFERENCES Customer(Customer_id) ON DELETE CASCADE,
        Order_date DATE DEFAULT SYSDATE,
        Status VARCHAR2(15) CHECK(Status IN('In-process','Fulfilled'))
    );



4)  CREATE TABLE Order_detail
    (
        Order_id VARCHAR(13) REFERENCES Order_(Order_id) ON DELETE CASCADE,
        Product_id VARCHAR(5) REFERENCES Product1(Product_id) ON DELETE CASCADE,
        Quantity NUMBER(5) CHECK(Quantity>0)
    );

INSERT DATA :-
--------------------

1)  INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Saloni','Hiteshbhai','Jariwala','Gopipura','Surat','395001','Gujarat','F','30-Sep-2001');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Devang','Hiteshbhai','Jariwala','Balaji Road','Rajkot','360001',DEFAULT,'M','22-Nov-2004');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Aditi','Vinaybhai','Chapadia','Vesu','Surat','395004',DEFAULT,'F','26-Jan-2001');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Anita','Hiteshbhai','Jariwala','Sahin Baug','Ahemdabad','380006',DEFAULT,'F','04-Apr-1976');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Nirali','Tejasbhai','Parekh','Linkin Road','Mumbai','400037','Maharastra','F','10-Feb-1998');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Rohan','Viralbhai','Tankariya','Khan Park Road','Old Delhi','110020','Delhi','M','12-Sep-2000');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Kaushal','Bharatbhai','Khasiwala','Alem','Kochin','682008','Kerala','M','16-May-1993');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Ritu',' ','Modi','Juhu','Mumbai','400049','Maharastra','F','12-Dec-2007');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Nishi','Kishorbhai','Rana','Howrah','Kolkata','712408','West Bengal','F','04-Mar-1999');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Payal','Mehulbhai','Chauhan','Mughal Road','Bhopal','462042','Madhya Pradesh','F','16-Oct-1995');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Parth','Yogeshbhai','Rana','Mall Road','Manali','695505','Himachal','M','28-Nov-2003');
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Aashi','Rupeshbhai','Shah','Piplod','Surat','395005',DEFAULT,'F',NULL);
    INSERT INTO Customer VALUES('C'||LPAD(sq_1.NEXTVAL,5,0),'Sanjana','Rajeshbhai','Ahir','Udhna','Navsari','395005',DEFAULT,'F',NULL);

2)  INSERT INTO Product VALUES('P1001','Hard Drive','15','5','5000','5576');
    INSERT INTO Product1 VALUES('P1002','Pen Drive','50','10','375','400');
    INSERT INTO Product1 VALUES('P1003','Mouse','29','5','150','200');
    INSERT INTO Product1 VALUES('P1004','Motherboard','10','4','7500','7000');
    INSERT INTO Product1 VALUES('P1005','Webcam','20','5','899','1000');
    INSERT INTO Product1 VALUES('P1006','USB Cable','150','50','300','350');
    INSERT INTO Product1 VALUES('P1007','Laptop Charger','30','5','900','1000');
    INSERT INTO Product1 VALUES('P1008','SSD','5','10','10200','11500');
    INSERT INTO Product1 VALUES('P1009','Joystick','20','30','250','290');
    INSERT INTO Product1 VALUES('P1010','Keyboard','50','30','150','200');
    INSERT INTO Product1 VALUES('P1020','Printer','20','5','14300','15000');
    INSERT INTO Product1 VALUES('P1030','Hard Disk','15','5','3848','4000');
    INSERT INTO Product1 VALUES('P1040','Headphone','50','25','4596','5000');
    INSERT INTO Product1 VALUES('P1050','CD Drive','100','20','1500','2000');
    INSERT INTO Product1 VALUES('P1060','Laptop','10','4','90000','115000');

3)  INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00008','05-Jan-2020','Fulfilled');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00006','20-Jul-2019','Fulfilled');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00001','01-Aug-2020','In-process');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00010','13-Aug-2020','In-process');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00002','19-Feb-2018','Fulfilled');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00005','30-Sep-2019','Fulfilled');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00003','15-Mar-2020','In-process');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00007','20-Feb-2019','In-process');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00012','11-Oct-2018','Fulfilled');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00004','24-Apr-2020','In-process');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00009','09-May-2018','Fulfilled');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00011','14-Jun-2019','Fulfilled');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00001','01-Sep-2020','Fulfilled');
    INSERT INTO Order_ VALUES(('O_'||(sq_2.NEXTVAL)||'_'||TO_CHAR(SYSDATE,'DD_MM_YY')),'C00011','05-Sep-2020','Fulfilled');


4)  INSERT INTO Order_detail VALUES('O_3_28_10_20','P1001','2');
    INSERT INTO Order_detail VALUES('O_2_28_10_20','P1003','5');
    INSERT INTO Order_detail VALUES('O_4_28_10_20','P1004','1');
    INSERT INTO Order_detail VALUES('O_6_28_10_20','P1006','3');
    INSERT INTO Order_detail VALUES('O_7_28_10_20','P1007','1');
    INSERT INTO Order_detail VALUES('O_8_28_10_20','P1008','1');
    INSERT INTO Order_detail VALUES('O_10_28_10_20','P1010','5');
    INSERT INTO Order_detail VALUES('O_11_28_10_20','P1020','6');
    INSERT INTO Order_detail VALUES('O_12_28_10_20','P1030','2');
    INSERT INTO Order_detail VALUES('O_3_28_10_20','P1040','3');
    INSERT INTO Order_detail VALUES('O_1_28_10_20','P1050','20');
    INSERT INTO Order_detail VALUES('O_2_28_10_20','P1010','2');
    INSERT INTO Order_detail VALUES('O_4_28_10_20','P1009','2');
    INSERT INTO Order_detail VALUES('O_5_28_10_20','P1008','1');
    INSERT INTO Order_detail VALUES('O_6_28_10_20','P1007','1');
    INSERT INTO Order_detail VALUES('O_7_28_10_20','P1006','5');
    INSERT INTO Order_detail VALUES('O_8_28_10_20','P1005','7');
    INSERT INTO Order_detail VALUES('O_9_28_10_20','P1020','2');
    INSERT INTO Order_detail VALUES('O_12_28_10_20','P1004','2');
    INSERT INTO Order_detail VALUES('O_1_28_10_20','P1002','3');
    INSERT INTO Order_detail VALUES('O_5_28_10_20','P1005','2');
    INSERT INTO Order_detail VALUES('O_9_28_10_20','P1009','2');
    INSERT INTO Order_detail VALUES('O_9_28_10_20','P1002','5');


sequences:-
-------------------------------------------------------------------------------------
1.) CREATE sequence sq_1
    maxvalue 99999
    cycle
    cache 5;

2.) CREATE sequence sq_2
    maxvalue 999
    cycle;

INDEX:-
-------------------------------------------------------------------------------------
1.) CREATE UNIQUE INDEX IDX_1 ON Order_detail (Order_Id,Product_Id);
2.) CREATE bitmap INDEX IND_2 ON customer(gender);
3.) CREATE INDEX IND_3 ON customer (UPPER(city));
4.) CREATE INDEX IND_4 ON Product((Sell_price-Cost_price)*100/Cost_price);
5.)     CREATE INDEX IND_5 ON customer(pincode) REVERSE;

exercise computation ON tabledata:-
-------------------------------------------------------------------------------------
1.)ALTER TABLE customer
add constraint chk_pincode CHECK(LENGTH(Pincode)=6);


2.)SELECT Product_Id,Description,QOH
    FROM product1
    WHERE QOH>0;

3.)SELECT Fname,Mname,Lname
    FROM customer
    WHERE dob IS NULL;

4.)SELECT *
    FROM order_
    WHERE status='Fulfilled';

5.)SELECT *
    FROM customer
    WHERE pincode LIKE '395%';


operators:-
-----------------------------------------------------------------------------------------
1.)SELECT *
    FROM product
    WHERE Reorder_lvl < QOH;

2.)SELECT Product_Id,Description,((Sell_price-Cost_price)*100/Cost_price) AS Profit_In_Percentage
    FROM Product;

3.)SELECT customer_id,Fname,Mname,Lname,city
    FROM customer
    WHERE city LIKE '_a%';

4.)SELECT *
    FROM product
    WHERE Cost_Price BETWEEN '1000' AND '10000';

5.)SELECT *
    FROM customer
    WHERE UPPER(City) LIKE UPPER('Surat') OR UPPER(City) LIKE UPPER('ahmedabad') OR UPPER(City) LIKE UPPER('mumbai');

6.)SELECT Fname||' '||Mname||' '||Lname AS Fullname,City,State
    FROM Customer
    WHERE UPPER(State) LIKE UPPER('Gujarat');

7.)

8.)SELECT SUBSTR(fname,1,1)||'.'|| ' ' ||SUBSTR(mname,1,1)||'.'||' ' ||lname AS Name_of_Customer
    FROM Customer;

9.)SELECT Fname||' '||Mname||' '||Lname AS Name_of_Customer
    FROM Customer
    WHERE Customer_Id IN (SELECT Customer_Id
                     FROM Order_
                     WHERE Order_id IN (SELECT Order_id
                                FROM Order_detail
                                WHERE Product_Id IN(SELECT Product_Id
                                FROM Product
                                WHERE UPPER(Description) IN('HARD DISK','PEN DRIVE','MOTHERBOARD','HEADPHONE'))));

10.)SELECT Fname||' '||Mname||' '||Lname AS Name_of_Customer
    FROM Customer
    WHERE Customer_Id NOT IN(SELECT Customer_Id
                FROM Order_);


FUNCTION:-
-------------------------------------------------------------------------------------------

1.)
    SELECT Fname||' '||Mname||' '||Lname AS Name_of_Customer
    FROM Customer
    WHERE LENGTH(Lname)>6 AND TO_CHAR(DOB,'YYYY')='1993';

2.)
    SELECT Description
    FROM Product
    WHERE INSTR (UPPER(Description),UPPER('DRIVE'))<>0;

3.)
    SELECT Description,LPAD(Sell_price,10,'*') AS Selling_price
    FROM product
    WHERE Product_Id IN(SELECT Product_Id
                 FROM Order_detail
                 WHERE Order_Id IN(SELECT Order_Id
                          FROM Order_
                          WHERE TO_CHAR(Order_date,'MON')IN('FEB','JAN')));

4.)
    SELECT COUNT(product_id) AS number_of_product
    FROM product
    WHERE sell_price = '65000' OR sell_price > '65000';

5.)
    SELECT AVG(sell_price) AS avg_product_selling_price
    FROM product;

6.)
    SELECT c.Fname||' '||c.Mname||' '||c.Lname AS Name,COUNT(r.Order_Id) AS Total_Order
    FROM customer c,order_ r
    WHERE c.customer_id=r.customer_id
    GROUP BY c.customer_id,c.Fname||' '||c.Mname||' '||c.Lname ;

7.) SELECT TO_CHAR(Order_date,'DD-MONTH YYYY ') AS Order_Date
    FROM Order_;

8.)
    SELECT TRUNC(SYSDATE-Order_date) AS Date_Difference
    FROM Order_
    WHERE UPPER(Status) LIKE 'IN-PROCESS';

9.)
    SELECT*
    FROM order_
    WHERE order_date BETWEEN sysdate-10 AND SYSDATE;

10.)
    SELECT o.order_id ,p.product_id,(p.sell_price*o.quantity) AS total_amount
    FROM order_detail o,product p
    WHERE p.product_id = o.product_id;



join AND correlation:-
-----------------------------------------------------------------------------------------------

1.)
    SELECT p.Description,p.Product_Id,COUNT(o.Quantity) Total_Quantity
    FROM Order_detail o,Product p
    WHERE p.Product_Id=o.Product_Id
    GROUP BY o.Product_Id,p.Description,p.Product_Id;

2.)
    SELECT p.Description
    FROM Product p,Order_ o,Order_detail od,Customer c
    WHERE p.Product_Id=od.Product_Id AND o.Order_Id=od.Order_Id AND o.Customer_Id=c.Customer_Id AND TO_CHAR(o.Order_date,'YYYY')=TO_CHAR(SYSDATE,'YYYY') AND c.Fname LIKE 'Ritu' AND c.Lname LIKE 'Modi';

3.)
    SELECT p.Description,od.Quantity
    FROM Product P,Order_ o,Order_detail od
    WHERE p.Product_Id=od.Product_Id AND o.Order_Id=od.Order_Id AND TO_CHAR(o.Order_Date,'MON')=TO_CHAR(SYSDATE,'MON') AND o.Status LIKE 'Fulfilled';

4.)