Dark Mode

8,877 views

17 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

- $4$
- $5$
- $0$
- $2$

0

@Meet_2008 In SQL, duplicate values are not eliminated automatically. Hence all the 4 rows satisfying (cost>225) will be printed as given here in the best answer.

2

34 votes

Best answer

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

@Satbir Your answer is great. It would be better if you would display just the $s.sno$ and $s.sname$ columns only, as asked in the question.

1

6 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

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

reshown
Jul 19, 2020
by Chirag Shilwant

Please see this question

https://gateoverflow.in/47/gate2012-15

According to the solution of GATE authority, statement R is correct for GATE 2012 . Hence if that statement R is correct then for GATE 2020 question the inner query is incorrect. And hence answer must be 0.

Correct me if I am wrong.

1

@ChiragShilwant

Check the answer given by Arjun Sir for that question of **GATE 2012.**

they clearly mentioned in there ans that.

The above link says that all columns used in group by must be present in select clause as per SQL-92 standard but later standards doesn't enforce it. I tried this on MySQL and it works. It is allowed in MSSQL also- see below link.

From Microsoft (obviously applicable only to MS-SQL)http://msdn.microsoft.com/en-us/library/ms177673.aspx

Expressions in the GROUP BY clause can contain columns of the tables, derived tables or views in the FROM clause. The columns are not required to appear in the SELECT clause <select> list.

Each table or view column in any nonaggregate expression in the <select> list must be included in the GROUP BY list:

So, as per standard it is not allowed, but in most current DBMS it is allowed. And there is no reason why this shouldn't be allowed. So, ideally 'S' is more correct than 'R' or both are debatable and marks should have been given to all.

I hope you got it now!

0