SQL
Queries Handbook
100 Most Used Queries
■ SELECT & Filtering ■ JOINs ■ Aggregates
■ Subqueries ■ Window Functions ■ DDL & DML
■ Indexes & Views ■ Stored Procedures ■ Transactions
Exam Prep · Interview Ready · Quick Revision
Covers: MySQL · PostgreSQL · SQL Server · Oracle · SQLite syntax with notes on differences
,SQL Queries Handbook | 100 Most Used Queries Page 2
■ Section 1 — SELECT & Filtering Basics
#001
Select All Columns
Retrieve every column and row from a table. Use sparingly in production — fetching unused columns wastes
bandwidth.
SELECT *
FROM employees;
✎ Prefer listing explicit columns in production code. SELECT * is fine for quick exploration.
#002
Select Specific Columns
Retrieve only the columns you need — faster and cleaner.
SELECT first_name, last_name, salary
FROM employees;
#003
Filter with WHERE
Return only rows that match a condition.
SELECT *
FROM employees
WHERE department = 'Engineering';
#004
Multiple Conditions with AND / OR
Combine filters. AND requires both conditions; OR requires at least one.
SELECT *
FROM employees
WHERE department = 'Sales'
AND salary > 50000;
✎ Wrap OR conditions in parentheses to avoid precedence bugs: WHERE a AND (b OR c).
#005
NOT Operator
Exclude rows matching a condition.
SELECT *
FROM employees
WHERE NOT department = 'HR';
#006
DISTINCT — Remove Duplicates
Return unique values only.
SELECT DISTINCT department
FROM employees;
✎ DISTINCT applies across all selected columns, not just the first one.
Exam Prep · Interview Cracker · Quick Reference
, SQL Queries Handbook | 100 Most Used Queries Page 3
#007
ORDER BY — Sort Results
Sort results ascending (default) or descending.
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;
#008
LIMIT / TOP — Restrict Rows
Return only the first N rows. Syntax varies by database.
-- MySQL / PostgreSQL / SQLite
SELECT * FROM employees LIMIT 10;
-- SQL Server
SELECT TOP 10 * FROM employees;
-- Oracle
SELECT * FROM employees WHERE ROWNUM <= 10;
#009
OFFSET — Pagination
Skip N rows before returning results. Combined with LIMIT for page-based results.
SELECT *
FROM employees
ORDER BY employee_id
LIMIT 10 OFFSET 20; -- page 3 (rows 21-30)
✎ Always include ORDER BY with OFFSET — without it, the 'skipped' rows are undefined.
#010
BETWEEN — Range Filter
Filter rows where a column value falls within an inclusive range.
SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
✎ BETWEEN is inclusive on both ends — equivalent to col >= low AND col <= high.
#011
IN — Match a List
Filter rows where a column matches any value in a list.
SELECT *
FROM employees
WHERE department IN ('Sales', 'Marketing', 'HR');
#012
NOT IN
Exclude rows matching any value in a list. Beware NULL values in the list — NOT IN returns no rows if the list
contains NULL.
SELECT *
FROM employees
WHERE department NOT IN ('Finance', 'Legal');
✎ If the subquery can return NULL, prefer NOT EXISTS over NOT IN.
Exam Prep · Interview Cracker · Quick Reference
Queries Handbook
100 Most Used Queries
■ SELECT & Filtering ■ JOINs ■ Aggregates
■ Subqueries ■ Window Functions ■ DDL & DML
■ Indexes & Views ■ Stored Procedures ■ Transactions
Exam Prep · Interview Ready · Quick Revision
Covers: MySQL · PostgreSQL · SQL Server · Oracle · SQLite syntax with notes on differences
,SQL Queries Handbook | 100 Most Used Queries Page 2
■ Section 1 — SELECT & Filtering Basics
#001
Select All Columns
Retrieve every column and row from a table. Use sparingly in production — fetching unused columns wastes
bandwidth.
SELECT *
FROM employees;
✎ Prefer listing explicit columns in production code. SELECT * is fine for quick exploration.
#002
Select Specific Columns
Retrieve only the columns you need — faster and cleaner.
SELECT first_name, last_name, salary
FROM employees;
#003
Filter with WHERE
Return only rows that match a condition.
SELECT *
FROM employees
WHERE department = 'Engineering';
#004
Multiple Conditions with AND / OR
Combine filters. AND requires both conditions; OR requires at least one.
SELECT *
FROM employees
WHERE department = 'Sales'
AND salary > 50000;
✎ Wrap OR conditions in parentheses to avoid precedence bugs: WHERE a AND (b OR c).
#005
NOT Operator
Exclude rows matching a condition.
SELECT *
FROM employees
WHERE NOT department = 'HR';
#006
DISTINCT — Remove Duplicates
Return unique values only.
SELECT DISTINCT department
FROM employees;
✎ DISTINCT applies across all selected columns, not just the first one.
Exam Prep · Interview Cracker · Quick Reference
, SQL Queries Handbook | 100 Most Used Queries Page 3
#007
ORDER BY — Sort Results
Sort results ascending (default) or descending.
SELECT first_name, salary
FROM employees
ORDER BY salary DESC;
#008
LIMIT / TOP — Restrict Rows
Return only the first N rows. Syntax varies by database.
-- MySQL / PostgreSQL / SQLite
SELECT * FROM employees LIMIT 10;
-- SQL Server
SELECT TOP 10 * FROM employees;
-- Oracle
SELECT * FROM employees WHERE ROWNUM <= 10;
#009
OFFSET — Pagination
Skip N rows before returning results. Combined with LIMIT for page-based results.
SELECT *
FROM employees
ORDER BY employee_id
LIMIT 10 OFFSET 20; -- page 3 (rows 21-30)
✎ Always include ORDER BY with OFFSET — without it, the 'skipped' rows are undefined.
#010
BETWEEN — Range Filter
Filter rows where a column value falls within an inclusive range.
SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
✎ BETWEEN is inclusive on both ends — equivalent to col >= low AND col <= high.
#011
IN — Match a List
Filter rows where a column matches any value in a list.
SELECT *
FROM employees
WHERE department IN ('Sales', 'Marketing', 'HR');
#012
NOT IN
Exclude rows matching any value in a list. Beware NULL values in the list — NOT IN returns no rows if the list
contains NULL.
SELECT *
FROM employees
WHERE department NOT IN ('Finance', 'Legal');
✎ If the subquery can return NULL, prefer NOT EXISTS over NOT IN.
Exam Prep · Interview Cracker · Quick Reference
