SQL Scenario based Interview Questions

1. How to select UNIQUE records from a table using a SQL Query?

Consider below EMPLOYEE table as the source data

CREATE TABLE EMPLOYEE (
    EMPLOYEE_ID NUMBER(6,0), 
    NAME VARCHAR2(20), 
    SALARY NUMBER(8,2) 
);

INSERT INTO EMPLOYEE(EMPLOYEE_ID,NAME,SALARY) VALUES(100,'Jennifer',4400);
INSERT INTO EMPLOYEE(EMPLOYEE_ID,NAME,SALARY) VALUES(100,'Jennifer',4400);
INSERT INTO EMPLOYEE(EMPLOYEE_ID,NAME,SALARY) VALUES(101,'Michael',13000);
INSERT INTO EMPLOYEE(EMPLOYEE_ID,NAME,SALARY) VALUES(101,'Michael',13000);
INSERT INTO EMPLOYEE(EMPLOYEE_ID,NAME,SALARY) VALUES(101,'Michael',13000);
INSERT INTO EMPLOYEE(EMPLOYEE_ID,NAME,SALARY) VALUES(102,'Pat',6000);
INSERT INTO EMPLOYEE(EMPLOYEE_ID,NAME,SALARY) VALUES(102,'Pat',6000);
INSERT INTO EMPLOYEE(EMPLOYEE_ID,NAME,SALARY) VALUES(103,'Den',11000);

SELECT * FROM EMPLOYEE;

METHOD-1: Using GROUP BY Function

GROUP BY clause is used with SELECT statement to collect data from multiple records and group the results by one or more columns. The GROUP BY clause returns one row per group. By applying GROUP BY function on all the source columns, unique records can be queried from the table.

Below is the query to fetch the unique records using GROUP BY function.

Query:

SELECT 
  EMPLOYEE_ID, 
  NAME, 
  SALARY 
FROM EMPLOYEE 
GROUP BY EMPLOYEE_ID, NAME, SALARY;

Result:

Related Article: GROUP BY ALL Function

METHOD-2: Using ROW_NUMBER Analytic Function

The ROW_NUMBER Analytic function is used to provide consecutive numbering of the rows in the result by the ORDER selected for each PARTITION specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.

Using ROW_NUMBER Analytic function, assign row numbers to each unique set of records.

Query:

SELECT 
  EMPLOYEE_ID, 
  NAME, 
  SALARY, 
  ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_ID,NAME,SALARY ORDER BY EMPLOYEE_ID) AS ROW_NUMBER
FROM EMPLOYEE;

Result:

Query:

SELECT EMPLOYEE_ID, NAME, SALARY FROM( 
  SELECT 
    EMPLOYEE_ID, 
    NAME, 
    SALARY, 
    ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_ID,NAME,SALARY ORDER BY EMPLOYEE_ID) AS ROW_NUMBER 
  FROM EMPLOYEE)
WHERE ROW_NUMBER = 1;

Result:

Related Article: Filter Window Functions using QUALIFY Clause

2. How to delete DUPLICATE records from a table using a SQL Query?

Consider the same EMPLOYEE table as source discussed in previous question

METHOD-1: Using ROWID and ROW_NUMBER Analytic Function

An Oracle server assigns each row in each table with a unique ROWID to identify the row in the table. The ROWID is the address of the row which contains the data object number, the data block of the row, the row position and data file.

STEP-1: Using ROW_NUMBER Analytic function, assign row numbers to each unique set of records. Select ROWID of the rows along with the source columns

Query:

SELECT 
  ROWID,
  EMPLOYEE_ID,
  NAME,
  SALARY, 
  ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_ID,NAME,SALARY ORDER BY EMPLOYEE_ID) AS ROW_NUMBER 
FROM EMPLOYEE;

Result:

STEP-2: Select ROWID of records with ROW_NUMBER > 1

Query:

SELECT ROWID FROM( 
    SELECT 
      ROWID,
      EMPLOYEE_ID,
      NAME,
      SALARY, 
      ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_ID,NAME,SALARY ORDER BY EMPLOYEE_ID) AS ROW_NUMBER 
    FROM EMPLOYEE)
WHERE ROW_NUMBER > 1;

Result:

STEP-3: Delete the records from the source table using the ROWID values fetched in previous step

Query:

DELETE FROM EMP WHERE ROWID IN (
  SELECT ROWID FROM(
      SELECT ROWID,
      ROW_NUMBER() OVER(PARTITION BY EMPLOYEE_ID,NAME,SALARY ORDER BY EMPLOYEE_ID) AS ROW_NUMBER 
  FROM EMPLOYEE)
WHERE ROW_NUMBER > 1);

Result:

The table EMPLOYEE will have below records after deleting the duplicates

METHOD-2: Using ROWID and Correlated subquery

Correlated subquery is used for row-by-row processing. With a normal nested subquery, the inner SELECT query runs once and executes first. The returning values will be used by the main query. A correlated subquery, however, executes once for every row of the outer query. In other words, the inner query is driven by the outer query.

In the below query, we are comparing the ROWIDs’ of the unique set of records and keeping the record with MIN ROWID and deleting all other rows.

Query:

DELETE FROM EMPLOYEE A WHERE ROWID > (
  SELECT MIN(ROWID) FROM EMPLOYEE B WHERE B.EMPLOYEE_ID = A.EMPLOYEE_ID );

Result:

The table EMPLOYEE will have below records after deleting the duplicates

The opposite of above discussed case can be implemented by keeping the record with MAX ROWID from the unique set of records and delete all other duplicates by executing below query.

Query:

DELETE FROM EMPLOYEE A WHERE ROWID < (
  SELECT MAX(ROWID) FROM EMPLOYEE B WHERE B.EMPLOYEE_ID = A.EMPLOYEE_ID );

Result:

The table EMPLOYEE will have below records after deleting the duplicates

Related Article: How to Remove Duplicates in Snowflake?

3. How to read TOP 5 records from a table using a SQL query?

Consider below table DEPARTMENTS as the source data

CREATE TABLE Departments(
    Department_ID number,
    Department_Name varchar(50)
);

INSERT INTO DEPARTMENTS VALUES('10','Administration');
INSERT INTO DEPARTMENTS VALUES('20','Marketing');
INSERT INTO DEPARTMENTS VALUES('30','Purchasing');
INSERT INTO DEPARTMENTS VALUES('40','Human Resources');
INSERT INTO DEPARTMENTS VALUES('50','Shipping');
INSERT INTO DEPARTMENTS VALUES('60','IT');
INSERT INTO DEPARTMENTS VALUES('70','Public Relations');
INSERT INTO DEPARTMENTS VALUES('80','Sales');

SELECT * FROM Departments;

ROWNUM is a “Pseudocolumn” that assigns a number to each row returned by a query indicating the order in which Oracle selects the row from a table. The first row selected has a ROWNUM of 1, the second has 2, and so on.

Query:

SELECT * FROM Departments WHERE ROWNUM <= 5;

Result:

4. How to read LAST 5 records from a table using a SQL query?

Consider the same DEPARTMENTS table as source discussed in previous question.

In order to select the last 5 records we need to find (count of total number of records – 5) which gives the count of records from first to last but 5 records.

Using the MINUS function we can compare all records from DEPARTMENTS table with records from first to last but 5 from DEPARTMENTS table which give the last 5 records of the table as result.

MINUS operator is used to return all rows in the first SELECT statement that are not present in the second SELECT statement.

Query:

SELECT * FROM Departments
MINUS
SELECT * FROM Departments WHERE ROWNUM <= (SELECT COUNT(*)-5 FROM Departments);

Result:

5. What is the result of Normal Join, Left Outer Join, Right Outer Join and Full Outer Join between the tables A & B?

Table_A

Table_B

Normal Join:

Normal Join or Inner Join is the most common type of join. It returns the rows that are exact match between both the tables.

The following Venn diagram illustrates a Normal join when combining two result sets:

Query:

SELECT 
    a.COL AS A, 
    b.COL AS B 
FROM TABLE_A a JOIN TABLE_B b 
ON a.COL = b.COL;

Result:

Left Outer Join:

The Left Outer Join returns all the rows from the left table and only the matching rows from the right table. If there is no matching row found from the right table, the left outer join will have NULL values for the columns from right table.

The following Venn diagram illustrates a Left join when combining two result sets:

Query:

SELECT 
    a.COL AS A, 
    b.COL AS B 
FROM TABLE_A a LEFT OUTER JOIN TABLE_B b 
ON a.COL = b.COL;

Result:

Right Outer Join:

The Right Outer Join returns all the rows from the right table and only the matching rows from the left table. If there is no matching row found from the left table, the right outer join will have NULL values for the columns from left table.

The following Venn diagram illustrates a Right join when combining two result sets:

Query:

SELECT 
    a.COL AS A, 
    b.COL AS B 
FROM TABLE_A a RIGHT OUTER JOIN TABLE_B b 
ON a.COL = b.COL;

Result:

Full Outer Join:

The Full Outer Join returns all the rows from both the right table and the left table. If there is no matching row found, the missing side columns will have NULL values.

The following Venn diagram illustrates a Full join when combining two result sets:

Query:

SELECT 
    a.COL AS A, 
    b.COL AS B 
FROM TABLE_A a FULL OUTER JOIN TABLE_B b 
ON a.COL = b.COL;

Result:

NOTE: NULL do not match with NULL

6. How to find the employee with second MAX Salary using a SQL query?

Consider below EMPLOYEES table as the source data

CREATE TABLE Employees(
    EMPLOYEE_ID NUMBER(6,0), 
    NAME VARCHAR2(20 BYTE), 
    SALARY NUMBER(8,2)
);
        
INSERT INTO EMPLOYEES(EMPLOYEE_ID,NAME,SALARY) VALUES(100,'Jennifer',4400);
INSERT INTO EMPLOYEES(EMPLOYEE_ID,NAME,SALARY) VALUES(101,'Michael',13000);
INSERT INTO EMPLOYEES(EMPLOYEE_ID,NAME,SALARY) VALUES(102,'Pat',6000);
INSERT INTO EMPLOYEES(EMPLOYEE_ID,NAME,SALARY) VALUES(103,'Den', 11000);
INSERT INTO EMPLOYEES(EMPLOYEE_ID,NAME,SALARY) VALUES(104,'Alexander',3100);
INSERT INTO EMPLOYEES(EMPLOYEE_ID,NAME,SALARY) VALUES(105,'Shelli',2900);
INSERT INTO EMPLOYEES(EMPLOYEE_ID,NAME,SALARY) VALUES(106,'Sigal',2800);
INSERT INTO EMPLOYEES(EMPLOYEE_ID,NAME,SALARY) VALUES(107,'Guy',2600);
INSERT INTO EMPLOYEES(EMPLOYEE_ID,NAME,SALARY) VALUES(108,'Karen',2500);

SELECT * FROM Employees;

METHOD-1: Without using SQL Analytic Functions

In order to find the second MAX salary, employee record with MAX salary needs to be eliminated. It can be achieved by using below SQL query.

Query:

SELECT MAX(salary) AS salary FROM Employees WHERE salary NOT IN (
  SELECT MAX(salary) AS salary FROM Employees);

Result:

The above query only gives the second MAX salary value. In order to fetch the entire employee record with second MAX salary we need to do a self-join on Employee table based on Salary value.

Query:

WITH TEMP AS(
  SELECT MAX(salary) AS salary FROM Employees WHERE salary NOT IN (
    SELECT MAX(salary) AS salary FROM Employees)
)
SELECT a.* FROM Employees a JOIN TEMP b on a.salary = b.salary;

Result:

METHOD-2: Using SQL Analytic Functions

Query:

The DENSE_RANK is an analytic function that calculates the rank of a row in an ordered set of rows starting from 1. Unlike the RANK function, the DENSE_RANK function returns rank values as consecutive integers.

SELECT 
  Employee_Id, 
  Name, 
  Salary 
FROM(
   SELECT 
      Employees.*, 
      DENSE_RANK() OVER(ORDER BY Salary DESC) as SALARY_RANK 
   FROM Employees
)
WHERE SALARY_RANK =2;

Result:

By replacing the value of SALARY_RANK, any highest salary rank can be found easily.

Related Article: SQL Analytic Functions Interview Questions

7. How to find the employee with third MAX Salary using a SQL query without using Analytic Functions?

Consider the same EMPLOYEES table as source discussed in previous question

In order to find the third MAX salary, we need to eliminate the top 2 salary records. But we cannot use the same method we used for finding second MAX salary (not a best practice). Imagine if we have to find the fifth MAX salary. We should not be writing a query with four nested sub queries.

STEP-1:

The approach here is to first list all the records based on Salary in the descending order with MAX salary on top and MIN salary at bottom. Next, using ROWNUM select the top 2 records.

Query:

SELECT salary FROM(
  SELECT salary FROM Employees ORDER BY salary DESC
)
WHERE ROWNUM < 3;

Result:

STEP-2:

Next find the MAX salary from EMPLOYEE table which is not one of top two salary values fetched in the earlier step.

Query:

SELECT MAX(salary) as salary FROM Employees WHERE salary NOT IN (
    SELECT salary FROM(
      SELECT salary FROM Employees ORDER BY salary DESC
    )
    WHERE ROWNUM < 3
);

Result:

STEP-3:

In order to fetch the entire employee record with third MAX salary we need to do a self-join on Employee table based on Salary value.

Query:

WITH TEMP AS(
  SELECT MAX(salary) as salary FROM Employees WHERE salary NOT IN (
      SELECT salary FROM(
        SELECT salary FROM Employees ORDER BY salary DESC
      )
      WHERE ROWNUM < 3)
)
SELECT a.* FROM Employees a join TEMP b on a.salary = b.salary;

Result:

In order to find the employee with nth highest salary, replace the rownum value with n in above query.

8. Write an SQL query to find authors with more than one book and an average book price exceeding $10.

Consider below BOOKS table as the source data.

Using HAVING Clause:

HAVING clause in SQL is used in conjunction with the GROUP BY clause to filter the results of a query based on aggregated values. Unlike the WHERE clause, which filters individual rows before they are grouped, the HAVING clause filters the result set after the grouping and aggregation process.

Using GROUP BY clause, we can find the number of books published and the average price of books published by each author as shown below.

Query:

SELECT
   AUTHOR,
   COUNT(*) AS BOOK_COUNT,
   AVG(PRICE) AS AVG_PRICE
FROM BOOKS
GROUP BY AUTHOR;

Result:

Then the HAVING clause can be applied to filter the results of the grouped data as shown below.

Query:

SELECT
    AUTHOR,
    COUNT(*) AS BOOK_COUNT,
    AVG(PRICE) AS AVG_PRICE
FROM BOOKS
GROUP BY AUTHOR
HAVING COUNT(*) > 1 AND AVG(PRICE) > 10;

Result:

To display only the Author information, retrieve the Author details from the previous query as shown below.

Query:

SELECT AUTHOR FROM (
    SELECT
        AUTHOR, COUNT(*) AS BOOK_COUNT, AVG(PRICE) AS AVG_PRICE
    FROM BOOKS
    GROUP BY AUTHOR
    HAVING COUNT(*) > 1 AND AVG(PRICE) > 10
);

Result:

9. How to update records in a table based on the values from another table using a SQL query?

Consider below EMPLOYEES and DEPARTMENTS tables as the source data. Both tables can be linked using the DEPARTMENT_ID fields in each of the tables.

EMPLOYEES:

DEPARTMENTS:

Scenario-1: Update Salary of all employees by 5% belonging to department id 20

This is straightforward forward and the data in employees can be updated using UPDATE statement as shown below.

UPDATE EMPLOYEES SET SALARY = SALARY*1.05 WHERE DEPARTMENT_ID=20;

Scenario-2: Update Salary of all employees by 5% belonging to IT department

In this scenario, we do not have information of department name in the employees table. We need to join EMPLOYEES table with DEPARTMENTS table using DEPARTMENT_ID field to get information of employees belonging to IT department.

Method-1: Using Sub-Queries

The below query updates Salary of all employees by 5% belonging to IT department employing a subquery for the department identification.

UPDATE EMPLOYEES
SET SALARY = SALARY*1.05
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = 'IT')
;

Method-2: Using UPDATE…FROM

The UPDATE…FROM statement allows you to update values in a table based on data from another table, similar to how you would use a join in a SELECT statement.

The below query updates Salary of all employees by 5% belonging to IT department using UPDATE…FROM syntax.

UPDATE EMPLOYEES e
SET SALARY = e.SALARY * 1.05
FROM DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
AND d.DEPARTMENT_NAME = 'IT';

The UPDATE…FROM syntax often leads to more concise and readable code compared to using subqueries. The syntax also allows for a more straightforward representation of complex update scenarios involving multiple tables and conditions.

10. How to delete records in a table based on the values from another table using a SQL query?

Consider below EMPLOYEES and DEPARTMENTS tables as the source data. Both tables can be linked using the DEPARTMENT_ID fields in each of the tables.

EMPLOYEES:

DEPARTMENTS:

Scenario-1: Delete records of all employees by belonging to department id 20

This is straight forward and the data in employees can be deleted using DELETE statement as shown below.

DELETE FROM EMPLOYEES WHERE DEPARTMENT_ID=20;

Scenario-2: Delete records of all employees belonging to IT department

In this scenario, we do not have information of department name in the employees table. We need to join EMPLOYEES table with DEPARTMENTS table using DEPARTMENT_ID field to get information of employees belonging to IT department.

Method-1: Using Sub-Queries

The below query deletes records of all employees belonging to IT department employing a subquery for the department identification.

DELETE FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = 'IT');

Method-2: Using DELETE…USING

The DELETE…USING statement allows you to update values in a table based on data from another table, similar to how you would use a join in a SELECT statement.

The below query deletes records of all employees belonging to IT department using DELETE…USING syntax.

DELETE FROM EMPLOYEES e
USING DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
AND d.DEPARTMENT_NAME = 'IT';

The DELETE…USING syntax is more commonly associated with databases like Snowflake, PostgreSQL, where it allows you to specify additional tables to be used in the DELETE operation. This can be useful when you need to delete records from one table based on conditions involving another table(s).

It’s important to note that the exact syntax and features might not be supported in some databases.

Subscribe to our Newsletter !!

Related Articles: