Consider a relational database containing the following schemas.

$\overset{\text{Catalogue}} {\begin{array}{|c|c|c|} \hline \underline{\text{sno}} & \underline{\text{pno}} & \text{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 \\ \hline \end{array}} \quad \overset{ \text{Suppliers} }{\begin{array}{|c|c|c|} \hline \underline{\text{sno}} & \text{sname} & \text{location} \\ \hline S1 & \text{M/s Royal furniture} & \text{Delhi}\\ \hline S2 & \text{M/s Balaji furniture} & \text{Bangalore} \\ \hline S3 & \text{M/s Premium furniture} & \text{Chennai} \\ \hline \end{array}} \quad \overset{\text{Parts}}{\begin{array}{|c|c|c|} \hline \underline{\text{pno}} & \text{pname} & \text{part\_spec} \\ \hline P1 & \text{Table} & \text{Wood} \\ \hline P2 & \text{Chair} & \text{Wood} \\ \hline P3 & \text{Table} & \text{Steel} \\ \hline P4 & \text{Almirah} & \text{Steel} \\ \hline P5 & \text{Almirah} & \text{Wood} \\ \hline \end{array}}$

The primary key of each table is indicated by underlining the constituent fields.

SELECT s.sno, s.sname
FROM Suppliers s, Catalogue c
WHERE s.sno=c.sno AND
    cost > (SELECT AVG (cost)
            FROM Catalogue
            WHERE pno = ‘P4’
            GROUP BY pno) ;

The number of rows returned by the above SQL query is

View worked solution

Answer: A

To find the number of rows returned by the SQL query, we can break down the execution into two steps: evaluating the inner subquery and then evaluating the outer query.

Step 1: Evaluate the Inner Subquery

The inner query is:

SELECT AVG (cost)
FROM Catalogue
WHERE pno = 'P4'
GROUP BY pno;

From the Catalogue table, we filter the rows where pno = 'P4':

(S2, P4, 200)
(S3, P4, 250)

Calculating the average cost for these rows:

\text{AVG(cost)} = \frac{200 + 250}{2} = 225

Thus, the subquery returns a single value: 225.

Step 2: Evaluate the Outer Query

The outer query simplifies to:

SELECT s.sno, s.sname
FROM Suppliers s, Catalogue c
WHERE s.sno = c.sno AND cost > 225;

First, we find all rows in the Catalogue table where cost > 225:

(S2, P5, 250) — since $250 > 225$
(S3, P1, 250) — since $250 > 225$
(S3, P5, 300) — since $300 > 225$
(S3, P4, 250) — since $250 > 225$

Next, we perform an inner join with the Suppliers table on sno. Since there is no DISTINCT keyword in the SELECT clause, duplicate rows are preserved in the final output:

s.sno	s.sname
S2	M/s Balaji furniture
S3	M/s Premium furniture
S3	M/s Premium furniture
S3	M/s Premium furniture

Thus, the query returns exactly 4 rows.

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

GATE CS 2020 Q13 — Tuple Calculus and SQL

Step 1: Evaluate the Inner Subquery

Step 2: Evaluate the Outer Query

Related questions