SQL Interview Questions and Answers

Are you preparing to become a data analyst, backend developer, or database administrator? You should start by preparing with the most asked SQL interview questions and answers. Interviewers will assess your knowledge of SQL regardless of how long you have worked in these areas because it is a common and must-have skill among them.

This guide provides a thorough overview of the possible SQL interview questions, ranging from basic scripted queries to advanced scenario-based interviews based on previous experience. These will help you to demonstrate practical SQL skills, like writing optimized queries, designing efficient database structures, and solving real-world data problems.

Whether you're a fresher preparing for your first technical interview or an experienced professional aiming for a senior role, understanding both SQL concepts and hands-on applications can significantly improve your confidence and performance during the interview process. Let’s start!

Related Article: SQL Cheat Sheet: Basic to Advanced

SQL Interview Questions for Freshers

These are the questions you will most likely face in your first or second interview. Focus on understanding the concept behind each answer, not just memorizing it.

1. What is SQL?

SQL stands for Structured Query Language. It is the standard language for managing and manipulating relational databases. You use SQL to create, read, update, and delete data stored in tables. Almost every major company uses SQL in some form.

2. Explain the different types of SQL commands.

SQL commands fall into five categories:

• DDL (Data Definition Language): Commands like CREATE, ALTER, DROP, and TRUNCATE. These define or change the database structure.
• DML (Data Manipulation Language): Commands like SELECT, INSERT, UPDATE, and DELETE. These work with actual data.
• DCL (Data Control Language): Commands like GRANT and REVOKE. These manage permissions.
• TCL (Transaction Control Language): Commands like COMMIT, ROLLBACK, and SAVEPOINT. These manage transactions.
• DQL (Data Query Language): Some categorize SELECT separately under DQL.

3. What is the difference between DELETE, TRUNCATE, and DROP?

This question comes up in almost every SQL fresher interview.

• DELETE removes specific rows. You can use a WHERE clause. It is a DML command and supports ROLLBACK.
• TRUNCATE removes all rows from a table at once. It is faster than DELETE but does not support WHERE. It is a DDL command.
• DROP removes the entire table, including its structure. You cannot recover it easily after dropping it.

4. What is a Primary Key?

A primary key is a column (or a set of columns) that uniquely identifies each row in a table. It does not allow NULL values. Each table can have only one primary key.

Read Also: What is a RDBMS (Relational Database Management System)?

5. What is a Foreign Key?

A foreign key is a unique column (or collection of columns) in one database table that links to the primary key of another table. In this way, it creates a relationship between the two tables - cross-referencing them together. As such, it is important for ensuring both accuracy and consistency across both sets of data.

6. Differentiate between WHERE and HAVING?

WHERE filters rows before grouping. You use it with SELECT, UPDATE, or DELETE.

HAVING filters groups after the GROUP BY clause has been applied. You can use aggregate functions like SUM, COUNT, and AVG in HAVING but not in WHERE.

Example:

-- WHERE filters individual rows
SELECT department, COUNT(*) 
FROM employees 
WHERE salary > 50000 
GROUP BY department;

-- HAVING filters groups
SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;

7. What are JOINs in SQL? Name the types.

JOIN combines rows from two or more tables based on a related column. The main types are:

• INNER JOIN: It eturns rows that have matching values in both tables.
• LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table. Non-matching rows from the right show as NULL.
• RIGHT JOIN (RIGHT OUTER JOIN): It returns all rows from the right table and matching rows from the left.
• FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in either table.
• CROSS JOIN: Returns the Cartesian product of both tables.
• SELF JOIN: Joins a table with itself.

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

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

UNION ALL combines results and keeps all duplicates. It is faster than the UNION because it skips the deduplication step.

9. What is a NULL value in SQL?

NULL represents a missing or unknown value. It is not zero and it is not an empty string. You cannot compare NULL using = or !=. You must use IS NULL or IS NOT NULL.

10. What is normalization?

Normalization is the process of organizing a database to reduce redundancy and improve data integrity. This breaks large tables into smaller related tables. The common normal forms are 1NF, 2NF, 3NF, and BCNF.

Also Read: MongoDB vs MySQL: Understanding Key Differences

11. What is the difference between a clustered and a non-clustered index?

A clustered index sorts and stores the data rows physically in the table based on the index key. A table can have only one clustered index.

A non-clustered index stores the index separately from the data. A table can have multiple non-clustered indexes.

12. What is an alias in SQL?

An alias gives a temporary name to a table or a column in a query. You use the AS keyword.

SELECT first_name AS name, salary AS monthly_pay 
FROM employees;

SQL Interview Questions for Intermediate Professionals

At this level, interviewers expect you to write queries, not just define terms. They will give you a table and ask you to solve a problem.

1. What is a subquery? How is it different from a JOIN?

A subquery is a query nested inside another query. It runs first, and the outer query uses its result.

SELECT name 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

A JOIN combines tables horizontally. A subquery often filters based on a value derived from another query. Both can solve similar problems, but JOINs are usually faster for large datasets.

2. What are aggregate functions in SQL?

Aggregate functions perform calculations on multiple rows and return a single value. Common ones include:

• COUNT() - counts rows
• SUM() - adds values
• AVG() - calculates average
• MIN() and MAX() - find the smallest and largest values

3. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

These are window functions. They all assign a number to rows within a partition but behave differently when there are ties.

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

• ROW_NUMBER() gives a unique number to every row, even if salaries are the same.
• RANK() gives the same number to tied rows but skips the next rank. (1, 1, 3)
• DENSE_RANK() gives the same number to tied rows but does not skip. (1, 1, 2)

Also Read: What is PL/SQL?

4. What is a CTE (Common Table Expression)?

A CTE is a temporary, named result set you define at the beginning of a query using the WITH keyword. It makes complex queries easier to read and reuse.

WITH high_earners AS (
  SELECT name, salary 
  FROM employees 
  WHERE salary > 80000
)
SELECT * FROM high_earners WHERE department = 'Engineering';

5. How do you find duplicate records in a table?

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

6. How do you find the second highest salary?

-- Using LIMIT and OFFSET
SELECT DISTINCT salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 1 OFFSET 1;

-- Using subquery
SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

7. What is indexing and when should you use it?

An index speeds up data retrieval. You should add an index on columns you frequently use in WHERE, JOIN, or ORDER BY clauses.

However, indexes slow down INSERT, UPDATE, and DELETE operations because the index has to update too. Do not over-index.

Related Article: How to Delete Duplicate Rows in SQL?

8. What is a VIEW in SQL?

A VIEW is a virtual table based on the result of a SELECT query. It does not store data itself. You use views to simplify complex queries, restrict data access, and add a security layer.

CREATE VIEW active_employees AS 
SELECT name, department, salary 
FROM employees 
WHERE status = 'active';

9. What is a stored procedure?

The stored procedure is a precompiled set of SQL statements stored in the database. You can call it whenever you need it. It reduces network traffic, improves performance, and allows code reuse.

CREATE PROCEDURE GetEmployeeByDept(@dept VARCHAR(50))
AS
BEGIN
  SELECT * FROM employees WHERE department = @dept;
END;

10. What is a trigger?

A trigger is a SQL procedure that runs automatically when an event like INSERT, UPDATE, or DELETE happens on a table. You use triggers for logging changes, enforcing business rules, or maintaining audit trails.

11. What is the difference between INNER JOIN and LEFT JOIN?

-- INNER JOIN: only matching rows from both tables
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN: all rows from employees, NULL where no match in departments
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

12. How does GROUP BY work with NULL values?

SQL treats NULL as a separate group. If multiple rows have NULL in the grouped column, they all go into the same NULL group.

Also Read: MongoDB Tutorial

13. What is the COALESCE function?

COALESCE returns the first non-NULL value in a list of arguments. You use it to replace NULL with a default value.

SELECT name, COALESCE(phone, 'No Phone') AS contact 
FROM users;

SQL Interview Questions for Experienced Professionals

At the senior level, you are expected to design, optimize, and troubleshoot. Interviewers focus on performance, architecture, and real-world judgment.

1. What is query optimization and how do you approach it?

Query optimization is the process of improving the performance of a SQL query. Your approach should include:

• Checking the execution plan using EXPLAIN or EXPLAIN ANALYZE
• Adding or modifying indexes on frequently queried columns
• Avoiding SELECT * and fetching only needed columns
• Replacing correlated subqueries with JOINs where possible
• Using partitioning for large tables
• Avoiding functions on indexed columns in WHERE clauses

2. What is database partitioning?

Partitioning splits a large table into smaller and more manageable pieces. Each piece is called a partition. The database still treats it as one table but can scan only the relevant partition, which improves performance significantly.

Types include:

• Range partitioning: By date range or number range
• List partitioning: By specific values
• Hash partitioning: By a hash function

3. What is the N+1 query problem?

The N+1 problem happens when you run 1 query to fetch a list of records and then run N more queries to fetch related data for each record. It is a major performance killer in applications. You solve it by using JOINs or eager loading.

Related Article: What is PostgreSQL and What It Is Used For?

4. What is ACID in databases?

ACID stands for:

• Atomicity: They either the entire transaction completes or none of it does.
• Consistency: The database moves from one valid state to another.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Once a transaction commits, the changes persist even after a crash.

5. What is a deadlock and how do you handle it?

A deadlock happens when two or more transactions block each other, each waiting for the other to release a lock. The database usually detects this and kills one of the transactions.

You prevent deadlocks by:

• Accessing tables in the same order across all transactions
• Keeping transactions short
• Using lower isolation levels where appropriate
• Using SELECT ... FOR UPDATE carefully

6. What is the difference between OLTP and OLAP?

• OLTP (Online Transaction Processing): Designed for high-speed insert, update, and delete operations. Used in banking, e-commerce, and applications. Normalized schema.
• OLAP (Online Analytical Processing): Designed for complex queries over large datasets. Used in reporting and business intelligence. Often uses a denormalized schema or star schema.

7. What is a materialized view?

Materialized view stores the result of a query physically, unlike a regular view which is just a saved query. You can refresh it periodically. It improves read performance for complex aggregation queries but takes up storage space.

8. What are window functions and when do you use them?

A window functions perform calculations across a set of rows related to the current row without collapsing them into one row like aggregate functions do.

SELECT name, salary, department,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary,
  salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;

You use window functions for running totals, moving averages, rankings, and comparing rows within a group.

Read Also: What is MongoDB?

9. What is the difference between horizontal and vertical scaling for databases?

• Vertical scaling (Scale Up): You add more CPU, RAM, or storage to the same machine. It has limits and creates a single point of failure.
• Horizontal scaling (Scale Out): You add more machines. This suits read-heavy workloads through replication or sharding.

10. How do you handle schema migrations in production?

You handle schema migrations carefully in production:

• Always run migrations in a maintenance window or use zero-downtime migration strategies
• Add columns as nullable first, then populate them, then add constraints
• Never DROP a column directly; mark it as deprecated first
• Use migration tools like Flyway, Liquibase, or Alembic
• Test migrations in staging before production

11. What is sharding?

Sharding is a database scaling technique that distributes data across multiple servers. Each server stores only a specific portion of the overall dataset. You use sharding when a single database server cannot handle the data volume or traffic. It adds complexity to queries and transactions that span multiple shards.

12. What is a covering index?

Covering index includes all the columns a query needs. The database can answer the query entirely from the index without touching the actual table, which makes reads very fast.

-- This query is covered by an index on (department, salary, name)
SELECT name, salary 
FROM employees 
WHERE department = 'Engineering';

Scenario-Based SQL Interview Questions

These questions test how you think and solve real-world problems. The interviewer wants to see your process, not just the answer.

1. You are in charge of an online retail business. Your supervisor has requested you prepare a report on the 3 best-selling items by category. What technique will you employ in SQL to achieve this task?

Utilizing a window function DENSE_RANK() combined with PARTITION BY to partition by category and sorting sales in descending order would allow each product to receive a rank. I would then filter to only those records containing ranks of 3 or less. This procedure is very efficient, resolves tie situations appropriately and is a conventional method used in reporting and analytical situations.

2. Your business entity wishes to identify products that possess greater total sales than the average sales of all products. How are you going to accomplish this in SQL?

I will develop a CTE to derive total sales for each product. After this, I'll calculate the average sales from that CTE, and compare individual product total sales against the computed average. Any product with a total sale exceeding the average total sale will be returned. Utilising a CTE will promote readability of the SQL statement making it more manageable and repairable.

3. If a query to a customer table of 10M records takes 30 seconds to return results, what steps would you take to increase performance?

The first thing I will do is analyze the execution plan to find the bottlenecks. If there is a high frequency of queries searching for records based upon the email address (or whatever specific field), I will create an index on that field. Indexing will help reduce the number of full table scans and make lookups much quicker. I will also review query design, statistics, and database configuration to find ways to optimize the performance of the query.

Also Read: What is Microsoft SQL Server?

4. Your HR team needs a report showing all employees that report to a specific manager either directly or indirectly. What would you do to get that data?

I would create a recursive CTE to accomplish this task. Start with the employees that report directly to the manager, then recursively retrieve all employees that report to those employees. This will continue until all levels of the hierarchy have been returned. Recursive CTE's are well suited for organizational structures and other hierarchies.

5. Customer preferences are stored in a JSON column. The business wants a list of customers who have selected the "Dark" theme. How would you handle this?

I would use JSON extraction functions provided by the database. For example, in PostgreSQL, the ->> operator can extract the theme value from the JSON object. I would filter records where the theme equals "Dark." This allows efficient querying of semi-structured data without requiring separate columns for every preference.

Wrapping Up

SQL remains one of the most in-demand skills in tech. Whether you are a fresher trying to land your first job or a senior engineer preparing for a staff-level interview, these SQL interview questions will help you walk in with confidence.

The best way to prepare is to write actual SQL. Use a free tool like DB Fiddle, SQLiteOnline, or install PostgreSQL locally. Practice every query in this guide with real data.

FAQs

1. What SQL topics do freshers get asked about most?

Freshers mostly face questions on DDL vs DML commands, primary and foreign keys, JOINs, WHERE vs HAVING, DELETE vs TRUNCATE, and basic SELECT queries. Master these first.

2. Which SQL database should I learn for interviews?

MySQL and PostgreSQL are the most common in interviews. The core SQL syntax is the same across databases. Learn either one and you can adapt quickly to the other.

3. How important are window functions in SQL interviews?

Window functions like RANK(), DENSE_RANK(), ROW_NUMBER(), and SUM() OVER() are very important for intermediate and senior roles. Many companies include at least one window function question in their SQL rounds.

4. Is SQL enough to get a data analyst job?

SQL is the most essential skill for a data analyst, but companies also look for Excel, Python (pandas), and a basic understanding of statistics. SQL alone can get you into junior roles, but knowing Python makes you much more competitive.

5. What is the difference between SQL and NoSQL?

SQL databases are relational. They store data in structured tables with fixed schemas. NoSQL databases (like MongoDB, Cassandra) store data in documents, key-value pairs, or graphs. They handle unstructured data and scale horizontally more easily. SQL is suitable for structured data with complex relationships.