Ever get stuck trying to pull data from your database? You're not alone. I remember my first month working with SQL - I spent hours writing queries that returned either nothing or way too much. Frustrating doesn't even cover it. That's why concrete SQL examples with queries are worth their weight in gold when you're learning.

Today we're diving into real-world SQL query examples covering everything from basic SELECT statements to complex joins. I'll share actual code I've used in production systems, mistakes I've made (so you don't have to), and solutions for common business scenarios. These aren't textbook examples - they're battle-tested queries that solve actual problems.

Basic SQL Query Patterns You'll Use Daily

Let's start simple. These foundational SQL examples with queries handle about 80% of what most people need. I still use variations of these daily.

Filtering Data Like a Pro

SELECT product_name, price, stock_quantity 
FROM products
WHERE price < 50 
AND stock_quantity > 0
AND category = 'Electronics'
ORDER BY price DESC;

Why this matters: That time I needed discounted electronics? Forgot the stock check. Sold 3 items we didn't have. Customer service wasn't happy. Now I always include inventory checks where relevant.

Watch for this: WHERE price = 19.99 might miss $19.9900001 due to float precision. Better to use WHERE ABS(price - 19.99) < 0.001 for monetary values.

Aggregating Data Without Headaches

Sales reports taught me this the hard way. Grouped revenue by month once but forgot to filter test accounts. CEO saw inflated numbers. Awkward.

SELECT 
  YEAR(order_date) AS sale_year,
  MONTH(order_date) AS sale_month,
  COUNT(order_id) AS total_orders,
  SUM(order_total) AS revenue,
  AVG(order_total) AS avg_order_value
FROM orders
WHERE order_date >= '2023-01-01'
AND status = 'completed'
GROUP BY YEAR(order_date), MONTH(order_date)
HAVING SUM(order_total) > 10000
ORDER BY sale_year DESC, sale_month DESC;

Common aggregation pitfalls:

• Mixing aggregated and non-aggregated columns in SELECT without grouping
• Using WHERE instead of HAVING for aggregate conditions
• Forgetting NULL values affect COUNT() differently than COUNT(column)

Intermediate SQL Query Examples

Now let's tackle some juicier SQL examples with queries that solve real business problems. These examples come from actual e-commerce and analytics projects.

Joining Tables Without Losing Your Mind

Customer analysis queries often require joining multiple tables. This example combines orders, customers and products:

SELECT 
  c.customer_id,
  CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
  COUNT(o.order_id) AS order_count,
  SUM(oi.quantity * p.price) AS total_spent,
  MAX(o.order_date) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE c.signup_date > '2022-01-01'
GROUP BY c.customer_id, customer_name
HAVING COUNT(o.order_id) > 1
ORDER BY total_spent DESC
LIMIT 50;

Personal screw-up: Once did 5 LEFT JOINs without indexes. Took 18 minutes to run. Added indexes and got it down to 8 seconds. Index strategically!

Subquery Solutions

Finding customers who haven't ordered recently is a classic business case. I prefer EXISTS over IN for performance:

SELECT customer_id, email
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 
  FROM orders o 
  WHERE o.customer_id = c.customer_id
  AND o.order_date > CURRENT_DATE - INTERVAL '6 months'
);

When to use which subquery approach:

• EXISTS/NOT EXISTS: Checking for presence/absence
• IN/NOT IN: Matching against static lists (small datasets)
• Correlated subqueries: When you need row-by-row comparison

Advanced SQL Query Examples

These SQL query examples get into window functions and CTEs - powerful tools that solve complex problems efficiently.

Window Functions for Ranking and Trends

Need to compare monthly sales with previous months? Window functions beat self-joins any day. Here's how I track monthly growth:

WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(order_total) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS absolute_growth,
  (revenue - LAG(revenue) OVER (ORDER BY month)) / 
  LAG(revenue) OVER (ORDER BY month) * 100 AS percent_growth
FROM monthly_sales
ORDER BY month DESC;

Recursive Queries for Hierarchical Data

Organization charts and category trees used to give me nightmares until I mastered recursive CTEs. Here's how to get reporting hierarchies:

WITH RECURSIVE org_chart AS (
  SELECT
    employee_id,
    name,
    title,
    manager_id,
    1 AS level
  FROM employees
  WHERE manager_id IS NULL -- CEO
  
  UNION ALL
  
  SELECT
    e.employee_id,
    e.name,
    e.title,
    e.manager_id,
    oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart
ORDER BY level, name;

Warning: Recursive queries can crash if you have circular references (an employee managing their manager). Always test with cycle detection.

SQL Query Optimization Essentials

Writing SQL that works is half the battle. Making it perform well is where the real magic happens. These lessons came from expensive mistakes.

Execution Plan Checklist

When queries slow down, I always check:

• Seq scans happening? → Add WHERE clause filters
• Nested loops killing performance? → Check join conditions
• Sort operations on disk? → Increase work_mem if appropriate
• Missing indexes? → Create targeted indexes

Here's a query I optimized from 14 seconds to 0.2 seconds:

-- Before
SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023;

-- After
SELECT * FROM orders 
WHERE order_date >= '2023-01-01' 
AND order_date < '2024-01-01';

Why it improved: The first version couldn't use the index on order_date due to the function. The second uses a simple range scan.

Indexing Strategies That Work

Personal rule: I never index without checking query patterns first. Once indexed every date column blindly. Wrote performance improved? Yes. Writes slowed to a crawl? Also yes.

SQL Query Examples FAQ

Here are answers to common questions about SQL examples with queries that come up in my workshops:

Database-Specific SQL Examples

Syntax varies across databases. Here's how key operations differ in SQL query examples:

Annoying difference: Date math. In PostgreSQL it's INTERVAL '7 days', MySQL uses DATE_ADD(date, INTERVAL 7 DAY), SQL Server has DATEADD(day, 7, date). Drives me nuts when switching between them.

Common SQL Query Patterns Cheat Sheet

Bookmark this reference of patterns I use constantly:

• Find duplicates: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1
• Gaps in time series: Use LEAD() over ordered dates
• Latest record per group: DISTINCT ON in PostgreSQL, ROW_NUMBER() elsewhere
• Remove duplicates: DELETE USING with a self-join
• Pivot data: FILTER clauses in PostgreSQL, CASE WHEN in others

Final thought: The best way to learn is to take these SQL examples with queries and modify them for your database. Break them on purpose. See what errors you get. Fix them. That struggle is where real understanding happens.