Dev.to

0221 Basic SQL -

일주일간 배운 쿼리문에 대한 퀴즈를 풀었다. 그 퀴즈중 헷갈리고 정리가 필요한 문제들에서만 정리해보고, 어떻게 접근해야 하는지 정리해봤다. 문제를 풀다가 이렇게 다른 테이블과 아무 연관이 없는 테이블을 이용해 데이터 검색을 해야 하는 경우가 있다. job_grades. 다음과 같은 순서로 생각한다: 검색해야 하는 컬럼중 생각하는 테이블에 없는 컬럼을 찾는다. 부서명 셀프 조인 사용하기 사원1이 상사2에 보고 사원2도 상사2에 보고 사원3은 상사3에 보고 사원4는 상사8에 보고 사원 아이디와 사원의 매니저 아이디는 같은 테이블에 있다. 사원 아이디와 매니저는 모두 employees 테이블에 있다. 사원의 정보를 출력할 테이블의 별칭을 staff 라고 두고 매니저의 정보를 출력하는 가상의 테이블 별칭을 manager라고 둔다. 생각하는 순서는 다음과 같다. 먼저 검색해야 할 컬럼들을 별칭을 정할것들을 염두에 두고 나열한다. 같은 테이블에서 이름을 가져오기 때문에 컬럼에 별칭을 지정해준다. from 절에서 테이블 이름을 준다. where 절에서 스태프의 매니저 아이디 행들과 매니저 가상 테이블의 사원아이디가 매칭되는 행들만 가져온다. SELECT e1.employee_id AS employee_id, e1.last_name AS employee_name, e2.employee_id AS manager_id, e2.last_name AS manager_name FROM employees e1 LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id; 문제2 -- 18. hr 스키마에 있는 Employees, Departments 테이블의 구조를 파악한 후 사원 수가 다섯명 이상인 부서의 부서명과 사원 수를 출력하시오. 이때 사원 수가 많은 순으로 정렬하시오. group by 절을 하기 전에 조인을 해주지 않아 틀렸다. select d.department_name, count(e.employee_id) -- employee_id is primary. from departments d, employees e group by d.department_name having count(e.employee_id) >= 5;*/ -- -- 조인을 안해서 틀렸음. select d.department_name, count(e.employee_id) from departments d, employees e where e.department_id = d.department_id group by d.department_name having count(e.employee_id) >=5; -- ANSI select d.department_name, count(e.employee_id) from employees e join departments d on e.department_id = d.department_id group by d.department_name having count(e.employee_id) >= 5; -- 조인 기능은 서로 다른 테이블에 흩어져 있는 정보를 하나로 합쳐서 원하는 데이터를 뽑아내기 위해서 사용한다. 한 테이블에 "고객 정보" (이름, 고객ID)가 있고, 다른 테이블에 "주문 정보" (주문ID, 고객ID, 주문 날짜)가 있다고 가정한다. 만약 "어떤 고객이 언제 주문을 했는지" 알고 싶다면? 고객 테이블이랑 주문 테이블을 고객ID라는 공통된 키로 연결(조인)해서, 이름과 주문 날짜를 한 번에 볼 수 있게 만들 수 있다. 고객 정보 테이블이 customer 이고 주문 정보 테이블이 order 이라면, customer.customer_id = order.customer_id 를 하게 되면 두테이블에서 customer_id 가 일치하는 행들만 가져올 수 있다. 두 테이블의 교집합만 가져오는 것이다. 매니저로 근무하는 사원들의 총수를 조회하시오. 데이터베이스는 여러 테이블로 나뉘어 데이터를 저장하는데, 테이블 간 데이터를 연결하려면 공통된 값을 사용해야 해요. 그때 사용하는 것이 "키"입니다. 기본 키(Primary Key): 각 테이블에서 행(레코드)을 고유하게 식별하는 컬럼(예: employee_id가 직원을 고유하게 구분). 외래 키(Foreign Key): 한 테이블에서 다른 테이블의 기본 키를 참조하는 컬럼(예: manager_id가 다른 직원의 employee_id를 가리킴). "키를 참조한다" = "한 테이블의 외래 키가 다른 테이블(또는 같은 테이블)의 기본 키를 가리켜 연결한다." 직관적으로는 "이 데이터는 저 데이터와 관련이 있다"는 연결 고리라고 생각하면 됩니다. 워크벤치의 reverse engineer 기능을 이용해 eer 다이어그램을 만들면 쿼리문 작성에 도움이 된다. employes테이블에 연결된 선 4개를 하나씩 보면 employee 테이블의 어떤 컬럼이 다른 테이블의 컬럼과 연결되었는지 확인할 수 있다. 여기서 내가 필요로 하는 manager_id는 다른 테이블의 키와 연결되어있지 않다. manager_id 는 employees 테이블의 employee_id를 참조한다. 고로 셀프 조인을 써야 한다. 모든 사원은 employee id 가 있고 매니저도 마찬가지이다. 매니저로 근무하는 사원을 조회하려면 다음과 같은 쿼리문을 작성할 수 있다 -- same result select count(distinct e1.employee_id) as manager_count from employees e1 join employees e2 on e1.employee_id = e2.manager_id; e1은 매니저 테이블의 별칭이고 e2는 사원 테이블의 별칭이다. 매니저 테이블 e1 에서 사원 테이블을 조인할때 매니저테이블의 employee_id 가 e2

Feb 23, 2025 - 11:11

일주일간 배운 쿼리문에 대한 퀴즈를 풀었다. 그 퀴즈중 헷갈리고 정리가 필요한 문제들에서만 정리해보고, 어떻게 접근해야 하는지 정리해봤다.

문제를 풀다가 이렇게 다른 테이블과 아무 연관이 없는 테이블을 이용해 데이터 검색을 해야 하는 경우가 있다. job_grades.

다음과 같은 순서로 생각한다:

검색해야 하는 컬럼중 생각하는 테이블에 없는 컬럼을 찾는다. 부서명

셀프 조인 사용하기
사원1이 상사2에 보고
사원2도 상사2에 보고
사원3은 상사3에 보고
사원4는 상사8에 보고

사원 아이디와 사원의 매니저 아이디는 같은 테이블에 있다. 사원 아이디와 매니저는 모두 employees 테이블에 있다. 사원의 정보를 출력할 테이블의 별칭을 staff 라고 두고 매니저의 정보를 출력하는 가상의 테이블 별칭을 manager라고 둔다.

생각하는 순서는 다음과 같다.

먼저 검색해야 할 컬럼들을 별칭을 정할것들을 염두에 두고 나열한다. 같은 테이블에서 이름을 가져오기 때문에 컬럼에 별칭을 지정해준다. from 절에서 테이블 이름을 준다. where 절에서 스태프의 매니저 아이디 행들과 매니저 가상 테이블의 사원아이디가 매칭되는 행들만 가져온다.

SELECT e1.employee_id AS employee_id, 
       e1.last_name AS employee_name, 
       e2.employee_id AS manager_id, 
       e2.last_name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

문제2

-- 18. hr 스키마에 있는 Employees, Departments 테이블의 구조를 파악한 후 사원 수가 다섯명 이상인 부서의 부서명과 사원 수를 출력하시오. 이때 사원 수가 많은 순으로 정렬하시오.

group by 절을 하기 전에 조인을 해주지 않아 틀렸다.

select d.department_name, count(e.employee_id) -- employee_id is primary.
from departments d, employees e
group by d.department_name
having count(e.employee_id) >= 5;*/ -- -- 조인을 안해서 틀렸음.

select d.department_name, count(e.employee_id)
from departments d, employees e
where e.department_id = d.department_id
group by d.department_name
having count(e.employee_id) >=5;

-- ANSI
select d.department_name, count(e.employee_id)
from employees e
join departments d on e.department_id = d.department_id
group by d.department_name
having count(e.employee_id) >= 5; --

<조인을 하는 이유>

조인 기능은 서로 다른 테이블에 흩어져 있는 정보를 하나로 합쳐서 원하는 데이터를 뽑아내기 위해서 사용한다.

한 테이블에 "고객 정보" (이름, 고객ID)가 있고,
다른 테이블에 "주문 정보" (주문ID, 고객ID, 주문 날짜)가 있다고 가정한다. 만약 "어떤 고객이 언제 주문을 했는지" 알고 싶다면?

고객 테이블이랑 주문 테이블을 고객ID라는 공통된 키로 연결(조인)해서, 이름과 주문 날짜를 한 번에 볼 수 있게 만들 수 있다. 고객 정보 테이블이 customer 이고 주문 정보 테이블이 order 이라면, customer.customer_id = order.customer_id 를 하게 되면 두테이블에서 customer_id 가 일치하는 행들만 가져올 수 있다. 두 테이블의 교집합만 가져오는 것이다.

<문제3>

매니저로 근무하는 사원들의 총수를 조회하시오.

데이터베이스는 여러 테이블로 나뉘어 데이터를 저장하는데, 테이블 간 데이터를 연결하려면 공통된 값을 사용해야 해요. 그때 사용하는 것이 "키"입니다.
기본 키(Primary Key): 각 테이블에서 행(레코드)을 고유하게 식별하는 컬럼(예: employee_id가 직원을 고유하게 구분).
외래 키(Foreign Key): 한 테이블에서 다른 테이블의 기본 키를 참조하는 컬럼(예: manager_id가 다른 직원의 employee_id를 가리킴).
"키를 참조한다" = "한 테이블의 외래 키가 다른 테이블(또는 같은 테이블)의 기본 키를 가리켜 연결한다."
직관적으로는 "이 데이터는 저 데이터와 관련이 있다"는 연결 고리라고 생각하면 됩니다.

워크벤치의 reverse engineer 기능을 이용해 eer 다이어그램을 만들면 쿼리문 작성에 도움이 된다. employes테이블에 연결된 선 4개를 하나씩 보면 employee 테이블의 어떤 컬럼이 다른 테이블의 컬럼과 연결되었는지 확인할 수 있다.

여기서 내가 필요로 하는 manager_id는 다른 테이블의 키와 연결되어있지 않다. manager_id 는 employees 테이블의 employee_id를 참조한다. 고로 셀프 조인을 써야 한다.

모든 사원은 employee id 가 있고 매니저도 마찬가지이다. 매니저로 근무하는 사원을 조회하려면 다음과 같은 쿼리문을 작성할 수 있다

-- same result
select count(distinct e1.employee_id) as manager_count
from employees e1
join employees e2 on e1.employee_id = e2.manager_id;

e1은 매니저 테이블의 별칭이고 e2는 사원 테이블의 별칭이다. 매니저 테이블 e1 에서 사원 테이블을 조인할때 매니저테이블의 employee_id 가 e2