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 Companyu_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’);

Related topics

Professor Jayesh video tutorial

Please use contact page in this website if you find anything incorrect or you want to share more information about the topic discussed above.