[29]SQL문 활용 연습

작성일 | In

SQL문 활용 연습

테이블 생성

1
2
3
4
5
6
7
8
9
10
CREATE TABLE emp(
    empno   NUMBER(4)   --사번
    ,ename VARCHAR2(10) --이름
    ,job    VARCHAR2(9) --직급
    ,mgr    NUMBER(4)   --매니저정보
    ,hiredate   DATE    --입사일
    ,sal NUMBER(7,2)    --급여
    ,comm NUMBER(7,2) --커미션(보너스)
    ,deptno NUMBER(2)   --부서코드
);

행 삽입

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'개나리','사원',7902,'2000-12-17',200,20);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'진달래','주임',7698,'2001-12-15',360,20);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'라일락','주임',7698,'2001-02-17',355,30);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'손흥민','과장',7839,'2002-01-11',400,30);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'박지성','주임',7698,'2000-07-12',325,20);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'김연아','사원',7698,'2001-12-17',225,10);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'무궁화','사원',7839,'2005-11-14',200,10);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'홍길동','부장',7566,'2006-06-17',450,20);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'송강호','과장',7566,'2018-09-17',400,30);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'정우성','대표',7839,'2004-09-09',500,30);

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,deptno)
VALUES(7369,'김혜수','사원',7902,'2001-12-03',200,20);

1) 테이블 필드 목록 확인

1
SELECT * FROM emp;

2) 사원이름순(오름차순) 정렬

1
2
SELECT * FROM emp
ORDER BY ename;

3) 급여 내림차순으로 사원이름, 급여, 부서코드 조회

칼럼명 : ename, sal, deptno

1
2
3
SELECT ename, sal, deptno
FROM emp
ORDER BY sal DESC;

4) 부서코드 오름차순, 급여 내림차순으로 사원이름, 급여, 부서코드 조회

칼럼명 : ename, sal, deptno

1
2
3
SELECT ename, sal, deptno
FROM emp
ORDER BY deptno, sal DESC;

5) 직급 칼럼의 중복데이터를 하나씩만 조회

칼럼명 : job

1
SELECT DISTINCT(job) FROM emp;

6) 급여가 100보다 많고 400보다 적은 직원 조회

급여는 내림차순으로 정렬

1
2
3
4
SELECT *
FROM emp
WHERE sal>100 AND sal<400
ORDER BY sal DESC;

7) 급여가 100이하 또는 400 이상 직원 조회

급여 내림차순 정렬

1
2
3
4
SELECT *
FROM emp
WHERE sal<=100 OR sal>=400
ORDER BY sal DESC;

또는 NOT 연산자 사용

1
2
3
4
SELECT *
FROM emp
WHERE NOT(sal>100 AND sal<400)
ORDER BY sal DESC;

8) 직급이 과장 또는 부장인 직원들을 이름순으로 조회

칼럼명 : job , ename

1
2
3
4
SELECT *
FROM emp
WHERE job IN ('과장','부장')
ORDER BY ename

9) 부서코드가 30인 직원 조회

칼럼명 : deptno

1
2
3
SELECT *
FROM emp
WHERE deptno=30;

중복된 부서코드 한개씩만 조회

1
2
SELECT DISTINCT(deptno)
FROM emp;

10) 부서코드가 10 또는 20 또는 30인 직원 조회

OR, IN연산자 각각 활용해보기

칼럼명 : deptno

OR 연산자

1
2
3
SELECT *
FROM emp
WHERE deptno=10 OR deptno=20 OR deptno=30;

IN 연산자

1
2
3
SELECT *
FROM emp
WHERE deptno IN (10,20,30);

11) 급여가 300~500인 직원을 급여순으로 조회

AND, BETWEEN 연산자 활용

칼럼명 sal

AND 연산자

1
2
3
SELECT *
FROM emp
WHERE sal>=300 AND sal<=500;

BETWEEN 연산자

1
2
3
SELECT *
FROM emp
WHERE sal BETWEEN 300 AND 500;

12) 이름 조회하기

칼럼명 : ename

김씨성 조회하기

1
2
3
SELECT *
FROM emp
WHERE ename LIKE '김%';

이름에 ‘화’ 포함된 직원 조회

1
2
3
SELECT *
FROM emp
WHERE ename LIKE '%화%';

13) 부서코드가 20인 레코드에 대해서 급여의 1%를 커미션으로 책정하기

칼렴명 : deptno , comm(커미션) , sal

1
2
3
UPDATE emp
SET comm=sal*0.01
WHERE deptno=20;

14) 연봉을 구한 후 이름, 급여, 커미션, 연봉 조회

연봉 구하는 식 : 급여(sal)*12개월+보너스(comm)

1
2
3
4
5
SELECT ename
	   ,sal
	   ,comm
	   ,sal*12+comm
FROM emp;

커미션이 NULL이면 0으로 바꾼 후 연봉을 다시 계산하여 이름, 급여, 커미션, 연봉 조회

1
2
3
4
5
SELECT ename
	   ,sal
	   ,comm
	   ,sal*12+NVL(comm,0)
FROM emp;

15) 각 사람의 급여를 조회해서 ‘누구누구의 급여는 얼마입니다’로 조회하기

|| 결합연산자 : 자바에서의 + 연산자라고 생각하면됨 (연결연산자)

1
2
SELECT ename || '의 급여는' || sal || '입니다'
FROM emp;

16) SQL문 분석하기

문제

1
2
3
4
SELECT empno,sal
FROM emp
WHERE NOT(sal>200 AND sal<300)
ORDER BY sal;

분석

1
2
3
4
emp테이블에서
급여가 (200 초과이면서 300 미만)아닌
> 결론은 200 이하 또는 300 이상의
직원의 이름과 급여를 급여 오름차순으로 조회

문제

1
2
3
4
SELECT empno,sal
FROM emp
WHERE not sal>200 AND sal<300
ORDER BY sal;

분석

1
2
3
4
emp테이블에서 
급여가 200 초과가 아니면서(NOT이 sal>200만 적용), 급여가 300 미만인 
직원의 이름과 급여를 급여 오름차순으로 
조회한다.

연산자 우선 순위

  • () 괄호 : 연산자 우선순위보다 우선
  • 1 순위 : 비교연산자 , SQL연산자, 산수연산자
  • 2 순위 : NOT
  • 3 순위 : AND
  • 4 순위 : OR
  • 5 순위 : 결합연산자

17) emp테이블에서 입사일이 2005년1월1일 이전인 사원에 대해 이름, 입사일,부서번호를 입사일 순으로 조회

칼럼명 : hiredate , ename, deptno

1
2
3
4
SELECT ename, hiredate, deptno
FROM emp
WHERE hiredate<TO_DATE('20050101')
ORDER BY hiredate;

또는

1
2
3
4
SELECT ename, hiredate, deptno
FROM emp
WHERE hiredate<'20050101'
ORDER BY hiredate;

18) emp테이블에서 부서번호가 20번이나 30번인 부서에 속한 사원들에 대하여 이름,직업코드,부서번호를 이름순으로 조회

OR, IN연산자 각각 활용해서 모두 조회

칼럼명 : ename, job, deptno

IN 연산자

1
2
3
4
SELECT ename, job, deptno
FROM emp
WHERE deptno IN (20,30)
ORDER BY ename;

OR 연산자

1
2
3
4
SELECT ename, job, deptno
FROM emp
WHERE deptno=20 OR deptno=30
ORDER BY ename;

GROUP BY 연습

1) 부서코드별 급여합계를 구해 1500이상만 조회

실행 순서상 에러가 날 수 있다.

1
2
3
4
SELECT deptno,SUM(sal) AS 급여합계
FROM emp
GROUP BY deptno
HAVING SUM(sal)>=1500;

2) 급여가 300이상 데이터중 부서코드별 급여평균 400 이상 구하기

급여순으로 조회

1
2
3
4
5
6
SELECT deptno,AVG(sal)
FROM emp
WHERE sal>=300
GROUP BY deptno
HAVING AVG(sal)>=400
ORDER BY AVG(sal);

3) 부서별 급여총액이 가장 높은 급여총액과 가장 낮은 급여총액, 급여총액의 평균금액 출력

급여총액은 급여+커미션

출력할때 반올림해서 소수점 1자리까지 출력하기

1
2
3
4
5
6
7
SELECT deptno
	  ,ROUND(MAX(sal+NVL(comm,0)),1)
	  ,ROUND(MIN(sal+NVL(comm,0)),1)
	  ,ROUND(AVG(sal+NVL(comm,0)),1)
FROM emp
GROUP BY deptno
ORDER BY deptno;

4) hiredate칼럼을 사용해 월별로 입사한 인원수 구하기

TO_CHAR(날짜, ‘출력형식’)

날짜자료형의 데이터를 문자변환 함수

1
2
3
4
5
SELECT TO_CHAR(hiredate, 'mm')
	   ,COUNT(*)
FROM emp
GROUP BY TO_CHAR(hiredate, 'mm')
ORDER BY TO_CHAR(hiredate, 'mm');