doubt( sql query)

Question

consider the relation 

employee(employee_name,street,city)

works(employee_name,company_name,salary)

company(company_name,city)

manages(employee_name,manager_name)

query=Assume that the company may be located in several cities.find all the companies located in every city in which "small bank corporation" is located.

can anyone provide a general approach to solve this type of query ??? i am confused in the line "located in every city".it can be done through division operator in relation algebra but how this can be done in sql.

Comments

take the cities which have "small bank corporation" , check the company_names which are in the city

 

select C₁.company_name

from company C₁

where C₁.city in ( select C₂.city from company C₂ where C₂.company_name = "small bank corporation" ) ;

---

@shaikh the solution which is given is

select T.company name
from company T
where (select R.city
from company R
where R.company name = T.company name)
contains
(select S.city
from company S
where S.company name = ’Small Bank Corporation’)

can you explain how this two solution is similar.

---

it is a co-related query, let take each row from outer query then evaluate inner query.

let name T at outer query, R is at inner query1 and S is inner query2

 

i hope contains used as JUST like IN operator in our Normal SQL in this problem, then only those two quires are equivalent.

 

but i found a different functionality w.r.to contains ( JUST like LIKE operator in our Normal SQL  ) and with different syntax in the internet.

 

another good example on co-related query https://gateoverflow.in/242014