Top 20 Database SQL Interview Questions and Answers You Should Know

Image Source: unsplash

Preparing for technical interviews in 2025 matters more than ever. SQL shows up in over 70% of job descriptions for roles like Data Engineer and Backend Developer. Candidates often see questions about:

• Data retrieval and filtering

• Aggregate functions

• Joins, subqueries, and indexes

A guide with Database SQL Interview Questions Answers, cheat sheets, and real query examples helps candidates build confidence.

1. What is SQL?

Image Source: pexels

Definition

SQL stands for Structured Query Language. It is the standard language for managing and interacting with relational databases. People use SQL to create, read, update, and delete data stored in tables. Most modern database systems, like MySQL, PostgreSQL, and Microsoft SQL Server, support SQL. This language helps users work with large amounts of data in a simple and organized way.

Purpose

SQL plays a key role in database management. It gives users the power to control and organize data with ease. Here are some main purposes of SQL in today’s database systems:

• SQL lets users manipulate data through queries and subqueries. They can search, filter, and change records quickly.

• It keeps data safe and accurate by checking user permissions and maintaining links between tables using primary and foreign keys.

• SQL boosts performance by using a relational engine. This engine picks the best way to run each query.

Anyone preparing for Database SQL Interview Questions Answers will see how important SQL is for both simple and complex tasks. Mastering SQL helps candidates solve real-world problems and impress interviewers.

2. Types of SQL Statements

SQL statements fall into four main categories. Each type serves a different purpose in database management. Interviewers often ask about these in Database SQL Interview Questions Answers, so knowing them helps candidates stand out.

DDL, DML, DCL, TCL

Here is a quick overview of the four types:

• DML (Data Manipulation Language): These statements let users work with the data itself. They can add, change, or remove records from tables.

• DDL (Data Definition Language): These commands help users define and change the structure of database objects like tables and indexes.

• DCL (Data Control Language): These statements manage access to data. They control who can see or change information.

• TCL (Transaction Control Language): These commands handle transactions. They make sure changes to the database happen safely and can be undone if needed.

Tip: Interviewers may ask for examples of each type. Practicing simple queries for every category can boost confidence.

3. Primary Key

Definition

A primary key is a special column or set of columns in a database table. It helps the database identify each record uniquely. Every table should have one primary key. This key cannot contain duplicate values or nulls. People often see questions about primary keys in Database SQL Interview Questions Answers because they play a big role in keeping data organized and reliable.

Here’s a quick look at what makes a primary key important:

A primary key can be simple, using just one column, or composite, using more than one column. The database uses this key to find, update, or delete records quickly.

Tip: Interviewers may ask about the difference between a primary key and a candidate key. A primary key is a candidate key chosen to uniquely identify records.

Example

Let’s look at a sample employee table. The column emp_no acts as the primary key. It makes sure each employee has a unique number.

In this table, emp_no never repeats and never stays empty. The database uses it to keep track of each employee. If someone tries to add another employee with the same number, the database will not allow it.

4. Foreign Key

Definition

A foreign key connects two tables in a database. It acts like a bridge, linking a column in one table to the primary key in another. This connection keeps data accurate and organized. The foreign key makes sure that every value in its column matches a value in the referenced table. This rule is called referential integrity.

Here’s a quick look at what a foreign key does:

Referential integrity ensures that every ‘customer ID’ value in the order table corresponds to a valid ‘customer ID’ value in the customer table. It prevents the insertion of an order with a non-existent customer or the deletion of a customer record that has associated orders.

Example

Foreign keys show up in many real-world databases. In an e-commerce system, a foreign key might link an order to a customer. This setup guarantees that each order belongs to a real customer.

• In an e-commerce database, a foreign key might link an order table to a customer table, ensuring that every order is associated with a valid customer.

Here’s how foreign keys work in practice:

1. The orders table has a foreign key orders.user_id that references users.user_id, making sure every order connects to a registered user.

2. The orders table also has a foreign key orders.product_sku that references books.product_sku, so users can only order products that exist in the database.

Database SQL Interview Questions Answers often include foreign key scenarios because they test a candidate’s understanding of data relationships and integrity.

5. Normalization

Definition

Normalization in relational databases means structuring data to reduce repetition and improve accuracy. Database designers organize columns and tables into different normal forms. This process helps enforce proper relationships and dependencies between data. When a database follows normalization rules, it stores each piece of information only once. As a result, the database becomes easier to manage and less likely to contain errors.

• Normalization reduces data redundancy.

• It improves data integrity.

• It organizes columns and tables based on normal forms.

For example, instead of storing a customer’s address in every order record, the database keeps the address in one place. Orders then link to the customer’s record. This setup makes updates simple and keeps information consistent.

Importance

Normalization plays a big role in building strong databases. It solves many common problems that can appear in poorly designed systems. Here’s a quick look at why normalization matters:

Tip: Interviewers often ask about normalization because it shows a candidate’s ability to design databases that are both efficient and reliable. Knowing when and how to normalize helps avoid problems like update anomalies and inconsistent data.

6. Denormalization

Definition

Denormalization means combining data from multiple tables into one. This process adds some redundancy to the database. The main goal is to make data retrieval faster and easier. While normalization removes duplicate data and keeps everything consistent, denormalization does the opposite. It brings related data together, so users can run queries without joining many tables.

Here’s a quick comparison between normalization and denormalization:

Denormalization often appears in Database SQL Interview Questions Answers because it helps boost query speed, especially in large databases.

Use Cases

Denormalization works best in situations where speed matters more than perfect data organization. Many systems use denormalized tables to handle heavy workloads and complex queries. Here are some common use cases:

• Online Analytical Processing (OLAP): Analysts need to run complex queries on large datasets. Denormalized tables help them get results quickly.

• Reporting and Dashboards: Business users want fast access to summarized data. Denormalization reduces the need for multiple joins, making reports load faster.

• Content Management Systems (CMS): Users expect instant access to articles and related details. Denormalized structures allow quick retrieval.

• Real-time Analytics: Companies analyze streaming data for immediate insights. Denormalization supports rapid querying and analysis.

Tip: Denormalization can make databases run faster, but it may increase storage needs and risk data inconsistency. Developers should choose this approach when performance is a top priority.

7. Indexes

Definition

An index in SQL databases acts like a shortcut for finding data. It works as a pointer to information in a table, helping users locate rows faster. Think of an index as similar to the index at the back of a book—it guides readers to the right page quickly. In technical terms, an index is an on-disk structure linked to a table or view. It uses keys from one or more columns and organizes them in a B-tree structure. This setup allows the database to retrieve rows with speed and efficiency.

Interviewers often include index-related topics in Database SQL Interview Questions Answers because indexes play a big role in database performance.

Performance Impact

Indexes can make a huge difference in how fast queries run. They help users search, filter, and sort data quickly, especially in large tables. Here are some pros and cons of using indexes:

• Indexes work best for high-volume read operations.

• They help with frequent filtering, sorting, and joining.

• However, indexes add overhead when users insert, update, or delete data.

• Too many indexes can slow down write operations and use more storage.

Tip: Candidates should know when to use indexes and when to avoid them. This knowledge often appears in Database SQL Interview Questions Answers, so practicing with real queries helps.

8. JOINs

SQL JOINs help users combine data from two or more tables. They play a big role in almost every database interview. Interviewers often ask candidates to explain JOIN types and write sample queries. JOINs let people answer questions like, “Which customers placed orders?” or “Who manages each employee?”

Types

Several JOIN types exist in SQL. Each one serves a different purpose. The table below shows the most common JOINs, their definitions, and when to use them:

Tip: Interviewers love to ask about JOINs because they test a candidate’s ability to work with real-world data.

Examples

Here are some practical examples of JOIN queries. These show how JOINs combine data from multiple tables:

• INNER JOIN

  1 2 3 4

  SELECT customers.name, orders.order_id FROM customers INNER JOIN orders ON customers.id = orders.customer_id;

  This query lists customers who have placed orders.

• LEFT JOIN

  1 2 3 4

  SELECT customers.name, orders.order_id FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;

  This query lists all customers, even those who have not placed orders.

• RIGHT JOIN

  1 2 3 4

  SELECT employees.name, projects.project_name FROM employees RIGHT JOIN projects ON employees.project_id = projects.id;

  This query lists all projects, even if some have no assigned employees.

• FULL JOIN

  1 2 3 4

  SELECT a.name, b.order_id FROM customers a FULL JOIN orders b ON a.id = b.customer_id;

  This query shows all customers and all orders, including those without matches.

Note: JOINs help users answer complex questions with simple queries. Practicing JOINs builds strong SQL skills for interviews.

9. INNER vs LEFT JOIN

Differences

INNER JOIN and LEFT JOIN often confuse candidates during interviews. They both combine data from two tables, but they work in different ways. Here’s a simple breakdown:

• INNER JOIN returns only the rows where both tables have matching values. If a record exists in one table but not the other, SQL leaves it out.

• LEFT JOIN keeps every row from the left table. If there’s no match in the right table, SQL fills in the missing values with NULL.

Tip: If someone wants to see all employees, even those without a department, LEFT JOIN works best. If they only want employees who belong to a department, INNER JOIN is the right choice.

Sample Query

Let’s look at two queries that show the difference between INNER JOIN and LEFT JOIN. Both use an employees table and a departments table.

• The INNER JOIN query lists only employees who have a department.

• The LEFT JOIN query lists all employees. If someone like David does not belong to any department, SQL shows his name with NULL for the department.

Remember: LEFT JOIN helps spot missing links, while INNER JOIN focuses on matches. This difference often appears in database SQL interview questions.

10. Subquery

Definition

A subquery in SQL works like a query inside another query. People also call it a nested query or inner query. Subqueries help users solve problems that need more than one step. They let someone use the result of one query as input for another. This makes complex questions easier to answer.

Here are some key points about subqueries:

• A subquery is a query within another SQL statement.

• Subqueries can appear in SELECT, FROM, or WHERE clauses.

• They help retrieve data based on another query’s result.

• Users often use subqueries for comparisons, aggregations, or to simplify complex logic.

Subqueries make SQL powerful. They allow users to break down tough problems into smaller parts. This helps keep queries clean and easy to read.

Tip: Interviewers like to ask about subqueries because they show how well a candidate can handle advanced SQL tasks.

Example

Let’s look at a real-world example. Suppose someone wants to find out how many paintings each collector has purchased. A subquery in the SELECT clause can solve this problem.

To calculate the number of paintings purchased by each collector, use a subquery like this:

1 2 3 4 5 6 7 8 9 10

SELECT > first_name, > last_name, > ( > SELECT count(*) AS paintings > FROM sales > WHERE collectors.id = sales.collector_id > ) > FROM collectors; >

This subquery runs for each row in the collectors table. It counts the paintings for each collector and shows the result next to their name.

Subqueries help users answer questions that need data from more than one table. They make SQL flexible and powerful for interviews and real projects.

11. Views vs Tables

Differences

Views and tables often confuse people learning SQL. They look similar, but they work in different ways. A table stores data physically inside the database. It uses rows and columns to keep information safe and organized. A view, on the other hand, acts like a virtual table. It does not store data itself. Instead, it shows results from a query every time someone asks for it.

Here’s a quick comparison:

A table is the backbone of any database. It holds all the important data. A view helps users see only what they need, making work easier and safer.

Tip: Views can hide sensitive columns and simplify complex queries. They help teams share data without giving away too much.

Use Cases

Views shine in many real-world scenarios. They make life easier for developers and analysts. Here are some common ways people use views:

• Select data from multiple tables at once.

• Show only a subset of columns or rows.

• Hide joins and present a simple data model.

• Restrict access to sensitive information.

• Aggregate data, like calculating totals or averages.

• Simplify reporting by joining tables into one view.

• Limit exposure of underlying tables by setting permissions on views.

Views take up very little space because they only store the query definition. They help teams work faster and safer, especially when handling complex or sensitive data.

12. Stored Procedure

Definition

A stored procedure in SQL acts like a recipe for the database. It contains a set of SQL statements that run together as a single unit. Developers give each procedure a name, so they can call it whenever they need to perform a task. Stored procedures help teams work faster and safer. They keep code organized and make it easier to manage changes.

People use stored procedures for many reasons:

• They boost security by controlling who can run certain database operations.

• Teams break big tasks into smaller pieces, making code easier to maintain.

• Stored procedures cut down on network traffic because the database does more work on its own.

• Routine jobs, like updating records or cleaning up data, become automatic.

• Business rules and logic stay inside the database, so everyone follows the same rules.

Tip: Interviewers often ask about stored procedures because they show how well a candidate can organize and automate database tasks.

Syntax

Different SQL systems use their own syntax for creating stored procedures. The table below shows how popular platforms handle this:

Stored procedures help teams keep their databases running smoothly. They make complex tasks simple and repeatable. Anyone preparing for SQL interviews should know how to create and use stored procedures in different systems.

13. Trigger

Definition

A trigger in SQL works like an automatic alarm for the database. When someone changes data in a table, the trigger jumps into action. It runs a set of instructions without anyone needing to start it. Triggers help teams keep data safe and consistent. They also make sure important tasks happen every time data changes.

Here’s a quick look at what triggers do:

Triggers come in handy for many reasons. They can check if new data follows the rules. They might update another table when someone deletes a record. Some triggers even keep a log of every change. Teams use triggers to make sure the database always stays in good shape.

Tip: Triggers run on their own. No one needs to call them. This makes them perfect for keeping data accurate and handling routine jobs.

Syntax

SQL offers different ways to create triggers. The syntax depends on the type of trigger and the database system. Most triggers respond to changes like INSERT, UPDATE, or DELETE. Some even react to changes in the database structure or user logins.

Here’s a table showing common trigger types and their syntax:

A simple DML trigger might look like this:

1
2
3
4
5
6
7
8

CREATE TRIGGER trg_after_insert
ON employees
AFTER INSERT
AS
BEGIN
    INSERT INTO audit_log (change_type, change_date)
    VALUES ('INSERT', GETDATE());
END;

This trigger adds a record to the audit log every time someone inserts a new employee. Triggers help teams automate important tasks and keep the database running smoothly.

14. ACID Properties

Image Source: unsplash

Definition

ACID properties form the backbone of reliable database transactions. They help databases handle changes safely, even when something goes wrong. The word “ACID” stands for Atomicity, Consistency, Isolation, and Durability. Each property plays a unique role in keeping data safe and accurate.

Here’s a quick look at what each ACID property means:

Atomicity makes sure that a transaction completes fully or not at all. Consistency keeps the database rules in place. Isolation lets transactions run side by side without mixing up results. Durability protects data from loss, even if the system crashes.

Importance

ACID properties matter a lot in real-world databases. They keep data safe and trustworthy, especially when many users work at the same time. Without ACID, databases could lose information or show wrong results.

• Atomicity prevents partial updates from failed transactions. If someone tries to transfer money and the process fails halfway, the database rolls back the change. No money gets lost or duplicated.

• Consistency enforces data quality rules. Every transaction follows the rules set by the database, so data stays accurate.

• Isolation prevents transactions from interfering with each other. If two people update the same record, each change happens separately until finished.

• Durability ensures data permanence even after failures. Once a transaction is complete, the data stays safe, even if the power goes out.

ACID properties help teams build strong, reliable systems. They make sure users can trust the data, no matter what happens behind the scenes.

15. DELETE vs TRUNCATE

Differences

SQL offers two commands for removing data: DELETE and TRUNCATE. They might look similar, but they work in different ways. DELETE lets users remove specific rows from a table. TRUNCATE wipes out all rows at once. People often wonder which command to use, especially when speed matters.

Here’s a table that highlights the main differences:

DELETE works well when someone needs to remove only a few records. TRUNCATE is the better choice for clearing out a whole table quickly. TRUNCATE skips tracking each deleted row, so it finishes faster. Both commands allow users to undo changes if they use transactions.

Tip: If a team wants to keep a record of deleted rows or use conditions, DELETE is the way to go. For a fresh start with an empty table, TRUNCATE saves time.

Example

Let’s look at two sample queries. These show how DELETE and TRUNCATE work in practice:

DELETE targets specific rows using a condition. TRUNCATE removes all rows, leaving the table structure in place. Teams choose the command based on their needs—precision or speed.

16. Query Optimization

Tips

Query optimization helps databases run faster and use fewer resources. Interviewers often ask about this topic because it shows how well a candidate can write efficient SQL. Here are some practical tips for optimizing queries:

• Use SELECT only for needed columns
  Instead of SELECT *, list the columns you need. This reduces the amount of data the database returns.

• Add indexes to frequently searched columns
  Indexes speed up searches and filtering. They work best on columns used in WHERE, JOIN, or ORDER BY clauses.

• Write simple queries
  Break complex queries into smaller steps. This makes them easier to read and debug.

• Avoid unnecessary joins
  Use joins only when you need data from multiple tables. Extra joins slow down performance.

• Filter early
  Use WHERE clauses to limit rows before joining or grouping. This reduces the workload for the database.

Tip: Always test queries with sample data. Small changes can make a big difference in speed.

Common Pitfalls

Many candidates make mistakes that slow down their queries. Here’s a table showing common pitfalls and how to avoid them:

Slow queries frustrate users and waste resources. Candidates should watch for these mistakes and fix them before interviews. Query optimization skills help them stand out and solve real-world problems.

17. Transactions

A transaction in SQL groups several operations into a single unit. This means the database treats all the steps as one action. If every step works, the database saves the changes. If something fails, the database undoes everything. Transactions help keep data safe and accurate, especially when many users work at the same time.

COMMIT

The COMMIT command tells the database to save all changes made during the transaction. Once a user commits, the changes become permanent. No one can undo them with a simple command. Teams use COMMIT when they know every step in the transaction worked as planned.

Example:

1
2
3
4

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

In this example, the database moves money from one account to another. After both updates, the COMMIT command saves the changes.

Tip: Always check that every part of the transaction works before using COMMIT. Mistakes become permanent after this step.

ROLLBACK

The ROLLBACK command cancels all changes made in the current transaction. If something goes wrong, users can use ROLLBACK to undo every step. This keeps the database safe from errors or incomplete updates.

Example:

1
2
3
4

BEGIN TRANSACTION;
UPDATE products SET stock = stock - 5 WHERE id = 10;
-- Oops! Something went wrong
ROLLBACK;

Here, the database undoes the stock update because of a problem. ROLLBACK protects the data from mistakes.

Remember: Transactions give users control. Use COMMIT to save changes and ROLLBACK to undo them. This helps keep the database reliable and accurate.

18. Clustered vs Non-Clustered Index

Differences

Clustered and non-clustered indexes help databases find data faster. They work in different ways. A clustered index sorts and stores the actual table rows based on the index key. The table can have only one clustered index. A non-clustered index creates a separate structure that points to the table rows. Tables can have many non-clustered indexes.

Here’s a quick comparison:

Tip: Clustered indexes work best for columns that sort or filter large amounts of data. Non-clustered indexes help with quick searches on other columns.

Use Cases

Database designers pick indexes based on how people use the data. They choose clustered indexes for columns that organize the table, like primary keys. Non-clustered indexes help with columns used in searches or joins.

Common use cases:

• Clustered index on an order_id column in an orders table. This helps sort and find orders quickly.

• Non-clustered index on an email column in a users table. This speeds up searches for users by email.

• Clustered index for date columns in logs. This makes it easy to find records from a certain time.

• Non-clustered index for foreign keys. This helps join tables faster.

Designers often mix both index types to balance speed and storage. They look at how people query the database and pick the best option for each column.

19. Handling NULL Values

Dealing with NULL values in SQL can feel tricky, but everyone faces this challenge. NULL means a missing or unknown value in a database. Interviewers often ask about handling NULLs because mistakes can lead to wrong results. People who prepare for Database SQL Interview Questions Answers should know how to work with NULLs confidently.

Techniques

SQL offers several ways to handle NULL values. Here are some common techniques:

• IS NULL and IS NOT NULL: These operators help find rows with missing data.

• COALESCE(): This function returns the first non-NULL value from a list.

• IFNULL() or NVL(): These functions replace NULL with a default value.

• CASE Statement: This lets users create custom rules for NULLs.

Tip: Always check for NULLs before running calculations. Ignoring them can cause errors or unexpected results.

Here’s a quick table showing popular techniques:

Example

Suppose a school tracks student scores. Some students miss the test, so their scores are NULL. The teacher wants to show “Absent” instead of a blank space. SQL can help:

1
2
3
4

SELECT
  name,
  COALESCE(score, 'Absent') AS final_score
FROM students;

This query lists each student’s name and score. If the score is missing, SQL shows “Absent.” Handling NULLs this way keeps reports clear and easy to read.

20. SQL Functions

COUNT, SUM, AVG

SQL functions help users get quick answers from data. Three of the most common are COUNT, SUM, and AVG. These functions appear in almost every set of Database SQL Interview Questions Answers.

• COUNT: Finds the number of rows that match a condition.

• SUM: Adds up values in a column.

• AVG: Calculates the average value for a column.

These functions work well with GROUP BY to summarize data. For example, a manager might want to know how many orders each customer placed or the average salary in each department.

Tip: Interviewers often ask about these functions because they show how well a candidate can analyze data.

Examples

Here are some practical SQL queries that interviewers love to see:

• Find the second highest salary

  1 2 3 4

  SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

• List employees with the same manager

  1 2 3 4 5

  SELECT manager_id, COUNT(*) AS num_employees FROM employees GROUP BY manager_id HAVING COUNT(*) > 1;

• Count total orders per customer

  1 2 3 4

  SELECT customer_id, COUNT(order_id) AS total_orders FROM orders GROUP BY customer_id;

These examples help candidates prepare for Database SQL Interview Questions Answers. Practicing these queries builds confidence and shows real-world problem-solving skills.

Mastering these top 20 database SQL interview questions and answers gives candidates a real edge in 2025.

• Regular practice with sample queries builds confidence.

• Using this guide as a checklist helps with quick review.

  Stay curious! Keep up with new SQL trends and real-world examples for the best results.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL

• SQLFlash + PostgreSQL