실습 : 초보자를 위한 DB2 SQL 사용 가이드

최시열·2023년 3월 30일

기본 쿼리

Q. 일반적인 조회

SELECT FIRSTNME, COMM, EDLEVEL, PHONENO FROM db2admin.employee

Q. 문자열 및 숫자 표시

SELECT firstnme, '는', 10, '''년''동안', firstnme, '의 ♥하는 friend' FROM db2admin.employe

Q. 널(NULL) 표시?

SELECT firstnme, NULL, null, Null, comm FROM db2admin.employee

Q. 특별 레지스터를 활용한 조회 결과

/* SELECT 절에서 시스템에서 제공하는 특별한 환경 변수
SELECT current_user FROM db2admin.employee
current_server, user, schema, date, time, timezone, timestamp */

SELECT '오늘은', current_time, firstnme, '은 열공중' FROM db2admin.employee

Q. 산술 연산자로 숫자 연산

SELECT firstnme, '지금 근속년수가', YEAR(current_date) - YEAR(hiredate), '10년 후에는', YEAR(current_date) - YEAR(hiredate) + 10 FROM db2admin.employee

Q. 문자열 연산자로 문자열 결합

SELECT firstnme || '는', YEAR(current_date)-YEAR(hiredate), '년 동안', firstnme || '의 ♥하는 친구' FROM db2admin.employee

Q. 날짜 연산자로 날짜 연산

SELECT firstnme, YEAR(current_date) - YEAR(hiredate), current_date + 1 FROM db2admin.employee

Q. 숫자 스칼라 함수 활용

SELECT firstnme, salary, (salary * 10000) / 1230, ROUND((salary*10000) / 1230, -2) from db2admin.employee

Q. 문자 스칼라 함수 활용

SELECT firstnme, length(firstnme), substr(firstnme, 2, length(firstnme)), right(firstnme, 1) from db2admin.employee

Q. 날짜 스칼라 함수 활용

SELECT firstnme, YEAR(current_date)-YEAR(hiredate), YEAR(hiredate) FROM db2admin.employee

Q. 변환 스칼라 함수 활용

SELECT firstnme, year(current_date)-year(hiredate), TO_CHAR(TO_DATE('20130301', 'YYYYMMDD') + 3 + (year(current_date)-year(hiredate)) / 5, 'YYYY-MM-DD') FROM db2admin.employee

Q. 기타 스칼라 함수 활용

SELECT firstnme, decode(sex, 'M', '남자', '여자'), salary, year(current_date)-year(hiredate), year(hiredate) FROM db2admin.employee

Q. 중복 값 제거

SELECT DISTINCT comm FROM db2admin.employee

Q. AS 절 활용

SELECT firstnme, comm, dec(trunc(nvl(comm,0)*0.03,1),5,1) as "커미션 3%" FROM db2admin.employee

Q. ORDER BY 절 활용

SELECT firstnme, salary, year(current_date) - year(hiredate) FROM db2admin.employee ORDER BY sex, length(firstnme), salary DESC

Q. FETCH FIRST 활용

SELECT firstnme, salary, comm, salary+comm FROM db2admin.employee ORDER BY 4 DESC FETCH FIRST 3 ROWS ONLY

조건 쿼리

지정된 조건식을 만족하는 레코드만을 검색하는 것

Q. 비교 연산자 이용

SELECT firstnme, workdept, salary, comm FROM db2admin.employee WHERE salary < 50000

Q. AND 연산자를 이용해 조건 연결

SELECT firstnme, workdept, salary, comm FROM db2admin.employee WHERE NOT left(workdept, 1) = 'D' AND salary >= 40000 AND comm < 3000 ORDER BY firstnme

Q. AND와 OR 연산자를 이용해 조건 연결

SELECT firstnme, workdept, salary, comm FROM db2admin.employee WHERE (left(workdept, 1) = 'C' AND salary >= 40000) OR (NOT left(workdept, 1) = 'D' AND comm < 3000)

Q. IN 연산자를 이용해 값 비교

SELECT firstnme, workdept, salary, comm FROM db2admin.employee WHERE left(workdept, 1) IN ('A', 'C', 'D') AND salary >= 50000  AND comm >= 2000 ORDER BY comm DESC

Q. NOT 연산자와 IN 연산자를 이용해 값 비교

SELECT empno, firstnme, workdept, salary FROM db2admin.employee WHERE left(workdept, 1) NOT IN ('A', 'D') AND salary > 50000 ORDER BY firstnme

Q. BETWEEN 연산자를 이용하여 값 비교

SELECT firstnme, workdept, salary, year(current_date)-year(hiredate) as "YEARS" FROM db2admin.employee WHERE left(workdept, 1) IN ('A', 'D', 'C') AND salary BETWEEN 30000 AND 60000 AND year(current_date)-year(hiredate) > 20 ORDER BY year(current_date)-year(hiredate) DESC

Q. NOT, BETWEEN 연산자를 이용하여 값 비교

SELECT firstnme, salary, comm, year(current_date)-year(hiredate) as years
	FROM db2admin.employee
	WHERE salary NOT BETWEEN 35000 AND 50000
		AND comm >= 2000
		AND year(current_date)-year(hiredate) > 17
	ORDER BY years

Q. LIKE 연산자를 이용하여 값 비교

SELECT firstnme, workdept, salary
	FROM db2admin.employee
	WHERE (firstnme LIKE '%E'
		OR firstnme LIKE '%Y')
		AND year(current_date)-year(hiredate) >= 15
		AND salary < 80000
	ORDER BY firstnme

Q. NOT, LIKE 연산자를 이용하여 값 비교

SELECT firstnme, workdept, salary
	FROM db2admin.employee
	WHERE firstnme NOT LIKE 'D%'
		AND year(current_date) - year(hiredate) >= 15
		AND salary < 80000
	ORDER BY firstnme

Q. IS NULL 연산자를 이용하여 값 비교

SELECT deptname, mgrno, admrdept
	FROM db2admin.department
	WHERE deptname NOT LIKE 'S%'
		AND mgrno IS NULL
	ORDER BY deptname

Q. NOT, IS NULL 연산자를 이용하여 값 비교

SELECT deptname, mgrno, admrdept
	FROM db2admin.department
	WHERE deptname NOT LIKE '%A%'
		AND mgrno IS NOT NULL
	ORDER BY deptname

Q. SELECT 절에 CASE 수식 활용

SELECT firstnme, year(current_date) - year(hiredate) as years, salary as current_salary,
	CASE
		WHEN salary <= 50000 AND EDLEVEL < 18 THEN salary * 1.1
		ELSE salary
	END for_salary
	FROM db2admin.employee
	ORDER BY 4

그룹 쿼리

Q. 그룹 함수의 인수로 스칼라 함수 이용

SELECT count(*) as 전체
		, count(workdept) as 부서
		, sum(decode(sex, 'M', 1, 0)) as 남자
		, sum(decode(sex, 'F', 1, 0)) as 여자
	FROM db2admin.employee

Q. 그룹 함수의 인수로 CASE 문 이용

SELECT count(workdept) as 전체
		, sum(
			CASE
				WHEN year(current_date) - year(hiredate) >= 20 THEN 3
				WHEN year(current_date) - year(hiredate) >= 15 THEN 2
			ELSE 1
			END
		) as 주문
	FROM db2admin.employee

Q. 그룹 별로 그룹 함수 적용

SELECT
	workdept 부서, count(workdept) 인원, avg(salary) "평균 급여", max(salary) "최고 급여", avg(comm) "평균 커미션", max(comm) "최대 커미션"
	FROM db2admin.employee
	GROUP BY workdept
	ORDER BY workdept

Q. 그룹 기준 필드로 스칼라 함수로 이용

SELECT
	sex, count(sex), avg(salary), min(salary)
	FROM db2admin.employee
	WHERE midinit <> ' '
	GROUP BY sex

Q. 그룹 함수의 인수로 스칼라 함수와 CASE문 사용

SELECT
	workdept 부서, count(*) 부서원,
	sum(decode(sex, 'M', 1, 0)) 남자,
	sum(decode(sex, 'F', 1, 0)) 여자,
	sum(
		CASE
			WHEN year(current_date) - year(hiredate) > 20 THEN 1
			ELSE 0
		END
		) "20년 이상 근무"
	FROM db2admin.employee
	GROUP BY workdept

Q. 그룹 함수를 이용한 조건식으로 그룹 선택

SELECT
	workdept 부서, count(*) 부서원, avg(salary) 평균급여, max(salary) 최고급여, min(salary) 최저급여, max(salary)-min(salary) 차이
	FROM db2admin.employee
	GROUP BY workdept
	HAVING max(salary) - min(salary) >= 30000
	ORDER BY workdept

조인쿼리

Q. 두 개의 테이블 교차 조인

SELECT e.firstnme, e.salary, year(current_date)-year(e.hiredate), p.picture,
	CASE
		WHEN e.salary >= 60000 THEN 2
		ELSE 1
	END qty
	FROM EMPLOYEE e, EMP_PHOTO p
	WHERE e.salary >= 40000
		AND year(current_date)-year(e.hiredate) > 20
	ORDER BY e.firstnme, p.photo_format

Q. 두 개의 테이블 내부 조인

SELECT p.projno, p.actno, e.salary, e.firstnme
	FROM EMPLOYEE e INNER JOIN EMPprojact p ON e.empno = p.empno
	WHERE e.salary > 50000 AND EMPTIME = 1
	ORDER BY p.actno DESC, e.salary

Q. 세 개의 테이블 내부조인

SELECT e.firstnme, e.workdept, e.salary, p.projno, m.div
	FROM EMPLOYEE e INNER JOIN EMPprojact p ON e.empno = p.empno
					INNER JOIN empmdc m ON e.empno = m.empno 
	WHERE e.salary > 50000
	ORDER BY e.firstnme, p.projno

Q. 두 개의 테이블을 왼쪽 기준으로 외부 조인

SELECT a.firstnme 사원명, a.workdept 부서명, b.projno 프로젝트이름, a.salary 급여, year(current_date)-year(a.hiredate) 근속년수
	FROM db2admin.employee a LEFT JOIN db2admin.empprojact b ON a.empno = b.empno
	WHERE a.salary > 50000
	ORDER BY a.workdept, a.firstnme

Q. 세 개의 테이블을 왼쪽 기준으로 외부 조인

SELECT a.firstnme 사원명, a.salary 급여, year(current_date)-year(a.hiredate) 근속년수, NVL(b.projno, '*') 프로젝트이름, NVL(c.actno, 0) 퍼포먼스
	FROM db2admin.employee a LEFT JOIN db2admin.empprojact b ON a.empno = b.empno
							 LEFT JOIN db2admin.projact c ON b.projno = c.projno
	WHERE a.salary > 60000 AND year(current_date)-year(a.hiredate) < 20
	ORDER BY a.firstnme, b.projno

최시열

이전 포스트