On this page
SQL questions in Class 12 Informatics Practices usually give you a small table and ask for the output of a query. That is easy to get almost right and lose the mark for one character or one NULL. This post runs every function in the syllabus on one sample table, works out the outputs, and lists the traps examiners like. If you need a refresher on SELECT, WHERE and joins, read our post on SQL basics for CBSE first.
Which SQL functions are in the syllabus?
Unit 2 of the CBSE Informatics Practices curriculum for 2026-27, worth 25 marks, lists:
Math: POWER(), ROUND(), MOD().
Text: UCASE()/UPPER(), LCASE()/LOWER(), MID()/SUBSTRING()/SUBSTR(), LENGTH(), LEFT(), RIGHT(), INSTR(), LTRIM(), RTRIM(), TRIM().
Date: NOW(), DATE(), MONTH(), MONTHNAME(), YEAR(), DAY(), DAYNAME().
Aggregate: MAX(), MIN(), AVG(), SUM(), COUNT(), and COUNT(*).
GROUP BY, HAVING and ORDER BY, and equi-joins on two tables.
The textbook chapter is NCERT's Querying and SQL Functions, Chapter 1 of the Class 12 IP book, which uses MySQL.
The sample table
All the examples use this table. The data is made up for practice.
CREATE TABLE RESULT (
RollNo INT PRIMARY KEY,
SName VARCHAR(30),
Stream VARCHAR(10),
Marks DECIMAL(5,2),
ExamDate DATE
);
INSERT INTO RESULT VALUES
(1, 'Aditi Rao', 'Science', 78.46, '2026-03-02'),
(2, 'Ishaan Mehta', 'Commerce', NULL, '2026-03-02'),
(3, 'Meera Iyer', 'Science', 92.50, '2026-03-04'),
(4, 'Kabir Khan', 'Commerce', 64.24, '2026-03-04');Ishaan was absent, so his Marks is NULL.
How do the maths functions behave?
`POWER(2, 10)` gives 1024.
`MOD(17, 5)` gives 2, the remainder of 17 ÷ 5.
`ROUND(78.46, 1)` gives 78.5. `ROUND(64.24, 1)` gives 64.2.
`ROUND(92.50)` gives 93. With no second argument, ROUND() rounds to a whole number.
The MySQL manual explains that for exact values, such as DECIMAL columns, ROUND() rounds halves away from zero, so 92.50 becomes 93. It also shows that for approximate values the result depends on the system: `ROUND(2.5)` gives 3 but `ROUND(25E-1)` can give 2.
How do the text functions count positions?
Positions start at 1, not 0 as in Python.
`UPPER(SName)` for row 1 gives 'ADITI RAO'.
`LENGTH('Ishaan Mehta')` gives 12, because the space counts.
`INSTR('Ishaan Mehta', ' ')` gives 7: I-s-h-a-a-n are positions 1 to 6, and the space is 7. If the substring is missing, INSTR() returns 0.
`LEFT(SName, INSTR(SName, ' ') - 1)` gives the first name: for row 2, LEFT('Ishaan Mehta', 6) = 'Ishaan'.
`MID(SName, INSTR(SName, ' ') + 1)` gives the surname: MID('Ishaan Mehta', 8) = 'Mehta'. With no length argument, MID() runs to the end of the string.
`RIGHT('Kabir Khan', 4)` gives 'Khan'.
`LENGTH(TRIM(' Delhi '))` gives 5, while LTRIM() and RTRIM() remove spaces from one side only.
NCERT uses this same LEFT and INSTR pattern to extract the part of an email address before the @ sign. One detail from the MySQL string functions manual: LENGTH() counts bytes, so for text in Hindi or other multibyte scripts it can be larger than the number of characters. CHAR_LENGTH() counts characters. Board questions use English text, where the two agree.
How do the date functions work?
`DAYNAME('2026-03-02')` gives 'Monday', and `DAYNAME('2026-03-04')` gives 'Wednesday'.
`MONTHNAME(ExamDate)` gives 'March'; `MONTH(ExamDate)` gives 3.
`DAY('2026-03-04')` gives 4 and `YEAR('2026-03-04')` gives 2026.
`NOW()` gives the current date and time; `DATE(NOW())` keeps only the date.
Why does AVG() not divide by the number of rows?
Because aggregate functions skip NULL. The MySQL manual on aggregates says that unless stated otherwise they ignore NULL values, that COUNT(expr) counts non-NULL values, and that COUNT(*) counts rows whether or not they contain NULL. On our table:
`COUNT(*)` = 4, but `COUNT(Marks)` = 3.
`SUM(Marks)` = 78.46 + 92.50 + 64.24 = 235.20.
`AVG(Marks)` = 235.20 ÷ 3 = 78.40. MySQL shows it with extra decimal places, as 78.400000. Dividing by 4 to get 58.80 is the classic wrong answer.
`MAX(Marks)` = 92.50 and `MIN(Marks)` = 64.24.
How do GROUP BY and HAVING work together?
SELECT Stream, COUNT(*), AVG(Marks)
FROM RESULT
GROUP BY Stream
HAVING AVG(Marks) > 70;Grouping gives Science (Aditi and Meera) and Commerce (Ishaan and Kabir). Science's average is (78.46 + 92.50) ÷ 2 = 85.48. Commerce's average is 64.24, because Ishaan's NULL is skipped, even though COUNT(*) for Commerce is 2. Only Science passes the HAVING test, so the output is one row: Science, 2, 85.48.
You cannot write `WHERE AVG(Marks) > 70`. NCERT's table of single-row and multiple-row functions notes that aggregate functions are used in the SELECT clause, not in WHERE. Use WHERE to filter rows before grouping and HAVING to filter groups after.
Which mistakes cost marks?
Counting positions from 0 in MID() and INSTR().
Forgetting the space when computing LENGTH() or splitting names.
Dividing AVG() by all rows when some values are NULL.
Aggregates in WHERE.
Mixing up MONTH() and MONTHNAME(): one gives 3, the other 'March'.
A practice routine
Create the RESULT table in MySQL and run every query above. Write your predicted output first, then compare.
Add a fifth row with a NULL Stream and see how GROUP BY treats it.
Write five queries for NCERT's CARSHOWROOM tables using at least one function from each group.
Put at least 15 tested queries in your practical file, as the curriculum requires.
When a query gives an output you cannot explain, the Fermi Problem Library has worked problems with a step-by-step solver that explains the reasoning rather than printing an answer.
Tonight, predict the output of `SELECT MID('Informatics', 3, 4);` and check it against NCERT's Table 1.6.
