GATE2025_CS1_Q55

Consider the following database tables of a sports league and an SQL query. The value returned by the given SQL query is ______. (Answer in integer)

player

pid	pname	age
1	Jasprit	31
2	Atharva	24
3	Ishan	26
4	Axar	30

coach

cid	cname
101	Ricky
102	Mark
103	Trevor

team

tid	tname	city	cid
10	MI	Mumbai	102
20	DC	Delhi	101
30	PK	Mohali	103

members

pid	tid
1	10
2	30
3	10
4	20

SELECT MIN(P.age)
FROM player P
WHERE P.pid IN (
  SELECT M.pid
  FROM team T, coach C, members M
  WHERE C.cname = 'Mark'
  AND T.cid = C.cid
  AND M.tid = T.tid
)

✅ Final Answer:

The correct answer is 26.

🔹 Short Answer:

The query finds the minimum age of players coached by 'Mark'. First, the inner query finds Mark's coach ID (102), then his team ID (10), and finally the player IDs on that team (1 and 3). The outer query then filters the player table for these IDs, finds their ages (31 and 26), and returns the minimum, which is 26.

🔸 Long Answer:

To solve this, we must evaluate the nested SQL query. The execution starts from the innermost query and works its way out.

Step 1: Evaluate the Inner Subquery

The inner query is:

SELECT M.pid
FROM team T, coach C, members M
WHERE C.cname = 'Mark' AND T.cid = C.cid AND M.tid = T.tid

Let's break down the `WHERE` clause conditions:

1. C.cname = 'Mark': We filter the `coach` table to find the entry for Mark. This gives us the row `(102, 'Mark')`. So, `C.cid = 102`.
2. T.cid = C.cid: Now, we join this result with the `team` table where `T.cid = 102`. In the `team` table, the row `(10, 'MI', 'Mumbai', 102)` matches this condition. So, `T.tid = 10`.
3. M.tid = T.tid: Finally, we join this with the `members` table where `M.tid = 10`. Two rows in the `members` table match: `(1, 10)` and `(3, 10)`.

The `SELECT M.pid` clause then extracts the `pid` values from these resulting rows.
Result of the inner query: The set of player IDs is `{1, 3}`.

Step 2: Evaluate the Outer Query

The outer query now becomes:

SELECT MIN(P.age)
FROM player P
WHERE P.pid IN {1, 3}

1. FROM player P: We look at the `player` table.
2. WHERE P.pid IN {1, 3}: We filter this table to find rows where `pid` is either 1 or 3.
   • Row for pid=1: `(1, Jasprit, 31)`
   • Row for pid=3: `(3, Ishan, 26)`
3. SELECT MIN(P.age): From the filtered rows, we take the ages (31 and 26) and find the minimum value.

The minimum of {31, 26} is 26.

Conclusion

The final value returned by the SQL query is 26.

Bibliography:
- Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. McGraw-Hill. (Chapter on SQL).
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems. Pearson.

Ask your doubts in comment form our GURUJEE will reply ASAP, click notify me button so that you get notified on reply.