[국민취업지원제도 일경험프로그램 12일차] SQL 문제 풀이(JOIN)

12일차 특이사항 

1. SQL 문제 풀기

2. JOIN에 대해 학습이 더 필요하다.

 

SQL 문제 풀이

어제 나의 풀이는 틀린 부분이 많았다. 다시 풀어보자. 

 

1. 커미션(comm)이 없는 사원의 정보 검색
 - (사원번호, 사원이름, 직업, 부서코드, 프로젝트코드, 연봉, 커미션) 

SELECT EMPNO, ENAME, JOB, DEPTNO, PROJECT_CODE, SAL, COMM
FROM EMP 
WHERE COMM IS NULL;

2. 직업이 ('MANAGER','CLERK')인 사원의 정보검색 
 - (사원번호, 사원이름, 직업, 부서코드, 프로젝트코드, 연봉)

SELECT EMPNO, ENAME, JOB, DEPTNO, PROJECT_CODE, SAL
FROM EMP
WHERE JOB='CLERK' OR JOB='MANAGER';

SELECT EMPNO, ENAME, JOB, DEPTNO, PROJECT_CODE, SAL
FROM EMP
WHERE JOB IN ('CLERK', 'MANAGER');

: IN 쿼리를 써도 되고, OR을 써도 된다. 

3. 입사일(hiredate)가 81년도 이면서 연봉(sal)이 3000 이상인 사원정보검색
 - (사원번호, 사원이름, 연봉, 입사일(0000년00월00일))

SELECT EMPNO, ENAME, SAL, HIREDATE
FROM EMP
WHERE HIREDATE BETWEEN '1981/01/01' AND '1981/12/31' AND SAL >= 3000;

select empno, sal, ename, job, to_char(hiredate, 'yy"년"mm"월"dd') 
from emp
where to_char(hiredate, 'yy') = '81' and sal>=3000;

: BETWEEN을 써도 되지만 to_char함수를 사용할 수도 있다. 년, 월, 일 같이 한글로 표기할 때는 ""를 붙여줘야 한다. 

4. 각 부서별 사원수
 - (부서코드, 부서명, 사원수)

SELECT A.DEPTNO, A.DNAME, COUNT(B.DEPTNO) AS EMPLOYEE_COUNT
FROM DEPT A 
LEFT OUTER JOIN EMP B 
ON A.DEPTNO = B.DEPTNO 
group by A.DEPTNO, A.DNAME; 


select A.DEPTNO, A.DNAME, COUNT(B.DEPTNO) AS EMPLOYEE_COUNT 
from dept a, emp b 
where a.deptno=b.deptno(+) 
group by A.DEPTNO, A.DNAME;

: join문은 다른 형식으로 쓰인다. 직접 left join으로 써줄 수도 있지만 where 조건절로 쓸 수도 있다. 결과는 같다. 

5. 각 부서별 프로젝트 총 기간?
 - (부서코드, 부서명, 프로젝트 기간)

SELECT A.DEPTNO, DNAME, sum(PROJECT_TERM)
FROM dept A 
LEFT JOIN emp B ON A.deptno = B.deptno
LEFT JOIN project C on b.project_code = lower(c.PROJECT_CODE) group by A.DEPTNO, DNAME;


select dept.deptno, dname, sum(project_term) from dept, emp, project
where dept.deptno = emp.deptno(+)
and emp.project_code= lower(project.project_code(+))
group by dept.deptno ,dname;

: 이것도 같은 방식이다. 중요한 포인트는 lower을 사용하여 대문자 데이터 내용을 소문자로 바꿔줘야 null값이 안 나온다. 

6. (dept, emp, project) UNION을 사용하여 하나의 테이블로 완성
   (결과 총 22개의 데이터 검색)

select empno, ename, job from emp
union all
select 0, null, project_name from project
union all
select 0, null, null from dept; 


SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, PROJECT_CODE
FROM EMP
UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, DEPTNO, NULL
FROM DEPT
UNION ALL
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, PROJECT_CODE
FROM PROJECT;

: 컬럼개수와 이름이 일치하지 않으면 합칠 수 없다. null 값을 넣어서 해결할 수도 있고, 임의의 컬럼값을 지정해주면 된다. UNION을 쓰려면 컬럼개수, 이름이 일치해야 된다. 

 

7. 부서별 사원의 연봉을 내림차순으로 정렬
 - (부서코드, 부서명, 연봉, 랭크)

SELECT A.DEPTNO, DNAME, SAL,
       RANK() OVER (PARTITION BY A.DEPTNO ORDER BY SAL DESC) AS SALARY_RANK
FROM EMP A
left JOIN DEPT B ON A.DEPTNO = B.DEPTNO
GROUP BY A.DEPTNO, DNAME, SAL
ORDER BY A.DEPTNO, SAL DESC;

 

8. 심화문제
DB_STUDY_EXAMP1 테이블 참조하여 db교육_심화 이미지의 오른쪽 내용으로 보이게 쿼리문짜기

select a.STDR_DE, a.CPOP_DETS_ITEM_VL, a.CPOP_AMT,
       b.STDR_DE , b.CPOP_DETS_ITEM_VL, b.CPOP_AMT from
(select rownum rnum1, e1.*from db_study_examp1 e1 where io_se_cd = 1) A,
(select rownum rnum2, e2.*from db_study_examp1 e2 where io_se_cd =2) B
where rnum1 = rnum2;



SELECT a.STDR_DE, a.CPOP_DETS_ITEM_VL, a.CPOP_AMT,
       b.STDR_DE , b.CPOP_DETS_ITEM_VL, b.CPOP_AMT
FROM (
    SELECT rownum rnum1, e1.* FROM db_study_examp1 e1 WHERE io_se_cd = 1
) a
LEFT JOIN (
    SELECT rownum rnum2, e2.* FROM db_study_examp1 e2 WHERE io_se_cd = 2
) b 
ON a.rnum1 = b.rnum2;

: 서브쿼리로 테이블을 만든 후, 가상컬럼을 만들어서 기준을 만들어준다. 그 기준에 맞는 컬럼을 조회하면 위 그림이 나온다. 

 

db교육 내용정리

8.17 db 교육 

select 위험성이 별로 없다. 조회만 한다. 
insert 위험성이 크지 않다. 기존 데이터를 건드리지 않기 때문임. 
update 위험도 100%. where절 잘못 설정하면 큰일남. select 하고 나서 확인 후 실행할 것. 
delete 위험도 100%.  

데이터 잘못 설정하면 큰일난다. 돈과 관련된 웹 사이트 DB 잘못건드리면 손해배상 해야 할 수도 있다. 

---

PRIMARY key - nn(NOT NULL), unique(한테이블에 하나의 PRIMARY KEY)  
t1(pk)  t2 t3 t4 
1       1   1   1
1       2   3   4   -- 이 경우 오류가 난다. pk 중복. 

pk가 걸려있으면 중복이 안됨. 
pk가 여러 개 걸려있으면 행끼리 비교한다. 

---

inner join 
outer join 

a1 a2         b1 b2
1   1          2  2  

join
컬럼은 더하고 
데이터는 곱한다. 곱하기는 옆으로 붙는다!

a1   a2      b1      b2 
건수? row 하나를 건이라고 부름.  4*4 = 16 

inner join 곱해놓고 공통된 부분 찾기
join on 이 좋으나 , 도 좋다. 

inner join = 같은 것만 찾겠다. 교집합.

outer join. outer란 무엇인가? (left, right)가 붙어야 한다. 
inner와 outer는 생략이 가능하다. 

outer의 기준은 다 출력이 되고 옆에 해당되는 칼럼만 붙는다. 

select*from a, b where a1=b1(+) 
right join과 inner join은 값이 같다. (공식) 

---

1억 건의 데이터 조회하면 컴퓨터가 뻗어버림. 기상청 슈퍼 컴퓨터 정도는 돼야 함. 

서브쿼리 (역할 - 테이블 or 데이터)

어떻게, 언제 쓰나? from 뒤에 들어오면 table 역할을 한다. 

select*from (select*from dept) b, emp;  

★dept는 테이블 명이다. 행렬구조 자체가 table이다. 

프로젝트 가면 데이터 몇 건 있는지 확인, key값이 뭐냐? pk가 뭐냐? fk가 뭐냐? 기록해 두자. 
칼럼에 뭐든 임시로 추가할 수 있다(확장성) 

 

느낀 점

1. 쿼리문 작성을 많이 해봐야 할 것 같다. 이론으로 아는 것과 실전은 다르다. SQLD 시험 잘 보려면 쿼리문 연습과 동시에 문제를 풀어봐야 한다. 

 

2. DB 공부도 만만치 않네. 알아야 할 것이 많다. 오늘 회사에서 공부한 내용에 대해 강의를 들어보자.