👍🏻 LIKE
- 패턴과 일치하는 데이터를 검색
- %는 0개 이상의 문자를 나타냄
- _는 한 문자를 나타냄
S가 처음, 중간, 끝에 오는 이름을 검색
SELECT * FROM emp WHERE ename LIKE '%S%';
SELECT * FROM emp WHERE ename NOT LIKE '%S%';입사일이 22로 끝나는 사원 정보 구하기
SELECT ename, hiredate FROM emp WHERE hiredate LIKE '%22';FOR다음에 정해지지 않은 한 문자를 가진 사람을 불러오기
SELECT * FROM emp WHERE ename LIKE 'FOR_';한 글자 다음의 M, M다음에 없거나 여러개
SELECT * FROM emp WHERE ename LIKE '_M%';검색하고자 하는 문자열에 가 포함되어 있으면 앞에 \를 붙이고 escape \를 명시해준다.
SELECT * FROM emp WHERE ename LIKE'SC\_%' ESCAPE '\';검색하고자 하는 문자열에 %가 포함되어 있을 경우
SELECT * FROM emp WHERE ename like '%\%' ESCAPE '\';🈚 NULL 조건 사용
사원들 중 comm 계약을 안 맺은 사람 구하기
select * from emp where comm is null;
select * from emp where comm is not null;➕✖️ 논리연산자를 사용하여 조건 정의
- AND 연산자의 사용 : 구성 요소 조건이 모두 TRUE이면 TRUE를 반환
급여 2000 이상, 업무에는 MAN이 포함 => 모든 조건이 충족되면 반환
SELECT empno, ename, job, sal from emp where sal >= 2000 and job like '%MAN%'; - OR 연산자의 사용 : 조건 중 하나가 TRUE면 TRUE를 반환
급여 2000 이상 또는 업무에는 MAN이 포함 => 둘 중 하나만 충족되어도 반환
SELECT empno, ename, job, sal FROM emp WHERE sal>=2000 or job like '%MAN%';- NOT 연산자의 사용
업무가 CLERK, SALESMAN은 제외시킨 후 반환
SELECT ename, job FROM emp WHERE job not in ('CLERK' , 'SALESMAN');📜 실습 문제 3
- EMP 테이블에서 81년 05월 01일과 81년 12월 03일 사이에 입사한 사원의 이름, 급여, 입사일을 출력하세요
SELECT ename, sal, hiredate FROM emp WHERE hiredate BETWEEN '81-05-01' AND '81-12-03';- EMP 테이블에서 사원 번호가 7566. 7782, 7934인 사원을 제외한 사람들의 사원번호, 이름, 월급을 출력하세요.
SELECT empno, ename, sal FROM emp WHERE empno NOT IN (7566 , 7782, 7934);- EMP 테이블에서 급여가 $2,000와 $5,000사이고 부서 번호가 10 또는 30인 사원의 이름과 급여, 부서번호를 출력하세요.
SELECT ename, sal, deptno FROM emp WHERE (sal >= 2000 AND sal <=5000) AND deptno IN (10, 30); - EMP 테이블에서 업무가 SALESMAN 또는 MANAGER이면서 급여가 $1,600 , $2,975 , $2,850이 아닌 모든 사원이름, 업무, 급여를 표시하세요.
SELECT ename, job, sal FROM emp WHERE job IN ('SALESMAN' , 'MANAGER') AND sal NOT IN (1600, 2975, 2850);ORDER BY 절 : (정렬)
- ORDER BY : 검색된 행이 표시되는 순서를 지정합니다.
- ASC : 오름차순으로 행을 정렬합니다. (기본 순서)
- DESC : 내림차순으로 행을 정렬합니다.
SELECT * FROM emp WHERE deptno = 10 ORDER BY sal ASC;
SELECT * FROM emp WHERE deptno = 10 ORDER BY sal DESC;
SELECT * FROM emp WHERE deptno = 10 ORDER BY sal, ename;
SELECT * FROM emp WHERE deptno = 10 ORDER BY sal ASC , ename DESC; 내림차순 정렬
SELECT ename, job, deptno, hiredate FROM emp ORDER BY hiredate DESC;1차 정렬에서 중복값이 있을 경우 2차 정렬 수행
SELECT * FROM emp ORDER BY sal DESC, ename DESC;날짜 정렬
SELECT ename, job, deptno, hiredate FROM emp ORDER BY hiredate DESC;열 ALIAS를 기준으로 정렬
SELECT empno, ename, (sal*12) annsal FROM emp ORDER BY annsal;열의 숫자 위치를 사용하여 정렬
=> 세번째 열이 deptno이기 때문에 deptno로 정렬시킨 결과가 나타난다.
SELECT ename, job, deptno, hiredate FROM emp ORDER BY 3;테이블에 생성된 순서대로 열 숫자 위치가 부여된다.
SELECT * FROM emp ORDER BY 2;NULL값을 갖고 있는 칼럼을 정렬할 때 NULLS FIRST 또는 NULLS LAST 키워드를 사용하여 반환된 행 중 NULL 값을 포함하는 행이 정렬 순서상 맨 처음에 나타나거나 마지막에 나타나도록 지정한다.
SELECT * FROM emp ORDER BY comm NULLS FIRST;
SELECT * FROM emp ORDER BY comm NULLS LAST;- NULL 값은 맨 마지막에 나타나고 ename은 내림차순 정렬한다.
- NULL 값은 맨 처음에 나타나고 comm의 숫자들은 내림차순 정렬한다.
SELECT * FROM emp ORDER BY comm NULLS LAST, ename DESC;
SELECT * FROM emp ORDER BY comm DESC NULLS FIRST;📜 실습 문제 4
- EMP 테이블에서 사원 번호, 사원 이름, 입사일을 출력하는데 입사일이 빠른 사람순으로 정렬하시오.
SELECT empno, ename, hiredate FROM emp ORDER BY hiredate ASC; - EMP 테이블에서 사원 이름, 급여, 연봉을 구하고 연봉이 많은 순으로 정렬하시오.
SELECT ename, sal, sal*12 FROM emp ORDER by sal*12 DESC;- EMP 테이블에서 10번 또는 20번 부서에서 근무하고 있는 사원의 이름과 부서 번호를 출력하는데 이름을 영문자순으로 표시하시오.
SELECT ename, deptno FROM emp WHERE deptno IN(10 , 20) ORDER BY ename ASC; - EMP 테이블에서 커미션 계약을 맺은 모든 사원의 이름, 급여, 커미션을 출력하는데 커미션을 기준으로 내림차순 정렬하시오.
SELECT ename, sal, comm FROM emp WHERE comm IS NOT null ORDER BY comm DESC;문자 함수
LOWER : 소문자로 바꿔주는 함수
SELECT LOWER('HELLO') FROM dual;
SELECT LOWER(ename) FROM emp;UPPER : 대문자로 바꿔주는 함수
SELECT UPPER ('hello') FROM dual;INITCAP : 문장의 첫 글자를 대문자로 변경, 그 다음은 소문자
SELECT INITCAP('hello wORLD') FROM dual;CONCAT (문자열 1, 문자열 2) : 문자열 1과 문자열 2를 연결하여 하나의 문자열로 반환
SELECT CONCAT('Hello' , 'World') from dual;
SELECT CONCAT (ename , job) FROM emp;SUBSTR (대상문자열, 인덱스) : 대상 문자열에서 지정한 인덱스부터 문자열을 추출
[주의] 인덱스 1부터 시작
SELECT SUBSTR ('Hello World' , 3) FROM dual; > llo World
--------- 문자열 추출
SELECT SUBSTR ('Hello World' ,3,3) FROM dual; > llo
--------- 문자열 세번째부터 3개만 추출
SELECT SUBSTR ('Hello World',-3) FROM dual; > rld
----------- 뒤에서 3번째부터 끝까지 추출
SELECT SUBSTR ('Hello World',-3,2) FROM dual; > rl
----------- 뒤에서 3번째부터 문자 2개 추출
LENGTH(대상문자열) : 문자열의 개수
SELECT LENGTH ('Hello World') FROM dual;
SELECT LENGTH (ename) FROM emp;
INSTR (대상문자열, 검색 문자) : 검색문자의 위치값 검색
SELECT INSTR('Hello World' , 'e' ) FROM dual;
SELECT INSTR('Hello World','E') FROM dual; -- 검색문자가 없을 경우 0 반환
SELECT INSTR ('Hello World','o') FROM dual;대상문자열, 검색 문자, 검색 인덱스(해당 위치부터 검색)
SELECT INSTR ('Hello World','o',6) FROM dual;대상문자열, 검색 문자, 검색 인덱스, 반복 횟수
SELECT INSTR ('Hello World','o',1,2) FROM dual;LPAD(대상문자열, 총길이, 문자) : 지정한 길에 문자열을 출력하는데 공백은 왼쪽에 지정한 문자로 채움
SELECT LPAD ('HELLO', 10 , '*' ) FROM dual;> *****HELLO 공백을 왼쪽에 두고, 지정한 *로 왼쪽 공백을 채운다.
RPAD(대상문자열, 총길이, 문자) : 지정한 길에 문자열을 출력하는데 공백은 오른쪽에 지정한 문자로 채움
SELECT RPAD ('HELLO', 10 , '*' ) FROM dual;> HELLO***** 공백을 오른쪽에 두고, 지정한 *로 오른쪽 공백을 채운다.
TRIM : 문자열에서 공백이나 특정 문자를 제거한 다음에 다음 값을 반환
방향 : 왼쪽 -> LEADING , 오른쪽 -> TRAILING , 양쪽 -> BOTH = DEFAULT
LTRIM : 문자열의 왼쪽에서 공백이나 특정 문자를 제거한 다음 값을 반환
RTRIM : 문자열의 오른쪽에서 공백이나 특정 문자를 제거한 다음 값을 반환
SELECT TRIM (LEADING 'h' FROM 'habchh') FROM dual;
SELECT TRIM (TRAILING 'h' FROM 'habchh') FROM dual;
SELECT TRIM (BOTH 'h' FROM 'hahchh') FROM dual;REPLACE (대상문자열, OLD, NEW) : 대상문자열에서 OLD 문자를 NEW 문자로 대체
SELECT REPLACE ('010.1234.5678' , '.' , '-') FROM dual;함수 중첩
SELECT ename , LOWER(SUBSTR(ename,1,3)) FROM emp;📜실습 문제 5
1.EMP 테이블에서 업무의 첫글자는 대문자 나머지는 소문자로 출력하시오.
SELECT INITCAP (job) FROM emp;- EMP 테이블에서 사원 이름중 A가 포함된 사원이름을 구하고 그 이름 중 앞에서 3글자만 추출하여 출력하시오.
SELECT SUBSTR ( ename, 1 , 3) FROM emp WHERE ename LIKE '%A%';- EMP 테이블에서 이름의 3번째가 문자가 A인 모든 사원의 이름을 표시하시오.
SELECT ename FROM emp WHERE ename LIKE '__A%' ;
SELECT ename FROM emp WHERE SUBSTR( name, 3, 1) = 'A' ;- EMP 테이블에서 이름이 J, A, M으로 시작하는 모든 사원의 이름[첫 글자는 대문자로 나머지는 소문자] 및 이름의 길이를 표시하시오. [열 레이블은 name, length로 표시]
SELECT INITCAP (ename) name, LENGTH (ename) length FROM emp WHERE (ename LIKE 'J%' OR ename LIKE 'A%' OR ename LIKE 'M%');
SELECT INITCAP (ename) name, LENGTH (ename) length FROM emp WHERE SUBSTR ( ename , 1 , 1) IN( 'J' , 'A', 'M') ;🔢 숫자 함수
CEIL (실수) : 올림 처리한 정수값을 반환
SELECT CEIL(1.4) FROM dual;FLOOR(실수) : 버림 처리한 정수 값을 반환
SELECT FLOOR (1.7) FROM dual;ROUND (대상숫자, 지정자릿수) : 반올림
SELECT ROUND (45.926 , 2) FROM dual;
SELECT ROUND (45.926) FROM dual;
SELECT empno , ename , sal , ROUND(sal * 1.15) "New Salary" , ROUND(sal* 1.15) - sal "Increase" FROM emp;TRUNC (대상숫자 , 지정자리수) : 절삭
SELECT TRUNC (45.926 , 2 ) FROM dual;MOD (대상숫자, 나눌숫자) : 나머지값
SELECT MOD (17,2) FROM dual;📅 날짜 함수
- 오라클 데이터베이스는 내부 숫자 형식 (세기, 년, 월, 일, 시, 분, 초)으로 날짜를 저장한다. 기본 날짜 표시 형식은 DD-MON-RR
- 연도의 마지막 두자릿수만 지정하면 21세기 날짜를 20세기에 저장할 수 있다.
- 같은 방식으로 20세기 날짜를 21세기에 저장할 수 있다.
SYSDATE : ORACLE 서버의 현재 날짜와 시간을 반환
SELECT SYSDATE FROM dual;날짜에 산술 연산자 사용
SELECT ename , TRUNC((SYSDATE - hiredate) / 7) AS weeks FROM emp WHERE deptno = 10;MONTHS_BETWEEN(날짜1, 날짜2) : 두 날짜 간의 개월 수
SELECT MONTHS_BETWEEN ('2012-03-23','2010-01-23') FROM dual;
SELECT ename, TRUNC ( MONTHS_BETWEEN (SYSDATE , hiredate)) months_worked FROM emp ORDER BY months_worked;ADD_MONTHS : 특정 날짜의 월에 정수를 더한 다음 해당 날짜를 반환하는 함수
select add_months('2022-01-01',8) from dual;NEXT_DAY : 지정된 요일의 다음 날짜
SELECT NEXT_DAY ('2024-03-12','월요일') FROM dual;1(일요일) - 7(토요일)
SELECT NEXT_DAY ('2024-03-12',2) FROM dual;LAST_DAY : 월의 마지막 날
SELECT LAST_DAY ('2012-11-07' ) FROM dual;EXTRACT : 날짜 정보에서 특정한 연도, 월, 일, 시간, 분, 초 등을 추출
SELECT EXTRACT (YEAR FROM SYSDATE), EXTRACT (MONTH FROM SYSDATE) , EXTRACT(DAY FROM SYSDATE) FROM dual;📜 실습 문제 6
- EMP 테이블에서 모든 사원의 이름과 급여를 표시하세요. 급여는 15자 길이로 왼쪽에 $ 기호가 채워진 형식으로 표기하고 열 레이블은 SALARY로 지정하세요.
SELECT ename, LPAD (sal,15,'$') "Salary" FROM emp;- EMP 테이블에서 모든 사원의 이름, 업무, 급여, 부서 번호를 출력하세요. 부서 번호는 오름차순 정렬을 하고 사원의 이름은 길이 : 10 , 빈칸은 *로 표시하세요.
SELECT RPAD (ename ,10,'*'), job, sal, deptno FROM emp ORDER BY deptno ASC ;- 오늘부터 이번 달의 마지막 날 까지의 남은 날 수를 구하세요.
SELECT (LAST_DAY(SYSDATE) - SYSDATE ) FROM dual;- EMP 테이블에서 각 사원에 대해 사원 번호, 이름, 급여 및 15% 인상된 급여를 정수(반올림)로 표시하세요. 인상된 급여열의 레이블은 NEW SALARY로 지정하세요.
SELECT empno, ename, sal, ROUND(sal *1.15) "New Salary" FROM emp;변환 함수
- 암시적 데이터 유형 변환
할당문의 경우 Oracle Server는 다음을 자동으로 변환
표현식 계산을 위해 Oracle Server는 다음을 자동으로 변환
- 명시적 데이터 유형 변환
TO_CHAR : 숫자 => 문자 , 날짜 => 문자
TO_NUMBER : 문자 => 숫자
TO_DATE : 문자 => 날짜
날짜 포맷팅
숫자 포맷팅
실제 자리수와 일치
SELECT TO_CHAR (1234 , 9999) FROM dual;
SELECT TO_CHAR (1234 , '9999') FROM dual;
SELECT TO_CHAR (1234 , 0000) FROM dual;자리수가 모자람 => ####
SELECT TO_CHAR (1234 , 999) FROM dual;
SELECT TO_CHAR (1234 , '999') FROM dual;
SELECT TO_CHAR (1234 , '000') FROM dual;실제 자리수 보다 많은 자리수 지정
SELECT TO_CHAR (1234 , 99999) FROM dual;
SELECT TO_CHAR (1234 , '99999') FROM dual;
SELECT TO_CHAR (1234 , '00000') FROM dual;소수점 자리 지정
SELECT TO_CHAR (1234, 9999.99) FROM dual;
SELECT TO_CHAR (1234, '9999.99') FROM dual;
SELECT TO_CHAR (1234, '0000.00 ') FROM dual;반올림해서 소숫점 둘째자리까지 표시
SELECT TO_CHAR (25.897, '99.99') FROM dual;인상된 급여를 소수점 첫째자리까지 표시
SELECT TO_CHAR (SAL*1.15, '9,999.9') FROM EMP;통화 표시
SELECT TO_CHAR(1234, '$0000') FROM dual;
SELECT TO_CHAR(1234, '$9999') FROM dual;지역 통화 표시
SELECT TO_CHAR(1234, 'L9999') FROM dual;숙제
1) emp 테이블의 사원명과 업무를 쉼표(,)로 연결해서 표시하고 컬럼명은 Employee and Job으로 표시하시오.
2) 부서번호 30(deptno)에서 근무하며 월 2,000달러 이하를 받는 81년 5월 1일 이전에 입사한 사원의 이름, 급여, 부서번호, 입사일을 출력하시오.
3) emp 테이블에서 이름에 A와 E가 있는 모든 사원의 이름을 표시하시오.
4) emp 테이블에서 사원이름 중 S가 포함되지 않은 사람들 중 부서번호가 20인 사원들의 이름과 부서번호를 출력하시오.
5) emp 테이블에서 관리자(mgr)가 없는 모든 사원의 이름과 업무를 표시하시오.
6) emp 테이블에서 커미션 항목이 입력된 사원들의 이름과 급여, 커미션을 구하시오.
7) 이름의 글자수가 6자 이상인 사원의 이름을 소문자로 이름만 출력하시오.
8) 이름의 글자수가 6자이상인 사람의 이름을 앞에서 3자만 구하여 소문자로 출력하시오.
9) 각 사원의 이름을 표시하고 근무 달 수(입사일로부터 현재까지의 달 수)를 계산하여 열레이블을 MONTHS_WORKED로 지정하십시오. 결과는 정수로 반올림하여 표시하고 근무 달 수를 기준으로 오름차순으로 정렬하시오.
10) emp 테이블에서 이름(소문자로 표시), 업무, 근무연차를 출력하시오.
1) SELECT ename || ', ' || job AS "Employee and Job" FROM emp;
2) SELECT ename, sal, deptno, hiredate FROM emp WHERE deptno = 30 AND sal<= 2000 AND hiredate < '81-5-1' ;
3) SELECT ename FROM emp WHERE ename LIKE '%A%' AND ename LIKE '%E%';
4) SELECT ename, deptno FROM emp WHERE ename NOT LIKE '%S%' AND deptno = 20;
5) SELECT ename, job FROM emp WHERE mgr IS NOT null;
6) SELECT ename, sal, comm FROM emp WHERE comm IS NOT null;
7) SELECT LOWER(ename) FROM emp WHERE LENGTH(ename) >= 6;
8) SELECT SUBSTR (LOWER(ename) , 1 , 3) FROM emp WHERE LENGTH(ename) >= 6 ;
9) SELECT ename, ROUND( MONTHS_BETWEEN (SYSDATE , hiredate) ) AS MONTHS_WORKED FROM emp ORDER BY MONTHS_WORKED ASC ;
10) SELECT LOWER(ename) , job, TRUNC ( (SYSDATE - hiredate ) / 365 ) AS YEARS_WORKED FROM emp;