jntu lab work

lab1.sql

-------- JNTU LAB ANSWERS ----- PRUTHVI, SRIT -- ANANTAPUR -- 17-FEB-2015-------

CREATE TABLE DEPARTMENT
(
  DEPTNO NUMBER(2) PRIMARY KEY,
  DEPTNAME VARCHAR2(4) NOT NULL,
  DESCRIPTION VARCHAR(50)
);

CREATE TABLE EMPLOYEE
(
  EMPNO NUMBER(4) PRIMARY KEY,
  EMPNAME VARCHAR(20) NOT NULL,
  BASIC NUMBER(8,2),
  DEPTNO NUMBER(2),
  CONSTRAINT emp_dep_fk FOREIGN KEY (DEPTNO) REFERENCES DEPARTMENT(DEPTNO) ON DELETE CASCADE
);
--------- DONE CREATING TABLES AND EMPLOYEES WITH REQUIRED CONSTRAINTS --------

----START -- ADDING REMAINING COLUMNS ------------------------------------------
ALTER TABLE EMPLOYEE ADD (hra NUMBER(8,2), da NUMBER(8,2), DEDUCTIONS NUMBER(8,2), GROSS NUMBER(8,2), NET NUMBER(8,2), DATE_OF_BIRTH DATE);

---- DONE ADDING REMAINING COLUMS ----------------------------------------------

----START -- MAKE BASIC NOT NULL -----------------------------------------------

ALTER TABLE EMPLOYEE MODIFY (BASIC NUMBER(8,2) NOT NULL);

----DONE MAKING BASIC NOT NULL -------------------------------------------------

--SEE 1 for TABLE AND EMPLOYEE STRUCTURES---------------------------------------

---- START ADDING CONSTRAINT CHECK THAT BASIC SHOULDN'T BE LESS THAN 5000-------

ALTER TABLE EMPLOYEE MODIFY (BASIC NUMBER(8,2) CHECK(BASIC >= 5000));

---- DONE ALTERING CONSTRAINT THAT BASIC IS GREATHER THAN 5000------------------

---- CREATE HRA_DA TABLE TO STORE PERCENTAGES OF DA & HRA OF EMPS --------------
CREATE TABLE HRA_DA
(
  HRA NUMBER(5,2),
  DA NUMBER(5,2),
  EMPNO NUMBER(4) PRIMARY KEY,
  FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE(EMPNO)
);

---- DONE CREATEING DRA_HA TABLE------------------------------------------------

---- CALCULATING HRA AND DA USING PL/SQL ---------------------------------------
CREATE OR REPLACE PROCEDURE PROC_EMP_SAL 
IS
  HRA HRA_DA.HRA%TYPE;
  DA HRA_DA.DA%TYPE;
  EMP_HRA EMPLOYEE.HRA%TYPE;
  EMP_DA EMPLOYEE.DA%TYPE;
  GROSS EMPLOYEE.GROSS%TYPE;
  NET EMPLOYEE.NET%TYPE;
  QUE VARCHAR2(200);
BEGIN
  FOR X IN (SELECT EMPNO,BASIC,HRA,DA,DEDUCTIONS FROM EMPLOYEE)
  LOOP
    QUE := 'SELECT HRA FROM HRA_DA WHERE EMPNO=' || X.EMPNO;
    EXECUTE IMMEDIATE QUE INTO HRA;
    QUE := 'SELECT DA FROM HRA_DA WHERE EMPNO=' || X.EMPNO;
    EXECUTE IMMEDIATE QUE INTO DA;
    EMP_HRA := X.BASIC*HRA/100;
    EMP_DA := X.BASIC*DA/100;
    GROSS := X.BASIC + EMP_HRA + EMP_DA;
    NET := GROSS - X.DEDUCTIONS;
    QUE := 'UPDATE EMPLOYEE SET GROSS=' || GROSS
           || ',NET=' || NET
           || ',HRA=' || EMP_HRA
           || ',DA='  || EMP_DA
           || 'WHERE EMPNO=' || X.EMPNO;
    EXECUTE IMMEDIATE QUE;
  END LOOP;
END;
/
---- HRA, DA, GROSS, NET SUCCESSFULLY CALCULATED -------------------------------

---- CREATING TRIGGER ----------------------------------------------------------

CREATE OR REPLACE TRIGGER TRIG_EMP_SAL BEFORE INSERT OR UPDATE OF  BASIC ON EMPLOYEE
FOR EACH ROW
WHEN(NEW.basic < 5000)
BEGIN
  RAISE_APPLICATION_ERROR(-20001, 'Salary should not be less than 5000');
END;
/

---- DONE CREATING TRIGGER ------------------------------------------------------