1월 10일 국비교육 30일차

Q1. 부서번호가 10, 20번인 사원의 이름, 입사일, 상세 입사일을 다음과 같이 출력해보자.

SQL> SELECT ENAME AS 이름, HIREDATE AS 기본입사일,

2 TO_CHAR(HIREDATE, 'YYYY/MM/DD HH24:MI:SS') AS 상세입사일

3 FROM EMP WHERE DEPTNO IN(10, 20);

SQL> SELECT ENAME 이름, HIREDATE AS 기본입사일,

2 TO_CHAR(HIREDATE, 'YYYY/MM/DD HH"시" MI"분" SS"초"') AS 상세입사일

3 FROM EMP WHERE DEPTNO IN(10, 20);

SQL> CREATE TABLE TEST02(

2 MYNAME VARCHAR2(20),

3 MYDY DATE);

TO_DATE 함수

SELECT TO_DATE ('20100101', 'YYYYMMDD') FROM DUAL;

SELECT TO_CHAR('20100101' 'YYYY, MON') FROM DUAL; -> 오류

SELECT TO_CHAR( TO_DATE( '20100101', 'YYYYMMDD'), 'YYYY, MON') FROM DUAL; -> 2010, 1월

SELECT TO_DATE('041030 143000', 'YYMMDD HH24MISS') FROM DUAL;

SELECT TO_CHAR( TO_DATE( '041030 143000', 'YYMMDD HH24MISS'), 'DD-MON-YY HH:MI:SS PM') FROM DUAL;

SELECT TO_DATE('980630', 'YYMMDD') FROM DUAL;

SELECT TO_CHAR(TO_DATE('980630', 'YYMMDD'), 'YYYY.MM.DD') FROM DUAL;

TO_CHAR 패턴이 안맞으면 오류가 발생

TO_DATE 웬만하면 연 월일을 뽑아냄

SQL> SELECT MYDAY, TO_CHAR(MYDAY, 'YYYY-MM-DD')

2 FROM TEST02;

현재 년도 (뒤의 두 자리)	입력하는 년도 (뒤의 두 자리)	결과
00~49	00~49	현재 세기
00~49	50~99	전 세기
50~99	00~49	다음 세기
50~99	50~99	현재 세기

현재 년도	입력 년도	RR 형식 결과	YY 형식 결과
1994	95	1995	1995
1994	17	2017	1917
2001	17	2017	2017
2048	52	1952	2052
2051	47	2047	2047

#10

SELECT '2009/10/14' AS 현재, '95/10/27' AS 입력, TO_CHAR(TO_DATE('95/10/27', 'YY/MM/DD'), 'YYYY/MM/DD') AS YY형식1

SELECT '2009/10/14' AS 현재, '95/10/27' AS 입력, TO_CHAR(TO_DATE('95/10/27', 'YY/MM/DD'), 'RRRR/MM/DD') AS YY형식2

SELECT '2009/10/14' AS 현재, '95/10/27' AS 입력, TO_CHAR(TO_DATE('95/10/27', 'RR/MM/DD'), 'YYYY/MM/DD') AS RR형식1

SELECT '2009/10/14' AS 현재, '95/10/27' AS 입력, TO_CHAR(TO_DATE('95/10/27', 'RR/MM/DD'), 'RRRR/MM/DD') AS RR형식2

FROM DUAL;

#11

--QUESTION

--1. 사원 테이블에서 사원이름을 첫글자는 대문자로, 나머지는 소문자로 출력하라

SELECT INITCAP(ENAME) FROM EMP;

--2. 사원테이블에서 사원이름을 뽑고 또 이름의 두번째 글자부터 네번째 글자까지

-- 출력하라.

SELECT ENAME, SUBSTR(ENAME, 2, 3) FROM EMP;

--3. 사원테이블의 사원 이름의 철자 개수를 출력하라.

SELECT LENGTH(ENAME) FROM EMP;

--4. 사원테이블에서 사원 이름의 앞 글자 하나와 마지막 글자 하나만 출력하되

-- 모두 소문자로 각각 출력하라.

SELECT LOWER(SUBSTR(ENAME, 1, 1)), LOWER(SUBSTR(ENAME, -1, 1)) FROM EMP;

--5. 3456.78의 소수점 첫번째 자리에서 반올림하라.

SELECT ROUND(3456.78) FROM DUAL;

--6. 3의 4제곱을 구하고, 64의 제곱근을 구하라.

SELECT POWER(3, 4), SQRT(64) FROM DUAL;

--7. 오늘날짜와 오늘날짜에서 10일을 더한 날짜를 출력하라.

SELECT SYSDATE, SYSDATE+10 FROM DUAL;

--8. 국제 표준으로 현재 날짜를 출력하라.

--9. 사원테이블에서 사원이름과 사원들의 오늘 날짜까지의 근무일수를 구하라.

SELECT TRUNC(SYSDATE-HIREDATE) FROM EMP;

--10. 위 문제에서 근무일수를 00년 00개월 00일 근무하였는지

--확인할 수 있도록 변환하라.(단, 한 달을 30일로 계산하라)

-- 예)

-- | ENAME | 근무일수 |

-- | KING | 00년 00개월 00일 |

ⓐ TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12, 0) "년"

ⓑ TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12, 1) * 12, 0) - 월 구하기

-> ⓐ에서 구한 년수를 1로 나눈 나머지(소수점 이하 : 1년 이하의 년수)에 12(1년)를 곱하고 필요없는 0(일에 해당) 이하는 잘라버렸습니다.

-> ⓑ에서 구한 개월수를 1로 나눈 나머지(소수점이하 : 1개월 이하의 월수)에 365(1년)를 12로 나눈 30.4166667로 곱해주면 여기서 일수가 계산됨

--11. EMP 테이블에서 10번 부서의 사원에 대하여 담당 업무 중 좌측에 'A'를 삭제하고

급여 중 좌측의 1을 삭제하여 출력하여라.

-> 담당 업무 중 좌측의 'A'를 삭제 -> SUBSTR인줄 알았는데 TRIM 함수인듯 + 왼쪽이니까 LEADING 오른쪽이면? TRAILING

-> 급여 중 좌측의 1을 삭제 -> 마찬가지로 TRIM함수 LEADING인듯

SQL> SELECT ENAME, TRIM(LEADING 'A' FROM JOB), TRIM(LEADING '1' FROM SAL)

2 FROM EMP

3 WHERE DEPTNO = 10;

--12.EMP 테이블에서 10번 부서의 사원에 대하여 담당 업무 중 우측에 'T'를 삭제하고 급여 중 우측의 0을 삭제하여 출력하여라.

SQL> SELECT ENAME, TRIM(TRAILING 'T' FROM JOB), TRIM(TRAILING '0' FROM SAL)

2 FROM EMP

3 WHERE DEPTNO = 10;

--13.REPACE함수를 사용하여 사원이름에 SC문자열을 *?로 변경해서 조회.

SELECT REPLACE(ENAME, 'SC', '*?') FROM EMP;

--14.다음의 결과를 확인한다.

SELECT trunc(4567.678) 결과1, trunc(4567.678, 0) 결과2, trunc(4567.678, 2) 결과3, trunc(4567.678, -2) 결과4 FROM dual;

결과1 결과2 결과3 결과4

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

4567 4567 4567.67 4500

--15. EMP 테이블에서 급여를 30으로 나눈 나머지를 구하여 출력하여라.

SELECT MOD(SAL, 30) FROM EMP;

--16. EMP 테이블에서 현재까지의 근무일수가 몇 주 몇 일인가를 출력한다

ENAME HIREDATE SYSDATE Total Days WEEKS DAYS

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

SMITH 80/12/17 19/01/10 13903.469 1986 1

ALLEN 81/02/20 19/01/10 13838.469 1976 6

WARD 81/02/22 19/01/10 13836.469 1976 4

JONES 81/04/02 19/01/10 13797.469 1971 0

BLAKE 81/05/01 19/01/10 13768.469 1966 6

SELECT ENAME, HIREDATE, SYSDATE, (SYSDATE-HIREDATE) "TOTAL DAYS", TRUNC((SYSDATE-HIREDATE)/7) "WEEKS", TRUNC(MOD((SYSDATE-HIREDATE), 7)) "DAYS"

FROM EMP;

--17.EMP 테이블에서 10번 부서원이 현재까지의 근무 월수를 계산하여 출력한다

months_betweent사용

ENAME HIREDATE SYSDATE M_BETWEEN T_BETWEEN

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

CLARK 81/06/09 19/01/10 451.047396 451

KING 81/11/17 19/01/10 445.789332 445

MILLER 82/01/23 19/01/10 443.595783 443

SELECT ENAME, HIREDATE, MONTHS_BETWEEN(SYSDATE, HIREDATE) "M_BETWEEN",

TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) "T_BETWEEN"

FROM EMP

WHERE DEPTNO = 10;

--18. EMP 테이블에서 10번 30번 부서원의 입사 일자로부터 5개월이 지난 후 날짜를 계산하여 출력하여라. add_months 사용

ENAME HIREDATE A_MONTH

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

MILLER 82/01/23 82/06/23

JAMES 81/12/03 82/05/03

KING 81/11/17 82/04/17

MARTIN 81/09/28 82/02/28

TURNER 81/09/08 82/02/08

CLARK 81/06/09 81/11/09

BLAKE 81/05/01 81/10/01

WARD 81/02/22 81/07/22

ALLEN 81/02/20 81/07/20

SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 5) "A_MONTH"

FROM EMP

WHERE DEPT IN(10, 30);

--19.EMP 테이블에서 10번 부서원의 입사 일자로부터 돌아오는 금요일을 계산한다.

next_day 함수 로 한다.

ENAME HIREDATE N_6 N_7

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

MILLER 82/01/23 82/01/29 82/01/30

KING 81/11/17 81/11/20 81/11/21

CLARK 81/06/09 81/06/12 81/06/13

SELECT ENAME, HIREDATE,

FROM EMP

WHERE DEPTNO = 10;

--20. EMP 테이블에서 입사한 달의 근무 일수를 계산하여 조회한다. last_day 사용

EMPNO ENAME HIREDATE L_LAST L_DAY

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

7698 BLAKE 81/05/01 81/05/31 30

7902 FORD 81/12/03 81/12/31 28

7900 JAMES 81/12/03 81/12/31 28

7566 JONES 81/04/02 81/04/30 28

--21.EMP 테이블에서 10번 부서 중 입사 일자를 '1 MAY 1981'와 '1998년 1월 1일'의 형태로 조회한다.

ENAME HIREDATE T_HIREDATE T_KOR

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

MILLER 82/01/23 23 1월 1982 1982년 01월 23일

#12

1. SUBQUERY:

- SELECT, INSERT, UPDATE, DELETE문 또는 다른 SubQuery안에 들어가 있는 SELECT문을 말한다. 중첩된 SELECT 문이라는 뜻으로 Nested SELECT라고도 한다.

2. SubQuery의 사용규칙

- 모든 서브쿼리는 괄호로 싸여진다.

- 서브쿼리 안에 서브쿼리가 들어갈 수 있다.

이를 Nesting이라고 하며, 메모리가 허용하는 한 무제한으로 내장할 수 있다.

- 서브쿼리의 SELECT문에서는 하나의 컬럼값 또는 이를 응용한 하나의 표현식만 사용할 수 있다.

- 주쿼리에서 서브쿼리의 결과값을 조건으로 사용할 때, SOME, ANY, 또는 ALL 연산자를 사용하지 않는 일반적인 경우에는 서브쿼리에서는 하나의 레코드값만 리턴해야 한다. 대부분의 경우 서브쿼리에서는 GROUP BY, HAVING 문을 사용할 수 없다.

- 주 쿼리의 테이블 컬럼은 모두 서브쿼리에서 참조하여 사용할 수 있다.

- 서브쿼리에서 SELECT하지 않은 컬럼은 주 쿼리에서 사용할 수 없다.

3. SUBQUERY의 종류

ⓐ Single Row Subquery : 서브쿼리의 결과가 1개의 ROW인 경우

- >, >=, < , <=, = = , != , <>

ⓑ Multi Row Subquery : 서브쿼리의 결과가 여러 Row인 경우

- IN, NOT IN, >ANY, <ANY, >ALL, <ALL

Q1. 'SALESMAN'인 사원들과 같은 월급을 받는 사원의 이름과 월급을 출력하자.

SELECT ENAME, SAL

FROM EMP

WHERE SAL = (SELECT SAL

FROM EMP

WHERE JOB = 'SALESMAN');

Q2. 부서번호가 10번인 사람들과 같은 월급을 받는 사원의 이름과 월급을 출력하자.

SELECT ENAME, SAL

FROM EMP

WHERE SAL IN (SELECT SAL

FROM EMP

WHERE DEPTNO = 10);

Q3. 직업이 CLERK인 사원과 같은 부서에서 근무하는 사원의 이름과 월급과 부서번호를 출력해보자.

SELECT ENAME, DEPTNO

FROM EMP

WHERE DEPTNO IN (SELECT DEPTNO

FROM EMP

WHERE JOB = 'CLERK');

Q4. 'CHICAGO'에서 근무하는 사원들과 같은 부서에서 근무하는 사원의 이름과 월급을 출력

SQL> SELECT ENAME, SAL

2 FROM EMP

3 WHERE DEPTNO IN(SELECT DEPTNO

4 FROM DEPT

5 WHERE LOC = 'CHICAGO');

Q5. 부하직원이 있는 사원의 사원번호와 이름을 출력하자.

SELECT EMPNO, ENAME

FROM EMP

WHERE EMPNO IN (SELECT MGR

FROM EMP); // DATA OR DATA OR ... NULL ( = ANY)

Q6. 부하직원이 없는 사원의 사원번호와 이름을 출력하자.

SELECT EMPNO, ENAME

FROM EMP

WHERE EMPNO NOT IN (SELECT MGR, 0

FROM EMP);

오류 뜸 -> // DATA AND DATA AND ...; NULL(!=ALL)

Modify like Below!

SELECT EMPNO, ENAME

FROM EMP

WHERE EMPNO NOT IN (SELECT NVL(MGR, 0)

FROM EMP);

NVL(MGR, 0) = ISNULL(MGR, 0)

Q7. 'KING'에게 보고하는 즉 직속상관이 'KING'인 사원의 이름과 월급을 출력하라.

SELECT ENAME, SAL

FROM EMP

WHERE MGR IN (SELECT EMPNO

FROM EMP

WHERE ENAME = 'KING');

Q8. 20번 부서의 사원 중 가장 많은 월급을 받는 사원들보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하자.

SELECT ENAME, SAL

FROM EMP

WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20);

SELECT ENAME, SAL

FROM EMP

WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 20);

Q9. 20번 부서의 사원 중 가장 적은 월급을 받는 사원보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하자.

SELECT ENAME, SAL

FROM EMP

WHERE SAL > (SELECT MIN(SAL)

FROM EMP

WHERE DEPTNO = 20);

SELECT ENAME, SAL;

FROM EMP

WHERE SAL > ANY (SELECT SAL

FROM EMP

WHERE DEPTNO = 20);

Q10. 직업이 'SALESMAN'인 사원 중 가장 적은 월급을 받는 사원보다 더 적은 월급을 받는 사원들의 이름과 월급을 출력하라.

SQL> SELECT ENAME, SAL

2 FROM EMP

3 WHERE SAL < ALL (SELECT SAL

4 FROM EMP

5 WHERE JOB = 'SALESMAN');

Q11. 직업이 'SALESMAN'인 사원과 같은 부서에서 근무하고

같은 월급을 받는 사원의 이름, 월급, 부서번호를 출력하라.

SQL> SELECT ENAME, SAL, DEPTNO

2 FROM EMP

3 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE JOB = 'SALESMAN')

4 AND

5 SAL IN (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');

Q12. 30번 부서 사원들과 같은 월급과 커미션을 받는 사원들의 이름, 월급, 커미션을 출력하라.

SQL> SELECT ENAME, SAL, COMM

2 FROM EMP

3 WHERE SAL IN (SELECT SAL

4 FROM EMP

5 WHERE DEPTNO = 30)

6 AND COMM IN (SELECT COMM

7 FROM EMP

8 WHERE DEPTNO = 30);

저작자표시 비영리 변경금지

'국비교육 > JAVA' 카테고리의 다른 글

1월 14일 국비교육 32일차 (0)	2019.01.14
1월 11일 31일차 (0)	2019.01.11
1월 9일 국비교육 29일차 (0)	2019.01.09
1월 8일 국비교육 28일차 (0)	2019.01.08
1월 7일 국비교육 27일차 (0)	2019.01.07

UGONG2SAN

1월 10일 국비교육 30일차

'국비교육 > JAVA' 카테고리의 다른 글

댓글

티스토리툴바

1월 10일 국비교육 30일차

'국비교육 > JAVA' 카테고리의 다른 글

관련글

댓글

티스토리툴바