Compare Salary to Company Average

basic

HR wants to identify employees earning above the company average salary. Show each employee with their salary, the company average, and the difference.

SELECT

name,

department,

salary,

(SELECT AVG(salary) FROM employees) AS company_avg,

salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees)

ORDER BY salary DESC;

name | department | salary | company_avg | diff_from_avg

Alice | Engineering | 95000 | 75000 | 20000

Bob | Engineering | 85000 | 75000 | 10000

Carol | Sales | 82000 | 75000 | 7000

Scalar subquery (SELECT AVG(salary)) executes once and returns 75000. This value is cached and reused in SELECT twice and WHERE once. No performance penalty for reusing. WHERE filters to only above-average employees.

All

Product Pricing with Multiple Aggregates

basic

Show each product with min, max, and average prices for context.

SELECT

product_name,

price,

(SELECT MIN(price) FROM products) AS lowest_price,

(SELECT MAX(price) FROM products) AS highest_price,

(SELECT AVG(price) FROM products) AS avg_price

FROM products

WHERE category = 'Electronics'

ORDER BY price DESC;

product_name | price | lowest | highest | avg_price

Laptop | 999.99 | 19.99 | 999.99 | 149.99

Tablet | 599.99 | 19.99 | 999.99 | 149.99

Phone | 299.99 | 19.99 | 999.99 | 149.99

Three scalar subqueries calculate MIN, MAX, AVG. Each executes once, results cached. Perfect for adding context columns to reports.

All

Department Performance vs Company

intermediate

Find departments with average salaries above company average.

SELECT