Top 20 SQL Interview Questions and Answers for 2025

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases.

Main types of SQL statements:

1. DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE - defines database structure
2. DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE - manipulates data
3. DCL (Data Control Language): GRANT, REVOKE - controls access permissions
4. TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT - manages transactions

Example:

DDL (Data Definition Language):

1. Defines database structure and schema
2. Commands: CREATE, ALTER, DROP, TRUNCATE, RENAME
3. Auto-committed (cannot be rolled back)
4. Affects the structure of the database

DML (Data Manipulation Language):

1. Manipulates data within database objects
2. Commands: SELECT, INSERT, UPDATE, DELETE
3. Not auto-committed (can be rolled back)
4. Affects the data in the database

DCL (Data Control Language):

1. Controls access and permissions
2. Commands: GRANT, REVOKE
3. Manages user privileges

TCL (Transaction Control Language):

1. Manages transactions and database consistency
2. Commands: COMMIT, ROLLBACK, SAVEPOINT
3. Controls transaction behavior

DELETE:

1. DML command - removes rows from a table
2. Can use WHERE clause to delete specific rows
3. Can be rolled back (if not committed)
4. Maintains table structure
5. Slower for large datasets
6. Triggers are fired

TRUNCATE:

1. DDL command - removes all rows from a table
2. Cannot use WHERE clause
3. Cannot be rolled back
4. Maintains table structure
5. Faster than DELETE
6. Resets auto-increment counter
7. Triggers are NOT fired

DROP:

1. DDL command - removes entire table/database
2. Removes table structure and all data
3. Cannot be rolled back
4. Frees up space completely

Example:

WHERE Clause:

1. Filters rows before grouping
2. Used with SELECT, UPDATE, DELETE
3. Cannot use aggregate functions
4. Applied to individual rows
5. Used before GROUP BY

HAVING Clause:

1. Filters groups after grouping
2. Used only with SELECT and GROUP BY
3. Can use aggregate functions (COUNT, SUM, AVG, etc.)
4. Applied to groups
5. Used after GROUP BY

Example:

PRIMARY KEY:

1. Uniquely identifies each row in a table
2. Cannot contain NULL values
3. Only one PRIMARY KEY per table
4. Automatically creates a unique index
5. Ensures entity integrity

FOREIGN KEY:

1. References PRIMARY KEY of another table
2. Maintains referential integrity
3. Can contain NULL values
4. Multiple FOREIGN KEYs allowed per table
5. Prevents invalid data insertion

Example:

INNER JOIN:

1. Returns only matching rows from both tables
2. Excludes non-matching rows

LEFT JOIN (LEFT OUTER JOIN):

1. Returns all rows from left table
2. Returns matching rows from right table
3. NULL for non-matching right table rows

RIGHT JOIN (RIGHT OUTER JOIN):

1. Returns all rows from right table
2. Returns matching rows from left table
3. NULL for non-matching left table rows

FULL JOIN (FULL OUTER JOIN):

1. Returns all rows from both tables
2. NULL for non-matching rows from either table

Example:

GROUP BY:

1. Groups rows with same values into summary rows
2. Used with aggregate functions (COUNT, SUM, AVG, MAX, MIN)
3. Reduces multiple rows into single rows

ORDER BY:

1. Sorts the result set in ascending or descending order
2. Can sort by one or more columns
3. Default is ASC (ascending)

Example:

Subqueries are queries nested inside another query. They can be used in SELECT, FROM, WHERE, and HAVING clauses.

Types:

1. Scalar Subquery: Returns single value
2. Row Subquery: Returns single row
3. Column Subquery: Returns single column
4. Table Subquery: Returns table (used in FROM clause)

When to use:

1. When you need data from multiple tables
2. To filter based on aggregated values
3. To compare values with aggregated results
4. When JOIN might be complex

Example:

A view is a virtual table based on the result of a SQL query. It doesn't store data but displays data from underlying tables.

Advantages:

1. Simplifies complex queries
2. Provides security by hiding sensitive data
3. Allows data abstraction
4. Can be used like a regular table

Types:

1. Simple View: Based on single table
2. Complex View: Based on multiple tables, includes functions

Example:

Indexes are database objects that improve query performance by providing faster data retrieval.

Purpose:

1. Speed up SELECT queries
2. Enforce uniqueness (UNIQUE index)
3. Improve JOIN performance
4. Speed up ORDER BY and GROUP BY operations

Advantages:

1. Faster data retrieval
2. Improved query performance
3. Enforces uniqueness
4. Speeds up sorting and grouping

Disadvantages:

1. Additional storage space required
2. Slower INSERT, UPDATE, DELETE operations (indexes need to be updated)
3. Maintenance overhead
4. More indexes = more storage and slower writes

Example:

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

1NF (First Normal Form):

1. Each column contains atomic values (no multi-valued attributes)
2. Each row is unique
3. No repeating groups

2NF (Second Normal Form):

1. Must be in 1NF
2. All non-key attributes fully dependent on primary key
3. No partial dependencies

3NF (Third Normal Form):

1. Must be in 2NF
2. No transitive dependencies
3. Non-key attributes independent of each other

BCNF (Boyce-Codd Normal Form):

1. Must be in 3NF
2. Every determinant is a candidate key
3. Stricter than 3NF

Example: A table with employee_id, employee_name, department_id, department_name violates 3NF because department_name depends on department_id, not directly on employee_id. Solution: Split into employees and departments tables.

Stored Procedures:

1. Pre-compiled SQL code stored in database
2. Can return zero, one, or multiple values
3. Can have input and output parameters
4. Can contain DML statements (INSERT, UPDATE, DELETE)
5. Cannot be used in SELECT statements
6. Can call functions
7. Better for complex business logic

Functions:

1. Must return a single value or table
2. Can have only input parameters
3. Cannot contain DML statements (only SELECT)
4. Can be used in SELECT statements
5. Cannot call stored procedures
6. Better for calculations and data retrieval

Example:

ACID properties ensure reliable database transactions:

A - Atomicity:

1. Transaction is all-or-nothing
2. Either all operations succeed or all fail
3. No partial transactions

C - Consistency:

1. Database remains in valid state before and after transaction
2. All constraints and rules are maintained
3. No data corruption

I - Isolation:

1. Concurrent transactions don't interfere with each other
2. Each transaction sees consistent data
3. Prevents dirty reads, phantom reads

D - Durability:

1. Committed transactions persist even after system failure
2. Changes are permanently saved
3. Uses transaction logs for recovery

Example: When transferring money between accounts, either both accounts are updated (commit) or neither is updated (rollback), ensuring data consistency.

A trigger is a stored procedure that automatically executes in response to specific events (INSERT, UPDATE, DELETE) on a table.

Types:

1. BEFORE Trigger: Executes before the event
2. AFTER Trigger: Executes after the event
3. INSTEAD OF Trigger: Replaces the event (used with views)

When to use:

1. Audit logging (track changes)
2. Data validation
3. Enforcing business rules
4. Maintaining referential integrity
5. Automatic calculations

Example:

Clustered Index:

1. Determines the physical order of data in a table
2. Only one clustered index per table
3. Data rows are stored in sorted order
4. Faster for range queries
5. Primary key is typically a clustered index
6. Slower for INSERT operations (data needs to be reordered)

Non-Clustered Index:

1. Does not affect physical order of data
2. Multiple non-clustered indexes allowed per table
3. Contains pointers to data rows
4. Separate structure from table data
5. Faster for INSERT operations
6. Requires additional storage space

Example:

Key Difference: Clustered index = table data is sorted, Non-clustered index = separate index structure with pointers to data.

Method 1: Using Subquery

Method 2: Using LIMIT/OFFSET

Method 3: Using ROW_NUMBER()

Method 4: Using DENSE_RANK() (handles ties)

Basic Query:

With Department Names (JOIN):

With Ordering:

With Filtering (HAVING):

Method 1: Using GROUP BY and HAVING

Method 2: Using Window Functions

Method 3: Using Self-Join

To Delete Duplicates (keep one):

Update with WHERE condition:

Update with CASE statement:

Update with JOIN:

Update with Subquery:

Using BETWEEN:

Using Comparison Operators:

Using DATE functions (MySQL):

Last 30 days:

Current month: