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
| EMPNO | ENAME | MGR | HIREDATE | SAL | DEPTNAME |
| 1 | SMITH | 3 | 12/17/1980 | 800.00 | BANKING |
| 2 | ALLEN | 4 | 2/20/1981 | 1600.00 | INSURANCE |
| 3 | WARD | 2/22/1981 | 1250.00 | BANKING | |
| 4 | JONES | 4/2/1981 | 2975.00 | INSURANCE | |
| 5 | MARTIN | 4 | 9/28/1981 | 1250.00 | INSURANCE |
| 6 | BLAKE | 7 | 5/1/1981 | 2850.00 | SERVICES |
| 7 | CLARK | 6/9/1981 | 2450.00 | SERVICES | |
| 8 | SCOTT | 4 | 12/9/1982 | 3000.00 | INSURANCE |
| 9 | KING | 3 | 11/17/1981 | 5000.00 | BANKING |
| 10 | TURNER | 4 | 9/8/1981 | 1500.00 | INSURANCE |
| 11 | ADAMS | 3 | 1/12/1983 | 1100.00 | BANKING |
| 12 | JAMES | 7 | 12/3/1981 | 950.00 | SERVICES |
| 13 | FORD | 4 | 12/3/1981 | 3000.00 | INSURANCE |
| 14 | MILLER | 4 | 1/23/1982 | 1300.00 | INSURANCE |
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
| EMPID | INCENTIVE_DATE | INCENTIVE_AMOUNT |
| 1 | 2/1/2019 | 5000.00 |
| 2 | 2/6/2019 | 3000.00 |
| 3 | 2/7/2019 | 4000.00 |
| 1 | 1/1/2019 | 4500.00 |
| 2 | 1/4/2019 | 3500.00 |
Basic SQL questions for Practice
- 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=24.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