MJ's Blog
SQL 연습 본문
테이블 정보
PK (Primary Key) = 기본키
각 행을 유일하게 식별하는 컬럼으로 전체 테이블(ex)위와 같은경우 7개 테이블 전체)에서 절대 중복되지 않는 값이다.
PK는 NULL(빈값)이 될수없으며 각 테이블마다 딱 하나만 존재하고, 이 PK값을 다른테이블에서 참조값으로 사용할수도 있다.
FK (Foreign Key) = 외래키
다른 테이블의 PK를 참조하는 컬럼이다. 즉 오리지널은 PK고, 이 FK는 오리지널 값을 다른 테이블로 부터 빌려온 개념.
그래서 FK는 다른 테이블과의 연결고리 역할한다.
FK는 중복 가능하다. (여러 직원이 같은 부서에 있을 수 있으니까)
<실제 테이블 예시>
Practice
1. 모든 직원의 이름(first_name, last_name)과 이메일 주소를 조회
2. 'IT' 부서에 속한 모든 직원의 이름과 급여를 조회
3. 급여가 10000 이상인 모든 직원의 이름, 직함(job_title), 급여를 조회
4. 1995년 이후에 입사한 모든 직원의 이름과 입사일(hire_date)을 조회하고, 입사일이 최신순으로 정렬
5. 'Sales' 부서에 속한 직원들의 정보를 급여가 높은 순으로 정렬하여 이름, 직함, 급여, 부서명을 조회
SELECT
first_name,
last_name,
(
SELECT
job_title
FROM
jobs
WHERE
jobs.job_id = employees.job_id
) AS job_title,
salary,
'Sales' AS department_name
FROM
employees
WHERE
department_id = (
SELECT
department_id
FROM
departments
WHERE
department_name = 'Sales'
)
ORDER BY
salary DESC;
6. 각 부서별 직원 수를 조회 (직원 수가 없는 부서도 결과에 포함)
<에러1>
: 부서 이름이 안나오고 id만 나옴
: 직원이 없는 부서가 사라짐
department_id 기준으로 departments 표에 employees 표를 left join 필요
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
left join한 후 같은 department_id와 department_name을 가진 행들을 하나의 그룹으로 묶고, 내림차순 정렬
SELECT
d.department_name,
COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY employee_count DESC;
실행결과 확인
7. 'Europe' 지역에 위치한 부서에서 근무하는 모든 직원의 이름, 부서명, 도시, 국가명을 조회
1)직원의 이름, 부서명, 도시명, 국가명, 지역명 수집 필요
확인해보니 Europe 지역에 근무하는 직원의 이름, 부서명, 도시, 국가명이 각각 다른 테이블에 위치
- 직원 이름 → employees 테이블
- 부서명 → departments 테이블
- 도시명 → locations 테이블
- 국가명 → countries 테이블
- 지역명 → regions 테이블
각각 아래와 같이 정의
- 이름
e.first_name
e.last_name - 부서
d.department_name - 도시
l.city - 지역
c.country_name
2)유럽을 찾아가는 여정
Europe은 region_name이고 이는 regions 테이블에있고 이는 regions_id(PK)로 조회가능.
그래서 PK -> FK로 쭉 매칭해보면,
regions의 region_id (PK) -> country의 region_id(FK)
country의 country_id (PK) -> locations의 country_id (FK)
locations의 location_id (PK) -> departments의 location_id (FK)
departments의 department_id (PK) -> employees의 department_id (FK)
8. 각 직책별 평균 급여를 계산하고, 평균 급여가 7000 이상인 직책만 조회(평균 급여는 소수점 둘째 자리까지 반올림하여 표시)
1)평균급여는 소수점 둘째 자리까지 반올림 : ROUND(AVG(e.salary), 2)
2)평균 급여가 7000 이상인 직책만 조회 : WHERE AVG(e.salary) >= 7000
3) 직책별 평균 급여를 구하고 싶지만, 직책명은 jobs 테이블에, 급여는 employees 테이블에 있어서 두 테이블을 연결필요
employees.job_id = jobs.job_id로 두 테이블을 JOIN으로 연결
JOIN jobs j ON e.job_id = j.job_id
이제 아래와 같은 결과로 나옴
4) 각 직책별 평균급여 계산
GROUP BY j.job_id, j.job_title
GROUP BY j.job_id 를 안쓰고 GROUP BY j.job_id, j.job_title를 쓰는 이유?
GROUP BY j.job_id라고 쓸 경우 모든 데이터베이스에서 작동하지 않을 수도 있기때문에, 안전하고 표준적인 방법을 2개 다 쓰는것임.
5)결과
<실패1>
종합해서 위와 같이 시도해봤는데, 에러남.
지피티에 물어보니 집계함수는 WHERE에서 사용 불가하고, 그룹화 후 조건은 HAVING 사용한다고 함.
<개선>
9. 관리자가 없는 직원의 이름과 직함을 조회
1) 관리자가 없다 = manager_id가 NULL값이다.
10. 각 직원이 관리하는 직속 부하 직원 수를 관리자의 이름과 함께 조회(부하 직원이없는 관리자도 결과에 포함)
11. 'United States of America' 에서 근무하는 직원들의 수를 각 도시(city)별로 조회
1) 직원의 수 조회 : COUNT(e.employee_id) AS employee_count
2) Europe 찾던것과 동일한 원리로 United States of America를 찾는여정 떠나기
12. 자신이 속한 부서의 평균 급여보다 많은 급여를 받는 직원들의 이름, 급여, 해당 부서의 평균 급여를 조회
13. 각 부서에서 가장 높은 급여를 받는 직원의 이름, 부서명, 급여를 조회
14. 'Accountant' 직책을 가진 모든 직원들보다 급여가 높은 'Programmer' 직책의 직원정보를 조회
15. 모든 직원에 대해, 해당 직원의 급여가 전체 직원 평균 급여보다 높은지, 낮은지, 같은지를 '급여 수준'이라는 컬럼으로 표시하여 이름, 급여, 급여 수준을 조회
16. 각 직원의 정보와 함께, 해당 직원이 입사한 연도에 입사한 총 직원 수를 조회
17. 회사의 급여 총액과, 각 지역(region_name)별 급여 총액 및 전체 급여 총액 대비 비율
18. 'Alexander Hunold'의 급여를 현재 급여에서 500만큼 인상
19. 'Bruce Ernst'를 'Finance' 부서로 이동시키고, 직책을 'Accountant'로 변경
20. 'Valli Pataballa'의 last_name을 NULL로 변경
21. 'Diana Lorentz'의 job_id를 999로 변경
22. 'Penelope Gietz'의 부모인 'William Gietz'를 employees 테이블에서 삭제('PenelopeGietz가 어떻게 되는지 확인')
23. 'Nancy Greenberg'를 employees 테이블에서 삭제('Nancy Greenberg'가 관리하던직원들은 어떻게 되는지 확인)
24. 'Seattle'를 locations 테이블에서 삭제(departments, employees에는 어떤 영향을 미치는지 확인)
'DB' 카테고리의 다른 글
| Indexing, ACID properties, Eventual Consistency (0) | 2025.06.04 |
|---|---|
| Mysql DB 생성, 수정, 레코드 관리 (1) | 2022.09.30 |
