GATE DS&AI 2024 | Question: 21

Question

Consider the following two tables named Raider and Team in a relational database maintained by a Kabaddi league. The attribute ID in table Team references the primary key of the Raider table, ID.

Raider
ID	Name	Raids	RaidPoints
$1$	Arjun	$200$	$250$
$2$	Ankush	$190$	$219$
$3$	Sunil	$150$	$200$
$4$	Reza	$150$	$190$
$5$	Pratham	$175$	$220$
$6$	Gopal	$193$	$215$

 

Team
City	ID	BidPoints
Jaipur	$2$	$200$
Patna	$3$	$195$
Hyderabad	$5$	$175$
Jaipur	$1$	$250$
Patna	$4$	$200$
Jaipur	$6$	$200$

The SQL query described below is executed on this database:
SELECT *
FROM Raider, Team
WHERE Raider.ID=Team.ID AND City="Jaipur" AND
RaidPoints > 200;

The number of rows returned by this query is $\_\_\_\_\_\_\_\_$.

Answer 1

Take cross product of both the tables with condition Raider.ID = Team.ID and then match them with City = "Jaipur" and RaidPoints > 200

We will get 3 rows

Therefore Ans: 3

Answer 2

ID	Name	Raid	Raid Point	City	Bid Point
1	ARJUN	200	250	JAIPUR	250
2	ANKUSH	190	219	JAIPUR	200
3	SUNIL	150	200	PATNA	195
4	REZA	150	190	PATNA	200
5	PRATHAM	175	220	HYDERABAD	175
6	GOPAL	193	215	JAIPUR	200

The above table is the output of $RAIDER.ID=TEAM.ID$

From the above table find Raid Point$>200$ we get four tuples.

ID	Name	Raid	Raid Point	City	Bid Point
1	ARJUN	200	250	JAIPUR	250
2	ANKUSH	190	219	JAIPUR	200
5	PRATHAM	175	220	HYDERABAD	175
6	GOPAL	193	215	JAIPUR	200

 Now in above table just find out tuples whose city is "JAIPUR", we get:

ID	Name	Raid	Raid Point	City	Bid Point
1	ARJUN	200	250	JAIPUR	250
2	ANKUSH	190	219	JAIPUR	200
6	GOPAL	193	215	JAIPUR	200

So given SQL query returns three tuples as output.