Querying and SQL Functions
Class 12 · Informatics Practices
4 ideas in Class 12 Informatics Practices, explained in plain words, with games and quizzes to practise.
Numeric Single Row Functions
Part of: Single Row Functions in SQL
A numeric single row function takes one number from each row and returns one number for that row. POWER(2, 3) gives 8, ROUND(2912.564, 1) gives 2912.6, and MOD(21, 2) gives 1, the remainder after division.
Why it matters. Bills, taxes, instalments and discounts all need row-wise arithmetic inside queries. Without these functions you would have to export data and calculate by hand, which is slow and error prone.
Key words
- Single row function
- : A function that works on one row at a time and returns one value per row.
- POWER
- : Raises a number to a given power, so POWER(2, 3) is 8.
- ROUND
- : Rounds a number to a stated count of decimal places.
- MOD
- : Gives the remainder left after dividing one number by another.
Try it in two minutes
- Pick a price, say 582613, and ask the learner what 12 percent of it means in rupees.
- Show that ROUND with one decimal place turns 69913.56 into 69913.6.
- Ask what remains when 21 toffees are shared equally between 2 children, linking it to MOD.
- Ask the learner to predict MOD(100, 30) before revealing that 10 are left over.
Try at home. Take any three grocery bills, compute 5 percent of each, and round the result to the nearest whole rupee.
String Single Row Functions
Part of: Single Row Functions in SQL
String functions reshape text in each row. UCASE turns Kite into KITE, LENGTH counts its 4 letters, MID pulls a middle slice, and TRIM removes extra spaces around words.
Why it matters. Names, emails and codes arrive messy, with mixed case and stray spaces. These functions clean and standardise text inside the query itself.
Key words
- UCASE
- : Changes every letter of a string to capitals.
- MID
- : Cuts out a slice starting at a given position, counting from 1.
- LENGTH
- : Counts how many characters a string holds.
- INSTR
- : Reports the position where a smaller string first appears.
- TRIM
- : Removes unwanted spaces from the edges of a string.
Try it in two minutes
- Write your name in mixed case and ask the learner to shout it in full capitals, like UCASE.
- Count the letters of MADAM together to show what LENGTH does.
- Cover the first letter of SCHOOL and reveal CHO to show MID at work.
- Add spaces around a word and wipe them away to show TRIM.
Try at home. Pick three contacts from a phone book, write their names in capitals, and note the length of each surname.
Date Functions and Aggregate Functions
Part of: Summarising and Combining Data
Date functions read pieces of a calendar value: MONTHNAME turns a November date into the word November, while NOW gives the current date and time. Aggregate functions squash a whole column into one number, like SUM totalling four canteen prices to 178000.
Why it matters. Reports need monthly splits and grand totals. Doing this inside the query keeps summaries always in step with the stored data.
Key words
- NOW
- : Gives the current system date and time at the moment of the query.
- MONTHNAME
- : Turns a date into the name of its month, like November.
- Aggregate function
- : Combines many rows into a single result, such as a sum.
- COUNT star
- : Counts every row of a table, including rows with empty values.
Try it in two minutes
- Ask the learner for their birth date, then pull out its day, month and year by words.
- Add four small bill amounts on paper, then show SUM doing it in one step.
- Find the largest and smallest bill to show MAX and MIN.
- Count how many bills have an amount written to show how COUNT skips blanks.
Try at home. List the birth months of five family members and count how many fall in each half of the year.
Grouping Rows and Combining Tables
Part of: Summarising and Combining Data
GROUP BY collects rows that share a value, so six canteen sales become three buyer groups. HAVING keeps only the groups that pass a test. UNION, INTERSECT and MINUS merge two similar tables, while JOIN links two tables through a shared column.
Why it matters. Real questions are grouped and linked: sales per buyer, members in either club, prices matched to items. These clauses answer them in one query.
Key words
- GROUP BY
- : Collects rows with equal values into one summary row each.
- HAVING
- : Filters grouped rows after grouping, unlike WHERE which filters first.
- UNION
- : Merges two similar tables and shows shared rows only once.
- INTERSECT
- : Keeps only rows found in both tables.
- MINUS
- : Keeps rows of the first table that are missing in the second.
- JOIN
- : Links two tables through a shared column to fetch related rows.
Try it in two minutes
- Sort six coins into piles by value to show grouping, then keep only piles with two or more coins to show HAVING.
- List children in a painting club and a robotics club, then circle those in both to show INTERSECT.
- Match price tags to items by code to show a JOIN.
- Ask how many links are needed to chain four tables together.
Try at home. Group your bookshelf by subject, count each group, and keep only subjects with more than two books.
What your child will practise
- Date Functions and Aggregate Functions: Desert Quick Fire (Quick Fire)
- Grouping Rows and Combining Tables: Jungle Puzzle (Clue Puzzle)
- Grouping Rows and Combining Tables: Space Quick Fire (Quick Fire)
- Numeric Single Row Functions: Ocean Quick Fire (Quick Fire)
- String Single Row Functions: Desert Puzzle (Clue Puzzle)
- Quiz: Date Functions and Aggregate Functions (8 questions)
- Quiz: Grouping Rows and Combining Tables (8 questions)
- Quiz: Numeric Single Row Functions (8 questions)
- Quiz: String Single Row Functions (8 questions)