Consider the following database tables of a sports league.

player (pid, pname, age)            team(tid, tname, city, cid)
coach (cid, cname)                  members (pid,tid)

An instance of the table and an SQL query are given.

$\begin{array}{} \textbf{player} \\ \begin{array}{|c|c|} \hline \textbf{pid} & \textbf{pname} & \textbf{age} \\ \hline \text{1} & \text{Jasprit} & \text{31} \\ \hline \text{2} & \text{Atharva} & \text{24} \\ \hline \text{3} & \text{Ishan} & \text{26} \\ \hline \text{4} & \text{Axar} & \text{30} \\ \hline \end{array} \\ \end{array} \quad \begin{array}{} \textbf{coach} \\ \begin{array}{|c|c|} \hline \textbf{cid} & \textbf{cname} \\ \hline \text{101} & \text{Ricky} \\ \hline \text{102} & \text{Mark} \\ \hline \text{103} & \text{Trevor} \\ \hline \end{array} \\ \end{array} \quad \begin{array}{} \textbf{team} \\ \begin{array}{|c|c|c|} \hline \textbf{tid} & \textbf{tname} & \textbf{city} & \textbf{cid} \\ \hline \text{10} & \text{MI} & \text{Mumbai} & \text{102} \\ \hline \text{20} & \text{DC} & \text{Delhi} & \text{101} \\ \hline \text{30} & \text{PK} & \text{Mohali} & \text{103} \\ \hline \end{array} \\ \end{array} \quad \begin{array}{} \textbf{members} \\ \begin{array}{|c|c|} \hline \textbf{pid} & \textbf{tid} \\ \hline \text{1} & \text{10} \\ \hline \text{2} & \text{30} \\ \hline \text{3} & \text{10} \\ \hline \text{4} & \text{20} \\ \hline \end{array} \\ \end{array}$

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)

View worked solution

Answer: 26:26

To find the value returned by the given SQL query, we can evaluate it step-by-step starting from the nested subquery.

Step 1: Evaluate the Nested Subquery

The subquery 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

Filter by Coach Name: From the coach table, the row with cname = 'Mark' has cid = 102.
Join with Team Table (T.cid = C.cid): From the team table, the row with cid = 102 has tid = 10 (team name MI).
Join with Members Table (M.tid = T.tid): From the members table, we find the pid values associated with tid = 10:
- pid = 1 (where tid = 10)
- pid = 3 (where tid = 10)

Thus, the subquery returns the set of player IDs: {1, 3}.

Step 2: Evaluate the Outer Query

The outer query is:

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

From the player table, we look up the ages of players with pid in {1, 3}:

For pid = 1 (Jasprit): age = 31
For pid = 3 (Ishan): age = 26

Taking the minimum of these ages:

\text{MIN}(31, 26) = 26

Therefore, the value returned by the SQL query is 26.

Free practice · Your answers on this page are not saved.

GATE CS 2025 Set 1 Q45 — Tuple Calculus and SQL

Step 1: Evaluate the Nested Subquery

Step 2: Evaluate the Outer Query

Related questions