[데이터베이스] SELECT - 2

1. sysdate 활용해서 현재 시간,분,초, (1/100초 or 1/1000초) 까지 표현하는 SQL 작성 
- SQL 암기해서 손으로 작성해야 함. SQL 문장이 실행돼야 함.

 

답:

SELECT TO_CHAR(systimestamp, 'YYYY/MM/DD HH24:MI:SS:FF2') FROM DUAL;

SELECT TO_CHAR(systimestamp, 'YYYY/MM/DD HH24:MI:SS:FF3') FROM DUAL;

 

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

2. 다음의 집합 연산자 UNION, UNION ALL,INTERSECT, MINUS를 공부한후 각각의 예제 SQL을 만든후 결과가 왜 정렬되는지 설명 하십시오.

 

- 집합연산: UNION, UNION ALL, INTERSECT, MINUS (여기 참고)

UNION: 합집합과 같은 개념, 중복을 제거한 합 (INTERSECT, MINUS도 중복 제거)

UNION ALL: 'A와B의 합집합' + 'A와B의 교집합', 중복을 포함한 합

INTERSECT: A와 B의 교집합

MINUS: A와 B의 차집합

 

- 집합연산의 특징

>집합 연산을 할 때는 SELECT 문의 컬럼 개수와 타입이 일치해야 한다.

>UNION, INTERSECT, MINUS 연산은 중복된 결과를 제거한 결과의 합이 검색된다.

>UNION, INTERSECT, MINUS 정렬돼서 나옴... sort 알고리즘이 돌아갔기 때문?

>컬럼의 헤더는 앞 select문 따라가

>ORDER BY 절을 사용할 때는 문장의 제일 마지막에 사용한다.

>성능의 관점으로 보면, UNION ALL은 중복을 제거하는 작업을 하지 않기 때문에 UNION ALL 연산이 성능이 더 좋다.

대신 UNION ALL은 정렬을 명시해줘야 한다.(ORDER BY)

 

ex)

SELECT EMPNO, ENAME FROM EMP UNION SELECT EMPNO, ENAME FROM EMP_S;
SELECT EMPNO, ENAME FROM EMP UNION ALL SELECT EMPNO, ENAME FROM EMP_S order by empno;
SELECT EMPNO, ENAME FROM (SELECT EMPNO, ENAME FROM EMP ORDER BY EMPNO) UNION ALL SELECT EMPNO, ENAME FROM (SELECT EMPNO, ENAME FROM EMP_S ORDER BY EMPNO);

SELECT EMPNO, ENAME FROM EMP INTERSECT SELECT EMPNO, ENAME FROM EMP_S;
SELECT EMPNO, ENAME FROM EMP_S MINUS SELECT EMPNO, ENAME FROM EMP;
SELECT EMPNO, ENAME FROM EMP MINUS SELECT EMPNO, ENAME FROM EMP_S;

 

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

3. Interactive SQL 과 Embeded SQL를 설명 하고 각각의 사용예를 찾아서 기록하고 해석 하십시오. (여기 참고)

 

-Interactive SQL: SQLDeveloper와 같은 클라이언트에서 요청사항을 입력했을 때 즉각적으로 결과값이 나오는 SQL 사용방법

빠른 SQL 기능 검증 가능

 

-Embeded SQL(내장 SQL): C/C++, 코볼, 자바 같은 프로그래밍 언어에서 SQL을 사용하는 방법

MySQL은 임베디드 SQL을 지원하지 않는다.

 

 

> Interactive SQL과 Embedded SQL 예시

Interactive SQL 방식

:employee 테이블에서 city가 boston인 조건 하에 emp_lname, emp_fname 을 조회한다.

 

 

Embedded SQL 방식

exec: sql 시작

emp_lname과 emp_fname 열을 조회할거다 

into ?

employee 테이블에서

city = city_in을 만족하는 조건의 ?

sql문을 끝낸다

 

 

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

4.

 

CASE 과제

 

답

SELECT DEPTNO, ENAME, JOB, SAL, CASE 
WHEN DEPTNO = 10 THEN TRUNC(DECODE(SAL,NULL,0,SAL * 0.003) )
WHEN DEPTNO = 20 THEN TRUNC(DECODE(SAL,NULL,0,SAL * 0.2) )
WHEN DEPTNO = 30 THEN TRUNC(DECODE(SAL,NULL,0,SAL * 0.1) )
ELSE TRUNC(DECODE(SAL,NULL,0,SAL * 0.01) )
END AS BONUS
FROM EMP
ORDER BY DEPTNO, BONUS DESC;

 

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

5.

 

ROWNUM 과제

 

답

SELECT DEPTNO, ENAME, SAL
FROM (SELECT DEPTNO, ENAME, SAL FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM <= 5;

 

 

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

6.

 

YY와 RR의 차이점 과제

 

여기 참고

 

SELECT ENAME, HIREDATE, SAL
FROM EMP
WHERE HIREDATE between TO_DATE('81/02/20','YY/MM/DD') and TO_DATE('82/12/09','YY/MM/DD');

위 문장의 결과값이 안 나오는 이유?

 

>YY, RR은 년도의 뒷 두자리 의미

>YY: 현재 세기 기준으로 인식 (ex. 19 => 2019로 인식)

>RR: 50을 기준으로 현 세기, 전 세기로 구분해서 인식

 

>RR 문법

현재 년도가 0~49 인데... 입력 연도가 0~49 => 현재 세기

                                 입력 연도가 50~99 => 이전 세기

현재 년도가 50~99 인데... 입력 연도가 0~49 => 다음 세기

                                  입력 연도가 50~99 => 현재 세기

 

현재 세기 범위와 입력 연도 범위가 같으면 => 현재 세기

다르면...'이전 세기' 혹은 '다음 세기'

 

>YYYY 혹은 RRRR : 입력하는 년도 그대로 인식

 

답: 오라클은 기본적으로 rr방식을 채택한다...그러면 81,82을 이전 세기로 인식해서 1981~1982 범위로 인식.

그런데 yy포맷으로 데이터 타입 변환을 해버리면 2081, 2082로 인식해버리고, 2081~2082에 해당하는 데이터는 없으므로 결과값이 없는 것.

 

>test code

select deptno, ename, sal, hiredate,
case when hiredate >= to_date('2000/05/05') then 'yy' -- yy
else
'rr' --rr
end
from emp;