The GATE Grind

GATE 2025 CS (CS1) – Question 55

Databases · Relational Model: Relational Algebra, Tuple Calculus, SQL · 2 marks · Numerical answer

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

All Relational Model: Relational Algebra, Tuple Calculus, SQL questions →