Volume 2: Database Query using SQL

Structured Query Language (SQL) and Relational Database Management Systems (RDBMS) constitute 25 Marks in the CBSE Class 12 Information Practices board examination.

---

1. Unit Weightage & Strategic Importance

pie title CBSE IP (065) Unit Weightage
    "Database Query using SQL (Vol 2)" : 25
    "Data Handling with Pandas (Vol 1)" : 25
    "Computer Networks (Vol 3)" : 10
    "Societal Impacts (Vol 3)" : 10

Chapter	Key Topics	CBSE Marks Range	Question Types
1. Single-Row Functions	Math (POWER, ROUND, MOD), Text (MID, LENGTH, INSTR), Date (NOW, MONTHNAME, DAYNAME)	8 - 10 Marks	Direct function evaluation, Output prediction, Query formulation
2. Aggregate Functions & Grouping	COUNT(*), COUNT(col), SUM, AVG, MAX, MIN, GROUP BY, HAVING vs WHERE	7 - 9 Marks	Group summary queries, Output prediction with NULL handling
3. Table Joins & DDL/DML Foundations	Cartesian Product, Equi-Join, Natural Join, Degree & Cardinality, ALTER, UPDATE, DELETE	6 - 8 Marks	Multi-table query formulation, Degree & Cardinality calculations

---

2. RDBMS Theoretical Foundations

A Relational Database stores data in tables (relations) consisting of columns (attributes) and rows (tuples).

Core Terminology

• Relation (Table): A collection of data organized in rows and columns.
• Tuple (Row / Record): A single entity row representing one record in a table.
• Attribute (Column / Field): A named column representing a specific property.
• Degree: The total number of columns (attributes) in a relation.
• Cardinality: The total number of rows (tuples) in a relation.
• Primary Key: An attribute (or set of attributes) that uniquely identifies each tuple in a relation. Must be unique and cannot contain NULL.
• Candidate Key: Any attribute or minimal combination of attributes that is capable of becoming a Primary Key.
• Alternate Key: A candidate key that is not chosen as the Primary Key ($\text{Alternate Keys}=\text{Candidate Keys}-\text{Primary Key}$).
• Foreign Key: An attribute in a table that references the Primary Key of another table, establishing a link and maintaining referential integrity.

---

3. Mathematical Principles of Relations

Degree and Cardinality Rules

Let Relation $R_1$ have Degree $D_1$ and Cardinality $C_1$, and Relation $R_2$ have Degree $D_2$ and Cardinality $C_2$.

1. Cartesian Product ($R_1\times R_2$):

   $$\text{Degree}(R_1\times R_2)=D_1+D_2$$$$\text{Cardinality}(R_1\times R_2)=C_1\times C_2$$

2. Equi-Join ($R_1\bowtie R_2$):

   $$\text{Degree}(R_1\bowtie R_2)=D_1+D_2$$$$\text{Cardinality}(R_1\bowtie R_2)\le C_1\times C_2$$

---

4. Volume Chapters & Progression

1. Single-Row Math, Text & Date Functions: Complete master manual for POWER, ROUND, MOD, UCASE, LCASE, MID, SUBSTRING, LENGTH, INSTR, TRIM, NOW, SYSDATE, DATE, MONTH, YEAR, DAYNAME.
2. Aggregate Functions & GROUP BY / HAVING: Deep dive into group-level computations, NULL handling differences, and the strict rules governing WHERE versus HAVING.
3. Table Joins (Equi/Natural) & DDL/DML Review: Two-table join syntax, foreign keys, Cartesian products, and comprehensive DDL (CREATE, ALTER, DROP) vs DML (INSERT, UPDATE, DELETE, SELECT) commands.