IAE Academy

GATE 2024 DA Q.31: solution and answer

GATE 2024 · DAQ.31 · 1 mark · NAT

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.

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 ______.

GATE 2024 DA question 31: Two tables.Tap to open full size
Figure from the GATE 2024 DA paper. It is part of the question.
Figure description

Two tables. 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).

Answer and solution

ANSWER

The query returns 3 rows.

Official answer: 3Source: Official answer key, GATE 2024 DA (IISc Bengaluru)

  1. The query lists FROM Raider, Team with Raider.ID=Team.ID, so it is an equi-join. Every Team.ID (2, 3, 5, 1, 4, 6) matches exactly one Raider.ID, because Team.ID references the primary key of Raider. The join therefore has 6 rows, one per Team row.

  2. Apply City="Jaipur". The Team rows in Jaipur are those with ID 2, 1 and 6. The Patna rows (ID 3 and 4) and the Hyderabad row (ID 5) are removed.

  3. Apply RaidPoints > 200 to the matching Raider rows. ID 1 (Arjun) has 250, ID 2 (Ankush) has 219 and ID 6 (Gopal) has 215. All three are greater than 200, so all three stay.

  4. The result has rows. The strict comparison > would matter only for a row with exactly 200 raid points. That is Sunil (ID 3), who is in the Patna team and is already removed by the city condition.

KEY IDEA

A comma join with an equality condition on the key is an equi-join. Apply each filter to the joined rows, and count the rows that satisfy all three conditions together.

WATCH OUT FOR

Counting the three Jaipur rows without checking RaidPoints, or treating RaidPoints > 200 as >= 200 and then wrongly including Sunil. Sunil belongs to Patna, so he is excluded either way.

From the GATE 2024 DA paper. Every question of this paper · Download the full paper · All 4 SQL questions, with notes

More SQL questions