본문 바로가기
국비교육/JAVA

1월 10일 국비교육 30일차

by Diligejy 2019. 1. 10.

#1

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);


#2

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

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

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


#3


SQL> CREATE TABLE TEST02(

  2  MYNAME VARCHAR2(20),

  3  MYDY DATE);


#4

TO_DATE 함수 



#5

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;


#6




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

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



#7

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

2       FROM TEST02;


#8


#9

 현재 년도

(뒤의 두 자리)

입력하는 년도

(뒤의 두 자리) 

 결과

 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(일에 해당) 이하는 잘라버렸습니다.

ⓒ ROUND(MOD(MOD(MONTHS_BETWEEN(SYSDATE, HIREDATE)/12, 1) * 12, 1) * (365/12, 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

ⓒ Multi Column Subquery : WHERE절에서 여러 개의 COLUMN값을 비교할 경우

-


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

관련글

댓글

티스토리툴바