이번 시간에는 SELECT 조회문과 그 안에 다양한 함수들을 활용하여
여러 요구사항들을 해결하는 예제를 올려봅니다.
이것 역시도 어제와 그렇게 큰 난이도 차이가 나지 않네요
자 그럼 예제와 결과 화면을 보시죠
=========================================================
LOWER/UPPER
LOWER:대문자를 소문자로 바꿔 출력
UPPER:소문자를 대문자로 바꿔 출력
SELECT LOWER('HAPPY DAY') FROM DUAL;
SELECT UPPER('HAppy day') FROM DUAL;
SELECT 2*3 FROM EMP;
SELECT 2*3 FROM STUDENT;
DUAL이란 테이블에는 한 개의 행 밖에 없다.
연산이나 날짜 정보를 보기 위해 사용하는 테이블
SELECT SYSDATE,SYSTIMESTAMP FROM DUAL;
SELECT DNAME, INITCAP(DNAME) FROM DEPT;
INITCAP() : 첫글자만 대문자로
[1]EMP에서 20번 부서의 사원정보를 사번,이름,담당업무,부서번호를 가져오되 사원의 이름은 첫글자만 대문자로, 담당업무는 모두 소문자로, 사번 오름차순으로 가져오세요
SELECT * FROM EMP;
SELECT EMPNO,INITCAP(ENAME),LOWER(JOB),DEPTNO FROM EMP WHERE DEPTNO=20 ORDER BY EMPNO;
=========================================================
CONCAT(값1, 값2) : 두 개 이상의 문자나 컬럼을 서로 연결시켜주는 함수
SELECT CONCAT('ABCD','1234'), 'abcd'||'가나다' FROM DUAL;
SELECT EMPNO,ENAME,CONCAT(ENAME,JOB) FROM EMP;
SUBSTR(변수,INDEX,LEN) : 변수의 INDEX 위치에서 LEN 길이만큼의 문자를 반환
INDEX가 음수라면 문자는 끝에서부터 시작한다.
SELECT SUBSTR('ABCDEFG',2,3) FROM DUAL;
SELECT SUBSTR('ABCDEFG',-3,3) FROM DUAL;
[2]생년월일만 출력(99년 12월 25일)
SELECT SUBSTR('991225-1075112',1,2)||'년'||SUBSTR('991225-1075112',3,2)||'월'||SUBSTR('991225-1075112',5,2)||'일' FROM DUAL;
=========================================================
[3]주민번호 뒷자리(7개)만 출력
SELECT SUBSTR('991225-1075112',-7,7) FROM DUAL;
=========================================================
[4]사원테이블에서 사원의 이름 첫글자가 'K'보다 크고 'Y'보다 작은 사원의 사번,이름,업무를 출력하세요
SELECT * FROM EMP;
SELECT EMPNO,ENAME,JOB FROM EMP WHERE SUBSTR(ENAME,1,1)>'K' AND SUBSTR(ENAME,1,1)<'Y';
=========================================================
[5]상품(PRODUCTS) 테이블에서 출고가(OUTPUT_PRICE)를 가격단위('원')을 붙여서 상품명, "판매가격" 으로 출력하세요
SELECT * FROM PRODUCTS;
SELECT PRODUCTS_NAME, CONCAT(OUTPUT_PRICE,'원') "판매가격" FROM PRODUCTS ORDER BY OUTPUT_PRICE ASC;
--OUTPUT_PRICE||'원'
=========================================================
LENGTH(값) : 문자열 길이를 반환
[6]사원테이블에서 사원의 이름이 6자리 이상을 차지하는 사원의 이름과 업무, 이름 자릿수를 함께 출력하세요
SELECT ENAME,JOB,LENGTH(ENAME) FROM EMP WHERE LENGTH(ENAME)>=6;
=========================================================
LPAD / RPAD
LPAD(컬럼, 변수1, 변수2) : 문자값을 왼쪽부터 주어진 문자로 채움
전체자리를 15자리로 정하고 남는 문자열은 '*'로 채우겠다.
SELECT ENAME,LPAD(ENAME,15,'*'),SAL,LPAD(SAL,10,'-') FROM EMP;
SELECT DNAME,RPAD(DNAME,15,'*') FROM DEPT;
LTRIM / RTRIM
LTRIM(변수1,변수2)
:변수 1값 중에서 변수2와 같은 단어가 있을 경우 그 문자를 삭제한 나머지 값을 반환한다.
왼쪽 'A' 문자를 지운다
SELECT LTRIM('AAAHello Oracle AA', 'A') FROM DUAL;
오른쪽 'A' 문자를 지운다
SELECT RTRIM('AAAHello Oracle AA', 'A') FROM DUAL;
[7]' HELLO ORACLE ' 문자열의 앞 뒤 공백을 제거하여 출력하고, 문자열 길이도 출력하세요
SELECT LENGTH(TRIM(' HELLO ORACLE ')), RTRIM(LTRIM(' HELLO ORACLE ', ' ')) "공백 제거" FROM DUAL;
=========================================================
[8] 사원테이블에서 10번 부서 사원의 담당 업무 중 좌측에 'M'을 삭제하고 급여 중 좌측의 1을 삭제하여 출력하세요
SELECT * FROM EMP;
SELECT LTRIM(JOB, 'M'), LTRIM(SAL, '1') FROM EMP WHERE DEPTNO=10;
=========================================================
REPLACE(컬럼, 변수1, 변수2)
: 정해진 컬럼에서 지정한 문자를 특정 문자로 변경하는 함수
TEST ORACLE의 TEST 문자열을 HELLO로 바꾼다
SELECT REPLACE('TEST ORACLE', 'TEST', 'HELLO') FROM DUAL;
[9] 사원 테이블에서 업무가 'A'로 시작하는 업무를 'A'를 '$'로 바꿔 출력하세요
SELECT ENAME,JOB,REPLACE(JOB,'A','$') FROM EMP WHERE JOB LIKE 'A%';
=========================================================
[10] 고객(MEMBER)테이블에서 직업이 '학생'인 정보를 모두 '대학생'으로 변경해 출력하세요
SELECT NAME,JOB,REPLACE(JOB,'학생','대학생') FROM MEMBER;
=========================================================
[11] 고객 테이블 주소에서 '서울시'를 '서울특별시'로 수정하세요
SELECT * FROM MEMBER;
UPDATE MEMBER SET ADDR=REPLACE(ADDR,'서울시','서울특별시') WHERE ADDR LIKE '%서울시%';
ROLLBACK;
=========================================================
숫자형 함수
ROUND(값), ROUND(값1, 값2)
SELECT ROUND(4567.789), ROUND(4567.789,0), ROUND(4567.789,2), ROUND(4567.789,-2) FROM DUAL;
소수점 첫번째 자리에 반올림, 소수점 첫번째 자리에 반올림, 소수점 셋째 자리에 반올림, 소수점을 기준으로 앞쪽으로 감(-2)
TRUNC : 절삭
SELECT TRUNC(4567.789), TRUNC(4567.789,0), TRUNC(4567.789,2), TRUNC(4567.789,-2) FROM DUAL;
[12] 상품 테이블의 상품 정보 가운데 100원 단위까지 버린 배송비를 상품별과 원래 배송비, 절삭한 배송비를 함께 보여주세요
SELECT PRODUCTS_NAME, TRANS_COST, TRUNC(TRANS_COST,-3) FROM PRODUCTS;
=========================================================
MOD(값1, 값2)
:값1을 값2로 나눈 나머지 값을 반환한다.
[13] EMP에서 부서번호가 10인 사원의 급여를 30으로 나눈 나머지를 사원명과 함께 출력하세요
SELECT * FROM EMP;
SELECT ENAME, MOD(SAL, 30)"30으로 나눈 나머지" FROM EMP WHERE DEPTNO='10';
=========================================================
ABS(값): 절대값을 반환
CEIL(값): 올림값을 반환
FLOOR(값): 내림값을 반환
SELECT NAME,AGE,AGE-40,ABS(AGE-40)"나이 차이" FROM MEMBER;
SELECT CEIL(123.556), FLOOR(123.556), ROUND(123.556) FROM DUAL;
SELECT POWER(2,8), SQRT(64), SIGN(-100), SIGN(100), SIGN(0) FROM DUAL;
POWER(값1,값2):거듭제곱
SQRT(값):제곱근
SIGN(값):값이 양수면 1을 반환, 음수면 -1을 반환, 0이면 0을 반환
주어진 숫자가 음수인지 양수인지 구별하고자 할 때 사용
SELECT CHR(65), ASCII('W') FROM DUAL;
SELECT SYSDATE+10 "10일 뒤", SYSDATE-10 "10일 전" FROM DUAL;
SELECT TO_CHAR(SYSTIMESTAMP -1/24,'YYYY-MM-DD HH:MI:SS')"1시간 전", TO_CHAR(SYSTIMESTAMP+3/24, 'YYYY-MM-DD HH:MI:SS')"3시간 후" FROM DUAL;
[14]EMP테이블에서 사원들이 현재까지 근무 년수가 몇 년인지 출력하세요. 단 근무 일수가 많은 사람 순으로 출력하세요
DATE-DATE:일수
SELECT * FROM EMP;
SELECT ENAME, TRUNC((SYSDATE-HIREDATE)/365) "근무년수" FROM EMP ORDER BY HIREDATE ASC;
=========================================================
[15]사원 테이블에서 사원들이 현재까지 근무일수가 몇주 몇일인지 출력하세요
SELECT ENAME, TRUNC((SYSDATE-HIREDATE)/7)"근무 주수", ROUND(MOD((SYSDATE-HIREDATE), 7)) "근무 일수" FROM EMP;
=========================================================
ADD_MONTHS(D, 값1) : 주어진 날짜(D)에 값1의 월 수를 더해 반환한다.
SELECT add_months('16/10/12', 2), add_months('16/10/31',4) from dual;
고객(Member) 테이블이 두 달의 기간을 가진 유료 회원이라는 가정하에 등록일(reg_date)을 기준으로 유료 회원인 고객의 정보(이름,등록일,만기일)를 보여주세요
select * from Member;
select NAME, REG_DATE, add_months(REG_DATE, 2)"만기일", MILEAGE from Member where MILEAGE>0;
LAST_DAY(D)
D가 포함되어 있는 월의 마지막 날짜 값을 반환
SELECT LAST_DAY('13/08/08') FROM DUAL;
SYSDATE/SYSTIMESTAMP
SYSDATE:시스템의 현재 시간을 DATE 유형으로 반환
SYSTIMESTAMP:시스템의 현재 시간을 TIMESTAMP유형으로 반환
SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;
SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'cc year-month-ddd day') from dual;
cc: 세기 / year: 연도 / month:월 / ddd:[ddd->1년의 날짜, dd->1개월 날짜, d->일주일의 날짜] / day: 요일
=========================================================
댓글 없음:
댓글 쓰기