How to download youtube video in your gallery

Top 50 SQL Interview Questions and Answers

From basic to advanced level with detailed explanations, code examples, and preparation tips to help you ace your next SQL interview.

Table of Contents

• Basic Questions (1-15)
• Intermediate Questions (16-35)
• Advanced Questions (36-50)
• FAQ

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. Whether you're interviewing for a database administrator, data analyst, or backend developer position, SQL skills are often a key requirement. This guide covers the top 50 SQL interview questions ranging from basic to advanced levels, with detailed answers and practical examples.

Basic SQL Questions (1-15)

1. What is SQL and what are its main components?

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. Its main components are:

• DDL (Data Definition Language): Commands like CREATE, ALTER, DROP to define database structure
• DML (Data Manipulation Language): Commands like SELECT, INSERT, UPDATE, DELETE to manipulate data
• DCL (Data Control Language): Commands like GRANT, REVOKE to control access
• TCL (Transaction Control Language): Commands like COMMIT, ROLLBACK to manage transactions
• DQL (Data Query Language): SELECT statements to query data

Tip

Most interview questions focus on DML and DQL, but understanding all components shows comprehensive knowledge.

2. What is the difference between DELETE and TRUNCATE commands?

Both DELETE and TRUNCATE are used to remove data from tables, but they differ in several ways:

DELETE	TRUNCATE
DML command (can be rolled back)	DDL command (cannot be rolled back)
Can use WHERE clause to filter rows	Removes all rows (no WHERE clause)
Slower for large tables (logs each row)	Faster (deallocates data pages)
Resets identity columns (depends on DBMS)	Resets identity columns to seed value

DELETE FROM customers WHERE status = 'inactive';
-- vs --
TRUNCATE TABLE customers;

3. What are SQL constraints? List the most common ones.

SQL constraints are rules enforced on data columns to maintain data integrity. Common constraints:

• NOT NULL: Column cannot have NULL values
• UNIQUE: All values in column must be unique
• PRIMARY KEY: Uniquely identifies each row (NOT NULL + UNIQUE)
• FOREIGN KEY: Ensures referential integrity with another table
• CHECK: Validates that values meet a specific condition
• DEFAULT: Sets a default value when none is specified
• INDEX: Improves query performance (not strictly a constraint)

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  age INT CHECK (age >= 18),
  dept_id INT REFERENCES departments(id),
  join_date DATE DEFAULT CURRENT_DATE
);

4. What is a primary key?

A primary key is a column or set of columns that uniquely identifies each row in a table. Characteristics:

• Must contain unique values
• Cannot contain NULL values
• A table can have only one primary key
• Often used with auto-increment for surrogate keys
• Creates a clustered index by default (in most DBMS)

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2)
);

Intermediate SQL Questions (16-35)

16. What are JOINs in SQL? Explain different types with examples.

JOINs combine rows from two or more tables based on related columns. Main types:

• INNER JOIN: Returns rows when there's a match in both tables
• LEFT (OUTER) JOIN: Returns all rows from left table and matched rows from right (NULL if no match)
• RIGHT (OUTER) JOIN: Returns all rows from right table and matched rows from left (NULL if no match)
• FULL (OUTER) JOIN: Returns rows when there's a match in either table (NULL for non-matching sides)
• CROSS JOIN: Returns Cartesian product (all possible combinations)
• SELF JOIN: Joins a table to itself (uses table aliases)

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

-- LEFT JOIN example
SELECT e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

17. What is the difference between WHERE and HAVING clauses?

Both filter data but have key differences:

WHERE	HAVING
Filters rows before grouping	Filters groups after GROUP BY
Can be used without GROUP BY	Requires GROUP BY (except in some DBMS)
Cannot use aggregate functions	Can use aggregate functions
Faster execution	Slower as it processes groups

-- WHERE filters individual rows
SELECT product_name, price
FROM products
WHERE price > 100;

-- HAVING filters groups
SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

18. What are indexes and how do they improve performance?

Indexes are database objects that improve query performance by providing faster data retrieval. They work like a book's index:

• Clustered Index: Determines physical order of data (only one per table, usually primary key)
• Non-Clustered Index: Separate structure with pointers to data (multiple allowed)
• Composite Index: Index on multiple columns
• Unique Index: Ensures index key contains only unique values

Benefits:

• Faster data retrieval for SELECT queries
• Speeds up JOIN operations
• Improves ORDER BY performance

Trade-offs:

• Slows down INSERT/UPDATE/DELETE (indexes must be updated)
• Consumes additional storage space

-- Create an index
CREATE INDEX idx_customer_name
ON customers(last_name, first_name);

-- Create a unique index
CREATE UNIQUE INDEX idx_product_code
ON products(product_code);

Advanced SQL Questions (36-50)

36. What are window functions? Provide examples.

Window functions perform calculations across a set of table rows related to the current row, without collapsing rows like GROUP BY. They include:

• Aggregate functions: SUM(), AVG(), COUNT(), etc. with OVER()
• Ranking functions: ROW_NUMBER(), RANK(), DENSE_RANK()
• Analytic functions: LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()

-- Running total using SUM() OVER()
SELECT order_date, amount,
  SUM(amount) OVER(ORDER BY order_date) AS running_total
FROM orders;

-- Rank employees by salary in each department
SELECT employee_name, department, salary,
  RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Tip

Window functions are powerful for complex reporting and analytics queries without needing subqueries or temporary tables.

37. Explain recursive CTEs with an example.

Recursive Common Table Expressions (CTEs) allow querying hierarchical or graph data by repeatedly executing a query that references itself. They consist of:

1. Anchor member: Base result set
2. Recursive member: References the CTE name
3. Termination condition: Stops when no rows are returned

-- Organizational hierarchy example
WITH RECURSIVE org_hierarchy AS (
  -- Anchor member (top-level employees)
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive member (subordinates)
  SELECT e.id, e.name, e.manager_id, h.level + 1
  FROM employees e
  JOIN org_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM org_hierarchy
ORDER BY level, name;

Common use cases:

• Organizational charts
• Bill of materials
• Network paths
• Graph traversal (friends of friends)

38. What is query optimization and how would you approach it?

Query optimization improves SQL query performance. Approach:

1. Analyze execution plans: Use EXPLAIN or similar to understand how the query executes
2. Identify bottlenecks: Look for full table scans, expensive sorts, or nested loops
3. Optimization techniques:
   • Add appropriate indexes (but not too many)
   • Rewrite queries to use efficient joins
   • Avoid SELECT * (only retrieve needed columns)
   • Use WHERE clauses to filter early
   • Consider denormalization for read-heavy scenarios
   • Use query hints if necessary (as last resort)
4. Test changes: Compare performance before/after

-- Example of analyzing a query
EXPLAIN ANALYZE SELECT c.name, COUNT(o.order_id)
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'USA'
GROUP BY c.name
ORDER BY COUNT(o.order_id) DESC;

Tip

Always measure performance improvements - some "optimizations" might actually degrade performance in certain scenarios.

SQL Interview FAQ

How should I prepare for an SQL interview?

To prepare for an SQL interview:

1. Review basic SQL commands (SELECT, INSERT, UPDATE, DELETE)
2. Practice writing complex queries with JOINs, subqueries, and GROUP BY
3. Understand database design principles (normalization, indexes)
4. Work on optimization techniques (reading execution plans)
5. Prepare for both theoretical questions and hands-on coding tests
6. Practice with real datasets (try platforms like LeetCode or HackerRank)

What's the difference between SQL and NoSQL?

Key differences:

SQL Databases	NoSQL Databases
Relational model	Non-relational (document, key-value, etc.)
Fixed schema	Dynamic schema
Vertical scaling	Horizontal scaling
ACID transactions	BASE model (Basically Available, Soft state, Eventually consistent)
Examples: MySQL, PostgreSQL	Examples: MongoDB, Cassandra

What are some common SQL performance issues?

Common SQL performance issues include:

• Missing or improper indexes
• Full table scans due to unoptimized WHERE clauses
• Nested subqueries that could be JOINs
• Using SELECT * instead of specific columns
• Complex joins on non-indexed columns
• Cursor overuse (procedural processing instead of set-based)
• Implicit conversions in comparisons
• Over-normalized database schemas

© 2023 SQL Interview Prep Guide. All rights reserved.

Practice these questions to ace your next SQL interview!