Back to SQL Practice: 331 Real Interview Queries
Q1Question 1 of 9

SQL Basics: SELECT, WHERE, ORDER BY, DISTINCT

Answer: SELECT retrieves columns, WHERE filters rows, ORDER BY sorts results, DISTINCT removes duplicates, and LIMIT restricts row count. These are the building blocks of every SQL query.

Key Concepts
  • SELECT retrieves specific columns from a table. Use SELECT * only for debugging, never in production queries.
  • WHERE filters rows before any grouping. It cannot use aggregate functions like COUNT or SUM. Use comparison operators (=, <>, >, <, >=, <=), BETWEEN, IN, LIKE, and IS NULL.
  • ORDER BY sorts the result set. ASC is ascending (default), DESC is descending. You can sort by multiple columns: ORDER BY col1 DESC, col2 ASC.
  • DISTINCT removes duplicate rows from the result. It compares ALL selected columns to decide uniqueness, not just one.
  • LIMIT (MySQL/PostgreSQL) or TOP (SQL Server) or FETCH FIRST (ANSI SQL) restricts the number of rows returned. Always use with ORDER BY for deterministic results.
  • NULL is not a value, it means unknown. Use IS NULL or IS NOT NULL to check. NULL = NULL returns NULL (not true). Use COALESCE(col, default) to handle NULLs.
  • SQL execution order: FROM > WHERE > GROUP BY > HAVING > SELECT > DISTINCT > ORDER BY > LIMIT. Write in this mental order, even though SQL syntax starts with SELECT.

Definition

SELECT retrieves columns, WHERE filters rows, ORDER BY sorts results, DISTINCT removes duplicates, and LIMIT restricts row count. These are the building blocks of every SQL query.
  • SELECT: Retrieves specific columns. SELECT name, salary FROM employees returns 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):

idnamesalarydepartment_idmanager_idjoin_datecitystatus
1Rajesh90000101NULL2020-01-15MumbaiActive
2Priya7500010112021-03-20MumbaiActive
3Amit9500010212019-06-10DelhiActive
4Sneha6000010232022-08-01DelhiInactive
5Vikram9000010312020-11-25ChennaiActive
6Meena7500010352023-02-14ChennaiActive
7Rajesh5500010412024-05-10PuneActive

---

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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table table1.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. What condition filters the rows I need? Write the WHERE clause for that condition.
  2. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT * 
FROM employees 
WHERE department_id IS NULL;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. 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):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT * 
FROM employees 
WHERE LEFT(name, 1) = RIGHT(name, 1); 
 
Questions

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. 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:

  1. Do I need to rank rows, handling ties? RANK skips after ties, DENSE_RANK does not.
  2. How should the result be sorted? ASC (ascending) or DESC (descending)?
  3. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. SELECT: Projects the final columns and evaluates expressions/aliases.
  3. DISTINCT: Removes duplicate rows by comparing all selected columns.
  4. ORDER BY: Sorts the final result set.
  5. 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:

  1. What condition filters the rows I need? Write the WHERE clause for that condition.
  2. Am I looking for missing data? Use IS NULL (not = NULL).

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT * 
FROM employees 
WHERE manager_id IS NULL;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. 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:

  1. How should the result be sorted? ASC (ascending) or DESC (descending)?
  2. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. SELECT: Projects the final columns and evaluates expressions/aliases.
  3. DISTINCT: Removes duplicate rows by comparing all selected columns.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table project_assignments.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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:

  1. What condition filters the rows I need? Write the WHERE clause for that condition.
  2. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT * 
FROM employees 
WHERE salary > ALL ( 
  SELECT salary FROM employees WHERE 
department_id = 10 
);

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. 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:

  1. What condition filters the rows I need? Write the WHERE clause for that condition.
  2. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT DISTINCT customer_id 
FROM orders 
WHERE payment_method != 'Credit Card';

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. 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:

  1. Which tables do I need? How are they related? What column connects them?
  2. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table employees.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.
  5. 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):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
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:

  1. FROM: Reads table products.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. 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.

---

Share
Use ← → arrow keys to navigate