42 SQL Interview Questions and Answers (2025)

Blog / 42 SQL Interview Questions and Answers (2025)

$104,864

What's that number you're asking?

That's the average salary earned by SQL developers according to recent studies.

Not bad, not bad at all.

With salaries like that and SQL being the 4th most popular programming language in existence...

It's never been a better time to upgrade your SQL skills.

Q1.

What is SQL?

Junior

SQL (Structured Query Language) is a domain-specific language used to manage and manipulate relational databases.
Its primary purpose is to query and manage data in databases, including tasks like creating, updating, deleting, and retrieving data.

Q2.

What is a database?

Junior

A database is an organized collection of structured information or data stored electronically in a computer system, allowing for efficient retrieval, insertion, and management of data.

Q3.

What is a primary key in SQL?

Junior

A primary key is a column or a set of columns in a table that uniquely identifies each row in that table.
Primary keys enforce entity integrity by ensuring that each row can be uniquely identified and that no duplicate or NULL values exist in the key columns.

Preparing for a tech interview?👉Top questions

Q4.

What is a foreign key?

Junior

A foreign key is a field in a table that establishes a link between two tables in a relational database.
It enforces referential integrity by ensuring that values in the foreign key field match values in the primary key of another table.
This maintains data consistency and prevents orphaned records.

Q5.

Explain the ACID properties in the context of database transactions.

Junior

ACID stands for Atomicity, Consistency, Isolation, and Durability, which are properties that ensure the reliability of database transactions.
- Atomicity: Transactions are treated as single, indivisible units. Either all changes in a transaction are committed, or none of them are.
- Consistency: Transactions take the database from one consistent state to another, preserving data integrity.
- Isolation: Transactions are isolated from each other, ensuring that one transaction's changes do not interfere with others.
- Durability: Once a transaction is committed, its changes are permanent and will survive system failures.

Don't let one question ruin your next technical interview...

JavaScript•90

Python•97

OOP•41

Q6.

Explain the difference between SQL and NoSQL databases.

Junior

SQL databases are relational databases that use structured tables with predefined schemas. E.g. MySQL, PostgreSQL, and Oracle.
NoSQL databases are non-relational and use flexible schemas. E.g. MongoDB, Cassandra, and Redis.

Q7.

What is a JOIN in SQL, and what are its types?

Junior

A JOIN clause is used to combine rows from two or more tables based on a related column. Types of JOINs include:

INNER JOIN: Returns records that have matching values in both tables. Use when you want to retrieve only the records that have corresponding matches in both tables.
LEFT (OUTER) JOIN: Returns all records from the left table and the matched records from the right table. Unmatched right table records result in NULL values.
RIGHT (OUTER) JOIN: Returns all records from the right table and the matched records from the left table. Unmatched left table records result in NULL values.
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table. Unmatched records from both tables result in NULL values on the side without a match.
CROSS JOIN: Returns the Cartesian product of rows from both tables. Each row from the first table is combined with all rows from the second table.
SELF JOIN: Joins a table to itself as if it were two separate tables, temporarily renaming at least one table in the SQL statement.

Q8.

What is a view in SQL?

Mid

A view is a virtual table based on the result-set of an SQL statement.
It contains rows and columns just like a real table but does not store data physically; it pulls data from underlying tables.
Example:

Q9.

What is an index in SQL, and why is it used?

Mid

An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional writes and storage space.
Indexes are used to quickly locate data without having to search every row in a table.
Example:
- Creates a new index on the Email column.

Q10.

Explain the difference between clustered and non-clustered indexes.

Mid

Clustered Index: Sorts and stores the data rows in the table based on the index key; only one per table.
Non-Clustered Index: Contains a pointer to the data rows; multiple can exist per table.

Q11.

What is the difference between BETWEEN and IN operators in SQL?

Mid

BETWEEN Operator: Used to filter records within a certain inclusive range.
IN Operator: Used to filter records that match any value in a specified list.

Q12.

What is the difference between DELETE and TRUNCATE commands?

Mid

DELETE: Removes specified rows from a table; can use WHERE clause; logs individual row deletions; can be rolled back.
TRUNCATE: Removes all rows from a table; cannot use WHERE clause; logs deallocation of data pages; faster than DELETE; cannot be rolled back in some databases.

Q13.

What are constraints in SQL, and name a few?

Mid

Constraints are rules applied to table columns to enforce data integrity. Examples include:

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

Q14.

What are aggregate functions in SQL? Can you name a few?

Mid

Aggregate functions perform calculations on multiple values and return a single value.
Examples include:
- COUNT()
- SUM()
- AVG()
- MAX()
- MIN()

Q15.

Explain SQL normalization and denormalization.

Mid

Normalization: It is the process of organizing data in a relational database to minimize data redundancy and dependency. It is typically used to maintain data integrity, reduce storage space, and simplify data maintenance.
Denormalization: It is the opposite of normalization, where data is intentionally duplicated or combined to improve query performance. Denormalization is used when read-heavy operations are more critical than data modification and

Q16.

What is SQL injection, and how can you prevent it?

Mid

SQL injection is a code injection technique that exploits security vulnerabilities in an application's software. Prevention methods include:
- Using parameterized queries
- Employing stored procedures
- Validating user input
- Using ORM frameworks

Q17.

Explain ORDER BY in SQL.

Mid

The ORDER BY clause is used to sort query results based on one or more columns. You can specify the sort order as ascending (ASC) or descending (DESC). It is commonly used to arrange data in a specific order for presentation.
Example:

Q18.

Explain the difference between the GROUP BY and HAVING clauses in SQL.

Mid

GROUP BY: It is used to group rows that have the same values in specified columns. Typically used with aggregate functions like SUM or COUNT.
HAVING: It filters grouped rows based on aggregate function results. It allows you to specify conditions on grouped data after using GROUP BY.
Example:

Q19.

Explain the MERGE statement.

Mid

The MERGE statement combines INSERT, UPDATE, and DELETE operations in a single SQL command based on a condition, often used for upserting (update or insert).
Example:
- This matches rows between TargetTable and SourceTable. If a match is found, it updates; otherwise, it inserts.

Q20.

What is a stored procedure in SQL?

Mid

A stored procedure is a prepared SQL code that can be saved and reused.
It can accept parameters and allows for modular programming, improving performance and security.
Example:

Q21.

What is a trigger in SQL?

Mid

A trigger is a database object that is automatically executed or fired when certain events occur, such as INSERT, UPDATE, or DELETE operations on a table.
Example:

Q22.

Explain database sharding and its benefits in managing large-scale databases.

Mid

Database sharding is a technique for horizontally partitioning a database into smaller, more manageable pieces called shards. Each shard is hosted on a separate server.
It improves scalability, distribution, and performance for large-scale databases by distributing data and query load across multiple servers.

Q23.

What are Common Table Expressions (CTEs)?

Mid

A CTE is a temporary named result set defined within the execution scope of a single SQL statement, improving readability and maintenance.
Example:

Q24.

Explain SQL deadlock and provide strategies for preventing and resolving deadlocks in database systems.

Mid

A deadlock occurs when two or more transactions block each other by holding resources the other needs, creating a cycle of dependency that prevents progress.
Strategies to Prevent Deadlocks:
- Consistent Locking Order: Ensure all transactions acquire locks in the same sequence.
- Minimize Lock Time: Keep transactions short to reduce lock durations.
- Use Indexes: Reduce the scope of locks by optimizing queries with indexes.
- Lower Isolation Levels: Use READ COMMITTED or READ UNCOMMITTED to reduce locking where possible.
Strategies to Resolve Deadlocks:
- Deadlock Detection: Database systems like SQL Server automatically detect and terminate one transaction (the victim).
- Retry Logic: Implement retry mechanisms in application logic to handle terminated transactions.
- Timeouts: Set transaction timeouts to avoid prolonged blocking.

Q25.

What is the purpose of the SQL LIKE operator, and how can wildcards be used with it?

Mid

The LIKE operator is used to search for a specified pattern in a column. Wildcards can be used with LIKE:

%: Matches any sequence of characters, zero or more characters.
_: Matches any single character.
[]: Matches any character within the specified range.
[^]: Matches any character not in the specified range.

Q26.

What is a pivot table in SQL?

Mid

A pivot table is a data summarization tool used to transform rows into columns, providing a clear, aggregated view of data. It is often used to perform data analysis by grouping and aggregating data.

Q27.

How do you implement error handling in SQL?

Mid

TRY...CATCH (T-SQL): Handle errors in SQL Server.
EXCEPTION (PL/pgSQL): Handle errors in PostgreSQL.
DECLARE HANDLER (MySQL): Define error-handling behavior.
Rollback on Errors: Use ROLLBACK to undo transactions if errors occur.
Check Constraints: Validate data to avoid errors at the database level.
Logging: Log errors to a table or file for debugging.

Q28.

Explain the SQL concept of NULL.

Mid

NULL represents missing or unknown data in SQL. It is not the same as an empty string or zero. In database design, NULL should be used sparingly and appropriately.
It's important to handle NULL values in queries using functions like IS NULL or IS NOT NULL to ensure accurate results.

Q29.

What is Write-Ahead Logging (WAL)?

Senior

Write-Ahead Logging (WAL) is a database mechanism that ensures atomicity and durability by recording all changes in a log file before applying them to the main database.
This enables recovery from crashes by replaying or rolling back the log to maintain a consistent state.

Q30.

How do you optimize SQL queries?

Senior

SQL query optimization is the process of improving the efficiency and speed of SQL queries. Techniques include:
- Creating appropriate indexes on columns used in WHERE clauses.
- Avoiding SELECT * and fetching only necessary columns.
- Rewriting inefficient queries.
- Using EXPLAIN or query execution plans to analyze and optimize query performance.
- Using joins appropriately

Q31.

Explain database replication, its use cases, and its types.

Senior

Database replication is the process of copying data from one database to another in real-time or near-real-time. Benefits of database replication include improved availability, fault tolerance, and read scalability.
Use cases include creating high availability solutions, distributing data across geographically dispersed locations, and offloading read-heavy workloads.
Types include:
- Snapshot Replication
- Transactional Replication
- Merge Replication

Q32.

What is SQL concurrency control?

Senior

Concurrency control in SQL ensures that multiple users can access and modify data simultaneously without conflicts.
Techniques such as locking, isolation levels, and transactions are used to maintain data consistency by preventing issues like lost updates and data corruption.

Q33.

Explain SQL partitioning.

Senior

Partitioning is the practice of splitting a large table into smaller, more manageable partitions based on specific criteria (e.g., range, list, or hash).
It can improve database performance by reducing the amount of data that needs to be scanned during queries, particularly for tables with millions of records.
For example, partitioning by date can make it faster to retrieve data for a specific time period.

Q34.

Explain SQL materialized views.

Senior

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.
Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt. Neque porro quisquam est, qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit, sed quia non numquam eius modi tempora incidunt ut labore et dolore magnam aliquam quaerat voluptatem.

Q35.

Explain SQL transaction isolation levels.

Senior

Q36.

What do you understand by multi-version concurrency control?

Senior

Q37.

What is SQL query caching and how does it improve database performance?

Senior

Q38.

What are windowing functions, and how are they different from aggregate functions?

Senior

Q39.

What are the best practices for securing a SQL database?

Senior

Q40.

Name some strategies for database backup and recovery.

Senior

Q41.

How do you handle transactions in distributed databases?

Senior

Q42.

What is the difference between OLTP and OLAP systems?

Senior

About TechPrep

TechPrep has helped thousands of engineers land their dream jobs in Big Tech and beyond. Covering 60+ topics, including coding and DSA challenges, system design write-ups, and interactive quizzes, TechPrep saves you time, build your confidence, and make technical interviews a breeze.