chowdhury_riham

Oracle Hr Schema (JOIN Practice)

Dec 3rd, 2020 (edited)
1,076
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. 1.  Emp ID,Emp Name
  2. SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name"
  3. FROM employees;
  4.  
  5. 2.  Emp ID,Emp Name, Dept Name
  6. ***** ORACLE JOIN *****
  7. SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name" , department_name "Dept Name"
  8. FROM employees e,departments d
  9. WHERE e.department_id = d.department_id;
  10. ***** NATURAL JOIN *****
  11. // will NOT give the output AS it has two common COLUMN
  12. SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name" , department_name "Dept Name"
  13. FROM employees NATURAL JOIN departments;
  14.  
  15. ***** USING *****
  16. SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name" , department_name "Dept Name"
  17. FROM employees JOIN departments
  18. USING (department_id);
  19. ***** ON *****
  20. SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name" , department_name "Dept Name"
  21. FROM employees e JOIN departments d
  22. ON e.department_id = d.department_id;
  23.  
  24. 3.  Emp ID,Emp Name, Dept Name, City Name
  25. SELECT employee_id "EMP ID", first_name || ' ' || last_name "Emp Name" , department_name "Dept Name", city "City Name"
  26. FROM employees e,departments d, locations l
  27. WHERE e.department_id = d.department_id AND d.location_id = l.location_id;
  28.  
  29. 4.  Emp ID,Emp Name, Dept Name, City Name,Country
  30. SELECT employee_id "EMP ID", first_name ||' '|| last_name "Emp Name", department_name "Dept Name", city "City Name", country_name Country
  31. FROM employees e,departments d, locations l, countries c
  32. WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id=c.country_id;
  33.  
  34. 5.  Emp ID,Emp Name, Dept Name, City Name,Country,Region Name
  35. 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"
  36. FROM employees e,departments d, locations l, countries c, regions r
  37. 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;
  38.  
  39. 6.  Emp Name,City Name
  40. SELECT first_name || ' ' || last_name "Emp Name", city "City Name"
  41. FROM employees e,departments d, locations l
  42. WHERE e.department_id = d.department_id AND d.location_id = l.location_id;
  43.  
  44. 7.  Dept Name, Country
  45. SELECT department_name "Dept Name", country_name Country
  46. FROM departments d, locations l, countries c
  47. WHERE d.location_id = l.location_id AND l.country_id=c.country_id;
  48.  
  49. 8.  Dept Name,Region Name
  50. SELECT department_name "Dept Name", region_name "Region Name"
  51. FROM departments d, locations l, countries c, regions r
  52. WHERE d.location_id = l.location_id AND l.country_id=c.country_id AND c.region_id =r.region_id;
  53.  
  54. 9.  Emp ID,Emp Name,Region Name
  55. SELECT employee_id "EMP ID", first_name ||' '|| last_name "Emp Name", region_name "Region Name"
  56. FROM employees e,departments d, locations l, countries c, regions r
  57. 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;
  58.  
  59. 10. City Name,Region Name
  60. SELECT city "City Name", region_name "Region Name"
  61. FROM locations l, countries c, regions r
  62. WHERE l.country_id=c.country_id AND c.region_id =r.region_id;
RAW Paste Data