Informatics Practices · Class 12 Optional

Ch 1Querying and SQL Functions — Class 12 Informatics Practices, concept-first.

In Class XI, you learned the fundamentals of databases — how to create them in MySQL, populate them with data, and retrieve that data using SQL queries. This chapter builds directly on that foundation.

Q&A

Concepts

Not available

Exam weightage

Start learning — read this chapter →

Key concepts

Hover a concept to preview it and jump to its most relevant Q&A.

SQL Rounding Functions

Imagine you're keeping a record of your monthly expenses. You spend ₹1,247.83 on groceries, ₹532.19 on transport, and ₹2,108.56 on rent.

Most relevant Q&A

Start with this concept →

Chapter contents

The NCERT structure, section by section. Open a section to see its questions, then read the concept-first solution.

1.1

Introduction

In Class XI, you learned the fundamentals of databases — how to create them in MySQL, populate them with data, and retrieve that data using SQL queries.

1.2

Functions in SQL

Functions are a core part of SQL, just as they are in programming languages. A function takes some input, performs a specific task, and returns a result.

1.2.1

Single Row Functions

Single row functions are also known as scalar functions. They are applied on a single value and return a single value — one output for every row they process.

(A)

Numeric Functions

3 Q

Numeric (math) functions take numbers in and give numbers out. The textbook works with three commonly used ones — POWER(), ROUND() and MOD() — whose syntax and worked outputs are listed in Table 1.5:

+Worked Examples1 question

Example 1.1In order to increase sales, a car dealer decides to offer customers the option to pay the total amount in 10 easy EMIs (equal monthly instal…Preview

+Worked Examples1 question

Example 1.2Using the SALE table of the CARSHOWROOM database: (a) Add a new column Commission to the SALE table. The column Commission should have a tot…Preview

+Activities1 question

Activity 1.1Using the table SALE of CARSHOWROOM database, write SQL queries for the following: (a) Display the InvoiceNo and commission value rounded of…Preview

(B)

String Functions

3 Q

String functions perform operations on alphanumeric data — changing case, extracting a substring, measuring length, locating one string inside another, and stripping unwanted spaces.

+Worked Examples1 question

Example 1.3Using the CUSTOMER relation (Table 1.2) to understand the working of string functions: (a) Display the customer name in lower case and the c…Preview

+Activities1 question

Activity 1.2Using the table INVENTORY from CARSHOWROOM database, write sql queries for the following: (a) Convert the CarMake to uppercase if its value…Preview

+Activities1 question

Activity 1.3Using the table EMPLOYEE from CARSHOWROOM database, write SQL queries for the following: (a) Display employee name and the last 2 characters…Preview

(C)

Date and Time Functions

3 Q

Date and time functions operate on date and time data — displaying the current date, extracting a date's day/month/year, or naming the day of the week.

+Worked Examples1 question

Example 1.4Using the EMPLOYEE table of the CARSHOWROOM database to illustrate the working of date and time functions: (a) Select the day, month number…Preview

+Activities1 question

Activity 1.4Using the table EMPLOYEE of CARSHOWROOM database, list the day of birth for all employees whose salary is more than 25000.Preview

+Think & Reflect1 question

Q1Can we use arithmetic operators (+, -. *, or /) on date functions?Preview

1.2.2

Aggregate Functions

2 Q

Aggregate functions are also called multiple-row functions. Unlike single-row functions that work on one row at a time, aggregate functions work on a whole set of records at once.

+Worked Examples1 question

Example 1.5Using the INVENTORY table of the CARSHOWROOM database and the aggregate functions in SQL: (a) Display the total number of records from the I…Preview

+Activities1 question

Activity 1.5(a) Find sum of Sale Price of the cars purchased by the customer having ID C0001 from table SALE. (b) Find the maximum and minimum commissio…Preview

1.3

GROUP BY in SQL

2 Q

The whole point of GROUP BY is to stop treating your table as a list of individual rows and start treating it as a collection of groups.

+Worked Examples1 question

Example 1.6Using the SALE table of the CARSHOWROOM database along with GROUP BY and the HAVING clause: (a) Display the number of cars purchased by each…Preview

+Activities1 question

Activity 1.6(a) List the total number of cars sold by each employee. (b) List the maximum sale made by each employee.Preview

1.4

Operations on Relations

Operations on relations let us combine or compare data from two tables. The textbook introduces three such operations: Union, Intersection, and Set Difference.

1.4.1

Union (U)

The Union operation combines the selected rows from two tables into a single result. If a row appears in both tables, it is shown only once in the output — duplicates are automatically removed.

1.4.2

Intersect (∩)

The INTERSECT operation, denoted by the symbol ∩, is used to find common tuples (rows) that appear in two tables.

1.4.3

Minus (-)

The MINUS operation, written with the minus sign (−), answers a simple question: Which rows exist in the first table but not in the second? It is also called set difference.

1.4.4

Cartesian Product

The Cartesian product is a set operation that combines every row from one relation with every row from another relation.

1.5

Using Two Relations in a Query

We have so far written every query using only one table — a single relation. But real databases store related data across multiple tables.

1.5.1

Cartesian Product on Two Tables

The Cartesian product is the operation that underlies every query involving more than one table. When you list multiple tables in the FROM clause separated by commas, the database engine first combine…

+Worked Examples1 question

Example 1.7Using the DANCE and MUSIC relations to understand how two relations are used together in a query: (a) Display all possible combinations of t…Preview

1.5.2

Join on Two Tables

A database is rarely useful if you can only look at one table at a time. Real questions — like "What is the price of a white shirt?" — require data from two different tables.

+Worked Examples1 question

Example 1.7 (JOIN)Let us create two tables UNIFORM (UCode, UName, UColor) and COST (UCode, Size, Price) in the SchoolUniform database. UCode is Primary Key in…Preview

Summary

- A Function is used to perform a particular task and return a value as a result. - Single row functions work on a single row to return a single value.

Exercises

+Show 5 questions5 questions

CBSE Sample Papers

Questions from official CBSE sample papers.

+Show 20 questions20 questions