Oracle 2일차

— 1. emp 테이블에서 담당업무가 MANAGER인 사원의 사원번호, 성명, 업무, 급여, 부서번호(deptno)를 출력하라

select empno, ename, job, sal, deptno
from emp
where upper(job) = ‘MANAGER’; — 싹 다 대문자나 소문자로 바꿔놓고 하면 헷갈려도 해결가능
— 또는 initcap() 을 써서 카멜표기법 형태로 바꿀 수도 있음

— 2. emp 테이블에서 job이 MANAGER, CLERK, ANALYST가 아닌 사원의 사원번호, 성명, 업무, 급여, 부서번호를 출력하라

select empno, ename, job, sal, deptno
from EMP
where job in (‘MANAGER’, ‘CLERK’, ‘ANALYST’);

— 3. emp 테이블에서 15% 인상된 급여가 3500 이상인 직원의 이름, 기존급여, 인상된 급여를 출력하라

select ename, sal as “기존 급여”, sal1.15 as “인상된 급여” 
from EMP 
where sal1.15 >= 3500;

— 4. emp2 테이블에서 정규직과 인턴직을 제외한 직급 중 부서번호가 1008번이거나 취미가 오락인 직원의 이름, 고용형태, 부서번호, 취미 출력

select name, emp_type, deptno, hobby
from emp2
where emp_type not in (‘정규직’, ‘인턴직’)
and (deptno = 1008 or hobby = ‘오락’); — 괄호 안 넣으면 and 가 or 보다 먼저 수행됨

— 5. student 테이블에서 “ㅅ” 성인 학생의 이름, 학번, 제1전공번호(deptno1) 출력

select name, studno, deptno1
from student
WHERE NAME >= ‘사’
AND NAME < ‘아’;

— 테이블 목록 확인

select * from tab;

— SYSTEM 계정으로 접속 시

select * from EMPLOYEES; -- 조회 불가(테이블 소유자 문제)
select * from HR.EMPLOYEES; -- 조회 가능

— SCOTT 계정으로 접속 시

select * from HR.EMPLOYEES; -- 조회 불가(권한이 없어서. employees는 HR 소유)

— [계정 관리]

alter user SCOTT account unlock; -- 계정 잠금 해제
alter user SCOTT account lock; -- 계정 잠금
alter user SCOTT account identified by "oracle"; -- 계정 패스워드 변경
alter user SCOTT account unlock identified by "oracle"; -- 계정 잠금 해체 + 패스워드 변경

— [ORDER BY 절]
— 정렬
— 오름차순, 내림차순 정렬 순서 전달 가능(기본: 오름차순)
— 정렬을 수행하지 않으면 데이터의 입력 순서대로 출력
— order by절에 컬럼을 나열하여 1차정렬, 2차정렬, 3차정렬 … 가능
— 각 컬럼별로 정렬순서 전달 가능 (asc, desc)

— 예제) emp에서 이름 순으로 정렬하여 모든 직원의 이름, 급여, 부서번호 출력

select ENAME, SAL, DEPTNO
from EMP
order by ENAME;

— 예제) emp에서 부서별로 이름 순으로 정렬하여 모든 직원의 이름, 급여, 부서번호 출력

select ENAME, SAL, DEPTNO
from EMP
order by deptno, ename;

— 예제) student 테이블에서 모든 학생의 이름, 학년, 키를 학년별로 4학년부터 키가 큰 순서대로 출력

select name, grade, height
from student
order by grade desc, height desc;

— 컬럼 별칭을 사용한 정렬 가능

select name, grade as 학년, height as 키
from student
order by 학년 desc, 키 desc;

— 컬럼 순번(select절에 나열된 순서)을 사용한 정렬 가능

select name, grade, height
from student
order by 2 desc, 3 desc;

— 쌍따옴표의 경우 필수인 애는 쌍따옴표로 전달해야되고 없어도 되는 애는 없이 전달해도 됨

select name, grade as "grade!", height as "키"
from student
order by "grade!" desc, 키 desc;

— [distinct]
— 중복행을 제거하여 한 번만 출력하기 위함
— select절 바로 뒤에 단 한 번만 사용

select distinct deptno
from emp;

select distinct deptno, job
from emp
order by deptno, job;

— [연습문제]
— 1. emp 테이블에서 가장 최근에 입사한 순으로 사원번호, 이름, 업무, 급여, 입사일자, 부서번호 출력

select empno, ename, job, sal, hiredate, deptno
from EMP
order by hiredate desc;

— 2. emp 테이블에서 job이 President이고 급여가 1500 이상이거나 업무가 salesman인 사원의
— 사원번호, 이름, 업무, 급여를 출력하여라

select empno, ename, job, SAL
from EMP
where job='PRESIDENT' and sal>=1500
or job='SALESMAN';

— 3. emp 테이블에서 급여가 1100 이상이거나, 이름이 M으로 시작하지 않는 사원의
— 사원번호, 이름, 담당업무, 급여, 입사일자, 부서번호를 출력

select empno, ename, job, sal, hiredate, deptno
from EMP
where sal>=1100
or ename not like 'M%';
-- or ename < 'M' or ename > 'M';

— [논리연산자]
— not > and > or 의 우선순위

— [연결연산자 ||]
— 문자열과 문자열을 결합하여 하나로 표현하기 위해 사용
— 문자열과 문자열 사이에 삽입

select 'ab'||'cd'||'ㅁㄴㅇㄹ'
from dual;

— 예제) emp에서 모든 직원의 사번과 이름을 7369_SMITH 형태로 출력

select empno, ename, empno||''||ename as 사번이름
from emp;

— 예제) emp에서 각 직원의 정보를 아래와 같이 출력
— SMITH의 10% 인상된 급여는 880입니다.

select ename||'의 10% 인상된 급여는 '||sal*1.1||'입니다.' as 출력
from emp;

— 어제 2번퀴즈) emp2 테이블에서 정규직과 인턴직을 제외한 직급 중 부서번호가 1008번이거나 취미가 오락인 직원의 이름, 고용형태, 부서번호, 취미 출력

update emp2
set hobby = '오락'
where name = '백원만'; -- 테스트를 위해 임시 데이터 수정
-- case1) 잘못된 쿼리. 정규직인 '백원만'이 출력됨.
select *
from emp2
where emp_type not in ('정규직', '인턴직')
and deptno = 1008 or hobby = '오락';
-- case2) 옳은 쿼리
select *
from emp2
where emp_type not in ('정규직', '인턴직')
and (deptno = 1008 or hobby = '오락');

rollback; — update 취소

— [함수]
— input value(인수)와 output value와의 관계를 표현하는 객체
— 대체적으로 하나 이상의 input value 필요
— 인수 없는 함수도 존재. ex)sysdate
— 반드시 output value return 함
— 사용자가 직접 정의하여 사용 가능(PL/SQL)
— from절을 사용한 모든 select문의 절에서 사용 가능

— 함수의 종류
— 1) input value 와 output value의 관계에 따라
— 단일행 함수: 한 쌍의 인수에 매칭되는 하나의 결과값이 리턴되는 형태의 함수
— ex) upper(*)
— 다중행 함수(그룹함수): 여러 행의 데이터가 하나의 결과로 리턴되는 형태의 함수
— ex) sum(), max(), min(), avg(), count()

— 2) 인수나 결과값의 데이터 타입에 따라
— 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수

— [문자함수]
— 1. 대소문자 치환: upper(), lower(), initcap()
— 사용법: upper(대상)

select initcap('ABC_Ddsf sdf') from dual;

— 2. 문자열 추출: substr()-문자열 일부를 추출하는 함수. 문자열로 리턴됨.
— 사용법: substr(원본대상, 시작위치[, 추출할 개수])
— 마지막 인수(추출할 개수)는 생략가능. 생략 시 끝까지 추출
— 시작위치는 음수를 넣어서 뒤에서부터 추출 가능

select substr('abcdefghijk', -5)
from dual;

— 예제) student 테이블에서 모든 여학생의 이름, 학년, 주민번호 출력
desc student; — 테이블의 컬럼 속성 확인

select name, grade, jumin
from student
where substr(jumin, 7, 1) = '2'
or substr(jumin, 7, 1) = '4';
--where jumin like '______2%'; --이것도 가능

— 숫자인데 어떻게 문자열이랑 처리가 됐지??
— 자동 형변환이 일어남.
— 근데 그 과정에서 자원 소모되니깐 엥간해서는 자동 형변환 안되게 잘 하자

— 그렇다면 문자열이 아닌 것도 substr()에 들어갈 수 있나??

select *
from student
where substr(stdno, 2, 1) = '4'; -- 에러나네;

— 예제) professor 테이블에서 박씨 성의 교수에 대한 이름, 직급, 급여 출력

select name, position, pay
from professor
--where name like '박%';
where substr(name, 1, 1) = '박';

— 3. 문자열 위치: instr()-문자열에서 일부 문자열의 위치를 찾을 때 사용. 숫자 리턴됨.
— 사용법: instr(원본대상, 찾고싶은 문자열[, 시작위치, 발견횟수])
— 시작위치, 발견횟수 생략가능(생략 시 1)
— 시작위치 음수 가능(뒤에서부터의 위치 지정, 스캔방향이 오른쪽에서 왼쪽으로 substr 이랑은 다르다)
— instr() 돌렸는데 값 못찾으면 0 리턴. 즉 있으면 1이상, 없으면 0

select 'a#b#c#d#e#', instr('a#b#c#d#e#', '#', -6, 1)
from dual;

— 예제) 이메일에서 이메일 아이디만 뽑기

select substr(email, 1, instr(email, '@')-1) as "이메일 아이디만"
from professor;
--where substr(email, 1, instr(email, '@')-1); -- 에러 발생
-- where절에는 조건이 들어가야됨.

— 4. 문자열 길이: length(): 문자열의 수, lengthb(): 문자열의 크기(bytes)
— 문자열의 길이를 숫자로 리턴

select length('한글'), lengthb('한글')
from dual;

— 5. 문자열 치환/삭제: replace(): 문자열 치환 또는 삭제
— 사용법: replace(원본대상, 찾을 문자열[, 바꿀문자열])
— 바꿀 문자열 생략 가능. 생략 시 또는 빈 문자열(”) 전달 시 문자열 삭제

select 'abcba', replace('abcba', 'ab', '')
from dual;

— 예제) student 테이블에서 4학년 학생의 이름, 학년, 주민번호 출력
— 단, 주민번호 뒷자리 마스킹하기

select name, grade,
replace(jumin, substr(jumin, 7), 'xxxxxxx') as 주민번호마스킹,
substr(jumin, 1, 6)||'xxxxxxx' as 다른방식
from student
where grade = 4;

— 문제(24.10.15 Tuesday)
— 1. student 테이블에서 각 학생의 이름, 전화번호를 출력
— 단, 전화번호는 다음과 같은 형태로 출력
— 055)381-2158 -> 0553812158

select name, replace(replace(TEL, ')', ''), '-', '') as 전화번호
from student;

— 2. student 테이블에서 각 학생의 이름, 학년, 생년월일 출력(주민번호에서 추출)
— 단, 이름의 두번째 글자를 마스킹 처리하여 표현

select replace(name, substr(name, 2, 1), 'x') as 이름, grade,
substr(jumin, 1, 6) as 생년월일
from student;

— 3. student 테이블에서 각 학생의 이름, 지역번호와 국번 각각 추출
— 단, 055)381-2158에서 지역번호는 055, 국번은 381을 의미

select name,
substr(TEL, 1, instr(TEL, ')')-1) as 지역번호,
substr(TEL, instr(TEL, ')')+1, instr(TEL, '-')-instr(TEL, ')')-1) as 국번
from student;

— 4. HR 계정에서 수행
— EMPLOYEES 테이블에서 EMAIL에 LAST_NAME이 포함되어 있는 직원의
— EMPLOYEE_ID, LAST_NAME, EMAIL 출력 (대소 구분x) (총 85건)
— OConnell DOCONNEL (X)
— Grant DGRANT (O)
— Whalen JWHALEN (O)
— De Haan LDEHAAN (X)

select EMPLOYEE_ID, LAST_NAME, EMAIL
from EMPLOYEES
where instr(EMAIL, upper(LAST_NAME)) > 0;