Consider the following tables PARTICIPANT and ACTIVITY : Table : PARTICIPANT | ADMNO | NAME | HOUSE | ACTIVITYCODE | | --- | --- | --- | --- | | 6473 | Kapil Shah | Gandhi | A105 | | 7134 | Joy Mathew | Bose | A101 | | 8786 | Saba Arora | Gandhi | A102 | | 6477 | Kapil Shah | Bose | A101 | | 7658 | Faizal Ahmed | Bhagat | A104 | Table : ACTIVITY | ACTIVITYCODE | ACTIVITYNAME | POINTS | | --- | --- | --- | | A101 | Running | 200 | | A102 | Hopping bag | 300 | | A103 | Skipping | 200 | | A104 | Bean bag | 250 | | A105 | Obstacle | 350 | Write command in SQL for the following : (iii) To display Names of Participants along with Activity Codes and Activity Names for only those participants who are taking part in Activities that have ‘bag’ in their Activity Names and Points of activity are above 250.

Consider the following tables PARTICIPANT and ACTIVITY — CBSE Class 12 Informatics Practices 2019 PYQ

Question

Consider the following tables PARTICIPANT and ACTIVITY :

Table : PARTICIPANT

ADMNO	NAME	HOUSE	ACTIVITYCODE
6473	Kapil Shah	Gandhi	A105
7134	Joy Mathew	Bose	A101
8786	Saba Arora	Gandhi	A102
6477	Kapil Shah	Bose	A101
7658	Faizal Ahmed	Bhagat	A104

Table : ACTIVITY

ACTIVITYCODE	ACTIVITYNAME	POINTS
A101	Running	200
A102	Hopping bag	300
A103	Skipping	200
A104	Bean bag	250
A105	Obstacle	350

Write command in SQL for the following : (iii) To display Names of Participants along with Activity Codes and Activity Names for only those participants who are taking part in Activities that have ‘bag’ in their Activity Names and Points of activity are above 250.

CBSECBSE Class XII Board 2019Subjective· 2mImportance★★★★★

🔒 Locked · start free trial →

You're viewing a preview — the full solution, concept, methods & PYQ mapping are locked.

Start your 14-day free trial to unlock the full solution →

Concept understanding — Relational Algebra Operations

Relational Algebra Operations: A First Look

Think of a relational database as a collection of neat, rectangular tables. Each table has rows (records) and columns (attributes). Now, suppose you want to ask questions of this data — "Which customers live in Delhi?" or "Show me all orders placed last month." Relational algebra is the set of basic operations you use to answer such questions. It is the language of queries at the most fundamental level.

You do not need to write code or do math. You only need to understand what each operation does to a table — like a set of tools in a toolbox.

The Core Operations

There are eight classic operations. They fall into two groups: those that work on one table at a time, and those that combine two tables.

Operations on a Single Table

Select (also called Restrict) — This operation picks certain rows from a table based on a condition. For example, from a table of students, you might select only those rows where the city is "Mumbai". The result is a smaller table with the same columns but fewer rows.

Project — This operation picks certain columns from a table. For example, from a student table with columns Roll No, Name, City, and Marks, you might project only Name and City. The result is a table with fewer columns. Duplicate rows are automatically removed.

Rename — This operation simply gives a new name to the resulting table or to its columns. It is useful when you need to refer to the same table more than once in a query, or when you want clearer column headings.

Note

Select and Project are the two most frequently used operations. Select narrows down rows; Project narrows down columns. Together they let you extract exactly the slice of data you need.

Operations That Combine Two Tables

Union — This combines two tables that have the same structure (same number of columns and compatible data types). The result contains all rows that appear in either table, with duplicates removed. Think of it as "add the rows of one table to the rows of another, but keep only unique ones."

Set Difference — This gives you rows that are in the first table but not in the second. For example, "Which students are enrolled in Course A but not in Course B?"

Intersection — This gives you rows that appear in both tables. For example, "Which customers have bought both a laptop and a printer?"

Cartesian Product — This pairs every row of the first table with every row of the second table. If the first table has 10 rows and the second has 5, the result has 50 rows. This operation is rarely used alone — it is the foundation for the most powerful operation of all.

Join — This is the heart of relational algebra. A join combines rows from two tables based on a related column between them. For example, you have a Customers table and an Orders table. The join operation matches each order to the customer who placed it, using the Customer ID column that appears in both tables. The result is a single table with all the customer details alongside their orders.

Important

The Join operation is what makes relational databases relational. Without it, data in separate tables would remain isolated. Joins let you connect information across tables — customers to orders, students to courses, products to suppliers — and answer questions that span multiple pieces of data.

Why Relational Algebra Matters …

Start your 14-day free trial to unlock the full solution →

To display the names of participants along with their activity codes and activity names based on the given conditions, we must combine information from both the PARTICIPANT and ACTIVITY tables. This requires an INNER JOIN operation, linking the tables using the common ACTIVITYCODE column.

After joining, we apply two filtering conditions:

The ACTIVITYNAME must contain the substring 'bag'. This is achieved using the LIKE operator with the wildcard character % on both sides of 'bag'.
The POINTS for the activity must be greater than 250.

Finally, we select the NAME from the PARTICIPANT table, and ACTIVITYCODE and ACTIVITYNAME from the ACTIVITY table to present the desired output.

SELECT P.NAME, P.ACTIVITYCODE, A.ACTIVITYNAME
FROM PARTICIPANT P …

Start your 14-day free trial to unlock the full solution →

To display participant names, activity codes, and activity names for specific activities, we must join the PARTICIPANT and ACTIVITY tables and then filter the results based on the activity name containing 'bag' and having points above 250.

When working with relational databases, information is often spread across multiple tables to maintain efficiency and avoid redundancy. To answer questions that require data from more than one table, we need to establish a link between them. In this scenario, we want to see participant names alongside details of their activities, which means we need to combine data from the PARTICIPANT table and the ACTIVITY table.

The key to linking these two tables is the ACTIVITYCODE column. Both tables contain this column: ACTIVITYCODE in the ACTIVITY table uniquely identifies each activity (acting as its primary key), and ACTIVITYCODE in the PARTICIPANT table indicates which activity a participant is involved in (acting as a foreign key). This common column allows us to join the tables and match participants to their respective activities.

Note

A primary key uniquely identifies each record in a table, while a foreign key in one table refers to the primary key in another table, establishing a relationship between them. Here, ACTIVITYCODE in ACTIVITY is likely a primary key, and in PARTICIPANT, it's a foreign key.

Once the tables are joined, we need to filter the combined data according to the given conditions. The problem specifies two criteria for the activities:

The ACTIVITYNAME must contain the word 'bag'. This requires a pattern-matching operator, LIKE, combined with wildcard characters. The % wildcard matches any sequence of zero or more characters, so '%bag%' will find 'Hopping bag', 'Bean bag', or any other activity name that includes 'bag'.
The POINTS for the activity must be greater than 250. This is a straightforward numerical comparison using the > operator.

Both these conditions must be true for an activity to be included in the result, so they will be combined using the AND logical operator in the WHERE clause. Finally, we select only the NAME from the PARTICIPANT table and the ACTIVITYCODE and ACTIVITYNAME from the ACTIVITY table, as requested.

The SQL command to achieve this is:

SELECT P.NAME, A.ACTIVITYCODE, A.ACTIVITYNAME
FROM PARTICIPANT AS P
JOIN ACTIVITY AS A ON P.ACTIVITYCODE = A.ACTIVITYCODE
WHERE A.ACTIVITYNAME LIKE '%bag%' AND A.POINTS > 250;
``` …

Start your 14-day free trial to unlock the full solution →

Same / Similar Concept — real previous-year questions on the same or a closely similar concept, not this exact question.

CBSE 2026Set 91/41 markMCQ
$Q. A relation in MySQL database consists of 2 tuples and 3 attributes. If 2 attributes are deleted and 4 tuples are added, what will be the cardinality of the relation ? (A) 4 (B) 5 (C) 6 (D) 7$
›Reveal solutionSolution

Cardinality refers to the number of tuples (rows) in a relation; deleting attributes affects only the structure, not the row count, so starting with 2 tuples and adding 4 more gives a cardinality of 6.

Understanding what happens to a relation when we modify its structure or content requires clarity on two fundamental terms: cardinality and degree. Cardinality is the number of tuples (rows) in a relation—essentially, how many records the table holds. Degree, on the other hand, is the number of attributes (columns)—the structure of the relation itself.

The question begins with a relation containing 2 tuples and 3 attributes. Picture a small table with three columns and two rows of data. Now, two operations are performed: first, 2 attributes are deleted, and second, 4 tuples are added.

Deleting attributes changes the degree of the relation. If we remove 2 out of 3 attributes, we are left with a single-column table. This operation affects the structure—the width of the table—but it does not touch the number of rows. The 2 tuples that were already present remain in the relation; they simply have fewer fields now. …
Start your 14-day free trial to see the full solution →
CBSE 2025Set 91/41 mark
$Q. State True or False : If table A has 6 rows and 3 columns, and table B has 5 rows and 2 columns, the Cartesian product of A and B will have 30 rows and 5 columns.$
›Reveal solutionSolution

The statement is True — the Cartesian product of two tables combines every row of one with every row of the other, so the number of rows multiplies and the number of columns adds.

Let's think about what the Cartesian product actually does. In relational algebra, when you take the Cartesian product of two tables (say A and B), you are pairing each row of A with every row of B. The result is a new table that contains all possible combinations of rows from the two original tables.

The number of rows in the product is the product of the row counts of A and B. Here, A has 6 rows and B has 5 rows, so the result has 6 x 5 = 30 rows.

What about the columns? The Cartesian product does not merge or eliminate any columns — it simply places all columns from A side by side with all columns from B. So the total number of columns is the sum of the column counts of A and B. A has 3 columns, B has 2 columns, so the result has 3 + 2 = 5 columns.

Note

This is exactly how the Cartesian product works in relational algebra — it is a cross join, not a natural join. No matching or merging of columns happens; every column from both tables appears in the output. …

Start your 14-day free trial to see the full solution →

🎓Unlock everything free for 14 days

Unlock everything free for 14 days