Concept Scroll LogoConceptScroll
Join or Sign In
Informatics PracticesClass 12Querying and SQL

Querying and SQL | Class 12 Informatics Practices Notes

By ConceptScroll Team · Published on 17 July 2026 · 5 min read

Querying and SQL – this guide gives you a concise, exam-ready overview of Querying and SQL from Class 12 Informatics Practices, written by ConceptScroll editors and reviewed against the latest NCERT textbook.

1.2.2 Aggregate Functions

Aggregate functions in SQL, also known as Multiple Row Functions, operate on a set of rows and return a single summarized value. They are essential for data analysis and reporting, allowing calculations like totals, averages, counts, and finding maximum or minimum values. Unlike Single Row Functions that process one row at a time, aggregate functions process multiple rows collectively. Common aggregate functions include MAX() to find the largest value, MIN() for smallest, AVG() for average, SUM() for total sum, and COUNT() to count rows or non-null values. These functions are typically used with GROUP BY clauses to group data based on column values and perform aggregate calculations per group. COUNT(*) counts all rows including nulls, while COUNT(column) counts only non-null values.

📊 Diagram: Tables 1.8 and 1.9 compare single row and multiple row functions and list aggregate functions with examples.

🧪 Activity: Activity 1.5: Write queries to find sum of sale prices by customer C0001, max and min commission from SALE table.

🔗 Connection: Prepares for learning GROUP BY clause to group rows and apply aggregate functions.

Frequently asked questions

1. Answer the following questions: a) Define RDBMS. Name any two RDBMS software. b) What is the purpose of the following clauses in a select statement? i) ORDER BY ii) HAVING c) Site any two differences between Single_row functions and Aggregate functions. d) What do you understand by Cartesian Product? e) Write the name of the functions to perform the following operations: i) To display the day like “Monday”, “Tuesday”, from the date when India got independence. ii) To display the specified number of characters from a particular position of the given string. iii) To display the name of the month in which you were born. iv) To display your name in capital letters.

a) RDBMS (Relational Database Management System) is a database management system based on the relational model that stores data in tables (relations) consisting of rows and columns. Examples include MySQL and Oracle.

b) Purpose of clauses: i) ORDER BY: Used to sort the result set of a query by one or more columns either in ascending or descending order. ii) HAVING: Used to filter groups created by GROUP BY clause based on a condition.

c) Differences between Single_row functions and Aggregate f

2. Write the output produced by the following SQL commands: a) SELECT POW(2,3); b) SELECT ROUND(123.2345, 2), ROUND(342.9234,-1); c) SELECT LENGTH("Informatics Practices"); d) SELECT YEAR("1979/11/26"), MONTH("1979/11/26"), DAY("1979/11/26"), MONTHNAME("1979/11/26"); e) SELECT LEFT("INDIA",3), RIGHT("Computer Science",4); f) SELECT MID("Informatics",3,4), SUBSTR("Practices",3);

a) POW(2,3) returns 8 because 2 raised to the power 3 is 8.

b) ROUND(123.2345, 2) returns 123.23 (rounded to 2 decimal places). ROUND(342.9234, -1) rounds to nearest 10, returns 340.

c) LENGTH("Informatics Practices") returns 20 (number of characters including space).

d) YEAR("1979/11/26") returns 1979. MONTH("1979/11/26") returns 11. DAY("1979/11/26") returns 26. MONTHNAME("1979/11/26") returns 'November'.

e) LEFT("INDIA",3) returns 'IND'. RIGHT("Computer Science",4) returns

3. Consider the following table named "Product", showing details of products being sold in a grocery shop. | PCode | PName | UPrice | Manufacturer | | --- | --- | --- | --- | | P01 | Washing Powder | 120 | Surf | | P02 | Tooth Paste | 54 | Colgate | | P03 | Soap | 25 | Lux | | P04 | Tooth Paste | 65 | Pepsodant | | P05 | Soap | 38 | Dove | | P06 | Shampoo | 245 | Dove | a) Write SQL queries for the following: i. Create the table Product with appropriate data types and constraints. ii. Identify the primary key in Product. iii. List the Product Code, Product name and price in descending order of their product name. If PName is the same then display the data in ascending order of price. iv. Add a new column Discount to the table Product. v. Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all those products where the UPrice is more than 100, otherwise the discount will be 0. vi. Increase the price by 12 per cent for all the products manufactured by Dove. vii. Display the total number of products manufactured by each manufacturer. b) Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product: i. SELECT PName, Average(UPrice) FROM Product GROUP BY Pname; ii. SELECT DISTINCT Manufacturer FROM Product; iii. SELECT COUNT(DISTINCT PName) FROM Product; iv. SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;

a) SQL Queries:

i. CREATE TABLE Product ( PCode VARCHAR(5) PRIMARY KEY, PName VARCHAR(50), UPrice INT, Manufacturer VARCHAR(50) );

ii. Primary key is PCode.

iii. SELECT PCode, PName, UPrice FROM Product ORDER BY PName DESC, UPrice ASC;

iv. ALTER TABLE Product ADD COLUMN Discount DECIMAL(5,2);

v. UPDATE Product SET Discount = CASE WHEN UPrice > 100 THEN UPrice * 0.10 ELSE 0 END;

vi. UPDATE Product SET UPrice = UPrice * 1.12 WHERE Manufacturer = 'Dove';

vii. SELECT Manufacturer, COU

4. Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following: a) Add a new column Discount in the INVENTORY table. b) Set appropriate discount values for all cars keeping in mind the following: (i) No discount is available on the LXI model. (ii) VXI model gives a 10% discount. (iii) A 12% discount is given on cars other than LXI model and VXI model. c) Display the name of the costliest car with fuel type "Petrol". d) Calculate the average discount and total discount available on Car4. e) List the total number of cars having no discount.

a) ALTER TABLE INVENTORY ADD COLUMN Discount DECIMAL(5,2);

b) UPDATE INVENTORY SET Discount = CASE WHEN Model = 'LXI' THEN 0 WHEN Model = 'VXI' THEN Price 0.10 ELSE Price 0.12 END;

c) SELECT CarName FROM INVENTORY WHERE FuelType = 'Petrol' ORDER BY Price DESC LIMIT 1;

d) SELECT AVG(Discount) AS AverageDiscount, SUM(Discount) AS TotalDiscount FROM INVENTORY WHERE CarName = 'Car4';

e) SELECT COUNT(*) FROM INVENTORY WHERE Discount = 0;

Ready to ace this chapter?

Get the full Querying and SQL chapter — interactive notes, diagrams, worked solutions, polls and a free practice quiz — in the ConceptScroll app.

Open in ConceptScroll →

Study smarter with ConceptScroll

Daily NCERT-aligned reels, AI doubt solving and chapter quizzes — all free.

Start learning free
#cbse notes#class 12#informatics practices#ncert

Continue reading

← All articles