Complete Summary and Solutions for Querying and SQL Functions – NCERT Class XII Informatics Practices, Chapter 1 – Explanation, Questions, Answers

Detailed summary and explanation of Chapter 1 'Querying and SQL Functions' from the NCERT Informatics Practices textbook for Class XII, covering the basics of SQL querying, different types of queries, and the use of SQL functions for data retrieval and manipulation. The chapter explains SELECT statements, usage of WHERE, ORDER BY, GROUP BY, and HAVING clauses, along with built-in SQL functions such as aggregate functions, scalar/string functions, date and numeric functions, illustrated through examples and typical exam-style tasks. It also includes all NCERT exercises, questions, and solutions to strengthen conceptual understanding and practical SQL skills.

Updated: 2 days ago

Categories: NCERT, Class XII, Informatics Practices, Chapter 1, SQL, Databases, Querying, SQL Functions, SELECT Statement, Aggregate Functions, String Functions, Date Functions, Numeric Functions, Summary, Questions, Answers, Explanation

Tags: Querying and SQL Functions, SQL, Databases, SELECT, WHERE, ORDER BY, GROUP BY, HAVING, Aggregate Functions, String Functions, Date Functions, Numeric Functions, NCERT, Class 12, Informatics Practices, Summary, Explanation, Questions, Answers, Chapter 1

Querying and SQL Functions - Class 12 Informatics Practices Chapter 1 Ultimate Study Guide 2025

Full Chapter Summary & Detailed Notes - Querying and SQL Functions Class 12 NCERT

Overview & Key Concepts

Chapter Goal: Build on Class XI database concepts; learn advanced SQL for querying, functions, grouping, relations. Exam Focus: Single/multiple row functions, GROUP BY, joins (Cartesian, equi), CARSHOWROOM database. 2025 Updates: MySQL emphasis, real-world data manipulation. Fun Fact: Quote by Lev Manovich on finding existing data. Core Idea: Functions for efficient data handling in databases.
Wider Scope: From single row (math/string/date) to aggregate; operations on relations, multi-table queries.
Expanded Content: Point-wise for recall; add 2025 relevance like query optimization.

Introduction to Querying

Database Setup: CARSHOWROOM schema with INVENTORY (CarId, CarName, Price, etc.), CUSTOMER (CustId, CustName, etc.), SALE (InvoiceNo, CarId, etc.), EMPLOYEE (EmpId, EmpName, etc.).
Purpose: Perform queries using functions, ordering, grouping, multi-table ops.
Example: Populate/manipulate data with SQL.
Expanded: Builds on MySQL basics; focus on efficiency.

Conceptual Diagram: Schema (Fig 1.1)

Relations linked via keys; visualizes data flow.

Why This Guide Stands Out

Comprehensive: All functions/code point-wise, 2025 with query examples; analyzed for database prep.

Functions in SQL

Definition: Perform tasks returning values; single/aggregate.
Single Row: Scalar; apply to one value (e.g., POWER(), ROUND()). Categories: Numeric (POWER, ROUND, MOD), String (UCASE, LCASE, MID, etc.), Date (NOW, DATE, MONTH, etc.).
Aggregate: Multiple rows (not detailed in provided pages).
Do You Know?: Functions on single/multiple records.
Expanded: Input/output types vary.

Exam Activities

Write queries for GST calc, string manipulation.

Group By in SQL

Definition: Group records by criteria for aggregate functions.
Expanded: Used with HAVING for filters.

Operations on Relations

Definition: Union, intersect, minus (not detailed).
Expanded: Set operations for combining results.

Using Two Relations in a Query

Definition: Joins like Cartesian (cross), equi-join.
Expanded: Multi-table data retrieval.

Conceptual Diagrams: Functions (Fig 1.2)

Categories: Numeric, String, Date.

Summary Key Points

SQL Functions: Single row (math/string/date); GROUP BY for aggregates; joins for multi-tables. Database: CARSHOWROOM.
Impact: Efficient querying; challenges: Syntax accuracy.

Project & Group Ideas

Group: Build queries on sample DB; individual: Function poster.
Debate: Single vs aggregate use.

Key Definitions & Terms - Complete Glossary

All terms from chapter; detailed with examples, relevance. Expanded: 30+ terms grouped; added advanced like "Scalar Functions", "Equi-Join" for depth/easy flashcards.

SQL Function

Performs task returning values. Ex: POWER(2,3)=8. Relevance: Data manipulation.

Single Row Function

Applies to one value. Ex: ROUND(2912.564,1)=2912.6. Relevance: Scalar ops.

Aggregate Function

Applies to multiple rows. Ex: SUM on group. Relevance: Summaries.

POWER(X,Y)

X to power Y. Ex: POWER(2,3)=8. Relevance: Math calc.

ROUND(N,D)

Rounds N to D decimals. Ex: ROUND(283.2)=283. Relevance: Precision.

MOD(A,B)

Remainder A/B. Ex: MOD(21,2)=1. Relevance: Division.

UCASE(string)

Uppercase string. Ex: UCASE("informatics")="INFORMATICS". Relevance: Case change.

LCASE(string)

Lowercase string. Ex: LCASE("INFORMATICS")="informatics". Relevance: Case change.

MID(string,pos,n)

Substring from pos size n. Ex: MID("Informatics",3,4)="form". Relevance: Extraction.

LENGTH(string)

Character count. Ex: LENGTH("Informatics")=11. Relevance: Size check.

LEFT(string,N)

Left N chars. Ex: LEFT("Computer",4)="Comp". Relevance: Prefix.

RIGHT(string,N)

Right N chars. Ex: RIGHT("SCIENCE",3)="NCE". Relevance: Suffix.

INSTR(string,substring)

Position of substring. Ex: INSTR("Informatics","ma")=6. Relevance: Search.

LTRIM(string)

Remove leading spaces. Ex: LTRIM(" DELHI")="DELHI". Relevance: Clean.

RTRIM(string)

Remove trailing spaces. Ex: RTRIM("PEN ")="PEN". Relevance: Clean.

TRIM(string)

Remove both spaces. Ex: TRIM(" MADAM ")="MADAM". Relevance: Clean.

NOW()

Current date/time. Ex: NOW(). Relevance: Timestamp.

DATE()

Extract date. Ex: DATE(NOW()). Relevance: Date part.

MONTH()

Month number. Ex: MONTH("2019-01-24")=1. Relevance: Time extract.

MONTHNAME()

Month name. Ex: MONTHNAME("2019-01-24")="January". Relevance: Readable.

YEAR()

Year extract. Ex: YEAR("2019-01-24")=2019. Relevance: Time extract.

DAY()

Day number. Ex: DAY("2019-01-24")=24. Relevance: Time extract.

DAYNAME()

Day name. Ex: DAYNAME("2019-01-24")="Thursday". Relevance: Readable.

GROUP BY

Group rows. Ex: GROUP BY FuelType. Relevance: Aggregates.

Cartesian Product

All row combos. Ex: SELECT * FROM A, B. Relevance: Joins.

Equi-Join

Join on equality. Ex: ON A.id = B.id. Relevance: Related data.

Scalar Function

Single value return. Ex: ROUND(). Relevance: Per row.

Numeric Function

Math ops. Ex: POWER. Relevance: Calculations.

String Function

Text ops. Ex: UCASE. Relevance: Manipulation.

Date Function

Time ops. Ex: NOW. Relevance: Dates.

Relation

Table in DB. Ex: INVENTORY. Relevance: Data store.

Schema

DB structure. Ex: CARSHOWROOM. Relevance: Design.

Query

Data retrieval. Ex: SELECT. Relevance: Access.

Tip: Group by category; examples for recall. Depth: Function debates. Errors: Syntax mix. Interlinks: To DB chapters. Advanced: Optimization. Real-Life: Business queries. Graphs: Function types. Coherent: Numeric → String. For easy learning: Flashcard per term with example.

Text Book Questions & Answers - NCERT Exercises

Direct from activities/examples. Answers based on chapter content, point-wise for exams.

Activities from Text

Activity 1.1: Using SALE table, write SQL for...

Answer:

a) Display InvoiceNo and commission rounded to zero: SELECT InvoiceNo, ROUND(Commission,0) FROM SALE;
b) Details where payment mode is credit card: SELECT * FROM SALE WHERE PaymentMode = 'Credit Card';

Activity 1.2: Using INVENTORY, write SQL for...

Answer:

a) Uppercase CarName if starts with 'B': SELECT UCASE(CarName) FROM INVENTORY WHERE CarName LIKE 'B%';
b) Substring from pos 3 if Model length >4: SELECT MID(Model,3) FROM INVENTORY WHERE LENGTH(Model)>4;

Example Questions

Example 1.1: GST Calculation

Answer:

a) GST as 12% rounded to 1 decimal: SELECT ROUND(12/100*Price,1) "GST" FROM INVENTORY;
b) Add FinalPrice: ALTER TABLE INVENTORY ADD FinalPrice Numeric(10,1); Then update.
c) EMI: ROUND((FinalPrice-MOD(FinalPrice,10000))/10,0)
d) Remaining: MOD(FinalPrice,10000)

Example 1.2: Commission

Answer:

a) Add Commission: ALTER TABLE SALE ADD(Commission Numeric(7,2));
b) Update and display >73000: UPDATE SALE SET Commission=12/100*SalePrice; SELECT * FROM SALE WHERE Commission > 73000;
c) Rounded Commission: SELECT InvoiceNo, SalePrice, ROUND(Commission,0) FROM SALE;

Example 1.3: String Functions on CUSTOMER

Answer:

a) Lower name, upper email: SELECT LOWER(CustName), UPPER(Email) FROM CUSTOMER;
b) Length and part before @: SELECT LENGTH(Email), LEFT(Email, INSTR(Email,"@")-1) FROM CUSTOMER;

Tip: Practice code; full marks: Syntax + output.

Key Concepts - In-Depth Exploration

Core ideas with examples, pitfalls, interlinks. Expanded: All concepts with steps/examples/pitfalls.

Single Row Functions

Steps: 1. Apply per value, 2. Categories (math/string/date). Ex: ROUND for precision. Pitfall: Wrong args. Interlink: Queries. Depth: Scalar return.

Numeric Functions

Steps: 1. POWER for exponents, 2. ROUND for decimals. Ex: GST calc. Pitfall: Integer overflow. Interlink: Math ops. Depth: Remainders with MOD.

String Functions

Steps: 1. UCASE/LCASE for case, 2. MID for substr. Ex: Email parse. Pitfall: Position off-by-one. Interlink: Data clean. Depth: Trimming spaces.

Date Functions

Steps: 1. NOW for current, 2. MONTHNAME for readable. Ex: SaleDate extract. Pitfall: Format mismatch. Interlink: Time queries. Depth: Day/week ops.

GROUP BY

Steps: 1. Group criteria, 2. With aggregates. Ex: Group by FuelType. Pitfall: No aggregate error. Interlink: HAVING. Depth: Summaries.

Relations Operations

Steps: 1. Joins for multi-table. Ex: SALE with INVENTORY. Pitfall: Cartesian explosion. Interlink: Keys. Depth: Equi vs non-equi.

Database Schema

Steps: 1. Define tables/keys. Ex: CarId foreign. Pitfall: No relations. Interlink: Integrity. Depth: Normalization.

Query Building

Steps: 1. SELECT columns, 2. FROM tables, 3. WHERE conditions. Ex: Commission >73000. Pitfall: Syntax. Interlink: Functions. Depth: Optimization.

Advanced: Aggregate details, joins types. Pitfalls: Arg errors. Interlinks: To XI DB. Real: Business reports. Depth: 8 concepts details. Examples: Code. Graphs: Function tables. Errors: Type confusion. Tips: Steps evidence; compare (single/aggregate).

Technique Examples - From Text with Simple Explanations

Expanded with evidence, interpretations; focus on application, analysis. Added function/query breakdowns.

Example 1: GST Calculation (Numeric)

Simple Explanation: 12% on Price rounded.

Step 1: Calc 12/100*Price.
Step 2: ROUND to 1 decimal.
Step 3: Display as GST.
Simple Way: Multiply → Round → Output.

SELECT ROUND(12/100*Price,1) "GST" FROM INVENTORY;

Example 2: Add/Update FinalPrice

Simple Explanation: Sum Price + GST.

Step 1: ALTER add column.
Step 2: UPDATE set value.
Step 3: SELECT to verify.
Simple Way: Add → Update → View.

ALTER TABLE INVENTORY ADD FinalPrice Numeric(10,1);
UPDATE INVENTORY SET FinalPrice=Price+ROUND(Price*12/100,1);

Example 3: EMI and Remaining

Simple Explanation: Divide into 10, mod 10000.

Step 1: Subtract mod from FinalPrice.
Step 2: Divide by 10, round.
Step 3: Mod for remaining.
Simple Way: Adjust → Divide → Remainder.

SELECT CarId, FinalPrice, ROUND((FinalPrice-MOD(FinalPrice,10000))/10,0) "EMI", MOD(FinalPrice,10000) "Remaining" FROM INVENTORY;

Example 4: Commission Calc

Simple Explanation: 12% on SalePrice.

Step 1: Add column.
Step 2: Update 12/100*SalePrice.
Step 3: Select >73000.
Simple Way: Add → Calc → Filter.

ALTER TABLE SALE ADD(Commission Numeric(7,2));
UPDATE SALE SET Commission=12/100*SalePrice;
SELECT * FROM SALE WHERE Commission > 73000;

Example 5: String on Customer

Simple Explanation: Case and extract.

Step 1: LOWER name.
Step 2: UPPER email.
Step 3: LENGTH and LEFT before @.
Simple Way: Case → Length → Extract.

SELECT LOWER(CustName), UPPER(Email) FROM CUSTOMER;
SELECT LENGTH(Email), LEFT(Email, INSTR(Email,"@")-1) FROM CUSTOMER;

Tip: Run in MySQL; troubleshoot (e.g., syntax errors). Added for functions/queries.

Quick Revision Notes & Mnemonics

Concise summaries for subtopics. Tables for scan: Key points, examples, mnemonics. Covers functions, GROUP BY, joins.

Subtopic	Key Points	Examples	Mnemonics/Tips
Single Row Functions	Per value. Math: POWER/ROUND/MOD. String: UCASE/MID. Date: NOW/MONTH.	ROUND(Price,1); MID(Model,3).	MSD (Math, String, Date). Tip: "Many Single Duties" – Categories.
Numeric Functions	POWER(X,Y). ROUND(N,D). MOD(A,B).	POWER(2,3)=8; MOD(21,2)=1.	PRM (Power, Round, Mod). Tip: "Power Rounds Mods" – Order use.
String Functions	UCASE/LCASE. MID/LENGTH/LEFT/RIGHT. INSTR/LTRIM/RTRIM/TRIM.	UCASE("hi")="HI"; TRIM(" hi " )="hi".	UM LIRT (Ucase/Mid/Length/Instr/Right/Trim). Tip: "Use My Lengthy Instructions Right Trimly".
Date Functions	NOW/DATE. MONTH/MONTHNAME. YEAR/DAY/DAYNAME.	MONTHNAME(NOW())="November".	NDMYD (Now/Date/Month/Year/Day). Tip: "Now Date My Year Day" – Sequence.
GROUP BY	Group criteria. With aggregates/HAVING.	GROUP BY FuelType.	GH (Group, Having). Tip: "Group Having Fun" – Filters.

Overall Tip: Use MSD-PRM-UM LIRT-NDMYD-GH for full scan (5 mins). Flashcards: Front (function), Back (syntax + mnemonic). Covers 100% chapter – easy for exams!

Key Terms & Processes - All Key

Expanded table 30+ rows; quick ref. Added advanced (e.g., Scalar, Join). Overflow fixed.

Term/Process	Description	Example	Usage
SQL Function	Task returning value	POWER(2,3)	Manipulation
Single Row	Per value	ROUND	Scalar
Aggregate	Multiple rows	SUM	Summaries
POWER(X,Y)	X^Y	POWER(2,3)=8	Exponent
ROUND(N,D)	Round to D	ROUND(2912.564,1)=2912.6	Precision
MOD(A,B)	A % B	MOD(21,2)=1	Remainder
UCASE	Uppercase	UCASE("hi")="HI"	Case
LCASE	Lowercase	LCASE("HI")="hi"	Case
MID	Substring	MID("Informatics",3,4)="form"	Extract
LENGTH	Char count	LENGTH("hi")=2	Size
LEFT	Left N chars	LEFT("Computer",4)="Comp"	Prefix
RIGHT	Right N chars	RIGHT("SCIENCE",3)="NCE"	Suffix
INSTR	Position	INSTR("Informatics","ma")=6	Search
LTRIM	Leading trim	LTRIM(" hi")="hi"	Clean
RTRIM	Trailing trim	RTRIM("hi ")="hi"	Clean
TRIM	Both trim	TRIM(" hi ")="hi"	Clean
NOW	Current time	NOW()	Timestamp
DATE	Date part	DATE(NOW())	Extract
MONTH	Month num	MONTH("2019-01-01")=1	Extract
MONTHNAME	Month name	MONTHNAME("2019-01-01")="January"	Readable
YEAR	Year	YEAR("2019-01-01")=2019	Extract
DAY	Day num	DAY("2019-01-24")=24	Extract
DAYNAME	Day name	DAYNAME("2019-01-24")="Thursday"	Readable
GROUP BY	Group rows	GROUP BY FuelType	Aggregates
Cartesian Product	All combos	FROM A, B	Cross join
Equi-Join	Equality join	ON A.id=B.id	Related
Schema	DB structure	CARSHOWROOM	Design
Relation	Table	INVENTORY	Store
Query	Data access	SELECT	Retrieval
Scalar	Single return	Single row func	Per row
ALTER TABLE	Modify table	ADD column	Structure
UPDATE	Set values	SET Commission=...	Modify
WHERE	Filter	WHERE Commission>73000	Condition

Tip: Examples memory; sort category. Easy: Table scan. Added 10 rows depth.

SQL Processes Step-by-Step

Step-by-step breakdowns of core processes. Visual descriptions; focus on actionable steps with examples.

Process 1: Numeric Function Use (GST Calc)

Step 1: Select Price column.
Step 2: Multiply by 0.12.
Step 3: ROUND to 1 decimal.
Step 4: Alias as GST.

Visual: Select → Multiply → Round → Output.

Process 2: Add/Update Column

Step 1: ALTER TABLE ADD.
Step 2: UPDATE SET formula.
Step 3: Verify with SELECT.
Step 4: Handle warnings.

Visual: Add → Set → View → Check.

Process 3: String Extraction (Email)

Step 1: INSTR for @ pos.
Step 2: LEFT to pos-1.
Step 3: LENGTH for total.
Step 4: Display together.

Visual: Find Pos → Extract Left → Length → Output.

Process 4: Commission Filter

Step 1: Calc Commission.
Step 2: UPDATE table.
Step 3: WHERE > threshold.
Step 4: ROUND if needed.

Visual: Calc → Update → Filter → Round.

Process 5: EMI Calculation

Step 1: MOD for remainder.
Step 2: Subtract from total.
Step 3: Divide by 10.
Step 4: ROUND to 0.

Visual: Mod → Subtract → Divide → Round.

Tip: Follow like checklist; apply to scenarios. Easy: Number + example per step.

This article has been published in CBSE Class 12 Board Examination. Explore everything related to it here.

Group Discussions

No forum posts available.

Easily Share with Your Tribe

Complete Summary and Solutions for Querying and SQL Functions – NCERT Class XII Informatics Practices, Chapter 1 – Explanation, Questions, Answers

Querying and SQL Functions

Full Chapter Summary & Detailed Notes - Querying and SQL Functions Class 12 NCERT

Overview & Key Concepts

Introduction to Querying

Conceptual Diagram: Schema (Fig 1.1)

Why This Guide Stands Out

Functions in SQL

Exam Activities

Group By in SQL

Operations on Relations

Using Two Relations in a Query

Conceptual Diagrams: Functions (Fig 1.2)

Summary Key Points

Project & Group Ideas

Key Definitions & Terms - Complete Glossary

SQL Function

Single Row Function

Aggregate Function

POWER(X,Y)

ROUND(N,D)

MOD(A,B)

UCASE(string)

LCASE(string)

MID(string,pos,n)

LENGTH(string)

LEFT(string,N)

RIGHT(string,N)

INSTR(string,substring)

LTRIM(string)

RTRIM(string)

TRIM(string)

NOW()

DATE()

MONTH()

MONTHNAME()

YEAR()

DAY()

DAYNAME()

GROUP BY

Cartesian Product

Equi-Join

Scalar Function

Numeric Function

String Function

Date Function

Relation

Schema

Query

Text Book Questions & Answers - NCERT Exercises

Activities from Text

Activity 1.1: Using SALE table, write SQL for...

Activity 1.2: Using INVENTORY, write SQL for...

Example Questions

Example 1.1: GST Calculation

Example 1.2: Commission

Example 1.3: String Functions on CUSTOMER

Key Concepts - In-Depth Exploration

Single Row Functions

Numeric Functions

String Functions

Date Functions

GROUP BY

Relations Operations

Database Schema

Query Building

Technique Examples - From Text with Simple Explanations

Example 1: GST Calculation (Numeric)

Example 2: Add/Update FinalPrice

Example 3: EMI and Remaining

Example 4: Commission Calc

Example 5: String on Customer

Interactive Quiz - Master SQL Functions

Quick Revision Notes & Mnemonics

Key Terms & Processes - All Key

SQL Processes Step-by-Step

Process 1: Numeric Function Use (GST Calc)

Process 2: Add/Update Column

Process 3: String Extraction (Email)

Process 4: Commission Filter