Lab · SQL and Databases
Lab: maintenance dues for a housing society
The brief
The treasurer of Green Meadows Co-operative Housing Society in Pune keeps September's maintenance in a messy table. Clean the names, mask phone numbers, count days late, label each flat, pick a contact for reminders and work out GST on the larger bills.
Why it matters
Society treasurers, small offices and accountants do this every month: tidy the names, see who paid late, send reminders and add GST where the law asks for it. SQL functions, CASE and COALESCE do all of it in one query instead of an evening of Excel.
Skills you practise
- UPPER, TRIM, SUBSTR and ||
- julianday and CAST
- CASE
- COALESCE and IS NULL
- ROUND
What you will have at the end
Five queries that turn a messy dues table into a notice-board list, a late-payment report, a reminder list and GST bills.
How it is marked (rubric, 100 points)
| Correct resultsAll five checks pass, including the unpaid flats in step 3 and the GST rule in step 5. | 50 |
|---|---|
| Right function for the jobTRIM and UPPER for names, julianday for dates, CASE in the right order, COALESCE for a fallback value. | 25 |
| ExplainedYou can explain why IS NULL must come first in the CASE, and why GST is charged on the whole ₹8,200 and not on ₹700. | 25 |
0/5 steps done
Step 1 of 5
Clean names and masked phones
01-names.sql lists each flat, the owner's name trimmed and in capitals, and the phone shown as XXXXXX plus its last four digits.
The volunteer typed names with extra spaces and mixed capitals. TRIM removes spaces at both ends and UPPER makes every letter a capital, so UPPER(TRIM(owner)) gives a clean name. The notice board must not show full phone numbers: SUBSTR(phone, -4) takes the last four characters, and || joins two pieces of text. Call the columns owner and phone.
Run shows what your code prints. Check runs the step's tests. Ctrl+Enter checks too.