30 Real-World SQL Queries Examples for Practice

Introduction

If you've learned the basics of SQL — SELECT, WHERE, ORDER BY — you're already past the hardest part. But knowing syntax and actually being able to use SQL to solve real problems are two different things. The gap between them? Practice with real-world SQL queries examples.

This post gives you exactly that. You'll find 30 SQL query examples with explanation, all built around a single e-commerce dataset so the examples feel connected and make sense together. Each query covers a scenario you'd actually encounter on the job — filtering customer data, calculating revenue, joining tables, writing subqueries, and more.

Whether you're preparing for a data analyst role, working on a personal project, or just want to sharpen your skills, these SQL practice queries will help you think like someone who uses SQL daily — not just someone who knows the rules.

Quick Setup Note

You don't need to install anything to follow along. These queries work on MySQL, PostgreSQL, and SQL Server with minor syntax differences. If you want a zero-setup option, head to DB Fiddle (dbfiddle.uk) or SQLiteOnline — both are free, browser-based, and ready in seconds.

The examples use four tables throughout:

• customers — customer_id, first_name, last_name, email, city, country, created_at
• orders — order_id, customer_id, order_date, status, total_amount
• order_items — item_id, order_id, product_id, quantity, unit_price
• products — product_id, product_name, category, price, stock_quantity

Category 1: Basic Filtering and Sorting — SQL Queries Examples for Beginners

These are the foundation. If you're comfortable with SELECT and WHERE, these queries will stretch you just enough to build real confidence.

Query 1: Find All Customers from a Specific Country

Use case: A marketing team wants to run a region-specific campaign and needs a list of all customers based in India.

SELECT
    customer_id,
    first_name,
    last_name,
    email,
    city
FROM customers
WHERE country = 'India'
ORDER BY last_name ASC;

What it does: Filters the customers table to return only rows where country equals 'India', then sorts the results alphabetically by last name. The ORDER BY makes the output easier to scan in a real report.

Query 2: Find Orders Above a Certain Value

Use case: A sales analyst wants to identify high-value orders — anything above ₹5,000 — for a priority review.

SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM orders
WHERE total_amount > 5000
ORDER BY total_amount DESC;

What it does: The WHERE clause filters out low-value orders, and DESC sorting puts the biggest orders at the top — exactly how you'd want to present this to a manager.

Query 3: Find Products in a Category with Low Stock

Use case: The inventory team wants to flag products in the 'Electronics' category that are running low (fewer than 10 units left).

SELECT
    product_id,
    product_name,
    price,
    stock_quantity
FROM products
WHERE category = 'Electronics'
  AND stock_quantity < 10
ORDER BY stock_quantity ASC;

What it does: Combines two conditions with AND — category must match AND stock must be below 10. Sorting by stock_quantity ascending shows the most urgent items first.

Query 4: Search for Customers by Partial Email Domain

Use case: You want to find all customers who signed up with a Gmail address — useful for email deliverability analysis.

SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM customers
WHERE email LIKE '%@gmail.com'
ORDER BY first_name;

What it does: LIKE with the % wildcard matches any email ending in @gmail.com, regardless of what comes before it. This is one of the most common real-world SQL patterns for text-based filtering.

Query 5: Find Orders Placed Within a Date Range

Use case: A finance team needs all orders placed during Q1 (January to March) for a quarterly revenue report.

SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'
ORDER BY order_date ASC;

What it does: BETWEEN is inclusive on both ends, so this returns every order from January 1st through March 31st. It's cleaner and more readable than using >= and <=.

Category 2: Aggregation and Grouping — Real-World SQL Examples

This is where SQL starts to feel powerful. Aggregation turns raw rows into meaningful summaries.

Query 6: Count Total Customers by Country

Use case: A growth team wants to know how many customers they have in each country to prioritize expansion efforts.

SELECT
    country,
    COUNT(customer_id) AS total_customers
FROM customers
GROUP BY country
ORDER BY total_customers DESC;

What it does: GROUP BY collapses all rows with the same country into one, and COUNT() tallies how many customers are in each group. The alias AS total_customers makes the output column readable.

Query 7: Calculate Total Revenue by Month

Use case: Management wants a monthly revenue breakdown to spot seasonal trends.

SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,  -- Use TO_CHAR in PostgreSQL
    SUM(total_amount) AS monthly_revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month ASC;

What it does: DATE_FORMAT extracts the year and month, GROUP BY groups all orders in the same month, and SUM() adds up the revenue. The WHERE ensures only completed orders are counted.

Query 8: Find the Average Order Value by Customer

Use case: An analyst wants to understand spending behavior — which customers place big orders on average vs. small ones.

SELECT
    customer_id,
    COUNT(order_id) AS total_orders,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
GROUP BY customer_id
ORDER BY avg_order_value DESC;

What it does: AVG() calculates the mean order value per customer, ROUND() limits it to two decimal places, and COUNT() adds useful context about how many orders each customer has placed.

Query 9: Find Categories with More Than 5 Products

Use case: A product manager wants to see which categories are well-stocked enough to feature in a promotional campaign.

SELECT
    category,
    COUNT(product_id) AS product_count
FROM products
GROUP BY category
HAVING COUNT(product_id) > 5
ORDER BY product_count DESC;

What it does: HAVING filters after grouping, while WHERE filters before. You can't use WHERE to filter on aggregated values like COUNT() — that's exactly why HAVING exists.

Query 10: Find the Top 5 Best-Selling Products by Quantity

Use case: The sales team wants to know which products move the most units so they can prioritize restocking.

SELECT
    p.product_name,
    SUM(oi.quantity) AS total_units_sold
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_units_sold DESC
LIMIT 5;

What it does: Joins order_items with products to get product names, sums total units sold across all orders, and uses LIMIT 5 to return only the top five. This is the kind of query you'd run every week in a real business.

Category 3: Joins — Combining Tables in SQL Practice Queries

Joins are where a lot of beginners get stuck. These five examples cover the most common scenarios clearly.

Query 11: Get Customer Details for Every Order (INNER JOIN)

Use case: A customer service rep needs a combined view of orders with customer names and emails attached.

SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    c.first_name,
    c.last_name,
    c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;

What it does: INNER JOIN returns only rows where there's a match in both tables. If an order has no matching customer, it won't appear in the results — which is the safe default for reporting.

Query 12: Find Customers Who Have Never Placed an Order (LEFT JOIN)

Use case: A re-engagement campaign needs a list of registered customers who haven't bought anything yet.

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

What it does: LEFT JOIN keeps all customers even with no matching order. The WHERE o.order_id IS NULL then filters to only those unmatched rows — customers with no orders at all. This is a classic pattern worth memorizing.

Query 13: Get Full Order Details with Product Names

Use case: A fulfillment team needs a complete view of what was ordered — order ID, product name, quantity, and unit price — for packing slips.

SELECT
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_id, p.product_name;

What it does: Joins three tables in sequence. Each JOIN adds a layer of information — order_items links orders to products, and products provides the name. The calculated column line_total shows the value of each line item.

Query 14: Find Products That Have Never Been Ordered

Use case: An inventory audit needs to identify dead stock — products sitting in the warehouse that no customer has ever ordered.

SELECT
    p.product_id,
    p.product_name,
    p.category,
    p.stock_quantity
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;

What it does: Same LEFT JOIN + IS NULL pattern as Query 12, applied to products. Any product that never appears in order_items will show up here with NULL on the right side of the join.

Query 15: Count Orders per Customer Including Customers with Zero Orders

Use case: A loyalty program analysis needs total order counts for all customers, including those who registered but never bought.

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_orders DESC;

What it does: LEFT JOIN ensures all customers appear even with no orders. COUNT(o.order_id) returns 0 for those customers rather than excluding them — because COUNT on a NULL column returns 0, not an error.

Category 4: Subqueries — SQL Query Examples with Explanation

Subqueries let you use the result of one query inside another. They feel advanced but the logic is straightforward once you see it in action.

Query 16: Find Customers Who Spent Above the Average Order Value

Use case: A VIP program wants to identify customers whose typical order value is above the platform average.

SELECT
    customer_id,
    ROUND(AVG(total_amount), 2) AS avg_spend
FROM orders
GROUP BY customer_id
HAVING AVG(total_amount) > (
    SELECT AVG(total_amount) FROM orders
)
ORDER BY avg_spend DESC;

What it does: The subquery in parentheses calculates the overall average order value. The outer query finds customers whose personal average beats that number. The inner query runs once and its result is used as a filter value.

Query 17: Find the Most Expensive Product in Each Category

Use case: A pricing analyst wants to see the top-priced item in each product category for a competitor comparison.

SELECT
    product_id,
    product_name,
    category,
    price
FROM products
WHERE price = (
    SELECT MAX(price)
    FROM products p2
    WHERE p2.category = products.category
);

What it does: This is a correlated subquery — the inner query references the outer query's current row. For each product, it checks whether its price matches the max price in its own category.

Query 18: Find Orders That Contain a Specific Product

Use case: After a product recall, the operations team needs to identify every order that included the affected item.

SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM orders
WHERE order_id IN (
    SELECT order_id
    FROM order_items
    WHERE product_id = 101  -- Replace with the recalled product's ID
);

What it does: The subquery returns a list of order_ids that contain product 101. The outer query retrieves full order details for those IDs. IN with a subquery is one of the most readable ways to filter by a dynamic list.

Query 19: Find Customers Who Have Placed More Than 3 Orders

Use case: A retention analyst wants to identify loyal, repeat customers for a rewards program.

SELECT
    customer_id,
    first_name,
    last_name,
    email
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(order_id) > 3
);

What it does: The subquery groups orders by customer and filters to those with more than 3. The outer query then pulls the full customer details for those IDs.

Query 20: Find Products Priced Above the Category Average

Use case: A merchandising team wants to flag premium products within each category — items priced above their category's typical price.

SELECT
    product_name,
    category,
    price
FROM products p1
WHERE price > (
    SELECT AVG(price)
    FROM products p2
    WHERE p2.category = p1.category
)
ORDER BY category, price DESC;

What it does: Another correlated subquery. For each product, the inner query calculates the average price for that product's category. If the product's price beats that average, it's included.

Category 5: String and Date Functions — Real-World SQL Examples

These functions come up constantly in real work — formatting names, parsing dates, cleaning messy data.

Query 21: Combine First and Last Name into a Full Name

Use case: A report needs customer names displayed as a single "Full Name" field rather than two separate columns.

SELECT
    customer_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    email
FROM customers
ORDER BY last_name;

What it does: CONCAT() joins strings together. The space between names is added as a string literal. In PostgreSQL you can also use: first_name || ' ' || last_name.

Query 22: Extract Year and Month from Order Dates

Use case: A dashboard needs order data broken out by year and month as separate columns for pivot table analysis.

SELECT
    order_id,
    order_date,
    YEAR(order_date) AS order_year,
    MONTH(order_date) AS order_month,
    total_amount
FROM orders
ORDER BY order_date DESC;

What it does: YEAR() and MONTH() extract those parts from a date column. In PostgreSQL, use EXTRACT(YEAR FROM order_date) instead.

Query 23: Find Customers Who Registered More Than 1 Year Ago

Use case: A re-engagement campaign targets customers who joined over a year ago but may have gone quiet.

SELECT
    customer_id,
    first_name,
    last_name,
    email,
    created_at
FROM customers
WHERE created_at < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
ORDER BY created_at ASC;

What it does: CURDATE() returns today's date, and DATE_SUB() subtracts one year from it. The WHERE filters to customers who registered before that cutoff. In PostgreSQL: CURRENT_DATE - INTERVAL '1 year'.

Query 24: Convert Email Addresses to Lowercase

Use case: Before exporting a customer list, a data team needs to normalize all email addresses to lowercase to avoid duplicates.

SELECT
    customer_id,
    first_name,
    LOWER(email) AS normalized_email
FROM customers;

What it does: LOWER() converts the entire string to lowercase. A simple but important data cleaning step — 'User@Gmail.com' and 'user@gmail.com' are the same person, and normalizing prevents them from being counted twice.

Query 25: Find Orders Placed in the Last 30 Days

Use case: A weekly operations report needs to show only recent activity — orders from the past 30 days.

SELECT
    order_id,
    customer_id,
    order_date,
    total_amount,
    status
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY order_date DESC;

What it does: DATE_SUB with INTERVAL 30 DAY gives a rolling 30-day window from today — more useful than hardcoding dates because it stays current every time the query runs.

Category 6: Real-World Business Scenarios — SQL for Beginners Going Further

These are the queries that tie everything together. Each one solves a problem you'd actually encounter in a data analyst or business analyst role.

Query 26: Calculate Total Revenue per Customer (Customer Lifetime Value)

Use case: A finance team wants to rank customers by total spend to identify the highest-value accounts.

SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS full_name,
    COUNT(o.order_id) AS total_orders,
    ROUND(SUM(o.total_amount), 2) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY lifetime_value DESC;

What it does: Joins customers and orders, groups by customer, and calculates both order count and total spend. LEFT JOIN ensures customers with zero orders still appear with 0 values — important for a complete picture.

Query 27: Find the Best-Selling Category by Revenue

Use case: A business review meeting needs to know which product category generates the most revenue.

SELECT
    p.category,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'completed'
GROUP BY p.category
ORDER BY total_revenue DESC;

What it does: A three-table join that pulls together categories, quantities sold, and prices to calculate revenue by category. Only completed orders are included — the right approach for any financial analysis.

Query 28: Find Repeat Customers (Ordered More Than Once)

Use case: A CRM team wants to segment one-time buyers from repeat customers to tailor their messaging.

SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS full_name,
    c.email,
    COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name, c.email
HAVING COUNT(o.order_id) > 1
ORDER BY order_count DESC;

What it does: HAVING filters to customers with more than one order after grouping. This is fundamentally different from WHERE — by the time HAVING runs, the grouping has already happened.

Query 29: Month-over-Month Revenue Growth

Use case: An analyst wants to track whether revenue is growing or shrinking each month compared to the previous one.

SELECT
    month,
    monthly_revenue,
    LAG(monthly_revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY month))
        / LAG(monthly_revenue) OVER (ORDER BY month) * 100, 2
    ) AS growth_pct
FROM (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        ROUND(SUM(total_amount), 2) AS monthly_revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
) monthly_data
ORDER BY month;

What it does: Uses a subquery to build monthly totals first, then applies the LAG() window function to look at the previous month's revenue. LAG() is available in MySQL 8+, PostgreSQL, and SQL Server.

Query 30: Full Customer Order Summary Report

Use case: A management dashboard needs a single clean view of every customer — when they joined, how many orders they've placed, and their total spend.

SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS full_name,
    c.email,
    c.city,
    c.country,
    DATE_FORMAT(c.created_at, '%d %b %Y') AS member_since,
    COUNT(o.order_id) AS total_orders,
    COALESCE(ROUND(SUM(o.total_amount), 2), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id, c.first_name, c.last_name,
    c.email, c.city, c.country, c.created_at
ORDER BY total_spent DESC;

What it does: COALESCE handles customers with no orders by replacing NULL with 0 — so the report shows 0.00 instead of blank for non-buyers. DATE_FORMAT makes the join date human-readable. This is the kind of query you'd hand directly to a stakeholder.

Practice Tips: How to Get the Most Out of These SQL Practice Queries

Working through 30 queries is a solid start. Here's how to make sure it actually sticks:

1. Don't just run them — break them. After each query works, try removing a clause, changing an operator, or adding a condition. See what breaks and why. Error messages are some of the best teachers in SQL.
2. Modify the filters. Every query uses specific values — a country, a date range, a price threshold. Change those values and re-run. This builds the intuition for how filters interact with your data.
3. Build your own dataset. Create a small table in DB Fiddle — even just 10–15 rows — and write queries against data you invented. When you understand the data deeply, it's easier to spot when a query is returning something wrong.
4. Rewrite joins as subqueries and vice versa. Queries 12 and 19 can both be written multiple ways. Practice writing the same result two different ways — it forces you to understand the underlying logic rather than just pattern-matching syntax.
5. Use free public datasets. Once you're comfortable with these examples, move to real data. Kaggle has hundreds of free CSV datasets. Mode Analytics also has a public SQL editor with real data built in — no setup needed.

Conclusion

You've just worked through 30 real-world SQL queries examples covering everything from basic filtering to window functions. Each category in this post — filtering, aggregation, joins, subqueries, functions, and business scenarios — represents a skill layer that practicing data professionals use daily.

The goal was never just to show you syntax. It was to show you how SQL thinking works: how you move from a business question to a query, and why certain techniques exist in the first place.

Keep these SQL practice queries bookmarked. Come back to them when you're stuck on a real problem — chances are one of these patterns is close to what you need. And if you want more SQL for beginners content like this, follow along for more tutorials covering advanced joins, CTEs, stored procedures, and interview prep.

The best thing you can do now is open a SQL editor and start writing.