in Databases edited by
7,051 views
11 votes
11 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$
in Databases edited by
by
2441 3623 5536
7.1k views

4 Comments

Query returning s.no,s.name

Total 4 rows
0
0

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. 

2
2
^^ check the selected answer in that question, option R is not correct
0
0
Inner query results in zero ? Every attribute of group by clause must select in select clause
0
0

Subscribe to GO Classes for GATE CSE 2022

3 Answers

23 votes
23 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

edited by
by
99 129 310

1 comment

Ohh yes got it!

I forgot that sql doesn't eliminate duplicates and got 2 rows as answer. :-P
5
5
11 votes
11 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

 

by
189 424 744
4 votes
4 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
by
35 104 555

2 Comments

reshown by

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. 

0
0


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-9292 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
0
Answer:

Related questions

13 votes
13 votes
5 answers
3