데이터 분석을 위한 SQL 서브쿼리

소개

이 프로젝트에서는 하위 쿼리 (subquery) 를 사용하여 인사 데이터베이스의 직원 (emp) 및 부서 (dept) 테이블에서 관련 정보를 검색하는 방법을 배우게 됩니다. 여러 테이블의 데이터에 액세스하고 분석하기 위해 복잡한 SQL 쿼리를 작성하는 연습을 할 것입니다.

👀 미리보기

🎯 과제

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

• MySQL 서버를 시작하고 인사 데이터베이스를 가져오는 방법
• 하위 쿼리를 사용하여 최고 급여를 받는 직원을 찾는 방법
• 특정 부서의 직원이 전체 회사에서 차지하는 비율을 계산하는 방법
• 하위 쿼리를 사용하여 특정 위치에서 근무하는 모든 직원을 검색하는 방법
• 자신의 부서 평균 급여를 초과하는 급여를 받는 직원을 찾는 방법

🏆 성과

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

• SQL 에서 하위 쿼리의 개념과 사용법을 이해합니다.
• 여러 테이블의 데이터를 결합하는 복잡한 SQL 쿼리를 작성합니다.
• 하위 쿼리를 사용하여 데이터베이스에서 의미 있는 통찰력을 분석하고 추출합니다.
• 실제 시나리오에서 SQL 기술을 시연합니다.

MySQL 시작 및 데이터베이스 가져오기

이 단계에서는 MySQL 서버를 시작하고 인사 데이터베이스를 가져오는 방법을 배우게 됩니다.

1. sudo 명령을 사용하여 비밀번호 없이 MySQL 서버를 시작합니다:

sudo service mysql start

2. MySQL 프롬프트에 액세스합니다:

sudo mysql

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

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

이렇게 하면 personnel 데이터베이스가 생성되고 필요한 테이블과 데이터로 채워집니다.

최고 연봉을 받는 직원 쿼리

이 단계에서는 하위 쿼리를 사용하여 최고 급여를 받는 직원의 이름을 검색하는 방법을 배우게 됩니다.

1. /home/labex/project 디렉토리에 subquery.sql이라는 새 파일을 생성합니다.
2. subquery.sql 파일에 다음 SQL 쿼리를 추가합니다:

-- 최고 급여를 받는 직원 (들) 을 검색하는 쿼리
SELECT ename AS `Name` FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

이 쿼리는 먼저 하위 쿼리를 사용하여 emp 테이블에서 최대 급여를 찾은 다음, 해당 최대 급여를 받는 직원 (들) 의 ename (직원 이름) 을 선택합니다.

부서 10 에 속한 직원 비율 계산

이 단계에서는 하위 쿼리를 사용하여 전체 회사에 대한 부서 10 의 직원 비율을 계산하는 방법을 배우게 됩니다.

1. subquery.sql 파일에 다음 SQL 쿼리를 추가합니다:

-- 부서 10 의 직원 비율을 계산하는 쿼리
SELECT d.dname AS Department, COUNT(e.empno) / (SELECT COUNT(empno) FROM emp) AS Proportion
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.deptno = 10;

이 쿼리는 먼저 emp 및 dept 테이블을 조인하여 부서 10 의 직원 수를 계산합니다. 그런 다음 이 수를 emp 테이블의 총 직원 수로 나누어 (하위 쿼리를 사용하여) 비율을 계산합니다.

뉴욕에서 근무하는 직원 정보 조회

이 단계에서는 하위 쿼리를 사용하여 뉴욕 지점에서 근무하는 모든 직원을 검색하는 방법을 배우게 됩니다.

1. subquery.sql 파일에 다음 SQL 쿼리를 추가합니다:

-- 뉴욕에서 근무하는 모든 직원을 검색하는 쿼리
SELECT e.*
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.loc = 'NEW YORK';

이 쿼리는 emp 및 dept 테이블을 조인하여 부서가 뉴욕에 위치한 직원을 검색합니다.

부서 평균 급여보다 높은 급여를 받는 직원 조회

이 단계에서는 하위 쿼리를 사용하여 해당 부서의 평균 급여보다 높은 급여를 받는 직원을 검색하는 방법을 배우게 됩니다.

1. subquery.sql 파일에 다음 SQL 쿼리를 추가합니다:

-- 해당 부서의 평균 급여보다 높은 급여를 받는 직원을 검색하는 쿼리
SELECT e.* FROM emp e
JOIN (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) AS avg
ON e.deptno = avg.deptno
WHERE e.sal > avg.avg_sal;

이 쿼리는 먼저 하위 쿼리를 사용하여 각 부서의 평균 급여를 계산합니다. 그런 다음 이 하위 쿼리를 emp 테이블과 조인하여 해당 부서의 평균 급여보다 높은 급여를 받는 직원을 선택합니다.

이 단계를 완료하면 subquery.sql 파일에 프로젝트 요구 사항을 충족하는 데 필요한 모든 SQL 쿼리가 포함됩니다.

2. 파일을 저장합니다.
3. MySQL 프롬프트에서 다음 명령을 실행하여 subquery.sql 스크립트를 실행합니다:

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

다음 출력을 볼 수 있습니다:

요약

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