Top 50 Interview Questions on SQL

Sunday, November 16, 2025 12:12 PM

Table and its data:

Select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------ ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

select * from dept;

DEPTNO DNAME LOC

------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

[Link] to retrieve the second highest salary of employee ?

Method 1 (by using Sub-query):
select max(sal) from emp where sal< (select max(sal) from emp);

Method 2 (by using Sub-query):

select max(sal) from emp where sal NOT IN(select max(sal) from emp);

Method 3 (by using correlated sub query):

select distinct(sal) from emp e1
where (select count(distinct sal) from emp e2 where [Link]<=[Link]) =2;

SQL Page 1
Method 4 (by using Window function):
select distinct(ranking), sal from
(
select sal,
dense_rank() over(order by sal desc) as ranking
from emp
)
where ranking=2;

[Link] to get the nth highest salary ?

Method 1 (by using correlated sub query):
select distinct(sal) from emp e1
where (select count(distinct sal) from emp e2 where [Link]<=[Link]) =n;

Method 2 (by using Window function):

select sal from
(
select sal,
dense_rank() over(order by sal desc) as ranking
from emp
)
where ranking=n;

Note: In place of n we need to write nth position

[Link] to get the one or more nth highest salary ?

Eg: 4th and 5th highest salary

Method 1 (by using correlated sub query):

select distinct(sal) from emp e1
where (select count(distinct sal) from emp e2 where [Link]<=[Link])
in (4,5);

Method 2 (by using Window function):

select sal from
(
select sal,
dense_rank() over(order by sal desc) as ranking
from emp
)
where ranking in (4,5);

4 . How do you fetch all employees whose salary is greater

SQL Page 2
4 . How do you fetch all employees whose salary is greater
than the average salary?

Method 1 (by using Sub-query):

select * from emp
where sal >(select avg(sal) from emp);

Method 2 (by using Window function):

select * from
(
select emp.*,
avg(sal) over() as avg_sal
from emp
)
where sal>=avg_sal;

5. How to find the duplicate record in a table ?

Method 1 (by using group and having clause):
select ename,count(*) from emp
group by ename
having count(*)>=2;

Method 2 (by using Window function):

select * from
(
select emp.*,
count(*) over(partition by ename) as counting
from emp
)
where counting>=2;

[Link] do you delete the duplicate records ?

DELETE FROM emp

WHERE ROWID IN (
SELECT rid
FROM (
SELECT ROWID AS rid,
ROW_NUMBER() OVER (PARTITION BY ename ORDER BY ename) AS ranking
FROM emp
)
WHERE ranking >= 2
);

SQL Page 3
);

[Link] do get common records from two tables ?

Select * from emp1

Intersect
Select * from emp2;

[Link] do you fetch the top 5 records from the table ?

select * from
(
select emp.*,
row_number() over(order by empno) as ranking
from emp
)
where ranking<=5;

[Link] do you fetch the top 5 highest salary records from the
table ?
select * from
(
select emp.*,
row_number() over(order by sal desc) ranking
from emp
)
where ranking<=5;

[Link] do you fetch the bottom 5 records from the table ?

select * from
(
select emp.*,
row_number() over(order by empno) as ranking
from emp
)
where ranking>(select count(*)-5 from emp);

[Link] do you fetch the top 5 lowest salary records from

the table ?
select * from
(

SQL Page 4
(
select emp.*,
row_number() over(order by sal asc) ranking
from emp
)
where ranking<=5;

[Link] do you fetch the first and last records from the
table ?
select * from
(
select emp.*,
row_number() over(order by empno) as ranking
from emp
)
where ranking in (1,(select count(*) from emp) );

[Link] do you fetch the even number of records from the

table ?
select * from
(
select emp.*,
row_number() over(order by empno) as ranking
from emp
)
where mod(ranking,2)=0;

[Link] do you fetch the odd number of records from the

table ?
select * from
(
select emp.*,
row_number() over(order by empno) as ranking
from emp
)
where mod(ranking,2)=1;

[Link] to calculate the max, min, total and average salary of

all employees ?
Select max(sal) , min(sal), sum(sal) ,avg(sal) from emp;

SQL Page 5
Select max(sal) , min(sal), sum(sal) ,avg(sal) from emp;

[Link] to calculate the max,min, total, avg salary of all

employees
From each department ?
Select deptno,max(sal) , min(sal), sum(sal) ,avg(sal) from emp
group by deptno;

[Link] to display details of employees whose salary is

greater than avg salary ?

select * from
(
select emp.*,
avg(sal) over() as avg_sal
from emp
)
where sal>avg_sal;

[Link] to display details of employees whose salary is

greater than avg salary from each department ?
Method (by using Window function):
select * from
(
select emp.*,
avg(sal) over(partition by deptno) as avg_sal
from emp
)
where sal>avg_sal;

[Link] to display details of employees whose salary is

maximum from each department ?

Method 1 (by using Window function):

select * from
(
select emp.*,
row_number() over(partition by deptno order by sal desc) ranking
from emp
)
where ranking=1;

SQL Page 6
Method 2 (by using correlated subquery ):
select * from emp e1
where sal=(select max(sal) from emp e2 where [Link]=[Link]);

[Link] to display details of employees whose salary is 2nd

highest salary from each department ?
Method (by using Window function):
select * from
(
select emp.*,
dense_rank() over(partition by deptno order by sal desc) ranking
from emp
)
where ranking=2;

[Link] a query to display details of emp with percentage

according to salary ?
Method 1 (by using aggregate function):
select * from
(
select emp.*,
round(sal/(select sum(sal) from emp)*100 , 2)||'%' as percentage
from emp)
;

Method 2 (by using Window function):

select * from
(
select emp.*,
round(sal/(sum(sal) over() )*100 , 2)||'%' as percentage
from emp)
;

[Link] a query to display details of emp with percentage

according to salary from each department?

Method (by using Window function):

select * from
(
select emp.*,

SQL Page 7
 select emp.*,
round(sal/(sum(sal) over(partition by deptno) )*100 , 2)||'%' as percentage
from emp)
;

[Link] a query to display details of emp with percentage

according to salary from each department
Where percentage is > 20%;
Method (by using Window function):
select * from
(
select emp.*,
round(sal/(sum(sal) over(partition by deptno) )*100 , 2) as percentage
from emp
)
where percentage>20;

[Link] a query to display the sum of salary from each

Month
select to_char(hiredate,'mon') as month , sum(sal)
from emp
group by to_char(hiredate,'mon');

[Link] a query to display the sum of salary from each

year
select to_char(hiredate,'yyyy') as year , sum(sal)
from emp
group by to_char(hiredate,'yyyy');

[Link] a query to display the sum of salary from each

day
select to_char(hiredate,'dy') as day , sum(sal)
from emp
group by to_char(hiredate,'dy');

[Link] a query to display the sum of salary from weekend

select to_char(hiredate,'dy') as day , sum(sal)

SQL Page 8
select to_char(hiredate,'dy') as day , sum(sal)
from emp
group by to_char(hiredate,'dy')
Having to_char(hiredate,'dy') in ('sun','sat');

[Link] a query to display the sum of salary from weekday

select to_char(hiredate,'dy') as day , sum(sal)
from emp
group by to_char(hiredate,'dy')
Having to_char(hiredate,'dy') not in ('sun','sat');

[Link] a query to calculate running total

[Link] total with hiredate:

select emp.*,
sum(sal) over(order by hiredate) as running_total
from emp;

[Link] total with month:

select t.*,
sum(saL) over(order by month) as running_total
from
(
select to_char(hiredate,'mon') as month ,sum(sal) as sal
from emp
group by to_char(hiredate,'mon'))t;

[Link] total with year:

select t.*,
sum(saL) over(order by month) as running_total
from
(
select to_char(hiredate,'yyyy') as month ,sum(sal) as sal
from emp
group by to_char(hiredate,'yyyy'))t;

30. Write a query to display the employees which have same

first name ?

SQL Page 9
select * from emp
where ename in (
select ename from emp
group by ename
having count(*)>=2
);

[Link] a query to display employee's Name and dept's

name

Method 1 (by using join):

select [Link] , [Link] from emp e join dept d
on [Link]=[Link];

Method 2 (by using correlated sub query):

select [Link],( select [Link] from dept d where [Link]=[Link])
from emp e;

[Link] a query to display the employee's name and their

manager's name
select [Link] , [Link] from emp e join emp m
on [Link]=[Link];

[Link] a query to display the employee's name and their

manager manager's name
select [Link] ,[Link] from emp e join emp m
on [Link]=[Link] join emp mm
on [Link]=[Link];

[Link] a query to display the employees details who were

hired before their manager.

select e.* from emp e join emp m

on [Link]=[Link]
where [Link]<[Link];

[Link] a query to display the employees details who have

SQL Page 10
[Link] a query to display the employees details who have
no their manager
select emp.* from emp
where mgr is null;

[Link] a query to display the hierarchy of manager of any

employee.

select emp.*,level
from emp
start with empno=7369
connect by prior mgr=empno;

37. Write a query to display the employee details where

employee's name is start with 's'
select ename from emp
where ename like 'S%';

[Link] a query to display the employee details where

employee's name is end with 's'
SELECT * FROM EMP
WHERE ENAME LIKE '%S';

[Link] a query to display the employee details where

employee's name at last second position there will be 'e'
letter.
select * from emp
where ename like '%E_';

[Link] a query to display the employee details where

employee's name is
i. Start with 'a'
ii. End with 's'

select * from emp

where ename like 'A%S';

SQL Page 11
where ename like 'A%S';

[Link] a query to find the length of each employee's name

select ename , length(ename) from emp;

[Link] a query to concatenate employee's name and job

select ename||' ' || job from emp;

[Link] a query to reverse the ename

select ename,reverse(ename) from emp;

[Link] a query to find the first and last letter of ename

select ename,substr(ename,1,1) as first_letter , substr(ename,-1,1) as last_letter from emp;

[Link] a query to find the palindrome ename

select ename from emp
where ename=reverse(ename);

[Link] a query to find

[Link] ename starts with vowel letter.
select ename from emp
where substr(ename ,1,1) in('A','E','I','O','U');
[Link] middle ename's letter start with vowel letter

select ename from emp

Where substr(ename,(length(ename)/2)+1,1) in('A','E','I','O','U');

[Link] a query as given scenario:

[Link] letter of ename should be Capital letter
[Link] letter of ename should be Capital letter
[Link] middle letter should be small letter
[Link] revere all ename col.

Output table:

SQL Page 12
Output table:
ENAME OUPUT
---------- -----------
SMITH HtimS
ALLEN NellA
WARD DraW
JONES SenoJ
MARTIN NitraM
BLAKE EkalB
CLARK KralC
SCOTT TtocS
KING GniK
TURNER RenruT
ADAMS SmadA
JAMES SemaJ
FORD DroF
MILLER RelliM

QUERY:
select ename,reverse(upper((substr(ename,1,1)))||lower(substr(ename,2,length(ename)-2))||upper
(substr(ename,length(ename),1))) as ouput from emp;

[Link] a query according to input and output

Input data:
table name:emp1

ID NAME GENDER
---------- ---------- ------
1 Alice Female
2 Bob male
3 Carol Female
4 David male
5 Eve Female

Output Table:
GENDER PERCENTAGE
------ --------------------------
male 40%
Female 60%

QUERY:

select gender as Gender , count(*)/(select count(*) from emp1)*100 ||'%' as Percentage

from emp1
group by gender;

SQL Page 13
[Link] a query to divide the emp table into 3 segment
select emp.*,
ntile(3) over(order by empno) as segement
from emp;

[Link] a query according to the scenario :

If sal>=2500 then 'excellent'
Elif 2500<sal >1500 then 'very good'
Else 'good'

select empno,ename,sal,case
when sal>=2500 then 'excellent'
when sal>=1500 and sal<2500 then 'very good'
else 'good'
end as Grading
from emp;

[Link] a query according to the scenario :

Split the table into 3 segment and assign the group name as
first , second and third

Output Table:
EMPNO ENAME SAL GROUPING GRADING
---------- ---------- ---------- ---------- ------
7369 SMITH 800 1 First
7499 ALLEN 1600 1 First
7521 WARD 1250 1 First
7566 JONES 2975 1 First
7654 MARTIN 1250 1 First
7698 BLAKE 2850 2 Second
7782 CLARK 2450 2 Second
7788 SCOTT 3000 2 Second
7839 KING 5000 2 Second
7844 TURNER 1500 2 Second
7876 ADAMS 1100 3 Third
7900 JAMES 950 3 Third
7902 FORD 3000 3 Third
7934 MILLER 1300 3 Third

Query:

SQL Page 14
select t.*,case
when grouping=1 then 'First'
when grouping=2 then 'Second'
else 'Third'
end Grading
from
(
select empno,ename,sal,
ntile(3) over(order by empno) as grouping
from emp
)t
;

SQL Page 15