SQL Interview Questions and Answers
1. Basic SQL Queries (Beginner)
- What is SQL?
SQL (Structured Query Language) is used to interact with relational databases. It allows you to create, read,
update, and delete data.
- Types of SQL Commands
DDL: CREATE, ALTER, DROP; DML: SELECT, INSERT, UPDATE, DELETE; DCL: GRANT, REVOKE; TCL:
COMMIT, ROLLBACK.
- SELECT, FROM, WHERE
SELECT: pick columns; FROM: pick table; WHERE: filter rows.
- Retrieve all columns and rows
SELECT * FROM table_name;
- Filter rows
SELECT * FROM table WHERE column = 'value';
- AND, OR in WHERE
AND: both conditions true; OR: at least one true.
- Sort result set
SELECT * FROM table ORDER BY column ASC/DESC;
- Distinct values
SELECT DISTINCT column FROM table;
- Aggregate functions
COUNT(), SUM(), AVG(), MIN(), MAX()
- GROUP BY and aggregate
SELECT column, COUNT(*) FROM table GROUP BY column;
- HAVING vs WHERE
WHERE filters rows, HAVING filters groups after aggregation.
- Limit rows
MySQL/PostgreSQL: LIMIT 10; SQL Server: TOP 10; Oracle: ROWNUM <= 10.
- Aliases
SELECT column AS alias FROM table;
2. Intermediate SQL Queries
- Joins: INNER, LEFT, RIGHT, FULL OUTER JOIN
INNER: matched rows; LEFT: all left + matched right; RIGHT: all right + matched left; FULL: all rows.
- Self-join
Join a table to itself.
- UNION vs UNION ALL
UNION removes duplicates; UNION ALL includes duplicates.
- Subqueries
Query inside a query.
- Correlated vs Non-correlated subquery
Correlated references outer query; non-correlated runs independently.
- IN, NOT IN
Check if value exists in a list.
- BETWEEN
Check if value is in a range.
- LIKE
Pattern match, e.g., 'A%'
- NULL checks
IS NULL, IS NOT NULL
- Set operations
UNION, INTERSECT, EXCEPT/MINUS
- INSERT, UPDATE, DELETE
INSERT INTO table VALUES(...); UPDATE table SET ... WHERE ...; DELETE FROM table WHERE ...
3. Advanced SQL Concepts
- Window functions
ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER()
- PARTITION BY
Divide rows into groups.
- ORDER BY in window
Sort within partitions.
- CTEs
WITH cte AS (SELECT ...) SELECT ...
- Recursive CTE
Used for hierarchies.
- Stored procedures
Precompiled routines.
- Triggers
Auto-executed actions.
- Indexes
Speed reads, types: B-tree, bitmap.
- Transactions & ACID
Atomicity, Consistency, Isolation, Durability.
- Isolation levels
Read Uncommitted, Read Committed, Repeatable Read, Serializable.
- Normalization
1NF, 2NF, 3NF, BCNF.
- Denormalization
Add redundancy for speed.
4. Database Design & Normalization
- Design schema
Define tables, keys, relationships.
- Data anomalies
Insert/update/delete problems in bad design.
- Normalize schema
Apply normal forms.
- Normalization vs denormalization
Trade consistency vs speed.
- Data types
Choose based on size, precision.
- Keys and constraints
Primary key (unique row), Foreign key (links), Unique (no duplicates).
5. Performance & Optimization
- Identify slow queries
EXPLAIN, ANALYZE.
- Optimize queries
Indexes, rewrite, reduce joins.
- Index impact
Fast read, slow write.
- Execution plans
Understand query path.
- Query caching
Reuse frequent results.
- Schema optimization
Keys, indexes, partitioning.
- Partitioning, sharding
Split data across partitions/shards.
6. Scenario-Based Questions
- Customers and order count
SELECT c.name, COUNT(o.id) FROM Customers c LEFT JOIN Orders o ON c.id=o.customer_id GROUP BY
c.name;
- Second highest salary
SELECT MAX(salary) FROM Employees WHERE salary < (SELECT MAX(salary) FROM Employees);
- Find duplicates
SELECT col1, COUNT(*) FROM table GROUP BY col1 HAVING COUNT(*) > 1;
- Events in last hour
SELECT COUNT(*) FROM events WHERE timestamp >= NOW() - INTERVAL '1 hour';
- Running total
SELECT col, SUM(amount) OVER (ORDER BY date) FROM table;
- LEFT JOIN vs INNER JOIN
LEFT JOIN keeps unmatched left rows; INNER JOIN only matched.
- Troubleshoot slow query
Check execution plan, indexes, optimize logic, server resources.
- Blog schema
Users(id, name, email), Posts(id, user_id, title, content), Comments(id, post_id, user_id, comment).
