In this post, I have listed Database SQL Queries Interview questions. These SQL queries are really important from interview point of view.
These SQL Queries covers most of the database concepts and these SQLs are more frequently interview asking questions
SQL Query to find total employees department-wise(include department with zero employees)?
1 2 3 4 5 6 7 8 9 10 |
SELECT d.deptno, d.dname, COUNT(e.deptno) "TOTAL_EMPLOYEES" FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno GROUP BY d.deptno, d.dname; |
Let’s say I want to get department name and total employees count in each department (include department with zero employees) and sort employees count in ascending order?
1 2 3 4 5 6 7 8 9 |
SELECT d.dname, COUNT(e.deptno) "TOTAL_EMPLOYEES" FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno GROUP BY d.dname ORDER BY COUNT(e.deptno) ; |
SQL query to find average salary department wise?
Get department no and average salary
1 2 3 4 5 6 7 |
SELECT deptno, AVG(sal) "Average_salary" FROM emp GROUP BY deptno; |
Get department no, department name and average salary
1 2 3 4 5 6 7 8 9 10 |
SELECT d.deptno, d.dname, AVG(e.sal) FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno GROUP BY d.deptno, d.dname; |
Get department no, department name and average salary(sort average salary in ascending/descending order
1 2 3 4 5 6 7 8 9 10 11 |
SELECT d.deptno, d.dname, AVG(e.sal) FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno GROUP BY d.deptno, d.dname ORDER BY AVG(e.sal); |
1 2 3 4 5 6 7 8 9 10 11 |
SELECT d.deptno, d.dname, AVG(e.sal) FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno GROUP BY d.deptno, d.dname ORDER BY AVG(e.sal) DESC; |
SQL Query to find 2nd highest salary of employee?
SQL Query to get 2nd highest salary
1 2 3 4 5 6 7 8 9 10 11 |
SELECT MAX(SAL) FROM emp WHERE sal NOT IN ( SELECT MAX(sal) FROM emp ); |
1 2 3 4 5 6 7 8 9 10 11 |
SELECT MAX(sal) FROM emp WHERE sal < ( SELECT MAX(sal) FROM emp ); |
SQL Query to get 3rd highest salary
1 2 3 4 5 6 7 8 9 10 11 |
SELECT MAX(SAL) FROM EMP WHERE SAL <(SELECT MAX(sal) FROM emp WHERE sal < ( SELECT MAX(sal) FROM emp )); |
SQL Query to find the Nth highest salary of an employee?
To get 2nd Highest salary to replace N with 2 and to get 3rd Highest salary to replace N with 3 etc…..
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT ename,sal FROM emp e1 WHERE N-1 = ( SELECT COUNT(DISTINCT sal) FROM emp e2 WHERE e2.sal > e1.sal ); |
Below are the SQL queries to get 2nd/3rd highest salary:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT ename,sal FROM emp e1 WHERE 1 = ( SELECT COUNT(DISTINCT sal) FROM emp e2 WHERE e2.sal > e1.sal ); |
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT ename,sal FROM emp e1 WHERE 3 = ( SELECT COUNT(DISTINCT sal) FROM emp e2 WHERE e2.sal > e1.sal ); |
2nd Form of SQL query to get Nth Highest salary is:
Here to get 2nd highest salary to replace N with 2, To get 3rd highest salary to replace N with 3 etc…
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT * FROM emp e1 WHERE N = ( SELECT COUNT(DISTINCT sal) FROM emp e2 WHERE e2.sal >= e1.sal ); |
SQL Query to find Nth highest salary in Oracle using ROW_NUMBER() function?
To get first/second/3rd/4th etc… highest salary replace N with 1,2,3,4 etc…
Here N = 1,2,3,4…
1 2 3 4 5 6 7 8 |
SELECT * FROM (SELECT e.*, ROW_NUMBER() OVER( ORDER BY sal DESC ) AS rownumber FROM emp e) WHERE rownumber=N |
For Example, to get 2nd highest salary below is the SQL Query:
1 2 3 4 5 6 7 8 |
SELECT * FROM (SELECT e.*, ROW_NUMBER() OVER( ORDER BY sal DESC ) AS rownumber FROM emp e) WHERE rownumber=2 ; |
SQL Query to find Nth highest salary in Oracle using DENSE_RANK() function?
To get first/second/3rd/4th etc… highest salary replace N with 1,2,3,4 etc…
Here N = 1,2,3,4…
1 2 3 4 5 6 7 8 9 |
SELECT * FROM (SELECT ename, sal, DENSE_RANK() OVER( ORDER BY sal DESC ) AS dr FROM emp) WHERE dr=N; |
For Example, to get 2nd highest salary below is the SQL Query:
1 2 3 4 5 6 7 8 9 |
SELECT * FROM (SELECT ename, sal, DENSE_RANK() OVER( ORDER BY sal DESC ) AS dr FROM emp) WHERE dr=2; |
Find Nth Highest Salary Using RANK() Function?
Put N = 1,2,3,4,…etc to get 1st/2nd/3rd/4th ..highest salary in blow SQL query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
select e3.empno , e3.ename , e3.sal FROM ( SELECT e1.sal, RANK() OVER( ORDER BY e1.sal DESC ) rank FROM ( SELECT DISTINCT e2.sal FROM emp e2 ) e1 ) e4, emp e3 WHERE rank = N AND e3.sal = e4.sal; |
SQL query to find duplicate rows in the table?
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT sname, phone, gender, COUNT(*) FROM student GROUP BY sname, phone, gender HAVING COUNT(*) > 1; |
SQL query to create a table with the same structure of the Existing table?
1 2 3 4 5 6 |
CREATE TABLE tempstudent AS SELECT * FROM student; |
SQL query to create a table with the same structure with data of the Existing table?
1 2 3 4 5 6 |
CREATE TABLE tempstudent AS SELECT * FROM student WHERE 1=10; |
NOTE: In WHERE Clause you to provide any false condition like 1=2 or 1=10, ‘A’ = ‘B’
Create SQL Query to get information of Employee where Employee is not assigned to the department?
1 2 3 4 5 6 |
SELECT * FROM employee WHERE deptno IS NULL; |
How to fetch all the records from Employee whose joining year or month or day is given?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT * FROM employee WHERE to_char(hiredate, 'YYYY') = '2019'; SELECT * FROM employee WHERE to_char(hiredate, 'MM') = '04'; SELECT * FROM employee WHERE to_char(hiredate, 'DD') = '23'; |
SQL Query to find all Employees with its managers?
1 2 3 4 5 6 7 8 |
SELECT e.ename "EMPLOYEE_NAME", m.ename "MANAGER_NAME" FROM employee e, employee m WHERE m.empno = e.mgr; |
How to display Number from 1 to Given limit with a SQL query?
1 2 3 4 5 6 7 |
DEFINE LIMIT = 50 SELECT level FROM dual CONNECT BY level <= &LIMIT |
SQL Both Queries display numbers from 1 to 60 :
1 2 3 4 5 6 |
SELECT level FROM dual CONNECT BY level <= 60; |
1 2 3 4 5 6 |
SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 60; |
SQL Query to list deptno, dept name for all the departments in which there are no employees?
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT deptno, dname FROM dept WHERE deptno NOT IN ( SELECT deptno FROM emp ); |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT deptno, dname FROM dept d WHERE NOT EXISTS ( SELECT * FROM emp e WHERE d.deptno = e.deptno ); |
How to write SQL Query to get a total salary from salary and commission?
1 2 3 4 5 |
SELECT ename, sal + nvl(comm, 0) FROM employee; |
SQL Query to Find the Joining date of Employee in YYYY-MON-DD format?
1 2 3 4 5 6 |
SELECT ename, to_char(hiredate, 'YYYY-MON-DD') HIREDATE FROM emp; |
Write a SQL Query to find the maximum salary of each department?
get get deptno,dname & max salary department wise:
1 2 3 4 5 6 7 |
SELECT deptno, MAX(sal) FROM emp GROUP BY deptno; |
get deptno,dname & max salary department wise and sort max salary in ascending order:
1 2 3 4 5 6 7 8 9 10 |
SELECT e.deptno, d.dname, MAX(sal) FROM emp e INNER JOIN DEPT d ON e.deptno = d.deptno GROUP BY e.deptno,d.dname ORDER BY MAX(sal); |
Write SQL Query to get Total Salary department wise where more than 5 employees exist?
1 2 3 4 5 6 7 8 |
SELECT deptno, SUM(sal) "TOTAL_SALARY" FROM emp GROUP BY deptno HAVING COUNT(empno) >5; |
SQL query to increase salary by 20 percent for all employees in department number 10 and 20?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
UPDATE employee SET sal = CASE deptno WHEN 10 THEN sal + (sal *.2) WHEN 20 THEN sal + (sal *.2) ELSE sal END WHERE deptno IN ( 10, 20 ); |
SQL Query to find the Nth minimum salary of employees?
Put N= 1,2,3,4… to get first/second/3rd/4th… minimum salary
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT * FROM employee e1 WHERE N = ( SELECT COUNT(DISTINCT e2.sal) FROM employee e2 WHERE e2.sal <= e1.sal ); |
For Example to get 3rd minimum salary
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT * FROM employee e1 WHERE 2 = ( SELECT COUNT(DISTINCT e2.sal) FROM employee e2 WHERE e2.sal <= e1.sal ); |
To get 4th minimum salary
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DEFINE N = 4; SELECT * FROM employee e1 WHERE &N = ( SELECT COUNT(DISTINCT e2.sal) FROM employee e2 WHERE e2.sal <= e1.sal ); |
Selecting ODD or EVEN rows from a table in Oracle-explained two methods?
SQL query to find the total salary department wise?
SQL query to find total salary department wise and sort total salary in ascending/descending order?
SQL query to find the total number of employees in each department?
SQL to find departments whose the number of employees is greater than 4?
You May Also Like:
Introduction to Hibernate 5
Latest hibernate distribution Zip file download link
Hibernate 5 distribution binary details
Create SessionFactory in Hibernate5 using hibernate.cfg.xml
Create SessionFactory in Hibernate5 without hibernate.cfg.xml
Save and persist an entity example in hibernate
Hibernate CRUD(Create,Read,Update and Delete) example
Dirty checking in hibernate example
Understanding hibernate Configuration File
Why to use hibernate dialect?
Hibernate hbm2ddl property
What are the benefits of using hibernate?
That’s all about Database SQL Queries Interview questions
If you have any feedback or suggestion please feel free to drop in below comment box.