SQL Interview Preparation (Questions and Detailed Answers)

1. What is SQL?

Answer:
SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. It allows users to create, retrieve, update, and delete data from databases.

2. What are the main types of SQL commands?

Answer:

• DDL (Data Definition Language) – CREATE, ALTER, DROP
• DML (Data Manipulation Language) – INSERT, UPDATE, DELETE
• DQL (Data Query Language) – SELECT
• DCL (Data Control Language) – GRANT, REVOKE
• TCL (Transaction Control Language) – COMMIT, ROLLBACK

3. What is a Primary Key?

Answer:
A Primary Key is a column (or set of columns) that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.

4. What is a Foreign Key?

Answer:
A Foreign Key is a column that creates a relationship between two tables by referencing the Primary Key of another table.

5. What is Normalization?

Answer:
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

6. What are different Normal Forms?

• 1NF – Eliminate repeating groups
• 2NF – Remove partial dependency
• 3NF – Remove transitive dependency
• BCNF – Stronger version of 3NF

7. What is Denormalization?

Answer:
Denormalization is the process of combining tables to improve query performance by reducing joins.

8. What is an Index?

Answer:
An index improves the speed of data retrieval operations on a table.

CREATE INDEX idx_name
ON employees(name);

9. What are the types of Indexes?

• Clustered Index
• Non-Clustered Index
• Unique Index
• Composite Index
• Full-text Index

10. What is a View?

Answer:
A view is a virtual table based on the result of a SQL query.

CREATE VIEW employee_view AS
SELECT name, salary FROM employees;

11. What is a Stored Procedure?

Answer:
A Stored Procedure is a precompiled SQL program stored in the database that can be executed multiple times.

12. What is a Trigger?

Answer:
A trigger is automatically executed when a specified database event occurs such as INSERT, UPDATE, or DELETE.

13. What is a Cursor?

Answer:
A cursor is used to process query results row by row.

14. What is a Transaction?

Answer:
A transaction is a sequence of SQL operations executed as a single logical unit.

15. What are ACID properties?

• Atomicity – All operations succeed or none
• Consistency – Database remains valid
• Isolation – Transactions do not interfere
• Durability – Changes remain after commit

16. What is the difference between WHERE and HAVING?

17. What are Joins?

Answer:
Joins combine rows from two or more tables based on a related column.

18. Types of Joins

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• CROSS JOIN

19. Example of INNER JOIN

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;

20. What is a Subquery?

Answer:
A subquery is a query inside another query.

SELECT name
FROM employees
WHERE salary > 
(SELECT AVG(salary) FROM employees);

21. What is a Correlated Subquery?

A subquery that depends on the outer query.

22. What is a Self Join?

A join where a table is joined with itself.

23. What is a CTE (Common Table Expression)?

WITH EmployeeCTE AS
(
SELECT name, salary
FROM employees
)
SELECT * FROM EmployeeCTE;

24. What is Window Function?

Window functions perform calculations across a set of rows related to the current row.

25. Example of ROW_NUMBER()

SELECT name,
ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

26. What is RANK() function?

RANK() assigns ranking to rows with gaps in ranking.

27. What is DENSE_RANK()?

Similar to RANK but without gaps.

28. What is NTILE()?

Divides rows into equal groups.

29. What is LEAD()?

Access next row value.

30. What is LAG()?

Access previous row value.

31. What is UNION?

Combines results of two queries and removes duplicates.

32. What is UNION ALL?

Combines results but keeps duplicates.

33. Difference between DELETE, TRUNCATE, DROP

34. What is a Composite Key?

A key made of multiple columns.

35. What is a Unique Key?

Ensures column values remain unique.

36. What is a Check Constraint?

ALTER TABLE employees
ADD CONSTRAINT salary_check
CHECK (salary > 0);

37. What is Default Constraint?

salary INT DEFAULT 10000

38. What is NULL?

Represents missing or unknown value.

39. Difference between NULL and 0?

NULL means unknown value while 0 is a numeric value.

40. What is COALESCE?

SELECT COALESCE(NULL, 'Default Value');

41. What is Query Optimization?

Answer:
Query Optimization is the process of improving the performance of SQL queries so that the database retrieves data faster and uses fewer system resources. The database optimizer chooses the most efficient execution plan based on indexes, statistics, and table structures.

42. What is an Execution Plan?

Answer:
An Execution Plan shows how a database engine executes a SQL query. It describes the sequence of operations such as table scans, index usage, joins, and sorting.

43. What is the difference between Index Scan and Index Seek?

• Index Seek: Directly finds specific rows using an index. It is faster.
• Index Scan: Reads the entire index to find matching rows. It is slower.

44. What is a Deadlock?

Answer:
A deadlock occurs when two or more transactions block each other by holding resources that the other transaction needs.

45. How can Deadlocks be avoided?

• Access tables in the same order
• Keep transactions short
• Use proper indexes
• Avoid unnecessary locks

46. What is Database Partitioning?

Answer:
Partitioning divides a large table into smaller pieces called partitions to improve performance and manageability.

47. What is Sharding?

Answer:
Sharding is a database architecture where data is distributed across multiple servers to improve scalability and performance.

48. What is a Materialized View?

Answer:
A Materialized View stores the result of a query physically in the database, unlike a normal view which is virtual. It improves query performance.

49. What is Data Warehousing?

Answer:
A Data Warehouse is a system used for reporting and data analysis. It stores large volumes of historical data from multiple sources.

50. What is the difference between OLTP and OLAP?

51. What is Star Schema?

Answer:
Star Schema is a database design used in data warehouses where a central fact table is connected to multiple dimension tables.

52. What is Snowflake Schema?

Answer:
Snowflake Schema is a normalized version of Star Schema where dimension tables are further divided into sub-dimensions.

53. What is a Fact Table?

Answer:
A Fact Table stores quantitative data such as sales amount, revenue, and transaction counts.

54. What is a Dimension Table?

Answer:
A Dimension Table stores descriptive attributes such as product name, customer details, and location.

55. What is a Data Mart?

Answer:
A Data Mart is a smaller section of a data warehouse designed for a specific department such as finance or marketing.

56. What is ETL?

Answer:
ETL stands for Extract, Transform, Load.

• Extract data from sources
• Transform data into proper format
• Load data into a data warehouse

57. What is Incremental Load?

Answer:
Incremental Load means loading only new or updated data instead of loading the entire dataset again.

58. What is a Data Lake?

Answer:
A Data Lake is a storage system that holds structured, semi-structured, and unstructured data in its raw format.

59. What is Big Data SQL?

Answer:
Big Data SQL allows SQL queries to run on large distributed data platforms such as Hadoop or Spark.

60. What is a Column Store Index?

Answer:
A Column Store Index stores data column-wise instead of row-wise and improves performance for analytical queries.

61. How to find the Second Highest Salary?

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

62. How to find Duplicate Records?

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

63. How to Delete Duplicate Rows?

DELETE FROM employees
WHERE id NOT IN
(
SELECT MIN(id)
FROM employees
GROUP BY name
);

64. How to find Employees without Department?

SELECT *
FROM employees
WHERE department_id IS NULL;

65. Highest Salary in Each Department

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;

66. Find Nth Highest Salary

SELECT salary
FROM
(
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_num
FROM employees
) t
WHERE rank_num = 3;

67. What is Pivot in SQL?

Answer:
Pivot converts rows into columns for better reporting.

68. What is Unpivot?

Answer:
Unpivot converts columns into rows.

69. Find Missing Numbers in a Sequence

SELECT number + 1
FROM numbers
WHERE number + 1 NOT IN (SELECT number FROM numbers);

70. Running Total Example

SELECT id,
SUM(amount) OVER (ORDER BY id) AS running_total
FROM sales;

71. Find Top 5 Records

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;

72. Find Bottom 5 Records

SELECT *
FROM employees
ORDER BY salary ASC
LIMIT 5;

73. Employees hired in Last 30 Days

SELECT *
FROM employees
WHERE hire_date >= CURRENT_DATE - INTERVAL '30 days';

74. Count Employees per Department

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

75. Find Employees with Same Salary

SELECT salary
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;

76. Detect Gaps in Sequence

SELECT id + 1
FROM employees
WHERE id + 1 NOT IN (SELECT id FROM employees);

77. Moving Average Example

SELECT id,
AVG(salary) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
FROM employees;

78. Manager Hierarchy Query

WITH RECURSIVE employee_hierarchy AS (
SELECT id, manager_id, name
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.manager_id, e.name
FROM employees e
JOIN employee_hierarchy eh
ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

79. Rank Employees by Salary

SELECT name,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

80. Detect Duplicate Emails

SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

81. What is SQL Query Execution Order?

Answer:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

82. What is SQL Injection?

Answer:
SQL Injection is a security vulnerability where attackers insert malicious SQL code into queries.

83. How to Prevent SQL Injection?

• Use Prepared Statements
• Use Parameterized Queries
• Validate user input

84. What is Database Replication?

Answer:
Replication copies data from one database server to another to improve availability and performance.

85. What is Log Shipping?

Answer:
Log Shipping automatically sends transaction logs from a primary database to a secondary database.

86. What is Change Data Capture?

Answer:
CDC tracks and captures changes made to database tables.

87. What is a Temporal Table?

Answer:
A Temporal Table stores historical versions of data automatically.

88. What is a Recursive Query?

Answer:
A recursive query repeatedly executes itself until a condition is met.

89. What is a Graph Database?

Answer:
A Graph Database stores data in nodes and relationships rather than tables.

90. What is JSON Support in SQL?

Answer:
Modern SQL databases support JSON data storage and querying.

91. What is XML Support in SQL?

Answer:
SQL databases can store and query XML formatted data.

92. What is Data Masking?

Answer:
Data masking hides sensitive information such as credit card numbers.

93. What is Row Level Security?

Answer:
Row Level Security restricts access to specific rows in a table.

94. What is Parallel Query?

Answer:
Parallel queries divide tasks into multiple operations executed simultaneously.

95. What is Query Hint?

Answer:
Query hints instruct the optimizer to use specific execution strategies.

96. What is Bulk Insert?

BULK INSERT employees
FROM 'employees.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

97. What is a Database Snapshot?

Answer:
A snapshot is a read-only copy of a database at a specific time.

98. What is Data Compression?

Answer:
Data compression reduces storage space and improves performance.

99. What is an In-Memory Database?

Answer:
An in-memory database stores data in RAM instead of disk for faster performance.

100. What is a Cloud Database?

Answer:
A Cloud Database is a database hosted on cloud platforms such as AWS, Azure, or Google Cloud.