SQL is one of the most important skills for developers, data analysts, database administrators, data engineers, and software professionals who work with relational databases.
Whether you are preparing for your first SQL interview, a developer job, a data analyst position, or an experienced database role, interviewers commonly ask questions about SQL syntax, queries, joins, filtering, grouping, constraints, indexes, subqueries, database design, transactions, and performance optimization.
Knowing SQL commands is important, but a good interview preparation strategy also requires understanding why a query works, when to use a particular SQL feature, and how to optimize database operations.
In this guide, you will find 50+ SQL interview questions and answers, ranging from basic to advanced concepts.
SQL Interview Questions for Beginners
Let’s start with fundamental SQL questions that are commonly asked in entry-level interviews.
1. What Is SQL?
SQL stands for Structured Query Language.
It is a language used to interact with relational databases.
SQL can be used to:
- Create databases and tables
- Insert data
- Retrieve data
- Update records
- Delete records
- Filter data
- Sort results
- Group data
- Join tables
- Manage database objects
- Control transactions
Example:
SELECT *
FROM employees;
This retrieves all records from the employees table.
2. What Is a Database?
A database is an organized collection of data that allows information to be stored, managed, and retrieved efficiently.
Examples include databases used by:
- E-commerce applications
- Banking systems
- Mobile applications
- Websites
- ERP systems
- CRM platforms
- School management systems
Popular relational database systems include:
- MySQL
- PostgreSQL
- Microsoft SQL Server
- Oracle Database
- SQLite
3. What Is a Relational Database?
A relational database stores data in tables consisting of rows and columns.
Tables can be connected using relationships.
For example:
Customers
|
| customer_id
↓
Orders
The relationship between these tables can be implemented using a foreign key.
4. What Is a Table in SQL?
A table stores related records in rows and columns.
Example:
employees
employee_id | name | salary
------------|--------|--------
1 | Rahul | 50000
2 | Priya | 60000
3 | Amit | 55000
Each row represents a record, while each column represents an attribute.
5. What Is a Row?
A row represents a single record in a table.
For example:
1 | Rahul | 50000
could represent one employee.
6. What Is a Column?
A column represents a specific attribute of the data.
For example:
employee_id
name
salary
department_id
Each column has a defined data type.
7. What Is a Primary Key?
A primary key uniquely identifies each row in a table.
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100)
);
A primary key:
- Identifies records uniquely
- Does not allow
NULL - Enforces uniqueness
- Can consist of multiple columns as a composite primary key
8. What Is a Foreign Key?
A foreign key is a column or set of columns that references a key in another table.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);
The foreign key helps maintain referential integrity.
9. What Is a UNIQUE Constraint?
A UNIQUE constraint prevents duplicate values according to the database’s uniqueness rules.
Example:
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(150) UNIQUE
);
This prevents duplicate email values in the column.
10. What Is the NOT NULL Constraint?
NOT NULL prevents a column from containing NULL.
Example:
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
Every user must have a value for name.
11. What Is a CHECK Constraint?
A CHECK constraint validates a condition.
Example:
CREATE TABLE products (
product_id INT PRIMARY KEY,
price DECIMAL(10,2)
CHECK (price >= 0)
);
This prevents negative prices.
12. What Is a DEFAULT Constraint?
A DEFAULT value is automatically used when an insert does not provide a value for that column.
Example:
CREATE TABLE users (
user_id INT PRIMARY KEY,
status VARCHAR(20) DEFAULT 'active'
);
If status is omitted, the database can use:
active
13. What Is NULL in SQL?
NULL represents the absence of a value or an unknown/missing value.
It is not the same as:
0
or:
''
or:
FALSE
To check for NULL, use:
SELECT *
FROM employees
WHERE department_id IS NULL;
Do not use:
WHERE department_id = NULL;
because NULL requires SQL’s three-valued logic.
14. What Is the SELECT Statement?
SELECT retrieves data from one or more tables.
Example:
SELECT name, salary
FROM employees;
To retrieve all columns:
SELECT *
FROM employees;
In production applications, selecting only required columns is often preferable to using SELECT *.
15. What Is the WHERE Clause?
The WHERE clause filters rows.
Example:
SELECT *
FROM employees
WHERE salary > 50000;
Only employees whose salary is greater than 50000 are returned.
16. What Is ORDER BY?
ORDER BY sorts query results.
Ascending:
SELECT *
FROM employees
ORDER BY salary ASC;
Descending:
SELECT *
FROM employees
ORDER BY salary DESC;
17. What Is GROUP BY?
GROUP BY groups rows that have the same values in specified columns.
Example:
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
This counts employees in each department.
18. What Is HAVING?
HAVING filters groups after aggregation.
Example:
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
This returns departments containing more than five employees.
19. WHERE vs HAVING
This is a very common interview question.
WHERE
Filters individual rows before grouping.
SELECT *
FROM employees
WHERE salary > 50000;
HAVING
Filters groups after aggregation.
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;
A simple way to remember:
WHERE → Rows
HAVING → Groups
20. What Are SQL Aggregate Functions?
Aggregate functions calculate a value from multiple rows.
Common aggregate functions include:
COUNT()
SUM()
AVG()
MIN()
MAX()
Example:
SELECT AVG(salary)
FROM employees;
This calculates the average salary.
21. What Is COUNT()?
COUNT() counts rows or non-NULL values depending on the expression.
Example:
SELECT COUNT(*)
FROM employees;
This counts rows.
Another example:
SELECT COUNT(email)
FROM employees;
This counts non-NULL email values.
22. What Is DISTINCT?
DISTINCT removes duplicate result values.
Example:
SELECT DISTINCT department_id
FROM employees;
This returns each department ID once.
23. What Is a SQL JOIN?
A JOIN combines related data from multiple tables.
Example:
SELECT
customers.name,
orders.order_id
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;
This combines customer and order information.
24. What Are the Types of SQL JOINs?
Common join types include:
INNER JOINLEFT JOINRIGHT JOINFULL OUTER JOINCROSS JOIN
Some database systems also support other join-related features.
25. What Is INNER JOIN?
INNER JOIN returns rows where the join condition matches in both tables.
Example:
SELECT
customers.name,
orders.order_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;
Only customers with matching orders are returned.
26. What Is LEFT JOIN?
A LEFT JOIN returns all rows from the left table and matching rows from the right table.
Example:
SELECT
customers.name,
orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
Customers without orders can still appear, with NULL values for columns from orders.
27. What Is a RIGHT JOIN?
A RIGHT JOIN returns all rows from the right table and matching rows from the left table.
Example:
SELECT
customers.name,
orders.order_id
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;
Support and practical usage vary by database system.
Many developers prefer rewriting such queries using LEFT JOIN for consistency.
28. What Is FULL OUTER JOIN?
A FULL OUTER JOIN returns:
- Matching rows
- Unmatched rows from the left table
- Unmatched rows from the right table
Example:
SELECT *
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;
Not all database systems support FULL OUTER JOIN directly.
29. What Is a CROSS JOIN?
A CROSS JOIN creates a Cartesian product.
If table A has 3 rows and table B has 4 rows, the result can contain:
3 × 4 = 12 rows
Example:
SELECT *
FROM colors
CROSS JOIN sizes;
This can be useful when intentionally generating every combination, but an accidental Cartesian product can create huge result sets.
30. What Is a Self Join?
A self join joins a table to itself.
For example, employees may have managers:
employee_id
name
manager_id
You can find employees and their managers:
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;
Here, the employees table is used twice with different aliases.
31. What Is a Subquery?
A subquery is a query nested inside another SQL statement.
Example:
SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The inner query calculates the average salary.
The outer query returns employees earning more than that average.
32. What Is a Correlated Subquery?
A correlated subquery references a value from the outer query.
Example:
SELECT e1.*
FROM employees e1
WHERE salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);
The inner query depends on the current row from the outer query.
Correlated subqueries can be useful, but sometimes an equivalent join or window-function solution performs better.
33. What Is a CTE?
CTE stands for Common Table Expression.
It allows you to define a temporary named result set for a query.
Example:
WITH high_salary AS (
SELECT *
FROM employees
WHERE salary > 50000
)
SELECT *
FROM high_salary;
CTEs can make complex queries easier to read and maintain.
34. What Is a Recursive CTE?
A recursive CTE references itself and is useful for hierarchical or graph-like data.
For example:
Company
├── Manager
│ ├── Employee
│ └── Employee
└── Manager
A recursive CTE can traverse such structures.
Example syntax:
WITH RECURSIVE employee_tree AS (
SELECT employee_id, name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, e.manager_id
FROM employees e
JOIN employee_tree t
ON e.manager_id = t.employee_id
)
SELECT *
FROM employee_tree;
Recursive CTE syntax varies between database systems.
35. What Is a SQL View?
A view is a stored query that can be queried like a table.
Example:
CREATE VIEW active_users AS
SELECT user_id, name, email
FROM users
WHERE status = 'active';
You can then use:
SELECT *
FROM active_users;
Views can simplify complex queries and provide controlled access to data.
36. What Is an Index?
An index is a database structure that can improve the performance of certain queries.
Example:
CREATE INDEX idx_users_email
ON users(email);
It can help queries such as:
SELECT *
FROM users
WHERE email = 'rahul@example.com';
However, indexes also consume storage and require maintenance during data modifications.
37. What Are the Advantages of Indexes?
Indexes can:
- Speed up searches
- Improve some joins
- Help with certain sorting operations
- Support uniqueness
- Improve some range queries
But indexes can also:
- Consume storage
- Increase write overhead
- Require maintenance
- Become redundant
Therefore, indexes should be designed around actual workload requirements.
38. What Is a Composite Index?
A composite index contains multiple columns.
Example:
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);
Column order matters.
This index may be particularly useful for queries that begin with customer_id.
39. What Is Normalization?
Normalization is the process of organizing relational data to reduce unnecessary duplication and improve data integrity.
Common normal forms include:
1NF
2NF
3NF
BCNF
For example, instead of storing customer information repeatedly in every order row, create separate:
customers
orders
tables and connect them using keys.
40. What Is Denormalization?
Denormalization intentionally introduces some redundancy to improve performance, simplify reads, or satisfy specific application requirements.
For example, an analytics system might store precomputed values rather than calculating them through expensive joins every time.
Denormalization should be based on actual requirements and measured performance rather than used automatically.
41. DELETE vs TRUNCATE vs DROP
This is a very common interview question.
DELETE
Removes rows.
DELETE FROM employees
WHERE employee_id = 10;
It can normally use a WHERE clause.
TRUNCATE
Removes all rows from a table using database-specific semantics.
TRUNCATE TABLE employees;
It generally cannot be used with a normal WHERE clause.
DROP
Removes the database object itself.
DROP TABLE employees;
This removes the table definition and its data.
Exact transaction, identity-reset, trigger, and logging behavior varies between database systems.
42. DELETE vs TRUNCATE
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Removes rows | Yes | Yes, all rows |
| WHERE supported | Yes | No |
| Removes table structure | No | No |
| Usually intended for all rows | No | Yes |
| Transaction behavior | Database-specific | Database-specific |
| Trigger behavior | Database-specific | Database-specific |
Do not rely on one database’s behavior when writing portable SQL.
43. What Is a Transaction?
A transaction is a logical unit of database work.
For example:
BEGIN;
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;
COMMIT;
If something goes wrong:
ROLLBACK;
Transactions help maintain consistency.
44. What Is ACID?
ACID describes four important transaction properties.
Atomicity
All operations in a transaction succeed or the transaction is rolled back.
Consistency
A transaction should leave the database in a valid state according to its rules.
Isolation
Concurrent transactions should interact according to the database’s configured isolation semantics.
Durability
Committed changes should survive failures according to the database’s durability guarantees.
45. What Are Transaction Isolation Levels?
Common isolation levels include:
- Read Uncommitted
- Read Committed
- Repeatable Read
- Serializable
Some database systems also provide additional modes or variations.
Higher isolation can reduce certain concurrency anomalies but may increase locking, blocking, or other performance costs.
46. What Is a Deadlock?
A deadlock occurs when two or more transactions wait for resources held by each other.
Example:
Transaction A
locks Row 1
waits for Row 2
Transaction B
locks Row 2
waits for Row 1
Neither can continue.
Database systems usually detect deadlocks and abort one transaction so the others can proceed.
47. What Is a Stored Procedure?
A stored procedure is a database-side program containing SQL statements and procedural logic.
For example, some database systems support syntax such as:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT *
FROM employees;
END;
Stored-procedure syntax varies significantly between database systems.
48. What Is a Trigger?
A trigger is database logic that executes automatically when a specified database event occurs.
For example:
INSERT
UPDATE
DELETE
A trigger might be used for:
- Auditing
- Automatic history records
- Data validation
- Synchronization tasks
Triggers should be used carefully because they can make application behavior harder to understand and debug.
49. What Is a Window Function?
A window function performs calculations across related rows while keeping individual rows in the result.
Example:
SELECT
employee_id,
name,
salary,
RANK() OVER (
ORDER BY salary DESC
) AS salary_rank
FROM employees;
Unlike GROUP BY, window functions do not collapse the rows into one row per group.
50. What Is ROW_NUMBER()?
ROW_NUMBER() assigns a sequential number to rows within a window.
Example:
SELECT
employee_id,
name,
salary,
ROW_NUMBER() OVER (
ORDER BY salary DESC
) AS row_num
FROM employees;
This can be useful for ranking and pagination patterns.
51. RANK() vs DENSE_RANK() vs ROW_NUMBER()
Suppose salaries are:
100000
90000
90000
80000
ROW_NUMBER()
Produces unique sequential numbers:
1
2
3
4
RANK()
Produces:
1
2
2
4
DENSE_RANK()
Produces:
1
2
2
3
This is a very common SQL interview topic.
52. How Do You Find the Second Highest Salary?
One approach is:
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
This returns the second distinct-highest salary when one exists.
Another approach using DENSE_RANK() is:
SELECT salary
FROM (
SELECT
salary,
DENSE_RANK() OVER (
ORDER BY salary DESC
) AS salary_rank
FROM employees
) ranked
WHERE salary_rank = 2;
The best solution depends on whether duplicates should be treated as one salary level or separate rows.
53. How Do You Find Duplicate Records?
Suppose you want to find duplicate emails:
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
This returns email values that occur more than once.
54. How Do You Find Employees With No Department?
Using LEFT JOIN:
SELECT e.*
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
This returns employees whose department does not have a matching record.
55. How Do You Find the Highest Salary in Each Department?
A window-function approach:
SELECT *
FROM (
SELECT
e.*,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS salary_rank
FROM employees e
) ranked
WHERE salary_rank = 1;
Using RANK() returns all employees tied for the highest salary in their department.
If you need exactly one row per department, ROW_NUMBER() may be more appropriate with a deterministic tie-breaker.
56. How Do You Count Employees in Each Department?
SELECT
department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
This is one of the most common GROUP BY interview questions.
57. How Do You Find Employees Earning More Than the Average Salary?
SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The subquery calculates the average salary.
58. How Do You Find the Top 5 Highest Salaries?
The syntax depends on the database.
For MySQL and PostgreSQL:
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;
For SQL Server:
SELECT TOP 5 *
FROM employees
ORDER BY salary DESC;
For Oracle, PostgreSQL, SQL Server, and other systems, modern pagination/row-limiting syntax can differ.
Always use the syntax appropriate for your database.
59. What Is the Difference Between UNION and UNION ALL?
UNION combines results and removes duplicate rows.
SELECT email FROM customers
UNION
SELECT email FROM subscribers;
UNION ALL combines results without removing duplicates.
SELECT email FROM customers
UNION ALL
SELECT email FROM subscribers;
Because UNION has to perform duplicate elimination, UNION ALL can be faster when duplicate removal is not required.
60. What Is EXISTS?
EXISTS checks whether a subquery returns at least one row.
Example:
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
This returns customers who have at least one order.
61. EXISTS vs IN
Both can be used for related filtering problems, but their semantics and performance depend on the query and database optimizer.
Example using IN:
SELECT *
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);
Example using EXISTS:
SELECT *
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
Do not assume that EXISTS is always faster than IN, or vice versa. Use the form that clearly expresses the requirement and verify performance with execution plans when necessary.
62. What Is a Self-Referencing Foreign Key?
A self-referencing foreign key references another row in the same table.
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT,
FOREIGN KEY (manager_id)
REFERENCES employees(employee_id)
);
Here:
manager_id
references:
employee_id
in the same table.
This is useful for hierarchical structures.
63. What Is Referential Integrity?
Referential integrity ensures that relationships between related tables remain valid.
For example:
customers
customer_id = 10
and:
orders
customer_id = 10
The foreign key can ensure that the referenced customer exists.
64. What Is Query Optimization?
Query optimization is the process of improving SQL queries and database design so that queries use resources efficiently.
Optimization may involve:
- Proper indexes
- Better query structure
- Appropriate joins
- Avoiding unnecessary columns
- Reducing unnecessary rows
- Updating statistics where applicable
- Reviewing execution plans
- Improving schema design
65. What Is an Execution Plan?
An execution plan shows how the database intends to execute a query.
You can often inspect one using:
EXPLAIN
For example:
EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 1001;
The exact command and output vary between database systems.
66. How Can You Optimize a Slow SQL Query?
A practical approach is:
Step 1: Inspect the execution plan
Use:
EXPLAIN
or the database’s equivalent.
Step 2: Check indexes
Determine whether appropriate indexes exist.
Step 3: Check filtering
Retrieve only the rows you need.
Step 4: Avoid unnecessary columns
Instead of:
SELECT *
use:
SELECT name, email
when those are the only required fields.
Step 5: Review joins
Make sure join conditions are correct and appropriate indexes exist.
Step 6: Check data volume
A query that works well on 10,000 rows may behave differently on 100 million rows.
67. What Is a Cartesian Product?
A Cartesian product occurs when every row from one table is combined with every row from another table.
For example:
SELECT *
FROM customers
CROSS JOIN products;
If there are:
100 customers
1000 products
the result can contain:
100 × 1000 = 100,000 rows
An accidental Cartesian product can severely affect query performance.
68. What Is SQL Injection?
SQL injection is a security vulnerability where untrusted input is interpreted as part of an SQL statement.
Unsafe pattern:
Building SQL by directly concatenating user input
Applications should use:
- Parameterized queries
- Prepared statements
- Proper input handling
- Least-privilege database accounts
For example, instead of dynamically concatenating:
SELECT *
FROM users
WHERE email = 'USER_INPUT';
use parameterized queries provided by your programming language or database library.
69. What Is a Constraint?
A constraint is a database rule that controls what data can be stored.
Common constraints include:
PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK
DEFAULT
Example:
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) CHECK (price >= 0)
);
70. What Is the Difference Between CHAR and VARCHAR?
Both store character strings, but they are designed differently.
CHAR
Fixed-length character data.
CHAR(10)
VARCHAR
Variable-length character data.
VARCHAR(100)
For many general-purpose string columns, VARCHAR is commonly appropriate.
Exact storage behavior and performance characteristics depend on the database system.
71. What Is the Difference Between DECIMAL and FLOAT?
DECIMAL is an exact numeric type commonly used when exact decimal representation is important, such as monetary values.
FLOAT is an approximate floating-point type.
For financial values, an exact numeric type such as:
DECIMAL(10,2)
is generally preferable to floating-point representation.
72. What Is a Composite Key?
A composite key uses multiple columns together as a key.
Example:
CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);
The combination of student_id and course_id must be unique.
73. What Is a Surrogate Key?
A surrogate key is an artificial identifier created specifically to identify a row.
For example:
customer_id = 1001
It has no business meaning and is used as a stable identifier.
Common surrogate-key approaches include:
- Auto-generated integers
- Identity columns
- UUIDs
The appropriate choice depends on the application and database architecture.
74. What Is a Natural Key?
A natural key is an existing real-world attribute that uniquely identifies a record.
Examples might include:
Email
National identifier
SKU
ISBN
Whether a natural key should be used as the primary key depends on whether the value is guaranteed to remain stable, unique, appropriate for relationships, and suitable for the database’s operational requirements.
75. What Is Database Normalization?
Database normalization organizes data to reduce unnecessary duplication and improve consistency.
Common normal forms include:
1NF
2NF
3NF
BCNF
Most application database designs commonly focus on achieving an appropriate level of normalization rather than blindly maximizing normalization.
SQL Interview Query Practice
Here are some practical questions you should practice before an SQL interview.
Question 1
Find all employees earning more than 50000.
SELECT *
FROM employees
WHERE salary > 50000;
Question 2
Find the highest salary.
SELECT MAX(salary)
FROM employees;
Question 3
Find the average salary.
SELECT AVG(salary)
FROM employees;
Question 4
Count employees by department.
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
Question 5
Find duplicate emails.
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
Question 6
Find employees without departments.
SELECT e.*
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
Question 7
Find the second-highest distinct salary.
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
Question 8
Find the top 3 salaries.
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 3;
For SQL Server, the syntax would differ.
Question 9
Find employees earning more than their department average.
SELECT *
FROM (
SELECT
e.*,
AVG(salary) OVER (
PARTITION BY department_id
) AS department_avg
FROM employees e
) x
WHERE salary > department_avg;
Question 10
Find the highest-paid employee in each department.
SELECT *
FROM (
SELECT
e.*,
RANK() OVER (
PARTITION BY department_id
ORDER BY salary DESC
) AS rnk
FROM employees e
) x
WHERE rnk = 1;
SQL Interview Preparation Tips
1. Understand Concepts, Not Just Syntax
Do not memorize:
SELECT
FROM
WHERE
GROUP BY
without understanding how they work together.
2. Practice Writing Queries
Try solving problems involving:
- Filtering
- Sorting
- Aggregation
- Joins
- Subqueries
- CTEs
- Window functions
3. Understand NULL
Many SQL interview questions involve:
IS NULL
IS NOT NULL
COALESCE()
and three-valued logic.
4. Practice JOINs
You should be comfortable explaining:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
CROSS JOIN
SELF JOIN
5. Learn Query Optimization
For experienced roles, interviewers may ask about:
- Indexes
- Execution plans
- Query performance
- Table scans
- Index scans/seeks
- Composite indexes
- Statistics
- Join strategies
6. Understand Database Design
Be prepared to explain:
- Primary keys
- Foreign keys
- Normalization
- Relationships
- Constraints
- Indexes
- Transactions
7. Practice Real-World Problems
Instead of solving only simple syntax questions, practice scenarios such as:
E-commerce orders
Employee management
Bank transactions
Customer analytics
Product inventory
User authentication
Course enrollment
These scenarios help you understand how SQL is used in real applications.
SQL Interview Questions by Difficulty
| Level | Topics |
|---|---|
| Beginner | SELECT, WHERE, ORDER BY, INSERT, UPDATE, DELETE |
| Beginner | Primary Key, Foreign Key, Constraints |
| Intermediate | JOINs, GROUP BY, HAVING, Aggregate Functions |
| Intermediate | Subqueries, CTEs, Views |
| Intermediate | UNION, EXISTS, CASE |
| Advanced | Window Functions |
| Advanced | Transactions and ACID |
| Advanced | Indexes and Execution Plans |
| Advanced | Query Optimization |
| Advanced | Normalization and Database Design |
| Advanced | Deadlocks and Concurrency |
Most Important SQL Interview Topics
If you have limited preparation time, prioritize these topics:
SQL Basics
SELECT
WHERE
ORDER BY
DISTINCT
LIMIT / TOP
Aggregation
COUNT
SUM
AVG
MIN
MAX
GROUP BY
HAVING
Joins
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL OUTER JOIN
SELF JOIN
CROSS JOIN
Database Design
Primary Key
Foreign Key
Constraints
Normalization
Relationships
Advanced SQL
Subqueries
CTEs
Window Functions
Views
Transactions
Indexes
Execution Plans
SQL Interview Cheat Sheet
| Concept | Key Point |
|---|---|
| SQL | Language for working with relational databases |
| SELECT | Retrieves data |
| WHERE | Filters rows |
| GROUP BY | Groups rows |
| HAVING | Filters groups |
| ORDER BY | Sorts results |
| JOIN | Combines related tables |
| PRIMARY KEY | Uniquely identifies rows |
| FOREIGN KEY | Maintains relationships |
| UNIQUE | Prevents duplicate values |
| NOT NULL | Requires a value |
| CHECK | Validates a condition |
| DEFAULT | Provides a default value |
| INDEX | Can improve query performance |
| VIEW | Stored query exposed like a table |
| CTE | Named query expression |
| Subquery | Query inside another query |
| Transaction | Logical unit of database work |
| ACID | Transaction properties |
| Normalization | Reduces unnecessary duplication |
| Window Function | Calculates across related rows without collapsing rows |
Frequently Asked SQL Interview Questions
What is SQL?
SQL is a language used to manage and query relational databases.
What is a primary key?
A primary key uniquely identifies each row in a table.
What is a foreign key?
A foreign key references a key in another table and helps maintain referential integrity.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping, while HAVING filters groups after aggregation.
What is the difference between DELETE, TRUNCATE, and DROP?
DELETE removes selected rows, TRUNCATE removes all rows while keeping the table structure, and DROP removes the table itself.
What is an index?
An index is a database structure that can improve the performance of certain queries.
What is normalization?
Normalization organizes data to reduce unnecessary duplication and improve data integrity.
What is a JOIN?
A JOIN combines data from related tables.
What is a subquery?
A subquery is a query nested inside another SQL statement.
What is a CTE?
A Common Table Expression provides a named temporary result set within a SQL statement.
What is a window function?
A window function performs calculations across related rows while preserving individual rows in the result.
What is ACID?
ACID stands for Atomicity, Consistency, Isolation, and Durability.
How do you optimize a slow SQL query?
Start by examining the execution plan, then review indexes, filtering, joins, selected columns, data volume, and database statistics/configuration.
Final SQL Interview Preparation Checklist
Before attending an SQL interview, make sure you can confidently explain:
SQL Fundamentals
- What SQL is
- What a relational database is
- Tables, rows, and columns
- SQL data types
- NULL
SQL Queries
- SELECT
- WHERE
- ORDER BY
- GROUP BY
- HAVING
- DISTINCT
- CASE
Joins
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
- SELF JOIN
- CROSS JOIN
Database Design
- Primary keys
- Foreign keys
- Composite keys
- Constraints
- Relationships
- Normalization
- Denormalization
Performance
- Indexes
- Composite indexes
- Query execution plans
- EXPLAIN
- Query optimization
Advanced SQL
- Subqueries
- CTEs
- Recursive CTEs
- Views
- Window functions
- Transactions
- ACID
- Isolation levels
- Deadlocks
Practical Queries
Practice questions involving:
- Second-highest salary
- Duplicate records
- Highest salary per department
- Employees without departments
- Customers with orders
- Customers without orders
- Running totals
- Ranking
- Top-N records
- Department averages
- Date-based reports
Conclusion
SQL interviews are not only about remembering SQL commands. Interviewers want to know whether you understand how relational databases work and how to solve real-world data problems efficiently.
Start with the fundamentals:
SELECT
WHERE
ORDER BY
GROUP BY
Then move to:
JOINs
Aggregate Functions
Subqueries
CTEs
After that, learn:
Indexes
Database Design
Normalization
Transactions
Window Functions
Query Optimization
Most importantly, practice writing SQL queries instead of only reading them.
A strong SQL candidate should be able to look at a real-world database problem, understand the relationships between tables, choose an appropriate SQL technique, write a correct query, and explain why that query works.
The best way to prepare for an SQL interview is to combine SQL theory with practical query-solving and database design knowledge.
