[Oracle] 기본적인 SQL

1. BETWEEN 조건식

한식의 값이 다른 두식으로 정의된 간격에 있는지 여부를 결정

함수이므로 관계 연산자가 더 효율적 

형식

컬럼명 BETWEEN 값 AND 값

 

사용 예

-- emp 테이블 : sal가 2000000~3000000 사이 인 자료 중 name, sal 컬럼 출력
SELECT name, sal
FROM emp
WHERE sal BETWEEN 2000000 AND 3000000;

SELECT name, sal
FROM emp
WHERE sal>= 2000000 AND sal <= 3000000;


-- emp 테이블 : sal가 2000000~3000000을 제외한 자료 중 name, sal 컬럼 출력
SELECT name, sal
FROM emp
WHERE sal NOT BETWEEN 2000000 AND 3000000;

SELECT name, sal
FROM emp
WHERE sal <2000000 OR sal> 3000000;


-- emp 테이블 : hireDate가 2010년도인 자료중 name, hireDate 컬럼 출력
SELECT name, hireDate
FROM emp
WHERE hireDate BETWEEN '2010-01-01' AND '2010-12-31';

SELECT name, hireDate
FROM emp
WHERE TO_CHAR(hireDate, 'YYYY') = 2010;

 

2. IN 조건식

list에 있는 값 중 하나라도 일치하면 TRUE

-- emp 테이블 : city가 '서울', '인천', '경기' 인  자료 중 name, city 컬럼 출력
SELECT name, city
FROM emp
WHERE city = '서울' OR city = '경기' OR city = '인천';

SELECT name, city
FROM emp
WHERE city IN ('서울', '경기', '인천');


-- emp 테이블 : city가 '서울', '인천', '경기' 를 제외한  자료 중 name, city 컬럼 출력
SELECT name, city
FROM emp
WHERE city NOT IN ('서울', '경기', '인천');


-- emp 테이블 : city와 pos가 '서울  이면서 '부장'이거나 '경기' 이면서 '과장' 인 자료 중 name,  city, pos 컬럼 출력
SELECT name, city, pos
FROM emp
WHERE (city ='서울' AND POS = '부장') OR (city ='경기' AND POS = '과장');

SELECT name, city, pos
FROM emp
WHERE (city, pos) IN (('서울', '부장'), ('경기','과장'));


-- emp 테이블 : city와 pos가 '서울' 이면서 '부장' 인 자료 중 name, city, pos 컬럼 출력(subquery)
SELECT name, city, pos 
FROM emp
WHERE (city = '서울' AND pos ='부장');

SELECT name, city, pos 
FROM emp
WHERE (city,pos) IN(('서울','부장'));

 

3.  LIKE 조건식

주어진 문자열이 패턴과 일치 하는지 여부 확인 

패턴 문자

% : 0문자 이상 공통 (null과는 일치할 수 X)
_ : 1문자 이상 공통 

-- emp 테이블 : name이 '김'씨인  자료 중 empNo, name 컬럼 출력
SELECT empNo, name, tel FROM emp
WHERE tel LIKE '김%';  -- 3이 존재하는 전화번호 


-- emp 테이블 : LIKE 예           
SELECT empNo, name, tel FROM emp
WHERE tel LIKE '%3%'; -- 3이 존재하는 전화번호 

SELECT empNo, name, tel FROM emp
WHERE tel LIKE '%3'; -- 3으로 끝나는 전화번호 

SELECT empNo, name, tel, rrn FROM emp
WHERE rrn LIKE '_0%'; -- 생년월일에서 년도가 10의 배수인 사람 


-- 전화번호에 3또는 5가 존재하는 경우(empNo, name, tel)
SELECT empNo, name, tel FROM emp
WHERE tel LIKE '%3%' OR '%5%' ; 


-- 주민번호(rrn)를 이용하여 2월에 출생한 서울, 경기 사람(empNo, name, rrn, city)

SELECT empNo, name, rrn, city FROM emp
WHERE rrn LIKE '__02%' AND (city ='서울' OR  city ='경기' ); 

   
-- 성씨가 'ㄱ' 인 모든 이름 출력(나씨는 출력 안됨)
SELECT name FROM emp
WHERE name > '가' AND name < '나';

ESCAPE : '%' 나 '_' (패턴 문자)가 포함된 데이터를 추출하기 위해 활용

WITH : 반복적으로 사용되는 쿼리를 블럭화 할 경우 사용
 

WITH tb AS (
	SELECT '가가가' name, '우리_나라' content  FROM dual
		UNION ALL
	SELECT '나나나' name, '자바%스프링' content  FROM dual
    	UNION ALL
	SELECT '다다다' name, '우리나라' content  FROM dual
		UNION ALL
	SELECT '라라라' name, '모바일' content  FROM dual
		UNION ALL
	SELECT '마마마' name, '안드로이드%모바일' content  FROM dual
    ) 
	SELECT * FROM  tb;

-- content 컬럼에서 %를 검색 
 WITH tb AS (
 	SELECT '가가가' name, '우리_나라' content  FROM dual
		UNION ALL
	SELECT '나나나' name, '자바%스프링' content  FROM dual
		UNION ALL
	SELECT '다다다' name, '우리나라' content  FROM dual
		UNION ALL	
    SELECT '라라라' name, '모바일' content  FROM dual
		UNION ALL
	SELECT '마마마' name, '안드로이드%모바일' content  FROM dual
    ) 
    SELECT * FROM  tb 
    WHERE content LIKE '%#%%' ESCAPE '#';

WITH tb AS (
	SELECT '가가가' name, '우리_나라' content  FROM dual
    	UNION ALL
	SELECT '나나나' name, '자바%스프링' content  FROM dual
		UNION ALL
	SELECT '다다다' name, '우리나라' content  FROM dual
  		UNION ALL
	SELECT '라라라' name, '모바일' content  FROM dual
		UNION ALL
	SELECT '마마마' name, '안드로이드%모바일' content  FROM dual
	) 
    SELECT * FROM  tb 
    WHERE INSTR(content, '%')>=1;

 

4. NULL

오라클에서는 null과 '' 는 모두 null

-- 주의사항 
SELECT 10 + NULL FROM dual; -- null 

-- emp 테이블 : tel이 NULL 인 자료 중 name, tel 컬럼 출력
SELECT name, tel FROM emp;
SELECT name, tel FROM emp WHERE tel = NULL: -- 잘못된 표현
SELECT name, tel FROM emp WHERE tel IS NULL: 

-- emp 테이블 : tel이 NULL 아닌 자료 중 name, tel 컬럼 출력
SELECT name, tel FROM emp WHERE tel is NOT NULL:

 

5. CASE 표현식(Expressions)

IF~THEN~ELSE 논리와 유사한 방식으로 표현식을 작성해서 SQL의 비교 연산 기능을 보완 하는 역할

 

형식1  간단한 CASE 표현식

 SELECT empNo, name, rrn,
 	CASE SUBSTR(rrn, 8, 1)
		WHEN '1' THEN '남자'  -- WHEN 1 THEN '남자' 라고 코딩하면 오류
		WHEN '2' THEN '여자'
		WHEN '3' THEN '남자'
		WHEN '4' THEN '여자'
	END AS 성별
FROM emp;

SELECT empNo, name, rrn,
	CASE MOD(SUBSTR(rrn, 8, 1), 2)
		WHEN 0 THEN '여자'
		WHEN 1 THEN '남자'
	END AS 성별
FROM emp;

형식2  조건 CASE 표현식

-- empNo, name, sal, bonus, 총급여, 세금
-- 세금은 총급여가 300만원이상이면 3%
-- 세금은 총급여가 200만원이상이면 2%
-- 나머지 세금은 0

SELECT empNo, name, sal, bonus, (sal+bonus) tot,
	CASE
		WHEN (sal+bonus) >= 3000000 THEN (sal+bonus) * 0.03
		WHEN (sal+bonus) >= 2000000 THEN (sal+bonus) * 0.02
		ELSE 0
	END 세금
FROM emp;

 

 

6. DECODE 함수

 

DECODE(a, 'b', 1, 2) : a항목 값이 'b' 이면 1, 그렇지 않으면 2를 반환

DECODE(a, 'b', 1) : a항목 값이 'b' 이면 1, 그렇지 않으면 null을 반환

DECODE(a, 'b', 1, 'c', 2) :  a항목 값이 'b' 이면 1, 'c' 이면 2, 그렇지 않으면 null을 반환

 DECODE(a, 'b', 1, 'c', 2, 3) : a항목 값이 'b' 이면 1, 'c' 이면 2, 그렇지 않으면 3 을 반환

SELECT empNo, name, rrn,
	DECODE(SUBSTR(rrn, 8, 1), 1, '남자') 
FROM emp;  -- 성별이 1이면 남자 그렇지 않으면 null

SELECT empNo, name, rrn,
	DECODE(SUBSTR(rrn, 8, 1), 1, '남자', 2, '여자', 3, '남자', 4, '여자', '외국인')
FROM emp;

SELECT empNo, name, rrn,
	DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자') 성별
FROM emp;

7. SELECT 구문의 옵션

ALL 옵션 - 모든 행 반환, 생략 가능 
SELECT ALL dept FROM emp;
SELECT dept FROM emp;

DISTINCT | UNIQUE 옵션 - 중복적인 행은 하나만 출력 
SELECT DISTINCT dept FROM emp;
SELECT UNIQUE dept FROM emp;

SELECT DISTINCT dept, pos FROM emp;
-- 영업부 부장 
-- 영업부 과장

 

8. SELECT 구문의 ORDER BY 절

--sal 오름차순
SELECT name, dept, sal, bonus, sal + bonus pay 
FROM emp ORDER BY sal; 

--sal 오름차순 (ASC 생략 가능)
SELECT name, dept, sal, bonus, sal + bonus pay 
FROM emp ORDER BY sal ASC;  

--sal 내림차순
SELECT name, dept, sal, bonus, sal + bonus pay 
FROM emp ORDER BY sal DESC;  

-- dept 오름차순 정렬하고 dept 가 같으면 sal 내림차순 정렬
SELECT name, dept, sal, bonus, sal + bonus pay 
FROM emp ORDER BY dept, sal DESC ;  

-- dept 내림차순 정렬하고 dept 가 같으면 sal 오름차순 정렬
SELECT name, dept, sal, bonus, sal + bonus pay 
FROM emp ORDER BY dept DESC, sal ; 

-- dept 내림차순 정렬하고 dept 가 같으면 sal 내림차순 정렬
SELECT name, dept, sal, bonus, sal + bonus pay 
FROM emp ORDER BY dept DESC, sal DESC ; 

-- dept 오름차순 정렬하고 dept 가 같으면 sal 오름차순 정렬
SELECT name, dept, sal, bonus, sal + bonus pay 
FROM emp ORDER BY dept, sal; 

-- sal + bonus 내림차순 
SELECT name, dept, sal, bonus, sal + bonus pay 
FROM emp ORDER BY pay DESC ;

구문분석

FROM 절 -> WHERE절 -> SELECT절 -> ORDER BY
따라서 SELECT 절의 별명은 ORDER BY에서 사용 가능 

-- sal + bonus 내림차순 : 숫자로 컬럼의 위치를 지정하여 정렬 
SELECT name, dept, sal, bonus, sal + bonus pay
FROM emp ORDER BY 5 DESC;


-- 남자중 city 오름차순 정렬하고 city 가 같으면 남자를 먼저 출력 : name, rrn, dept, sal
SELECT name, rrn, city, sal
FROM emp 
WHERE MOD(SUBSTR(rrn, 8, 1),2) = 1
ORDER BY city, sal DESC;
     
SELECT name, rrn, city, sal
FROM emp 
WHERE SUBSTR(rrn, 8, 1) IN (1,3,5,7,9)
ORDER BY city, sal DESC;


-- dept 가 개발부인 사람중 남자를 먼저 출력하고 성별이 같으면 급여 내림차순  : name, rrn, dept, sal 
SELECT name, rrn, dept, sal 
FROM emp 
WHERE dept ='개발부'
ORDER BY MOD(SUBSTR(rrn, 8, 1),2) DESC, sal DESC;

SELECT name, rrn, dept, sal, MOD(SUBSTR(rrn, 8, 1),2) 성별
FROM emp 
WHERE dept ='개발부'
ORDER BY 성별 DESC, sal DESC;
     

-- dept 오름차순 정렬하고 dept가 같으면 남자를 먼저 출력: name, rrn, dept, sal 
SELECT name, rrn, dept, sal 
FROM emp 
ORDER BY dept DESC, MOD(SUBSTR(rrn, 8, 1),2) DESC;


-- dept는 영업부를 먼저 출력 : name, dept
-- 오름차순 정렬에서는 NULL이 나중에 출력 
SELECT name, tel FROM emp;
SELECT name, tel FROM emp ORDER BY tel;

SELECT name, dept, CASE WHEN dept = '영업부' THEN 0 END
FROM emp;

SELECT name, dept
FROM emp
ORDER BY CASE WHEN dept = '영업부' THEN 0 END; -- 오름차순에서 NULL은 가장 마지막에

 

9. SQL 문제

pos 가 부장, 과장, 대리, 사원순으로 출력 : name, dept, pos

SELECT name, dept, pos
FROM emp
ORDER BY CASE
         WHEN pos = '부장' THEN 1 
         WHEN pos = '과장' THEN 2
         WHEN pos = '대리' THEN 3
         WHEN pos = '사원' THEN 4        
         END;

SELECT name, dept, pos 
FROM emp
ORDER BY DECODE(pos, '부장', 1, '과장', 2, '대리', 3, '사원', 4);

여자를 먼저 출력하고 성별이 동일하면 sal 내림차순 : name, rrn, sal

SELECT name, rrn, sal
FROM emp
ORDER BY MOD(SUBSTR(rrn, 8,1),2), sal DESC;

서울 사람중에서 기본급+수당 내림차순으로 정렬 : name, city, sal+bonus

SELECT name, city, sal+bonus
FROM emp
WHERE city = '서울'
ORDER BY sal+bonus DESC;

SELECT name, city, sal+bonus pay
FROM emp
WHERE city = '서울'
ORDER BY sal+bonus DESC;

 

 

여자중 부서오름차순으로 정렬하고 부서가 같으면 기본급 내림차순 정렬 : name, rrn, dept, sal

SELECT name, rrn, dept, sal
FROM emp
WHERE MOD(SUBSTR(rrn, 8,1), 2) = 0
ORDER BY dept, sal DESC;

전화번호가 NULL 인 데이터를 먼저 출력

SELECT name, tel FROM emp ORDER BY tel DESC;

 

전화번호가 NULL 인 데이터를 나중에 출력

SELECT name, tel FROM emp ORDER BY tel;

 

랜덤하게 5개 추출(이벤트 당첨등)
SELECT empNo, name, DBMS_RANDOM.VALUE
FROM emp; 

SELECT empNo, name
FROM emp
ORDER BY DBMS_RANDOME.RANK;

SELECT * FROM emp (
SELECT empNo, name 
FROM emp
WHERE DBMS_RANDOME.RANK;
) WHERE ROWNUM <=5;


SELECT empNo, name (
FROM emp
ORDER BY DBMS_RANDOME.RANK
FETCH FIRST 5 ROWS ONLY; --12C이상에서만 가능 

 

10. 집합 연산자(Set Operators)

- UNION  합집합 교집합은 한번만 출력

SELECT name, city, dept FROM emp WHERE dept = '개발부'
	UNION
SELECT name, city, dept FROM emp WHERE city = '인천';

- UNION ALL  합집합 중복적인 데이터도 출력

SELECT name, city, dept FROM emp WHERE dept = '개발부'
	UNION ALL
SELECT name, city, dept FROM emp WHERE city = '인천';

 - MINUS  차집합 (첫 번째 SQL문 결과에 있고, 두 번재 SQL 문의 결과에는 없는 데이터를 출력

SELECT name, city, dept FROM emp WHERE dept = '개발부'
	MINUS
SELECT name, city, dept FROM emp WHERE city = '인천';

 - INTERSECT 교집합 두 번째 SQL문의 결과와 첫 번째 SQL문의 결과에 중복된 행만 출력

SELECT name, city, dept FROM emp WHERE dept = '개발부'
	INTERSECT
SELECT name, city, dept FROM emp WHERE city = '인천';

11. pseudo 컬럼(의사 컬럼)

오라클에서 내부적으로 사용되는 컬럼

테이블 열처럼 동작하지만 실제로 테이블에는 저장 X

 ROWID : 행의 주소 

SELECT ROWID, empNo, name FROM emp;

ROWNUM : 쿼리 결과로 나오는 각각의 행들에 대한 순서 값을 나타내는 의사 컬럼
ROWNUM을 사용하여 행수를 제한할 수 있음

SELECT ROWNUM, empNo, name, dept, pos, sal FROM emp
WHERE ROWNUM <=10;

양의 정수보다 큰 ROWNUM 값에 대해서 항상 거짓 

SELECT empNo, name, dept, pos, sal FROM emp
WHERE ROWNUM >1; -- 한행도 출력X

- ORDER BY 절이 있는 경우 ROWNUM을 사용하는 경우 행 엑세스 방법에 따라 결과가 달라짐  - 사용 금지 

SELECT empNo, name, dept, pos, sal FROM emp
ORDER BY sal DESC;

SELECT empNo, name, dept, pos, sal FROM emp
WHERE ROWNUM <=10 
ORDER BY sal DESC; --의도와 다른 결과가 나온다

- ORDER BY 절이 있는 경우 ROWNUM을 사용할 경우에는 서브쿼리를 사용한다 

SELECT * FROM (
SELECT empNo, name, dept, pos, sal FROM emp
ORDER BY sal DESC
) WHERE ROWNUM <=10;

- sal 내림차순 정렬해서 6번째 10번째 데이터 출력 

SELECT * FROM (
	SELECT empNo, name, dept, pos, sal 
    	FROM emp
	ORDER BY sal DESC
)WHERE ROWNUM >= 6 AND ROWNUM <=10; -- 오류. 크다는 비교 불가능

 

SELECT * FROM (
	SELECT ROWNUM rnum, tb.* FROM (
    	SELECT empNo, name, dept, pos, sal
        FROM emp
     	ORDER BY sal DESC
	) tb WHERE ROWNUM <= 10
) WHERE rnum >= 6;