Code / SQL with Data / Unit 3: Sorting and calculating

Calculated columns हिसाब वाले कॉलम

Lesson 7 of 13 · about 14 minutes · Do maths in SELECT: + - * /, ROUND, and the integer trap.

⚡ 0 XP

Step 1 of 2

Maths inside SELECT

A column in SELECT can be a calculation: + - * /. Give it a name with AS:

SELECT name, runs / matches AS per_match FROM players;

But wait — 1680 / 42 gives 40 while 1925 / 45 gives 42, not 42.78! When both numbers are whole numbers (integers), SQLite does integer division and throws away the decimal part. The fix: multiply by 1.0 first, so the maths uses decimals:

runs * 1.0 / matches

Then ROUND(value, 1) rounds to 1 decimal place.

Example

SELECT name,
  runs / matches AS wrong,
  runs * 1.0 / matches AS right
FROM players;

Compare the two columns: the first one lost its decimals.

🎯 Challenge: Runs per match for batters +20 XP

For players whose role is Batter, show name and ROUND(runs * 1.0 / matches, 1) named average. Sort by average, highest first.

Try twice to unlock the solution (0/2)

query.sqlCtrl ⏎

Tables (practice database)

Results

Press Run to see your query’s rows here.

Checklist

Press Run to check your code.

Your rows match the expected answer, in the right order
Uses ROUND

Lesson notes (text version)

1. Maths inside SELECT

A column in SELECT can be a calculation: + - * /. Give it a name with AS:

SELECT name, runs / matches AS per_match FROM players;

runs * 1.0 / matches

Then ROUND(value, 1) rounds to 1 decimal place.

                SELECT name,
  runs / matches AS wrong,
  runs * 1.0 / matches AS right
FROM players;

Challenge: Runs per match for batters

For players whose role is Batter, show name and ROUND(runs * 1.0 / matches, 1) named average. Sort by average, highest first.

2. Diwali offer prices

Meet our second dataset: a made-up neighbourhood kirana shop. The products table has id, name, category and price (in ₹). The sales table records every sale; we will use it in the next unit.

Calculated columns are perfect for prices. A 10% discount means the customer pays 90%, so the offer price is price * 0.9. A ₹240 atta bag becomes ₹216.

                -- Sample data for practice
SELECT name, price, price * 2 AS two_packs
FROM products;

Challenge: 10% off on snacks

For products in the Snacks category, show name, price and price * 0.9 named offer_price.