SELECT 조회문을 활용하여 다양한 조건문 출력

안녕하세요

이번 시간에는 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: 요일

=========================================================

사원(emp)테이블을 select하는 예제

안녕하세요

이번 시간에는 사원(emp) 테이블을 select하는 예제 올려봅니다.

이번 예제도 쉽긴 하지만 저번 예제보다는 좀 더 활용도가 높습니다.

LIKE 연산자, ORDER BY절 등이 포함되 있으니 천천히 살펴보세요~!


==========================================================

[1]사원(emp)테이블에서 업무가 salesman이거나 president인 사원의 사번, 사원명, 업무, 급여를 보여주세요
--SALESMAN, PRESIDENT 같은 경우 원래 값이 대문자이므로 대문자로 조회해야함
select empno,ename,job,sal from emp where
job='SALESMAN' or job='PRESIDENT';



--값은 대소문자를 구분한다 upper()함수=>대문자로 바꿔줌 lower()

==========================================================

--[2]사원 테이블에서 급여가 1500이 아닌 사원의 이름,급여를 출력하세요
select ename,sal from emp where sal!=1500;
--sal<>1500



==========================================================

--[3]emp에서 급여가 2000,3000,5000인 사원의 이름, 업무, 급여 보여주세요
select ename,job,sal from emp where sal=2000 or sal=3000 or sal=5000;
--sal in(2000,3000,5000)



==========================================================

[4]emp에서 급여가 2000,3000,5000이 아닌 사원의 이름, 업무, 급여 보여주세요
select ename,job,sal from emp where sal not in(2000,3000,5000);
select ename,job,sal from emp where sal<>2000 and sal<>3000 and sal<>5000;



==========================================================

LIKE 연산자
검색 할 때 와일드 카드를 사용하여 많이 쓴다
WHERE 컬럼명 LIKE '조건';
WHERE 컬럼명 LIKE '조건%';
WHERE 컬럼명 LIKE '%조건';
WHERE 컬럼명 LIKE '%조건%';
%->와일드 카드 / 문자가 0개 이상의 문자
'_' =>1개의 문자와

SELECT * FROM STUDENT WHERE NAME LIKE '홍%';

[5]emp에서 이름 S자로 시작하는 사원의 이름,업무 보여주세요
select * from emp;
select ename,job from EMP where ENAME like 'S%';



==========================================================

[6]emp에서 이름 중 S자가 들어가는 사원의 모든 정보를 보여주세요
select * from emp where ename like '%S%';



==========================================================

[7]emp에서 이름이 D로 끝나는 사원의 이름, 급여를 보여주세요
select ename, sal from emp where ename like '%D';

==========================================================

[8]이름의 두번째에 O자가 들어가는 사람 정보를 보여주세요
select * from emp where ename like '_O%';



==========================================================

[9]이름의 끝에서 세번째에 T자가 들어가는 사람 정보를 보여주세요
select * from emp where ename like '%T__';



==========================================================

[10 emp에서 82년도에 입사한 사원의 이름,입사일을 보여주세요
select ename,hiredate from emp where hiredate like '82%';



==========================================================

날짜 포맷을 변경하자
ALTER SESSION SET NLS_DATE_FORMAT='YY/MM/DD';
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';

[11] member에서 '강북구'에 사는 회원의 이름,주소,마일리지를 보여주세요
select name, ADDR, MILEAGE from member where addr like '%강북%';



==========================================================

[12] EMP에서 COMM이 0인 사원의 이름, JOB, 급여, COMM을 보여주세요
select ename, job, sal, comm from emp where comm=0;



==========================================================

[13] EMP에서 보너스(COMM)을 받지 않는 사원의 이름, 업무, 급여, COMM을 보여주세요
SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE COMM is NULL;
SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE COMM is not NULL;



null 비교는 = 로 비교하면 안된다, IS NULL 연산자로 비교한다

==========================================================

ORDER BY절
데이터를 정렬하고자 할 때 사용한다
오름차순 ASEC <= 디폴트
내림차순 DESC
ORDER BY절은 SELECT문의 제일 뒤에 위치한다.

WGHO순서 (WHERE, GROUP BY, HAVING, ORDER BY)

[14] EMP에서 입사일자 순으로 정렬하여 사번,이름,입사일을 보여주세요
select empno,ename,hiredate from emp order by hiredate desc;



==========================================================

[15] 이름을 알파벳 순으로 보여주세요
select ename from emp order by ename;
select empno,ename,hiredate, sal*12+nvl(comm,0) from emp order by 2 asc; --2번째 칼럼을 오름차순으로 보여주세요



==========================================================

[16] 연봉이 제일 많은 순서대로 가져와 보여주세요
select empno,ename,hiredate,sal*12+nvl(comm,0) from emp order by 4 desc;



==========================================================

[17] EMP에서 부서번호(DEPTNO)로 정렬한 후 부서번호가 같을 경우 급여가 많은 순으로 정렬하여 사번, 이름, 부서번호, 급여, 업무를 출력하세요
select empno, ename, deptno, sal, job from emp order by deptno, sal desc;



==========================================================

[18] 상품(PRODUCTS) 테이블에서 판매가격(OUTPUT_PRICE)이 저렴한 순서대로 상품을 정렬하여 모든 정보를 보여주세요
select * from products order by output_price;



==========================================================

오라클 SELECT문 초보자 모드 돌입

안녕하세요

이번 시간에는 오라클 SELECT문 기초 예제 올려보겠습니다.

이번 SELECT문은 굉장히 이해하기 쉬워서 몇번 치시다 보면

손에 익혀서 고수가 될것입니다.

하지만 앞으로 이후의 SELECT문의 난이도는 조금 있는편이라 쉽지는 않습니다.

어쨋든 SELECT문 초보자 모드 보시죠~!

ㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱㄱ



=========================================================

--부서(DEPT) 테이블에서 부서번호, 부서명을 가져오세요
SELECT DEPTNO, DNAME FROM DEPT;



=========================================================

--SALGRADE테이블에서 GRADE, LOSAL을 가져오세요
SELECT GRADE, LOSAL FROM SALGRADE;



=========================================================

--산술표현식도 가능하다.
--EMP테이블에서 사원명(ENAME), 급여(SAL)와 더불어 10% 인상된 급여를 보여주세요
--AS(ALIAS)==>별칭  AS 별칭
SELECT ENAME, SAL, (SAL+(SAL*0.1))AS SAL_UP FROM EMP;



=========================================================

--한글 별칭도 허용 ""를 감싸서
SELECT ENAME, SAL, SAL+300 "300플러스" FROM EMP;



=========================================================

--EMP에서 사원명, 급여, 보너스(comm)를 가져오세요
SELECT * FROM EMP;
SELECT ENAME, SAL, COMM FROM EMP;



=========================================================

--EMP에서 사원명, 급여, 보너스(COMM), 연봉(SAL*12+COMM)을 가져오세요
SELECT ENAME, SAL, COMM, SAL*12+COMM "연봉" FROM EMP;



=========================================================

--MEMBER(회원) 테이블에서 회원의 이름(NAME)과 나이, 직업을 보여주세요
SELECT * FROM MEMBER;
SELECT NAME, AGE, JOB FROM MEMBER;



=========================================================

--MEMBER테이블에서 회원의 이름과 적립된 마일리지를 보여주되, 마일리지에 13을 곱한 결과를 MILE UP이라는 별칭으로 보여주세요
SELECT NAME, MILEAGE, MILEAGE*13 "MILE UP" FROM MEMBER;



=========================================================

--문자열 결합 연산자(||)
SELECT ENAME||' IS A '||JOB AS "EMPLOYEE INFO" FROM EMP;



=========================================================

--EMP에서 이름과 연봉을 "KING: 1 YEAR SALARY=6000" 형식으로 출력하세요
SELECT * FROM EMP;
SELECT ENAME||': 1 YEAR SALARY='|| (SAL*12+NVL(COMM,0)) "사원 연봉" FROM EMP;



=========================================================

--EMP에서 업무를 가져와 모두 출력하세요
SELECT JOB FROM EMP;
SELECT * FROM EMP;



=========================================================

--EMP에서 담당하고 있는 업무 종류를 출력하세요
--DISTINCT는 중복행을 제거하여 가져온다.
SELECT DISTINCT JOB FROM EMP;



=========================================================

--EMP에서 중복되지 않는 부서번호를 출력하세요
SELECT DISTINCT DEPTNO FROM EMP;



=========================================================

--부서별로 담당하는 업무를 한번씩만 출력하세요
SELECT DISTINCT DEPTNO,JOB FROM EMP ORDER BY DEPTNO ASC;



=========================================================

--EMP에서 급여가 3000이상인 사원의 사번, 이름, 업무, 급여, 입사일을 출력하세요
SELECT * FROM EMP;
SELECT EMPNO, ENAME, JOB, SAL, HIREDATE FROM EMP WHERE SAL>=3000;



=========================================================

--EMP에서 업무가 'MANAGER'인 사원의 사번, 업무, 부서번호, 급여를 출력하세요
--값의 경우 대소문자 구분을 한다
SELECT EMPNO, JOB, DEPTNO, SAL FROM EMP WHERE JOB='MANAGER';



=========================================================

--EMP에서 1982년 1월 1일 이후에 입사한 사원의 사번,이름,업무,입사일자를 출력하세요
SELECT EMPNO,ENAME,JOB,HIREDATE FROM EMP WHERE HIREDATE>='82/01/01';



=========================================================

--EMP에서 급여가 1300에서 1500사이인 사원의 이름, 업무, 급여를 출력하세요
SELECT ENAME,JOB,SAL FROM EMP WHERE sal Between 1300 and 1500;



=========================================================

--EMP에서 사번이 7902, 7788, 7566인 사원의 사번, 이름, 입사일을 출력하세요
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE
EMPNO='7902' OR EMPNO='7788' OR EMPNO='7566';
-- 위 방법보다 더 간단한 방법
SELECT EMPNO,ENAME,HIREDATE FROM EMP WHERE EMPNO IN(7902,7788,7566);

=========================================================