SQL문 분석(코딩테스트 복기)하고 스킬 습득하기

1. 문제1

 

<SQL문>

SELECT round(avg(AGE),4) FROM EMPLOYEE A,DEPT B
WHERE A.DEPTCODE = B.CODE and B.NAME='개발';

 

<해석>

EMPLOYEE라는 테이블과 DEPT라는 테이블을 각각 A,B라고 alias를 칭한다

 

A라는 테이블에 존재하는 DEPTCODE라는 열과 B라는 테이블에 존재하는 CODE가 서로 같고<이거는 A의 DEPTCODE와 B의 CODE가 서로 같은 변수라서 inner join key>

 

>>> DEPTCODE랑 CODE를 연결시켜 A와 B를 inner join함

 

그런데 여기서 만약 '개발'이라는 NAME을 가지는 행만 조회하고 싶다면

 

WHERE에 추가 조건으로 and로 연결시켜서

 

WHERE A.DEPTCODE=B.CODE and B.NAME='개발'한다.

 

그러면 이 상태의 TABLE에서 'AGE'변수의 평균을 구해서 출력하고 싶다면 SELECT문에 avg(AGE)를 사용한다.

 

이 때 반올림해서 소수점 4자리까지 나타내고 싶다면 round(avg(AGE),4)

 

-----------------------------------------------------------------------------------------------------------------------

 

2. 문제2

 

subquery를 이용하는 것이 중요하다는 것을 배웠음

 

subquery를 이용해서 하나하나씩 파고 들어가서 원하는 목적에 맞게 출력을 시키는 그런 느낌??

 

한문장으로 써야하는데 subquery없이는 한문장으로 쓰기 어렵다는거

 

subquery 사용시에는 꼭 alias를 사용해야함

 

<SQL문>

 

SELECT 

 

(RANK() OVER(ORDER BY H.TOTAL desc, H.LAST_SOLVED_TIME)) as RANKING, H.NAME, H.TOTAL, H.LAST_SOLVED_TIME 

 

FROM 

 

(SELECT G.NAME,sum(G.SCORE) as TOTAL,max(G.max_date) as LAST_SOLVED_TIME FROM (SELECT E.ID,E.NAME,E.IDX,E.SCORE,F.SOLVED,E.max_date FROM (SELECT C.ID,C.NAME,C.IDX,D.SCORE,C.max_date FROM (SELECT B.ID,B.NAME,A.IDX,A.max_date FROM (SELECT ID,IDX,max(SOLVED_DATE) as max_date FROM SOLVE_LOGS GROUP BY ID,IDX ORDER BY ID,IDX) A, USERS B WHERE A.ID = B.ID) C, QUIZZES D WHERE C.IDX = D.IDX) E, SOLVE_LOGS F WHERE E.max_date = F.SOLVED_DATE and E.ID = F.ID) G
GROUP BY G.NAME,G.SOLVED
HAVING G.SOLVED = 't') H;

 

 

<해석>

 

먼저 H라는 subquery 테이블에서 RANKING, NAME, TOTAL, LAST_SOLVED_TIME이라는 열을 조회할 것이다.

 

RANKING은 행 index를 만든 것인데

 

ROW_NUMBER()은 그냥 1,2,3,4,....

 

RANK()는 동일한 순위는 동일하게 나타내고 다음 순위를 건너 뛰어서 1,1,3,4...

 

DENSE_RANK()는 동일 순위가 있어도 다음 순위를 건너뛰지 않는다. 1,1,2,3,...

 

(RANK() OVER(ORDER BY H.TOTAL desc, H.LAST_SOLVED_TIME))에서 

 

(RANK() OVER()) 형태인데 OVER() 안에는 RANK를 매기는 조건을 써준다.

 

그래서 H.TOTAL을 내림차순으로, H.TOTAL이 같으면 H.LAST_SOLVED_TIME을 오름차순으로 RANK를 매긴다.

 

그러니까 H.TOTAL이 클수록 RANK가 높고, H.TOTAL이 같으면 H.LAST_SOLVED_TIME이 작을수록 순위가 높다

 

----------------------------------------------------------------------------------------------------------

다음 이제 H라는 테이블은 어떻게 만들어졌는가?

 

SELECT 

 

G.NAME,sum(G.SCORE) as TOTAL,max(G.max_date) as LAST_SOLVED_TIME 

 

FROM 

 

(SELECT E.ID,E.NAME,E.IDX,E.SCORE,F.SOLVED,E.max_date FROM (SELECT C.ID,C.NAME,C.IDX,D.SCORE,C.max_date FROM (SELECT B.ID,B.NAME,A.IDX,A.max_date FROM (SELECT ID,IDX,max(SOLVED_DATE) as max_date FROM SOLVE_LOGS GROUP BY ID,IDX ORDER BY ID,IDX) A, USERS B WHERE A.ID = B.ID) C, QUIZZES D WHERE C.IDX = D.IDX) E, SOLVE_LOGS F WHERE E.max_date = F.SOLVED_DATE and E.ID = F.ID) G

GROUP BY G.NAME,G.SOLVED

 

HAVING G.SOLVED = 't'

 

G라는 테이블에서 NAME, SCORE의 sum을 TOTAL로 max_date의 max값을 LAST_SOLVED_TIME으로 지칭하여 조회하는데

 

NAME과 SOLVED로 그룹화하여 G.SOLVED='t'를 만족시키는 행에 대해서만 NAME,TOTAL,LAST_SOLVED_TIME을 조회한 테이블이 H

 

----------------------------------------------------------------------------------------------

 

그러면 G는 어떻게 만들었지??

 

SELECT 

 

E.ID,E.NAME,E.IDX,E.SCORE,F.SOLVED,E.max_date 

 

FROM 

 

(SELECT C.ID,C.NAME,C.IDX,D.SCORE,C.max_date FROM (SELECT B.ID,B.NAME,A.IDX,A.max_date FROM (SELECT ID,IDX,max(SOLVED_DATE) as max_date FROM SOLVE_LOGS GROUP BY ID,IDX ORDER BY ID,IDX) A, USERS B WHERE A.ID = B.ID) C, QUIZZES D WHERE C.IDX = D.IDX) E, SOLVE_LOGS F 

 

WHERE E.max_date = F.SOLVED_DATE and E.ID = F.ID

 

E라는 테이블과 SOLVE_LOGS라는 테이블을 F로 하여 이 두 테이블을 JOIN하는데

 

E의 max_date와 F의 SOLVED_DATE를 join key로, E의 ID와 F의 ID를 2번째 join key로 하여 inner join을 한 상태에서

 

E의 ID, NAME, IDX, SCORE를 조회하고 F의 SOLVED를 조회하고 E의 max_date를 조회한다.

 

---------------------------------------------------------------------------------------------------------

그러면 E는 어떻게 만들었지??

 

SELECT 

 

C.ID,C.NAME,C.IDX,D.SCORE,C.max_date 

 

FROM 

 

(SELECT B.ID,B.NAME,A.IDX,A.max_date FROM (SELECT ID,IDX,max(SOLVED_DATE) as max_date FROM SOLVE_LOGS GROUP BY ID,IDX ORDER BY ID,IDX) A, USERS B WHERE A.ID = B.ID) C, QUIZZES D 

 

WHERE C.IDX = D.IDX

 

C라는 테이블과 QUIZZES를 D라고 하여 두 테이블을 IDX라는 join key로 inner join한 다음에

 

C의 ID, NAME,IDX를 조회하고 D의 SCORE를 조회하고 C의 max_date를 조회한 테이블

 

--------------------------------------------------------------------------------------------------------------------

 

그러면 C는 어떻게 만들었냐?

 

SELECT 

 

B.ID,B.NAME,A.IDX,A.max_date 

 

FROM 

 

(SELECT ID,IDX,max(SOLVED_DATE) as max_date FROM SOLVE_LOGS GROUP BY ID,IDX ORDER BY ID,IDX) A, USERS B 

 

WHERE A.ID = B.ID

 

 

A라는 테이블과 USERS를 B라고 하여 ID를 join key로 한 inner join을 한 다음에 B의 ID, NAME을 조회하고 A의 IDX, max_date를 조회한다

 

그러면 마지막 A는 어떻게 만들었지??

 

SELECT 

 

ID,IDX,max(SOLVED_DATE) as max_date 

 

FROM SOLVE_LOGS 

 

GROUP BY ID,IDX 

 

ORDER BY ID,IDX

 

SOLVE_LOGS라는 테이블에서 ID,IDX로 그룹화한다음 ID,IDX를 오름차순으로 차례대로 정렬하여

 

ID,IDX,solved_date의 max값을 max_date라고 하여 조회한다.

 

문제를 풀 때 목적이 무엇이지??

 

그러면 어디서부터 시작해야할까??

 

select * from a로 하나의 테이블을 만든다음, 이를 subquery로 해서

 

이 테이블에서 또 select * from (select * from a) b 이런식으로 조작을 해서... 

 

내가 원하는 형태를 또 만들고... 여기서 또 필요하다면 select * from (select * from (select * from a) b) c 형식으로 또 조작을 해서... 

 

내가 원하는 출력을 만든다. 이게 sql문제의 기본일듯

 

-------------------------------------------------------------------------------

 

아무튼 추가로 여기서 SOLVED_DATE가 TIMESTAMP라는 시간형태의 자료형인데

 

max()함수로 시간 값이 가장 늦은 값을 구할 수 있다는거

 

min()함수는 시간 값이 가장 빠른 값을 구할 수 있겠지

 

 

3. 되돌아보기

 

subquery를 잘 사용하자

 

select * from (select * from (select * from a) b) c ............