SQL Scenario Based Interview Questions

Last updated on March 8th, 2025 at 01:15 pm

Here, we will discuss SQL Scenario Based Interview Questions, which are asked by interviewers in most company interviews for any job position.

1. What is MySQL?

MySQL is an open-source relational database management system (RDBMS) that uses SQL (Structured Query Language) to manage data. It’s known for its speed, reliability, and ease of use, powering applications.

2. Top MySQL Interview Topics

Storage engines (InnoDB vs MyISAM), indexes (B-Tree, covering), ACID properties, normalization (1NF-3NF), JOIN types, transactions/isolation levels, SQL optimization (EXPLAIN, indexing), replication, partitioning, stored procedures, triggers, deadlocks, constraints (PK, FK), SQL injection prevention, TRUNCATE vs DELETE, CHAR vs VARCHAR, GROUP BY/HAVING, query tuning, NULL handling, and data types.

1. Explain the Order of Execution of the SQL query

The order of execution for an SQL query is as follows:

FROM: Identify the tables involved.
WHERE: Filter rows based on conditions.
GROUP BY: Aggregate data based on specified columns.
HAVING: Filter aggregated data.
SELECT: Choose which columns to display.
DISTINCT: Remove duplicate rows.
ORDER BY: Sort the result set.
LIMIT/OFFSET: Restrict the number of rows returned.

2. Explain functions Rank, Dense_Rank & Row_Number

RANK: Useful for assigning ranks to students based on scores, where ties receive the same rank but the next rank skips accordingly (e.g., 1, 1, 3).
DENSE_RANK: Similar to RANK but does not skip ranks in case of ties (e.g., 1, 1, 2).
ROW_NUMBER: Assigns a unique sequential integer to rows, regardless of ties. Useful for pagination in displaying search results.

3. Write a query to find the cumulative sum of the amount ordered by transaction date

SELECT 
    transaction_date, 
    amount, 
    SUM(amount) OVER (ORDER BY transaction_date) AS running_total
FROM 
    transactions;

4. Write a query to find the Most selling product by sales.

--Most Selling Product by sales
SELECT 
    product_id, 
    SUM(sales_amount) AS total_sales
FROM 
    sales
GROUP BY 
    product_id
ORDER BY 
    total_sales DESC
LIMIT 1;

5. Write a query to find the 2nd/nth highest Salary of employees

SELECT DISTINCT salary 
FROM employees 
ORDER BY salary DESC 
OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;

6. Difference between UNION vs UNION ALL

UNION: Combines the result sets of two or more SELECT statements and removes duplicates.
UNION ALL: Combines the result sets without removing duplicates, thus potentially returning more rows.

7. Write a query to identify if there are any duplicates in a table or not

SELECT 
    column_name, 
    COUNT(*) 
FROM 
    table_name 
GROUP BY 
    column_name 
HAVING 
    COUNT(*) > 1;

8. Write a simple SQL query for Inner, Left, and Outer Joins

-- Inner Join
SELECT e.name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.id;

-- Left Join
SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.id;

-- Right Join
SELECT e.name, d.department_name 
FROM employees e 
RIGHT JOIN departments d ON e.department_id = d.id;

9. Write a query to find all those records where the transaction value is greater than the previous transaction value

SELECT 
    transaction_id, 
    transaction_value 
FROM 
    (SELECT 
         transaction_id, 
         transaction_value, 
         LAG(transaction_value) OVER (ORDER BY transaction_date) AS prev_value 
     FROM transactions) AS subquery 
WHERE 
    transaction_value > prev_value;

10. Write a query to find the 2nd highest Salary of the employee using the window function

SELECT salary 
FROM (
    SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rank 
    FROM employees
) AS ranked_salaries 
WHERE rank = 2;

11. Write a query to find the difference amount from the previous transaction date (using Windows function)

SELECT 
    transaction_date, 
    amount, 
    amount - LAG(amount) OVER (ORDER BY transaction_date) AS running_difference
FROM 
    transactions;

12. Write a query to display year-on-year/month-on-month growth

SELECT 
    EXTRACT(YEAR FROM transaction_date) AS year, 
    SUM(amount) AS total_amount, 
    SUM(amount) - LAG(SUM(amount)) OVER (ORDER BY EXTRACT(YEAR FROM transaction_date)) AS growth 
FROM 
    transactions 
GROUP BY 
    year;

13. Write a query to find the rolling average of daily sign-ups

SELECT 
    signup_date, 
    COUNT(user_id) AS daily_signups, 
    AVG(COUNT(user_id)) OVER (ORDER BY signup_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_average 
FROM 
    signups 
GROUP BY 
    signup_date;

14. Write a query to find the difference in amount than the previous date using self-join

SELECT 
    a.transaction_date, 
    a.amount - b.amount AS running_difference 
FROM 
    transactions a 
LEFT JOIN 
    transactions b ON a.transaction_date = DATEADD(DAY, 1, b.transaction_date);

15. Write a query to find the cumulative sum of the amount using self-join

SELECT 
    a.transaction_date, 
    SUM(b.amount) AS cumulative_sum 
FROM 
    transactions a 
JOIN 
    transactions b ON b.transaction_date <= a.transaction_date 
GROUP BY 
    a.transaction_date;

16. Write a query to find the highest Salary of employees

-- Highest Salary
SELECT 
    employee_id, 
    MAX(salary) AS highest_salary
FROM 
    employees;

SQL Scenario Based Interview Questions

Table of Contents

1. What is MySQL?

2. Top MySQL Interview Topics