SQL Interview Questions

Question 1

What is SQL and its categories?

Accepted Answer

SQL manages relational databases. DDL (define schema), DML (manipulate data), DCL (permissions), TCL (transactions). DDL: CREATE, ALTER, DROP, TRUNCATE. DML: SELECT, INSERT, UPDATE, DELETE. DCL: GRANT, REVOKE. TCL: COMMIT, ROLLBACK, SAVEPOINT. SQL is declarative. Standard ANSI SQL with vendor extensions (PostgreSQL, MySQL, SQL Server).

Question 2

What are JOINs and their types?

Accepted Answer

INNER (matching rows), LEFT (all left + matching), RIGHT (all right + matching), FULL (all from both), CROSS (cartesian). INNER JOIN: only matching rows. LEFT JOIN: all from left, NULL for non-matching right. FULL OUTER: all from both. CROSS JOIN: every combination. SELF JOIN: table joined with itself. Performance: INNER fastest, CROSS most expensive.

Question 3

What are indexes?

Accepted Answer

Indexes are data structures speeding up retrieval by creating sorted references to rows, like a book's index. Types: B-tree (default), Hash (exact match), GIN (full-text), GiST (geometric). Benefits: faster SELECT, WHERE, JOIN. Costs: slower INSERT/UPDATE, extra storage. Index columns in WHERE/JOIN/ORDER BY. EXPLAIN ANALYZE to verify usage.

Question 4

What are window functions?

Accepted Answer

Window functions calculate across related rows without collapsing them: ROW_NUMBER, RANK, LAG, LEAD over PARTITION BY. Syntax: function() OVER (PARTITION BY col ORDER BY col). Functions: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM, AVG. Unlike GROUP BY, preserves individual rows. Use for rankings, running totals, comparing to previous rows.

Question 5

What are CTEs?

Accepted Answer

CTEs (WITH clause) are temporary named result sets improving readability and enabling recursive queries for hierarchies. WITH cte AS (SELECT ...) SELECT FROM cte. Recursive: WITH RECURSIVE for hierarchical data (org charts, categories). CTEs are not always materialized — optimizer may inline them.

Question 6

What is normalization?

Accepted Answer

Normalization eliminates data redundancy by organizing into related tables (1NF-3NF). Denormalization adds redundancy for read speed. 1NF: atomic values. 2NF: no partial dependencies. 3NF: no transitive dependencies. Benefits: integrity, reduced storage. Denormalization: intentional redundancy for joins avoidance. Normalize for OLTP, denormalize for OLAP.

Question 7

How do you optimize slow queries?

Accepted Answer

Use EXPLAIN ANALYZE, add indexes, rewrite queries, optimize JOINs, use pagination, and partition large tables. Steps: (1) EXPLAIN ANALYZE for execution plan. (2) Add indexes for sequential scans. (3) Check JOIN order. (4) Covering indexes. (5) Avoid SELECT *. (6) Replace subqueries with JOINs. (7) Pagination. (8) Partition large tables. (9) Connection pooling. (10) Materialized views for complex aggregations.

Question 8

What are ACID properties?

Accepted Answer

Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent safety), Durability (survives crashes). Isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Trade safety for performance. BEGIN/COMMIT/ROLLBACK. PostgreSQL default: READ COMMITTED. SERIALIZABLE prevents all anomalies but may need retry logic.

Question 9

A report query takes 30 seconds. How to optimize?

Accepted Answer

Profile with EXPLAIN, add composite indexes, use materialized views, consider partitioning and pre-aggregation. Steps: (1) EXPLAIN ANALYZE. (2) Add composite indexes. (3) Materialized views for aggregations. (4) Partition by date. (5) Pre-aggregate in summary tables. (6) Consider columnar storage for analytics. (7) Parallel query. (8) Application-level caching.

Question 10

Amazon: Design an e-commerce database schema.

Accepted Answer

Core: users, products, categories, orders, order_items, addresses, payments, reviews with proper foreign keys and indexes. Tables: users(id, email), products(id, title, price, category_id, stock), categories(id, name, parent_id), orders(id, user_id, status, total), order_items(order_id, product_id, quantity, price), addresses, payments, reviews. Indexes on foreign keys and frequently queried columns.

Free SQL Course - Learn Database Queries, Joins & Data Management

What You'll Learn

Career Opportunities

Course Completion Certificate

Course Modules Overview

Introduction to SQL and Databases

SELECT Queries & Filtering

SQL Joins & Relationships

Aggregate Functions & Grouping

Subqueries & Nested Queries

Data Manipulation (INSERT, UPDATE, DELETE)

Database Design & Normalization

Indexes and Performance

Stored Procedures & Functions

SQL Project Ecommerce Database

Database Administration & Security

SQL Career & Advanced Topics

Course Curriculum

Introduction to SQL and Databases

SELECT Queries & Filtering

SQL Joins & Relationships

Aggregate Functions & Grouping

Subqueries & Nested Queries

Data Manipulation (INSERT, UPDATE, DELETE)

Database Design & Normalization

Indexes and Performance

Stored Procedures & Functions

SQL Project Ecommerce Database

Database Administration & Security

SQL Career & Advanced Topics

Your Learning Path

Beginner

Intermediate

Advanced

Tools & Technologies

MySQL

PostgreSQL

DBeaver

Ready to Start Learning?

Related Free Courses

Python Django Course

Node.js Course

Java Course

PHP Course