SQL Examples

Count completed vs cancelled orders in one pass using FILTER clauses.

Use SUM(DISTINCT ...) and AVG(DISTINCT ...) to avoid double-counting repeated values.

A single query demonstrating all five core aggregates over the employees table.

Compare COUNT(*), COUNT(column), and COUNT(DISTINCT column) on the customers table.

Find the cheapest and most expensive product within each category.

Concatenate related values into a single comma-separated list per group.

Compute the average movie budget per genre using AVG() grouped by genre.

Count how many movies each director has made using COUNT() grouped by director.

Collapse individual orders into one summary row per customer using GROUP BY, COUNT, and SUM.

Group by both category and status to see order counts for every product-category/status combination.

Use HAVING to keep only departments whose average salary exceeds a threshold after aggregation.

Group store orders by order month to chart order volume over time.

Sum total units ordered per product category using SUM() grouped by category.

Sort employees so a specific department appears first, then by salary.

Use CASE WHEN to bucket employees into salary bands, and inside COUNT() to conditionally tally rows.

Use CASE with COUNT to pivot order statuses into separate columns.

Bucket employees into salary bands and count how many fall in each.

Use COALESCE to replace NULL manager names with a friendly placeholder.

Use a WITH clause to compute total spend per customer as a named, reusable result set, then filter and join against it.

Rewrite a nested subquery as a CTE to compute above-average-salary employees.

Chain several CTEs to compute department stats then rank departments by average salary.

Use a recursive CTE to generate a sequence of integers from 1 to 10.

Walk the manager tree recursively to list each employee and their management chain depth.

Find movies whose budget exceeds the average budget for their own genre, using a subquery that references the outer query's current row.

Wrap an aggregated subquery in FROM to rank products by total revenue.

Compare a value against a set using > ANY, > ALL, and IN subqueries.

Add a per-row subquery column showing each employee's distance from the company average.

Filter employees whose salary exceeds the average salary of their department.

Rank employees by salary company-wide using DENSE_RANK, which never skips numbers after ties.

Show each employee alongside the highest- and lowest-paid peer in their department.

Use LAG() to compare each employee's salary with the previous-highest-paid employee.

Use NTILE(4) to assign employees into salary quartiles.

Rank employees by salary within each department using RANK() OVER (PARTITION BY ...).

Assign a sequential number to each employee within their department, ordered by salary.

Build a cumulative salary total across employees ordered by hire date.

Use a CTE with ROW_NUMBER() to keep only the two highest-paid employees in each department.

Compute each employee's salary share of their department's total payroll.

Rank employees by salary within their own department using RANK() OVER (PARTITION BY ...), without collapsing rows the way GROUP BY does.

Use EXISTS with a correlated subquery to find authors who have published at least one book, and NOT EXISTS for the opposite.

Find customers who have at least one completed order using EXISTS.

Find employees who are not managers of anyone using NOT EXISTS.

Use FULL OUTER JOIN to see every department and every employee, surfacing mismatches on either side.

Use INNER JOIN to attach department names to employee records, keeping only rows that match on both sides.

Join employees to departments, then aggregate to compute headcount and average salary per department.

Use LEFT JOIN plus a NULL check to find customers who have never placed an order.

Use LEFT JOIN to list every movie alongside its audience rating, including movies that have never been rated.

Use RIGHT JOIN to list every department, including ones with no employees, alongside any matching staff.

Join the employees table to itself to pair each employee with their manager.

Chain INNER JOINs across the store schema to show what each customer bought and for how much.

Use UNION to combine employees and department heads into a single name list with a role label.