Backend interview prep

Start

SELECT, WHERE & ORDER BY Interview Questions

Master SQL SELECT, WHERE, ORDER BY, DISTINCT, LIMIT, OFFSET, filtering, sorting, operators, NULL handling, and production query best practices with interview-focused questions and examples.

Introduction

The majority of SQL queries written in enterprise applications use

  • SELECT
  • WHERE
  • ORDER BY

These statements allow applications to

  • Retrieve Data
  • Filter Records
  • Sort Results
  • Limit Output
  • Build Reports
  • Search Business Data

Examples

  • Banking Applications
  • E-Commerce Websites
  • HR Systems
  • Healthcare Systems
  • Social Media Platforms

Understanding these statements thoroughly is essential for every SQL interview.

Query Execution Architecture

flowchart LR

Application --> SqlQuery["SQL Query"]

SqlQuery["SQL Query"] --> Database

Database --> Filtering

Filtering --> Sorting

Sorting --> Result

Sample Employee Table

ID Name Department Salary City
1 John IT 90000 Dallas
2 Alice HR 75000 Austin
3 David IT 85000 Dallas
4 Bob Finance 95000 Houston
5 Emma HR 70000 Austin

1. What is SELECT?

Answer

SELECT retrieves data from one or more tables.

Syntax

SELECT column_name
FROM table_name;

Example

SELECT name
FROM Employee;

Result

John

Alice

David

2. How do you retrieve all columns?

Use

SELECT *
FROM Employee;

Returns every column.

3. Is SELECT * recommended?

No.

Production systems should retrieve

only required columns.

Bad

SELECT *
FROM Employee;

Good

SELECT id,
       name,
       salary
FROM Employee;

4. What is WHERE?

WHERE filters rows

before returning results.

Syntax

SELECT *
FROM Employee
WHERE salary > 80000;

WHERE Flow

flowchart LR

Table --> WHERE

WHERE --> MatchingRows["Matching Rows"]

5. Which operators can be used in WHERE?

Common operators

  • =
  • <>
  • !=
  • <

  • =

  • <=

Example

SELECT *
FROM Employee
WHERE salary >= 80000;

6. What is AND?

AND requires

all conditions

to be true.

Example

SELECT *
FROM Employee
WHERE department='IT'
AND salary>80000;

7. What is OR?

OR requires

at least one condition

to be true.

SELECT *
FROM Employee
WHERE department='IT'
OR department='HR';

8. What is NOT?

NOT reverses a condition.

SELECT *
FROM Employee
WHERE NOT department='IT';

Logical Operators

AND

All True

↓

Return Row

OR

Any True

↓

Return Row

NOT

Reverse Result

9. What is LIKE?

LIKE searches

using patterns.

Example

SELECT *
FROM Employee
WHERE name LIKE 'J%';

Returns

John

LIKE Wildcards

Wildcard Meaning
% Any characters
_ Single character

10. What is IN?

IN checks

multiple values.

Example

SELECT *
FROM Employee
WHERE department IN ('IT','HR');

11. What is BETWEEN?

BETWEEN filters

within a range.

SELECT *
FROM Employee
WHERE salary
BETWEEN 70000 AND 90000;

12. What is IS NULL?

NULL cannot be compared

using

=

Correct

SELECT *
FROM Employee
WHERE city IS NULL;

13. What is IS NOT NULL?

Returns

rows

having values.

SELECT *
FROM Employee
WHERE city IS NOT NULL;

NULL Handling

flowchart LR

Column --> NULL

Column --> NotNull["NOT NULL"]

14. What is DISTINCT?

DISTINCT removes duplicates.

Example

SELECT DISTINCT department
FROM Employee;

Result

IT

HR

Finance

15. What is ORDER BY?

ORDER BY sorts

query results.

Default

Ascending.

SELECT *
FROM Employee
ORDER BY salary;

ORDER BY Flow

flowchart LR

Rows --> Sort --> Output

16. How do you sort descending?

Use

DESC.

SELECT *
FROM Employee
ORDER BY salary DESC;

17. How do you sort ascending?

Ascending

is default.

ORDER BY salary ASC;

18. Can ORDER BY use multiple columns?

Yes.

Example

SELECT *
FROM Employee
ORDER BY department,
salary DESC;

Multi Column Sorting

Department

↓

Salary

↓

Final Result

19. What is LIMIT?

LIMIT restricts

the number of rows returned.

MySQL/PostgreSQL

SELECT *
FROM Employee
LIMIT 5;

SQL Server

SELECT TOP 5 *
FROM Employee;

Oracle (12c+)

FETCH FIRST 5 ROWS ONLY;

20. What is OFFSET?

OFFSET skips rows.

SELECT *
FROM Employee
LIMIT 10
OFFSET 20;

Useful for pagination.

Pagination

Page 1

Rows 1-10

↓

Page 2

Rows 11-20

21. Banking Example

SELECT *
FROM Accounts
WHERE balance > 100000;

Retrieve premium customers.

22. E-Commerce Example

SELECT *
FROM Products
WHERE category='Mobile'
ORDER BY price;

Sort mobiles by price.

23. HR Example

SELECT *
FROM Employee
WHERE department='IT'
ORDER BY salary DESC;

Highest-paid IT employees.

24. Healthcare Example

SELECT *
FROM Patients
WHERE blood_group='O+';

25. Social Media Example

SELECT *
FROM Posts
ORDER BY created_date DESC;

Latest posts.

26. Production Example

SELECT
customer_id,
customer_name
FROM Customers
WHERE status='ACTIVE'
ORDER BY created_date DESC
LIMIT 20;

Dashboard query.

27. Common Mistakes

  • Using SELECT *
  • Missing WHERE clause
  • Using = NULL
  • Forgetting ORDER BY
  • Using LIKE unnecessarily
  • Returning too many rows

28. Performance Tips

  • Retrieve only required columns.
  • Filter using indexed columns.
  • Avoid unnecessary DISTINCT.
  • Use LIMIT for UI queries.
  • Avoid leading wildcard searches (LIKE '%abc') when possible, as they often prevent index usage.
  • Index columns used in WHERE and ORDER BY when appropriate.

29. Query Execution Order

Logical execution order

FROM

↓

WHERE

↓

SELECT

↓

DISTINCT

↓

ORDER BY

↓

LIMIT

30. What are the best practices?

  • Avoid SELECT * in production.
  • Always filter unnecessary rows.
  • Use ORDER BY only when needed.
  • Use LIMIT for pagination.
  • Handle NULL correctly.
  • Prefer IN over multiple OR conditions where appropriate.
  • Use indexes for filtering columns.
  • Select only required columns.
  • Test query performance.
  • Use meaningful aliases.

Query Workflow

flowchart LR

FROM --> WhereSelectOrderBy["WHERE --> SELECT --> ORDER BY --> LIMIT --> Result"]

Enterprise Best Practices

  • Never use SELECT * in production APIs.
  • Use WHERE clauses to minimize scanned rows.
  • Create indexes on frequently filtered columns.
  • Use ORDER BY carefully on large datasets.
  • Paginate large result sets.
  • Handle NULL values correctly.
  • Review execution plans for slow queries.
  • Keep SQL readable using aliases.
  • Avoid unnecessary sorting.
  • Benchmark production queries.

Quick Revision

Topic Key Point
SELECT Retrieve Data
WHERE Filter Rows
ORDER BY Sort Rows
DISTINCT Remove Duplicates
LIMIT Restrict Rows
OFFSET Skip Rows
LIKE Pattern Matching
IN Multiple Values
BETWEEN Range Search
IS NULL Check Missing Values

Interview Tips

Interviewers frequently ask

  • What is SELECT?
  • Why avoid SELECT *?
  • Explain WHERE.
  • LIKE vs IN.
  • IS NULL vs = NULL.
  • ORDER BY ASC vs DESC.
  • LIMIT vs OFFSET.
  • Execution order of SELECT queries.
  • How do you paginate SQL results?
  • How do you optimize SELECT queries?

A strong interview explanation is:

"SELECT retrieves data from a table, WHERE filters rows before they are returned, and ORDER BY sorts the final result set. In production systems, we avoid SELECT *, retrieve only required columns, use indexed columns in WHERE clauses, apply ORDER BY only when necessary, and paginate results using LIMIT/OFFSET or database-specific alternatives. Proper indexing and execution plan analysis are essential for high-performance queries."

Summary

SELECT, WHERE, and ORDER BY form the foundation of SQL querying and are used in nearly every enterprise application. They enable efficient data retrieval, filtering, and sorting, while features such as DISTINCT, LIKE, IN, BETWEEN, LIMIT, and OFFSET support advanced search and pagination scenarios.

Understanding these commands, execution order, optimization techniques, and production best practices is essential for Backend Developers, Database Engineers, Java Developers, and Solution Architects working with relational databases.