Employees Management Application

Apart from giving you an idea about how to create tables with constraints, it also enables you to understand how to create queries, pl/sql programs, stored procedures and functions and database triggers.

However, note, this sample collection of tables is only for learning purpose and they are hypothetical.

Required Tables

Structure of Tables

Creating Tables

Loading data into tables

Queries

Required Tables

Table Name	Meaning
DEPT	Stores the details of departments of the company.
EMPLOYEE	Stores information about all the employees of the company.
LEAVES	Stores information about types of leaves available
EMP_LEAVES	Stores information about leaves taken by the employees.

Structure of Tables

REM   ****** script to create tables realted to EMPLOYEES application   ******
REM   ****** 			AUTHOR : P.SRIKANTH 		        ******
REM   ******			DATE   : 23-AUG-2001			******
REM   ************************************************************************



drop table emp_leaves cascade constraints;
drop table employee cascade constraints;
drop table dept cascade constraints;
drop table leaves cascade constraints;



CREATE TABLE DEPT 
(
   DEPTNO NUMBER(2) CONSTRAINT DEPT_PK PRIMARY KEY,
   DEPTNAME  VARCHAR2(20) CONSTRAINT DEPT_DEPTNAME_U UNIQUE,
   HOD       VARCHAR2(20)
);

CREATE TABLE LEAVES
( 
    LEAVETYPE  CHAR(1)   CONSTRAINT LEAVES_PK  PRIMARY KEY,
    LEAVENAME  VARCHAR2(20),
    NOLEAVES   NUMBER(2) CONSTRAINT LEAVES_NOLEAVES_CHK   CHECK ( NOLEAVES <= 20)
);


CREATE TABLE EMPLOYEE
(
    EMPNO   NUMBER(5)   CONSTRAINT EMPLOYEE_PK PRIMARY KEY,
    EMPNAME VARCHAR2(20),
    SAL     NUMBER(5)   CONSTRAINT EMPLOYEE_SAL_CHK  CHECK ( SAL >= 1000),
    DEPTNO  NUMBER(2)   CONSTRAINT EMPLOYEE_DEPTNO_FK  REFERENCES DEPT(DEPTNO),
    DESG    VARCHAR2(20),
    DJ      DATE,
);


CREATE TABLE EMP_LEAVES
(
    EMPNO NUMBER(5)   CONSTRAINT EMP_LEAVES_EMPNO_FK   REFERENCES EMPLOYEE(EMPNO),
    LEAVETYPE CHAR(1) CONSTRAINT EMP_LEAVES_LEAVETYPE_FK    REFERENCES LEAVES(LEAVETYPE)
                      CONSTRAINT EMP_LEAVES_LEAVETYPE_NN  NOT NULL,
    STDATE DATE,
    ENDDATE DATE,
    CONSTRAINT EMP_LEAVES_PK  PRIMARY KEY (EMPNO,STDATE),
    CONSTRAINT EMP_LEAVES_DATES_CHK  CHECK (STDATE <= ENDDATE)
);

REM   ****** script to insert sample data into EMPLOYEES application    ******
REM   ****** 			AUTHOR : P.SRIKANTH 		        ******
REM   ******			DATE   : 23-AUG-2001			******
REM   ************************************************************************



rem  remove all existing rows first

DELETE FROM EMP_LEAVES;
DELETE FROM EMPLOYEE;
DELETE FROM DEPT;
DELETE FROM LEAVES;



INSERT INTO LEAVES VALUES('S','SICK',15);
INSERT INTO LEAVES VALUES('C','CASUAL',15);
INSERT INTO LEAVES VALUES('E','EARNING',5);
INSERT INTO LEAVES VALUES('O','OVERTIME',5);


INSERT INTO DEPT VALUES(1,'MAINFRAME','GEORGE');
INSERT INTO DEPT VALUES(2,'CLIENT/SERVER','BILL');
INSERT INTO DEPT VALUES(3,'SYSTEMS','GARRY');
INSERT INTO DEPT VALUES(4,'INTERNET','PAUL');
INSERT INTO DEPT VALUES(5,'ACCOUNTS','ANDY');


INSERT INTO EMPLOYEE VALUES(101,'GEORGE',12000,1,'12-JUL-2001','PM');
INSERT INTO EMPLOYEE VALUES(102,'BILL',12000,2,'14-JUL-2001','PM');
INSERT INTO EMPLOYEE VALUES(103,'GARRY',15000,3,'1-JUL-2001','PM');
INSERT INTO EMPLOYEE VALUES(104,'PAUL',11000,4,'2-JUL-2001','PL');
INSERT INTO EMPLOYEE VALUES(105,'ANDY',7000,5,'25-JUN-2001','AM');
INSERT INTO EMPLOYEE VALUES(106,'KEATS',10000,1,'17-JUL-2001','SA');
INSERT INTO EMPLOYEE VALUES(107,'JOEL',8000,2,'15-JUL-2001','SP');
INSERT INTO EMPLOYEE VALUES(108,'ROBERTS',7500,2,'15-JUL-2001','PRO');
INSERT INTO EMPLOYEE VALUES(109,'HERBERT',8000,4,'22-JUL-2001','SA');
INSERT INTO EMPLOYEE VALUES(110,'MICHEAL',6000,4,'15-JUL-2001','PRO');


INSERT INTO EMP_LEAVES VALUES(102,'S','23-JUL-2001','25-JUL-2001');
INSERT INTO EMP_LEAVES VALUES(104,'C','24-JUL-2001','25-JUL-2001');
INSERT INTO EMP_LEAVES VALUES(104,'S','28-JUL-2001','29-JUL-2001');
INSERT INTO EMP_LEAVES VALUES(101,'C','27-JUL-2001','28-JUL-2001');
INSERT INTO EMP_LEAVES VALUES(106,'O','28-JUL-2001','29-JUL-2001');
INSERT INTO EMP_LEAVES VALUES(109,'C','1-AUG-2001','2-AUG-2001');
INSERT INTO EMP_LEAVES VALUES(103,'C','2-AUG-2001','5-AUG-2001');
INSERT INTO EMP_LEAVES VALUES(105,'S','17-AUG-2001',NULL);
INSERT INTO EMP_LEAVES VALUES(108,'S','23-AUG-2001',NULL);



COMMIT;

Queries Related To Employees Management Application

 
SELECT * FROM EMPLOYEE
WHERE  SYSDATE - DJ <= 15;
 

 
SELECT * FROM EMPLOYEE
WHERE  TRUNC(SYSDATE) = TRUNC(DJ);

NOTE:
TRUNC FUNCTION IS REQUIRED IN ORDER TO IGNORE TIME DIFFERENCE BETWEEN TWO DATES.
 

 
SELECT * FROM EMPLOYEE
WHERE  DJ >= LAST_DAY(DJ) - 7;
  

 
SELECT EMPNAME, LAST_DAY(DJ) + 1 "FIRST SALARY DATE"
FROM EMPLOYEE
WHERE  EMPNO = 102;
 

 
DELETE FROM EMPLOYEE WHERE  TO_CHAR(SYSDATE,'MMYYYY') = TO_CHAR(DJ,'MMYYYY')
  

SELECT * FROM EMPLOYEE
WHERE SAL BETWEEN 12000 AND 14000; 
 

 
SELECT * FROM EMPLOYEE
WHERE  DEPTNO IN (1,3);
  

 
SELECT EMPNAME
FROM EMPLOYEE  WHERE EMPNAME LIKE 'M%';
  

 
DELETE FROM EMPLOYEE
WHERE EMPNAME LIKE '%A%';  

 
SELECT * FROM EMPLOYEE
WHERE  EMPNAME LIKE '_S%';  

 
SELECT * FROM EMPLOYEE
WHERE  SAL IS NULL;  

 
SELECT * FROM EMPLOYEE
WHERE  SYSDATE - DJ <= 20;  

 
SELECT * FROM EMP_LEAVES
WHERE  ENDDATE - STDATE > 10;  

 
SELECT EMPNO, EMPNAME, DJ, MONTHS_BETWEEN(SYSDATE,DJ) EXP, SAL
FROM EMPLOYEE;

 

 
SELECT * FROM EMPLOYEE
WHERE  SAL > 10000 AND  LENGTH(DESG) > 3;

 
SELECT * FROM EMPLOYEE
WHERE  INSTR(EMPNAME, ' ' , 1, 2) <> 0;

 
SELECT * FROM EMP_LEAVES
WHERE   STDATE BETWEEN   LAST_DAY( ADD_MONTHS(STDATE,-2)) + 1  AND 
                         LAST_DAY( ADD_MONTHS(STDATE,-1)) + 1
AND  ENDDATE IS NULL;  

 
SELECT * FROM EMPLOYEE
WHERE SAL > 10000 OR  DESG = 'PL'  AND  MONTHS_BETWEEN(SYSDATE,DJ) > 36; 
 

 
SELECT  SUBSTR(EMPNAME,1,INSTR(EMPNAME,' ') -1) FNAME, LAST_DAY(DJ) + 1
FROM EMPLOYEE;  

 
SELECT  AVG(SAL)
FROM EMPLOYEE
WHERE  TO_CHAR(DJ,'YYYY')  = TO_CHAR(SYSDATE,'YYYY'); 
 

 
SELECT  DEPTNO, AVG(SAL)
FROM EMPLOYEE
WHERE  SAL > 10000
GROUP BY  DEPTNO;  

 
SELECT  EMPNO, EMPNAME, DESG, DJ, SAL, SAL * DECODE(DEPTNO,1,1.0,0.75) BONUS
FROM EMPLOYEE; 
 

 
SELECT * FROM EMP_LEAVES
WHERE   LEAVETYPE = 'S' AND  TO_CHAR(STDATE,'fmDAY') = 'MONDAY';  

 
SELECT  EMPNO, SUM(ENDDATE-STDATE) "NO LEAVES"
FROM EMP_LEAVES
GROUP  BY  EMPNO;  

 
SELECT  DESG,SUM(SAL)
FROM EMPLOYEE
GROUP BY  DESG;  

 
SELECT  DEPTNO, DESG, SUM(SAL)
FROM  EMPLOYEE
GROUP BY  DEPTNO, DESG; 
 

 
SELECT DEPTNO, AVG(SAL)
FROM  EMPLOYEE
WHERE  SAL > 10000
GROUP BY DEPTNO;
 

 
SELECT  MAX(SAL) FROM EMPLOYEE;  

 
SELECT EMPNO,LEAVETYPE, SUM( ENDDATE -STDATE) "NO. LEAVES"
FROM  EMP_LEAVES
GROUP BY  EMPNO, LEAVETYPE; 
 

 
SELECT  DEPTNO, MIN(SAL), MAX(SAL), MAX(SAL) - MIN(SAL)
FROM   EMPLOYEE
GROUP BY  DEPTNO
HAVING  COUNT(*) > 2;  

 
SELECT  LEAVETYPE, EMPNO, COUNT(*)
FROM EMP_LEAVES
GROUP BY  LEAVETYPE,EMPNO;  

 
SELECT  EMPNO
FROM EMP_LEAVES
WHERE  TO_CHAR(STDATE,'MMYYYY')   = TO_CHAR(SYSDATE,'MMYYYY')
GROUP BY EMPNO
HAVING SUM(ENDDATE -STDATE) > 2;
 

 
SELECT  DESG
FROM EMPLOYEE
GROUP BY  DESG
HAVING  COUNT(*) > 5 OR  AVG(SAL) > 12000;
 

 
SELECT LEAVETYPE
FROM EMP_LEAVES
GROUP BY LEAVETYPE
HAVING  COUNT (DISTINCT EMPNO) > 3;  

 
SELECT  EMPNO, EMPNAME, DJ, DEPTNAME,SAL, HOD
FROM    EMPLOYEE E, DEPT D
WHERE   E.DEPTNO =  D.DEPTNO;  

 
SELECT EMPNO, STDATE, ENDDATE, LEAVENAME
FROM   EMP_LEAVES EL, LEAVES L
WHERE  EL.LEAVETYPE = L.LEAVETYPE AND ENDDATE IS NOT NULL;  

 
SELECT E.DEPTNO, DEPTNAME, EMPNAME, TRUNC(MONTHS_BETWEEN(SYSDATE,DJ)  / 12)
FROM   EMPLOYEE E, DEPT D
WHERE  E.DEPTNO = D.DEPTNO AND  DESG ='PRO'; 
 

 
SELECT EL.EMPNO, EMPNAME, DEPTNAME, LEAVENAME,STDATE, NOLEAVES
FROM   EMPLOYEE E, DEPT D, EMP_LEAVES EL, LEAVES L
WHERE  E.DEPTNO = D.DEPTNO AND EL.LEAVETYPE = L.LEAVETYPE
       AND  EL.EMPNO = E.EMPNO;  

 
SELECT EL.* 
FROM EMP_LEAVES EL, EMPLOYEE E,DEPT D
WHERE  E.EMPNO = EL.EMPNO 
       AND E.DEPTNO = D.DEPTNO AND  HOD = 'DUKE';  

SELECT  E1.*
FROM  EMPLOYEE E1, EMPLOYEE E2
WHERE E2.EMPNAME = 'WILLY' 
      AND  E1.DJ > E2.DJ;

SELECT * FROM EMPLOYEE
WHERE EMPNO IN
   ( SELECT EMPNO FROM EMP_LEAVES 
     WHERE TO_CHAR(SYSDATE,'MMYYYY') = TO_CHAR(STDATE,'MMYYYY')
   );

SELECT * FROM DEPT
WHERE DEPTNO IN 
 ( SELECT DEPTNO
   FROM EMPLOYEE
   GROUP BY DEPTNO
   HAVING  COUNT(*) > 2);

SELECT * FROM EMPLOYEE
WHERE EMPNO IN
   ( SELECT EMPNO
     FROM EMP_LEAVES 
     GROUP BY EMPNO
     HAVING SUM( ENDDATE - STDATE) > 10 
   );

SELECT * FROM DEPT
WHERE DEPTNO  IN
   ( SELECT DEPTNO
     FROM EMPLOYEE
     WHERE  TO_CHAR(DJ,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
     GROUP BY DEPTNO
     HAVING COUNT(*) > 3 
   );

SELECT EMPNAME FROM EMPLOYEE
WHERE SAL = 
   ( SELECT MAX(SAL)
     FROM   EMPLOYEE
   );

SELECT * FROM EMPLOYEE
WHERE EMPNO IN
   ( SELECT EMPNO 
     FROM   EMP_LEAVES
     WHERE  LEAVETYPE='S'
     GROUP  BY EMPNO
     HAVING  SUM(ENDDATE - STDATE ) > 10
   )
   OR EMPNO IN
   ( SELECT EMPNO 
     FROM   EMP_LEAVES
     GROUP  BY EMPNO
     HAVING  SUM(ENDDATE - STDATE ) > 15
   );
  

SELECT EMPNO,EMPNAME,DESG,DEPTNAME
FROM   EMPLOYEE E, DEPT D
WHERE  E.DEPTNO = D.DEPTNO
       AND  EMPNO NOT IN 
         ( SELECT EMPNO 
           FROM  EMP_LEAVES
           WHERE  TO_CHAR(STDATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
         );

 

SELECT * FROM EMPLOYEE
WHERE EMPNAME IN
   ( SELECT HOD
     FROM   DEPT
   );

  

SELECT * FROM DEPT
WHERE DEPTNO IN 
 (
  SELECT DEPTNO
  FROM   EMP_LEAVES EL, EMPLOYEE E
  WHERE  EL.EMPNO = E.EMPNO
  GROUP  BY DEPTNO
  HAVING SUM(ENDDATE-STDATE) =
                            ( 
                             SELECT MAX(SUM(ENDDATE-STDATE))
                             FROM   EMP_LEAVES EL, EMPLOYEE E
                             WHERE  EL.EMPNO = E.EMPNO
                             GROUP  BY DEPTNO
                            )
 );

SELECT EMPNO, SUM(ENDDATE-STDATE)
FROM EMP_LEAVES
WHERE EMPNO IN
(
 SELECT EMPNO FROM EMPLOYEE
 WHERE  DEPTNO IN
   (
        SELECT  DEPTNO
        FROM    DEPT
        WHERE   HOD = 'STEVE'
   )
)
GROUP BY EMPNO;

SELECT * FROM EMPLOYEE E
   WHERE  2 > ( SELECT COUNT(*)
                FROM   EMPLOYEE
                WHERE  SAL > E.SAL);

THIS IS DONE IN THREE STEPS.


CREATE TABLE NEWTABLE AS SELECT  A,B,C  FROM  OLDTABLE; 
DROP TABLE OLDTABLE 
RENAME NEWTABLE TO OLDTABLE 

SELECT * FROM DEPT
WHERE  DEPTNO IN 
    ( SELECT DEPTNO
      FROM   EMPLOYEE
      WHERE  EMPNO IN
                  (
                     SELECT EMPNO
                     FROM   EMP_LEAVES
                     GROUP  BY EMPNO
                     HAVING SUM(ENDDATE - STDATE) >
                     (   
                      SELECT  AVG(ENDDATE - STDATE)
                      FROM    EMP_LEAVES
                      WHERE   TO_CHAR(SYSDATE,'YYYY') = TO_CHAR(STDATE,'YYYY')
                     )
                  )
    );             

SELECT COUNT(*)
FROM   EMPLOYEE
WHERE  SAL > ( SELECT AVG(SAL)
               FROM  EMPLOYEE
               WHERE  DESG  = 'MANAGER');

SELECT * FROM EMPLOYEE
WHERE DEPTNO IN (1,3) AND  SAL > 5000;

 
SELECT * FROM EMP_LEAVES
WHERE EMPNO BETWEEN 103 AND 110;
 

 
SELECT * FROM EMPLOYEES
WHERE NAME LIKE '%X%'  OR  NAME LIKE '%Z%';

 
SELECT * FROM DEPT
WHERE HOD  = 'STEVE' AND DEPTNAME LIKE '%P';
 

 
SELECT * FROM USER_CONSTRAINTS
WHERE  TABLE_NAME = 'EMP_LEAVES';

 
SELECT EMPNO, EMPNAME, DJ, MONTHS_BETWEEN(SYSDATE,DJ) "NO MONTHS"
FROM  EMPLOYEE;
 

 
SELECT EMPNO, LEAVETYPE, STDATE, ENDDATE - STDATE
FROM EMP_LEAVES
WHERE  ENDDATE IS NULL;

 
SELECT  EMPNO, EMPNAME,  LAST_DAY(DJ) + 1 "FIRST SAL DATE"
FROM  EMPLOYEE;
 

 
SELECT * FROM EMPLOYEE
WHERE  EMPNAME LIKE '%TE%'  AND LENGTH(EMPNAME) > 5;

 
SELECT EMPNO, LEAVETYPE,STDATE, NEXT_DAY(STDATE,'Saturday'), ENDDATE - STDATE
FROM EMP_LEAVES
WHERE ENDATE IS NOT NULL;
 

 
SELECT * FROM EMPLOYEE
WHERE TO_CHAR(DJ,'YYYY') = TO_CHAR(SYSDATE,'YYYY');
 

 
SELECT * FROM EMPLOYEE
WHERE  INSTR(EMPNAME,'APP') = 4;
   OR
SELECT * FROM EMPLOYEE
WHERE  SUBSTR(EMPNAME,4,3) ='APP';

 
SELECT  EMPNO, EMPNAME,
        DECODE(DEPTNO, 1,'MONDAY',2,'THURSDAY','SUNDAY') HOLIDAY
FROM  EMPLOYEE;
 

 
SELECT EMPNO, LEAVETYPE, TO_CHAR(STDATE,'DD-MM'), ENDDATE 
FROM EMP_LEAVES
WHERE  EMPNO BETWEEN 103 AND 107 
        AND TO_CHAR(SYSDATE,'YYYY') = TO_CHAR(STDATE,'YYYY');
 

 
UPDATE EMP_LEAVES
   SET  STDATE = TRUNC(STDATE);
 

 
SELECT DEPTNO, SUM(SAL)
FROM  EMPLOYEE
GROUP BY DEPTNOL;

 
SELECT  DEPTNO, AVG(SAL)
FROM EMPLOYEE
WHERE TO_CHAR(SYSDATE,'YYYY') = TO_CHAR(DJ,'YYYY')
GROUP BY DEPTNO;
 

 
SELECT EMPNO, SUM(ENDDATE - STDATE)
FROM EMP_LEAVES
GROUP BY EMPNO;
 

 
SELECT LEAVETYPE, SUM(ENDDATE - STDATE)
FROM EMP_LEAVES
GROUP BY LEAVETYPE;
 

 
SELECT EMPNO
FROM EMP_LEAVES
GROUP BY  EMPNO
HAVING  SUM(ENDDATE  - STDATE) > 10;
 

 
SELECT  TO_CHAR(DJ,'YYYY')
FROM   EMPLOYEE
GROUP BY TO_CHAR(DJ,'YYYY')
HAVING COUNT(*) > 5;
 

 
SELECT EMPNO
FROM EMP_LEAVES
WHERE TO_CHAR(SYSDATE,'YYYY') = TO_CHAR(STDATE,'YYYY')
GROUP BY EMPNO
HAVING  SUM(ENDDATE - STDATE) > 20;
 

 
SELECT LEAVETYPE
FROM EMP_LEAVES
GROUP BY LEAVETYPE
HAVING  COUNT(*) > 10;
 

 
SELECT  DEPTNO, DESG, TO_CHAR(DJ,'YYYY'), COUNT(*)
FROM  EMPLOYEE
GROUP BY DEPTNO, DESG, TO_CHAR(DJ,'YYYY');
 

 
SELECT DISTINCT DEPT
FROM   EMPLOYE
GROUP  BY DEPT, DESG
HAVING AVG(SAL) > 1000;
 

 
SELECT DEPTNO, MAX(SAL) - MIN(SAL)
FROM   EMPLOYEE
GROUP BY  DEPTNO;
 

 
SELECT LEAVETYPE
FROM  EMP_LEAVES
WHERE TO_CHAR(STDATE,'MMYYYY') = TO_CHAR(SYSDATE,'MMYYYY')
GROUP BY LEAVETYPE
HAVING  SUM(ENDDATE - STDATE) > 10

UNION

SELECT LEAVETYPE
FROM  EMP_LEAVES
GROUP BY LEAVETYPE
HAVING  SUM(ENDDATE - STDATE) > 20
 

 
SELECT SUM( NVL(ENDDATE,SYSDATE) - STDATE)
FROM EMP_LEAVES;
 

 
SELECT EMPNO, EL.LEAVETYPE,STDATE, ENDDATE - STDATE, NOLEAVES
FROM   EMP_LEAVES EL, LEAVES L
WHERE  EL.LEAVETYPE = L.LEAVETYPE;
 

 
SELECT E.DEPTNO, DEPTNAME,EMPNAME,DESG
FROM  EMPLOYEE E, DEPT D
WHERE  DETPNAME LIKE 'A%'  AND  E.DEPTNO = D.DEPTNO;
 

 
SELECT EMPNO,EMPNAME, DEPTNAME,HOD 
FROM   EMPLOYEE E, DEPT D
WHERE  EMPNO NOT IN
       (SELECT EMPNO FROM EMP_LEAVES)
       AND E.DEPTNO = D.DEPTNO;
 

 
SELECT E.EMPNO,EMPNAME,DEPTNAME, LEAVENAME,STDATE,ENDDATE
FROM   EMPLOYEE E, DEPT D, EMP_LEAVES EL
WHERE  E.DEPTNO = D.DEPTNO AND  E.EMPNO = EL.EMPNO;
 

 
SELECT * FROM DEPT
WHERE  DEPTNO IN 
       ( SELECT DEPTNO FROM EMPLOYEE
         WHERE  TO_CHAR(SYSDATE,'MMYYYY') = TO_CHAR(DJ,'MMYYYY') );
 

 
SELECT L.LEAVETYPE,LEAVENAME, EMPNO , STDATE
FROM   EMP_LEAVES EL, LEAVES L
WHERE  L.LEAVETYPE = EL.LEAVETYPE (+);
 

 
SELECT E1.*
FROM  EMP_LEAVES E1, EMP_LEAVES E2
WHERE E2.STDATE= '05-MAR-2000' AND E2.EMPNO = 101
      AND  E1.ENDDATE - E1.STDATE >  E2.ENDDATE - E2.STDATE;
 

 
SELECT  * FROM DEPT
WHERE  DEPTNO IN
      ( SELECT  DEPTNO 
        FROM EMPLOYEE
        GROUP BY DEPTNO
        HAVING  AVG(SAL) > 10000);
 

 
SELECT * FROM DEPT
WHERE DEPTNO IN
      ( SELECT  DEPTNO 
        FROM EMPLOYEE
        WHERE SAL > 5000
        GROUP BY DEPTNO
        HAVING  COUNT(*) > 3);
 

 
SELECT * FROM EMPLOYEE
WHERE EMPNO NOT IN
      ( SELECT  EMPNO 
        FROM EMP_LEAVES
        WHERE LEAVETYPE = 'S'
             AND  TO_CHAR(STDATE,'MMYYYY') = TO_CHAR(SYSDATE,'MMYYYY')
      );
 

 
SELECT * FROM EMPLOYEE
WHERE  SAL = 
       (SELECT MAX(SAL) 
        FROM EMPLOYEE);
 

 
SELECT * FROM EMPLOYEE
WHERE  SAL > 
   ( SELECT  AVG(SAL)
     FROM  EMPLOYEE
     WHERE  TO_CHAR(SYSDATE,'YYYY') = TO_CHAR(DJ,'YYYY')
   );
 

 
SELECT * FROM DEPT
WHERE DEPTNO NOT IN 
  ( SELECT DEPTNO FROM EMPLOYEE
    WHERE  TO_CHAR(DJ,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
   );
 

 
DELETE FROM  EMP_LEAVES
WHERE  EMPNO  = ( SELECT MAX(EMPNO) FROM EMPLOYEE);
 

 
SELECT E.* 
FROM EMPLOYEE E, DEPT D
WHERE  E.DEPTNO = D.DEPTNO AND DEPTNAME = 'PRODUCTION'
       AND  EMPNO IN 
            ( SELECT EMPNO
              FROM EMP_LEAVES
              GROUP BY EMPNO
              HAVING SUM(ENDDATE-STDATE) > 20
             );
 

 
SELECT * FROM EMP_LEAVES
WHERE  EMPNO  IN
    ( SELECT EMPNO 
      FROM  EMPLOYEE
      WHERE  SAL > 10000 AND  MONTHS_BETWEEN(SYSDATE,DJ) <= 6
    );

This is done using correlated subquery. Subquery is used to return 
the number of employees whose salary is greater than the salary of the
current employee in main query. If that count is less than 5 that means
the employee is drawing one of the top five salraies.

select * from employee e
where  5 > 
     ( select count(distinct sal)
       from employee
       where  sal > e.sal);

UPDATE EMPLOYEE E
   SET  SAL= ( SELECT AVG(SAL) FROM EMPLOYEE WHERE DEPTNO = E.DEPTNO)
WHERE EMPNO = 102;  

SELECT * FROM DEPT
WHERE DEPTNO IN 
(
  SELECT DEPTNO 
  FROM   EMPLOYEE E,EMP_LEAVES EL
  WHERE  E.EMPNO=EL.EMPNO
  GROUP  BY DEPTNO
  HAVING SUM(ENDDATE-STDATE) = 
     ( SELECT MAX(SUM(ENDDATE - STDATE))
       FROM   EMPLOYEE  E, EMP_LEAVES EL
       WHERE  E.EMPNO = EL.EMPNO
       GROUP  BY DEPTNO
    )
 )      

RENAMING A COLUMN DONE IN THREE STEPS.

CREATE A NEW TABLE FROM EMPLOYEE TABLE. GIVE ALIAS JOINDATE TO DJ COLUMN IN QUERY.

CREATE TABLE TEMPLOYEE
      AS SELECT EMPNO,EMPNAME,SAL,DJ JOINDATE, DESG, DEPTNO
         FROM EMPLOYEE;


DROP EMPLOYEE TABLE.

DROP TABLE EMPLOYEE;

RENAME NEW TABLE TO EMPLOYEE TABLE.

RENAME TEMPLOYEE TO EMPLOYEE;    

DECLARE
   SAL_103 EMPLOYEE.SAL%TYPE;
BEGIN
   -- GET SALARY OF 103
   SELECT  SAL INTO SAL_103
   FROM EMPLOYEE
   WHERE EMPNO = 103;

   -- UPDATE SALARY OF 103 WITH SALARY OF 101
   UPDATE EMPLOYEE 
      SET SAL = (SELECT SAL FROM EMPLOYEE WHERE EMPNO = 101)
   WHERE EMPNO = 103;

   -- UPDATE SALARY OF 101 WITH SALARY OF 103
   UPDATE EMPLOYEE 
      SET SAL = SAL_103;
   WHERE EMPNO = 101;   

   COMMIT;
END;

-- PROCEDURE TO INSERT A NEW ROW INTO EMP_LEAVES TABLE
-- TAKES EMPLOYEE NUMBER AND LEAVETYPE
-- STDATE OF LEAVE IS SYSDATE AND ENDDATE IS NULL

CREATE OR REPLACE PROCEDURE NEWLEAVE(PEMPNO NUMBER, PLT CHAR)
IS
STATUS NUMBER(1):=0; -- INITALIZE VARIABLE  
CNT  NUMBER(3);
TNL  NUMBER(2);
NL   NUMBER(2);

BEGIN
  -- CHECK WHETHER EMPLOYEE NO. IS VALID
  SELECT 0 INTO CNT
  FROM  EMPLOYEE
  WHERE  EMPNO = PEMPNO;


  -- CHECK WHETHER LEAVETYPE IS VALID 
  -- IF LEAVETYPE IS VALID THEN GET MAX NO. OF LEAVES
  -- IN THAT LEAVETYPE

  STATUS := 1;  

  SELECT NOLEAVES INTO TNL
  FROM  LEAVES
  WHERE LEAVETYPE = PLT;

  -- CHECK WHETHER EMPLOYEE HAS ALREADY IN A LEAVE
  SELECT COUNT(*) INTO CNT
  FROM EMP_LEAVES
  WHERE  EMPNO = PEMPNO AND  ENDDATE IS NULL;

  IF  CNT <> 0 THEN  -- EMPLOYEE IS ALREADY ON LEAVE
    RAISE_APPLICATION_ERROR(-20120,'EMPLOYEE IS ALREADY ON LEAVE');
  END IF;
 
  
  -- CHECK WHETHER EMPLOYEE HAS CROSSED THE LIMIT
  -- CHECK NO. OF LEAVES OF THIS TYPE ALREADY CONSUMED
  SELECT SUM( ENDDATE-STDATE) INTO NL
  FROM EMP_LEAVES
  WHERE EMPNO = PEMPNO AND LEAVETYPE = PLT;

  IF  NL >= TNL THEN
    RAISE_APPLICATION_ERROR(-20130,'ALREADY CONSUMED TOTAL NUMBER OF LEAVES IN THIS LEAVETYPE');    
  END IF;

  -- VALID ENTRY, INSERT ROW

  INSERT INTO EMP_LEAVES 
    VALUES(PEMPNO,PLT,SYSDATE,NULL);
  COMMIT;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
     -- IF STATUS IS 0 THEN FIRST SELECT RAISE EXCEPTION
     IF STATUS = 0 THEN
       RAISE_APPLICATION_ERROR(-20110,'EMPLOYEE NUMBER IS NOT FOUND');
     ELSE   -- SECOND SELECT RAISE EXCEPTION
       RAISE_APPLICATION_ERROR(-20120,'LEAVETYPE IS NOT FOUND');
     END IF;
   WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(SQLCODE,SQLERRM);
END;

       

CREATE OR REPLACE FUNCTION  GETHSEMPNAMES  RETURN VARCHAR2
IS
  CURSOR HSEMP_CURSOR IS
   SELECT  EMPNAME 
   FROM EMPLOYEE
   WHERE  SAL  = (SELECT MAX(SAL) FROM EMPLOYEE);
   
 ENAME VARCHAR2(20);
 ALLNAMES VARCHAR2(200);

BEGIN
  
  ALLNAMES := '';
  FOR REC IN HSEMP_CURSOR  
   LOOP
      -- IF NOT FIRST NAME THEN ADD COMMA
      IF LENGTH(ALLNAMES) != 0 THEN
          ALLNAMES := ALLNAMES || ',';
      END IF;
     
      ALLNAMES := ALLNAMES || REC.EMPNAME;

   END LOOP;

  RETURN  ALLNAMES;
END;

SELECT * FROM DEPT
WHERE  HOD LIKE '%C%A%';
  

SELECT * FROM DEPT 
WHERE  DEPTNO > 10  AND DEPTNAME LIKE '%A';
  

SELECT * FROM EMPLOYEE
WHERE SAL > 10000 OR DESG = 'SA';
  

SELECT EMPNO, EMPNAME, ROUND(SAL,-2) "SAL" , DJ, MONTHS_BETWEEN(SYSDATE,DJ) "NO MONTHS"
FROM EMPLOYEE;
  

SELECT * FROM EMP_LEAVES 
   WHERE SYSDATE -STDATE <= 20;
  

 SELECT * FROM EMP_LEAVES
    WHERE ENDDATE - STDATE > 5;
  

 SELECT * FROM EMP_LEAVES WHERE EMPNO = 104 AND LEAVETYPE ='S';

DISPLAY EMPNO,LEAVETYPE,NO.OF DAYS OF LEAVE FOR LEAVES THAT WERE COMPLETED.
SELECT EMPNO, LEAVETYPE, ENDDATE - STDATE "NO DAYS"
   FROM EMP_LEAVES
   WHERE ENDDATE IS NOT NULL;
  

DISPLAY EMPLOYEE WHERE EMPNAME CONTAINS LETTERS 'M' AND 'J' IN ANY ORDER.

SELECT * FROM EMPLOYEE
WHERE EMPNAME LIKE '%M%' AND EMPNAME LIKE '%J%';
  

DISPLAY THE ROWS OF EMPLOYEE TABLE WHERE EMPLOYEE JOINED IN THE LAST 6 MONTHS AND SAL>5000 AND DESG. IS NOT PROGRAMMER.

SELECT * FROM EMPLOYEE
WHERE  SAL > 5000 AND  MONTHS_BETWEEN(SYSDATE,DJ) <= 6  AND DESG <> 'PRO';

  

DISPLAY DETAILS OF LEAVES THAT WERE TAKEN IN THE CURRENT MONTH.

SELECT * FROM EMP_LEAVES
WHERE  TO_CHAR(STDATE,'MMYY') = TO_CHAR(SYSDATE,'MMYY');
  

DISPLAY EMPNO,FIRST NAME,SALARY AND EXPERIENCE IN YEARS IN THE COMPANY.

SELECT  EMPNO, SUBSTR(EMPNAME, 1,DECODE(INSTR(EMPNAME,' '),0,LENGTH(EMPNAME),INSTR(EMPNAME,' '))) "FIRSTNAME", SAL,  MONTHS_BETWEEN(SYSDATE,DJ) / 12 "EXP.IN YEARS"
FROM EMPLOYEE;
  
DISPLAY THE DETAILS OF EMPLOYEE WHO JOINED IN THE MONTHS OF JULY IRRESPECTIVE OF THE YEAR.

SELECT * FROM EMPLOYEE
WHERE TO_CHAR(DJ,'MM') = 7;
  

DISPLAY DETAILS OF EMPLOYEE WHO HAVE MORE THAN 10 CHARS IN THE NAME OR HAVING LETTER 'G' AND 'C' IN THE NAME.


SELECT * FROM EMPLOYEE
WHERE LENGTH(EMPNAME) > 10 AND EMPNAME LIKE '%G%C';
  
CHANGE THE NAME OF EMPLOYEE 105 TO UPPERCASE AND REMOVE ALL LEADING AND TRAILING SPACES.

UPDATE  EMPLOYEE SET EMPNAME = UPPER(TRIM(EMPNAME)) WHERE EMPNO = 105;
  

DISPLAY EMPNO,LEAVETYPE,THE MONTH IN WHICH LEAVE STARTED AND THE MONTH IN WHICH LEAVE ENDED FOR LEAVES WHERE THESE TWO MONTHS ARE NOT SAME.

SELECT  EMPNO, LEAVETYPE, TO_CHAR(STDATE,'MONTH'), TO_CHAR(ENDDATE,'MONTH')
FROM EMP_LEAVES
WHERE TO_CHAR(STDATE,'MM') != TO_CHAR(ENDDATE,'MM');

  
DISPLAY LEAVES THAT ENDED IN PREVIOUS MONTH. 
SELECT * FROM EMP_LEAVES
WHERE  TO_CHAR(ENDDATE,'MMYY') =  TO_CHAR( ADD_MONTHS(SYSDATE,-1), 'MMYY');
  


DELETE DETAILS OF LEAVES WHERE THE LEAVE STARTED IN THE FIRST WEEK OF THE PREVIOUS MONTH.

SELECT * FROM EMP_LEAVES
WHERE  STDATE BETWEEN  LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 
		       AND LAST_DAY(ADD_MONTHS(SYSDATE,-2)) + 7;
  

DISPLAY EMPNAME IN UPPERCASE,DAY OF JOINING AND DATE OF FIRST SALARY AND WEEK DAY OF FIRST SALARY.

SELECT UPPER(EMPNAME), DJ, LAST_DAY(DJ) + 1, TO_CHAR( LAST_DAY(DJ) + 1, 'DAY')
FROM EMPLOYEE;
  

DISPLAY MONTHS IN WHICH EMPLOYEES JOINED IN THE CURRENT YEAR.

SELECT DISTINCT  TO_CHAR(DJ,'MONTH') "MONTH"
FROM   EMPLOYEE
WHERE TO_CHAR(DJ,'YYYY') = TO_CHAR(SYSDATE,'YYYY');
  


DISPLAY AVG.SALARY OF ALL THE EMPLOYEES.

SELECT AVG(SAL) 
FROM EMPLOYEE;
  

DISPLAY LEAVETYPE,NO.OF TIMES EMPLOYEES HAVE TAKEN THAT LEAVE.

SELECT  LEAVETYPE, SUM(ENDDATE-STDATE)
FROM   EMP_LEAVES
GROUP BY LEAVETYPE;

  
DISPLAY DEPTNO,AND NO.OF EMPLOYEES JOINED IN THE CURRENT YEAR.

SELECT DEPTNO, COUNT(*)
FROM  EMPLOYEE
WHERE TO_CHAR(DJ,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
GROUP BY DEPTNO;

  

DISPLAY MONTH NAME AND HOW MANY LEAVES STARTED IN THAT MONTH.

SELECT  TO_CHAR(STDATE,'MONTH'), COUNT(*)
FROM  EMP_LEAVES
GROUP BY TO_CHAR(STDATE,'MONTH');
  
DISPLAY THE EMPLOYEE WHO HAVE TAKEN MORE THAN 10 LEAVES SO FAR. 
 SELECT  EMPNO
 FROM EMP_LEAVES
 GROUP BY EMPNO
 HAVING SUM(ENDDATE-STDATE) > 10;
   
  

DISPLAY THE EMPLOYEE WHO HAS TAKEN MORE THAN 5 SICK LEAVES IN THE CURRENT YEAR.

SELECT EMPNO
FROM EMP_LEAVES
WHERE TO_CHAR(STDATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
GROUP BY  EMPNO
HAVING SUM(ENDDATE-STDATE) > 5;

  


DISPLAY DEPTNO,DESG AND AVG.SALARY.



SELECT  DEPTNO, DESG, AVG(SAL)
FROM   EMPLOYEE
GROUP BY DEPTNO, DESG;
  


DISPLAY YEAR,NO.OF EMPLOYEES JOINED WITH DESG PROGRAMMER.


SELECT TO_CHAR(DJ,'YYYY'), COUNT(*)
FROM EMPLOYEE
WHERE  DESG = 'PRO'
GROUP BY  TO_CHAR(DJ,'YYYY');

  

DISPLAY EMPNO,EMPNAME,DEPTNAME FOR EMPLOYEES WHO HAVE JOINED IN THE CURRENT MONTH.


SELECT EMPNO, EMPNAME, DEPTNAME
FROM EMPLOYEE E, DEPT D
WHERE TO_CHAR(DJ,'YYYY') = TO_CHAR(SYSDATE,'YYYY') AND E.DEPTNO = D.DEPTNO;
  

DISPLAY EMPNO,EMPNAME,DEPTNO,DEPTNAME,LEAVENAME,STDATE FOR ALL LEAVES THAT ARE NOT COMPLETED.

SELECT EL.EMPNO,EMPNAME, E.DEPTNO,DEPTNAME, LEAVENAME, STDATE
FROM  EMPLOYEE E, DEPT D, LEAVES L, EMP_LEAVES EL
WHERE  E.DEPTNO = D.DEPTNO AND E.EMPNO = EL.EMPNO AND L.LEAVETYPE= EL.LEAVETYPE;
  

DISPLAY EMPNAME AND TOTAL NO.OF LEAVES TAKEN(EMPNAME HAS TO BE UNIQUE)

SELECT  EMPNAME, SUM(ENDDATE-STDATE)
FROM   EMPLOYEE E, EMP_LEAVES EL
WHERE  E.EMPNO = EL.EMPNO
GROUP BY EMPNAME;

  


DISPLAY EMPNO,EMPNAME,LEAVETYPE,STDATE.INCLUDE EMPLOYEES WHO HAVE NOT TAKEN ANY LEAVE AND DISPLAY THE DATE IN THE ASCENDING ORDER OF EMPNO.

SELECT E.EMPNO, EMPNAME, LEAVETYPE, STDATE
FROM  EMPLOYEE E, EMP_LEAVES EL
WHERE  E.EMPNO = EL.EMPNO (+);
  

DISPLAY THE LEAVES THAT WERE TAKEN AFTER EMPNO 106 TOOK SICK LEAVE(ASSUMMING 106 HAS TAKEN 
ONLY ONE SICK LEAVE)

SELECT EL1.*
FROM   EMP_LEAVES EL1, EMP_LEAVES EL2
WHERE  EL2.EMPNO = 106 AND EL2.LEAVETYPE = 'S'
       AND  EL1.STDATE > EL2.STDATE;

  

DISPLAY DETAILS OF DEPT. IN WHICH WE HAVE AN EMPLOYEE WITH THE NAME CONTAINING 'KEVIN'.
SELECT * FROM DEPT
WHERE  DEPTNO IN ( SELECT DEPTNO 
                   FROM EMPLOYEE WHERE EMPNAME LIKE '%KEVIN%');

  

DISPLAY HIGHEST TOTAL NO.OF LEAVES TAKEN BY A SINGLE EMPLOYEE. 
SELECT  MAX(SUM(ENDDATE-STDATE))
FROM  EMP_LEAVES
GROUP BY EMPNO;
  


DISPLAY HIGHEST NO.OF DAYS IN SINGLE LEAVE.

SELECT  MAX(ENDDATE-STDATE)
FROM    EMP_LEAVES;
  

DISPLAY DETAILS OF DEPT WHERE DEPTNO HAS MORE THAN 5 EMPLOYEES.

SELECT * FROM DEPT
WHERE DEPTNO IN ( SELECT DEPTNO
                  FROM EMPLOYEE 
                  GROUP BY DEPTNO HAVING COUNT(*) > 5);
  

DISPLAY DETAILS OF EMPLOYEE WHO IS DRAWING THE MAX.SALARY.

SELECT  * FROM EMPLOYEE
WHERE SAL = ( SELECT MAX(SAL) FROM EMPLOYEE);

  


DISPLAY DETAILS OF LEAVES THAT WERE TAKEN BY EMPLOYEES OF DEPT 4.

SELECT * FROM EMP_LEAVES
WHERE  EMPNO IN ( SELECT EMPNO FROM EMPLOYEE WHERE DEPTNO = 4);
  


DISPLAY DEPTNO AND NO.OF EMPLOYEE WHO HAVE TAKEN LEAVE IN THE CURRENT MONTH.

SELECT DEPTNO, COUNT(*)
FROM  EMPLOYEE
WHERE  EMPNO IN ( SELECT EMPNO FROM EMP_LEAVES 
                  WHERE TO_CHAR(STDATE,'MMYY') = TO_CHAR(SYSDATE,'MMYY')
                )
GROUP BY DEPTNO;

  
DISPALY DETAILS OF EMPLOYEES WHO HAVE NOT TAKEN ANY LEAVE SO FAR. 
SELECT * FROM EMPLOYEE
WHERE EMPNO NOT IN ( SELECT EMPNO FROM EMP_LEAVES);
  


DISPLAY DETAILS OF EMPLOYEES WHO HAVE TAKEN A LEAVE IN THE PREVIOUS MONTH AND HAS 
NOT TAKEN ANY LEAVE IN ONE CURRENT MONTH.

SELECT * FROM EMPLOYEE
WHERE EMPNO IN ( SELECT EMPNO 
                 FROM EMP_LEAVES
                 WHERE TO_CHAR(STDATE,'MMYY') = TO_CHAR( ADD_MONTHS(SYSDATE,-1),'MMYY'))
 AND  EMPNO NOT IN 
               ( SELECT EMPNO 
                 FROM EMP_LEAVES
                 WHERE TO_CHAR(STDATE,'MMYY') = TO_CHAR(SYSDATE,'MMYY')
               );
 
  
DISPLAY DETAILS OF DEPT. IN WHICH ATLEAST 2 EMPLOYEE HAVE TAKEN MORE THAN 5 SICK LEAVES.
SELECT * FROM DEPT
   WHERE  DEPTNO IN ( SELECT DEPTNO 
                      FROM EMPLOYEE 
                      WHERE  EMPNO IN ( SELECT EMPNO
                                        FROM EMP_LEAVES WHERE LEAVETYPE='S'
                                        GROUP BY EMPNO HAVING SUM(ENDDATE-STDATE) > 5)
                    );

  

DISPLAY DETAILS OF DEPTS. IN WHICH ATLEAST ONE EMPLOYEE IS CURRENTLY ON LEAVE. 
SELECT * FROM DEPT
WHERE DEPTNO IN
         (SELECT DEPTNO FROM EMPLOYEE 
          WHERE EMPNO IN
            ( SELECT EMPNO 
              FROM   EMP_LEAVES
              WHERE  ENDDATE IS NULL));
  

DISPLAY DETAILS OF EMPLOYEES WHO ARE HEADED BY BILL OR WHO HAVE TAKEN A LEAVE ON PREVIOUS 'THURSDAY'

SELECT * FROM EMPLOYEE
WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE HOD = 'BILL')
  OR EMPNO IN( SELECT EMPNO 
               FROM EMP_LEAVES
               WHERE  TRUNC(STDATE) = NEXT_DAY(SYSDATE-15,'Thursday'));
  


DISPLAY THE DETAILS OF EMPLOYEES WITH FIRST 2 HIGHEST SALARY.


SELECT * FROM EMPLOYEE E
WHERE  2 > ( SELECT  COUNT(*)
             FROM EMPLOYEE WHERE SAL > E.SAL);

  

DISPLAY THE DETAILS OF LEAVETYPES WHERE THE LEAVE HAS BEEN TAKEN FOR MORE THAN 50 TIMES.

SELECT * FROM LEAVES
WHERE  LEAVETYPE IN ( SELECT  LEAVETYPE
                      FROM    EMP_LEAVES
                      GROUP   BY LEAVETYPE
                      HAVING  COUNT(*) > 50);

  

CHANGE THE DEPTNO OF EMPLOYEE 104 TO DEPTNO. OF 'INTERNET' DEPT.

UPDATE EMPLOYEE SET  DEPTNO = ( SELECT DEPTNO FROM DEPT WHERE DEPTNAME ='INTERNET')
WHERE  EMPNO = 104;

  


DROP COLUMN DESG. FROM EMPLOYEE TABLE.

STEP1 : CREATE TABLE NEWEMP AS SELECT EMPNO,EMPNAME,SAL,DEPTNO, DJ FROM EMPLOYEE;
STEP2 : DROP TABLE EMPLOYEE;
STEP3 : RENAME NEWEMP TO EMPLOYEE;