DBMS & SQL›Level 13
PART 3 • SQL MASTERY

Turn Rows into Meaningful Evidence

Transform values, form groups and calculate summaries without losing track of result grain, NULL behavior or filtering order.

Level 13 of 18Analytics SQL150–190 minutesLevel 12 recommended
BY THE END, YOU CAN
  • Choose scalar and aggregate functions.
  • Predict COUNT and NULL behavior.
  • Define correct grouping grain.
  • Separate WHERE from HAVING.
  • Build conditional aggregates.
  • Explain grouping errors.
01 • TRANSFORM EACH ROW

Scalar Functions Return One Value per Input Row

TEXTUPPER(name)LOWER(email)TRIM(city)CHAR_LENGTH(name)

Function names and concatenation syntax vary across products.

NUMERICROUND(cgpa, 1)ABS(balance)CEILING(score)MOD(value, 2)

Confirm precision, rounding mode and return type.

DATE / TIMECURRENT_DATEEXTRACT(YEAR FROM joined_on)joined_on + INTERVAL '7 days'

Date arithmetic is highly dialect-sensitive.

NULL HANDLINGCOALESCE(city, 'Unknown')NULLIF(score, 0)

Replacement values must preserve business meaning.

One source row→UPPER(name)→One transformed result value
02 • SUMMARIZE A SET

Aggregate Functions Collapse Many Rows into One Value

COUNT(*)Counts rows

Includes rows even when every selected data value contains NULL.

COUNT(cgpa)Counts non-NULL values

Rows whose cgpa is NULL do not contribute.

SUM(credits)Adds non-NULL values

The result of an empty or all-NULL input is generally NULL, not zero.

AVG(cgpa)Averages non-NULL values

Conceptually SUM(non-NULL values) divided by COUNT(cgpa).

MIN / MAXFind boundaries

Work with orderable values and ignore NULL inputs.

INPUT CGPA8.6, NULL, 9.1, 8.0
COUNT(*) = 4COUNT(cgpa) = 3AVG(cgpa) = 8.57
03 • CHOOSE THE RESULT GRAIN

GROUP BY Produces One Result Row per Distinct Group

AIML · Anu · 8.6CSE · Bharat · 7.9AIML · Charan · 9.1CSE · Esha · NULL
GROUP BY branch →
AIML2 studentsAVG = 8.85
CSE2 studentsAVG = 7.90
SELECT branch, COUNT(*) AS student_count,
       ROUND(AVG(cgpa), 2) AS average_cgpa
FROM students
GROUP BY branch;
Grouping rule

Every selected expression must normally be aggregated, functionally dependent where the DBMS permits it, or listed in GROUP BY.

branch ✓   COUNT(*) ✓   name ✗
04 • FILTER AT THE CORRECT STAGE

WHERE Filters Rows; HAVING Filters Groups

1. FROM

Read candidate rows.

→
2. WHERE

Remove individual rows before grouping.

→
3. GROUP BY

Form groups from surviving rows.

→
4. HAVING

Remove groups after aggregates exist.

ROW CONDITIONWHERE status = 'ACTIVE'

Inactive students never enter a group.

GROUP CONDITIONHAVING AVG(cgpa) >= 8.5

Only groups whose calculated average qualifies remain.

05 • CALCULATE MULTIPLE METRICS IN ONE PASS

CASE Turns Conditions into Aggregate Inputs

SELECT branch,
       COUNT(*) AS total,
       SUM(CASE WHEN cgpa >= 8.5 THEN 1 ELSE 0 END) AS strong_count,
       ROUND(100.0 * SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END)
             / COUNT(*), 1) AS active_percent
FROM students
GROUP BY branch;
Condition TRUECASE contributes 1
Condition FALSECASE contributes 0
SUMCounts the 1 values
Guard divisionUse decimal arithmetic and protect zero denominators
06 • DIAGNOSE WRONG SUMMARIES

Four Common Grouping Mistakes

UNGROUPED COLUMNSELECT branch, name, COUNT(*) GROUP BY branch

name is neither grouped nor aggregated and has no single value per branch.

AGGREGATE IN WHEREWHERE AVG(cgpa) >= 8

The average does not exist at the WHERE stage; use HAVING.

WRONG COUNTCOUNT(cgpa)

This counts recorded CGPA values, not all students. Use COUNT(*) for rows.

INTEGER DIVISIONstrong_count / total

Some dialect/type combinations truncate the fraction. Force decimal arithmetic.

07 • BUILD A GROUPED REPORT

Interactive Aggregate Analytics Laboratory

Choose row filtering, grouping, aggregate metrics and HAVING. See the generated SQL, group membership and final report.

GENERATED SQL
FINAL RESULT

08 • COUNT WHAT YOU ACTUALLY MEAN

NULL and Aggregate Outcome Laboratory

Select a calculation to trace which values contribute.

09 • CHECK YOUR UNDERSTANDING

Ten Formative Concept Checks

1. A scalar function normally returns:

2. COUNT(*) counts:

3. COUNT(cgpa) excludes:

4. GROUP BY branch creates:

5. Which clause filters groups?

6. WHERE executes logically:

7. AVG(cgpa) normally ignores:

8. To count rows satisfying a condition, use:

9. SUM over no matching rows generally returns:

10. In SELECT with GROUP BY, an ordinary selected column should usually be:

Answered correctly: 0 of 10
10 • EXPLAIN & PREPARE

University and Interview Questions

2-MARK QUESTIONS
  1. Scalar versus aggregate function?
  2. COUNT(*) versus COUNT(column)?
  3. Define grouping grain.
  4. WHERE versus HAVING?
  5. What does COALESCE do?
5-MARK / PRACTICAL
  1. Build branch-wise student counts.
  2. Filter groups by average CGPA.
  3. Explain aggregate NULL behavior.
  4. Write conditional aggregation.
INTERVIEW QUESTIONS
  1. Why does an ungrouped column fail?
  2. When should HAVING be avoided?
  3. How can integer division break percentages?
  4. What does AVG divide by?
  5. How do empty inputs affect SUM?
Show the grouped-query answer framework
  1. State one output row's meaning.
  2. Filter unwanted source rows with WHERE.
  3. Choose grouping columns that define that grain.
  4. Select aggregates with correct NULL semantics.
  5. Add conditional metrics when needed.
  6. Filter calculated groups with HAVING.
  7. Protect division and empty-set cases.
  8. Verify one group manually from its member rows.
LEVEL 13 SUMMARY

You Can Now Explain Every Number in a Report

  • Scalar functions transform each row.
  • Aggregates summarize sets and usually ignore NULL expressions.
  • GROUP BY defines one result row per distinct group.
  • WHERE filters before grouping; HAVING filters after it.
  • Conditional aggregation calculates targeted metrics.
  • Correct analytics begins by declaring result grain.
COURSE CHECKPOINT

Mark this level when you can trace a grouped query from source rows to every reported aggregate.

Saved in this browser only.