Employee - Leaves - Department :: SQL Queries

-

SQL Queries for learning:

This application is used to keep track of information about employees of a company. It also stores the information about departments and leaves taken by employees. You are required to create tables (as shown below) and insert data into each of the table.

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.

Required Tables

The following are the set of tables to be created to store the required information.

Table NameMeaning
DEPTStores the details of departments of the company.
EMPLOYEEStores information about all the employees of the company.
LEAVESStores information about types of leaves available
EMP_LEAVESStores information about leaves taken by the employees.

Structure of Tables

The following is the structure of each of the required table.

DEPT Table

Stores information about all the departments of the company.
Column NameDatatypeMeaning
DEPTNOnumber(2)Department Number
DEPTNAMEvarchar2(20)Department Name
HODvarchar2(20)Head of the department

Constraints

  • DEPTNO is primary key
  • DEPTNAME must be unique

EMPLOYEE table

Contains information about all the employees of the company.
Column NameDatatypeMeaning
EMPNOnumber(5)Employee Number
EMPNAMEvarchar2(20)Employee Name
SALnumber(6)Basic Salary
DEPTNOnumber(2)Department to which employee belongs
DJdateDate of joining the company
DESGvarchar2(20)Designation of the employee

Constraints

  • EMPNO is primary key
  • SAL must be >= 1000
  • DEPTNO is foreign key referencing DEPTNO of DEPT table

LEAVES Table

Contains information about the types of leaves available in the company.
Column NameDatatypeMeaning
LEAVETYPEchar(1)Code for the type of leave
LEAVENAMEvarchar2(20)Description of the type of the leave
NOLEAVESnumber(2)Number of leaves allotted to each employee for a leave type

Contraints

  • LEAVETYPE is primary key
  • NOLEAVES must be <= 20

EMP_LEAVES Table

Contains information about the leaves taken by employees.
Column NameDatatypeMeaning
EMPNOnumber(5)Employee number of the employee who has taken leave
LEAVETYPEchar(1)Type of the leave taken by the employee
STDATEdateStarting date of the leave
ENDDATEdateEnding date of the leave

Contraints

  • EMPNO + STDATE is primary key
  • LEAVETYPE is not null
  • EMPNO is foreign key referencing EMPNO in EMPLOYEES table
  • LEAVETYPE is foreign key referencing LEAVETYPE in LEAVES table
  • STDATE must be <= ENDDATE

Creating Tables

The following scirpt is used to create sample tables. Run the script at 
SQL> promt using START command of 
SQL*PLUS>
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)
);

Loading data into tables

The following script inserts a few rows into sample tables. Create sample data or copy the script given below into notepad, save it with .SQL extension and run it at SQL> using START command.
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

DISPLAY EMPLOYEES WHO HAVE JOINED IN THE LAST 15 DAYS
 
SELECT * FROM EMPLOYEE
WHERE  SYSDATE - DJ <= 15;
DISPLAY EMPLOYEES WHO HAVE JOINED TODAY
 
SELECT * FROM EMPLOYEE
WHERE  TRUNC(SYSDATE) = TRUNC(DJ);

NOTE:
TRUNC FUNCTION IS REQUIRED IN ORDER TO IGNORE TIME DIFFERENCE BETWEEN TWO DATES.
DISPLAY WHO HAVE JOINED IN THE LAST WEEK OF THE MONTH
 
SELECT * FROM EMPLOYEE
WHERE  DJ >= LAST_DAY(DJ) - 7;
DISPLAY WHEN EMPLOYEE 102 HAS TAKEN HIS FIRST SALARY
 
SELECT EMPNAME, LAST_DAY(DJ) + 1 "FIRST SALARY DATE"
FROM EMPLOYEE
WHERE  EMPNO = 102;
DELETE EMPLOYEES WHO HAVE JOINED THE CURRENT MONTH
 
DELETE FROM EMPLOYEE WHERE  TO_CHAR(SYSDATE,'MMYYYY') = TO_CHAR(DJ,'MMYYYY')
DETAILS OF EMPLOYEES WHOSE SALARY RANGE IS BETWEEN 12,000 TO 14,000
SELECT * FROM EMPLOYEE
WHERE SAL BETWEEN 12000 AND 14000;
DETAILS OF EMPLOYEES WHO BELONG TO DEPARTMENT 1 OR 3
 
SELECT * FROM EMPLOYEE
WHERE  DEPTNO IN (1,3);
SELECT NAMES OF EMPLOYEES WHOSE NAMES START WITH 'M'
 
SELECT EMPNAME
FROM EMPLOYEE  WHERE EMPNAME LIKE 'M%';
DELETE THOSE EMPLOYEES WHERE NAME HAS THE CHARACTER 'A'
 
DELETE FROM EMPLOYEE
WHERE EMPNAME LIKE '%A%';
SELECT EMPLOYEES WHERE SECOND CHARACTER IN NAME IS 'S'
 
SELECT * FROM EMPLOYEE
WHERE  EMPNAME LIKE '_S%';
SELECT THOSE EMPLOYEES WHOSE SALARY IS NOT KNOWN
 
SELECT * FROM EMPLOYEE
WHERE  SAL IS NULL;
DISPLAY THE DETAILS OF EMPLOYEES WHO HAVE JOINED IN THE LAST 20 DAYS
 
SELECT * FROM EMPLOYEE
WHERE  SYSDATE - DJ <= 20;
DISPLAY THE DETAILS OF LEAVES IF THE NUMBER OF LEAVES IS MORE THAN 10
 
SELECT * FROM EMP_LEAVES
WHERE  ENDDATE - STDATE > 10;
DISPLAY EMPNO,EMPNAME,DATE OF JOINING,NUMBER OF MONTHS OF EXPERIENCE AND BASIC SALARY
 
SELECT EMPNO, EMPNAME, DJ, MONTHS_BETWEEN(SYSDATE,DJ) EXP, SAL
FROM EMPLOYEE;
DISPLAY DETAILS OF EMPLOYEES WHO ARE DRAWING MORE THAN 10000 AND THE DESIGNATION IS CONTAINING MORE THAN 3 LETTERS
 
SELECT * FROM EMPLOYEE
WHERE  SAL > 10000 AND  LENGTH(DESG) > 3;
DISPLAY DETAILS OF EMPLOYEES WHOSE NAME IS CONTAINING MORE THAN ONE SPACE
 
SELECT * FROM EMPLOYEE
WHERE  INSTR(EMPNAME, ' ' , 1, 2) <> 0;
DISPLAY DETAILS OF LEAVES WHERE THE LEAVE STARTED IN THE PREVIOUS MONTH AND THE LEAVE IS NOT YET COMPLETED
 
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;
DISPLAY DETAILS OF EMPLOYEES WHERE BASIC SALARY IS MORE THAN 10000 OR DESIGNATION IS PL AND EXPERIENCE IS MORE THAN 3 YEARS
 
SELECT * FROM EMPLOYEE
WHERE SAL > 10000 OR  DESG = 'PL'  AND  MONTHS_BETWEEN(SYSDATE,DJ) > 36;
DISPLAY EMPNO,NAME AND FIRST NAME OF THE EMPLOYEE AND WHEN EMPLOYEE HAS TAKEN HIS FIRST SALARY
 
SELECT  SUBSTR(EMPNAME,1,INSTR(EMPNAME,' ') -1) FNAME, LAST_DAY(DJ) + 1
FROM EMPLOYEE;
FIND THE AVERAGE SALARY OF THE EMPLOYEE WHO JOINED IN THE CURRENT YEAR
 
SELECT  AVG(SAL)
FROM EMPLOYEE
WHERE  TO_CHAR(DJ,'YYYY')  = TO_CHAR(SYSDATE,'YYYY');
FIND THE AVERAGE SALARY OF EACH DEPARTMENT BY TAKING EMPLOYEES WHO EARN MORE THAN 10000
 
SELECT  DEPTNO, AVG(SAL)
FROM EMPLOYEE
WHERE  SAL > 10000
GROUP BY  DEPTNO;
DISPLAY DETAILS OF EMPLOYEES ALONG WITH BONUS WHICH WILL BE 100% ON SALARY FOR EMPLOYEES OF DEPARTMENT 1 AND 75% FOR OTHERS
 
SELECT  EMPNO, EMPNAME, DESG, DJ, SAL, SAL * DECODE(DEPTNO,1,1.0,0.75) BONUS
FROM EMPLOYEE;
DISPLAY DETAILS OF LEAVES TAKEN BY EMPLOYEES WHERE TYPE OF LEAVE IS 'S' AND LEAVE STARTED ON MONDAY
 
SELECT * FROM EMP_LEAVES
WHERE   LEAVETYPE = 'S' AND  TO_CHAR(STDATE,'fmDAY') = 'MONDAY';
DISPLAY EMPNO AND NO. OF LEAVES TAKEN BY EMPLOYEE
 
SELECT  EMPNO, SUM(ENDDATE-STDATE) "NO LEAVES"
FROM EMP_LEAVES
GROUP  BY  EMPNO;
DISPLAY DESIGNATION AND TOTAL SALARY OF THE EMPLOYEES OF DESIGNATION
 
SELECT  DESG,SUM(SAL)
FROM EMPLOYEE
GROUP BY  DESG;
FIND THE SUM OF SALARIES IN EACH DESIGNATION IN EACH DEPARTMENT
 
SELECT  DEPTNO, DESG, SUM(SAL)
FROM  EMPLOYEE
GROUP BY  DEPTNO, DESG;
FIND THE AVERAGE SALARY OF EACH DEPARTMENT AND SELECT ONLY THOSE EMPLOYEES HAVING SALARY MORE THAN 10000
 
SELECT DEPTNO, AVG(SAL)
FROM  EMPLOYEE
WHERE  SAL > 10000
GROUP BY DEPTNO;
DISPLAY MAXIMUM SALARY
 
SELECT  MAX(SAL) FROM EMPLOYEE;
DISPLAY EMPNO ,TYPE OF LEAVE,TOTAL NO OF LEAVES TAKEN
 
SELECT EMPNO,LEAVETYPE, SUM( ENDDATE -STDATE) "NO. LEAVES"
FROM  EMP_LEAVES
GROUP BY  EMPNO, LEAVETYPE;
DISPLAY DEPTNO,MIN SALARY,MAX SALARY ,DIFFERENCE BETWEEN MAX AND MIN SALARY FOR THE DEPARTMENTS THAT HAVE MORE THAN 2 EMPLOYEES
 
SELECT  DEPTNO, MIN(SAL), MAX(SAL), MAX(SAL) - MIN(SAL)
FROM   EMPLOYEE
GROUP BY  DEPTNO
HAVING  COUNT(*) > 2;
DISPLAY LEAVETYPE AND HOW MANY TIMES EACH EMPLOYEE HAS TAKEN LEAVE
 
SELECT  LEAVETYPE, EMPNO, COUNT(*)
FROM EMP_LEAVES
GROUP BY  LEAVETYPE,EMPNO;
DISPLAY EMPNO OF THE EMPLOYEE WHO HAS TAKEN MORE THAN 2 LEAVES IN THE CURRENT MONTH
 
SELECT  EMPNO
FROM EMP_LEAVES
WHERE  TO_CHAR(STDATE,'MMYYYY')   = TO_CHAR(SYSDATE,'MMYYYY')
GROUP BY EMPNO
HAVING SUM(ENDDATE -STDATE) > 2;
DISPLAY DESIGNATION THAT CONTAIN EITHER MORE THAN 5 EMPLOYEES OR AVERAGE SALARY MORE THAN 12000
 
SELECT  DESG
FROM EMPLOYEE
GROUP BY  DESG
HAVING  COUNT(*) > 5 OR  AVG(SAL) > 12000;
DISPLAY THE TYPE OF LEAVE THAT IS TAKEN BY MORE THAN 3 EMPLOYEES
 
SELECT LEAVETYPE
FROM EMP_LEAVES
GROUP BY LEAVETYPE
HAVING  COUNT (DISTINCT EMPNO) > 3;
DISPLAY EMPNO,EMPNAME,DATE OF JOINING,DEPTNAME, SALARY AND HOD
 
SELECT  EMPNO, EMPNAME, DJ, DEPTNAME,SAL, HOD
FROM    EMPLOYEE E, DEPT D
WHERE   E.DEPTNO =  D.DEPTNO;
DISPLAY EMPNO,STDATE,ENDDATE,LEAVENAME FOR ALL THE COMPLETED LEAVES
 
SELECT EMPNO, STDATE, ENDDATE, LEAVENAME
FROM   EMP_LEAVES EL, LEAVES L
WHERE  EL.LEAVETYPE = L.LEAVETYPE AND ENDDATE IS NOT NULL;
DISPLAY DEPTNO,DEPTNAME,EMPNAME,YEARS OF EXPERIENCE FOR ALL THE EMPLOYEES WITH DESIG 'PRO'
 
SELECT E.DEPTNO, DEPTNAME, EMPNAME, TRUNC(MONTHS_BETWEEN(SYSDATE,DJ)  / 12)
FROM   EMPLOYEE E, DEPT D
WHERE  E.DEPTNO = D.DEPTNO AND  DESG ='PRO';
DISPLAY EMPNO,EMPNAME,DEPTNAME,LEAVENAME,STDATE AND MAX NO. OF LEAVES IN THE CATEGORY
 
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;
DISPLAY THE DETAILS OF LEAVES TAKEN BY EMPLOYEES WHO ARE HAVING 'DUKE' AS THE HEAD OF THE DEPARTMENT.
 
SELECT EL.* 
FROM EMP_LEAVES EL, EMPLOYEE E,DEPT D
WHERE  E.EMPNO = EL.EMPNO 
       AND E.DEPTNO = D.DEPTNO AND  HOD = 'DUKE';
DISPLAY THE DETAILS OF EMPLOYEES WHO HAVE JOINED AFTER EMPLOYEE 'WILLY' HAS JOINED.
SELECT  E1.*
FROM  EMPLOYEE E1, EMPLOYEE E2
WHERE E2.EMPNAME = 'WILLY' 
      AND  E1.DJ > E2.DJ;
SELECT THE EMPLOYEES WHO HAVE TAKEN LEAVE IN THE PRESENT MONTH
SELECT * FROM EMPLOYEE
WHERE EMPNO IN
   ( SELECT EMPNO FROM EMP_LEAVES 
     WHERE TO_CHAR(SYSDATE,'MMYYYY') = TO_CHAR(STDATE,'MMYYYY')
   );
DISPLAY THE DETAILS OF DEPARTMENTS WHICH HAVE MORE THAN 2 EMPLOYEES
SELECT * FROM DEPT
WHERE DEPTNO IN 
 ( SELECT DEPTNO
   FROM EMPLOYEE
   GROUP BY DEPTNO
   HAVING  COUNT(*) > 2);
DISLAY THE DETAILS OF EMPLOYEES WHO HAVE TAKEN MORE THAN 10 LEAVES
SELECT * FROM EMPLOYEE
WHERE EMPNO IN
   ( SELECT EMPNO
     FROM EMP_LEAVES 
     GROUP BY EMPNO
     HAVING SUM( ENDDATE - STDATE) > 10 
   );
DISPLAY THE DETAILS OF DEPARTMENTS WHICH HAVE MORE THAN 3 EMPLOYEES JOINED IN THE CURRENT YEAR
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 
   );
DISPLAY THE NAME OF THE EMPLOYEE DRAWING THE MAX SALARY
SELECT EMPNAME FROM EMPLOYEE
WHERE SAL = 
   ( SELECT MAX(SAL)
     FROM   EMPLOYEE
   );
DISPLAY THE DETAILS OF EMPLOYEES WHO HAS TAKEN MORE THAN 10 SICKLEAVES OR MORE THAN 15 LEAVES
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
   );
DISPLAY EMPNO,EMPNAME,DESIGNATION AND DEPTNAME OF EMPLOYEES WHO HAVE NOT TAKEN ANY LEAVES IN THE CURRENT YEAR
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')
         );
DISPLAY THE DETAILS OF HOD'S
SELECT * FROM EMPLOYEE
WHERE EMPNAME IN
   ( SELECT HOD
     FROM   DEPT
   );
DISPLAY THE DEPARTMENTS IN WHICH EMPLOYEES HAVE TAKEN MAX NO OF LEAVES
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
                            )
 );
DISPLAY EMPNO,NOOFLEAVES FOR ALL EMPLOYEES WHO ARE HEADED BY 'STEVE'
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;
DISPLAY DETAILS OF EMPLOYEES DRAWING TOP 2 HIGHEST SALARIES
SELECT * FROM EMPLOYEE E
   WHERE  2 > ( SELECT COUNT(*)
                FROM   EMPLOYEE
                WHERE  SAL > E.SAL);
DROP AN UNWANTED COLUMN FROM ANY TABLE
THIS IS DONE IN THREE STEPS.


    

  1. CREATE TABLE NEWTABLE AS SELECT  A,B,C  FROM  OLDTABLE; 
    2. 

  2. DROP TABLE OLDTABLE 
    3. 

  3. RENAME NEWTABLE TO OLDTABLE 
    4.
DISPLAY DETAILS OF DEPARTMENT IN WHICH ATLEAST ONE EMPLOYEE HAS TAKEN MORE NO. OF LEAVES THAN AVERAGE LEAVES OF ALL THE EMPLOYEES WHO JOINED IN THE CURRENT YEAR
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')
                     )
                  )
    );
HOW MANY EMPLOYEES ARE EARNING MORE THAN THE AVERAGE SALARY OF MANAGERS
SELECT COUNT(*)
FROM   EMPLOYEE
WHERE  SAL > ( SELECT AVG(SAL)
               FROM  EMPLOYEE
               WHERE  DESG  = 'MANAGER');
DISPLAY THE DETAILS OF EMPLOYEES WHO BELONG TO DEPARTMENT 1 OR 3 AND DRAW MORE THAN 5000 SALARY
SELECT * FROM EMPLOYEE
WHERE DEPTNO IN (1,3) AND  SAL > 5000;
DISPLAY THE DETAILS OF LEAVES WHERE THE EMPNO IS IN THE RANGE 103 TO 110
 
SELECT * FROM EMP_LEAVES
WHERE EMPNO BETWEEN 103 AND 110;
DISPLAY DETAILS OF EMPLOYEES WHERE THE NAME CONTAINS LETTER X OR Z.
 
SELECT * FROM EMPLOYEES
WHERE NAME LIKE '%X%'  OR  NAME LIKE '%Z%';
DISPLAY DETAILS OF DEPARTMENT WHERE HEAD OF DEPARTMENT IS 'STEVE' AND THE DEPTNAME CONTAINS 'P' AS THE LAST CHARACTER.
 
SELECT * FROM DEPT
WHERE HOD  = 'STEVE' AND DEPTNAME LIKE '%P';
DISPLAY CONSTRAINTS OF EMP_LEAVES TABLE
 
SELECT * FROM USER_CONSTRAINTS
WHERE  TABLE_NAME = 'EMP_LEAVES';
DISPLAY EMPNO,EMPNAME,DATE OF JOINING & EXPEREINCE IN MONTHS
 
SELECT EMPNO, EMPNAME, DJ, MONTHS_BETWEEN(SYSDATE,DJ) "NO MONTHS"
FROM  EMPLOYEE;
DISPLAY EMPNO,LEAVETYPE,STDATE,NO OF DAYS BETWEEN SYSDATE & STDATE FOR LEAVES THAT ARE NOT COMPLETED.
 
SELECT EMPNO, LEAVETYPE, STDATE, ENDDATE - STDATE
FROM EMP_LEAVES
WHERE  ENDDATE IS NULL;
DISPLAY EMPNO,EMPNAME,DATE ON WHICH EMPLOYEE TOOK FIRST SALARY (ASSUMING ON 1ST OF EACH MONTH SALARY IS PAID).
 
SELECT  EMPNO, EMPNAME,  LAST_DAY(DJ) + 1 "FIRST SAL DATE"
FROM  EMPLOYEE;
DISPLAY THE DETIALS OF EMPLOYEES WHO HAVE THE PATTERN 'TE' IN NAME AND NAME HAS MORE THAN 5 LETTERS.
 
SELECT * FROM EMPLOYEE
WHERE  EMPNAME LIKE '%TE%'  AND LENGTH(EMPNAME) > 5;
DISPLAY THE DETAILS OF LEAVES ALONG WITH THE DATE OF COMING SATURDAY AFTER STDATE AND NO. OF DAYS OF LEAVES FOR LEAVES THAT ARE COMPLETED.
 
SELECT EMPNO, LEAVETYPE,STDATE, NEXT_DAY(STDATE,'Saturday'), ENDDATE - STDATE
FROM EMP_LEAVES
WHERE ENDATE IS NOT NULL;
DISPLAY THE DETAILS OF EMPLOYEES WHO HAVE JOINED IN THE CURRENT YEAR
 
SELECT * FROM EMPLOYEE
WHERE TO_CHAR(DJ,'YYYY') = TO_CHAR(SYSDATE,'YYYY');
DISPLAY THE DETAILS OF EMPLOYEES WHOSE NAME CONTAINS 'APP' IN 4TH,5TH,6TH POSITIONS.
 
SELECT * FROM EMPLOYEE
WHERE  INSTR(EMPNAME,'APP') = 4;
   OR
SELECT * FROM EMPLOYEE
WHERE  SUBSTR(EMPNAME,4,3) ='APP';
DISPLAY EMPNO,NAME,HOLIDAY WEEK,WHICH DEPENDS ON THE DEPT AS FOLLOWS: DEPT1:MONDAY DEPT2:THURSDAY OTHERS:SUNDAY
 
SELECT  EMPNO, EMPNAME,
        DECODE(DEPTNO, 1,'MONDAY',2,'THURSDAY','SUNDAY') HOLIDAY
FROM  EMPLOYEE;
DISPLAY THE EMPNO,LEAVETYPE,STDATE IN 'DD-MM' FORMAT AND ENDING DATE FOR ALL THE LEAVES THAT ARE TAKEN BY EMPLOYES WITH NUMBERS IN THE RANGE 103-107 AND IN THE CURRENT YEAR.
 
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');
TRUNCATE TIME PORTION IN STARTING DATE OF THE LEAVE.
 
UPDATE EMP_LEAVES
   SET  STDATE = TRUNC(STDATE);
DISPLAY THE SUM OF SALARY OF EACH DEPT
 
SELECT DEPTNO, SUM(SAL)
FROM  EMPLOYEE
GROUP BY DEPTNOL;
DISPLAY THE AVERAGE SALARY OF EACH DEPT BY TAKING EMPLOYEES WHO HAVE JOINED IN THE CURRENT YEAR.
 
SELECT  DEPTNO, AVG(SAL)
FROM EMPLOYEE
WHERE TO_CHAR(SYSDATE,'YYYY') = TO_CHAR(DJ,'YYYY')
GROUP BY DEPTNO;
DISPLAY EMPNO,TOTAL NO.OF LEAVES TAKEN BY EMPLOYEE
 
SELECT EMPNO, SUM(ENDDATE - STDATE)
FROM EMP_LEAVES
GROUP BY EMPNO;
DISPLAY THE TOTAL NO. OF LEAVES TAKEN FOR EACH LEAVETYPE
 
SELECT LEAVETYPE, SUM(ENDDATE - STDATE)
FROM EMP_LEAVES
GROUP BY LEAVETYPE;
DISPLAY EMPNO WHERE EMPLOYEE HAS TAKEN MORE THAN 10 LEAVES.
 
SELECT EMPNO
FROM EMP_LEAVES
GROUP BY  EMPNO
HAVING  SUM(ENDDATE  - STDATE) > 10;
DISPLAY THE YEAR IN WHICH MORE THAN 5 EMPLOYEES HAVE JOINED
 
SELECT  TO_CHAR(DJ,'YYYY')
FROM   EMPLOYEE
GROUP BY TO_CHAR(DJ,'YYYY')
HAVING COUNT(*) > 5;
DISPLAY EMPNO FOR EMPLOYEES WHO HAVE TAKEN MORE THAN 20 LEAVES IN THE CURRENT YEAR.
 
SELECT EMPNO
FROM EMP_LEAVES
WHERE TO_CHAR(SYSDATE,'YYYY') = TO_CHAR(STDATE,'YYYY')
GROUP BY EMPNO
HAVING  SUM(ENDDATE - STDATE) > 20;
DISPLAY THE LEAVETYPE THAT HAS BEEN TAKEN FOR MORE THAN 10 TIMES
 
SELECT LEAVETYPE
FROM EMP_LEAVES
GROUP BY LEAVETYPE
HAVING  COUNT(*) > 10;
DISPLAY DEPT,DESIGNATION,YEAR & NO. OF EMPLOYEES JOINED IN THAT YEAR IN THAT DEPARTMENT AND DESIGNATION.
 
SELECT  DEPTNO, DESG, TO_CHAR(DJ,'YYYY'), COUNT(*)
FROM  EMPLOYEE
GROUP BY DEPTNO, DESG, TO_CHAR(DJ,'YYYY');
DISPLAY DEPT IN WHICH THE AVGERAGE SAL OF ANY SINGLE DESIGNATION IS MORE THAN 10000.
 
SELECT DISTINCT DEPT
FROM   EMPLOYE
GROUP  BY DEPT, DESG
HAVING AVG(SAL) > 1000;
DISPLAY DEPTNO,DIFFERENCE BETWEEN MIN & MAX OF SALARY OF THE DEPT.
 
SELECT DEPTNO, MAX(SAL) - MIN(SAL)
FROM   EMPLOYEE
GROUP BY  DEPTNO;
DISPLAY LEAVETYPE FOR WHICH MORE THAN 10 LEAVES ARE TAKEN IN THE CURRENT MONTH OR 20 LEAVES TAKEN SO FAR.
 
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
DISPLAY TOTAL NO.OF LEAVES OF ALL EMPLOYES (CONSIDERING SYSDATE AS ENDING DATE IF ENDING DATE IS NOT AVAILABLE).
 
SELECT SUM( NVL(ENDDATE,SYSDATE) - STDATE)
FROM EMP_LEAVES;
DISPLAY EMPNO,LEAVETYPE ,STDATE,NO. OF LEAVES & MAX NO. OF LEAVES FOR THAT CATEGORY.
 
SELECT EMPNO, EL.LEAVETYPE,STDATE, ENDDATE - STDATE, NOLEAVES
FROM   EMP_LEAVES EL, LEAVES L
WHERE  EL.LEAVETYPE = L.LEAVETYPE;
DISPLAY DEPTNO,DEPTNAME,EMPNAME,DESIGNATION FOR THAT DEPT WHERE STARTING LETTER IS 'A'.
 
SELECT E.DEPTNO, DEPTNAME,EMPNAME,DESG
FROM  EMPLOYEE E, DEPT D
WHERE  DETPNAME LIKE 'A%'  AND  E.DEPTNO = D.DEPTNO;
DISPLAY EMPNO,NAME,DEPTNAME,HOD FOR THE EMPLOYEES WHO HAVE NOT TAKEN ANY LEAVE SO FAR.
 
SELECT EMPNO,EMPNAME, DEPTNAME,HOD 
FROM   EMPLOYEE E, DEPT D
WHERE  EMPNO NOT IN
       (SELECT EMPNO FROM EMP_LEAVES)
       AND E.DEPTNO = D.DEPTNO;
DISPLAY EMPNO,NAME,DEPTNAME,LEAVENAME,STDATE,ENDDATE
 
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;
DISPLAY DETAILS OF DEPT IN WHICH AT LEAST ONE EMPLOYEE HAS JOINED IN THE CURRENT MONTH.
 
SELECT * FROM DEPT
WHERE  DEPTNO IN 
       ( SELECT DEPTNO FROM EMPLOYEE
         WHERE  TO_CHAR(SYSDATE,'MMYYYY') = TO_CHAR(DJ,'MMYYYY') );
DISPLAY LEAVETYPE,LEAVENAME,EMPNO AND STDATE FOR ALL THE LEAVES INCLUDING LEAVETYPES THAT HAVE NOT BEEN USED BY ANY EMPLOYEE.
 
SELECT L.LEAVETYPE,LEAVENAME, EMPNO , STDATE
FROM   EMP_LEAVES EL, LEAVES L
WHERE  L.LEAVETYPE = EL.LEAVETYPE (+);
DISPLAY THE DETAILS OF LEAVES WHERE THE NO.OF DAYS OF LEAVES IS MORE THAN THE NOOFDAYS OF LEAVE TAKEN BY 101 IN LEAVE THAT STARTED ON 5-MARCH-00.
 
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;
DISPLAY DETAILS OF DEPT IN WHICH THE AVGERAGE SALARY IS > 10000.
 
SELECT  * FROM DEPT
WHERE  DEPTNO IN
      ( SELECT  DEPTNO 
        FROM EMPLOYEE
        GROUP BY DEPTNO
        HAVING  AVG(SAL) > 10000);
DISPLAY DETAILS OF DEPT WHERE THE DEPT HAS MORE THAN 3 EMPLOYEES DRAWING MORE THAN 5000
 
SELECT * FROM DEPT
WHERE DEPTNO IN
      ( SELECT  DEPTNO 
        FROM EMPLOYEE
        WHERE SAL > 5000
        GROUP BY DEPTNO
        HAVING  COUNT(*) > 3);
DISPLAY THE DETAILS OF EMPLOYEES WHO HAS NOT TAKEN SICK LEAVE IN CURRENT MONTH.
 
SELECT * FROM EMPLOYEE
WHERE EMPNO NOT IN
      ( SELECT  EMPNO 
        FROM EMP_LEAVES
        WHERE LEAVETYPE = 'S'
             AND  TO_CHAR(STDATE,'MMYYYY') = TO_CHAR(SYSDATE,'MMYYYY')
      );
DISPLAY DETAILS OF EMPLOYEES DRAWING THE MAXSAL.
 
SELECT * FROM EMPLOYEE
WHERE  SAL = 
       (SELECT MAX(SAL) 
        FROM EMPLOYEE);
DISPLAY DETAILS OF EMPLOYEES DRAWING MORE SALARY THAN THE AVERAGE SAL OF EMPLOYEES JOINED IN THE CURRENT YEAR.
 
SELECT * FROM EMPLOYEE
WHERE  SAL > 
   ( SELECT  AVG(SAL)
     FROM  EMPLOYEE
     WHERE  TO_CHAR(SYSDATE,'YYYY') = TO_CHAR(DJ,'YYYY')
   );
DISPLAY DETAILS OF DEPT'S IN WHICH NO EMPLOYEE JOINED IN THE CURRENT YEAR.
 
SELECT * FROM DEPT
WHERE DEPTNO NOT IN 
  ( SELECT DEPTNO FROM EMPLOYEE
    WHERE  TO_CHAR(DJ,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
   );
DELETE DETAILS OF LEAVES TAKEN BY EMPLOYEE WHOSE EMPNO IS THE HIGHEST EMPNO
 
DELETE FROM  EMP_LEAVES
WHERE  EMPNO  = ( SELECT MAX(EMPNO) FROM EMPLOYEE);
DISPLAY THE DETAILS OF EMPLOYEES WHO ARE BELONGING TO PRODUCTION DEPT AND HAVE TAKEN MORE THAN 20 LEAVES SO FAR.
 
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
             );
DISPLAY DETAILS OF LEAVES WHERE THE EMPLOYEE SAL IS MORE THAN 10000 AND JOINED IN THE LAST 6 MONTHS.
 
SELECT * FROM EMP_LEAVES
WHERE  EMPNO  IN
    ( SELECT EMPNO 
      FROM  EMPLOYEE
      WHERE  SAL > 10000 AND  MONTHS_BETWEEN(SYSDATE,DJ) <= 6
    );
Display details of employees drawing top 5 salaries.
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 THE SALARY OF EMPLOYEE 102 WITH THE AVERAGE SALARY OF HIS DEPARTMENT.
UPDATE EMPLOYEE E
   SET  SAL= ( SELECT AVG(SAL) FROM EMPLOYEE WHERE DEPTNO = E.DEPTNO)
WHERE EMPNO = 102;
DISPLAY DETAILS OF DEPARTMENT IN WHICH THERE ARE HIGHEST NUMBER OF LEAVES TAKEN
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
    )
 )
RENAME COLUMN DJ TO JOINDATE OF EMPLOYEE TABLE.
RENAMING A COLUMN DONE IN THREE STEPS.

    

  1. 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;


    

  2. DROP EMPLOYEE TABLE.

    
DROP TABLE EMPLOYEE;

    

  3. RENAME NEW TABLE TO EMPLOYEE TABLE.

    
RENAME TEMPLOYEE TO EMPLOYEE;
SWAP THE SALARY OF 101 WITH SALARY OF 103.
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;
CREATE A PROCEDURE TO TAKE EMPNO AND LEAVETYPE AND INSERT A ROW INTO EMP_LEAVES TABLE WITH THE FOLLOWING CONDITIONS.
  • CHECK WHETHER EMPNO AND LEAVETYPE ARE VALID
  • CHECK WHETHER EMPLOYEE IS ALREADY ON LEAVE
  • CHECK WHETHER EMPLOYEE HAS ALREADY USED ALL LEAVES IN THAT TYPE
-- 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 A FUNCTION TO RETURN NAMES OF THE EMPLOYEES DRAWING HIGEST SALARY. IF THERE IS MORE THAN ONE EMPLOYEE, EMPLOYEE NAMES ARE TO BE SEPARATED BY COMMA.
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;
GET THE DETAILS OF DEPT. WHICH IS HEADED BY PERSON WITH THE NAME THAT CONTAINS LETTER 'C' AND 'A'.
SELECT * FROM DEPT
WHERE  HOD LIKE '%C%A%';
DISPLAY THE DETAILS OF DEPT'S WHERE THE DEPTNO IS >10 AND DEPTNAME ENDS WITH 'A'.
SELECT * FROM DEPT 
WHERE  DEPTNO > 10  AND DEPTNAME LIKE '%A';
DISPLAY DETAILS OF EMPLOYEES WHO HAVE MORE THAN 10000 SALARY OR DESG. 'SA'.
SELECT * FROM EMPLOYEE
WHERE SAL > 10000 OR DESG = 'SA';
DISPLAY EMPNO,EMPNAME,SALARY ROUNDED TO 100'S.DOJ AND NO.OF MONTHS BETWEEN TODAY AND DATE OF JOINING.
SELECT EMPNO, EMPNAME, ROUND(SAL,-2) "SAL" , DJ, MONTHS_BETWEEN(SYSDATE,DJ) "NO MONTHS"
FROM EMPLOYEE;
DISPLAY DETAILS OF LEAVES THAT WERE TAKEN IN LAST 20DAYS.
SELECT * FROM EMP_LEAVES 
   WHERE SYSDATE -STDATE <= 20;
DISPLAY DETAILS OF LEAVES IN WHICH THE NO.OF DAYS OF LEAVES IS MORE THAN 5.
 SELECT * FROM EMP_LEAVES
    WHERE ENDDATE - STDATE > 5;
DISPLAY ALL SICK LEAVES BY EMPNO 104.
 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;

Comments

Post a Comment

Popular posts from this blog

PUTTY - The server's host key is not cached in the registry cache

-
Error: "The server's host key is not cached in the registry. You have no guarantee that the server is the computer you think it is. The server's rsa2 key fingerprint is: [SSH KEY FINGERPRINT] If you trust this host, enter "y" to add the key to PuTTY's cache and carry on connecting. If you want to carry on connecting just once, without adding the key to the cache, enter "n". If you do not trust this host, press Return to abandon the connection. Store key in cache? (y/n) y" Answer: Simply run this command in your windows preferred terminal (cmd.exe etc) and agree to accept the host and store the cache: C:\Program Files (x86)\PuTTY\plink.exe -ssh [SSH USERNAME]@[SSH HOST] -P [SSH PORT] echo y | pscp -i /path/to/key/file user@remote:/tmp/file  . echo y | plink -i /path/to/key/file scripts.sh Usually you would hit “y” here, assuming the host key is correct, in order to store it in future connection.  The storage of this goes into the Registry under ...
Read more

OIM-12c Installation - FMW - SOA - IDM

-
Image
Oracle Identity Manager (OIM) 12c Installation Prerequisites: The Oracle Identity Manager software includes the following: 1] Oracle's Java Development Kit (JDK) 1.8 (jdk-8u311-linux-x64.tar.gz) 2]  Fusion Middleware 12.2.1.4.0 3]  SOA Suite 12.2.1.4.0 4] Oracle Identity and Access Management  12.2.1.4.0 What Do You Need? For completing Oracle IDM Setup you will need, Environment as below: Oracle Enterprise Linux 6 or similar, with access to the Internet At least 8GB RAM (16GB recommended) An Oracle Database Enterprise Edition 12 or superior installed and running. Note: Installing a Database for Identity and Access Management (IDM) Suite provides instructions on how to install an Oracle Database that is suitable for Oracle Identity and Access Management Suite. oracle and root user credentials SYS database user credentials Basic understanding of Linux An Oracle Technology Network (OTN) account OIM-11g PS3 Installation Steps: Just for reference https://www.oracle.com/webfol...
Read more

SAML & OAuth 2.0

-
Both SAML (Security Assertion Markup Language) and OAuth 2.0 are widely used protocols for authentication and authorization in web applications, but they serve different purposes. SAML (Security Assertion Markup Language) Purpose: Primarily designed for authentication and single sign-on (SSO) . How it works: A user attempts to access a service provider (SP). The SP redirects the user to an identity provider (IdP). The IdP authenticates the user and sends a SAML assertion to the SP. The SP validates the assertion and grants access to the user. Common use cases: Enterprise applications, government services, and federated identity management systems. OAuth 2.0 Purpose: Designed for authorization , allowing users to grant third-party applications access to their data on a specific service. How it works: A user attempts to access a resource on a service provider. The SP redirects the user to the authorization server. The user grants permission to the application. The authorization s...
Read more