Scalar Functions: ucase(), lcase(), round(), mid()

📖 Theory

Transform, do not summarise

Last lesson's aggregates crushed many rows into one number. Scalar functions do the opposite: they touch one value at a time and hand back one result per row, leaving every row standing.

Meera wants item names in CAPITALS for a printed list, or prices rounded to whole rupees, or just the first three letters of each category as a code. Each transforms every row individually.

The scalar-vs-aggregate distinction is small but exam-critical: does the function summarise the table, or transform each row? Here, transform.

📖 Theory

A stamp vs a juicer

Last lesson's aggregate was a juicer: many oranges, one glass. A scalar function is a rubber stamp: it presses once on each page, every page still exists, each just marked. Run UCASE on 500 item names and you get 500 uppercase names, not one. Scalar = one stamp per row; aggregate = one juice from the whole pile. Same input table, opposite output shape.

📊 At a glance

Common scalar functions

⌨️ Practical

Transform each row

-- One result PER ROW: all 5 rows still returned
SELECT UCASE(item)          AS name,
       ROUND(price * 0.9, 0) AS discounted,
       MID(category, 1, 3)   AS code
FROM sales;
-- SUGAR | 41 | Gro
-- TEA   |108 | Bev
-- RICE  | 54 | Gro
-- SOAP  | 32 | Per
-- MILK  | 27 | Dai

🧩 Quiz

SELECT UCASE(item) FROM sales (5 rows) returns how many rows, and SELECT COUNT(item) FROM sales returns how many?

1. UCASE returns 5 rows (one per row); COUNT returns 1 row (a single total)
2. Both return 1 row
3. Both return 5 rows
4. UCASE returns 1, COUNT returns 5

💭 Think first

Make a category code

Meera wants a 3-letter uppercase code from each category (Grocery to GRO). Which TWO scalar functions does she combine, and in what order?

⚠️ Watch out

Where marks leak

The core confusion: scalar (one result per row, table stays) vs aggregate (one result total, rows collapse), define the difference clearly. Note dialect names: UCASE/UPPER and LCASE/LOWER are the same thing in different databases; MID (Access/MySQL) and SUBSTRING likewise. And ROUND's second argument sets decimal places (ROUND(n,0) = whole number). Getting the scalar-vs-aggregate line right is the whole point of this pair of lessons.

📖 Theory

Excel, one more time

UPPER, LOWER, ROUND, MID, these are literally Excel text and number functions (Unit 1's ROUND, Unit 2's helpers), now in SQL. The transfer is complete: spreadsheets taught you the functions; SQL scales them to real databases. Two lessons closed the query story; two remain to close the subject: sequences for automatic ID numbers, then views. Next: creating a sequence.

✅ Summary

Key takeaways

• Scalar functions transform ONE value and return one result PER ROW (the table keeps all its rows).
• This contrasts with aggregates, which collapse many rows into one value.
• UCASE/UPPER and LCASE/LOWER change case; ROUND(n,d) rounds; MID/SUBSTRING extracts text.
• SELECT with only scalars returns every row; SELECT with an aggregate returns one.
• Functions nest: inner runs first (UCASE(MID(category,1,3))).
• Memory hook: scalar is a stamp on each page; aggregate is a juicer for the whole pile.