Beginner FriendlyExpert TipsReal Examples
Master SQL Functions
SQL functions are powerful tools that allow you to manipulate and analyze data efficiently. From basic aggregate functions like COUNT and SUM to advanced window functions, mastering these concepts is essential for any data professional.
Whether you're a beginner looking to understand the basics or an experienced developer seeking to optimize complex queries, our tutorials provide practical examples and real-world scenarios to enhance your SQL skills.
Featured Articles
How to use JOIN effectively
Master the art of combining data from multiple tables with different JOIN types.
Learn INNER, LEFT, RIGHT, and FULL OUTER JOINs with practical examples.
GROUP BY vs PARTITION BY
Understand the key differences between these two powerful SQL clauses.
When to use GROUP BY for aggregation and PARTITION BY for window functions.
Advanced Window Functions
Dive deep into ROW_NUMBER, RANK, DENSE_RANK, and analytical functions.
Solve complex analytical problems with window functions and CTEs.
SQL Examples Library
1. Basic SELECT with WHERE
Filter data with conditions
SELECT first_name, last_name, email
FROM customers
WHERE country = 'USA'
AND age >= 25
ORDER BY last_name;2. INNER JOIN with Multiple Tables
Combine data from related tables
SELECT c.customer_name, o.order_date, p.product_name, oi.quantity
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01';3. GROUP BY with Aggregations
Summarize data by groups
SELECT
department,
COUNT(*) as employee_count,
AVG(salary) as avg_salary,
MAX(salary) as max_salary,
MIN(salary) as min_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;4. Window Functions - ROW_NUMBER
Rank rows within partitions
SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) as salary_rank
FROM employees
WHERE salary > 50000;5. LEFT JOIN with NULL Handling
Include all records from left table
SELECT
c.customer_name,
c.email,
COALESCE(o.order_count, 0) as total_orders,
COALESCE(o.total_spent, 0) as total_spent
FROM customers c
LEFT JOIN (
SELECT
customer_id,
COUNT(*) as order_count,
SUM(total_amount) as total_spent
FROM orders
GROUP BY customer_id
) o ON c.customer_id = o.customer_id;6. Subquery in WHERE Clause
Filter using results from another query
SELECT product_name, price, category
FROM products
WHERE price > (
SELECT AVG(price)
FROM products
WHERE category = 'Electronics'
)
AND category = 'Electronics'
ORDER BY price DESC;7. CASE WHEN Statement
Conditional logic in SQL
SELECT
employee_name,
salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 70000 THEN 'Mid-Level'
WHEN salary >= 40000 THEN 'Junior'
ELSE 'Entry-Level'
END as experience_level,
CASE
WHEN salary >= 80000 THEN salary * 0.15
ELSE salary * 0.10
END as bonus
FROM employees;8. Common Table Expression (CTE)
Temporary named result set
WITH monthly_sales AS (
SELECT
EXTRACT(YEAR FROM order_date) as year,
EXTRACT(MONTH FROM order_date) as month,
SUM(total_amount) as monthly_total
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
)
SELECT
year,
month,
monthly_total,
LAG(monthly_total) OVER (ORDER BY year, month) as prev_month,
monthly_total - LAG(monthly_total) OVER (ORDER BY year, month) as growth
FROM monthly_sales
ORDER BY year, month;9. String Functions
Manipulate text data
SELECT
customer_id,
UPPER(first_name) as first_name_upper,
LOWER(last_name) as last_name_lower,
CONCAT(first_name, ' ', last_name) as full_name,
LENGTH(email) as email_length,
SUBSTRING(email, 1, POSITION('@' IN email) - 1) as username,
TRIM(phone_number) as clean_phone
FROM customers
WHERE email LIKE '%@gmail.com';10. Date Functions
Work with dates and times
SELECT
order_id,
order_date,
EXTRACT(YEAR FROM order_date) as order_year,
EXTRACT(MONTH FROM order_date) as order_month,
EXTRACT(DOW FROM order_date) as day_of_week,
DATE_TRUNC('month', order_date) as month_start,
order_date + INTERVAL '30 days' as due_date,
AGE(CURRENT_DATE, order_date) as order_age
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 year';11. UNION and UNION ALL
Combine results from multiple queries
SELECT 'Customer' as type, first_name, last_name, email
FROM customers
WHERE country = 'USA'
UNION ALL
SELECT 'Employee' as type, first_name, last_name, email
FROM employees
WHERE department = 'Sales'
ORDER BY last_name, first_name;12. EXISTS vs IN
Different ways to check for existence
-- Using EXISTS (generally more efficient)
SELECT c.customer_name, c.email
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= '2024-01-01'
);
-- Using IN
SELECT customer_name, email
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= '2024-01-01'
);13. RANK vs DENSE_RANK
Different ranking methods
SELECT
student_name,
score,
RANK() OVER (ORDER BY score DESC) as rank_with_gaps,
DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank,
ROW_NUMBER() OVER (ORDER BY score DESC) as row_num,
PERCENT_RANK() OVER (ORDER BY score DESC) as percentile
FROM exam_results
WHERE subject = 'Mathematics'
ORDER BY score DESC;14. Recursive CTE
Handle hierarchical data
WITH RECURSIVE employee_hierarchy AS (
SELECT
employee_id,
employee_name,
manager_id,
1 as level,
employee_name as path
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1,
eh.path || ' -> ' || e.employee_name
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY level, employee_name;15. Advanced Analytics
Moving averages and cumulative sums
SELECT
order_date,
daily_sales,
SUM(daily_sales) OVER (
ORDER BY order_date
ROWS UNBOUNDED PRECEDING
) as cumulative_sales,
AVG(daily_sales) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as seven_day_avg,
LEAD(daily_sales, 1) OVER (ORDER BY order_date) as next_day_sales,
LAG(daily_sales, 1) OVER (ORDER BY order_date) as prev_day_sales
FROM (
SELECT
DATE(order_date) as order_date,
SUM(total_amount) as daily_sales
FROM orders
GROUP BY DATE(order_date)
) daily_totals
ORDER BY order_date;Join Our SQL Academy
Get advanced SQL tips, exclusive tutorials, and practice exercises delivered weekly.
Join 10,000+ developers mastering SQL