Question

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 table

coach table:

team table:

members table:

SQL query: \[ {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)

Hint:

To solve SQL queries involving JOIN operations between multiple tables, ensure that you carefully follow the relationships between the tables (e.g., matching \( cid \) and \( tid \) between coach, team, and members tables) and identify which rows satisfy the conditions.

Answer 1

Understanding the SQL Query
The SQL query returns the minimum age of players who are in teams coached by "Mark". Let's analyze it step by step.

Step 1: Identifying Teams Coached by "Mark"
- From the coach table, we see that "Mark" has cid = 102.
- Next, from the team table, the team with cid = 102 is "MI" (with tid = 10).

Step 2: Finding Players in Team "MI"
- From the members table, players who belong to team "MI" (tid = 10) are:
- Player 1 (Jasprit)
- Player 3 (Ishan)

Step 3: Determining the Minimum Age
- The ages of these players from the player table are:
- Player 1 (Jasprit): 31
- Player 3 (Ishan): 26
- The minimum age among them is 26.

Final Answer:
Thus, the value returned by the given SQL query is 26.