Question

Using HR database solve the following queries: [Use Join concept]

1. List all employee names who are living at ‘United Kingdom’
2. List all the cities of region ‘Asia’
3. List employee details whose manager lives in Toronto location
4. Display department name located at ‘California’
5. Display name of the managers whose street address is ‘2011 Interiors Blvd’

Answer 1

Answer:

1. select e.first_name from employees e join departments d on e.department_id = d.department_id

join locations l on d.location_id = l.location_id

join country c on c.country_id = l.country_id where c.country_name ='United Kingdom';

2. select l.city from locations l join country c on l.location_id = c.country_id

join regions r on r.region_id = l.region_id where l.region_name ='Asia';

3. select * from employees where manager_id IN (select e.manager_id from

employees e join department d on d.department_id = e.department_id

join locations l on d.location_id = l.location_id where l.city = 'Toronto');

4. select d.department_name from department d join locations l on

d.location_id = l.location_id where l.city='California';

5. SELECT m.first_name FROM employees e INNER JOIN employees m ON m.employee_id = e.manager_id

join department d on d.department_id = e.department_id join locations l

on l.location_id = d.location_id where l.street_address ='2011 Interiors Blvd';