GATE CSE 2020 | Question: 13

Question

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

• A. $4$
• B. $5$
• C. $0$
• D. $2$

Comments

Inner query results in zero ? Every attribute of group by clause must select in select clause

---

answer should b 2 actually because we just have to select s.sname and s.sno so there is 2 similar row so it will return 2 rows only

s2  M/s Balaji furniture

s3 M/s Premium furniture

reply whether am i right or not

---

@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.

Answer 1

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

Comments

Ohh yes got it!

I forgot that sql doesn't eliminate duplicates and got 2 rows as answer. :-P

---

@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.

---

Note:

Projection(in RA) = Select distinct (in Sql) != Select (in Sql)

Answer 2

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

 

Answer 3

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

Comments

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. 

---

@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-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!