csed421 database systems lab join. human resources (hr) schema
TRANSCRIPT
![Page 1: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/1.jpg)
CSED421Database Systems Lab
Join
![Page 2: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/2.jpg)
Human Resources (HR) schema
![Page 3: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/3.jpg)
Cross Join (Cartesian Prod-uct)
107×27=2889 rows selected.
employees departments
27 rows selected.107 rows selected.
SELECT e.last_name, d.department_nameFROM employees e, departments d;
…
![Page 4: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/4.jpg)
Inner JoinEMPLOYEES DEPARTMENTS
106 rows selected.
27 rows selected.107 rows selected.
SELECT e.last_name, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_id;
…
![Page 5: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/5.jpg)
Use a join to query data from more than one table.
The join condition in the WHERE clause. mysql> SELECT table1.column1, table2.column2
–> FROM table1, table2 –> WHERE table1.column3 = table2.column3 -> AND table1.column4 = table2.column4;
JOIN ~ ON ~ mysql> SELECT table1.column1, table2.column2
–> FROM table1 JOIN table2 –> ON table1.column3 = table2.column3 -> AND table1.column4 = table2.column4;
JOIN ~ USING ~ mysql> SELECT table1.column1, table2.column2
–> FROM table1 JOIN table2 –> USING (column3, column4);
Inner Join
![Page 6: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/6.jpg)
Equijoin
107 rows selected.
EMPLOYEES DEPARTMENTS
27 rows selected.
(Foreign key)
(Primary key)
SELECT e.employee_id, d.department_id, d.department_nameFROM employees e, departments dWHERE e.department_id = d.department_id;
![Page 7: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/7.jpg)
Non-Equijoin
107 rows selected.
EMPLOYEES JOB_GRADES
6 rows selected.
107 rows selected.
SELECT last_name, salary, grade_levelFROM employees, job_gradesWHERE salary BETWEEN lowest_sal AND highest_sal;
(WHERE salary >= lowest_sal AND salary <= highest_sal)
…
![Page 8: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/8.jpg)
Joining More than Two Ta-bles
107 rows selected.
EMPLOYEES DEPARTMENTS
27 rows selected.
23 rows selected.
LOCATIONS
SELECT e.employee_id, d.department_id, l.cityFROM employees e, departments d, locations lWHERE e.department_id = d.department_id AND d.location_id = l.location_id;
![Page 9: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/9.jpg)
Outer Join
107 rows selected.
EMPLOYEES DEPARTMENTS
27 rows selected.
![Page 10: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/10.jpg)
Left join All rows from the left table, even if there are no
matches in the right table. mysql> SELECT table1.column1, table2.column2 –> FROM table1 LEFT JOIN table2 –> ON table1.column3 = table2.column3;
Right join All rows from the right table, even if there are no
matches in the left table. mysql> SELECT table1.column1, table2.column2 –> FROM table1 RIGHT JOIN table2 –> ON table1.column3 = table2.column3;
Outer Join
![Page 11: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/11.jpg)
Outer Join
SELECT employee_id, department_nameFROM employees e LEFT JOIN departments dON e.department_id = d.department_id;
EMPLOYEESDEPARTMENTS
107 rows selected. 107 rows selected.
EMP ⋈ DEPT
27 rows selected.
![Page 12: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/12.jpg)
Outer Join
SELECT employee_id, department_nameFROM employees e JOIN departments dON e.department_id = d.department_id;
EMPLOYEESDEPARTMENTS
107 rows selected. 106 rows selected.
EMP ⋈ DEPT
27 rows selected.
![Page 13: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/13.jpg)
Self Join
8 rows selected.
107 rows selected.
EMPLOYEES (WORKER) EMPLOYEES (MANAGER)
SELECT e1.last_name, e2.last_nameFROM employees e1, employees e2WHERE e1.employee_id = e2.manager_id;
106 rows selected.
![Page 14: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/14.jpg)
SELECT table1.column, table2.columnFROMtable1[ CROSS JOIN table2 ] |[ NATURAL JOIN table2 ] |[ JOIN table2 USING (column_name)] |[ JOIN table2
ON (table1.column1 = table2.column2) ] |[ LEFT | RIGHT | FULL OUTER JOIN table2
ON (table1.column1 = table2.column2) ];
Joining Tables Using SQL: 1999 Syntax
![Page 15: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/15.jpg)
Source hr.sql
http://ids.postech.ac.kr/dblab/2014/hr.sql
Human Resources (HR) schema
![Page 16: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/16.jpg)
lab3 DB schema
![Page 17: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/17.jpg)
1. 특정 부서에 소속되어 있는 직원들의 이름 (last_name) 과 각 직원의 소속부서의 이름 (department_name) 을 출력하기 .
Practice
106 rows selected.
…
![Page 18: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/18.jpg)
2. 부서 이름 (department_name) 과 해당 부서가 위치한 국가 이름(country_name), 지역 이름 (region_name) 을 출력하기 .
Practice
27 rows selected.
![Page 19: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/19.jpg)
3. ‘Seattle’ 도시 (city) 에서 근무하는 직원들의 부서명(department_name) 과 이름 (last_name) 을 출력하기 .
Practice
![Page 20: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/20.jpg)
4. 모든 부서이름 (department_name) 과 각 부서에 소속된 직원의 수 출력하기 .
Practice
27 rows selected.
![Page 21: CSED421 Database Systems Lab Join. Human Resources (HR) schema](https://reader035.vdocuments.us/reader035/viewer/2022062222/5697bf751a28abf838c80776/html5/thumbnails/21.jpg)
5. 자신의 상사 (manager) 보다 오래 일한 직원들의 이름(last_name) 을 출력하기 .
Practice