- 오늘 학습 키워드
SQL 리마스터 - union, sub query, with 구문
- 오늘 학습한 내용을 나만의 언어로 정리하기
오늘은 SQL의 데이터테이블 결합 방법 중 하나인 union과, 쿼리 안의 쿼리인 sub query, 외부에 쿼리를 임시 저장하는 with 구문에 대해서 리마스터했다.
union은 거의 한 달만에 작성하는 문법이었다. 그렇다보니 꽤나 버벅이긴 했지만, 금방 감을 되찾을 수 있었다.
sub query와 with 구문은 SQL 코드카타를 진행하면서 자주 사용했던 문법이다. 그래서 이번에 진행된 복습에서는 큰 어려움이 없었다. 다만 sub query 중 중첩 서브쿼리는 조금 헤맸다.서브쿼리 내부의 where절에 메인쿼리와 연결을 시켜주는 과정을 몰랐기 때문이다.
이번 복습으로 이 과정 또한 확실히 익힐 수 있었다.
- 학습 내용
SQL 리마스터
union
- 부서 번호가 11인 직원 목록을 조회한 테이블과 12인 직원 목록을 조회한 테이블을 UNION ALL로 결합하시오
SELECT empno, ename_ko, deptno
FROM emp
WHERE deptno = 11
UNION ALL
SELECT empno, ename_ko, deptno
FROM emp
WHERE deptno = 12
;
- 재직 중인 직원 목록을 조회한 테이블과 퇴사한 직원을 조회한 테이블을 UNION으로 결합하시오 (employed가 1이면 재직중, 0이면 퇴사로 표시)
SELECT empno AS '사번',
ename_ko AS '이름',
if(employed=1, '재직 중', '퇴사') AS '재직여부'
FROM emp
WHERE employed=1
UNION ALL
SELECT empno AS '사번',
ename_ko AS '이름',
if(employed=1, '재직 중', '퇴사') AS '재직여부'
FROM emp
WHERE employed=0
;
sub query
1) 중첩 서브쿼리 - 비상관 서브쿼리
- emp 테이블에서 중첩 서브쿼리를 사용하여 아래와 같이 전체 평균 급여보다 많이 받는 직원을 조회하는 SQL 쿼리를 작성하시오.
SELECT ename_ko AS '이름',
joblv AS '직급',
sal AS '급여'
FROM emp
WHERE sal > (
SELECT avg(sal)
FROM emp
)
;
2) 중첩 서브쿼리 - 상관 서브쿼리
- emp 테이블에서 중첩 서브쿼리를 사용하여 아래와 같이 부서 평균 급여보다 많이 받는 직원을 조회하는 SQL 쿼리를 작성하시오.
SELECT ename_ko AS '이름',
joblv AS '직급',
sal AS '급여'
FROM emp AS a
WHERE a.sal > (
SELECT avg(b.sal)
FROM emp AS b
WHERE a.deptno = b.deptno
)
;
3) 스칼라 서브쿼리
- 스칼라 서브쿼리를 사용하여 아래와 같이 데이터를 출력하시오.
SELECT empno AS '사번',
ename_ko AS '이름',
deptno AS '부서번호',
(SELECT dname FROM dept b WHERE a.deptno = b.deptno) AS '부서명'
FROM emp a
;
- 스칼라 서브쿼리를 사용하여 데이터 조인 없이 아래와 같이 데이터를 출력하시오.
SELECT empno AS '사번',
ename_ko AS '이름',
deptno AS '부서번호',
(SELECT dname FROM dept b WHERE a.deptno = b.deptno) AS '부서명',
(SELECT avg(c.sal) FROM emp AS c WHERE a.deptno = c.deptno) AS '부서평균급여'
FROM emp a
;
4) 인라인 뷰 서브쿼리
- 재직자만 조회하는 쿼리를 인라인 뷰 활용하여 작성하고 부서명을 왼쪽 조인하여 데이터를 출력하시오.
SELECT e1.empno AS '사번', e1.ename_ko AS '이름', d.dname AS '부서명'
FROM (
SELECT empno, ename_ko, deptno
FROM emp
WHERE employed=1) AS e1
LEFT JOIN dept AS d
ON e1.deptno = d.deptno
;
- 부서별 급여 1등만 출력하는 서브 쿼리를 윈도우 함수와 인라인 뷰 활용하여 데이터를 출력하시오.
SELECT empno AS '사번',
ename_ko AS '이름',
sal AS '급여',
dept_rank AS '부서별 급여 순위'
FROM (
SELECT empno,
ename_ko,
sal,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS dept_rank
FROM emp
) AS ranked_emp
WHERE dept_rank = 1
;
with 구문
- with 구문(CTE)을 사용하여 사원의 총급여(sal + comm) 를 먼저 계산한 뒤, 아래와 같이 데이터를 출력하시오.
WITH total_sal AS (
SELECT empno, ename_ko, deptno, (sal + comm) AS total_sc
FROM emp
)
SELECT a.empno AS '사원번호',
a.ename_ko AS '이름',
a.total_sc AS '총급여',
b.dname AS '부서명'
FROM total_sal AS a
JOIN dept AS b
ON a.deptno = b.deptno
;
- with 구문(CTE)을 사용하여 부서별 급여 순위를 먼저 계산한 뒤, 각 부서별 1등에 해당하는 데이터만 출력하는 SQL 쿼리를 작성하시오.
WITH sal_rank AS (
SELECT empno,
ename_ko,
sal,
deptno,
RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS dept_rank
FROM emp
)
SELECT empno AS '사원번호',
ename_ko AS '이름',
deptno AS '부서번호',
sal AS '급여'
FROM sal_rank
WHERE dept_rank=1
;
- 학습하며 느낀 점
실무에서 정말로 자주 사용하는 것들 위주로 SQL 쿼리 작성 방법을 복습했다. 앞으로 진행할 코드카타에서도 오늘까지 배운 내용들을 적용해서 문제를 풀어볼 생각이다.
SQL 외에도 파이썬 기초 알고리즘, PANDAS 기초에 대해서 복습하는 시간을 가졌다. 일단 오늘은 SQL에 집중하고, 내일 마침 라이브세션도 하나 뿐이기에 파이썬과 PANDAS 복습을 집중해서 진행하려고 한다.
마치며 : 연말이 되어가면서 마음이 왠지 모르게 들뜨는 듯하다. 이따금씩 공부를 하다가도 집중력이 흐트러지거나 하는데, 정신 차리고 공부에 집중할 수 있도록 해야 한다.
집중력을 높이는 데에 찬물로 세수하는 것이 도움이 된다고 한다. 앞으로 공부를 하다가 집중이 안 된다면 빠르게 화장실로 가서 냉수 마찰을 해줘야겠다.
공부를 한 후 짧은 휴식을 취하는 것도 좋다고 한다. 사람이 무언가에 집중할 수 있는 시간은 그리 길지 않다. 성인의 경우, 일반적으로 20분 정도라고 한다. 일정 시간 집중해서 공부를 했다면 잠시 쉬었다가 다시 공부하는 것이 가장 효율적이다.
오래 공부하려면 전략을 잘 짜야 한다. 우선 가능한 오래 집중해서 공부하다가 잠시 휴식을 취할 것! 다가오는 프로젝트까지 잘 공부해서 좋은 결과를 낼 수 있도록 노력하자!
