Oracle SQL Commonly asked Interview questions

5 min readDec 25, 2023

by Abhishek VT

Hi All, Below are the commonly asked Oracle SQL interview questions. Here I am taking 2 tables and performing various operations on it.

Employee Table:

Department Table :

insert into  employee values (543,'Aditya Mall','Manager','dept_12',70000,30,to_date('21-Aug-2018','dd-MM-yyyy'));
insert into  employee values (126,'Pulkith Singh','Developer','dept_12',65000,23,to_date('07-Nov-2019','dd-MM-yyyy'));
insert into  employee values (114,'Ankita Patil','Tester','dept_12',50000,28,to_date('17-Jul-2020','dd-MM-YYYY'));
insert into  employee values (121,'Satish Malghan','Manager','dept_14',55555,30,to_date('21-Nov-2018','dd-MM-yyyy'));
insert into  employee values (343,'Darshan Mukdham','Developer','dept_14',400002730,22,to_date('29-Aug-2023','dd-MM-yyyy'));
insert into  employee values (432,'Chetan SK','Tester','dept_14',23323,23,to_date('30-Aug-2023','dd-MM-yyyy'));
insert into  employee values (212,'Satyam Pandey','Developer','dept_19',45000,28,to_date('19-Dec-2023','dd-MM-yyyy'));
insert into  employee values (987,'Nam dev','Manager','dept_19',78990,32,to_date('19-Dec-2021','dd-MM-yyyy'));
insert into  employee values (540,'Sonu SK','Teter','dept_19',98773,28,to_date('19-Dec-2011','dd-MM-yyyy'));
insert into  employee values (478,'Arun VT','Manger','dept_23',98773,28,to_date('19-Dec-2011','dd-MM-yyyy'));

insert into department values ('dept_12','Trade Finance','543','Banaglore');
insert into department values ('dept_14','Payment Team','121','Pune');
insert into department values ('dept_19','Swift Team','987','Mumbai');

Write a query to retrieve all department names along with the count of employees in each department.

SELECT
    d.dept_name,e.dept_id,
    COUNT(e.emp_id) employee_count
FROM
    employee e
    RIGHT JOIN department d ON e.dept_id = d.dept_id
GROUP BY
    d.dept_name,e.dept_id;

Result :

2. Write a query to retrieve a department names, employee count and total salary of each department.

SELECT
    d.dept_id,
    d.dept_name,
    COUNT(e.emp_id) employee_count,
    SUM(e.salary)   total_salary
FROM
    employee   e
    RIGHT JOIN department d ON e.dept_id = d.dept_id
GROUP BY
    d.dept_id,
    d.dept_name;

Result :

3. Write a query to retrieve a minimum, maximum and average salary of employees.

SELECT
    MAX(e.salary) max_salary,
    MIN(e.salary) min_salary,
    AVG(e.salary) avg_salary
FROM
    employee e;

Result :

4. Write a query to fetch the employee’s full names and replace the space with ‘-’.

SELECT
    replace(e.emp_name, ' ', '-')names
FROM
    employee e;

Result :

5. Write a query to fetch the employee’s full names and roles together ,separated by ‘-’.

SELECT
    concat(e.emp_name, '-' || e.emp_role) full_name_with_role
FROM
    employee e;

//OR

SELECT
   ( e.emp_name
    || '-'
    || e.emp_role)full_name_with_role
FROM
    employee e;

Result :

6. Write a query to find the count of the total occurrences of a particular character — ‘a’ in the employee name field.

SELECT
    e.emp_name,
    length(e.emp_name) - length(replace(e.emp_name, 'a', '')) count_of_a_in_name
FROM
    employee e;

Result :

7. Write a query to fetch the employee’s whose salary is in the range of 30000 to 60000.

SELECT
    *
FROM
    employee e
WHERE
    e.salary BETWEEN 30000 AND 60000;

Result :

8. write a query to fetch the duplicate records from department.

SELECT
    d.dept_id,
    d.dept_name,
    d.city
FROM
    department d
GROUP BY
    d.dept_id,
    d.dept_name,
    d.city
HAVING
    COUNT(*) > 1;

9. Write a query to fetch the employee whose age is even number.


SELECT
    e.emp_name,
    e.emp_role,
    e.age   
FROM
    employee e
WHERE
    mod(e.age, 2) = 0;

Result :

10. Write a query to find the max length of employee names ,show only the first and second max.

SELECT
    emp_name,
    MAX(length(emp_name)) length
FROM
    employee
GROUP BY
    emp_name
ORDER BY
    length(emp_name) DESC
FETCH FIRST 2 ROWS ONLY;

Result :

11. Write a query to fetch the employee name with their roles first character in parentheses.

SELECT
    emp_name
    || '('
    || substr(emp_role, 1, 1)
    || ')' names
FROM
    employee;

Result :

12. write a query to find the rank of the employees based on the salary.

SELECT
    e.*,
    DENSE_RANK()
    OVER(
        ORDER BY
            salary DESC
    ) rank
FROM
    employee e;

Result :

13. Write a query to fetch the rank of the employee within the department based on the salaries.

SELECT
    e.*,
    DENSE_RANK()
    OVER(PARTITION BY dept_id
         ORDER BY
             salary DESC
    ) rank
FROM
    employee e;

Result :

14. Write a query to fetch the 3rd Highest salary taking employee, fetch only one result.

WITH testing AS (
    SELECT
        emp_name,
        salary,
        RANK()
        OVER(
            ORDER BY
                salary DESC
        ) AS ranking
    FROM
        employee
)
SELECT
    *
FROM
    testing
WHERE
    ranking = 3
FETCH FIRST 1 ROW ONLY;

Result :

15. Write a query to fetch the employee name, role and role description based on the roles.

SELECT
    emp_name,
    emp_role,
    (
        CASE
            WHEN emp_role = 'Manager'   THEN
                'Manages the team'
            WHEN emp_role = 'Developer' THEN
                'Develops the software applications'
            WHEN emp_role = 'Tester'    THEN
                'Testing the software applications'
            ELSE
                'NA'
        END
    ) role_discription
FROM
    employee;

Result :

Thank you for taking the time to read my article until the end. I sincerely hope that you have gained valuable insights and knowledge from it. If you found the article enjoyable and informative, I kindly ask you to share it with your friends and colleagues.

Oracle SQL Commonly asked Interview questions

Written by Abhishek vt