The given query is a nested subquery but not co-related subquery (inner query is independent of the outer and so can be executed independently)
SELECT AVG (cost) FROM Catalogue WHERE pno= 'P4' GROUP BY pno
$$\begin{array} {c c c} \hline \underline{\textbf{sno}} & \underline{\textbf{pno}} & \underline{\textbf{cost}} \\\hline S1 & P1 & 150 \\\hline S1 & P2 & 50 \\\hline S1 & P3 & 100 \\\hline S2 & P4 & 200 \\\hline S2 & P5 & 250 \\\hline S3 & P1 & 250 \\\hline S3 & P2 & 150 \\\hline S3 & P5 & 300 \\\hline S3 & P4 & 250 \end{array}$$
First, we will select the tuples with pno = ‘P4’ and then group by pno (so just one group) and then find the average cost.
$$\begin{array} {c c c} \hline \underline{\textbf{sno}} & \underline{\textbf{pno}} & \underline{\textbf{cost}} \\\hline {\color{Blue} {S2} } & {\color{Blue} {P4} } & {\color{Blue} {200} } \\\hline {\color{Blue} {S3} } & {\color{Blue} {P4} } & {\color{Blue} {250} } \end{array} $$
So average cost $= \frac{200+250}{2} = 225$
$\therefore$ the inner query will return $225$
Now the given SQL query would become
SELECT s.sno,s.sname FROM Supplier s , Catalogue c WHERE s.sno=c.sno AND cost> 225
So here we need to do cross product of supplier table $s$ and Catalogue table $c$ and from the cross product we will select those rows where $s.sno=c.sno$ AND $ \text{cost} > 225$
Since it is given that $\text{cost} > 225$ so we do not need to consider rows from the Catalogue table having $cost\leq 225$ while doing cross product. Hence from the Catalogue table only the row numbers $5,6,8,9$ need to be taken while doing the cross product.
After doing cross product we’ll get,
$$\begin{array} {c c c c c c}\hline \text{s.sno} & \text{s.name} & \text{s.location} & \text{c.sno} & \text{c.pno} & \text{c.cost} \\\hline S1 & \text{M/s Royal furniture} & \text{Delhi} & S2 & P5 & 250 \\\hline S1 & \text{M/s Royal furniture} & \text{Delhi} & S3 & P1 & 250 \\\hline S1 & \text{M/s Royal furniture} & \text{Delhi} & S3 & P5 & 300 \\\hline S1 & \text{M/s Royal furniture} & \text{Delhi} & S3 & P4 & 250 \\\hline {\color{Blue}{S2} } & {\color{Blue}{\text{M/s Balaji furniture}} } & {\color{Blue}{\text{Bangalore}} } & {\color{Blue}{S2} } & {\color{Blue}{P5} } & {\color{Blue}{250} } \\\hline S2 & \text{M/s Balaji furniture} & \text{Bangalore} & S3 & P1 & 250 \\\hline S2 & \text{M/s Balaji furniture} & \text{Bangalore} & S3 & P5 & 300 \\\hline S2 & \text{M/s Balaji furniture} & \text{Bangalore} & S3 & P4 & 250 \\\hline S3 & \text{M/s Premium furniture} & \text{Chennai} & S2 & P5 & 250 \\\hline {\color{Blue}{S3} } & {\color{Blue}{\text{M/s Premium furniture}} } & {\color{Blue}{\text{Chennai}} } & {\color{Blue}{S3} } & {\color{Blue}{P1} } & {\color{Blue}{250} } \\\hline {\color{Blue}{S3} } & {\color{Blue}{\text{M/s Premium furniture}} } & {\color{Blue}{\text{Chennai}} } & {\color{Blue}{S3} } & {\color{Blue}{P5} } & {\color{Blue}{300} } \\\hline {\color{Blue}{S3} } & {\color{Blue}{\text{M/s Premium furniture}} } & {\color{Blue}{\text{Chennai}} } & {\color{Blue}{S3} } & {\color{Blue}{P4} } & {\color{Blue}{250} } \end{array}$$
Now after doing cross product only $4$ tuples will be selected from the table due to the condition $s.sno=c.sno$
$$\begin{array} {c c c c c c}\hline \text{s.sno} & \text{s.name} & \text{s.location} & \text{c.sno} & \text{c.pno} & \text{c.cost} \\\hline {\color{Blue}{S2} } & {\color{Blue}{\text{M/s Balaji furniture}} } & {\color{Blue}{\text{Bangalore}} } & {\color{Blue}{S2} } & {\color{Blue}{P5} } & {\color{Blue}{250} } \\\hline {\color{Blue}{S3} } & {\color{Blue}{\text{M/s Premium furniture}} } & {\color{Blue}{\text{Chennai}} } & {\color{Blue}{S3} } & {\color{Blue}{P1} } & {\color{Blue}{250} } \\\hline {\color{Blue}{S3} } & {\color{Blue}{\text{M/s Premium furniture}} } & {\color{Blue}{\text{Chennai}} } & {\color{Blue}{S3} } & {\color{Blue}{P5} } & {\color{Blue}{300} } \\\hline {\color{Blue}{S3} } & {\color{Blue}{\text{M/s Premium furniture}} } & {\color{Blue}{\text{Chennai}} } & {\color{Blue}{S3} } & {\color{Blue}{P4} } & {\color{Blue}{250} } \end{array}$$
$\therefore$ Option $A.$ $4$ is the correct answer