Top 20 SQL Interview Questions and Answers for 2025

Master SQL interview questions: What is the difference between DELETE, TRUNCATE, and DROP? Explain INNER JOIN vs LEFT JOIN. What is normalization and its types (1NF, 2NF, 3NF)? Plus ACID properties, subqueries, indexes, and practical query examples for 2025 interviews.

20 questions
SQLDatabaseInterview QuestionsDDLDMLJOINNormalizationACID2025

Questions & Answers

1What is SQL, and what are its main types of statements?

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: Create table
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50)
);

-- DML: Insert data
INSERT INTO employees VALUES (1, 'John');

-- DCL: Grant permission
GRANT SELECT ON employees TO user1;

2What is the difference between DDL, DML, DCL, and TCL?

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

3What is the difference between DELETE, TRUNCATE, and DROP?

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:

-- DELETE: Remove specific rows
DELETE FROM employees WHERE id = 5;

-- TRUNCATE: Remove all rows
TRUNCATE TABLE employees;

-- DROP: Remove entire table
DROP TABLE employees;

4Explain the difference between WHERE and HAVING clauses.

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:

-- WHERE: Filter before grouping
SELECT department, AVG(salary)
FROM employees
WHERE salary > 50000 -- Filters individual rows
GROUP BY department;

-- HAVING: Filter after grouping
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000; -- Filters groups

5What is a PRIMARY KEY and FOREIGN KEY?

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:

-- Primary Key
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

-- Foreign Key
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

6What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

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:

-- INNER JOIN
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- LEFT JOIN
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- RIGHT JOIN
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- FULL JOIN
SELECT e.name, d.dept_name
FROM employees e
FULL JOIN departments d ON e.dept_id = d.dept_id;

7Explain GROUP BY and ORDER BY with examples.

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:

-- GROUP BY: Count employees per department
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department;

-- ORDER BY: Sort results
SELECT name, salary
FROM employees
ORDER BY salary DESC; -- Highest first

-- Combined: Group and sort
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

8What are subqueries, and when do you use them?

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:

-- Subquery in WHERE clause
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
);

-- Subquery in SELECT clause
SELECT name,
salary,
(SELECT AVG(salary) FROM employees) as avg_salary
FROM employees;

-- Subquery in FROM clause
SELECT dept_name, emp_count
FROM (
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
) as dept_stats;

9What is a view in SQL?

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:

-- Create view
CREATE VIEW high_salary_employees AS
SELECT name, salary, department
FROM employees
WHERE salary > 50000;

-- Use view
SELECT * FROM high_salary_employees
WHERE department = 'IT';

-- Drop view
DROP VIEW high_salary_employees;

10What is the purpose of indexes, and what are their advantages/disadvantages?

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:

-- Create index
CREATE INDEX idx_employee_name ON employees(name);

-- Create unique index
CREATE UNIQUE INDEX idx_employee_email ON employees(email);

-- Drop index
DROP INDEX idx_employee_name;

11What is normalization, and explain its types (1NF, 2NF, 3NF, BCNF).

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.

12What are stored procedures, and how do they differ from functions?

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:

-- Stored Procedure
CREATE PROCEDURE GetEmployeeSalary
@emp_id INT
AS
BEGIN
SELECT name, salary FROM employees WHERE id = @emp_id;
END;

-- Function
CREATE FUNCTION CalculateBonus(@salary DECIMAL)
RETURNS DECIMAL
AS
BEGIN
RETURN @salary * 0.1;
END;

13Explain ACID properties in databases.

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.

14What is a trigger, and when is it used?

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:

-- Trigger to log salary changes
CREATE TRIGGER salary_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
IF OLD.salary != NEW.salary THEN
INSERT INTO salary_log (emp_id, old_salary, new_salary, change_date)
VALUES (NEW.id, OLD.salary, NEW.salary, NOW());
END IF;
END;

15What is the difference between clustered and non-clustered indexes?

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:

-- Clustered Index (usually on Primary Key)
CREATE CLUSTERED INDEX idx_emp_id ON employees(id);

-- Non-Clustered Index
CREATE NONCLUSTERED INDEX idx_emp_name ON employees(name);

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

16Write a query to find the second highest salary from an employee table.

Method 1: Using Subquery

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

Method 2: Using LIMIT/OFFSET

SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Method 3: Using ROW_NUMBER()

SELECT salary
FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rn
FROM employees
) as ranked
WHERE rn = 2;

Method 4: Using DENSE_RANK() (handles ties)

SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as dr
FROM employees
) as ranked
WHERE dr = 2;

17Write a query to count the number of employees in each department.

Basic Query:

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

With Department Names (JOIN):

SELECT d.dept_name, COUNT(e.emp_id) as employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name;

With Ordering:

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC;

With Filtering (HAVING):

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5; -- Departments with more than 5 employees

18Write a query to find duplicate records in a table.

Method 1: Using GROUP BY and HAVING

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

Method 2: Using Window Functions

SELECT id, name, email
FROM (
SELECT id, name, email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn
FROM employees
) as ranked
WHERE rn > 1;

Method 3: Using Self-Join

SELECT e1.id, e1.name, e1.email
FROM employees e1
INNER JOIN employees e2
ON e1.email = e2.email AND e1.id > e2.id;

To Delete Duplicates (keep one):

DELETE e1 FROM employees e1
INNER JOIN employees e2
WHERE e1.email = e2.email AND e1.id > e2.id;

19Write a query to update multiple rows based on a condition.

Update with WHERE condition:

-- Update salary for specific department
UPDATE employees
SET salary = salary * 1.1 -- 10% increase
WHERE department = 'IT';

Update with CASE statement:

UPDATE employees
SET salary = CASE
WHEN department = 'IT' THEN salary * 1.15
WHEN department = 'HR' THEN salary * 1.10
ELSE salary * 1.05
END;

Update with JOIN:

UPDATE employees e
SET e.salary = e.salary * 1.1
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Engineering';

Update with Subquery:

UPDATE employees
SET salary = salary + 5000
WHERE dept_id IN (
SELECT dept_id FROM departments WHERE location = 'New York'
);

20Write a query to retrieve records between two dates.

Using BETWEEN:

SELECT *
FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2023-12-31';

Using Comparison Operators:

SELECT *
FROM employees
WHERE hire_date >= '2020-01-01' AND hire_date <= '2023-12-31';

Using DATE functions (MySQL):

SELECT *
FROM employees
WHERE DATE(hire_date) BETWEEN '2020-01-01' AND '2023-12-31';

Last 30 days:

SELECT *
FROM employees
WHERE hire_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

Current month:

SELECT *
FROM employees
WHERE YEAR(hire_date) = YEAR(CURDATE())
AND MONTH(hire_date) = MONTH(CURDATE());
See Other FAQs