Definition
- SELECT: Retrieves specific columns.
SELECT name, salary FROM employeesreturns only name and salary columns. - WHERE: Filters rows using conditions. Supports =, <>, >, <, BETWEEN, IN, LIKE, IS NULL. Runs before GROUP BY.
- ORDER BY: Sorts the result. ASC (ascending, default) or DESC (descending). Can sort by multiple columns.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- LIMIT/TOP: Restricts output to N rows. Always pair with ORDER BY.
- NULL handling: Use IS NULL, IS NOT NULL, COALESCE. Never use = NULL.
This section is a focused tutorial on SQL Basics. Each question teaches you to think through the problem before writing SQL, build the query step by step, understand how the database executes it, and optimize for performance.
Understanding the Concepts
## SQL Basics: SELECT, WHERE, ORDER BY, DISTINCT
SELECT retrieves columns from a table. WHERE filters rows based on conditions. ORDER BY sorts the result. DISTINCT removes duplicate rows. LIMIT restricts the number of rows returned. These are the building blocks of every SQL query.
Sample Tables
employees table (shared across all queries in this section):
| id | name | salary | department_id | manager_id | join_date | city | status |
|---|---|---|---|---|---|---|---|
| 1 | Rajesh | 90000 | 101 | NULL | 2020-01-15 | Mumbai | Active |
| 2 | Priya | 75000 | 101 | 1 | 2021-03-20 | Mumbai | Active |
| 3 | Amit | 95000 | 102 | 1 | 2019-06-10 | Delhi | Active |
| 4 | Sneha | 60000 | 102 | 3 | 2022-08-01 | Delhi | Inactive |
| 5 | Vikram | 90000 | 103 | 1 | 2020-11-25 | Chennai | Active |
| 6 | Meena | 75000 | 103 | 5 | 2023-02-14 | Chennai | Active |
| 7 | Rajesh | 55000 | 104 | 1 | 2024-05-10 | Pune | Active |
---
Q1. Compare two tables and find rows with differences in any column (all columns)
Problem: Compare two tables and find rows with differences in any column (all columns).
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT *
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.id = t2.id
WHERE t1.col1 IS DISTINCT FROM t2.col1
OR t1.col2 IS DISTINCT FROM t2.col2
OR t1.col3 IS DISTINCT FROM t2.col3;
Step-by-step how the database executes this:
- FROM: Reads table
table1. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q2. Find employees who don’t have a department assigned
Problem: Find employees who don’t have a department assigned.
How to think before writing:
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT *
FROM employees
WHERE department_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the WHERE column(s) to avoid a full table scan.
---
Q3. List employees whose names start and end with the same letter
Problem: List employees whose names start and end with the same letter.
How to think before writing:
1. What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
GET columns FROM table
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT *
FROM employees
WHERE LEFT(name, 1) = RIGHT(name, 1);
Questions
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the WHERE column(s) to avoid a full table scan.
---
Q4. Retrieve the Nth highest salary from the employees table
Problem: Retrieve the Nth highest salary from the employees table.
How to think before writing:
- Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
- How should the result be sorted? ASC (ascending) or DESC (descending)?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;
(Replace N with the desired rank, e.g., N=3 for third
highest)
Step-by-step how the database executes this:
- FROM: Reads table
employees. - SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
- LIMIT/TOP: Returns only the first N rows from the sorted result.
Optimization: Run EXPLAIN ANALYZE to check the execution plan and add indexes where needed.
---
Q5. Find employees with no manager assigned
Problem: Find employees with no manager assigned.
How to think before writing:
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Am I looking for missing data? Use IS NULL (not = NULL).
Pseudo query (plain English first):
GET columns FROM table
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT *
FROM employees
WHERE manager_id IS NULL;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the WHERE column(s) to avoid a full table scan.
---
Q6. Find the nth highest salary in a company (e.g., 5th highest)
Problem: Find the nth highest salary in a company (e.g., 5th highest).
How to think before writing:
- How should the result be sorted? ASC (ascending) or DESC (descending)?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET n - 1 ROWS FETCH NEXT 1 ROW ONLY;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
- ORDER BY: Sorts the final result set.
Optimization: Run EXPLAIN ANALYZE to check the execution plan and add indexes where needed.
---
Q7. Find employees who have worked on at least one project with a budget over $1,000,000
Problem: Find employees who have worked on at least one project with a budget over $1,000,000.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT DISTINCT pa.employee_id
FROM project_assignments pa
JOIN projects p ON pa.project_id = p.project_id
WHERE p.budget > 1000000;
Step-by-step how the database executes this:
- FROM: Reads table
project_assignments. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q8. Find customers who have placed orders but never used a discount
Problem: Find customers who have placed orders but never used a discount.
How to think before writing:
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
GET columns FROM table
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT DISTINCT customer_id
FROM orders
WHERE discount_used = FALSE;
🔷 Medium to Advanced SQL Questions (201–300)
Step-by-step how the database executes this:
- FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the WHERE column(s) to avoid a full table scan.
---
Q9. Find employees with a salary greater than all employees in department 10
Problem: Find employees with a salary greater than all employees in department 10.
How to think before writing:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
SELECT *
FROM employees
WHERE salary > ALL (
SELECT salary FROM employees WHERE
department_id = 10
);
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.
---
Q10. Find customers who have placed orders but never paid by credit card
Problem: Find customers who have placed orders but never paid by credit card.
How to think before writing:
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
GET columns FROM table
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT DISTINCT customer_id
FROM orders
WHERE payment_method != 'Credit Card';
Step-by-step how the database executes this:
- FROM: Reads table
orders. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the WHERE column(s) to avoid a full table scan.
---
Q11. Find employees who have been assigned projects outside their department
Problem: Find employees who have been assigned projects outside their department.
How to think before writing:
- Which tables do I need? How are they related? What column connects them?
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT DISTINCT e.id, e.name
FROM employees e
JOIN project_assignments pa ON e.id =
pa.employee_id
JOIN projects p ON pa.project_id = p.project_id
WHERE e.department_id != p.department_id;
Step-by-step how the database executes this:
- FROM: Reads table
employees. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- DISTINCT: Removes duplicate rows by comparing all selected columns.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Q12. Find products that have been discontinued but still have sales
Problem: Find products that have been discontinued but still have sales.
How to think before writing:
1. Which tables do I need? How are they related? What column connects them?
Pseudo query (plain English first):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT p.product_id
FROM products p
JOIN sales s ON p.product_id = s.product_id
WHERE p.discontinued = TRUE;
Step-by-step how the database executes this:
- FROM: Reads table
products. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.
---
Key Takeaway
This section covered 12 interview questions on SQL Basics: SELECT, WHERE, ORDER BY, DISTINCT. Each question followed the same learning pattern: understand the problem, think about the approach, write a pseudo query in plain English, translate to SQL, understand the execution order, and consider optimization.
SELECT retrieves columns from a table. WHERE filters rows based on conditions. ORDER BY sorts the result. DISTINCT removes duplicate rows. LIMIT restricts the number of rows returned. These are the building blocks of every SQL query.
Practice each query on the shared sample table. If you can solve all 12 without looking at the answer, you have mastered this concept.
---