SQL Interview Questions

SQL (Structured Query Language) is the standard language used with relational DBMSs to define schemas (DDL), manipulate and query data (DML/DQL) and optimize access so it’s a core skill for developers, data analysts and DBAs and a frequent interview focus. SQL covers:

• Data definition (defining schemas/structures)
• Data manipulation (inserting, updating, deleting data)
• Querying (retrieving data as needed)
• Optimization (tuning access/performance)
• Together, these enable efficient storage, retrieval and management of data in relational database systems.

1. What is the difference between CHAR and VARCHAR2?

2. What is a view in SQL?

A view is a virtual table created from a SELECT query that displays data from one or more tables without storing it, helping simplify queries and improve security.

3. What is the purpose of the UNIQUE constraint?

The UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct. This prevents duplicate values and helps maintain data integrity.

4. What is a query in SQL?

A query is a SQL statement used to retrieve, update or manipulate data in a database. The most common type of query is a SELECT statement, which fetches data from one or more tables based on specified conditions.

5. What is a subquery?

A subquery is a query nested within another query. It is often used in the WHERE clause to filter data based on the results of another query, making it easier to handle complex conditions.

6. What is a composite primary key?

A composite primary key uses two or more columns together to uniquely identify each row when one column alone isn’t sufficient.

7. Explain the difference between the WHERE and HAVING clauses

• WHERE filters individual rows before grouping or aggregation, so it can’t use aggregate functions like SUM or COUNT; it’s best for narrowing raw data early (e.g., a date range or status).
• HAVING filters the resulting groups after GROUP BY, so it’s meant for conditions on aggregates (e.g., groups with totals above a threshold).

Example:

SELECT customer_id, COUNT(*) AS orders_2025
FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'      
GROUP BY customer_id
HAVING COUNT(*) > 5;                                            

8. What are SQL joins and what are the differences between INNER, LEFT, RIGHT and FULL joins?

SQL joins combine rows from two tables based on a matching condition (typically keys) to answer questions that span both tables.

• An INNER JOIN returns only matches that exist in both tables (the intersection).
• A LEFT JOIN returns all rows from the left table and the matching rows from the right; when there’s no match, right-side columns are NULL.
• A RIGHT JOIN is the mirror image: all rows from the right table plus matches from the left, NULL when absent.
• A FULL (OUTER) JOIN returns all rows from either table, filling in NULL where a counterpart is missing.

9. Describe a PRIMARY KEY and how it differs from a UNIQUE key

• A PRIMARY KEY uniquely identifies each row in a table: it combines UNIQUE + NOT NULL, there can be only one per table (though it can be composite across multiple columns) and it’s the default target for foreign keys.
• A UNIQUE key also enforces uniqueness, but doesn’t require NOT NULL and you can have many UNIQUE constraints per table.

10. What is a CTE (Common Table Expression) and when would you use it?

A CTE (Common Table Expression) is a temporary named result set created using the WITH clause that exists only during the execution of a single SQL statement. It is used to simplify complex queries, improve readability, avoid repeating subqueries and write recursive queries.

11. Explain normalization and briefly describe the different normal forms

Normalization organizes relational data to minimize redundancy and prevent update/insert/delete anomalies by splitting tables based on dependencies while preserving meaning.

• 1NF: Each column contains atomic values, and there are no repeating groups.
• 2NF: Meets 1NF and removes partial dependencies on a composite primary key.
• 3NF: Meets 2NF and removes transitive dependencies.
• BCNF: Every determinant must be a candidate key.
• 4NF: Removes multi-valued dependencies.
• 5NF (PJNF): Removes join dependencies to avoid data redundancy.

12. What is the difference between UNION and UNION ALL?

13. How do clustered and non‑clustered indexes differ ?

14. How do you perform pattern matching in SQL ?

Pattern matching in SQL is performed using the LIKE operator with wildcard characters.

• % matches zero or more characters.
• _ matches exactly one character.

15. How would you calculate the running total of sales for each product?

A running total is calculated using the SUM() window function with the OVER() clause. It adds each row's value to the cumulative total while keeping individual rows.

Example:

SELECT
    product_id,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
    ) AS running_total
FROM Sales;

This query calculates the cumulative sales total for each product in chronological order.

16. Explain correlated subqueries and provide an example use case

A correlated subquery is a subquery that references columns from the outer query. It is executed once for each row processed by the outer query.

Example: Find employees whose salary is greater than the average salary of their department.

SELECT e.employee_id, e.name, e.salary, e.department_id
FROM Employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM Employees e2
    WHERE e2.department_id = e.department_id
);

Output: Returns employees whose salary is higher than the average salary of their department.

17. What are EXISTS and NOT EXISTS and how do they differ from IN?

• EXISTS returns TRUE if the subquery returns at least one row.
• NOT EXISTS returns TRUE if the subquery returns no rows.
• IN checks whether a value exists in a list or the result of a subquery.

Difference:

• EXISTS and NOT EXISTS stop searching as soon as a matching row is found and work well with correlated subqueries.
• IN compares a value against all values returned by the subquery and is best suited for small result sets.

Example:

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

Output: Returns customers who have placed at least one order.

18. Explain anti-joins.

An anti-join returns rows from one table that do not have matching rows in another table. It is commonly implemented using NOT EXISTS or a LEFT JOIN with IS NULL.

Example: Find customers who have not placed any orders.

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

Output: Returns customers who have not placed any orders.

19. Explain the difference between RANK(), DENSE_RANK() and ROW_NUMBER()

20. Explain the purpose of LAG and LEAD functions

LAG and LEAD are window functions used to access values from the previous or next row without using a self-join.

• LAG() returns the value from the previous row.
• LEAD() returns the value from the next row.

They are commonly used to compare consecutive rows, calculate differences and analyze trends.

21. What is the difference between CROSS JOIN and INNER JOIN?

22. Explain foreign keys and how they enforce referential integrity

A foreign key is a column (or set of columns) in one table that references the primary key of another table. It enforces referential integrity by ensuring that values in the child table exist in the parent table, preventing invalid or orphan records.

23. Describe set operations like UNION, INTERSECT and EXCEPT and when each is useful

24. How would you optimize a slow query?

To optimize a slow query,

• Use EXPLAIN to find slow parts
• Add proper indexes and update statistics
• Use efficient conditions (avoid functions on columns)
• Filter data early and avoid SELECT *
• Optimize joins and reduce extra data
• Rewrite queries if needed (JOIN, UNION ALL)
• Use pagination, caching or partitioning for large data

25. Explain database partitioning.

Database partitioning is the process of dividing a large table into smaller, manageable partitions while treating it as a single logical table. It improves query performance, simplifies maintenance and enhances scalability.

Types:

• Horizontal Partitioning: Splits rows into different partitions.
• Vertical Partitioning: Splits columns into separate tables.

26. What strategies can protect a web application from SQL injection?

SQL injection can be prevented by following these best practices:

• Use parameterized queries (prepared statements).
• Validate and sanitize user input.
• Avoid dynamic SQL created through string concatenation.
• Use least-privilege database accounts.
• Use stored procedures securely.

27. What are the main types of SQL commands?

SQL commands are broadly classified into:

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE.
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.
• DCL (Data Control Language): GRANT, REVOKE.
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.

28. What is the purpose of the DEFAULT constraint?

The DEFAULT constraint assigns a default value to a column when no value is provided during an INSERT operation. This helps maintain consistent data and simplifies data entry.

29. What is denormalization and when is it used?