인사 데이터베이스 조인

소개

이 프로젝트에서는 personnel 데이터베이스의 emp 테이블, dept 테이블, 그리고 salgrade 테이블에서 관련 데이터를 검색하기 위해 join 쿼리를 사용하는 방법을 배우게 됩니다.

👀 미리보기

🎯 과제

이 프로젝트에서 다음을 배우게 됩니다:

• MySQL 데이터베이스에 접근하고 personnel 데이터를 가져오는 방법
• equi-join (inner join) 을 사용하여 직원 테이블에서 이름, 입사일, 그리고 해당 부서 이름을 검색하는 방법
• non-equi join 을 사용하여 직원 테이블에서 모든 직원의 이름, 급여, 그리고 해당 급여 등급 정보를 검색하는 방법
• self-join 을 사용하여 "SMITH"라는 직원의 상사 정보를 검색하는 방법
• left outer join 을 사용하여 모든 부서 정보와 해당 직원 정보를 검색하는 방법

🏆 성과

이 프로젝트를 완료하면 다음을 수행할 수 있습니다:

• 다양한 유형의 join 쿼리와 언제 사용해야 하는지 이해
• join 쿼리를 사용하여 데이터베이스의 여러 테이블에서 데이터를 검색하고 조작
• 고급 SQL 개념을 적용하여 복잡한 데이터 검색 문제를 해결

MySQL 접속 및 데이터 가져오기

이 단계에서는 MySQL 데이터베이스에 접속하고 personnel 데이터를 가져오는 방법을 배우게 됩니다.

1. 터미널을 열고 다음 명령을 실행하여 MySQL 서비스를 시작합니다:

sudo service mysql start

2. 비밀번호 없이 sudo 명령을 사용하여 MySQL 명령줄 인터페이스에 접속합니다:

sudo mysql

3. /home/labex/project/personnel.sql 파일에서 personnel 데이터를 MySQL 데이터베이스로 가져옵니다:

SOURCE /home/labex/project/personnel.sql;

이제 MySQL 데이터베이스에 성공적으로 접속하여 personnel 데이터를 가져왔습니다. 다음 단계로 진행할 수 있습니다.

사원 이름, 입사일, 부서명 조회

이 단계에서는 equi-join (inner join) 을 사용하여 직원 테이블에서 이름, 입사일 및 해당 부서 이름을 검색하는 방법을 배우게 됩니다.

1. 텍스트 편집기에서 join_query.sql 파일을 열고 다음 코드를 추가합니다:

SELECT e.ename AS `Name`, e.hiredate AS HireDate, d.dname AS DepartmentName
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

이 쿼리는 emp 테이블과 dept 테이블 간에 inner join 을 수행하며, emp 테이블의 deptno 열과 dept 테이블의 deptno 열을 일치시킵니다. 결과 출력에는 직원 이름, 입사일 및 부서 이름이 포함됩니다.

2. join_query.sql 파일을 저장합니다.

사원 이름, 급여, 급여 등급 조회

이 단계에서는 non-equi join 을 사용하여 직원 테이블에서 모든 직원의 이름, 급여 및 해당 급여 등급 정보를 검색하는 방법을 배우게 됩니다.

1. join_query.sql 파일을 열고 이전 쿼리 아래에 다음 코드를 추가합니다:

SELECT e.ename AS `Name`, e.sal AS Salary,
       CASE s.grade
           WHEN 1 THEN 'A'
           WHEN 2 THEN 'B'
           WHEN 3 THEN 'C'
           WHEN 4 THEN 'D'
           WHEN 5 THEN 'E'
           ELSE 'Unknown'
       END AS Grade
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

이 쿼리는 emp 테이블과 salgrade 테이블 간에 non-equi join 을 수행하며, 직원의 급여 (e.sal) 를 급여 등급 범위 (s.losal 및 s.hisal) 와 일치시킵니다. CASE 문은 숫자 급여 등급을 해당 문자 등급으로 변환하는 데 사용됩니다.

2. join_query.sql 파일을 저장합니다.

'SMITH' 사원의 상사 정보 조회

이 단계에서는 self-join 을 사용하여 "SMITH"라는 직원의 상사 정보를 검색하는 방법을 배우게 됩니다.

1. join_query.sql 파일을 열고 이전 쿼리 아래에 다음 코드를 추가합니다:

SELECT e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno
FROM emp e1
JOIN emp e2 ON e1.empno = e2.mgr
WHERE e2.ename = 'SMITH';

이 쿼리는 emp 테이블에 대해 self-join 을 수행하며, 한 직원의 empno 열 (e1) 을 다른 직원의 mgr 열 (e2) 과 일치시킵니다. WHERE 절은 결과를 "SMITH"라는 직원의 상사 정보만 포함하도록 필터링합니다.

2. join_query.sql 파일을 저장합니다.

모든 부서 정보 및 해당 사원 정보 조회

이 단계에서는 left outer join 을 사용하여 모든 부서 정보와 해당 직원 정보를 검색하는 방법을 배우게 됩니다. 부서에 직원이 없는 경우, 직원 정보는 NULL 로 채워집니다.

1. join_query.sql 파일을 열고 이전 쿼리 아래에 다음 코드를 추가합니다:

SELECT e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno,
       d.deptno AS deptno_dept, d.dname, d.loc
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno;

이 쿼리는 dept 테이블과 emp 테이블 간에 left outer join 을 수행하며, 두 테이블의 deptno 열을 일치시킵니다. 결과 출력에는 모든 부서 정보와 해당 직원 정보가 포함됩니다. 부서에 직원이 없는 경우, 직원 정보는 NULL 로 채워집니다.

2. join_query.sql 파일을 저장합니다.

이제 프로젝트의 모든 작업을 완료했습니다. MySQL 명령줄 인터페이스에서 join_query.sql 스크립트를 실행하여 쿼리 결과를 확인할 수 있습니다:

MariaDB [personnel]> SOURCE /home/labex/project/join_query.sql;

요약

축하합니다! 이 프로젝트를 완료했습니다. LabEx 에서 더 많은 랩을 연습하여 기술을 향상시킬 수 있습니다.