Frequently Asked SQL Interview Questions

SQL is one of the most important technologies in backend development, database administration, production support, data engineering, and enterprise application development. Almost every technical interview involving Java, Spring Boot, backend systems, or production support includes SQL-related questions.

Interviewers usually focus on:

• Query writing
• Database concepts
• Performance optimization
• Real-world problem solving
• Joins and indexing
• Transactions
• Normalization
• Stored procedures
• Production issue handling

Below are some of the most frequently asked SQL interview questions along with concise explanations.

1. What is SQL?

SQL (Structured Query Language) is a standard language used to interact with relational databases.

SQL is used for:

• Creating databases
• Retrieving data
• Updating records
• Managing tables
• Controlling access

Popular databases using SQL:

• MySQL
• PostgreSQL
• Oracle
• SQL Server

2. What is the Difference Between SQL and MySQL?

Example:

SQL → Query language
MySQL → Database server

3. What are Different Types of SQL Commands?

SQL commands are categorized into:

Examples:

• CREATE → DDL
• INSERT → DML
• SELECT → DQL
• GRANT → DCL
• COMMIT → TCL

4. What is a Primary Key?

A Primary Key uniquely identifies each record in a table.

Features:

• Unique values
• Cannot contain NULL
• One primary key per table

Example:

CREATE TABLE employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100)
);

5. What is a Foreign Key?

A Foreign Key creates relationship between two tables.

Example:

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    FOREIGN KEY(customer_id)
    REFERENCES customers(customer_id)
);

Used to maintain:

Referential Integrity

6. What is the Difference Between DELETE, DROP, and TRUNCATE?

7. What is Normalization?

Normalization is the process of organizing data to reduce:

• Redundancy
• Duplicate data
• Data inconsistency

Common normal forms:

• 1NF
• 2NF
• 3NF
• BCNF

8. What is Denormalization?

Denormalization combines tables to improve:

• Read performance
• Query speed

Used in:

• Reporting systems
• Analytics systems

Tradeoff:

• Increased redundancy

9. What are SQL Joins?

Joins combine data from multiple tables.

Types:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• SELF JOIN
• CROSS JOIN

Example:

SELECT c.customer_name,
       o.order_id
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;

10. Difference Between INNER JOIN and LEFT JOIN

11. What is an Index?

An Index improves query performance by enabling faster data retrieval.

Example:

CREATE INDEX idx_name
ON employee(emp_name);

Indexes improve:

• SELECT queries
• JOIN performance
• Search operations

12. Types of Indexes

Common indexes:

• Primary Index
• Unique Index
• Composite Index
• Clustered Index
• Non-clustered Index

13. What is a Composite Key?

A Composite Key uses multiple columns together as a primary key.

Example:

PRIMARY KEY(order_id, product_id)

14. What is a View?

A View is a virtual table created from SQL query.

Example:

CREATE VIEW employee_view AS
SELECT emp_name, salary
FROM employee;

Benefits:

• Security
• Query simplification

15. What is a Stored Procedure?

A Stored Procedure is a precompiled SQL block stored in database.

Example:

CREATE PROCEDURE GetEmployees()
BEGIN
   SELECT * FROM employee;
END;

Benefits:

• Reusability
• Performance
• Security

16. What is a Trigger?

A Trigger automatically executes when:

• INSERT
• UPDATE
• DELETE

operations occur.

Example use cases:

• Audit logging
• Validation
• Notifications

17. What is ACID Property?

ACID ensures reliable transactions.

18. What is a Transaction?

A transaction is a group of SQL operations executed as a single unit.

Commands:

• COMMIT
• ROLLBACK
• SAVEPOINT

19. Difference Between WHERE and HAVING

Example:

SELECT dept,
COUNT(*)
FROM employee
GROUP BY dept
HAVING COUNT(*) > 5;

20. Difference Between UNION and UNION ALL

21. What is GROUP BY?

GROUP BY groups rows based on column values.

Example:

SELECT department,
COUNT(*)
FROM employee
GROUP BY department;

22. What is the Difference Between CHAR and VARCHAR?

23. What is a Subquery?

A query inside another query.

Example:

SELECT *
FROM employee
WHERE salary >
(
   SELECT AVG(salary)
   FROM employee
);

24. What is a Correlated Subquery?

A subquery dependent on outer query.

Executes repeatedly for each row.

25. What is the Difference Between Clustered and Non-Clustered Index?

26. What is a Cursor?

A Cursor processes rows one by one.

Used for:

• Row-level processing
• Complex operations

Usually avoided in high-performance systems due to slower execution.

27. What is a Deadlock?

Deadlock occurs when two transactions wait indefinitely for each other’s resources.

Solutions:

• Proper indexing
• Consistent locking order
• Short transactions

28. What is Referential Integrity?

Ensures relationship consistency between tables using:

• Primary keys
• Foreign keys

Prevents invalid references.

29. What are Aggregate Functions?

Functions performing calculations on multiple rows.

Examples:

• COUNT()
• SUM()
• AVG()
• MAX()
• MIN()

30. What is the Difference Between OLTP and OLAP?

31. What is Database Normalization vs Denormalization?

32. How Do You Optimize SQL Queries?

Common optimization techniques:

• Use indexes
• Avoid SELECT *
• Optimize joins
• Use proper WHERE conditions
• Analyze execution plans
• Avoid unnecessary subqueries

33. What is an Execution Plan?

Execution plan shows how database executes query.

Used to analyze:

• Index usage
• Full table scans
• Query cost

Very important in production support.

34. What is a Full Table Scan?

Database scans entire table to find data.

Usually occurs when:

• Index missing
• Poor query design

Can cause:

• Slow queries
• High CPU usage

35. What are Constraints in SQL?

Constraints enforce rules on data.

Types:

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• CHECK
• NOT NULL
• DEFAULT

36. What is the Difference Between EXISTS and IN?

37. What is a Candidate Key?

A column that can uniquely identify records and qualify as primary key.

38. What is a Unique Key?

Ensures unique values in column.

Difference:

• Allows one NULL value
• Multiple unique keys allowed

39. What is Database Sharding?

Sharding splits database into smaller pieces across servers.

Used for:

• Scalability
• Large distributed systems

40. What is Partitioning?

Partitioning divides large tables into smaller partitions.

Improves:

• Query performance
• Manageability

Conclusion

SQL interview questions commonly focus on database fundamentals, joins, indexing, normalization, transactions, query optimization, and real-world problem solving. Strong SQL knowledge is essential for backend development, production support, enterprise applications, and modern data-driven systems. Understanding both theoretical concepts and practical query writing is critical for clearing technical interviews and handling real-world database challenges effectively.