SQL practice test queries on Employee data in relational table

Learn how to write SQL queries for questions given below. Queries are based on the Employee and Incentives tables given below.

Let’s prepare the both the table first and then start with SQL queries.

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

Employee Table with data

EMPNOENAMEMGRHIREDATESALDEPTNAME
1SMITH312/17/1980800.00BANKING
2ALLEN42/20/19811600.00INSURANCE
3WARD2/22/19811250.00BANKING
4JONES4/2/19812975.00INSURANCE
5MARTIN49/28/19811250.00INSURANCE
6BLAKE75/1/19812850.00SERVICES
7CLARK6/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);

Incentives Table with data

EMPIDINCENTIVE_DATEINCENTIVE_AMOUNT
12/1/20195000.00
22/6/20193000.00
32/7/20194000.00
11/1/20194500.00
21/4/20193500.00

Basic SQL 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 SQL Query 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

Related Posts