SQL (Structured Query Language) is a programming language used to store, manage, and retrieve data from databases. In many jobs today, especially in IT, business analysis, and software development, knowing SQL is very important because most companies store their important information in databases.
People who work with SQL need to understand how to write queries to fetch data, update records, create tables, use joins, handle stored procedures, and optimize databases for better performance. They also need basic knowledge of database design, indexing, transactions, and data security.
Preparing for SQL interview questions and answers is important because it helps candidates explain their skills clearly and handle both technical and real-world scenario questions during interviews. Good preparation shows you can work with real databases and solve actual problems in the job.
On this page, we are sharing a collection of popular SQL interview questions and answers for both freshers and experienced candidates. These cover basic concepts, advanced topics, and practical scenario-based questions to help you prepare better. We are also providing a PDF download, so you can study offline anytime.
Table of Contents
Basic SQL interview questions and Answers for Freshers
First, we’ll start with basic SQL interview questions and answers for freshers. These cover commonly asked topics like SQL concepts, writing queries, and working with data.
Que 1. What is SQL?
Answer:
SQL (Structured Query Language) is a standard language used to access, manage, and manipulate databases. It is used for querying data, inserting records, updating values, and managing database structures.
Que 2. What are the different types of SQL statements?
Answer:
Basic SQL interview questions and Answers often start with this. SQL statements are categorized into:
| SQL Statement Type | Description | Examples |
|---|---|---|
| DDL | Data Definition Language – defines structure | CREATE, ALTER, DROP |
| DML | Data Manipulation Language – manages data | SELECT, INSERT, UPDATE, DELETE |
| DCL | Data Control Language – manages permissions | GRANT, REVOKE |
| TCL | Transaction Control Language – controls transactions | COMMIT, ROLLBACK, SAVEPOINT |
Que 3. How do you retrieve all records from a table?
Answer:
SELECT * FROM table_name;Que 4. How do you retrieve only specific columns from a table?
Answer:
SELECT column1, column2 FROM table_name;Que 5. What is a primary key?
Answer:
A primary key uniquely identifies each row in a table. It cannot contain null values and must be unique.
Que 6. What is a foreign key?
Answer:
A foreign key links one table to another. It references the primary key in another table to enforce referential integrity.
Que 7. How do you insert data into a table?
Answer:
INSERT INTO table_name (column1, column2) VALUES (value1, value2);Que 8. How do you update a record in SQL?
Answer:
UPDATE table_name SET column1 = value1 WHERE condition;Que 9. How do you delete a record in SQL?
Answer:
DELETE FROM table_name WHERE condition;Que 10. What is the difference between WHERE and HAVING?
Answer:
| Clause | Used With | Purpose |
|---|---|---|
| WHERE | SELECT, UPDATE, DELETE | Filters rows before grouping |
| HAVING | GROUP BY | Filters after aggregation |
Que 11. What is the use of the GROUP BY clause?
Answer:
It groups rows with the same values into summary rows, often used with aggregate functions like SUM(), AVG(), COUNT().
SELECT department, COUNT(*) FROM employees GROUP BY department;Que 12. What is the purpose of the ORDER BY clause?
Answer:
ORDER BY is used to sort query results in ascending or descending order.
SELECT name FROM employees ORDER BY name ASC;Que 13. What is a CROSS JOIN and when would you use it?
Answer:
A CROSS JOIN returns the Cartesian product of two tables, all possible combinations of rows. It’s useful when you need to pair each row from one table with all rows from another.
Que 14. How do you use the LIKE operator in SQL?
Answer:
LIKE is used for pattern matching in Basic SQL interview questions and Answers.
SELECT * FROM customers WHERE name LIKE 'A%';Que 15. What are aggregate functions in SQL?
Answer:
COUNT()– number of rowsSUM()– total of a columnAVG()– average of a columnMAX()– highest valueMIN()– lowest value
Que 16. What is a subquery?
Answer:
A subquery is a query nested inside another SQL query.
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
Que 17. How do you eliminate duplicate records in a result set?
Answer:
SELECT DISTINCT column_name FROM table_name;
Que 18. What is the BETWEEN operator in SQL?
Answer:
It filters values within a specific range.
SELECT * FROM products WHERE price BETWEEN 100 AND 500;
Que 19. How do you rename a column in the result set?
Answer:
SELECT column_name AS alias_name FROM table_name;
Que 20. What is normalization in SQL?
Answer:
Normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves dividing a database into two or more tables and defining relationships between them.
Also Check: SQL Interview Questions and Answers for Freshers
Que 21. What is SQL and what are its main uses?
Answer:
SQL (Structured Query Language) is a standardized language used to manage and manipulate relational databases. It allows users to perform operations like querying, inserting, updating, and deleting data, as well as managing database structures (tables, indexes, views, etc.).
Main uses of SQL include:
- Data retrieval using
SELECTstatements - Data manipulation using
INSERT,UPDATE,DELETE - Schema definition using
CREATE,ALTER,DROP - Data access control using
GRANTandREVOKE
It’s the foundation for working with most relational database systems like MySQL, PostgreSQL, SQL Server, and Oracle.
Que 22. What is the difference between WHERE and HAVING clauses?
Answer:
| Feature | WHERE | HAVING |
|---|---|---|
| Used on | Individual rows | Groups of rows (with GROUP BY) |
| Condition | Filters rows before grouping | Filters after grouping |
| Aggregate | Cannot use aggregate functions | Can use aggregate functions |
Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;Que 23. What is a composite key in SQL?
Answer:
A composite key is a combination of two or more columns in a table that together uniquely identify a row. Unlike a single primary key, a composite key is used when no single column can uniquely identify a record.
Que 24. How do you retrieve unique records from a table?
Answer:
To fetch unique records, use the DISTINCT keyword with the SELECT statement.
Example:
SELECT DISTINCT department FROM employees;This returns each department only once, eliminating duplicates.
Que 25. What is a surrogate key in SQL?
Answer:
A surrogate key is an artificially generated identifier for a record, typically using an auto-incrementing integer. It’s used when no natural primary key exists or when it’s better not to expose business data as keys.
Que 26. What is the difference between INNER JOIN and LEFT JOIN?
Answer:
| Feature | INNER JOIN | LEFT JOIN |
|---|---|---|
| Matching rows | Returns only matching rows | Returns all left table rows, matched or not |
| Unmatched rows | Not included | Includes NULLs for unmatched right table rows |
Example:
SELECT *
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;This returns all orders, even if some customers don’t exist.
Que 27. What is normalization? Why is it important?
Answer:
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
Key benefits:
- Eliminates duplicate data
- Maintains data consistency
- Improves efficiency and scalability
Common forms of normalization include 1NF, 2NF, and 3NF, each with stricter rules.
Que 28. What is referential integrity in SQL?
Answer:
Referential integrity ensures that a foreign key value in one table corresponds to a primary key in another. It maintains the consistency and validity of relationships between tables.
Que 29. What is a NULL value in SQL?
Answer:NULL represents an unknown or missing value in SQL. It is not the same as zero or an empty string.
Important points:
NULLcan’t be compared using=; useIS NULLorIS NOT NULL.- Aggregates like
COUNTskipNULLunless specified.
Example:
SELECT * FROM employees WHERE manager_id IS NULL;Que 30. How do you use the GROUP BY clause?
Answer:GROUP BY groups rows with the same values in specified columns and allows you to perform aggregate functions on each group.
Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department;This shows the number of employees in each department.
Que 31. How do you use the COUNT DISTINCT function in SQL?
Answer:COUNT(DISTINCT column_name) counts unique non-null values in a column. It’s useful for identifying the number of unique entries, like distinct users or products.
Que 32. What is the difference between DELETE and TRUNCATE?
Answer:
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Operation | DML | DDL |
| Can filter rows | Yes (with WHERE clause) | No (removes all rows) |
| Rollback support | Yes | Usually not supported (depends on DB) |
| Triggers | Fires triggers | Doesn’t fire triggers |
Use DELETE for selective removal, and TRUNCATE for faster full deletion.
Que 33. How do you avoid duplicate entries in a table?
Answer:
To avoid duplicates:
- Use PRIMARY KEY or UNIQUE constraints
- Use
INSERT IGNOREorINSERT ... ON DUPLICATE KEY UPDATE(in MySQL) - Use
NOT EXISTSorNOT INin insert queries
Example:
INSERT INTO users (email)
SELECT 'test@example.com'
WHERE NOT EXISTS (
SELECT 1 FROM users WHERE email = 'test@example.com'
);Que 34. What are indexes and why are they used?
Answer:
An index is a data structure that improves the speed of data retrieval.
Advantages:
- Faster search, sorting, and filtering
- Optimized query performance
Types:
- Single-column
- Composite index
- Unique index
- Full-text index
However, indexes can slow down inserts and updates, so they should be used strategically.
Que 35. What is a subquery in SQL?
Answer:
A subquery is a query nested inside another query, typically used in SELECT, WHERE, or FROM clauses.
Example:
SELECT name
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'Sales'
);Subqueries help break down complex problems into manageable steps.
Que 36. What is the purpose of the CASE statement?
Answer:
The CASE statement allows conditional logic in SQL queries.
Example:
SELECT name,
CASE
WHEN salary > 50000 THEN 'High'
WHEN salary > 30000 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;It’s useful for deriving new columns or applying logic without multiple queries.
Que 37. What is a view in SQL?
Answer:
A view is a virtual table created using a SELECT query. It does not store data but displays results dynamically.
Use Cases:
- Simplify complex queries
- Abstract data for security
- Reuse common query logic
Example:
CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 70000;Que 38. How do you fetch the second highest salary from a table?
Answer:
Using subquery:
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);Other approaches include using LIMIT with OFFSET (MySQL) or ROWNUM/ROW_NUMBER() in Oracle/SQL Server.
Que 39. What are constraints in SQL?
Answer:
Constraints are rules enforced on columns to maintain data integrity.
Common constraints:
PRIMARY KEYFOREIGN KEYUNIQUENOT NULLCHECKDEFAULT
They prevent invalid or inconsistent data from being entered.
Que 40. What is the difference between CHAR and VARCHAR?
Answer:
| Feature | CHAR | VARCHAR |
|---|---|---|
| Storage | Fixed-length | Variable-length |
| Padding | Pads with spaces | Stores only actual characters |
| Performance | Slightly faster for fixed sizes | More efficient for varying sizes |
Use CHAR when the data size is uniform (like state codes), and VARCHAR when sizes vary (like names).
Also Check: Python Interview Questions and Answers
Advanced SQL interview questions and Answers for Experienced
These questions gradually increase in complexity, covering window functions, optimization, complex joins, subqueries, and advanced querying techniques.
41. What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?
Answer:
| Function | Behavior |
|---|---|
| RANK | Skips rank after duplicates |
| DENSE_RANK | Does not skip ranks |
| ROW_NUMBER | Unique sequence without tie logic |
Example:
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;42. What is a CTE and how is it different from a subquery?
Answer:
A CTE (Common Table Expression) improves readability and can be recursive. Unlike subqueries, CTEs can reference themselves.
WITH HighSalary AS (
SELECT * FROM employees WHERE salary > 50000
)
SELECT * FROM HighSalary;43. How do you find the nth highest salary using SQL?
Answer:
SELECT DISTINCT salary FROM employees e1
WHERE N - 1 = (
SELECT COUNT(DISTINCT salary) FROM employees e2
WHERE e2.salary > e1.salary
);44. What is a recursive CTE? Provide an example.
Answer:
A recursive CTE refers to itself to perform hierarchical operations.
WITH RECURSIVE emp_tree AS (
SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN emp_tree t ON e.manager_id = t.id
)
SELECT * FROM emp_tree;45. How do you remove duplicates while keeping the latest record?
Answer:
DELETE FROM orders o1
WHERE o1.id NOT IN (
SELECT MAX(o2.id) FROM orders o2 GROUP BY o2.order_number
);46. What is the purpose of the PARTITION BY clause in window functions?
Answer:PARTITION BY splits data into partitions (groups) for functions like RANK, SUM, AVG to work within each group.
SELECT department, employee,
RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees;
47. How do you calculate a moving average in SQL?
Answer:
SELECT date, sales,
AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM revenue;
48. What is the difference between INNER JOIN and FULL OUTER JOIN?
Answer:
| Join Type | Description |
|---|---|
| INNER JOIN | Only matching records |
| FULL OUTER JOIN | All records with NULL where unmatched |
49. How can you pivot data in SQL?
Answer (MySQL Example):
SELECT department,
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS Male,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS Female
FROM employees
GROUP BY department;
50. What is a correlated subquery?
Answer:
A correlated subquery refers to columns from the outer query and is evaluated once per row.
SELECT e1.name
FROM employees e1
WHERE salary > (
SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department
);
51. How can you identify and avoid SQL performance issues?
Answer:
- Use
EXPLAINto analyze queries - Add indexes on frequently filtered columns
- Avoid using
SELECT * - Minimize subqueries and use joins where better
- Consider denormalization for read-heavy loads
52. How do you implement pagination in SQL?
Answer:
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;
This retrieves page 3 of 10 items each.
53. How do you find gaps in a sequence of numbers?
Answer:
SELECT a.id + 1 AS missing
FROM numbers a
LEFT JOIN numbers b ON a.id + 1 = b.id
WHERE b.id IS NULL;
54. What is the use of EXISTS vs IN?
Answer:
| Feature | EXISTS | IN |
|---|---|---|
| Returns | Boolean (TRUE/FALSE) | Set of values |
| Performance | Faster for correlated subqueries | Better for small static lists |
55. How do you find employees with duplicate emails?
Answer:
SELECT email, COUNT(*)
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;
56. What is the difference between DELETE, TRUNCATE, and DROP?
Answer:
| Command | Removes Data | Rollback | Affects Structure |
|---|---|---|---|
| DELETE | Yes | Yes | No |
| TRUNCATE | Yes | No | No |
| DROP | Yes (all) | No | Yes |
57. What are materialized views and when to use them?
Answer:
A materialized view stores the result of a query physically and can be refreshed periodically. Useful for heavy aggregation and reporting queries.
58. How do you find the maximum salary in each department?
Answer:
SELECT department, MAX(salary)
FROM employees
GROUP BY department;59. How do you use CASE WHEN in SQL?
Answer:
SELECT name, salary,
CASE
WHEN salary > 100000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_bracket
FROM employees;60. How do you optimize a slow SQL query?
Answer:
- Use appropriate indexes
- Reduce the number of joins
- Avoid complex subqueries
- Use temporary tables if needed
- Rewrite logic with CTEs or window functions
- Use
LIMIT,WHERE, andEXPLAIN
Also Check: SQL Interview Questions for Experienced
Que 61. What is a Common Table Expression (CTE), and how is it different from a subquery?
Answer:
A Common Table Expression (CTE) is a temporary result set defined using the WITH clause that can be referenced within a SELECT, INSERT, UPDATE, or DELETE query.
Syntax:
WITH dept_avg AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT * FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;Differences from subqueries:
- CTEs improve readability and maintainability for complex queries.
- CTEs can be recursive, while subqueries cannot.
- CTEs can be referenced multiple times in the same query.
Que 62. What is a window function, and how does it differ from aggregate functions?
Answer:
Window functions perform calculations across a set of table rows related to the current row, without collapsing them into a single output row like aggregate functions.
Example:
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;Key differences:
- Aggregate functions return one row per group.
- Window functions return one row per input row and allow computations like ranking, running totals, and moving averages.
Que 63. How does RANK() differ from DENSE_RANK() and ROW_NUMBER()?
Answer:
| Function | Behavior with Ties | Gaps in Ranking | Example Output for Tied Values |
|---|---|---|---|
ROW_NUMBER() | Assigns unique number per row | Yes | 1, 2, 3 |
RANK() | Same rank for ties | Yes | 1, 1, 3 |
DENSE_RANK() | Same rank for ties | No | 1, 1, 2 |
Use RANK() when you want to skip ranks after duplicates, and DENSE_RANK() when you want consecutive ranks.
Que 64. What are the different types of indexes, and when would you use each?
Answer:
Types of Indexes:
- B-Tree Index: Default in most databases; used for equality and range queries.
- Bitmap Index: Efficient for low-cardinality columns (e.g., gender, status).
- Composite Index: Combines multiple columns; good for multi-column filtering.
- Unique Index: Enforces uniqueness of values.
- Full-text Index: Optimized for textual searches with large data.
Use Cases:
- Use B-Tree for frequently queried columns.
- Use Bitmap on static, low-distinct-value columns in data warehouses.
- Use Composite to speed up WHERE clauses with multiple columns.
Que 65. Explain indexing strategies for optimizing JOIN performance.
Answer:
To optimize JOIN performance:
- Index foreign keys: Index the column used in JOIN conditions, especially on the child table.
- Use composite indexes: When multiple columns are involved in the join.
- Covering indexes: Include all columns in SELECT and WHERE to avoid table lookups.
- Avoid functions on join columns: Functions prevent index usage.
Example:
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;Both orders.customer_id and customers.customer_id should be indexed.
Que 66. What is a recursive CTE? Give an example.
Answer:
A recursive CTE is a CTE that references itself to iterate over hierarchical or sequential data.
Example: Hierarchical employee structure
WITH RECURSIVE emp_hierarchy AS (
SELECT employee_id, manager_id, name, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.name, h.level + 1
FROM employees e
JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM emp_hierarchy;This helps in traversing organizational trees, bill-of-materials, etc.
Que 67. What is the difference between EXISTS and IN? When is one better than the other?
Answer:
| Feature | EXISTS | IN |
|---|---|---|
| Execution | Stops after first match | Checks all values in subquery |
| NULL Handling | Ignores NULLs | May return unexpected results |
| Performance | Better for correlated subqueries | Better for small static lists |
Use EXISTS when the inner query is large or correlated. Use IN when comparing against a small, fixed list.
Que 68. What is a covering index?
Answer:
A covering index is an index that includes all columns required by a query (SELECT, WHERE, JOIN). This allows the database to fulfill the query without accessing the base table, improving performance.
Example:
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary, name);A query like:
SELECT name FROM employees WHERE department_id = 10 AND salary > 50000;can be served entirely from the index.
Que 69. How do you detect and prevent SQL injection?
Answer:
SQL Injection occurs when untrusted input is embedded directly into SQL queries.
Detection Techniques:
- Monitor for anomalous query patterns
- Use tools like sqlmap or database logs
Prevention Best Practices:
- Always use prepared statements / parameterized queries
- Validate and sanitize user inputs
- Avoid dynamic SQL unless absolutely necessary
- Use ORM frameworks that abstract SQL safely
Que 70. Explain ACID properties in the context of SQL transactions.
Answer:
ACID stands for:
- Atomicity: All operations in a transaction succeed or none do.
- Consistency: Database remains in a valid state before and after the transaction.
- Isolation: Concurrent transactions do not interfere with each other.
- Durability: Once committed, data remains even during system failure.
These properties ensure data reliability and consistency in multi-user environments.
Que 71. What is the difference between MERGE, UPSERT, and INSERT … ON DUPLICATE KEY?
Answer:
| Syntax/Command | Purpose | Common In |
|---|---|---|
MERGE | Conditional insert/update/delete | SQL Server, Oracle |
UPSERT (PostgreSQL) | Insert or update if exists | PostgreSQL |
INSERT ... ON DUPLICATE | Insert or update | MySQL |
These commands allow updating or inserting data in a single atomic operation, reducing round trips.
Que 72. How would you optimize a slow-running SQL query?
Answer:
Steps to optimize:
- Check execution plan: Identify bottlenecks.
- Add missing indexes: On frequently filtered or joined columns.
- Avoid SELECT: Retrieve only needed columns.
- Limit subqueries and joins: Replace with joins or temp tables if needed.
- Use CTEs or temp tables for complex logic.
- Avoid functions in WHERE/JOIN clauses as they disable indexing.
Consistent profiling and testing are key to performance tuning.
Que 73. What is the purpose of the WITH TIES clause?
Answer:WITH TIES is used with TOP or LIMIT to include additional rows that match the value of the last row in the ordered set.
Example (SQL Server):
SELECT TOP 3 WITH TIES name, salary
FROM employees
ORDER BY salary DESC;If the 3rd and 4th employees have the same salary, both will be included. Useful when ranking or handling tied scores.
Que 74. How do you implement a pivot operation in SQL?
Answer:
Pivoting transforms rows into columns.
Example (using CASE):
SELECT department,
SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department;Alternatively, use the PIVOT operator in databases like SQL Server or Oracle.
Que 75. What is a materialized view, and how is it different from a regular view?
Answer:
| Feature | Regular View | Materialized View |
|---|---|---|
| Storage | No (virtual only) | Yes (physical data stored) |
| Performance | Slower, computed at runtime | Faster, precomputed results |
| Refreshable | No | Yes (on-demand or scheduled) |
Materialized views are useful for performance in reporting and analytical queries.
Que 76. What is a correlated subquery? Provide an example.
Answer:
A correlated subquery references columns from the outer query and is evaluated once for each row of the outer query.
Example:
SELECT e1.name, salary
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id
);It returns employees who earn more than the average salary in their own department.
Que 77. How do you ensure concurrency in high-transaction environments?
Answer:
To maintain concurrency:
- Use proper transaction isolation levels (
READ COMMITTED,REPEATABLE READ, etc.) - Implement row-level locking when needed
- Minimize lock durations
- Use optimistic concurrency control where feasible
- Monitor and resolve deadlocks proactively
Balancing consistency and performance is key in high-concurrency systems.
Que 78. How does the ISNULL() function differ from COALESCE()?
Answer:
| Function | Behavior | Portability |
|---|---|---|
ISNULL(x, y) | Returns y if x is NULL | SQL Server only |
COALESCE() | Returns first non-null value in list | ANSI SQL |
COALESCE() is more flexible and portable across databases, whereas ISNULL() is specific to SQL Server.
Que 79. What are lateral joins in SQL?
Answer:
LATERAL JOIN allows subqueries in the FROM clause to reference columns from previous tables in the same FROM clause.
Example (PostgreSQL):
SELECT e.name, p.*
FROM employees e,
LATERAL (
SELECT * FROM projects p WHERE p.employee_id = e.id
) p;It enables correlated subqueries to behave like table joins, often used for top-N per group queries.
Que 80. How do you find and remove duplicate rows from a table?
Answer:
To detect duplicates:
SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;To delete duplicates (keeping one):
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rn
FROM employees
)
DELETE FROM cte WHERE rn > 1;This keeps the first occurrence and removes the rest based on partitioned row numbers.
Technical SQL Interview Questions and Answers
Here are some Technical SQL Interview Questions and Answers for intermediate to advanced level candidates.
81. How do you find duplicate records in a table?
Answer:
Use GROUP BY and HAVING to find duplicates based on specific columns.
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;82. What is the difference between WHERE and HAVING in SQL?
Answer:
| Clause | Used With | Filters |
|---|---|---|
| WHERE | Before GROUP BY | Individual rows |
| HAVING | After GROUP BY | Aggregated groups |
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;83. How do you get the second highest salary from a table?
Answer:
Use DISTINCT, ORDER BY, and LIMIT.
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;84. What is the purpose of the IN and EXISTS operators in SQL?
Answer:
IN: Checks if a value matches any in a listEXISTS: Checks if a subquery returns rows
Use EXISTS when working with correlated subqueries for better performance in large datasets.
Also Check: 100 Spring Boot Interview Questions
85. What is the use of indexes in SQL and how do they improve performance?
Answer:
Indexes speed up data retrieval by creating a pointer structure on columns. They are useful on columns used in WHERE, JOIN, and ORDER BY.
CREATE INDEX idx_name ON employees(last_name);Note: Over-indexing can slow down INSERT and UPDATE.
86. Explain window functions with an example.
Answer:
Window functions perform calculations across rows related to the current row without collapsing the result set.
SELECT employee_id, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;87. How do you delete duplicate records but keep one?
Answer:
Use ROW_NUMBER() in a CTE to identify duplicates.
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
)
DELETE FROM cte WHERE rn > 1;88. What is a correlated subquery? Give an example.
Answer:
A correlated subquery refers to the outer query and is executed once for every row of the outer query.
SELECT name FROM employees e
WHERE salary > (
SELECT AVG(salary) FROM employees WHERE department = e.department
);89. How does indexing affect SQL query performance?
Answer:
Indexes improve query performance by allowing the database to find data without scanning entire tables. However, over-indexing can slow down inserts and updates due to maintenance overhead.
90. How do you write a query to transpose rows into columns (pivot)?
Answer:
Use CASE WHEN with aggregation.
SELECT department,
SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department;Also check: Technical Interview Questions and Answers
SQL interview questions and Answers for Data Analyst
hese are crafted to assess practical data analysis capabilities using SQL, increasing in complexity from simple queries to advanced analytics functions.
91. How do you retrieve the top 5 highest-paid employees from an employee table?
Answer:
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;92. How would you calculate the average order value per customer?
Answer:
SELECT customer_id, AVG(order_total) AS avg_order_value
FROM orders
GROUP BY customer_id;93. How do you calculate the percentage contribution of each product to total sales?
Answer:
SELECT product_id,
SUM(sales_amount) AS total_sales,
SUM(sales_amount) * 100.0 / (SELECT SUM(sales_amount) FROM sales) AS percentage_contribution
FROM sales
GROUP BY product_id;94. How do you find customers who have not placed any orders?
Answer:
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;95. How do you identify duplicate rows based on email in a user table?
Answer:
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;96. How do you calculate the cumulative sum of sales by date?
Answer:
SELECT sale_date, sales_amount,
SUM(sales_amount) OVER (ORDER BY sale_date) AS cumulative_sales
FROM sales;97. How would you find the month with the highest sales?
Answer:
SELECT EXTRACT(MONTH FROM sale_date) AS sale_month,
SUM(sales_amount) AS monthly_sales
FROM sales
GROUP BY sale_month
ORDER BY monthly_sales DESC
LIMIT 1;98. How do you compare year-over-year growth in SQL?
Answer:
SELECT year,
SUM(revenue) AS total_revenue,
LAG(SUM(revenue)) OVER (ORDER BY year) AS last_year_revenue,
(SUM(revenue) - LAG(SUM(revenue)) OVER (ORDER BY year)) * 100.0 /
LAG(SUM(revenue)) OVER (ORDER BY year) AS yoy_growth_percentage
FROM sales
GROUP BY year;Need more data analyst interview questions? Visit here: Data Analyst Interview Questions and Answers
Scenario Based SQL interview questions and Answers
99. You have a Sales table. How would you retrieve the top-selling product in each region?
Answer:
SELECT region, product, total_sales
FROM (
SELECT region, product, SUM(sales_amount) AS total_sales,
RANK() OVER (PARTITION BY region ORDER BY SUM(sales_amount) DESC) AS rnk
FROM sales
GROUP BY region, product
) ranked
WHERE rnk = 1;100. A Customers table has some customers who have never made a purchase. How do you find and list them?
Answer:
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;This scenario tests knowledge of LEFT JOIN and null-checking logic.
101. From an Orders table, how do you find customers who placed more than one order on the same day?
Answer:
SELECT customer_id, order_date, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;102. How do you return the first order placed by each customer from an Orders table?
Answer:
SELECT customer_id, order_id, order_date
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders
) ranked
WHERE rn = 1;This uses ROW_NUMBER() and is common in interviews for customer history analytics.
103. A Transactions table contains rows for each product purchase. How do you find products that were sold every day in January 2024?
Answer:
SELECT product_id
FROM transactions
WHERE DATE(transaction_date) BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY product_id
HAVING COUNT(DISTINCT DATE(transaction_date)) = 31;This shows logical filtering and validation across multiple dates.
104. How do you detect a price increase compared to the previous day for the same product in a “Product_prices” table?
Answer:
SELECT product_id, price_date, price,
LAG(price) OVER (PARTITION BY product_id ORDER BY price_date) AS previous_price
FROM product_prices
WHERE price > LAG(price) OVER (PARTITION BY product_id ORDER BY price_date);Also Check: Java interview Questions and Answers PDF
SQL interview questions and Answers PDF
Here is the PDF file of SQL questions and answers, so you can download and read anytime.
FAQs: SQL interview questions and Answers
What does an SQL-based job role typically involve?
An SQL job primarily involves working with relational databases to extract, manipulate, and analyze data. Professionals in these roles may design database schemas, write complex queries, optimize database performance, and support data pipelines. These roles are common in data analytics, business intelligence, data engineering, and backend development.
What are the common challenges faced during an SQL interview?
Candidates often face challenges with writing efficient queries under time constraints, especially involving JOIN, GROUP BY, and subqueries. Many interviews also include real-world scenarios that test your ability to clean data, handle edge cases, and explain query logic clearly. Performance tuning and SQL logic questions can be tricky if you’re not well-practiced.
What types of roles typically require SQL expertise?
SQL is a foundational skill across several roles, including Data Analyst, Business Analyst, Data Engineer, Database Administrator (DBA), and even Software Engineers who interact with backend systems. Strong SQL knowledge is essential for any role that involves structured data analysis or ETL processes.
How much does an SQL professional earn in the USA?
In the USA, SQL professionals earn between $70,000 to $120,000 per year depending on experience, industry, and location. Entry-level analysts may start around $65,000, while senior data engineers or DBAs can earn $130,000 or more, especially in tech-heavy cities like Seattle, San Francisco, and Austin.
Which companies commonly hire for SQL-related roles?
Top companies hiring SQL professionals include Google, Amazon, Microsoft, Facebook, Accenture, IBM, Deloitte, JPMorgan Chase, Walmart, and Netflix. These roles span across domains like finance, e-commerce, healthcare, and IT consulting.
How should I prepare for an SQL interview?
Prepare by practicing SQL problems on platforms like LeetCode, Mode Analytics, or StrataScratch. Focus on writing optimized queries involving joins, aggregations, window functions, and case statements. Review database normalization, indexing, and real-world reporting scenarios. Mock interviews and timed query writing can help build confidence.
Do certifications help in getting SQL jobs?
Certifications like Microsoft’s SQL Server, Google’s Data Analytics Certificate, and Oracle SQL Certification can enhance your profile, especially if you’re new to the field. However, practical query-writing skills and a strong understanding of data are usually more valued by hiring managers during SQL interviews.
Conclusion
We have already shared SQL interview questions and answers for all levels, from freshers to experienced. A PDF download is also available for easy access.
