retagged by
1 flag 13,358 views
22 votes
22 votes

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

  1. $4$
  2. $5$
  3. $0$
  4. $2$
retagged by
1 flag

3 Answers

Best answer
41 votes
41 votes

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

edited by
13 votes
13 votes

The inner query(Avg Cost) will return value as 225.Clearly, only 4 rows of Catalogue satisfy this criteria.

Sno. Cost
S2 250
S3 250
S3 300
S3 250

 

Now, cross, join of Catalogue(above) and Suppliers with s.sno=c.sno will result in 4 rows.

Answer-A

 

7 votes
7 votes
given that Nested subquery but not co-related subquery.

So, evaluate the innermost query first.

Select AVG(cost)

From Catalog C

where pno='P4'

groupby pno

 

there are two rows which contains pno='p4'

SUM= 200+250 = 450, AVG = $\frac{450}{2} = 225$

 

when you evaluate outer query :

in the catalog there are only four rows which have cost grater than 225

when you cross product them you will get 4 rows, but when you select only sid and sname there are three rows which have same value.

but SQL select operation doesn't eliminate duplicates.

Hence final answer is 4
edited by
Answer:

Related questions

18 votes
18 votes
3 answers
1
Arjun asked Feb 12, 2020
12,864 views
Which one of the following is used to represent the supporting many-one relationships of a weak entity set in an entity-relationship diagram?Diamonds with double/bold bor...
20 votes
20 votes
5 answers
3
Arjun asked Feb 12, 2020
11,683 views
Consider a schedule of transactions $T_1$ and $T_2$:$\begin{array}{|c|c|c|c|c|c|c|c|c|c|c|} \hline T_1 & RA & & & RC & & WD & & WB & \text{Commit} & \\ \hline T_2 & & R...