GATE 2025 CS (CS1) – Question 55
Consider the following database tables of a sports league.
player(pid,pname,age)
coach(cid,cname)
team(tid,tname,city,cid)
members(pid,tid)
An instance of the table and an SQL query are given.
player: (1,Jasprit,31), (2,Atharva,24), (3,Ishan,26), (4,Axar,30)
coach: (101,Ricky), (102,Mark), (103,Trevor)
team: (10,MI,Mumbai,102), (20,DC,Delhi,101), (30,PK,Mohali,103)
members: (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
)The value returned by the given SQL query is ______ . (Answer in integer)
Practise this question in The GATE Grind →
Show answer and explanation
Correct answer: 26
Explanation
Coach Mark has $cid=102$. In `team`, $cid=102$ belongs to team MI ($tid=10$). The inner query joins `team T, coach C, members M`:
- `C.cname = 'Mark'` gives coach 102.
- `T.cid = C.cid` gives team 10.
- `M.tid = T.tid` gives the members of team 10: $pid=1$ and $pid=3$.
So the inner query returns $\{1,3\}$. Those players are Jasprit (age 31) and Ishan (age 26). The outer query takes `MIN(age)`:
$$\min(31,26)=\mathbf{26}.$$
More Relational Model: Relational Algebra, Tuple Calculus, SQL questions
- GATE 2025 CS (CS2) Q54
- GATE 2024 CS (CS1) Q35
- GATE 2024 CS (CS2) Q45
- GATE 2023 CS Q16
- GATE 2023 CS Q61
- GATE 2022 CS Q25
All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →