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.
| pid | pname | age |
|---|---|---|
| 1 | Jasprit | 31 |
| 2 | Atharva | 24 |
| 3 | Ishan | 26 |
| 4 | Axar | 30 |
| cid | cname |
|---|---|
| 101 | Ricky |
| 102 | Mark |
| 103 | Trevor |
| tid | tname | city | cid |
|---|---|---|---|
| 10 | MI | Mumbai | 102 |
| 20 | DC | Delhi | 101 |
| 30 | PK | Mohali | 103 |
| 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)
Answer
26
Explanation
The subquery joins coach, team and members to find the players in teams coached by Mark.
- coach: Mark has cid = 102.
- team: the team with cid = 102 is MI, with tid = 10.
- members: tid = 10 gives pid 1 and pid 3.
The outer query takes the smaller age of pid 1 (Jasprit, 31) and pid 3 (Ishan, 26), which is 26.
Answer: 26