GATE CS 2025 Set 1 Q.55: SQL Queries and Integrity Constraints

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
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
    )

The value returned by the given SQL query is ______ . (Answer in integer)

Your answer

Official answer and solution

Official answer: 26
From: Official GATE 2025 answer key

Solution

Evaluate the inner subquery to find all player IDs belonging to teams coached by 'Mark', then find the minimum age among those players.

In the coach table, C.cname = 'Mark' matches cid = 102.
In the team table, T.cid = 102 matches tid = 10 (MI).
In the members table, M.tid = 10 matches pid values 1 and 3.
In the outer query on player, pid = 1 (Jasprit) has age = 31 and pid = 3 (Ishan) has age = 26.
Therefore, MIN(P.age) returns $\min(31, 26) = 26$ .

Final answer

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2025 Computer Science and Information Technology - Session 1 (CS1)
Question: 55 of 65
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source