Oracle Hr Schema (JOIN Practice)

1.  Emp ID,Emp Name
SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name"
FROM employees;

2.  Emp ID,Emp Name, Dept Name
***** ORACLE JOIN *****
SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name" , department_name "Dept Name"
FROM employees e,departments d
WHERE e.department_id = d.department_id;
***** NATURAL JOIN *****
// will NOT give the output AS it has two common COLUMN
SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name" , department_name "Dept Name"
FROM employees NATURAL JOIN departments;

***** USING *****
SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name" , department_name "Dept Name"
FROM employees JOIN departments
USING (department_id);
***** ON *****
SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name" , department_name "Dept Name"
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

3.  Emp ID,Emp Name, Dept Name, City Name
SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name" , department_name "Dept Name", city "City Name"
FROM employees e,departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

4.  Emp ID,Emp Name, Dept Name, City Name,Country
SELECT employee_id "EMP ID", first_name ||' '|| last_name "Emp Name", department_name "Dept Name", city "City Name", country_name Country
FROM employees e,departments d, locations l, countries c
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id=c.country_id;

5.  Emp ID,Emp Name, Dept Name, City Name,Country,Region Name
SELECT employee_id "EMP ID", first_name ||' '|| last_name "Emp Name", department_name "Dept Name", city "City Name", country_name Country , region_name "Region Name"
FROM employees e,departments d, locations l, countries c, regions r
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id=c.country_id AND c.region_id =r.region_id;

6.  Emp Name,City Name
SELECT first_name || ' ' || last_name "Emp Name", city "City Name"
FROM employees e,departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

7.  Dept Name, Country
SELECT department_name "Dept Name", country_name Country
FROM departments d, locations l, countries c
WHERE d.location_id = l.location_id AND l.country_id=c.country_id;

8.  Dept Name,Region Name
SELECT department_name "Dept Name", region_name "Region Name"
FROM departments d, locations l, countries c, regions r
WHERE d.location_id = l.location_id AND l.country_id=c.country_id AND c.region_id =r.region_id;

9.  Emp ID,Emp Name,Region Name
SELECT employee_id "EMP ID", first_name ||' '|| last_name "Emp Name", region_name "Region Name"
FROM employees e,departments d, locations l, countries c, regions r
WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id=c.country_id AND c.region_id =r.region_id;

10. City Name,Region Name
SELECT city "City Name", region_name "Region Name"
FROM locations l, countries c, regions r
WHERE l.country_id=c.country_id AND c.region_id =r.region_id;