Find all employees who live in the city where the company for which they work is located

Q. Consider the following employee database-

Employee (Emp_name, Street, City)

Works (Emp_name, Company_name, Salary)

Company (Company_name, City)

Manages (Emp_name, manager_name)

Write expressions in SQL for the following queries-

(i) Find all employees who live in the city where the company for which they work is located.

(ii) Find all employees who live in the same city and on the same street as their managers.

(iii) Find all employees in the database who do not work for ABC corporation.

(iv) Find all employee who earn more than every employee of ABC corporation.(

v) Find all company names located in every city in which ABC corporation is located.

Solution:

(i) select Emp_name from Employee, Works ,Company where Employee.Emp_name=Works.Emp_name and Works.Company_name = Company.Company_name and Employee.City=Company.City;

(ii) select Emp_name from Employee where Employee.street AND Employee.city IN(select Street,City from Employee, Manages where Emp_name = Manager_name;

(iii) select Emp_name from Employee where Emp_name NOT IN(select Emp_name from Works where Company u_name=’ABC’);

(iv) select Emp_name from Works where salary > (select max(salary) from Works where Company_name = ‘ABC’);

(v) select Company_name from Company where City IN (select City from Company where Comapnay_name =’ABC corporation’);