Super_shop_database.sql

CONNECT USER1/PASS1;

DROP TABLE SCHEDULE;
DROP TABLE TRANSECTION;
DROP TABLE SALES;
DROP TABLE PRODUCT;
DROP TABLE EMPLOYEE;

CREATE TABLE PRODUCT(
	P_ID NUMBER(5) NOT NULL,
	P_NAME VARCHAR(20) UNIQUE,
	P_TYPE VARCHAR(30),
	P_DESCRIPTION VARCHAR(60),
	P_PRICE NUMBER(*,2)
);

CREATE TABLE EMPLOYEE(
    E_NO NUMBER(5) NOT NULL,
	E_NAME VARCHAR(15),
	E_ADDRESS VARCHAR(30),
	AGE INT CHECK(AGE>=20 AND AGE<=50),
	GENDER VARCHAR(6),
	PHN_NUMBER NUMBER(12),
	JOIN_DATE DATE,
	E_TYPE VARCHAR(10),
	SALARY NUMBER(*,2)
);

CREATE TABLE SALES(
	S_ID INT NOT NULL,
	S_DATE_TIME timestamp,
	P_ID NUMBER(5),
	S_QUANTITY INT,
	E_ID NUMBER(5),
	S_AMOUNT NUMBER(*,2),
	S_PAID_AMOUNT_TYPE VARCHAR(10)
);
 
CREATE TABLE TRANSECTION(
	T_ID INT NOT NULL,
	S_ID INT
);

CREATE TABLE SCHEDULE(
	E_ID NUMBER(5) NOT NULL,
	WORKING_START TIMESTAMP,
	WORKING_END TIMESTAMP
);


--CREATE PRIMARY KEY AND FOREIGN KEY AFTER THE CREATION OF TABLE
ALTER TABLE PRODUCT DROP CONSTRAINT CONS_PRIMARY_PRODUCT;
ALTER TABLE PRODUCT ADD CONSTRAINT CONS_PRIMARY_PRODUCT PRIMARY KEY(P_ID);

ALTER TABLE EMPLOYEE DROP CONSTRAINT CONS_PRIMARY_EMPLOYEE;
ALTER TABLE EMPLOYEE ADD CONSTRAINT CONS_PRIMARY_EMPLOYEE PRIMARY KEY(E_NO);

ALTER TABLE SALES DROP CONSTRAINT CONS_PRIMARY_SALES;
ALTER TABLE SALES DROP CONSTRAINT CONS_FOREIGN1_SALES;
ALTER TABLE SALES DROP CONSTRAINT CONS_FOREIGN2_SALES;
ALTER TABLE SALES ADD CONSTRAINT CONS_PRIMARY_SALES PRIMARY KEY(S_ID);
ALTER TABLE SALES ADD CONSTRAINT CONS_FOREIGN1_SALES FOREIGN KEY(P_ID) REFERENCES PRODUCT(P_ID) ON DELETE CASCADE;
ALTER TABLE SALES ADD CONSTRAINT CONS_FOREIGN2_SALES FOREIGN KEY(E_ID) REFERENCES EMPLOYEE(E_NO) ON DELETE CASCADE;

ALTER TABLE TRANSECTION DROP CONSTRAINT CONS_FOREIGN_TRANSECTION;
ALTER TABLE TRANSECTION ADD CONSTRAINT CONS_FOREIGN_TRANSECTION FOREIGN KEY(S_ID) REFERENCES SALES(S_ID) ON DELETE CASCADE;

ALTER TABLE SCHEDULE DROP CONSTRAINT CONS_FOREIGN_SCHEDULE;
ALTER TABLE SCHEDULE ADD CONSTRAINT CONS_FOREIGN_SCHEDULE FOREIGN KEY(E_ID) REFERENCES EMPLOYEE(E_NO) ON DELETE CASCADE;


--SHOW CREATED TABLES
DESCRIBE PRODUCT;

DESCRIBE EMPLOYEE;

DESCRIBE SALES;

DESCRIBE TRANSECTION;

DESCRIBE SCHEDULE;


--ADD A COLUMN IN PRODUCT TABLE
ALTER TABLE PRODUCT ADD P_COMPANY VARCHAR(15);


--MODIFY TYPE OF A COLUMN
ALTER TABLE PRODUCT MODIFY P_NAME VARCHAR(35);


--DELETE A COLUMN
ALTER TABLE EMPLOYEE DROP COLUMN PHN_NUMBER;


--RENAME A COLUMN
ALTER TABLE EMPLOYEE RENAME COLUMN E_NO TO E_ID;


SET SERVEROUTPUT ON;
-- CREATE A SEQUENCE
DROP SEQUENCE SALE_ID_SEQUENCE;
CREATE SEQUENCE SALE_ID_SEQUENCE;

-- USING SEQUENCE FOR TRANSECTION TABLES TO INCRIMENT TRANSECTION ID FOR EVERY INSERTION
DROP TRIGGER SALE_ID_TRIGGER;
CREATE OR REPLACE TRIGGER SALE_ID_TRIGGER BEFORE INSERT ON SALES FOR EACH ROW
	BEGIN
		SELECT SALE_ID_SEQUENCE.NEXTVAL 
			INTO :NEW.S_ID
				FROM DUAL;
	END;
/

SET SERVEROUTPUT ON;

DROP TRIGGER TOTAL_COST;
CREATE OR REPLACE TRIGGER TOTAL_COST BEFORE INSERT ON SALES FOR EACH ROW
DECLARE
   PRICE PRODUCT.P_PRICE%TYPE ;
   QUANTITY SALES.S_QUANTITY%TYPE ;

  	BEGIN
	    SELECT P_PRICE INTO PRICE 
	   		FROM PRODUCT
	   			WHERE P_ID=:NEW.P_ID;
      :NEW.S_AMOUNT := PRICE*:NEW.S_QUANTITY;

	END;
/



INSERT INTO PRODUCT VALUES (00023,'ACI_RICE_PACK','RICE','ACI_NUTRILIFE_LOW_GI_RICE_PACK_1KG',70.00,'ACI');
INSERT INTO PRODUCT VALUES (00032,'AARONG_DAIRY_MILK','MILK_POWDER',' L_F_MILK_POWDER_BIB_350GM',270.00,'AARONG');
INSERT INTO PRODUCT VALUES (00011,'VEOLA-SOYABEAN_OIL_5_LITTER','OIL','COOKING_OIL',575.00,'VEOLA');
INSERT INTO PRODUCT VALUES (00034,'CADBURY_DAIRY_MILK','CHOCOLATE','CADBURY_DAIRY_MILK_SILK_ROAST_ALMOND_60G',130.00,'CADBURY');
INSERT INTO PRODUCT VALUES (00054,'DOVE_BODY_LOTION','LOTION','BODY_LOTION',370.00,'DOVE');
INSERT INTO PRODUCT VALUES (00033,'DOVE_SHAMPOO','SHAMPOO','MILD_SHAMPOO',375.00,'DOVE');
INSERT INTO PRODUCT VALUES (00090,'DOVE_BEAUTY_BAR','SOAP','BODY_SOAP',85.00,'DOVE');
INSERT INTO PRODUCT VALUES (00088,'HORLICKS_BOTTLE','ENERGY_DRINK','HORLICKS_BOTTLE_200GM',195.00,'HORLICKS');
INSERT INTO PRODUCT VALUES (00056,'HIMALEYA_FWASH','FACE_WASH','MILD_FACE_WASH',122.00,'HIMALEYA');
INSERT INTO PRODUCT VALUES (00077,'KUMARIKA_HAIR_OIL','HAIR_OIL','KUMARIKA_HARBAL_HAIR_OIL_200ML',150.00,'KUMARIKA');


INSERT INTO EMPLOYEE VALUES (10001, 'JHON', '55_K_STREET',27,'MALE','15-MAY-2015','FULL_TIME',10000.00);
INSERT INTO EMPLOYEE VALUES (10002, 'MARY', '10_M_STREET',23,'FEMALE','05-JAN-2014','PART_TIME',5000.00);
INSERT INTO EMPLOYEE VALUES (10003, 'LUCY', '3_H_STREET',30,'FEMALE','10-APR-2013','FULL_TIME',10000.00);
INSERT INTO EMPLOYEE VALUES (10004, 'MARTIN', '21_M_STREET',21,'MALE','25-JUL-2014','PART_TIME',5000.00);
INSERT INTO EMPLOYEE VALUES (10005, 'HARRY', '9_P_STREET',31,'MALE','12-FEB-2013','FULL_TIME',10000.00);
INSERT INTO EMPLOYEE VALUES (10006, 'TOM', '47_R_STREET',25,'MALE','05-SEP-2014','PART_TIME',5000.00);
INSERT INTO EMPLOYEE VALUES (10007, 'JENY', '68_K_STREET',26,'FEMALE','14-APR-2015','FULL_TIME',10000.00);
INSERT INTO EMPLOYEE VALUES (10008, 'LILLY', '2_P_STREET',23,'FEMALE','20-JAN-2014','PART_TIME',5000.00);
INSERT INTO EMPLOYEE VALUES (10009, 'ROSS', '31_R_STREET',30,'MALE','10-DEC-2013','FULL_TIME',10000.00);
INSERT INTO EMPLOYEE VALUES (10010, 'DANNY', '77_M_STREET',28,'MALE','07-JAN-2014','PART_TIME',5000.00);


INSERT INTO SALES(S_DATE_TIME,P_ID,S_QUANTITY,E_ID,S_PAID_AMOUNT_TYPE) VALUES ('01-MAY-2016 2:00:00',00023,2,10002,'CASH');
INSERT INTO SALES(S_DATE_TIME,P_ID,S_QUANTITY,E_ID,S_PAID_AMOUNT_TYPE) VALUES ('01-MAY-2016 2:00:00',00032,1,10001,'CASH');
INSERT INTO SALES(S_DATE_TIME,P_ID,S_QUANTITY,E_ID,S_PAID_AMOUNT_TYPE) VALUES ('02-MAY-2016 9:05:00',00011,3,10002,'CREDIT');
INSERT INTO SALES(S_DATE_TIME,P_ID,S_QUANTITY,E_ID,S_PAID_AMOUNT_TYPE) VALUES ('03-MAY-2016 2:00:00',00023,2,10002,'CASH');
INSERT INTO SALES(S_DATE_TIME,P_ID,S_QUANTITY,E_ID,S_PAID_AMOUNT_TYPE) VALUES ('03-MAY-2016 2:00:00',00032,1,10001,'CASH');
INSERT INTO SALES(S_DATE_TIME,P_ID,S_QUANTITY,E_ID,S_PAID_AMOUNT_TYPE) VALUES ('03-MAY-2016 2:00:00',00011,3,10002,'CASH');
INSERT INTO SALES(S_DATE_TIME,P_ID,S_QUANTITY,E_ID,S_PAID_AMOUNT_TYPE) VALUES ('04-MAY-2016 8:04:00',00023,2,10002,'CREDIT');
INSERT INTO SALES(S_DATE_TIME,P_ID,S_QUANTITY,E_ID,S_PAID_AMOUNT_TYPE) VALUES ('04-MAY-2016 8:04:00',00032,1,10001,'CREDIT');
INSERT INTO SALES(S_DATE_TIME,P_ID,S_QUANTITY,E_ID,S_PAID_AMOUNT_TYPE) VALUES ('05-MAY-2016 9:05:00',00011,3,10002,'CASH');


INSERT INTO TRANSECTION(T_ID,S_ID) VALUES(01,01);
INSERT INTO TRANSECTION(T_ID,S_ID) VALUES(01,02);
INSERT INTO TRANSECTION(T_ID,S_ID) VALUES(02,03);
INSERT INTO TRANSECTION(T_ID,S_ID) VALUES(03,04);
INSERT INTO TRANSECTION(T_ID,S_ID) VALUES(03,05);
INSERT INTO TRANSECTION(T_ID,S_ID) VALUES(03,06);
INSERT INTO TRANSECTION(T_ID,S_ID) VALUES(04,07);
INSERT INTO TRANSECTION(T_ID,S_ID) VALUES(04,08);
INSERT INTO TRANSECTION(T_ID,S_ID) VALUES(05,09);


INSERT INTO SCHEDULE VALUES (10002,'01-MAY-2016 1:00:00','01-MAY-2016 9:00:00');
INSERT INTO SCHEDULE VALUES (10004,'02-MAY-2016 1:00:00','02-MAY-2016 9:00:00');
INSERT INTO SCHEDULE VALUES (10006,'02-MAY-2016 1:00:00','02-MAY-2016 9:00:00');
INSERT INTO SCHEDULE VALUES (10008,'03-MAY-2016 1:00:00','03-MAY-2016 9:00:00');
SAVEPOINT CONT_4;
INSERT INTO SCHEDULE VALUES (10010,'05-MAY-2016 1:00:00','05-MAY-2016 9:00:00');
SAVEPOINT CONT_5;
ROLLBACK TO SAVEPOINT CONT_4;

SELECT P_ID,P_NAME,P_TYPE,P_DESCRIPTION ,P_PRICE,P_QUANTITY FROM PRODUCT;


SELECT * FROM EMPLOYEE;


--UPDATE PRICE OF A PRODUCT
UPDATE PRODUCT SET P_PRICE=275.00 WHERE P_NAME='AARONG_DAIRY_MILK';


--DELETE AN EMPLOYEE ID
DELETE FROM EMPLOYEE WHERE E_ID=10010;


--SELECT NAME OF FULL TIME EMPOYEE
SELECT E_NAME FROM EMPLOYEE WHERE E_TYPE='FULL_TIME' OR SALARY=10000.00;


--PATTERN MATCHING OF ADDRESS
SELECT E_ID,E_NAME FROM EMPLOYEE WHERE E_ADDRESS LIKE '%M_STREET%';


--ORGANIZE PRODUCT BY PRODUCT ID IN DESCENDING ORDER
SELECT P_ID,P_NAME AS NAME,P_PRICE AS PRICE FROM PRODUCT ORDER BY P_ID DESC;


--SELECT SPECIFIC COLUMNS,ALL ROWS
SELECT S_ID,S_DATE_TIME,P_ID,S_QUANTITY,E_ID,S_PAID_AMOUNT_TYPE FROM SALES;


--SELECT ALL COLUMNS,ALL ROWS
SELECT * FROM SCHEDULE;


--USE OF DISTINCT
SELECT DISTINCT(E_TYPE) FROM EMPLOYEE;


--CALCULATED FIELD
SELECT E_ID,(SALARY/5) AS SALARY_DIVIDED_BY_FIVE FROM EMPLOYEE WHERE E_TYPE='FULL_TIME';


--USE AND OPERATION
SELECT E_ID,E_NAME,AGE FROM EMPLOYEE WHERE AGE<30 AND E_TYPE='FULL_TIME';--USE AGGREGATE FUNCTION


--USE AGGREGATE FUNCTION
SELECT COUNT(E_ID),SUM(SALARY) AS TOTAL_SALARY_OF_EMPLOYEE FROM EMPLOYEE;


--USE SUBQUERY
SELECT E_ID,E_NAME FROM EMPLOYEE WHERE E_ID IN(SELECT E_ID FROM SCHEDULE GROUP BY E_ID);


--USE UNION OPERATION
SELECT P_ID,P_NAME,P_PRICE FROM PRODUCT WHERE P_COMPANY='DOVE' UNION SELECT P.P_ID,P.P_NAME,P.P_PRICE FROM PRODUCT P WHERE P.P_TYPE='FACE_WASH';


--JOIN OPERATION ON SALES AND PRODUCT TABLE
SELECT  A.P_ID,A.S_DATE_TIME,B.P_NAME 
FROM SALES A JOIN PRODUCT B 
ON A.P_ID=B.P_ID;


-- NATURAL JOIN OPERATION ON SALES AND PRODUCT TABLE
SELECT  P_ID,B.P_NAME,A.S_DATE_TIME,A.S_AMOUNT
FROM PRODUCT B NATURAL JOIN SALES A;


-- INNER JOIN OPERATION ON SALES AND PRODUCT TABLE
SELECT  B.P_ID,B.P_NAME,A.S_DATE_TIME,A.S_AMOUNT
FROM PRODUCT B INNER JOIN SALES A
ON B.P_ID=A.P_ID;


-- LEFT OUTER JOIN OPERATION ON SALES AND PRODUCT TABLE
SELECT  B.P_ID,B.P_NAME,A.S_DATE_TIME,A.S_AMOUNT
FROM PRODUCT B LEFT OUTER JOIN SALES A
ON B.P_ID=A.P_ID;


-- RIGHT OUTER JOIN OPERATION ON SALES AND PRODUCT TABLE
SELECT  B.P_ID,B.P_NAME,A.S_DATE_TIME,A.S_AMOUNT
FROM PRODUCT B RIGHT OUTER JOIN SALES A
ON B.P_ID=A.P_ID;


-- FULL OUTER JOIN OPERATION ON SALES AND PRODUCT TABLE
SELECT  B.P_ID,B.P_NAME,A.S_DATE_TIME,A.S_AMOUNT
FROM PRODUCT B FULL OUTER JOIN SALES A
ON B.P_ID=A.P_ID;


--PROCEDURE FOR CHECKING PRICE OF A PRODUCT
SET SERVEROUTPUT ON
DECLARE
    PRODUCT_ID  NUMBER;
    PRODUCT_PRICE NUMBER;
  
BEGIN
    PRODUCT_ID := &NUMBER1;
   
    SELECT P_PRICE INTO PRODUCT_PRICE FROM PRODUCT WHERE P_ID=PRODUCT_ID;
    DBMS_OUTPUT.PUT_LINE('PRICE : ' ||  PRODUCT_PRICE);
 END;
/


--FUNCTION FOR CALCULATING TOTAL PRICE OF PRODUCTS
SET SERVEROUTPUT ON
CREATE OR REPLACE FUNCTION P_TOTAL RETURN NUMBER IS
TOTAL_PRODUCT_PRICE PRODUCT.P_PRICE%TYPE;

BEGIN
	SELECT  SUM(P_PRICE) INTO TOTAL_PRODUCT_PRICE FROM PRODUCT;
    RETURN TOTAL_PRODUCT_PRICE;
 END;
/

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('TOTAL PRICE OF PRODUCTS:' || P_TOTAL);
END;
/

--PROCEDURE FOR INSERTING DATA INTO EMPLOYEE TABLE
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE ADD_EMPLOYEE(ID EMPLOYEE.E_ID%TYPE , NAME EMPLOYEE.E_NAME%TYPE , ADDRESS EMPLOYEE.E_ADDRESS%TYPE , AGE EMPLOYEE.AGE%TYPE , GENDER EMPLOYEE.GENDER%TYPE , JOINED EMPLOYEE.JOIN_DATE%TYPE , TYPE EMPLOYEE.E_TYPE%TYPE , SALARY EMPLOYEE.SALARY%TYPE) IS
BEGIN
	INSERT INTO EMPLOYEE(E_ID,E_NAME,E_ADDRESS,AGE,GENDER,JOIN_DATE,E_TYPE,SALARY) VALUES(ID,NAME,ADDRESS,AGE,GENDER,JOINED,TYPE,SALARY);
	COMMIT;
	DBMS_OUTPUT.PUT_LINE('SUCCESSFULLY INSERTED INTO EMPLOYEE TABLE.');
END;
/


--CALL ADD_EMPLOYEE PROCEDURE
SET SERVEROUTPUT ON
BEGIN	
	ADD_EMPLOYEE(10015, 'PITER', '59_G_STREET',25,'MALE','15-MAY-2016','FULL_TIME',10000.00);
END;
/

SELECT * FROM EMPLOYEE;


--USE CURSOR TO DISPLAY FIRST 5 EMPLOYEE FROM EMPLOYEE TABLE
SET SERVEROUTPUT ON

DECLARE
CURSOR CUR_EMP IS SELECT E_ID,E_NAME FROM EMPLOYEE;
EMP_RECORD CUR_EMP%ROWTYPE;

BEGIN
OPEN CUR_EMP;
LOOP
FETCH CUR_EMP INTO EMP_RECORD;
EXIT WHEN CUR_EMP%ROWCOUNT>5;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_RECORD:' || EMP_RECORD.E_ID || '	 ' || EMP_RECORD.E_NAME);
END LOOP;
CLOSE CUR_EMP;
END;
/