SQL BASIC I

Aggregations, Group bys, HAVINGS, logical/Arithmetic operators, condtionals statemnets , if then, filters

What does Count do and can’t do? What is the wildcard to count all the columns ?

• it counts all the columns in a row , it ignores null on a specfic column

• COUNT(*) or COUNT(1)

What are the 5 aggregations? how do you write them ? Which ones ignoeres nulls ? When ?

MIN/MAX(column)
AVG(column)
COUNT(column)
SUM(column)

Write a group by clause on averge orders purchased by month ?

note:
order_date (date or datetime of the order)

• order_id (unique order identifier)

• amount or total (the amount/value of each order)

SELECT

YEAR(order_date) AS order_year,

MONTH(order_date) AS order_month,

AVG(amount) AS average_order_amount

FROM orders

GROUP BY

YEAR(order_date),

MONTH(order_date)

How does group by handle limits?

group by exceeds limit results returned could be omitted
aggregates happend before limits

Write a CASE statement to classify score as:

• 'Excellent' if score > 90

• 'Good' if score between 70 and 90

• 'Average' if score between 50 and 69

• 'Poor' if below 50

SELECT score,

CASE

WHEN score > 90 THEN 'Excellent'

WHEN score BETWEEN 70 AND 90 THEN 'Good'

WHEN score BETWEEN 50 AND 69 THEN 'Average'

ELSE 'Poor'

END AS performance

FROM students;

Write a query to count how many employees are in each salary category ('High' if >70000, else 'Low') per department.

SELECT department_id,

COUNT(CASE WHEN salary > 70000 THEN 1 END) AS high_salary_count,

COUNT(CASE WHEN salary <= 70000 THEN 1 END) AS low_salary_count

FROM employees

GROUP BY department_id;

Write a query to find the average order amount by order size category using CASE:

• 'Small' if amount < 100

• 'Medium' if between 100 and 500

• 'Large' if > 500

SELECT

CASE

WHEN amount < 100 THEN 'Small'

WHEN amount BETWEEN 100 AND 500 THEN 'Medium'

ELSE 'Large'

END AS order_size,

AVG(amount) AS avg_order_amount

FROM orders

GROUP BY order_size;

Write an SQL query to get departments with more than 5 employees using HAVING.

SELECT department_id, COUNT(*) AS num_employees

FROM employees

GROUP BY department_id

HAVING COUNT(*) > 5;

What is the difference between avg and proportion in data?

Average tells you the trend of the data (performance, efficiency, pricing , behavior patterns) , proportion tells you what contributes to the whole (distribution, market share, concentration)

setting up averages what belongs in the denominator vs the numerator when to use group by ?

denomm → the unit you're averaging
num → what you're counting per unit (group by clause)?
"How many X per Y?" → X / Y

/When to use AVG() function? and when to use SUM() / COUNT()?

Caculate the different averages and how you would get the metric ? Avg. value per row

Avg. total per group

Weighted average

Avg. ratio (clicks/impressions)

Avg. total price per customer

Use...

Avg. value per row

AVG(column)

Avg. total per group

SUM(column) / COUNT(group_key)

Weighted average

SUM(value * weight) / SUM(weight)

Avg. ratio (clicks/impressions)

SUM(clicks) / SUM(impressions)

Avg. total price per customer

SUM(price) / COUNT(DISTINCT customer)