30 SQL Query Examples for Beginners to Advanced (Copy & Paste)
Practical SQL examples you can copy, paste, and run immediately. Each query includes the problem it solves, the SQL code, and the expected output. Uses a consistent sample database so you can follow along from start to finish.
Getting Started with SQL Queries
Every example in this guide uses three related tables: employees, departments, and orders. Here is the schema so you can create them yourself or follow along mentally.
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
salary DECIMAL(10,2),
hire_date DATE,
department_id INT REFERENCES departments(id)
);
CREATE TABLE orders (
id INT PRIMARY KEY,
employee_id INT REFERENCES employees(id),
customer_name VARCHAR(100),
amount DECIMAL(10,2),
order_date DATE,
status VARCHAR(20)
);Sample data used throughout:
| departments | id | name |
|---|---|---|
| 1 | Engineering | |
| 2 | Sales | |
| 3 | Marketing | |
| 4 | HR |
The employees table has 12 rows across these four departments, with salaries ranging from $45,000 to $125,000. The orders table has 20 rows with various statuses and amounts. Let's start querying.
Basic SELECT Queries
Example 1: Select All Columns
Problem: Retrieve every column and every row from the employees table.
SELECT *
FROM employees;| id | first_name | last_name | salary | department_id |
|---|---|---|---|---|
| 1 | Alice | Johnson | 95000 | 1 |
| 2 | Bob | Smith | 72000 | 2 |
| 3 | Carol | Williams | 125000 | 1 |
| ... (12 rows total) | ||||
Example 2: WHERE - Filter Rows
Problem: Find all employees in the Engineering department (department_id = 1).
SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 1;| first_name | last_name | salary |
|---|---|---|
| Alice | Johnson | 95000 |
| Carol | Williams | 125000 |
| Frank | Davis | 88000 |
Example 3: ORDER BY - Sort Results
Problem: List all employees sorted by salary from highest to lowest.
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;| first_name | last_name | salary |
|---|---|---|
| Carol | Williams | 125000 |
| Alice | Johnson | 95000 |
| Frank | Davis | 88000 |
| ... (12 rows) | ||
Example 4: LIMIT - Restrict Results
Problem: Get the top 3 highest-paid employees.
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;| first_name | last_name | salary |
|---|---|---|
| Carol | Williams | 125000 |
| Alice | Johnson | 95000 |
| Frank | Davis | 88000 |
Example 5: DISTINCT - Remove Duplicates
Problem: List all unique department IDs that have employees.
SELECT DISTINCT department_id
FROM employees
ORDER BY department_id;| department_id |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
Example 6: Column Aliases
Problem: Display employee full names and their annual salary with readable column headers.
SELECT
first_name || ' ' || last_name AS full_name,
salary AS annual_salary,
salary / 12 AS monthly_salary
FROM employees
ORDER BY annual_salary DESC
LIMIT 5;| full_name | annual_salary | monthly_salary |
|---|---|---|
| Carol Williams | 125000 | 10416.67 |
| Alice Johnson | 95000 | 7916.67 |
| Frank Davis | 88000 | 7333.33 |
Example 7: BETWEEN - Range Filtering
Problem: Find employees with salaries between $60,000 and $90,000.
SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 60000 AND 90000
ORDER BY salary;| first_name | last_name | salary |
|---|---|---|
| Diana | Lee | 62000 |
| Bob | Smith | 72000 |
| Frank | Davis | 88000 |
Example 8: LIKE - Pattern Matching
Problem: Find all employees whose last name starts with "S" or whose email contains "gmail".
SELECT first_name, last_name, email
FROM employees
WHERE last_name LIKE 'S%'
OR email LIKE '%gmail%';| first_name | last_name | |
|---|---|---|
| Bob | Smith | bob.smith@company.com |
| Grace | Stevens | grace@gmail.com |
Aggregate & GROUP BY Queries
Example 9: COUNT - Count Rows
Problem: How many employees are in each department?
SELECT
department_id,
COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
ORDER BY employee_count DESC;| department_id | employee_count |
|---|---|
| 1 | 4 |
| 2 | 3 |
| 3 | 3 |
| 4 | 2 |
Example 10: SUM - Total Values
Problem: What is the total order amount per status?
SELECT
status,
SUM(amount) AS total_amount,
COUNT(*) AS order_count
FROM orders
GROUP BY status;| status | total_amount | order_count |
|---|---|---|
| completed | 24500.00 | 10 |
| pending | 8300.00 | 5 |
| shipped | 12750.00 | 5 |
Example 11: AVG - Average Value
Problem: What is the average salary by department?
SELECT
d.name AS department,
ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
ORDER BY avg_salary DESC;| department | avg_salary |
|---|---|
| Engineering | 96750.00 |
| Sales | 71333.33 |
| Marketing | 65666.67 |
| HR | 55000.00 |
Example 12: MIN and MAX
Problem: Find the lowest and highest salary in each department.
SELECT
d.name AS department,
MIN(e.salary) AS lowest_salary,
MAX(e.salary) AS highest_salary,
MAX(e.salary) - MIN(e.salary) AS salary_range
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name;| department | lowest_salary | highest_salary | salary_range |
|---|---|---|---|
| Engineering | 78000 | 125000 | 47000 |
| Sales | 55000 | 85000 | 30000 |
| Marketing | 52000 | 78000 | 26000 |
| HR | 48000 | 62000 | 14000 |
Example 13: GROUP BY Multiple Columns
Problem: Count orders by employee and status.
SELECT
e.first_name,
o.status,
COUNT(*) AS num_orders,
SUM(o.amount) AS total_amount
FROM orders o
JOIN employees e ON o.employee_id = e.id
GROUP BY e.first_name, o.status
ORDER BY e.first_name, o.status;| first_name | status | num_orders | total_amount |
|---|---|---|---|
| Alice | completed | 3 | 7200.00 |
| Alice | pending | 1 | 1500.00 |
| Bob | shipped | 2 | 4800.00 |
Example 14: HAVING - Filter Groups
Problem: Find departments where the average salary exceeds $65,000.
SELECT
d.name AS department,
COUNT(*) AS employee_count,
ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
HAVING AVG(e.salary) > 65000
ORDER BY avg_salary DESC;| department | employee_count | avg_salary |
|---|---|---|
| Engineering | 4 | 96750.00 |
| Sales | 3 | 71333.33 |
| Marketing | 3 | 65666.67 |
Remember: WHERE filters rows before grouping. HAVING filters groups after aggregation. You cannot use HAVING without GROUP BY.
JOIN Queries
Example 15: INNER JOIN - Matching Rows Only
Problem: List every employee alongside their department name.
SELECT
e.first_name,
e.last_name,
d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
ORDER BY d.name, e.last_name;| first_name | last_name | department |
|---|---|---|
| Frank | Davis | Engineering |
| Alice | Johnson | Engineering |
| Carol | Williams | Engineering |
| ... (12 rows) | ||
Example 16: LEFT JOIN - Include Unmatched Left Rows
Problem: List all employees and their orders, including employees who have no orders.
SELECT
e.first_name,
e.last_name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.amount), 0) AS total_sales
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id
GROUP BY e.first_name, e.last_name
ORDER BY total_sales DESC;| first_name | last_name | order_count | total_sales |
|---|---|---|---|
| Alice | Johnson | 5 | 12300.00 |
| Bob | Smith | 4 | 9800.00 |
| Henry | Park | 0 | 0.00 |
Notice Henry Park appears with 0 orders. A LEFT JOIN keeps every row from the left table even when there is no match on the right.
Example 17: Multi-Table JOIN
Problem: Show each order with the employee name and department name.
SELECT
o.id AS order_id,
o.customer_name,
o.amount,
e.first_name || ' ' || e.last_name AS salesperson,
d.name AS department
FROM orders o
JOIN employees e ON o.employee_id = e.id
JOIN departments d ON e.department_id = d.id
ORDER BY o.order_date DESC
LIMIT 5;| order_id | customer_name | amount | salesperson | department |
|---|---|---|---|---|
| 20 | Acme Corp | 3200.00 | Alice Johnson | Engineering |
| 19 | TechStart | 1850.00 | Bob Smith | Sales |
| 18 | DataFlow | 4100.00 | Carol Williams | Engineering |
Example 18: Self JOIN
Problem: Find pairs of employees in the same department who earn different salaries.
SELECT
e1.first_name AS employee_1,
e2.first_name AS employee_2,
e1.salary AS salary_1,
e2.salary AS salary_2,
e1.department_id
FROM employees e1
JOIN employees e2
ON e1.department_id = e2.department_id
AND e1.id < e2.id
WHERE e1.salary != e2.salary
ORDER BY e1.department_id;| employee_1 | employee_2 | salary_1 | salary_2 | department_id |
|---|---|---|---|---|
| Alice | Carol | 95000 | 125000 | 1 |
| Alice | Frank | 95000 | 88000 | 1 |
| Bob | Eve | 72000 | 85000 | 2 |
Example 19: JOIN with Aggregate - Department Revenue
Problem: Calculate total order revenue per department.
SELECT
d.name AS department,
COUNT(DISTINCT e.id) AS num_salespeople,
COUNT(o.id) AS total_orders,
SUM(o.amount) AS total_revenue,
ROUND(SUM(o.amount) / COUNT(DISTINCT e.id), 2) AS revenue_per_person
FROM departments d
JOIN employees e ON d.id = e.department_id
JOIN orders o ON e.id = o.employee_id
GROUP BY d.name
ORDER BY total_revenue DESC;| department | num_salespeople | total_orders | total_revenue | revenue_per_person |
|---|---|---|---|---|
| Engineering | 3 | 9 | 22500.00 | 7500.00 |
| Sales | 3 | 8 | 18200.00 | 6066.67 |
| Marketing | 2 | 3 | 4850.00 | 2425.00 |
Example 20: CROSS JOIN - All Combinations
Problem: Generate every possible employee-department assignment.
SELECT
e.first_name,
d.name AS possible_department
FROM employees e
CROSS JOIN departments d
ORDER BY e.first_name, d.name
LIMIT 8;| first_name | possible_department |
|---|---|
| Alice | Engineering |
| Alice | HR |
| Alice | Marketing |
| Alice | Sales |
| Bob | Engineering |
| ... (48 rows: 12 employees x 4 departments) | |
Skip the typing - describe any JOIN in plain English and AI2SQL generates the SQL for you.
Subquery Examples
Example 21: Subquery in WHERE
Problem: Find employees who earn more than the company average salary.
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
)
ORDER BY salary DESC;| first_name | last_name | salary |
|---|---|---|
| Carol | Williams | 125000 |
| Alice | Johnson | 95000 |
| Frank | Davis | 88000 |
| Eve | Martinez | 85000 |
The inner query calculates the average once. The outer query uses that value to filter rows. The average salary here is approximately $74,500.
Example 22: Subquery in FROM (Derived Table)
Problem: Find departments where total order revenue exceeds $10,000.
SELECT
dept_revenue.department,
dept_revenue.total_revenue
FROM (
SELECT
d.name AS department,
SUM(o.amount) AS total_revenue
FROM orders o
JOIN employees e ON o.employee_id = e.id
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
) AS dept_revenue
WHERE dept_revenue.total_revenue > 10000
ORDER BY dept_revenue.total_revenue DESC;| department | total_revenue |
|---|---|
| Engineering | 22500.00 |
| Sales | 18200.00 |
Example 23: Correlated Subquery
Problem: Find each employee's salary and how it compares to their department's average.
SELECT
e.first_name,
e.salary,
d.name AS department,
(SELECT ROUND(AVG(e2.salary), 2)
FROM employees e2
WHERE e2.department_id = e.department_id
) AS dept_avg_salary,
e.salary - (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
) AS diff_from_avg
FROM employees e
JOIN departments d ON e.department_id = d.id
ORDER BY diff_from_avg DESC;| first_name | salary | department | dept_avg_salary | diff_from_avg |
|---|---|---|---|---|
| Carol | 125000 | Engineering | 96750.00 | 28250.00 |
| Eve | 85000 | Sales | 71333.33 | 13666.67 |
| Diana | 48000 | HR | 55000.00 | -7000.00 |
A correlated subquery references the outer query (e.department_id), so it runs once per row. This is slower than a JOIN for large tables but sometimes clearer to read.
Example 24: EXISTS - Check for Related Rows
Problem: Find employees who have at least one completed order.
SELECT e.first_name, e.last_name
FROM employees e
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.employee_id = e.id
AND o.status = 'completed'
);| first_name | last_name |
|---|---|
| Alice | Johnson |
| Bob | Smith |
| Carol | Williams |
| Eve | Martinez |
EXISTS stops scanning as soon as it finds one matching row, making it more efficient than IN for large subqueries.
Window Function Examples
Example 25: ROW_NUMBER - Sequential Ranking
Problem: Rank employees within each department by salary.
SELECT
first_name,
last_name,
d.name AS department,
salary,
ROW_NUMBER() OVER (
PARTITION BY e.department_id
ORDER BY salary DESC
) AS dept_rank
FROM employees e
JOIN departments d ON e.department_id = d.id;| first_name | department | salary | dept_rank |
|---|---|---|---|
| Carol | Engineering | 125000 | 1 |
| Alice | Engineering | 95000 | 2 |
| Frank | Engineering | 88000 | 3 |
| Eve | Sales | 85000 | 1 |
| Bob | Sales | 72000 | 2 |
PARTITION BY resets the numbering for each department. Without it, the rank would be global across the entire result set.
Example 26: RANK and DENSE_RANK
Problem: Rank all employees by salary company-wide, showing the difference between RANK and DENSE_RANK when there are ties.
SELECT
first_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees
ORDER BY salary DESC;| first_name | salary | rank | dense_rank |
|---|---|---|---|
| Carol | 125000 | 1 | 1 |
| Alice | 95000 | 2 | 2 |
| Frank | 88000 | 3 | 3 |
| Eve | 85000 | 4 | 4 |
RANK skips numbers after ties (1, 2, 2, 4). DENSE_RANK does not (1, 2, 2, 3). Use ROW_NUMBER when you need unique sequential values regardless of ties.
Example 27: Running Total with SUM OVER
Problem: Calculate a running total of order amounts sorted by date.
SELECT
order_date,
customer_name,
amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
WHERE status = 'completed'
ORDER BY order_date;| order_date | customer_name | amount | running_total |
|---|---|---|---|
| 2026-01-05 | Acme Corp | 2500.00 | 2500.00 |
| 2026-01-12 | TechStart | 1800.00 | 4300.00 |
| 2026-01-20 | DataFlow | 3400.00 | 7700.00 |
| 2026-02-03 | CloudNine | 4200.00 | 11900.00 |
Example 28: LAG and LEAD - Compare Adjacent Rows
Problem: For each completed order, show the previous order's amount and the difference.
SELECT
order_date,
customer_name,
amount,
LAG(amount, 1) OVER (ORDER BY order_date) AS prev_amount,
amount - LAG(amount, 1) OVER (ORDER BY order_date) AS change,
LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount
FROM orders
WHERE status = 'completed'
ORDER BY order_date;| order_date | amount | prev_amount | change | next_amount |
|---|---|---|---|---|
| 2026-01-05 | 2500.00 | NULL | NULL | 1800.00 |
| 2026-01-12 | 1800.00 | 2500.00 | -700.00 | 3400.00 |
| 2026-01-20 | 3400.00 | 1800.00 | 1600.00 | 4200.00 |
LAG looks backward; LEAD looks forward. The first row has NULL for prev_amount because there is no preceding row. You can provide a default value: LAG(amount, 1, 0).
Advanced Patterns
Example 29: Recursive CTE - Organizational Hierarchy
Problem: Build an employee hierarchy starting from the CEO and traversing down through managers.
-- Assumes employees table has a manager_id column
WITH RECURSIVE org_chart AS (
-- Base case: CEO (no manager)
SELECT
id,
first_name,
last_name,
manager_id,
1 AS level,
first_name || ' ' || last_name AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case: employees who report to someone
SELECT
e.id,
e.first_name,
e.last_name,
e.manager_id,
oc.level + 1,
oc.path || ' > ' || e.first_name || ' ' || e.last_name
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT level, first_name, last_name, path
FROM org_chart
ORDER BY path;| level | first_name | last_name | path |
|---|---|---|---|
| 1 | Carol | Williams | Carol Williams |
| 2 | Alice | Johnson | Carol Williams > Alice Johnson |
| 3 | Frank | Davis | Carol Williams > Alice Johnson > Frank Davis |
| 2 | Bob | Smith | Carol Williams > Bob Smith |
Recursive CTEs are the standard way to query tree-structured data (org charts, category trees, file systems). Always include a termination condition to avoid infinite loops.
Example 30: Pivot - Rows to Columns
Problem: Show each department's order count broken out by status as columns instead of rows.
SELECT
d.name AS department,
COUNT(CASE WHEN o.status = 'completed' THEN 1 END) AS completed,
COUNT(CASE WHEN o.status = 'pending' THEN 1 END) AS pending,
COUNT(CASE WHEN o.status = 'shipped' THEN 1 END) AS shipped,
COUNT(o.id) AS total
FROM departments d
JOIN employees e ON d.id = e.department_id
LEFT JOIN orders o ON e.id = o.employee_id
GROUP BY d.name
ORDER BY total DESC;| department | completed | pending | shipped | total |
|---|---|---|---|---|
| Engineering | 4 | 2 | 3 | 9 |
| Sales | 4 | 2 | 2 | 8 |
| Marketing | 2 | 1 | 0 | 3 |
| HR | 0 | 0 | 0 | 0 |
The CASE WHEN inside COUNT pattern works in every SQL database. SQL Server also has a native PIVOT operator, but the CASE approach is portable.
Next Steps: From Examples to Mastery
You now have 30 SQL query patterns that cover the vast majority of real-world database work. Here is how to turn these examples into actual skill:
- Run every example. Reading SQL is not the same as writing it. Set up a local database or use AI2SQL's built-in demo database to execute each query and see the results yourself.
- Modify the queries. Change the WHERE conditions, add extra columns, swap the JOIN type. Break things on purpose so you understand the error messages.
- Combine patterns. Real queries often use JOINs, aggregation, and window functions together. Try writing a query that joins three tables, groups by department, and ranks results with ROW_NUMBER.
- Use AI as a learning partner. Describe what you need in plain English with AI2SQL, then compare the generated SQL to what you would have written. This is the fastest feedback loop for improving your SQL.
- Bookmark this page. Come back whenever you need a quick reference for a specific pattern.
Try AI2SQL free - generate any query from plain English, run it against the demo database, and learn SQL faster than memorizing syntax alone.
Frequently Asked Questions
What are the most common SQL query examples for beginners?
The most common SQL queries for beginners are SELECT * FROM table (retrieve all data), SELECT with WHERE for filtering, ORDER BY for sorting, LIMIT to restrict results, and GROUP BY with COUNT or SUM for basic aggregations. These five patterns cover the majority of beginner-level database tasks.
How do I practice SQL queries with real examples?
The fastest way to practice is with a tool like AI2SQL that includes a built-in demo database. You can paste any example query and run it instantly. Alternatively, install PostgreSQL or MySQL locally, create sample tables (employees, departments, orders), insert test data, and work through examples one by one.
What is the difference between WHERE and HAVING in SQL?
WHERE filters individual rows before any grouping happens. HAVING filters groups after GROUP BY and aggregation. For example, WHERE salary > 50000 removes rows before grouping, while HAVING COUNT(*) > 5 removes groups that have fewer than 5 rows after the data has been grouped.
When should I use a subquery vs a JOIN in SQL?
Use a JOIN when you need columns from multiple tables in your result set. Use a subquery when you need to filter based on a condition from another table but don't need that table's columns in the output. In most databases, JOINs perform better than subqueries for large datasets, but correlated subqueries are useful for row-by-row comparisons.
Can AI write SQL queries for me instead of memorizing examples?
Yes. AI-powered tools like AI2SQL let you describe what you need in plain English and generate the correct SQL automatically. This is especially helpful for complex queries with multiple JOINs, window functions, or nested subqueries. Understanding SQL examples still helps you verify and fine-tune the generated queries.