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.
| 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 |
| 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
3
Explanation
The condition Raider.ID=Team.ID turns the cross product into a join that pairs each Team row with the raider of the same ID. Of these pairs, keep only the Jaipur rows whose raider has more than 200 raid points:
- Jaipur, ID 2: Ankush with 219 points, kept.
- Jaipur, ID 1: Arjun with 250 points, kept.
- Jaipur, ID 6: Gopal with 215 points, kept.
The Patna and Hyderabad rows fail the city test. Note that the comparison is strict, so a raider with exactly 200 points (Sunil) would not count, but he is in Patna anyway. The query returns 3 rows.
Answer: 3