Smart SQL Tools for Every Database

SQL Education Hub

Top 100 SQL Examples

Practical copy-and-paste SQL examples for beginners, students, developers, and data analysts. This page covers SELECT queries, filters, joins, grouping, subqueries, date functions, string functions, CTEs, and window functions.

Use this page as a SQL cheat sheet. Most examples are easy to adapt for MySQL, PostgreSQL, SQL Server, SQLite, and Oracle.

Browse Top 100 Queries Open SQL Builder Open Top 10 SQL Errors

Table of Contents

  1. Select all columns
  2. Select specific columns
  3. Column alias
  4. Distinct values
  5. Limit rows
  6. SQL Server TOP
  7. First 10 rows by dialect
  8. SQL comments
  9. Select a constant
  10. Calculated column
  11. WHERE equals
  12. WHERE not equals
  13. Greater than
  14. BETWEEN
  15. IN
  16. NOT IN
  17. LIKE
  18. Starts with
  19. Ends with
  20. IS NULL
  21. IS NOT NULL
  22. Multiple AND conditions
  23. OR conditions
  24. Parentheses in WHERE
  25. Case-insensitive search
  26. ORDER BY ascending
  27. ORDER BY descending
  28. Order by multiple columns
  29. Order by date
  30. Order by calculated value
  31. LIMIT and OFFSET
  32. Pagination query
  33. Random row
  34. Latest record
  35. Oldest record
  36. COUNT rows
  37. COUNT distinct
  38. SUM
  39. AVG
  40. MIN
  41. MAX
  42. GROUP BY one column
  43. GROUP BY multiple columns
  44. HAVING
  45. GROUP BY with ORDER BY
  46. INNER JOIN
  47. LEFT JOIN
  48. RIGHT JOIN
  49. FULL OUTER JOIN
  50. SELF JOIN
  51. Join three tables
  52. JOIN with aliases
  53. JOIN and GROUP BY
  54. Find unmatched rows
  55. Many-to-many join
  56. Subquery in WHERE
  57. Subquery in SELECT
  58. Subquery in FROM
  59. EXISTS
  60. NOT EXISTS
  61. Correlated subquery
  62. Compare with average
  63. Find duplicates
  64. Find max per group
  65. Delete duplicates
  66. INSERT one row
  67. INSERT multiple rows
  68. INSERT from SELECT
  69. UPDATE one row
  70. UPDATE multiple rows
  71. UPDATE with JOIN
  72. DELETE one row
  73. DELETE with condition
  74. DELETE duplicates
  75. UPSERT / merge
  76. Current date
  77. Current timestamp
  78. Add days to date
  79. Subtract days
  80. Date difference
  81. Extract year
  82. Extract month
  83. Format date
  84. Last 7 days
  85. Group by month
  86. Concatenate strings
  87. Uppercase
  88. Lowercase
  89. Trim spaces
  90. Substring
  91. Replace text
  92. String length
  93. Split string
  94. Search inside string
  95. Cast number to text
  96. CTE
  97. Recursive CTE
  98. ROW_NUMBER
  99. RANK
  100. Running total

Basic SQL

Example 1

Select all columns

Return every column from a table.

SELECT * FROM users;

Tip: Try changing table and column names to match your own database.

Example 2

Select specific columns

Return only the columns you need.

SELECT id, name, email FROM users;

Tip: Try changing table and column names to match your own database.

Example 3

Column alias

Rename a column in the result.

SELECT first_name AS name FROM users;

Tip: Try changing table and column names to match your own database.

Example 4

Distinct values

Remove duplicate values.

SELECT DISTINCT country FROM customers;

Tip: Try changing table and column names to match your own database.

Example 5

Limit rows

Return a small number of rows.

SELECT * FROM orders LIMIT 10;

Tip: Try changing table and column names to match your own database.

Example 6

SQL Server TOP

SQL Server uses TOP instead of LIMIT.

SELECT TOP 10 * FROM orders;

Tip: Try changing table and column names to match your own database.

Example 7

First 10 rows by dialect

Common row limiting syntax across dialects.

-- MySQL/PostgreSQL/SQLite
SELECT * FROM products LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM products;

-- Oracle
SELECT * FROM products FETCH FIRST 10 ROWS ONLY;

Tip: Try changing table and column names to match your own database.

Example 8

SQL comments

Add comments to explain a query.

-- Get active users
SELECT * FROM users WHERE status = 'active';

Tip: Try changing table and column names to match your own database.

Example 9

Select a constant

Return a fixed value.

SELECT 'Hello SQL' AS message;

Tip: Try changing table and column names to match your own database.

Example 10

Calculated column

Create a value from existing columns.

SELECT price, quantity, price * quantity AS total FROM order_items;

Tip: Try changing table and column names to match your own database.

WHERE Filters

Example 11

WHERE equals

Filter rows by exact value.

SELECT * FROM users WHERE status = 'active';

Tip: Try changing table and column names to match your own database.

Example 12

WHERE not equals

Exclude a value.

SELECT * FROM users WHERE status <> 'inactive';

Tip: Try changing table and column names to match your own database.

Example 13

Greater than

Find rows above a number.

SELECT * FROM products WHERE price > 100;

Tip: Try changing table and column names to match your own database.

Example 14

BETWEEN

Find values inside a range.

SELECT * FROM orders WHERE total BETWEEN 50 AND 200;

Tip: Try changing table and column names to match your own database.

Example 15

IN

Match any value from a list.

SELECT * FROM users WHERE country IN ('US', 'Canada', 'Korea');

Tip: Try changing table and column names to match your own database.

Example 16

NOT IN

Exclude values from a list.

SELECT * FROM users WHERE role NOT IN ('admin', 'owner');

Tip: Try changing table and column names to match your own database.

Example 17

LIKE

Search using a pattern.

SELECT * FROM customers WHERE name LIKE '%kim%';

Tip: Try changing table and column names to match your own database.

Example 18

Starts with

Find text that begins with letters.

SELECT * FROM customers WHERE email LIKE 'emily%';

Tip: Try changing table and column names to match your own database.

Example 19

Ends with

Find text that ends with letters.

SELECT * FROM files WHERE filename LIKE '%.pdf';

Tip: Try changing table and column names to match your own database.

Example 20

IS NULL

Find missing values.

SELECT * FROM users WHERE phone IS NULL;

Tip: Try changing table and column names to match your own database.

Example 21

IS NOT NULL

Find rows with a value.

SELECT * FROM users WHERE phone IS NOT NULL;

Tip: Try changing table and column names to match your own database.

Example 22

Multiple AND conditions

All conditions must be true.

SELECT * FROM orders WHERE status = 'paid' AND total > 100;

Tip: Try changing table and column names to match your own database.

Example 23

OR conditions

At least one condition must be true.

SELECT * FROM users WHERE city = 'Nashville' OR city = 'Atlanta';

Tip: Try changing table and column names to match your own database.

Example 24

Parentheses in WHERE

Control AND/OR logic.

SELECT * FROM users WHERE status = 'active' AND (city = 'Nashville' OR city = 'Franklin');

Tip: Try changing table and column names to match your own database.

Example 25

Case-insensitive search

Search text without caring about letter case.

-- PostgreSQL
SELECT * FROM users WHERE email ILIKE '%gmail%';

-- MySQL common option
SELECT * FROM users WHERE LOWER(email) LIKE '%gmail%';

Tip: Try changing table and column names to match your own database.

Sorting and Limiting

Example 26

ORDER BY ascending

Sort from low to high.

SELECT * FROM products ORDER BY price ASC;

Tip: Try changing table and column names to match your own database.

Example 27

ORDER BY descending

Sort from high to low.

SELECT * FROM products ORDER BY price DESC;

Tip: Try changing table and column names to match your own database.

Example 28

Order by multiple columns

Sort by more than one column.

SELECT * FROM users ORDER BY country ASC, name ASC;

Tip: Try changing table and column names to match your own database.

Example 29

Order by date

Show newest or oldest rows.

SELECT * FROM orders ORDER BY created_at DESC;

Tip: Try changing table and column names to match your own database.

Example 30

Order by calculated value

Sort by a computed column.

SELECT name, price * quantity AS total FROM items ORDER BY total DESC;

Tip: Try changing table and column names to match your own database.

Example 31

LIMIT and OFFSET

Skip rows for pagination.

SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 20;

Tip: Try changing table and column names to match your own database.

Example 32

Pagination query

Page 3 with 10 rows per page.

SELECT * FROM posts ORDER BY created_at DESC LIMIT 10 OFFSET 20;

Tip: Try changing table and column names to match your own database.

Example 33

Random row

Return one random row.

-- PostgreSQL/SQLite
SELECT * FROM products ORDER BY RANDOM() LIMIT 1;

-- MySQL
SELECT * FROM products ORDER BY RAND() LIMIT 1;

Tip: Try changing table and column names to match your own database.

Example 34

Latest record

Find the newest row.

SELECT * FROM orders ORDER BY created_at DESC LIMIT 1;

Tip: Try changing table and column names to match your own database.

Example 35

Oldest record

Find the oldest row.

SELECT * FROM orders ORDER BY created_at ASC LIMIT 1;

Tip: Try changing table and column names to match your own database.

Aggregation

Example 36

COUNT rows

Count all rows.

SELECT COUNT(*) AS total_users FROM users;

Tip: Try changing table and column names to match your own database.

Example 37

COUNT distinct

Count unique values.

SELECT COUNT(DISTINCT country) AS country_count FROM users;

Tip: Try changing table and column names to match your own database.

Example 38

SUM

Add numeric values.

SELECT SUM(total) AS revenue FROM orders;

Tip: Try changing table and column names to match your own database.

Example 39

AVG

Calculate average value.

SELECT AVG(price) AS average_price FROM products;

Tip: Try changing table and column names to match your own database.

Example 40

MIN

Find the smallest value.

SELECT MIN(price) AS cheapest FROM products;

Tip: Try changing table and column names to match your own database.

Example 41

MAX

Find the largest value.

SELECT MAX(price) AS most_expensive FROM products;

Tip: Try changing table and column names to match your own database.

Example 42

GROUP BY one column

Aggregate by category.

SELECT country, COUNT(*) AS users FROM users GROUP BY country;

Tip: Try changing table and column names to match your own database.

Example 43

GROUP BY multiple columns

Aggregate by more than one field.

SELECT country, status, COUNT(*) AS total FROM users GROUP BY country, status;

Tip: Try changing table and column names to match your own database.

Example 44

HAVING

Filter grouped results.

SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id HAVING COUNT(*) > 5;

Tip: Try changing table and column names to match your own database.

Example 45

GROUP BY with ORDER BY

Sort grouped results.

SELECT country, COUNT(*) AS users FROM users GROUP BY country ORDER BY users DESC;

Tip: Try changing table and column names to match your own database.

JOIN

Example 46

INNER JOIN

Return matching rows from both tables.

SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Tip: Try changing table and column names to match your own database.

Example 47

LEFT JOIN

Return all rows from the left table.

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Tip: Try changing table and column names to match your own database.

Example 48

RIGHT JOIN

Return all rows from the right table.

SELECT users.name, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

Tip: Try changing table and column names to match your own database.

Example 49

FULL OUTER JOIN

Return matched and unmatched rows from both tables.

SELECT users.name, orders.total
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

Tip: Try changing table and column names to match your own database.

Example 50

SELF JOIN

Join a table to itself.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Tip: Try changing table and column names to match your own database.

Example 51

Join three tables

Combine data from three related tables.

SELECT users.name, orders.id, products.name
FROM users
JOIN orders ON users.id = orders.user_id
JOIN products ON orders.product_id = products.id;

Tip: Try changing table and column names to match your own database.

Example 52

JOIN with aliases

Use short table names.

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

Tip: Try changing table and column names to match your own database.

Example 53

JOIN and GROUP BY

Count joined records.

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

Tip: Try changing table and column names to match your own database.

Example 54

Find unmatched rows

Find users with no orders.

SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Tip: Try changing table and column names to match your own database.

Example 55

Many-to-many join

Join through a bridge table.

SELECT s.name, c.title
FROM students s
JOIN student_courses sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id;

Tip: Try changing table and column names to match your own database.

Subqueries

Example 56

Subquery in WHERE

Filter using another query.

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

Tip: Try changing table and column names to match your own database.

Example 57

Subquery in SELECT

Show a calculated value from another query.

SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count FROM users;

Tip: Try changing table and column names to match your own database.

Example 58

Subquery in FROM

Query a temporary result set.

SELECT country, total_users FROM (SELECT country, COUNT(*) AS total_users FROM users GROUP BY country) t;

Tip: Try changing table and column names to match your own database.

Example 59

EXISTS

Check whether related rows exist.

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Tip: Try changing table and column names to match your own database.

Example 60

NOT EXISTS

Find rows without related records.

SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Tip: Try changing table and column names to match your own database.

Example 61

Correlated subquery

A subquery that uses the outer query.

SELECT * FROM orders o WHERE total > (SELECT AVG(total) FROM orders WHERE user_id = o.user_id);

Tip: Try changing table and column names to match your own database.

Example 62

Compare with average

Find products above average price.

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

Tip: Try changing table and column names to match your own database.

Example 63

Find duplicates

Find emails used more than once.

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

Tip: Try changing table and column names to match your own database.

Example 64

Find max per group

Find each customer's largest order.

SELECT * FROM orders o WHERE total = (SELECT MAX(total) FROM orders WHERE user_id = o.user_id);

Tip: Try changing table and column names to match your own database.

Example 65

Delete duplicates

Example pattern for duplicate cleanup.

DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY email
);

Tip: Try changing table and column names to match your own database.

INSERT, UPDATE, DELETE

Example 66

INSERT one row

Add one record.

INSERT INTO users (name, email) VALUES ('Emily', 'emily@example.com');

Tip: Try changing table and column names to match your own database.

Example 67

INSERT multiple rows

Add several records.

INSERT INTO users (name, email) VALUES ('Amy', 'amy@example.com'), ('John', 'john@example.com');

Tip: Try changing table and column names to match your own database.

Example 68

INSERT from SELECT

Copy rows from another table.

INSERT INTO archived_orders SELECT * FROM orders WHERE created_at < '2024-01-01';

Tip: Try changing table and column names to match your own database.

Example 69

UPDATE one row

Modify one record.

UPDATE users SET status = 'active' WHERE id = 1;

Tip: Try changing table and column names to match your own database.

Example 70

UPDATE multiple rows

Modify many records.

UPDATE products SET discontinued = true WHERE stock = 0;

Tip: Try changing table and column names to match your own database.

Example 71

UPDATE with JOIN

Update using another table.

-- MySQL
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.last_order_date = o.created_at;

Tip: Try changing table and column names to match your own database.

Example 72

DELETE one row

Remove one record.

DELETE FROM users WHERE id = 1;

Tip: Try changing table and column names to match your own database.

Example 73

DELETE with condition

Remove matching rows.

DELETE FROM logs WHERE created_at < '2024-01-01';

Tip: Try changing table and column names to match your own database.

Example 74

DELETE duplicates

Delete duplicate rows carefully.

DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);

Tip: Try changing table and column names to match your own database.

Example 75

UPSERT / merge

Insert or update if already exists.

-- PostgreSQL
INSERT INTO users (id, email) VALUES (1, 'a@example.com')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

Tip: Try changing table and column names to match your own database.

Date SQL

Example 76

Current date

Return today's date.

SELECT CURRENT_DATE;

Tip: Try changing table and column names to match your own database.

Example 77

Current timestamp

Return current date and time.

SELECT CURRENT_TIMESTAMP;

Tip: Try changing table and column names to match your own database.

Example 78

Add days to date

Add days to a date.

-- PostgreSQL
SELECT CURRENT_DATE + INTERVAL '7 days';

-- MySQL
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY);

Tip: Try changing table and column names to match your own database.

Example 79

Subtract days

Subtract days from a date.

-- PostgreSQL
SELECT CURRENT_DATE - INTERVAL '7 days';

-- MySQL
SELECT DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);

Tip: Try changing table and column names to match your own database.

Example 80

Date difference

Find days between dates.

-- PostgreSQL
SELECT DATE '2026-05-09' - DATE '2026-05-01' AS days;

-- MySQL
SELECT DATEDIFF('2026-05-09', '2026-05-01') AS days;

Tip: Try changing table and column names to match your own database.

Example 81

Extract year

Get year from a date.

SELECT EXTRACT(YEAR FROM created_at) AS year FROM orders;

Tip: Try changing table and column names to match your own database.

Example 82

Extract month

Get month from a date.

SELECT EXTRACT(MONTH FROM created_at) AS month FROM orders;

Tip: Try changing table and column names to match your own database.

Example 83

Format date

Format dates by dialect.

-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;

-- PostgreSQL
SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM orders;

Tip: Try changing table and column names to match your own database.

Example 84

Last 7 days

Find recent records.

SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

Tip: Try changing table and column names to match your own database.

Example 85

Group by month

Monthly totals.

SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) FROM orders GROUP BY month ORDER BY month;

Tip: Try changing table and column names to match your own database.

String SQL

Example 86

Concatenate strings

Join text values.

-- PostgreSQL/SQLite
SELECT first_name || ' ' || last_name AS full_name FROM users;

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

Tip: Try changing table and column names to match your own database.

Example 87

Uppercase

Convert text to uppercase.

SELECT UPPER(name) FROM users;

Tip: Try changing table and column names to match your own database.

Example 88

Lowercase

Convert text to lowercase.

SELECT LOWER(email) FROM users;

Tip: Try changing table and column names to match your own database.

Example 89

Trim spaces

Remove spaces from both ends.

SELECT TRIM(name) FROM users;

Tip: Try changing table and column names to match your own database.

Example 90

Substring

Get part of a string.

SELECT SUBSTRING(name FROM 1 FOR 3) FROM users;

Tip: Try changing table and column names to match your own database.

Example 91

Replace text

Replace part of a string.

SELECT REPLACE(phone, '-', '') FROM users;

Tip: Try changing table and column names to match your own database.

Example 92

String length

Count characters.

SELECT LENGTH(name) FROM users;

Tip: Try changing table and column names to match your own database.

Example 93

Split string

Split text by delimiter.

-- PostgreSQL
SELECT SPLIT_PART(email, '@', 2) AS domain FROM users;

Tip: Try changing table and column names to match your own database.

Example 94

Search inside string

Find text position.

-- PostgreSQL
SELECT POSITION('@' IN email) FROM users;

-- MySQL
SELECT LOCATE('@', email) FROM users;

Tip: Try changing table and column names to match your own database.

Example 95

Cast number to text

Convert number to string.

SELECT CAST(id AS VARCHAR) FROM users;

Tip: Try changing table and column names to match your own database.

Advanced SQL

Example 96

CTE

Use a named temporary result.

WITH active_users AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users;

Tip: Try changing table and column names to match your own database.

Example 97

Recursive CTE

Generate hierarchical or repeated results.

WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

Tip: Try changing table and column names to match your own database.

Example 98

ROW_NUMBER

Number rows in order.

SELECT name, ROW_NUMBER() OVER (ORDER BY created_at) AS row_num FROM users;

Tip: Try changing table and column names to match your own database.

Example 99

RANK

Rank rows with ties.

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank FROM students;

Tip: Try changing table and column names to match your own database.

Example 100

Running total

Calculate cumulative sum.

SELECT order_date, total,
SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

Tip: Try changing table and column names to match your own database.

Back to Top