50 SQL Interview Questions and Answers for Freshers and Experienced

1. What is SQL?

How to Answer:

• Define SQL as a standard language used to manage relational databases.
• Mention its main functions: creating, retrieving, updating, and deleting data.
• Explain that SQL is widely used to manage structured data across industries.

Sample Answer:

SQL (Structured Query Language) is a standard language used to manage and manipulate relational databases. It allows users to create, retrieve, update, and delete data. SQL is widely used across industries to store, organise, and manage structured data.

2. What Are the Different Types of SQL Commands?

How to Answer:

• Name the five main types of SQL commands.
• Briefly explain the purpose of each type.
• Give common examples of commands under each category.

Sample Answer:

SQL commands are divided into five main types:

• DDL (Data Definition Language): CREATE, ALTER, DROP. These commands define or change database structures.
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE. These commands add, modify, or remove data.
• DQL (Data Query Language): SELECT. This command retrieves data from a database.
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT. These commands manage database transactions.
• DCL (Data Control Language): GRANT, REVOKE. These commands manage user permissions.

3. What Is a Primary Key in SQL?

How to Answer:

• Define a primary key as a unique identifier for each record.
• Explain that it can contain one or more columns.
• Mention that primary key values cannot be duplicated or NULL.
• State that a table can have only one primary key constraint, although it can contain multiple columns.

Sample Answer:

A primary key is a column or a group of columns that uniquely identifies each record in a table. It does not allow duplicate or NULL values. A table can have only one primary key constraint, which helps maintain data accuracy and integrity.

4. What Is a Foreign Key?

How to Answer:

• Define a foreign key as a column or group of columns that refers to a key in another table.
• Explain how it connects related tables.
• Mention its role in maintaining referential integrity.

Sample Answer:

A foreign key is a column or group of columns that refers to a primary key or another eligible unique key in a related table. It connects tables and helps maintain referential integrity by preventing invalid references.

5. What Is the Difference Between DELETE and TRUNCATE Commands?

How to Answer:

• Explain what each command removes.
• Mention whether a WHERE condition can be used.
• Compare their performance and rollback behaviour without making database-specific claims.

Sample Answer:

• DELETE: Removes selected rows or all rows from a table. It supports a WHERE condition to remove specific records.
• TRUNCATE: Removes all rows from a table while keeping its structure. It does not support a WHERE condition.
• Performance: TRUNCATE is often faster when removing all rows from a large table.
• Rollback: Rollback support depends on the database system and transaction settings, so it is not accurate to say that TRUNCATE can never be rolled back.

6. What Is the Difference Between SQL and MySQL?

How to Answer:

• Explain that SQL is a language used to work with relational databases.
• Define MySQL as a relational database management system.
• Describe how MySQL uses SQL to manage data.

Sample Answer:

SQL is a standard language used to query and manage relational databases, while MySQL is a relational database management system (RDBMS) that uses SQL. SQL defines how users interact with data, whereas MySQL provides the software to store, retrieve, and manage that data.

7. What is normalization in SQL, and what are its types?

How to Answer:

• Define normalization as the process of organizing data to reduce redundancy.
• Explain how it improves data consistency.
• Name the main types of normalization.

Sample Answer:

Normalization is the process of organizing data in a database to reduce redundancy and improve data consistency. The main types are:

• 1NF: Ensures each column contains a single value.
• 2NF: Removes partial dependencies.
• 3NF: Removes transitive dependencies.

Looking to go beyond SQL and build expertise in AI-powered data analytics? Join upGrad’s Professional Certificate Programme in Data Science & Agentic AI and gain hands-on experience in SQL, Python, Machine Learning, and Generative AI.

8. What is an index in SQL, and what are its types?

How to Answer:

• Define an index as a database object that speeds up data retrieval.
• Explain that indexes can slow down INSERT, UPDATE, and DELETE operations because they need to be updated when data changes.
• Name the two common types of indexes.

Sample Answer:

An index in SQL speeds up data retrieval by helping the database find records quickly. However, indexes can slow down INSERT, UPDATE, and DELETE operations because they need to be updated when data changes.

• Clustered Index: Organizes table data according to the index key.
• Non-Clustered Index: Stores index keys separately and uses references to locate records.

9. What is the difference between UNIQUE and PRIMARY KEY constraints?

How to Answer:

Explain the differences between these two one by one.

Sample Answer:

• PRIMARY KEY: Ensures uniqueness and does not allow NULL values. A table can have only one primary key.
• UNIQUE: Ensures uniqueness but allows NULL values, depending on the database system. A table can have multiple unique constraints.

10. What is a stored procedure in SQL?

How to Answer:

• Define a stored procedure as a set of SQL statements stored in a database.
• Mention its benefits, such as improving performance, reducing repeated code, and enhancing security.

Sample Answer:

A stored procedure is a set of SQL statements stored in a database and executed when needed. It helps reduce repeated code, improve performance, reduce network traffic, and enhance security by limiting direct access to data.

11. What is the difference between CHAR and VARCHAR data types?

How to Answer:

Explain the difference between fixed-length and variable-length data types.

Sample Answer:

• CHAR: Stores fixed-length character strings.
• VARCHAR: Stores variable-length character strings and uses space based on the value stored.

Read: Difference Between VARCHAR and VARCHAR2

12. What is a NULL value in SQL?

How to Answer:

• Define NULL as representing missing or unknown data.
• Differentiate NULL from zero or an empty string.
• Mention IS NULL and IS NOT NULL.

Sample Answer:

A NULL value in SQL represents missing or unknown data. It is different from zero or an empty string. SQL uses IS NULL and IS NOT NULL to check for NULL values in queries.

13. What is the difference between GROUP BY and ORDER BY?

How to Answer:

Explain the purpose of each clause and how they differ.

Sample Answer:

• GROUP BY: Groups rows with common values, often using aggregate functions like SUM() or COUNT().
• ORDER BY: Sorts query results in ascending or descending order.

14. What is a view in SQL?

How to Answer:

• Define a view as a virtual table based on an SQL query.
• Explain how it simplifies data access and can improve security.

Sample Answer:

A view is a virtual table based on the result of an SQL query. It displays data from existing tables and helps simplify complex queries. Views can also improve security by restricting access to selected data.

Also Read: SQL For Beginners: Essential Queries, Joins, Indexing & Optimization Tips

15. What are aggregate functions in SQL?

How to Answer:

• List common aggregate functions and explain their purpose.
• Mention that they perform calculations on a set of values.

Sample Answer:

Aggregate functions perform calculations on a set of values and return a single result. Common aggregate functions include:

• SUM(): Calculates the total of a column.
• AVG(): Returns the average value.
• COUNT(): Counts rows or non-NULL values, depending on the expression.
• MAX() / MIN(): Finds the highest and lowest values.

16. What are the different constraints in SQL?

How to Answer:

• List common constraints and explain their purpose.
• Mention how constraints help maintain data integrity.

Sample Answer:

Constraints are rules applied to table columns to maintain data integrity. Common constraints include:

• PRIMARY KEY: Uniquely identifies each record.
• FOREIGN KEY: Links related tables.
• UNIQUE: Prevents duplicate values.
• NOT NULL: Prevents NULL values.
• CHECK: Ensures values meet a specified condition.

Understand Data, Features, and how models learn from patterns with Machine Learning Courses. You can master neural networks, NLP & reinforcement with Python through courses from Top Universities

SQL Joins and Subqueries Interview Questions

17. What are joins in SQL? What are the different types?

How to Answer:

• Define joins as operations used to combine data from multiple tables based on a related column.
• List the main types of joins.

Sample Answer:

Joins are used to retrieve data from multiple tables based on a related column. The different types of joins are:

• INNER JOIN: Returns records with matching values in both tables.
• LEFT JOIN: Returns all records from the left table and matching records from the right.
• RIGHT JOIN: Returns all records from the right table and matching records from the left.
• FULL JOIN: Returns all records when there is a match in either table.

Also Read: SQL for Data Science: Functions, Queries, and Best Practices

18. What is the difference between INNER JOIN and OUTER JOIN?

How to Answer:

• Explain what INNER JOIN returns.
• Explain how OUTER JOIN handles unmatched rows.
• Name the three types of OUTER JOIN.

Sample Answer:

INNER JOIN returns only matching rows from both tables based on a common column. OUTER JOIN also includes unmatched rows from one or both tables. Its types are LEFT JOIN, RIGHT JOIN, and FULL JOIN, depending on which unmatched rows are included.

19. What is a self-join in SQL?

How to Answer:

• Define a self-join as a join in which a table is joined with itself.
• Explain why it is used.
• Mention the use of table aliases.

Sample Answer:

A self-join is a join in which a table is joined with itself to compare rows within the same table. Table aliases are used to distinguish between the two instances of the table.

20. What is a subquery in SQL? Explain with an example.

How to Answer:

• Define a subquery as a query written inside another SQL query.
• Explain that it helps retrieve data based on the result of another query.
• Give a simple use case.

Sample Answer:

A subquery is a query placed inside another SQL query. It helps retrieve data based on the result of an inner query. For example, a subquery can find employees whose salaries are higher than the average salary in their department.

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

How to Answer:

Explain how both operators combine results from multiple SELECT statements and how they handle duplicate rows.

Sample Answer:

• UNION: Combines the results of two or more SELECT statements and removes duplicate rows.
• UNION ALL: Combines the results and keeps duplicate rows.

Both queries must return the same number of columns, with compatible data types in corresponding columns.

22. What is the difference between EXISTS and IN operators?

How to Answer:

• Explain how IN checks whether a value matches a value in a list or subquery result.
• Explain how EXISTS checks whether a subquery returns any rows.

Sample Answer:

• IN: Checks whether a value matches any value returned by a list or subquery.
• EXISTS: Checks whether a subquery returns at least one row.

Both operators can be used to filter query results. Their performance depends on the query, database system, and available indexes.

Learn more about data science and get an affordable MS in Data Science online degree from the UK’s Liverpool John Moores University in only 18 months, apply now! 

SQL Window Functions Interview Questions

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

How to Answer:

Explain the difference between these three functions one by one.

Sample Answer:

• RANK(): Assigns ranks but skips numbers after duplicate values.
• DENSE_RANK(): Assigns ranks without skipping numbers after duplicate values.
• ROW_NUMBER(): Assigns a unique sequential number to each row.

24. What is a window function in SQL?

How to Answer:

• Define a window function as a function that performs calculations across related rows.
• Explain that it retains individual rows instead of grouping them into a single result.

Sample Answer:

A window function performs calculations across a set of rows related to the current row. Unlike aggregate functions used with GROUP BY, it retains individual row data. Examples include RANK(), DENSE_RANK(), ROW_NUMBER(), and SUM() OVER().

Also Read: Is SQL Hard to Learn? Breaking Down the Challenges and Solutions 

25. What is the difference between aggregate functions and window functions in SQL?

How to Answer:

• Explain how aggregate functions summarise data.
• Describe how window functions calculate results while retaining individual rows.

Sample Answer:

• Aggregate Functions: Calculate a single result for a group of rows, such as the total or average salary.
• Window Functions: Perform calculations across related rows while keeping each row in the result.

For example, AVG() with GROUP BY can show the average salary for each department, while a window function can show each employee's salary alongside their department's average salary.

26. How do you calculate a running total using a window function in SQL?

How to Answer:

• Define a running total as the cumulative sum of values over a sequence of rows.
• Explain that SUM() with the OVER() clause can calculate a running total.
• Mention that the rows must be ordered correctly.

Sample Answer:

A running total is a cumulative sum that adds each value to the total of the previous rows. In SQL, SUM() with the OVER() clause calculates a running total. For example, a sales table can show each day's sales alongside the cumulative sales up to that day. The results are ordered by date to calculate the total correctly.

Advanced SQL Questions for Experienced: Indexing, Optimisation and Transactions

27. What is the difference between WHERE and HAVING clauses?

How to Answer:

Explain the difference between these two clauses.

Sample Answer:

• WHERE: Filters records before aggregation and applies to individual rows.
• HAVING: Filters grouped records after aggregation using functions like SUM() or COUNT().

28. What are ACID properties in SQL?

How to Answer:

Explain the properties of each element one by one.

Sample Answer:

ACID properties ensure reliable database transactions:

• Atomicity: Transactions are fully completed or not executed at all.
• Consistency: Data remains valid before and after a transaction.
• Isolation: Transactions do not interfere with each other.
• Durability: Completed transactions are permanently saved.

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

How to Answer:

Explain the difference between these three one by one.

Sample Answer:

• DELETE: Removes specific rows from a table and can remove all rows when used without a WHERE condition.
• TRUNCATE: Removes all rows but keeps the table structure.
• DROP: Deletes the entire table, including its structure.

30. What is the difference between a view and a table?

How to Answer:

• Explain that a table stores data in the database.
• Define a view as a virtual table based on an SQL query.
• Mention how views simplify queries and restrict data access.

Sample Answer:

A table stores data in the database, while a view is a virtual table based on an SQL query. Views simplify complex queries and can enhance security by restricting access to selected data.

31. What is a trigger in SQL?

How to Answer:

• Define a trigger in SQL.
• Explain when it runs and what it is used for.

Sample Answer:

A trigger in SQL is a set of SQL statements that runs automatically when a specific event occurs in a table, such as an INSERT, UPDATE, or DELETE operation. It helps automate tasks and maintain data consistency.

32. What is a cursor in SQL?

How to Answer:

• Define a cursor.
• Explain how it processes query results.

Sample Answer:

A cursor in SQL processes query results one row at a time. It is useful when a task requires individual row processing, although it can be slower than set-based operations.

33. What is the difference between a candidate key and a primary key?

How to Answer:

Explain the difference between these two keys.

Sample Answer:

A candidate key is any column or set of columns that can uniquely identify a record. A primary key is a selected candidate key that uniquely identifies rows and cannot have NULL values.

34. What is the COALESCE function in SQL?

How to Answer:

• Define COALESCE() as a function that returns the first non-NULL value from a list.
• Explain how it helps handle NULL values.

Sample Answer:

The COALESCE() function returns the first non-NULL value from a list. It helps replace NULL values with meaningful data.

35. What are the different isolation levels in SQL?

How to Answer:

• Define isolation levels as ways to control how transactions interact with each other.
• List the four main isolation levels.

Sample Answer:

Isolation levels control how transactions interact with each other to maintain data integrity. The four main isolation levels are:

• Read Uncommitted: Allows transactions to read uncommitted changes.
• Read Committed: Prevents transactions from reading uncommitted changes.
• Repeatable Read: Prevents data from changing when the same row is read again within a transaction.
• Serializable: The strictest level, which provides the highest level of transaction isolation.

36. How does a clustered index work?

How to Answer:

• Define a clustered index as a way to determine the physical order of data in a table.
• Explain its effect on query performance.

Sample Answer:

A clustered index determines the physical order of data in a table. A table can have only one clustered index because its data can be stored in only one order. It improves search performance for range queries but can slow down insertions and updates.

37. What is the difference between a CTE and a temporary table?

How to Answer:

Explain the difference between these two one by one.

Sample Answer:

A CTE (Common Table Expression) is a temporary named result set used within a single SQL statement. A temporary table stores data temporarily in the database and can be used across multiple statements within its scope. CTEs help simplify complex queries, while temporary tables are useful when intermediate results need to be reused.

38. What are materialized views in SQL?

How to Answer:

• Define a materialized view.
• Explain how it differs from a regular view.

Sample Answer:

A materialized view stores the result of a SQL query physically in the database. Unlike a regular view, which retrieves data from its underlying tables when queried, a materialized view stores the query results. It can improve query performance but needs to be refreshed to reflect changes in the underlying data.

39. What is the difference between NOLOCK and READ COMMITTED in SQL?

How to Answer:

Explain the difference between these two isolation options.

Sample Answer:

• NOLOCK: Allows queries to read uncommitted data, which may include changes that are later rolled back.
• READ COMMITTED: Prevents queries from reading uncommitted data.

NOLOCK can reduce waiting for locks but may return inconsistent results. READ COMMITTED provides more reliable results by preventing dirty reads.

40. What is sharding in SQL databases?

How to Answer:

• Define sharding as splitting a large database into smaller parts.
• Explain how it helps manage large datasets and improve performance.

Sample Answer:

Sharding divides a large database into smaller parts called shards. Each shard stores a portion of the data and can be managed separately. It helps improve scalability and handle large amounts of data efficiently.

41. When should denormalization be used instead of normalization?

How to Answer:

• Define normalization and denormalization.
• Explain when denormalization is useful.

Sample Answer:

Normalization organizes data to reduce redundancy and improve consistency. Denormalization adds some redundancy to improve query performance. It is useful in reporting systems and data warehouses where faster data retrieval is more important than reducing redundancy.

42. What is the difference between OLTP and OLAP systems?

How to Answer:

• Define both systems.
• Explain their main purposes and uses.

Sample Answer:

• OLTP (Online Transaction Processing): Handles day-to-day transactions, such as banking operations. It is optimized for quick inserts and updates.
• OLAP (Online Analytical Processing): Handles complex queries and data analysis. It is used for reporting and supports read-heavy operations.

Learn: How to develop analytical thinking

43. How does indexing affect SQL performance?

How to Answer:

• Explain how indexing improves data retrieval.
• Mention its effect on INSERT, UPDATE, and DELETE operations.
• Explain why choosing the right index type matters.

Sample Answer:

Indexing speeds up data retrieval by helping the database find records faster. However, it can slow down INSERT, UPDATE, and DELETE operations because indexes must also be updated when data changes. Choosing the right index type helps improve SQL performance.

44. What is a deadlock in SQL, and how can it be prevented?

How to Answer:

• Define a deadlock.
• Explain ways to prevent it.

Sample Answer:

A deadlock occurs when two transactions hold resources that the other needs, preventing both from progressing. It can be prevented by:

• Ensuring transactions access resources in a consistent order.
• Keeping transactions short.
• Using database deadlock detection mechanisms.

45. What is partitioning in SQL, and what are its types?

How to Answer:

• Define partitioning as dividing a large table into smaller parts.
• List the main types of partitioning.

Sample Answer:

Partitioning divides a large table into smaller, manageable parts to improve data management and query performance. The main types are:

• Range Partitioning: Divides data based on value ranges, such as years.
• List Partitioning: Divides data based on predefined values, such as regions.
• Hash Partitioning: Uses a hash function to distribute data across partitions.

Scenario-Based SQL Interview Questions

Use the following sample tables to answer the five SQL interview questions.

Sample Database Setup

The database contains three tables: Employees, Products, and Orders. A separate Customers table stores customer details.

Employees Table

Products Table

Orders Table

The Customers table contains customer_id and customer_name. Customers 201, 202, 203, and 204 are listed. Customer 204 has not placed any orders.

46. Write a query to find the second-highest salary.

How to Answer:

Use a subquery to find the highest salary below the maximum salary.

Sample Answer:

SELECT MAX(salary) AS second_highest_salary
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);

Explanation: This query returns the second-highest distinct salary, which is 60000.

47. Write a query to find duplicate records in a table.

How to Answer:

Use GROUP BY and HAVING COUNT(*) > 1 to find repeated values.

Sample Answer:

SELECT salary, COUNT(*) AS total
FROM Employees
GROUP BY salary
HAVING COUNT(*) > 1;

Explanation: This query finds salary values that appear more than once. In the sample data, 80000 appears twice.

48. Write a query to find employees who earn more than the average salary.

How to Answer:

Calculate the average salary and compare each employee's salary with it.

Sample Answer:

SELECT employee_name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);

Explanation: This query returns employees whose salaries are higher than the average salary of all employees.

49. Write a query to calculate total sales for each product.

How to Answer:

Multiply the quantity by the price and calculate the total for each product.

Sample Answer:

SELECT p.product_name,
       SUM(o.quantity * o.price) AS total_sales
FROM Products p
JOIN Orders o ON p.product_id = o.product_id
GROUP BY p.product_id, p.product_name;

Explanation: This query calculates total sales for each product. Laptop sales total 150000, Phone sales total 80000, and Tablet sales total 30000.

50. Write a query to find customers who have not placed any orders.

How to Answer:

Use LEFT JOIN to find customers who do not have matching orders.

Sample Answer:

SELECT c.customer_id, c.customer_name
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Explanation: This query returns customers who have not placed any orders. In the sample data, it returns customer 204.

How to Practice for a SQL Interview

Preparing for an SQL interview requires a solid grasp of database concepts, problem-solving skills, and the ability to apply SQL in different scenarios. Whether you are a beginner or an experienced professional, these tips will help you prepare for SQL interview questions with confidence.

1. Understand the Basics Thoroughly

Build a strong foundation in basic SQL concepts and commands.

• Review essential commands like SELECT, INSERT, UPDATE, and DELETE.
• Practise simple queries involving filtering, sorting, and limiting results.
• Write basic queries using INNER JOIN and LEFT JOIN.

2. Practise Query Optimisation

Learn how to improve query performance and efficiency.

• Use EXPLAIN to analyse query execution.
• Understand indexing and its impact on performance.
• Avoid unnecessary subqueries when a JOIN is more suitable.

3. Master Different Join Types

Understand how joins work and when to use them.

• Practice INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
• Learn to join multiple tables and apply suitable filters.

Also Read: SQL Jobs for Freshers: Salary, Career Growth & Opportunities

4. Understand Normalisation and Data Integrity

Learn how to organise data and maintain database consistency.

• Study the concepts of 1NF, 2NF, and 3NF.
• Practise designing tables with proper relationships and constraints, such as PRIMARY KEY and FOREIGN KEY.

5. Work on Complex Queries and Subqueries

Prepare for questions that involve nested queries and complex data retrieval.

• Practise correlated and non-correlated subqueries.
• Try using a subquery to find employees who belong to a department with more than 50 employees.

Get Comfortable with Aggregation and Grouping

Learn how to summarise data and filter grouped results.

• Use GROUP BY with SUM(), COUNT(), AVG(), MAX(), and MIN().
• Practise filtering groups using HAVING.

6. Prepare for Data Manipulation and Transactions

Understand how to modify data and manage transactions.

• Practise INSERT, UPDATE, and DELETE queries.
• Learn to use COMMIT, ROLLBACK, and SAVEPOINT.

Also Read: SQL Vs MySQL: Difference Between SQL and MySQL

7. Know How to Handle NULL Values

Learn how to identify and handle missing or unknown values.

• Use IS NULL and IS NOT NULL to filter records.
• Practise using COALESCE() to replace NULL values with meaningful data.

8. Understand Indexing and Its Impact

Learn how indexes affect query performance.

• Understand the difference between clustered and non-clustered indexes.
• Practise creating indexes and analysing their impact on query execution.

9. Review Database-Specific Features

Prepare for features supported by the database system used in your interview.

• Learn SQL Server features such as CROSS APPLY and FOR XML PATH.
• If the role uses MySQL or PostgreSQL, review their specific features.

10. Test Your Knowledge with Mock Interviews

Practise answering SQL questions under interview conditions.

• Use mock interview platforms or practise with a peer.
• Time yourself to improve your speed and confidence.

Regular practice across these topics can help you strengthen your SQL skills and prepare for both basic and advanced interview questions.

Preparing for an SQL interview? Contact our experts for more tips and guidance to help you prepare with confidence. You can also explore our top courses to build your SQL skills and boost your career prospects.

Similar Reads:

• Difference between Data Analysis and Data Analytics 
• Business Analytics Vs Data Analytics
• Data Mining vs Data Analysis
• What Is Data Interpretation?
• What is data?
• Mock Interview Guide

Reference:
https://www.imarcgroup.com/india-database-as-a-service-market