SQL interviews often include tricky questions to assess candidates’ skills and problem-solving abilities. To find out more, let’s get right in.

Table of Contents

These SQL queries questions are based on the Oracle database, although they can be used directly or with minor modifications with other databases.

Below Employee table is considered for most of the questions else otherwise mentioned with the question itself.

1. Write a SQL to get Nth maximum salary

--Second highest salary (N=2)
SELECT DISTINCT salary 
   FROM employee A WHERE 2=(SELECT COUNT (DISTINCT B.salary) FROM employee  B WHERE A.salary<=B.salary);

--Output : 10000

2. Write a SQL to get maximum salary without using max() function

-- Option 1 using ALL
SELECT salary 
  FROM employee 
WHERE salary >= ALL (SELECT salary FROM employee);

-- Option 2 using ANY
SELECT DISTINCT salary 
 FROM employee 
WHERE salary NOT IN (SELECT salary FROM employee WHERE salary < ANY (SELECT salary FROM employee));

--Output : 12000 in both cases

3. DELETE duplicate rows

Using MAX() function we are keeping the latest rows, you can use MIN() to keep the oldest rows and delete the latest. Here column “EMP_NUM” considered as duplicate, you can have any number of columns in GROUP BY clause based on which rows will be deleted.

--Delete duplicate based on column "EMP_ID" and columns unique ROWID (system provided ID)
DELETE from employee WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM employee group by emp_id );

--You can use any unique column like "ID" of table  
DELETE from employee WHERE id NOT IN (SELECT MAX(id) FROM employee group by emp_id );

--Output : deleted row will be 
ID	EMP_ID	EMP_NAME	SALARY	DEPT_NO	MANAGER_ID DOJ	        DOL	DOB
1	101	Ajay Singh	10000	2	-          20-AUG-22	 - 	 - 

4. Convert number to word

SELECT TO_CHAR (TO_DATE (100,'J'),'JSP') FROM DUAL;

--Output : ONE HUNDRED

5. Get the Nth row from a table

-- N=5, getting 5th row
SELECT * 
 FROM employee 
WHERE rowid = (
    SELECT rowid FROM employee WHERE rownum <= 5
     MINUS
    SELECT rowid FROM employee WHERE rownum < 5
);

--Output :
ROW_ID	EMP_ID	EMP_NAME	SALARY	DEPT_NO	MANAGER_ID DOJ	        DOL	DOB
5	101	Ajay Singh	10000	2	-          20-AUG-22	 - 	 -

6. Get X to Y rows from a table

-- Getting 3 to 5 rows (X=3 and Y=5)
SELECT * 
 FROM employee 
WHERE rowid in (
    SELECT rowid FROM employee WHERE rownum <= 5 
     MINUS
    SELECT rowid FROM employee WHERE rownum < 3
);

--Output :
ROW_ID	EMP_ID	EMP_NAME	SALARY	DEPT_NO	MANAGER_ID DOJ	        DOL	DOB
3	103	Vishal Nigam	7500	2	101        25-DEC-20	 - 	 - 
4	104	Ashish Joshi	12000	2	101        07-JUL-22	 - 	 - 
5	101	Ajay Singh	10000	2	-          20-AUG-22	 - 	 -

7. Get every Nth rows from a table

Getting every 2nd rows from the table (N=2), for getting every 4th rows we can replace 2 with 4.

-- Option 1 
SELECT * 
 FROM employee 
WHERE (ROWID, 0) IN (SELECT ROWID, MOD(ROWNUM, 2) FROM employee);

--Option 2
SELECT * 
 FROM  (SELECT e.*,rownum rn FROM employee e ) t 
WHERE MOD(t.rn, 2) = 0;

--Output :
ROW_ID	EMP_ID	EMP_NAME	SALARY	DEPT_NO	MANAGER_ID  DOJ	        DOL	DOB
2	102	Payal Verma	5000	2	101         01-JAN-21	 - 	 - 	
4	104	Ashish Joshi	12000	2	101         07-JUL-22	 - 	 - 	

8. Get EVEN rows from a table

Note: Query is same as previous one but for getting EVEN we need to fix the N=2, and for Nth rows we can change it to any number.

-- Option 1 
SELECT * 
 FROM employee 
WHERE (ROWID, 0) IN (SELECT ROWID, MOD(ROWNUM, 2) FROM employee);

--Option 2
SELECT * 
 FROM  (SELECT e.*,rownum rn FROM employee e ) t 
WHERE MOD(t.rn, 2) = 0;

--Output :
ROW_ID	EMP_ID	EMP_NAME	SALARY	DEPT_NO	MANAGER_ID DOJ	        DOL	DOB
2	102	Payal Verma	5000	2	101        01-JAN-21	 - 	 - 	
4	104	Ashish Joshi	12000	2	101        07-JUL-22	 - 	 - 	

9. Write a SQL to get ODD rows from a table

Note: Below queries are almost same as previous query, only change 1 from 0 to get all ODD rows from the table

-- Option 1 
SELECT * 
 FROM employee 
WHERE (ROWID, 1) IN (SELECT ROWID, MOD(ROWNUM, 2) FROM employee);

--Option 2
SELECT * 
 FROM  (SELECT e.*,rownum rn FROM employee e ) t 
WHERE MOD(t.rn, 2) = 1;

--Output :
ROW_ID	EMP_ID	EMP_NAME	SALARY	DEPT_NO	MANAGER_ID   DOJ	 DOL	DOB
1	101	Ajay Singh	10000	2	-            20-AUG-22	 - 	 - 
3	103	Vishal Nigam	7500	2	101          25-DEC-20	 - 	 - 
5	101	Ajay Singh	10000	2	-            20-AUG-22	 - 	 - 

10. To find all employees who are earning more than their managers

SELECT emp.*
FROM employee emp
 INNER JOIN employee mgr 
ON emp.manager_id = mgr.employee_id
 WHERE emp.salary > mgr.salary;

-- Output
ROW_ID	EMP_ID	EMP_NAME	SALARY	DEPT_NO	MANAGER_ID	DOJ	        DOL	DOB
4	104	Ashish Joshi	12000	2	101	        07-JUL-22	 - 	 - 

11. Write a SQL query to find the five most common names in the Employee table

SELECT emp_name, COUNT(*) AS name_count
FROM employee
GROUP BY emp_name
ORDER BY name_count DESC,emp_name 
    FETCH FIRST 2 ROWS ONLY;

-- Output
EMP_NAME	NAME_COUNT
Ajay Singh	2
Ashish Joshi	1

Learn more about SQL and other topics

• Learn SQL by Oracle
• What is SQL by AWS
• Are You Good Enough in PLSQL? Test your knowledge right now
• Test Your Knowledge on Python Lists
• Oracle Interview Questions: Must Know Before Your Big Day
• Python Interview Questions: A Quick Refresher for Intermediate
• Python Interview Questions: A Quick Refresher for Expert