Top 50 SQL Interview Questions and Answers for Freshers (2026)

Difficulty: Medium

A standard view is a named query that behaves like a virtual table.

It generally does not store query results physically. The underlying query runs whenever the view is referenced.

CREATE VIEW HighSalaryEmployees AS
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > 80000;

A materialized view is different because it stores query results and must be refreshed. Its availability and syntax vary between database systems.

Difficulty: Medium
An index is a database structure that helps the database locate rows efficiently.

Indexes can improve data retrieval speed, but they also:

• Consume additional storage
• Add overhead to INSERT
• Add overhead to UPDATE
• Add overhead to DELETE

Indexes are commonly considered for columns used in:

• WHERE conditions
• Join conditions
• Sorting
• Grouping

An index is not automatically beneficial for every column. Its usefulness depends on query patterns, selectivity, and the database optimizer.

Difficulty: Medium

Normalization organizes relational data to reduce unnecessary duplication and prevent update, insert, and delete anomalies.

Common normal forms include:

• First Normal Form: Values are atomic and rows can be uniquely identified.
• Second Normal Form: The table is in 1NF and non-key attributes depend on the complete key.
• Third Normal Form: The table is in 2NF and non-key attributes have no transitive dependency on the key.
• BCNF: Every determinant is a candidate key.

Difficulty: Medium

Denormalization intentionally introduces duplication or combines data to reduce joins and improve certain read-heavy workloads.

It may improve reporting performance, but it can also increase:

• Storage requirements
• Data duplication
• Risk of inconsistent data
• Update complexity

Denormalization should be used after analysing and measuring the workload.

Difficulty: Medium

A stored procedure is a named collection of database statements stored and executed inside the database.

Stored procedures may:

• Accept parameters
• Apply business logic
• Insert or update data
• Return results
• Control transactions

The syntax and capabilities of stored procedures differ between database systems.

Difficulty: Medium

A trigger is database code that runs automatically when a specified event occurs.

Common trigger events include:

• INSERT
• UPDATE
• DELETE

Triggers can be used for auditing and enforcing business rules. However, excessive trigger logic can make a system difficult to understand and debug.

Difficulty: Medium

A common written SQL query order is:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT

A simplified logical processing order is:

1. FROM and JOIN
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT, TOP, or FETCH

The database optimizer may execute operations differently internally while maintaining the same result.

Difficulty: Medium
Group the records using the column that should be unique and select groups whose count is greater than one.

SELECT Email, COUNT(*) AS DuplicateCount
FROM Customers
WHERE Email IS NOT NULL
GROUP BY Email
HAVING COUNT(*) > 1;

Difficulty: Hard
First, define which duplicate record should be kept.

The following SQL Server example keeps the record with the smallest CustomerID for each email address.

WITH RankedCustomers AS (
    SELECT
        CustomerID,
        ROW_NUMBER() OVER (
            PARTITION BY Email
            ORDER BY CustomerID
        ) AS RowNum
    FROM Customers
)
DELETE FROM RankedCustomers
WHERE RowNum > 1;

The deletion syntax may vary by database system.

In an interview:

• Mention the database you are using.
• Run the ranking query as a SELECT first.
• Verify which records will be removed.
• Use a controlled transaction or backup before deleting production data.

Difficulty: Hard

DENSE_RANK() correctly handles duplicate salary values.

It can be used inside clauses such as SELECT, FROM, and WHERE.

WITH SalaryRanks AS (
    SELECT
        Salary,
        DENSE_RANK() OVER (
            ORDER BY Salary DESC
        ) AS SalaryRank
    FROM Employees
)
SELECT DISTINCT Salary
FROM SalaryRanks
WHERE SalaryRank = 2;

This query returns the second-highest distinct salary.

If the table contains only one distinct salary, the query returns no row.

Difficulty: Medium

A Common Table Expression, or CTE, is a named temporary result set that exists within the scope of a single statement.

It is created using the WITH clause and can make complex queries easier to read.

WITH DepartmentSalary AS (
    SELECT
        DepartmentID,
        AVG(Salary) AS AverageSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT DepartmentID, AverageSalary
FROM DepartmentSalary
WHERE AverageSalary > 60000;

A CTE is not a permanent table and does not automatically improve performance.

Difficulty: Hard

Window functions calculate values across related rows without collapsing them into one row per group.

Common window functions include:

SELECT
    Name,
    DepartmentID,
    Salary,
    AVG(Salary) OVER (
        PARTITION BY DepartmentID
    ) AS DepartmentAverage
FROM Employees;

Unlike GROUP BY, this query keeps each employee row in the result.

Difficulty: Medium

RANK() leaves gaps after tied values, while DENSE_RANK() does not.

Difficulty: Medium

UNION combines compatible results from two or more SELECT statements and removes duplicate rows.

SELECT Email
FROM Customers

UNION

SELECT Email
FROM Employees;

The queries must return:

• The same number of columns
• Columns in the same order
• Compatible data types

Difficulty: Medium

Use UNION ALL when duplicates are valid or when the inputs are already known to be distinct.

Difficulty: Medium

EXISTS checks whether a subquery returns at least one row.

SELECT c.CustomerID, c.CustomerName
FROM Customers AS c
WHERE EXISTS (
    SELECT 1
    FROM Orders AS o
    WHERE o.CustomerID = c.CustomerID
);

This query returns customers who have placed at least one order.

Difficulty: Medium

CASE adds conditional logic to an SQL expression.

SELECT
    Name,
    Salary,
    CASE
        WHEN Salary >= 80000 THEN ‘High’
        WHEN Salary >= 50000 THEN ‘Medium’
        ELSE ‘Entry’
    END AS SalaryBand
FROM Employees;

Difficulty: Medium

COALESCE() returns the first non-NULL expression from a list.

SELECT
    CustomerName,
    COALESCE(
        PhoneNumber,
        AlternatePhone,
        ‘Not Available’
    ) AS ContactNumber
FROM Customers;

The expressions should have compatible data types.

Difficulty: Medium

Country codes may suit CHAR, while names and email addresses usually suit VARCHAR.

Exact storage and padding behaviour can vary by database system.

Difficulty: Medium
ACID describes four properties of reliable database transactions.

• Atomicity: All transaction operations succeed, or all are rolled back.
• Consistency: The database moves from one valid state to another.
• Isolation: Concurrent transactions follow the selected isolation guarantees.
• Durability: Committed changes survive failures according to the database’s durability guarantees.

Difficulty: easy
Transaction Control Language, or TCL, manages database transactions.

Common TCL commands include:

• COMMIT – Makes transaction changes permanent
• ROLLBACK – Undoes uncommitted changes
• SAVEPOINT – Creates a point for a partial rollback

Transaction behaviour depends on the database system, storage engine, and autocommit settings.

Difficulty: easy
Data Control Language, or DCL, is commonly used to describe commands that manage database permissions.

Common DCL commands include:

• GRANT
• REVOKE

GRANT SELECT
ON Employees
TO analyst_user;

The exact privilege syntax differs between database systems.

Difficulty: easy
Data Definition Language, or DDL, defines or changes database objects.

Common DDL commands include:

• CREATE
• ALTER
• DROP
• TRUNCATE

Whether a DDL statement commits automatically or can be rolled back depends on the database system.

Difficulty: easy
Data Manipulation Language, or DML, is used to access or modify data in existing database objects.

Common DML commands include:

• INSERT
• UPDATE
• DELETE
• MERGE

Classification can differ between learning resources.

Many tutorials classify SELECT as Data Query Language, or DQL. Oracle documentation classifies it as a limited form of DML because it accesses data.

In an interview, explain which convention you are using instead of treating one classification as universal.

Difficulty: Hard
Start by measuring the query and reviewing its execution plan.

Possible SQL query optimization methods include:

• Retrieve only required columns instead of using SELECT *.
• Filter unnecessary rows.
• Create appropriate indexes for frequently filtered or joined columns.
• Avoid functions that prevent efficient index usage.
• Write accurate and efficient join conditions.
• Remove unnecessary DISTINCT operations.
• Avoid unnecessary sorting.
• Avoid repeating the same subqueries.
• Use pagination when the complete result is not required.
• Keep database statistics updated.
• Review data types and implicit conversions.
• Consider partitioning or controlled denormalization when justified.

SQL optimization is workload-specific. An index that improves data retrieval may also slow down insert and update operations.

Therefore, always test performance changes using realistic data and execution plans.