Database - KK JavaTutorials

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)?

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;

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?

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

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

SELECT
    deptno,
    AVG(sal) "Average_salary"
FROM
    emp
GROUP BY
    deptno;

SELECT

deptno,

AVG(sal) "Average_salary"

FROM

emp

GROUP BY

deptno;

Get department no, department name and average salary

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;

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

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

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

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;

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

SELECT
    MAX(SAL)
FROM
    emp
WHERE
    sal NOT IN (
        SELECT
            MAX(sal)
        FROM
            emp
    );

SELECT

MAX(SAL)

FROM

emp

WHERE

sal NOT IN (

SELECT

MAX(sal)

FROM

emp

);

SELECT
    MAX(sal)
FROM
    emp
WHERE
    sal < (
        SELECT
            MAX(sal)
        FROM
            emp
    );

SELECT

MAX(sal)

FROM

emp

WHERE

sal < (

SELECT

MAX(sal)

FROM

emp

);

SQL Query to get 3rd highest salary

SELECT MAX(SAL) FROM EMP WHERE SAL <(SELECT
    MAX(sal)
FROM
    emp
WHERE
    sal < (
        SELECT
            MAX(sal)
        FROM
            emp
    ));

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…..

SELECT
   ename,sal
FROM
    emp e1
WHERE
    N-1 = (
        SELECT
            COUNT(DISTINCT sal)
        FROM
            emp e2
        WHERE
            e2.sal > e1.sal
    );

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:

SELECT
   ename,sal
FROM
    emp e1
WHERE
    1 = (
        SELECT
            COUNT(DISTINCT sal)
        FROM
            emp e2
        WHERE
            e2.sal > e1.sal
    );

SELECT

ename,sal

FROM

emp e1

WHERE

1 = (

SELECT

COUNT(DISTINCT sal)

FROM

emp e2

WHERE

e2.sal > e1.sal

);

SELECT
   ename,sal
FROM
    emp e1
WHERE
    3 = (
        SELECT
            COUNT(DISTINCT sal)
        FROM
            emp e2
        WHERE
            e2.sal > e1.sal
    );

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…

SELECT
    *
FROM
    emp e1
WHERE
    N = (
        SELECT
            COUNT(DISTINCT sal)
        FROM
            emp e2
        WHERE
            e2.sal >= e1.sal
    );

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…

SELECT * FROM (SELECT
    e.*,
    ROW_NUMBER() OVER(
        ORDER BY
            sal DESC
    ) AS rownumber
FROM
    emp e) WHERE rownumber=N

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:

SELECT * FROM (SELECT
    e.*,
    ROW_NUMBER() OVER(
        ORDER BY
            sal DESC
    ) AS rownumber
FROM
    emp e) WHERE rownumber=2 ;

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…

SELECT * FROM (SELECT
    ename,
    sal,
    DENSE_RANK() OVER(
        ORDER BY
            sal DESC
    ) AS dr
FROM
    emp) WHERE dr=N;

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:

SELECT * FROM (SELECT
    ename,
    sal,
    DENSE_RANK() OVER(
        ORDER BY
            sal DESC
    ) AS dr
FROM
    emp) WHERE dr=2;

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

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;

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?

SELECT
    sname,
    phone,
    gender,
    COUNT(*)
FROM
    student
GROUP BY
    sname,
    phone,
    gender
HAVING
    COUNT(*) > 1;

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?

CREATE TABLE tempstudent
    AS
        SELECT
            *
        FROM
            student;

CREATE TABLE tempstudent

SELECT

FROM

student;

SQL query to create a table with the same structure with data of the Existing table?

CREATE TABLE tempstudent
    AS
        SELECT
            *
        FROM
            student WHERE 1=10;

CREATE TABLE tempstudent

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?

SELECT
    *
FROM
    employee
WHERE
    deptno IS NULL;

SELECT

FROM

employee

WHERE

deptno IS NULL;

How to fetch all the records from Employee whose joining year or month or day is given?

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

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?

SELECT
    e.ename "EMPLOYEE_NAME",
    m.ename "MANAGER_NAME"
FROM
    employee  e,
    employee  m
WHERE
  m.empno = e.mgr;

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?

DEFINE LIMIT = 50
SELECT
    level
FROM
    dual
CONNECT BY
    level <= &LIMIT

DEFINE LIMIT = 50

SELECT

level

FROM

dual

CONNECT BY

level <= &LIMIT

SQL Both Queries display numbers from 1 to 60 :

SELECT
    level
FROM
    dual
CONNECT BY
    level <= 60;

SELECT

level

FROM

dual

CONNECT BY

level <= 60;

SELECT
    ROWNUM
FROM
    dual
CONNECT BY
    ROWNUM <= 60;

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?

SELECT
    deptno,
    dname
FROM
    dept
WHERE
    deptno NOT IN (
        SELECT
            deptno
        FROM
            emp
    );

SELECT

deptno,

dname

FROM

dept

WHERE

deptno NOT IN (

SELECT

deptno

FROM

emp

);

SELECT
    deptno,
    dname
FROM
    dept d
WHERE
    NOT EXISTS (
        SELECT
            *
        FROM
            emp e
        WHERE
            d.deptno = e.deptno
    );

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?

SELECT
    ename,
    sal + nvl(comm, 0)
FROM
    employee;

SELECT

ename,

sal + nvl(comm, 0)

FROM

employee;

SQL Query to Find the Joining date of Employee in YYYY-MON-DD format?

SELECT
    ename,
    to_char(hiredate, 'YYYY-MON-DD') HIREDATE
    
FROM
    emp;

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:

SELECT
    deptno,
    MAX(sal)
FROM
    emp
GROUP BY
    deptno;

SELECT

deptno,

MAX(sal)

FROM

emp

GROUP BY

deptno;

get deptno,dname & max salary department wise and sort max salary in ascending order:

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

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?

SELECT
    deptno,
    SUM(sal) "TOTAL_SALARY"
FROM
    emp
GROUP BY
    deptno
    HAVING COUNT(empno) >5;

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?

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

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,

);

SQL Query to find the Nth minimum salary of employees?

Put N= 1,2,3,4… to get first/second/3rd/4th… minimum salary

SELECT
    *
FROM
    employee e1
WHERE
    N = (
        SELECT
            COUNT(DISTINCT e2.sal)
        FROM
            employee e2
        WHERE
            e2.sal <= e1.sal
    );

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

SELECT
    *
FROM
    employee e1
WHERE
    2 = (
        SELECT
            COUNT(DISTINCT e2.sal)
        FROM
            employee e2
        WHERE
            e2.sal <= e1.sal
    );

SELECT

FROM

employee e1

WHERE

2 = (

SELECT

COUNT(DISTINCT e2.sal)

FROM

employee e2

WHERE

e2.sal <= e1.sal

);

To get 4th minimum salary

DEFINE N = 4;

SELECT
    *
FROM
    employee e1
WHERE
    &N = (
        SELECT
            COUNT(DISTINCT e2.sal)
        FROM
            employee e2
        WHERE
            e2.sal <= e1.sal
    );