Chapter 01 - Querying and SQL Functions

CBSE Class 12 Informatics Practices

1.1 Introduction

1.1.1 Context from Class XI (Why this chapter exists)

NCERT begins this chapter by reminding students that in Class XI, they have already studied:

What a database is
How data is stored in tables (relations)
How to:
- Create databases
- Create tables
- Insert records
- Retrieve records using SQL

At that stage, SQL usage was basic and row-oriented.

Example of what you typically did in Class XI:

SELECT * FROM STUDENT;

This kind of query:

Displays individual rows
Does not analyse data
Does not summarise information

So, although correct, it is limited in usefulness when databases become large.

1.1.2 Limitation of Simple SELECT Queries

NCERT implicitly highlights a problem:

Retrieving rows is not enough when we want to extract meaning from data.

Consider a table with thousands of rows. Just viewing rows does not answer questions like:

How many records exist?
What is the total or average value?
Which category appears most frequently?
Which customer made the maximum number of purchases?

Such questions require processing, not just retrieval.

1.1.3 Purpose of This Chapter

Therefore, this chapter introduces advanced SQL features that allow us to:

Process data
Summarise data
Group related records
Work with more than one table
Apply operations on relations

NCERT explicitly states that in this chapter, students will learn to:

Use single row functions
Use aggregate (multiple row) functions
Arrange records in ascending or descending order
Group records using GROUP BY
Query multiple tables together

All of these are essential for real-life database usage .

1.1.4 Shift in Thinking: From Rows to Information

A very important conceptual shift happens here.

Earlier (Class XI), SQL questions were like:

“Show all records.”

Now (Class XII), SQL questions are like:

“Show meaningful information derived from records.”

Example comparison:

Earlier Queries	Now Queries
Show all cars	Count cars
Show all prices	Average price
Show all sales	Total sales per customer
Show employee data	Highest salary

This shift is the foundation of this chapter.

1.1.5 Database Used in This Chapter (NCERT)

NCERT uses a sample database called:

CARSHOWROOM

This database is used throughout the chapter to explain:

Functions
GROUP BY
Relation operations
Multi-table queries

The important idea here is not the database itself, but:

How SQL extracts insights from it

You are expected to understand the logic, not memorise data.

1.1.6 Key Takeaway from Section 1.1

By the end of this introduction, NCERT wants you to clearly understand:

SQL is not only for displaying data
SQL is also for:
- Calculating
- Summarising
- Grouping
- Analysing
This chapter builds directly on Class XI SQL knowledge
The focus is now on decision-oriented queries

This prepares you for:

Advanced SQL questions in exams
Data handling in real-world applications

1.2 Functions in SQL

1.2.1 What is a Function in SQL? (NCERT definition explained)

NCERT defines a function as:

A function is used to perform a particular task and it returns a value as a result.

In SQL, a function:

Takes input (a column value, a constant, or an expression)
Performs an operation
Produces an output

This output can then be:

Displayed
Compared
Used in further processing

1.2.2 Why Functions are Needed in SQL

To understand why functions are necessary, consider the following situation.

Assume we have a table:

INVENTORY

CarID	Model	Price
C001	LXI	520000
C002	VXI	610000
C003	ZXI	750000

If you write:

SELECT Price FROM INVENTORY;

You only see values:

520000
610000
750000

But SQL users often want answers like:

What is the total price of all cars?
What is the average price?
What is the highest price?
What is the price after tax?

These cannot be answered by simple SELECT statements alone.

This is exactly why functions are introduced.

1.2.3 Classification of Functions (Very Important)

NCERT classifies SQL functions based on how many rows they work on.

There are two categories:

Single Row Functions
Aggregate (Multiple Row) Functions

This distinction is conceptual and exam-critical.

1.2.4 Single Row Functions

NCERT Definition

Single row functions:

Work on one row at a time
Return one result for each row

If a table has N rows, the output will also have N rows.

Key Characteristics of Single Row Functions

According to NCERT:

They operate on individual values
They return one result per row
They can be used in:
- SELECT
- WHERE
- ORDER BY

This means they are flexible and commonly used.

Categories of Single Row Functions (NCERT)

NCERT further divides single row functions into three groups:

Numeric (Math) Functions
String Functions
Date and Time Functions

We will study each group exactly in this order, just like the textbook.

1.2.4 (A) Numeric (Math) Functions

What Numeric Functions Do

Numeric functions:

Accept numeric values as input
Perform mathematical operations
Return numeric values

These functions are used for:

Calculations
Financial analysis
Rounding
Finding remainders

🔹 POWER(x, y)

This function calculates x raised to the power y.

Example 1: Constant values

SELECT POWER(2,3);

Output

Explanation:

2³ = 8
This query returns one value because it does not involve a table.

Example 2: Using a column value

Assume:

INVENTORY

CarID	Price
C001	520000
C002	610000

SELECT CarID, POWER(Price, 1)
FROM INVENTORY;

Output

CarID	POWER(Price,1)
C001	520000
C002	610000

Here:

Function runs once per row
Output rows = input rows This confirms it is a single row function.

🔹 ROUND(number, decimals)

This function rounds a number to the specified number of decimal places.

Example 1: Rounding with decimals

SELECT ROUND(2912.564, 1);

Output

2912.6

Explanation:

Rounded to one decimal place

Example 2: Rounding without decimals

SELECT ROUND(283.2);

Output

Explanation:

Default rounding is to the nearest integer

🔹 MOD(a, b)

This function returns the remainder after dividing a by b.

SELECT MOD(21, 2);

Output

Explanation:

21 ÷ 2 = 10 remainder 1

NCERT Applied Example: GST Calculation (Important)

NCERT gives a practical business example.

Suppose:

GST = 12%
We want to calculate GST for each car

INVENTORY

CarID	Price
C001	520000
C002	610000
C003	750000

Query

SELECT CarID, ROUND(Price * 0.12, 1) AS GST
FROM INVENTORY;

Output

CarID	GST
C001	62400.0
C002	73200.0
C003	90000.0

Explanation:

GST calculated row by row
Each row is independent
Output rows = input rows

This clearly demonstrates single row function behaviour.

1.2.4 (B) String Functions

What String Functions Do

String functions:

Work on character (text) data
Return:
- Character values, or
- Numeric values (such as length or position)

They are used to:

Format text
Extract parts of strings
Clean unwanted spaces

Example Table

CUSTOMER

CustID	Name
CU01	Amit Sharma
CU02	Neha Singh
CU03	Rahul Mehta

🔹 UCASE() / LCASE()

Convert text to uppercase or lowercase.

SELECT UCASE(Name)
FROM CUSTOMER;

Output

UCASE(Name)
AMIT SHARMA
NEHA SINGH
RAHUL MEHTA

Each row is processed individually, again confirming a single row function.

🔹 MID(string, position, length)

Extracts a substring.

SELECT MID("INFORMATICS", 3, 4);

Output

FORM

Explanation:

Start at position 3
Extract 4 characters

🔹 LENGTH(string)

Returns number of characters.

SELECT Name, LENGTH(Name)
FROM CUSTOMER;

Output

Name	LENGTH
Amit Sharma	12
Neha Singh	10
Rahul Mehta	12

🔹 LEFT() and RIGHT()

SELECT LEFT(Name,4), RIGHT(Name,5)
FROM CUSTOMER;

This extracts characters from:

Left side
Right side of a string

⏸️ Pause Point (Important)

We have now fully and sequentially covered:

✔ Definition of SQL functions ✔ Why functions are required ✔ Classification of functions ✔ Single row functions • Numeric functions • String functions

1.2.4 © Date and Time Functions

1.2.4©.1 Why Date and Time Functions are Needed

In real databases, many attributes are of DATE or DATETIME type, such as:

Date of birth
Date of joining
Invoice date
Order date

Storing a date is not enough. We often need to:

Extract day, month, or year
Find day name
Display dates in a readable form
Compare dates

Date & Time functions allow us to extract and manipulate components of a date.

1.2.4©.2 Sample Table Used (NCERT Style)

NCERT uses the EMPLOYEE table from the CARSHOWROOM database.

EMPLOYEE

EmpID	EmpName	DOJ
E001	Ramesh	2017-12-12
E002	Sanjay	2016-06-05
E003	Zohar	1999-01-08
E004	Arpit	2010-12-02
E005	Mayank	2013-10-23

Here:

DOJ = Date of Joining
Data type = DATE

1.2.4©.3 NOW()

Purpose

Returns the current system date and time.

Query

SELECT NOW();

Output (example)

2019-07-11 19:41:17

📌 Important points:

Output includes date + time
Value depends on the system clock
Used when current timestamp is needed

1.2.4©.4 DATE()

Purpose

Extracts only the date part from a date-time value.

Query

SELECT DATE(NOW());

Output

2019-07-11

Explanation:

NOW() → date + time
DATE() → removes time part

1.2.4©.5 DAY(date)

Purpose

Returns the day of the month (numeric).

Query

SELECT DAY("2003-03-24");

Output

1.2.4©.6 MONTH(date)

Purpose

Returns the month number (1–12).

Query

SELECT MONTH("2003-11-28");

Output

1.2.4©.7 MONTHNAME(date)

Purpose

Returns the name of the month.

Query

SELECT MONTHNAME("2003-11-28");

Output

November

This function is often used to make output more readable.

1.2.4©.8 YEAR(date)

Purpose

Returns the year from a date.

Query

SELECT YEAR("2003-10-03");

Output

1.2.4©.9 DAYNAME(date)

Purpose

Returns the name of the day.

Query

SELECT DAYNAME("2019-07-11");

Output

Thursday

This is useful for:

Attendance systems
Payroll calculations
Weekly reports

1.2.4©.10 Applying Date Functions on Table Data (NCERT Example)

NCERT now applies these functions to actual table rows, which is very important.

Query: Extract day, month, and year of joining of all employees

SELECT DAY(DOJ), MONTH(DOJ), YEAR(DOJ)
FROM EMPLOYEE;

Output

DAY(DOJ)	MONTH(DOJ)	YEAR(DOJ)
12	12	2017
5	6	2016
8	1	1999
2	12	2010
23	10	2013

Explanation:

Function applied row by row
Each row produces one result
Output rows = input rows

Hence, these are single row functions.

1.2.4©.11 NCERT Formatting Example (Conceptual Understanding)

NCERT gives an example like:

If the date of joining is not a Sunday, display it in the format "Wednesday, 26, November, 1979"

This example shows:

Combining multiple date functions
Creating human-readable output

Conceptually, this uses:

DAYNAME()
DAY()
MONTHNAME()
YEAR()

Such formatting questions are often asked as:

Explain the output
Identify the functions used

1.2.4©.12 Key Observations (NCERT Intent)

From this section, NCERT wants you to understand:

Date & Time functions:
- Extract parts of a date
- Do not change the original data
They are single row functions
Each row is processed independently
Output rows = input rows

1.2.5 Aggregate (Multiple Row) Functions

1.2.5.1 Meaning of Aggregate Functions (NCERT explanation)

NCERT introduces aggregate functions as functions that:

Operate on a set of rows
Return a single value
Are used to summarise data

This is the key difference from single row functions.

Single row functions → one result per row Aggregate functions → one result per group (or whole table)

1.2.5.2 Why Aggregate Functions are Required

Consider again the INVENTORY table.

INVENTORY

CarID	Model	FuelType	Price
C001	LXI	Petrol	520000
C002	VXI	Petrol	610000
C003	VXI	Diesel	680000
C004	ZXI	Petrol	750000
C005	LXI	Diesel	540000

If you run:

SELECT Price FROM INVENTORY;

You get five rows.

But if you want:

Total value of all cars
Average car price
Highest or lowest price
Number of cars

You do not want five rows. You want one summarised value.

This is exactly what aggregate functions do.

1.2.5.3 Characteristics of Aggregate Functions

According to NCERT:

They work on multiple rows
They return only one value
They are used only in the SELECT clause
They ignore NULL values (except COUNT(*))

These points are often tested in theory questions.

Types of Aggregate Functions (NCERT)

NCERT discusses the following aggregate functions:

SUM()
AVG()
MAX()
MIN()
COUNT()
COUNT(*)
DISTINCT (used with COUNT)

We will study them one by one.

1.2.5.4 SUM()

Purpose

Returns the total of values in a numeric column.

Example 1: Total price of all cars

SELECT SUM(Price)
FROM INVENTORY;

Output

Explanation

All Price values are added
Five rows → one result
Hence, aggregate function

Example 2: SUM with condition

SELECT SUM(Price)
FROM INVENTORY
WHERE FuelType = 'Petrol';

Output

Explanation:

Only Petrol cars are selected
SUM is applied after filtering
This shows aggregate functions can work with WHERE

1.2.5.5 AVG()

Purpose

Returns the average (mean) of values.

Example: Average price of all cars

SELECT AVG(Price)
FROM INVENTORY;

Output

Explanation:

Total ÷ number of rows
Returned as a single value

Example: Average price of LXI models

SELECT AVG(Price)
FROM INVENTORY
WHERE Model = 'LXI';

1.2.5.6 MAX() and MIN()

Purpose

MAX() → highest value
MIN() → lowest value

Example: Highest and lowest car price

SELECT MAX(Price), MIN(Price)
FROM INVENTORY;

Output

MAX(Price)	MIN(Price)
750000	520000

Explanation:

MAX finds the largest value
MIN finds the smallest value
Both return one row

Example: Most expensive Diesel car

SELECT MAX(Price)
FROM INVENTORY
WHERE FuelType = 'Diesel';

1.2.5.7 COUNT(column)

Purpose

Counts non-NULL values in a column.

Example: Number of cars in inventory

SELECT COUNT(CarID)
FROM INVENTORY;

Output

Explanation:

Counts rows where CarID is not NULL
Since CarID is a primary key, result = number of rows

Example: Number of Petrol cars

SELECT COUNT(CarID)
FROM INVENTORY
WHERE FuelType = 'Petrol';

1.2.5.8 COUNT(*)

Purpose

Counts number of rows, including rows with NULL values.

Example

SELECT COUNT(*)
FROM INVENTORY;

Output

COUNT(column) vs COUNT(*): NCERT Concept

COUNT(column)	COUNT(*)
Ignores NULL	Includes NULL
Counts values	Counts rows

This distinction is frequently asked in exams.

1.2.5.9 DISTINCT with COUNT()

Purpose

Counts unique values only.

Example: Number of different models

SELECT COUNT(DISTINCT Model)
FROM INVENTORY;

Output

Explanation:

Models are: LXI, VXI, ZXI
Duplicates removed before counting

1.2.5.10 Important Rule (NCERT Highlight)

Aggregate functions cannot be mixed with normal columns in SELECT unless GROUP BY is used.

❌ Incorrect:

SELECT Model, COUNT(*)
FROM INVENTORY;

✔ Correct (will be studied in next section):

SELECT Model, COUNT(*)
FROM INVENTORY
GROUP BY Model;

This rule leads directly to Section 1.3 (GROUP BY).

1.2.5.11 Key Takeaways from Aggregate Functions

NCERT expects you to clearly understand:

Aggregate functions:
- Work on multiple rows
- Return one value
Common aggregate functions:
- SUM, AVG, MAX, MIN, COUNT
COUNT(*) vs COUNT(column)
Use of DISTINCT
Aggregate functions prepare data for GROUP BY

1.3 GROUP BY in SQL

1.3.1 Why GROUP BY is Required (NCERT Motivation)

At the end of Section 1.2, we saw an important limitation:

Aggregate functions such as COUNT(), SUM(), AVG() return only one value.

Example:

SELECT COUNT(*) FROM INVENTORY;

Output:

This tells us:

Total number of cars = 5

But very often, we want one aggregate value per category, not for the entire table.

For example:

Number of cars per model
Average price per fuel type
Total sales per customer

These kinds of questions cannot be answered by aggregate functions alone.

This is exactly why GROUP BY is introduced.

1.3.2 Meaning of GROUP BY (NCERT Definition Explained)

NCERT explains GROUP BY as a clause that:

Groups rows having the same values in specified columns into summary rows.

In simpler words:

GROUP BY collects similar rows together
Aggregate functions are then applied to each group separately

1.3.3 Basic Syntax of GROUP BY

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

Important observations:

GROUP BY always works with aggregate functions
The column in GROUP BY must also appear in SELECT

1.3.4 Table Used for Explanation (NCERT Style)

We continue with the INVENTORY table.

INVENTORY

CarID	Model	FuelType	Price
C001	LXI	Petrol	520000
C002	VXI	Petrol	610000
C003	VXI	Diesel	680000
C004	ZXI	Petrol	750000
C005	LXI	Diesel	540000

1.3.5 GROUP BY with COUNT()

Question (NCERT-style):

Find the number of cars for each model.

Query

SELECT Model, COUNT(*)
FROM INVENTORY
GROUP BY Model;

Output

Model	COUNT(*)
LXI	2
VXI	2
ZXI	1

Explanation (Very Important)

Step-by-step execution:

Rows are grouped based on Model
- Group 1 → LXI
- Group 2 → VXI
- Group 3 → ZXI
COUNT(*) is applied separately to each group
Output shows one row per group

This is the core idea of GROUP BY.

1.3.6 GROUP BY with SUM()

Question:

Find the total price of cars for each fuel type.

Query

SELECT FuelType, SUM(Price)
FROM INVENTORY
GROUP BY FuelType;

Output

FuelType	SUM(Price)
Petrol	1880000
Diesel	1220000

Explanation

All Petrol rows are grouped together
All Diesel rows are grouped together
SUM is applied to each group independently

Again:

Input rows = 5
Output rows = 2 (number of groups)

1.3.7 GROUP BY with AVG()

Question:

Find the average price of cars for each model.

Query

SELECT Model, AVG(Price)
FROM INVENTORY
GROUP BY Model;

Output

Model	AVG(Price)
LXI	530000
VXI	645000
ZXI	750000

Explanation

LXI → (520000 + 540000) / 2
VXI → (610000 + 680000) / 2
ZXI → only one value

This shows how AVG() behaves with different group sizes.

1.3.8 GROUP BY with Multiple Rows per Group (SALE Table)

NCERT now shifts to a transaction table.

SALE

InvoiceNo	CarID	CustID	PaymentMode
I001	C001	CU01	Credit Card
I002	C002	CU01	Cash
I003	C003	CU02	Credit Card
I004	C004	CU03	Online
I005	C005	CU02	Credit Card

Question:

Find the number of cars purchased by each customer.

Query

SELECT CustID, COUNT(*)
FROM SALE
GROUP BY CustID;

Output

CustID	COUNT(*)
CU01	2
CU02	2
CU03	1

Explanation

CU01 → 2 purchases
CU02 → 2 purchases
CU03 → 1 purchase

This is a very common board-style question.

1.3.9 WHERE vs GROUP BY (Execution Order – Conceptual)

NCERT implicitly expects you to understand execution order.

Important Rule:

WHERE filters rows
GROUP BY groups remaining rows
Aggregate functions are applied after grouping

Example:

Count Petrol cars per model

SELECT Model, COUNT(*)
FROM INVENTORY
WHERE FuelType = 'Petrol'
GROUP BY Model;

Explanation:

WHERE removes Diesel rows
Remaining rows are grouped by Model
COUNT is applied to each group

1.3.10 HAVING Clause (Very Important Subsection)

Why HAVING is Needed

Once rows are grouped, we cannot use WHERE to filter groups.

To filter grouped results, SQL provides HAVING.

Difference Between WHERE and HAVING (NCERT Focus)

WHERE	HAVING
Filters rows	Filters groups
Used before GROUP BY	Used after GROUP BY
Cannot use aggregate functions	Uses aggregate functions

Example:

Display customers who purchased more than one car.

Query

SELECT CustID, COUNT(*)
FROM SALE
GROUP BY CustID
HAVING COUNT(*) > 1;

Output

CustID	COUNT(*)
CU01	2
CU02	2

Explanation

GROUP BY creates groups per CustID
HAVING filters only those groups where COUNT > 1

This is one of the most frequently asked SQL questions.

1.3.11 Important Rules of GROUP BY (NCERT Expectations)

Every column in SELECT (except aggregate functions) must appear in GROUP BY
Aggregate functions cannot be used in WHERE
HAVING works only with grouped data
Output rows = number of groups

Key Takeaways from Section 1.3

By the end of this section, NCERT expects you to understand:

Why GROUP BY is required
How GROUP BY forms groups
Use of GROUP BY with:
- COUNT
- SUM
- AVG
Difference between WHERE and HAVING
Filtering grouped results using HAVING

1.4 Operations on Relations

1.4.1 Meaning of “Operations on Relations”

In SQL, a relation means a table.

So, operations on relations means:

Operations performed between two tables to produce a new table (result set).

NCERT connects this idea with set theory, where:

Tables are treated as sets of rows
Operations are performed to:
- Combine
- Compare
- Relate data

This section introduces set-based operations, not row-by-row processing.

1.4.2 Conditions Required for Operations on Relations

NCERT clearly states that not all tables can be combined.

For most relation operations (like UNION), the following conditions must be satisfied:

Both relations must have the same number of columns
Corresponding columns must have the same data type
Columns must be in the same order

If any of these conditions are violated, the operation is invalid.

1.4.3 UNION Operation

Meaning of UNION

The UNION operation:

Combines rows from two relations
Removes duplicate rows
Produces a single result set

In set terminology:

UNION = elements present in either set (without repetition)

Example Tables (NCERT-style)

Table: DANCE

Name
Asha
Ravi
Neha

Table: MUSIC

Name
Ravi
Meena
Neha

Query

SELECT Name FROM DANCE
UNION
SELECT Name FROM MUSIC;

Output

Name
Asha
Ravi
Neha
Meena

Explanation

Rows from both tables are combined
Duplicate values (Ravi, Neha) appear only once
Final result behaves like a set

Important NCERT Rule

UNION automatically removes duplicate rows.

If duplicates are required, NCERT mentions UNION ALL (conceptual awareness).

1.4.4 INTERSECTION Operation

Meaning of INTERSECTION

The INTERSECTION operation returns:

Only those rows which are common in both relations

In set terminology:

INTERSECTION = elements common to both sets

Using the same tables:

DANCE

Name
Asha
Ravi
Neha

MUSIC

Name
Ravi
Meena
Neha

Conceptual Result of INTERSECTION

Name
Ravi
Neha

NCERT Clarification (Very Important)

NCERT explains that:

MySQL does not provide INTERSECT as a direct command
INTERSECTION is taught as a conceptual operation
Students must understand what the result means, even if syntax is not used

In exams, questions are usually:

“What will be the result of INTERSECTION?”
“Which tuples will appear?”

1.4.5 SET DIFFERENCE Operation

Meaning of SET DIFFERENCE

SET DIFFERENCE returns:

Rows that are present in the first relation
But not present in the second relation

In set terminology:

A − B = elements in A but not in B

Example

DANCE

Name
Asha
Ravi
Neha

MUSIC

Name
Ravi
Meena
Neha

Conceptual Result:

DANCE − MUSIC

Name
Asha

Important NCERT Note

Like INTERSECTION, SET DIFFERENCE is also:
- A conceptual operation
- Explained using examples
Students must be able to:
- Identify which rows remain
- Explain reasoning

1.4.6 CARTESIAN PRODUCT

Meaning of Cartesian Product

The Cartesian Product of two relations:

Combines each row of the first table
With every row of the second table

This produces all possible combinations of rows.

Example Tables

STUDENT

AdmNo	Name
101	Rahul
102	Anita

STREAM

StCode	Stream
S01	Science
S02	Commerce
S03	Humanities

Query

SELECT * FROM STUDENT, STREAM;

Output (Conceptual)

AdmNo	Name	StCode	Stream
101	Rahul	S01	Science
101	Rahul	S02	Commerce
101	Rahul	S03	Humanities
102	Anita	S01	Science
102	Anita	S02	Commerce
102	Anita	S03	Humanities

Cardinality and Degree (Very Important)

NCERT emphasises these two terms:

Cardinality

Number of rows in a table

For Cartesian Product:

Rows = rows in table1 × rows in table2

Here:

STUDENT → 2 rows
STREAM → 3 rows ➡ Result → 6 rows

Degree

Number of columns in a table

For Cartesian Product:

Columns = columns of table1 + columns of table2

Here:

STUDENT → 2 columns
STREAM → 2 columns ➡ Result → 4 columns

These calculations are frequently asked in exams.

1.4.7 Key Observations from Operations on Relations

NCERT expects students to understand:

UNION:
- Combines rows
- Removes duplicates
INTERSECTION:
- Common rows only
- Conceptual in MySQL
SET DIFFERENCE:
- Rows in one table but not the other
CARTESIAN PRODUCT:
- All row combinations
- Leads to very large result sets

1.4.8 Exam-Oriented Summary for Section 1.4

Conditions for UNION are mandatory
INTERSECTION & DIFFERENCE are usually theoretical
Cartesian Product questions often ask:
- Number of rows
- Number of columns
Cartesian Product without condition is usually undesirable in real life → This leads naturally to the next section

Section 1.4 Completed

We have now fully and sequentially covered:

✔ Meaning of operations on relations ✔ UNION ✔ INTERSECTION ✔ SET DIFFERENCE ✔ CARTESIAN PRODUCT ✔ Cardinality & Degree

1.5 Using Two Relations in a Query

1.5.1 Why Do We Need to Use Two Relations Together?

Up to now, we have:

Used functions on a single table
Used GROUP BY within a single table
Studied Cartesian Product as a raw combination of two tables

However, in real databases, information is not stored in one big table.

Instead:

Data is distributed across multiple related tables
Tables are connected using common columns (keys)

This design:

Avoids redundancy
Improves consistency
Makes databases scalable

Therefore, to answer meaningful questions, SQL must be able to:

Retrieve data from more than one relation at the same time

This is the purpose of using two relations in a query.

1.5.2 Understanding the Relationship Between Tables

NCERT uses the idea that:

One table stores primary information
Another table stores related information
A common column links them

This common column is usually:

A primary key in one table
A foreign key in the other

1.5.3 Tables Used (NCERT Style Example)

Let us take the same STUDENT and STREAM tables used by NCERT.

STUDENT

AdmNo	Name	StCode
101	Rahul	S01
102	Anita	S02
103	Mohit	S03

Here:

StCode represents the stream code
It does not directly tell us the stream name

STREAM

StCode	Stream
S01	Science
S02	Commerce
S03	Humanities

Here:

StCode is the primary key
Stream is the stream name

1.5.4 The Problem with a Single Table Query

Suppose we want to answer this question:

Display the name of each student along with the stream name.

If we query only the STUDENT table:

SELECT Name, StCode FROM STUDENT;

Output:

Name	StCode
Rahul	S01
Anita	S02
Mohit	S03

This is not user-friendly, because:

S01, S02, S03 are just codes
The actual stream names are stored elsewhere

Hence, we must access both tables together.

1.5.5 Cartesian Product: The First Step (NCERT Logic)

When two tables are used in the FROM clause:

SELECT * FROM STUDENT, STREAM;

SQL performs a Cartesian Product by default.

This means:

Every row of STUDENT is combined with every row of STREAM

Result:

STUDENT → 3 rows
STREAM → 3 rows ➡ Output → 3 × 3 = 9 rows

Most of these combinations are meaningless.

NCERT clearly states that:

Cartesian product alone is usually not useful.

So we must restrict it.

1.5.6 Restricting Cartesian Product Using a Condition

To extract meaningful combinations, we add a condition that links the tables.

This condition is written using:

WHERE clause
A common column

Correct Query (NCERT Style)

SELECT Name, Stream
FROM STUDENT, STREAM
WHERE STUDENT.StCode = STREAM.StCode;

Output

Name	Stream
Rahul	Science
Anita	Commerce
Mohit	Humanities

Explanation (Very Important)

Step-by-step execution:

SQL first creates the Cartesian Product
The WHERE condition:
```
STUDENT.StCode = STREAM.StCode
```

filters only matching rows 3. Only valid student–stream pairs remain 4. Final output is meaningful and readable

This is conceptually known as an Equi-Join, though NCERT does not emphasise join terminology here.

1.5.7 Another NCERT-Style Example (CARSHOWROOM Context)

Let us now use the SALE and INVENTORY tables.

INVENTORY

CarID	Model	Price
C001	LXI	520000
C002	VXI	610000
C003	ZXI	750000

SALE

InvoiceNo	CarID	CustID
I001	C001	CU01
I002	C002	CU02
I003	C003	CU01

Question:

Display Invoice Number, Model, and Price of each car sold.

Query

SELECT InvoiceNo, Model, Price
FROM SALE, INVENTORY
WHERE SALE.CarID = INVENTORY.CarID;

Output

InvoiceNo	Model	Price
I001	LXI	520000
I002	VXI	610000
I003	ZXI	750000

Explanation

SALE.CarID matches with INVENTORY.CarID
Information from both tables is combined
Without using two relations, this output is not possible

1.5.8 Importance of WHERE Clause in Multi-Table Queries

NCERT strongly point (implicit but critical):

If the linking condition is missing:
- Output becomes a Cartesian Product
- Result is meaningless
Correct WHERE condition:
- Connects tables logically
- Produces valid results

This idea is often tested indirectly in exams.

1.5.9 Common Mistakes to Avoid (Exam Focus)

Forgetting the WHERE condition
Using wrong column for matching
Confusing column names when both tables have same column names
Not qualifying column names (Table.Column) when needed

1.5.10 Conceptual Takeaways from Section 1.5

NCERT expects students to understand:

Data is spread across multiple relations
Cartesian Product is the base operation
Meaningful results require:
- Matching rows using a common column
WHERE clause controls which combinations are selected
Multi-table queries are essential for real-world databases

Page last updated: 17-Mar-2026 18:38 IST

Document Version: v19