Consider the following database tables of a sports league. \( \begin{array}{l…
2025
Consider the following database tables of a sports league.
\( \begin{array}{l l} \textbf{player}(\text{pid}, \text{pname}, \text{age}) & \textbf{team}(\text{tid}, \text{tname}, \text{city}, \text{cid}) \\ \textbf{coach}(\text{cid}, \text{cname}) & \textbf{members}(\text{pid}, \text{tid}) \end{array} \)
An instance of the table and an SQL query are given.
\(\begin{aligned} &\text{SELECT } \text{MIN}(P.\text{age}) \\ &\text{FROM } \textbf{player} \ P \\ &\text{WHERE } P.\text{pid} \text{ IN } ( \\ &\quad \text{SELECT } M.\text{pid} \\ &\quad \text{FROM } \textbf{team} \ T, \textbf{coach} \ C, \textbf{members} \ M \\ &\quad \text{WHERE } C.\text{cname} = 'Mark' \\ &\quad \text{AND } T.\text{cid} = C.\text{cid} \\ &\quad \text{AND } M.\text{tid} = T.\text{tid} \ ) \end{aligned} \)
The value returned by the given SQL query is ______ . (Answer in integer)
Attempted by 108 students.
Show answer & explanation
Correct answer: 26
Key idea: find the players who are members of teams coached by Mark, then take the minimum of their ages.
Find the coach record for the name Mark: Mark has cid = 102.
Find teams with cid = 102: that is the team with tid = 10.
Find members with tid = 10: the member pids are 1 and 3.
Look up ages for pid 1 and pid 3: ages are 31 and 26. The minimum age is 26.
Answer: 26
A video solution is available for this question — log in and enroll to watch it.