19.09.24. (논리연산자/LIKE/NULL/IN/ORDER BY/ORACLE DATATYPE/함수)

(1) where 조건 검색에 논리 연산자, 범위조건 사용

​

X Y =

AND T T T

T F F

F T F

F F F

​

X Y =

OR T T T

T F T

F T T

F F F

​

X Y =

NOR T T F

T F T

F T T

F F F

​

같지않다.

<>

!= (main)

^=

​

(연습문제 ex)

문1 부서번호 10번인 사원의 사원번호, 사원이름, 월급을 출력하시오

​

select empno, ename, sal

from emp

where deptno = 10;

​

문2 사원번호 7369보다 큰 사원의 이름, 입사일, 부서번호를 출력하시오

​

select ename, hiredate, deptno

from emp

where empno > 7369;

​

문3 사원번호 7300보다 크고, 7400보다 작은 사원의 이름, 입사일, 부서번호를 출력하시오

​

select ename, hiredate, deptno

from emp

where empno > 7300 and empno < 7400;

​

문4 사원이름이 smith인 사원의 사원이름, 입사일, 부서번호를 출력하시오.

행의 제목을 사원이름, 입사일. 부서번호

​

select ename as 사원이름 , hiredate as 입사일, deptno as 부서번호

from emp

where ename = 'SMITH'; (*데이터에 입력한 그대로 대문자로 적어야 함!!!!)

​

(2) LIKE

​

% : 0개 이상의 문자

_ : 1개의 문자

​

select ename as 사원이름 , hiredate as 입사일, deptno as 부서번호

from emp

where ename LIKE 'S%';

​

문5 이름이 K로 시작하는 사원의 사원이름, 사원번호, 직책을 출력하시오.

​

SELECT ENAME, EMPNO, JOB

FROM EMP

WHERE ENAME LIKE 'K%';

​

문6 부서번호가 10인 사원 중 직급이 CLERK이 아닌 사원의 이름, 직책, 부서번호를 출력하시오.

​

SELECT ENAME, JOB, DEPTNO

FROM EMP

WHERE DEPTNO = 10 AND JOB != 'CLERK';

​

​

문7 이름이 W로 시작하고 D로 끝나는 사람의 사원명, 직책, 부서번호를 출력하시오.

​

SELECT ENAME, JOB, DEPTNO

FROM EMP

WHERE ENAME LIKE 'W%' AND ENAME LIKE '%D';

OR

LIKE 'W%D';

​

문8 이름에 A가 포함되어 있는 사원의 이름과 사원번호를 출력하시오.

​

SELECT ENAME,EMPNO

FROM EMP

WHERE ENAME LIKE '%A%';

​

(3) NULL : 값이 없는 것, IS로 표현

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

WHERE STU_HEIGHT IS NOT NULL; (NULL이 아닌 값만 출력 : 키가 입력 된 사람)

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

WHERE STU_HEIGHT IS NULL; (NULL인 경우에만 출력 : 키가 입력 안 된 사람=공란인 사람)

​

(4) IN = OR (1학년 이거나 2학년인 학생이름)

​

SELECT STU_NAME

FROM STUDENT

WHERE STU_GRADE = 1 OR STU_GRADE = 2;

​

SELECT STU_NAME

FROM STUDENT

WHERE STU_GRADE IN (1,2);

​

문9 사원중에 CLERK, SALESMAN, MANAGER인 사원이름, 월급, 사원번호를 출력하시오.

​

SELECT ENAME, SAL, EMPNO

FROM EMP

WHERE JOB IN ('CLERK','SALESMAN','MANAGER');

​

문10 사원중에 COMM을 받고, SALESMAN인 사원의 사원이름, 사원번호, 총급여(SAL+COMM)을 출력하시오.

​

SELECT ENAME, EMPNO, (SAL+COMM) AS 총급여

FROM EMP

WHERE COMM IS NOT NULL AND JOB = 'SALESMAN';

​

(5) ORDER BY - 정렬 (ASC; 오름차순은 생략가능, BUT DESC; 내림차순은 반드시 입력)

(* 가장 최근 날짜는 desc / 알파벳, ㄱㄴㄷㄹ순은 asc)

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY STU_HEIGHT ASC;

=

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY STU_HEIGHT;

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY STU_HEIGHT DESC;

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY STU_NAME;

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY 1; (이름)

​

SELECT STU_NAME, STU_HEIGHT

FROM STUDENT

ORDER BY 2; (키)

​

SELECT STU_NAME AS 이름, STU_HEIGHT AS 키

FROM STUDENT

ORDER BY 이름;

​

SELECT STU_DEPT AS 과, STU_HEIGHT AS 키

FROM STUDENT

ORDER BY 과;

​

SELECT STU_DEPT, STU_HEIGHT

FROM STUDENT

ORDER BY 1,2 DESC; (1 부서명정렬 2 키정렬) 내림차순;

​

문11 매니저가 아닌 사원들 중에 COMM을 받지 않는 사원들의 정보를 모두 출력하시오.

​

SELECT * (모든 정보 출력)

FROM EMP

WHERE JOB != 'MANAGER' AND COMM IS NULL;

​

​

문12 사원들의 정보를 사원 직무별, 급여순으로 검색하시오. 단, 급여는 1000만원 이상만 검색.

​

SELECT JOB, SAL

FROM EMP

WHERE SAL >= 1000

ORDER BY 1,2;

​

​

문13 부서번호가 30번인 사원들 중 사원번호, 이름, 입사일을 출력하시오. 사원번호를 오름차순으로 정렬할 것.

​

SELECT EMPNO, ENAME, HIREDATE

FROM EMP

WHERE DEPTNO = 30

ORDER BY EMPNO ASC;

​

(6) Oracle Data Type

​

CHAR : 고정길이 문자열 (입력한 데이터와 최대 크기를 비교하여 편차가 클 경우 저장공간의 낭비가 심하다)

VARCHAR2 : 가변길이 문자열 (입력한 데이터에 따라 저장공간이 할당되므로 낭비를 줄일 수 있다)

​

(7) 함수

​

-숫자함수

​

1.ROUND(반올림)

SELECT ROUND(345.678,1) 소수 첫째자리에서 반올림

FROM DUAL; (존재하지 않지만 임시)

​

SELECT STU_NAME, ROUND(STU_HEIGHT,-1) 일의 자리에서 반올림(정수)

FROM STUDENT;

​

2.ABS(절대값)

​

3.MOD(나머지) : 2를 입력하면 짝/홀 구분 가능

SELECT MOD(10,3) FROM DUAL;

SELECT MOD(STU_WEIGHT,2) FROM STUDENT;

​

ex) 사원번호가 홀수인 사람들의 사원번호와 사원이름을 출력하라.

​

SELECT EMPNO,ENAME

FROM EMP

WHERE MOD(EMPNO,2) = 1;

​

4.FLOOR(소수점 버려)

SELECT FLOOR(123.456) FROM DUAL;

​

-문자함수

​

1.LOWER(소문자로 변환)

SELECT LOWER('KOREA') FROM DUAL;

​

2.UPPER(대문자로 변환)

SELECT UPPER('korea') FROM DUAL;

​

3.INITCAP(첫번째 문자만 대문자로 변환)

SELECT INITCAP('korea') FROM DUAL;

​

SELECT INITCAP(LOWER('KOREA')) FROM DUAL;

​

4.CONCAT(두개의 문자를 연결)

SELECT CONCAT('hi','HELLO') FROM DUAL;

= SELECT 'hi' || 'HELLO' FROM DUAL;

​

**ex) 사원들의 사원이름, 사원직무를 연결하여 검색하시오.

(예: King President)

SELECT CONCAT(CONCAT(INITCAP(ENAME),' '),INITCAP(JOB)) FROM EMP;

​

5.SUBSTR(대상,시작위치,추출할 문자 개수)

* -1,1 이라면 뒤에서부터 한글자만 검색

​

('문자의 일부분'을 세번째부터 5자를 반환)

SELECT SUBSTR('ABCDEFGHIJKL',3,5) FROM DUAL;

​

ex) 이름이 E로 끝나는 사원을 검색하라. SUBSTR 함수를 사용하여 ENAME의 마지막 문자 한개만 추출해서

이름이 E로 끝나는 사원을 검색해보라.

​

SELECT *

FROM EMP

WHERE SUBSTR(ENAME,-1,1) = 'E';

​

======

​

SELECT *

FROM EMP

WHERE ENAME LIKE '%E';

​

6.LENGTH(문자열길이확인)

SELECT LENGTH('ABCDEFGHIJKL') FROM DUAL;

​

7.INSTR(대상,찾을글자,시작위치,몇번째발견)

​

('특정문자'의 첫번째자부터 C가 1번 연속 나타나는 '위치')

SELECT INSTR('ABCDEFGHIJKL','C',1,1) FROM DUAL;

​

8.LPAD(자릿수 지정함수, 빈 공간을 특정 문자로 왼쪽부터 채움)

SELECT LPAD('ABCDEFGHIJKL',15,'*') FROM DUAL;

​

9.RPAD(자릿수 지정함수, 빈 공간을 특정 문자로 오른쪽부터 채움)

SELECT RPAD('ABCDEFGHIJKL',15,'*') FROM DUAL;

​

문14 사원들의 사원이름, 사원직무를 소문자로 검색하시오

SELECT LOWER(ENAME), LOWER(JOB) FROM EMP;

​

문15 사원들의 사원이름과 사원이름의 첫 글자를 검색하여 출력하시오

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

​

문16 사원이름을 10자리로 하고 뒤에 '-'를 채워 넣으시오.

SELECT RPAD(ENAME,10,'-') FROM EMP;

​

-날짜함수

​

1.SYSDATE(현재날짜)

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE+1 FROM DUAL;

SELECT SYSDATE+(1/24) FROM DUAL; 1시간 뒤

SELECT SYSDATE+(1/(24*60)) FROM DUAL; 1분뒤

SELECT ENAME, HIREDATE, HIREDATE+3 FROM EMP;

SELECT EMPNO, (MONTHS_BETWEEN(SYSDATE, HIREDATE)/12) FROM EMP;

​

2.SELECT NEXT_DAY(SYSDATE,'화') FROM DUAL;

3.SELECT ADD_MONTHS(SYSDATE,3) FROM DUAL;

4.SELECT LAST_DAY(ADD_MONTHS(SYSDATE,3)) FROM DUAL;

5.SELECT ROUND(SYSDATE,'MON') FROM DUAL;

6.SELECT TRUNC(SYSDATE,'MON') FROM DUAL;

​

-변환함수

​

1.TO_CHAR(숫자, 날짜 데이터를 문자 데이터로 변환)

*숫자*

SELECT TO_CHAR(123456789, 'L999,999,999') FROM DUAL;

(L은 LOCAL 현재 있는 나라에 따른 화폐 표시)

​

(9는 자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않는다.)

SELECT TO_CHAR(1234567, 'L999,999,999') FROM DUAL;

= \1,234,567

​

(0은 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채운다.)

SELECT TO_CHAR(1234789, 'L000,000,000') FROM DUAL;

= \001,234,789

​

*날짜*

ex) HIREDATE를 내가 원하는 형식으로 바꾸고자 할 때 사용

SELECT EMPNO, HIREDATE, TO_CHAR(HIREDATE,'MM-DD-YYYY') FROM EMP;

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD, HH24:MI:SS') FROM DUAL;

SELECT EMPNO, HIREDATE, TO_CHAR(HIREDATE,'MM-DD-YYYY DAY' ) FROM EMP;

​

2.TO_NUMBER(문자 데이터를 숫자 데이터로 변환)

SELECT TO_NUMBER(TO_CHAR(123456789)) FROM DUAL;

​

ex) 20000 - 10000

(*산술연산을 하려면 문자형식을 숫자형식으로 변환한 후 실행해야 된다)

SELECT TO_NUMBER('20000','99999') - TO_NUMBER('10000','99999') FROM DUAL;

​

3.TO_DATE(문자 데이터를 날짜 데이터로 변환)

SELECT TO_DATE('1983-08-26','YY-MM-DD') FROM DUAL;

​

ex) 올해 며칠이 지났는지 현재 날짜에서 2008/01/01을 뺀 결과를 출력하시오.

(*2008/01/01이 문자형식이기 때문에 날짜로 바꿔줘야 날짜 - 날짜가 된다)

SELECT TRUNC(SYSDATE - TO_DATE('2008/01/01','YYYY/MM/DD')) FROM DUAL;

​

문17 사원번호, 입사일, 근무 일자를 검색하여 출력하시오

SELECT EMPNO, HIREDATE, ROUND(SYSDATE-HIREDATE) FROM EMP;

​

문18 사원번호, 입사일, 입사 10년째 날짜를 검색하여 출력하시오.

SELECT EMPNO, HIREDATE, HIREDATE+(10*365) FROM EMP;

​

문19 입사일이 81/04/02 보다 늦고 82/12/09 보다 빠른 사원의 이름, 월급, 부서번호를 출력하시오.

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE HIREDATE < TO_DATE('19821209','YYYY-MM-DD')

AND HIREDATE > TO_DATE('19810402','YYYY-MM-DD');

​

문20 모든 사원은 자신의 상관이 있다. 하지만, 유일하게 상관이 없는 로우가 있다.

상관이 없는 사원만 출력하고 MGR 값이 NULL 대신 CEO로 출력하시오.

(*ORA-01722: 수치가 부적합합니다 ---> MGR값이 숫자기 때문에 문자로 바꿔줘야 함!!)

SELECT EMPNO, ENAME, NVL(MGR,'CEO')

FROM EMP

WHERE MGR IS NULL;

​

SELECT EMPNO, ENAME, NVL(TO_CHAR(MGR),'CEO')

FROM EMP

WHERE MGR IS NULL;