SQL practice test queries on Employee data in relational table

Learn how to write SQL queries for questions below the data.

Table Name: Employee

create table EMPLOYEE
(
  EMPNO    NUMBER(4) not null,
  ENAME    VARCHAR2(10),
  MGR      NUMBER(4),
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  DEPTNAME VARCHAR2(10)
)
insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (1, 'SMITH', 3, to_date('17-12-1980', 'dd-mm-yyyy'), 800.00, 'BANKING');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (2, 'ALLEN', 4, to_date('20-02-1981', 'dd-mm-yyyy'), 1600.00, 'INSURANCE');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (3, 'WARD', null, to_date('22-02-1981', 'dd-mm-yyyy'), 1250.00, 'BANKING');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (4, 'JONES', null, to_date('02-04-1981', 'dd-mm-yyyy'), 2975.00, 'INSURANCE');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (5, 'MARTIN', 4, to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 'INSURANCE');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (6, 'BLAKE', 7, to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, 'SERVICES');

insert into EMP1 (EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (7, 'CLARK', null, to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, 'SERVICES');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (8, 'SCOTT', 4, to_date('09-12-1982', 'dd-mm-yyyy'), 3000.00, 'INSURANCE');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (9, 'KING', 3, to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, 'BANKING');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (10, 'TURNER', 4, to_date('08-09-1981', 'dd-mm-yyyy'), 1500.00, 'INSURANCE');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (11, 'ADAMS', 3, to_date('12-01-1983', 'dd-mm-yyyy'), 1100.00, 'BANKING');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (12, 'JAMES', 7, to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, 'SERVICES');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (13, 'FORD', 4, to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, 'INSURANCE');

insert into EMPLOYEE(EMPNO, ENAME, MGR, HIREDATE, SAL, DEPTNAME)
values (14, 'MILLER', 4, to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, 'INSURANCE');

EMPNOENAMEMGRHIREDATESALDEPTNAME
1SMITH312/17/1980800.00BANKING
2ALLEN42/20/19811600.00INSURANCE
3WARD 2/22/19811250.00BANKING
4JONES 4/2/19812975.00INSURANCE
5MARTIN49/28/19811250.00INSURANCE
6BLAKE75/1/19812850.00SERVICES
7CLARK 6/9/19812450.00SERVICES
8SCOTT412/9/19823000.00INSURANCE
9KING311/17/19815000.00BANKING
10TURNER49/8/19811500.00INSURANCE
11ADAMS31/12/19831100.00BANKING
12JAMES712/3/1981950.00SERVICES
13FORD412/3/19813000.00INSURANCE
14MILLER41/23/19821300.00INSURANCE

Table Name: Incentives

create table INCENTIVES
(
  EMPID            INTEGER,
  INCENTIVE_DATE   DATE,
  INCENTIVE_AMOUNT NUMBER(18,2)
)
insert into INCENTIVES (EMPID, INCENTIVE_DATE, INCENTIVE_AMOUNT)
values (1, to_date('01-02-2019', 'dd-mm-yyyy'), 5000.00);

insert into INCENTIVES (EMPID, INCENTIVE_DATE, INCENTIVE_AMOUNT)
values (2, to_date('06-02-2019', 'dd-mm-yyyy'), 3000.00);

insert into INCENTIVES (EMPID, INCENTIVE_DATE, INCENTIVE_AMOUNT)
values (3, to_date('07-02-2019', 'dd-mm-yyyy'), 4000.00);

insert into INCENTIVES (EMPID, INCENTIVE_DATE, INCENTIVE_AMOUNT)
values (1, to_date('01-01-2019', 'dd-mm-yyyy'), 4500.00);

insert into INCENTIVES (EMPID, INCENTIVE_DATE, INCENTIVE_AMOUNT)
values (2, to_date('04-01-2019', 'dd-mm-yyyy'), 3500.00);
EMPIDINCENTIVE_DATEINCENTIVE_AMOUNT
12/1/20195000.00
22/6/20193000.00
32/7/20194000.00
11/1/20194500.00
21/4/20193500.00

Basic questions for Practice

  1. Display all the information of the Employee table
SELECT  * 
FROM 	EMP1;

2.Display unique Department names from Employee table.

SELECT DISTINCT DEPTNAME FROM Employee ;

--OR

SELECT UNIQUE DEPTNAME FROM Employee ;
--OR

SELECT  DEPTNAME FROM Employee  GROUP BY DEPTNAME;
--OR
SELECT DEPTNAME FROM Employee  
UNION
SELECT DEPTNAME FROM Employee  

--OR
SELECT DEPTNAME FROM Employee  
MINUS 
SELECT NULL FROM Employee  

--OR
SELECT DEPTNAME 
FROM (
      SELECT DEPTNAME,
             RANK()OVER(PARTITION BY DEPTNAME ORDER BY EMPNO) RNK 
      FROM Employee  
    )
WHERE RNK=1;

-- OR
SELECT DEPTNAME 
FROM (
      SELECT DEPTNAME,
             DENSE_RANK()OVER(PARTITION BY DEPTNAME ORDER BY EMPNO) RNK 
      FROM Employee  
    )
WHERE RNK=1;

3.List the details of the employees in ascending order of their salaries.

SELECT * 
FROM Employee
ORDER BY SAL ASC;

4.List the employees who joined before 1981.

SELECT * 
FROM Employee
WHERE HIREDATE < '01-JAN-1981';

--OR
SELECT * 
FROM Employee
WHERE EXTRACT(YEAR FROM HIREDATE) < 1981;
--OR
SELECT * 
FROM Employee
WHERE TO_CHAR(HIREDATE,'YYYY') < '1981';

--OR

SELECT * 
FROM Employee
WHERE TO_NUMBER(TO_CHAR(HIREDATE,'YYYY')) < 1981;

5.List the Empno, Ename, Sal, Daily Sal of all Employees in the ASC order of AnnSal.

SELECT EMPNO,
       ENAME,
       SAL,
       SAL/30 DAILY_SAL
 FROM Employee
 ORDER BY SAL*12;

6.List the employees who are working for the department name BANKING or INSURANCE



SELECT * 
FROM  Employee
WHERE DEPTNAME in ('BANKING','INSURANCE') ;

--OR

SELECT * 
FROM  Employee
WHERE DEPTNAME ='BANKING' OR DEPTNAME = 'INSURANCE';

--OR
SELECT * 
FROM  Employee
WHERE DEPTNAME =ANY ('BANKING','INSURANCE') ;
--OR
SELECT * 
FROM  Employee
WHERE DEPTNAME =SOME ('BANKING','INSURANCE') ;

7.List the employees who are joined in the year 1981

SELECT * 
FROM   Employee
WHERE HIREDATE LIKE '%81';

--OR

SELECT * 
FROM   Employee
WHERE EXTRACT(YEAR FROM HIREDATE) = '1981';

--OR

SELECT * 
FROM   Employee
WHERE TO_CHAR(HIREDATE,'YYYY') = '1981';

--OR

SELECT * 
FROM   Employee
WHERE REGEXP_LIKE(HIREDATE,'81');

8.List the employees who does not belong to department name INSURANCE

 SELECT * 
 FROM   Employee
 WHERE DEPTNAME <>'INSURANCE';

--OR

SELECT * 
FROM   Employee
WHERE DEPTNAME !='INSURANCE';

10.Select employee details from employee table if data exists in incentive table ?

SELECT *
FROM   Employee E1
WHERE EMPNO  IN (SELECT EMPID 
                    FROM   INCENTIVES
                ); 

--OR

SELECT * 
FROM Employee E1
WHERE EXISTS (SELECT 1 
              FROM INCENTIVES I
              WHERE I.EMPID=E1.EMPNO 
              );

--OR

SELECT * 
FROM Employee E1
WHERE EMPNO 
IN  (SELECT EMPNO FROM Employee E1
      INTERSECT
     SELECT EMPID FROM INCENTIVES I            
    );

11.Get Employee ID’s of those employees who didn’t receive incentives

SELECT * 
FROM Employee E1
WHERE NOT EXISTS 
              (SELECT 1 
              FROM INCENTIVES I
              WHERE I.EMPID=E1.EMPNO 
              );
   --OR           
              
SELECT EMPNO
FROM   Employee E1
WHERE EMPNO 
      NOT IN (SELECT EMPID 
              FROM   INCENTIVES
             );              
--OR

SELECT EMPNO
FROM   Employee E1
MINUS
SELECT EMPID 
FROM   INCENTIVES;

12.query to rank employees based on their incentives for a month

SELECT  ENAME,
        INCENTIVE_AMOUNT,
         RANK() OVER (PARTITION BY TO_CHAR(INCENTIVE_DATE,'MON-YYY') ORDER BY INCENTIVE_AMOUNT DESC) AS rnk 
FROM    Employee  a, INCENTIVES b 
WHERE   a.EMPNO=b.EMPID;  


SELECT  ENAME,
        INCENTIVE_AMOUNT,
        DENSE_RANK() OVER (PARTITION BY TO_CHAR(INCENTIVE_DATE,'MON-YYY') ORDER BY INCENTIVE_AMOUNT DESC) AS rnk 
FROM    Employee  a, INCENTIVES b 
WHERE   a.EMPNO=b.EMPID ;      

SELECT  ENAME,
        INCENTIVE_AMOUNT,
         ROW_NUMBER() OVER (PARTITION BY TO_CHAR(INCENTIVE_DATE,'MON-YYY') ORDER BY INCENTIVE_AMOUNT DESC) AS rnk 
FROM    Employee  a, INCENTIVES b 
WHERE   a.EMPNO=b.EMPID;  

Complex questions for Practice

1. SQL Query to print the number of employees per department in the organization


  SELECT DEPTNAME, 
         COUNT(DEPTNAME) AS Employees_count 
  FROM   Employee
  GROUP  BY DEPTNAME;

2.SQL Query to find the name of the top level manager of each department.

SELECT ENAME
  FROM Employee
  WHERE EMPNO IN (SELECT MGR FROM EMP1 )
  AND MGR IS NULL;
  
  --OR
  
  SELECT ENAME
  FROM Employee E1
  WHERE EXISTS  (SELECT 1 FROM EMP1 E2 WHERE E1.EMPNO=E2.MGR )
  AND E1.MGR IS NULL;

-- OR

  SELECT ENAME
  FROM Employee
  WHERE EMPNO =ANY (SELECT MGR FROM EMP1 )
  AND MGR IS NULL;

--  OR

  SELECT ENAME
  FROM Employee
  WHERE EMPNO =SOME (SELECT MGR FROM EMP1 )
  AND MGR IS NULL;

3.SQL Query to find the employee details who got second maximum incentive in Febrauary 2019

SELECT E1.*
   FROM Employee E1, INCENTIVES A
   WHERE E1.EMPNO = A.EMPID
   AND 2 = (
             SELECT count(1)
             FROM INCENTIVES B
             WHERE TO_CHAR(B.INCENTIVE_DATE,'MM-YYYY')='02-2019'
             AND A.INCENTIVE_AMOUNT <= B.INCENTIVE_AMOUNT
            )
   AND TO_CHAR(A.INCENTIVE_DATE,'MM-YYYY')='02-2019'
   ORDER BY INCENTIVE_AMOUNT ASC;

--OR--
SELECT E1.*
   FROM Employee E1, INCENTIVES A
   WHERE E1.EMPNO = A.EMPID
   AND 2 = (
             SELECT count(1)
             FROM INCENTIVES B
             WHERE TO_CHAR(B.INCENTIVE_DATE,'MON-YYYY')='FEB-2019'
             AND A.INCENTIVE_AMOUNT <= B.INCENTIVE_AMOUNT
            )
   AND TO_CHAR(A.INCENTIVE_DATE,'MON-YYYY')='FEB-2019'
   ORDER BY INCENTIVE_AMOUNT ASC;

--OR--

SELECT E1.*,INCENTIVE_AMOUNT
   FROM Employee E1,(
                 SELECT EMPID,
                 INCENTIVE_AMOUNT,
                 INCENTIVE_DATE,
                 RANK() OVER(PARTITION BY TO_CHAR(INCENTIVE_DATE,'MON-YYYY') ORDER BY INCENTIVE_AMOUNT DESC) RNK
                 FROM   INCENTIVES 
                 ) I1
WHERE E1.EMPNO=I1.EMPID
AND  TO_CHAR(I1.INCENTIVE_DATE,'MON-YYYY') ='FEB-2019'   
AND  I1.RNK=2

--OR

SELECT E1.*,INCENTIVE_AMOUNT
   FROM Employee E1,(
                 SELECT EMPID,
                 INCENTIVE_AMOUNT,
                 INCENTIVE_DATE,
                 ROW_NUMBER() OVER(PARTITION BY TO_CHAR(INCENTIVE_DATE,'MON-YYYY') ORDER BY INCENTIVE_AMOUNT DESC) RNK
                 FROM   INCENTIVES 
                 ) I1
WHERE E1.EMPNO=I1.EMPID
AND  TO_CHAR(I1.INCENTIVE_DATE,'MON-YYYY') ='FEB-2019'   
AND  I1.RNK=2

4.SQL Query to find the employee who got minimum incentive in 2019

SELECT E1.* 
FROM Employee E1, INCENTIVES I1 
WHERE E1.EMPNO=I1.EMPID
AND   I1.INCENTIVE_AMOUNT =
                          (
                           SELECT MIN(INCENTIVE_AMOUNT)
                           FROM INCENTIVES
                           WHERE EXTRACT (YEAR FROM INCENTIVE_DATE)=2019
                           );

5.SQL Query to find the employee who did not get any incentive in january 2019



  SELECT *
  FROM  Employee
  WHERE EMPNO NOT IN
       (SELECT EMPID
          FROM INCENTIVES
         WHERE INCENTIVE_DATE BETWEEN '01-JAN-2019' AND '31-JAN-2019');

--OR

 SELECT *
 FROM Employee
 WHERE EMPNO 
          NOT IN
           (SELECT EMPID
            FROM INCENTIVES
            WHERE TO_CHAR(INCENTIVE_DATE,'MON-YYYY') ='JAN-2019'
            );

6.SQL Query to find the month where Employees got Maximum incentive.

SELECT EXTRACT(MONTH FROM INCENTIVE_DATE) MONTH
FROM  INCENTIVES
WHERE INCENTIVE_AMOUNT=(
                        SELECT MAX(INCENTIVE_AMOUNT) 
                        FROM INCENTIVES
                        );


--OR

SELECT TO_CHAR( INCENTIVE_DATE,'MONTH') MONT,
       TO_CHAR( INCENTIVE_DATE,'MON') MON,
       TO_CHAR( INCENTIVE_DATE,'MM') MON
FROM  INCENTIVES
WHERE INCENTIVE_AMOUNT=(
                        SELECT MAX(INCENTIVE_AMOUNT) 
                        FROM INCENTIVES
                        );                        
                        

7.SQL query to select the employees getting salary greater than the average salary of the department that are working in

SELECT *
FROM Employee E1,
     (
       SELECT DEPTNAME, AVG(SAL) SAL 
       FROM Employee GROUP BY DEPTNAME
     )    E2
WHERE E1.DEPTNAME = E2.DEPTNAME
AND   E1.SAL > E2.SAL;


-----OR

SELECT EMPNO, ENAME, DEPTNAME, SAL
  FROM Employee e1
 WHERE SAL > (
              SELECT avg(SAL) 
              FROM Employee e2 
              WHERE e2.DEPTNAME = e1.DEPTNAME
              );
----OR

SELECT e.*
FROM  (
        SELECT E1.*, 
               avg(SAL) over(partition by DEPTNAME) as avgsalary
        FROM Employee E1
       ) e
 where e.SAL > e.avgsalary;

----OR

SELECT *
FROM Employee E1
WHERE E1.SAL > ALL(
   SELECT avg(SAL) 
   FROM EMP1 e2  
   GROUP BY DEPTNAME);

8.SQL query to compute the group salary of all the employees.

  SELECT EMPNO,
       ENAME,
       MGR,
       SAL,
       (SELECT SUM(SAL)
          FROM Employee e
         START WITH e.ENAME = E1.ENAME
        CONNECT BY PRIOR EMPNO = MGR) GROUP_SAL
FROM Employee E1;

9. SQL query to list the employees and name of employees reporting to each person.

SELECT E.*
FROM   (SELECT EMPNO,
               ENAME,
              ( 
               SELECT ENAME 
               FROM   Employee E1
               WHERE E1.EMPNO = E2.MGR
              ) REPORTING_NAME
        FROM Employee E2
       ) E
WHERE E.REPORTING_NAME IS NOT NULL;

---OR

SELECT E1.EMPNO,E1.ENAME,E2.ENAME
FROM Employee E1,
          (
           SELECT EMPNO,
                  ENAME 
           FROM   Employee
           WHERE  EMPNO IN 
                        ( SELECT DISTINCT MGR
                          FROM   Employee
                        )
             )
           E2
WHERE E1.MGR=E2.EMPNO   ;        

--- OR

WITH E2 AS
 ( SELECT EMPNO, ENAME
    FROM Employee
   WHERE EMPNO IN (SELECT DISTINCT MGR FROM Employee))

SELECT E1.EMPNO, E1.ENAME, E2.ENAME
  FROM Employee E1, E2
 WHERE E1.MGR = E2.EMPNO;

10.SQL query to find the department with highest number of employees.

SELECT DEPTNAME
FROM   (    
         SELECT DEPTNAME,
                Empcnt_per_dept,
                DENSE_RANK() over(ORDER BY Empcnt_per_dept DESC) RNK
         FROM ( SELECT distinct DEPTNAME,
                COUNT(1) over(partition by DEPTNAME) Empcnt_per_dept
                  FROM Employee E1
               )
        )
WHERE RNK = 1;

---- OR

SELECT DEPTNAME
FROM   ( SELECT DEPTNAME, COUNT(1) CNT
         FROM Employee E1
         GROUP BY DEPTNAME
         ORDER BY CNT DESC
       )
WHERE ROWNUM = 1;

--

WITH E1 AS
(        SELECT DEPTNAME, COUNT(1) CNT
         FROM Employee E1
         GROUP BY DEPTNAME
         ORDER BY CNT DESC
)
SELECT DEPTNAME FROM E1 WHERE ROWNUM=1;

11. SQL query to find the employees hired in last n months.

SELECT * FROM Employee 
WHERE FLOOR(EXTRACT (YEAR FROM SYSDATE)*12)-FLOOR(EXTRACT (YEAR FROM HireDate)*12) 
BETWEEN 1 AND 10 -- N is the number months


Leave a Comment