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)
);
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);
