MadeEasy Test Series: Databases - Sql

Question

i think neither of the queries can implement given specification. plz confirm!
 

Comments

query 2 is correct ,but don't know what are they trying to do in query 1,so many tuple variable used unnecessarily

Answer 1

First of all it is a question based on nested query with correlation in which :

a) Outer query uses the result of inner query 

b) Inner query uses the attributes defined in the outer query..

So here we need to implement division query as we need to find the names of 

"companies which are located in every city plus the additional constraint that in that particular city "Made easy" centre should be located..

So we find M.company_name s.t. |All cities in which "Madeeasy" is located| - |All the cities in which the company referred in the outer query is present| 

So this has to be checked for each of the companies using the subquery mentioned above..Since the attribute of outer query is being used , it is nested query with correlation..

Now the subquery which is written above in plain form can be written in SQL using difference operator..So if 

. |All cities in which "Madeeasy" is located|  = |All the cities in which the company referred in the outer query is present| 

for a particular company so the difference operator will yield an empty result for that company , thus indicating that the particular company is present in all cities in which "Madeeasy" is located..

We know ,

In correlated subquery , we use EXISTS and NOT EXISTS clauses..Here we use NOT  EXISTS clause as we need those company names whose result of inner query(or subquery) is an empty result..And NOT EXISTS returns true when the result of the inner subquery is empty which is the case here..

Hence option C) is the correct answer.. 

The same thing we can represent in relational algebra as :

Π_{company_name,city}(σ_{company_name = "Madeeasy"} (Company)) / Π_city(σ_{company_name = "Madeeasy"} (Company))

Comments

@Anusha
Don't you think query 1 is technically wrong, there is no operator given in Where Clause?

---

@vijay yes

---

In video solution provided by made easy they have used contains clause in query 1 instead of except clause,

so i wanted to know that if contains is used in first query instead of except then it will give correct answer right???

and also what about no operator used with where clause it is not valid right??