UIUX _국비과정 0605 [오라클DB SQL 쿼리문]

데이터를 조회하는 연산자

 

집합연산자 (합집합, 교집합, 차집합)

• 두개의 select 구문은 반드시 컬럼의 개수와 타입이 같아야 한다
• 컬럼명은 달라도 상관 없다
• 실제로는 물리적으로 다른 데이터들을 가지고 집합연산을 한다.

 

-- 합집합 (union)
select empno, ename, sal, deptno
from emp
where deptno = 10
union
select empno, ename, sal, deptno
from emp
where deptno = 20;

union → 중복 제거

union all → 중복 허용

 

-- 차집합 (minus)
select empno, ename, sal, deptno
from emp
minus
select empno, ename, sal, deptno
from emp
where deptno = 10;

-- 교집합
select empno, ename, sal, deptno
from emp
intersect
select empno, ename, sal, deptno
from emp
where deptno = 10

 

 

문제풀이) 내가 푼 ver.

-- 문제 1번
-- 이름이 s 로 끝나는 사람
select *
from emp
where ename like '%S';

-- 문제 2번
-- 30번 부서에서 근무하는 사람
select empno, ename, job, sal, deptno
from emp
where deptno = 30;

-- 문제 3번
-- 급여가 2000 초과인 사원

-- 집합연산자 사용 X
select empno, ename, sal, deptno
from emp
where sal > 2000 and (deptno = 20 or deptno = 30);

-- 집합연산자 사용
select empno, ename, sal, deptno
from emp
where sal > 2000
intersect
select empno, ename, sal, deptno
from emp
where deptno = 20 or deptno = 30;

-- 문제 4번
-- 급여 2000 이상 3000 이하 이외의 범위
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp
minus
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp
where sal >= 2000 and sal <= 3000;

-- 문제 5번
-- 이름에 E 포함, 급여 1000 ~ 2000 사이
select empno, ename, sal, deptno
from emp
where ename like '%E%'
and not (sal >= 1000 and sal <= 2000);

-- 문제 6번
-- 추가수당 X , 'MANAGER','CLERK' 직책 구분, 이름의 두번째 글자가 L이 아닌 사원
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp
where comm is null
intersect
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp
where (job = 'MANAGER' or job = 'CLERK')
and ename not like '_L%';

 

강사님 ver.

-- 문제 3번
-- 집합연산자 사용 X
select empno, ename, sal, deptno
from emp
where deptno in(20, 30)
and sal > 2000;


-- 문제 5번
-- 이름에 E 포함, 부서 30, 급여 1000 ~ 2000 사이
select empno, ename, sal, deptno
from emp
where ename like '%E%'
and deptno = 30
and sal not between 2000 and 3000;

-- 문제 6번
-- 추가수당 X , 상급자가 없고, 'MANAGER','CLERK' 직책, 이름의 두번째 글자가 L이 아닌 사원
select *
from emp
where comm is null
and mgr is not null
and job in ('MANAGER','CLERK')
and ename not like '_L%';

문제에서 놓친 조건이 있었다.. 문제를 잘 읽자!

그리고 in을 잘 활용해야겠다.

 

 

 

함수

• 문자함수
• 숫자함수
• 날짜함수

 

🍀  문자함수

-- 문자함수
-- upper 대문자 lower 소문자 initcap 첫글자 대문자 뒤는 소문자
select upper(ename), lower(ename), initcap(ename)
from emp;

select *
from emp
where ename = upper('scott');

select *
from emp
where lower(ename) = 'scott';

-- length 길이
select ename, length(ename)
from emp
where length(ename) >= 5;

-- 문자열에서 일부 추출
-- 오라클DB에서 인덱스는 1번부터 시작

-- substr (문자열, 시작위치, 개수)
-- substr (문자열, 시작위치)

select job, substr(job, 1, 2), substr(job, 5)
from emp;

-- 원하는 문자를 넣어 확인 dual
-- 문자를 변경하고 싶을 때
select '010-123-1234' as phone,
replace('010-123-1234', '-', '/'),
replace('010-123-1234', '-') -- '-'가 빠진다
from dual;

-- 문자연결
select ename || ' is a ' || job
from emp;

select concat(empno, ename)
from emp;

select concat(ename, concat(' is a ', job))
from emp;

-- instr(문자열, 찾고자하는 문자)
-- instr(문자열, 찾고자하는 문자, 문자를 찾고자하는 시작위치)
-- instr(문자열, 찾고자하는 문자, 문자를 찾고자하는 시작위치, 찾으려는 문자가 몇번째 있는 문자인지(위치))

select instr('HELLO, ORACLE','L'),
instr('HELLO, ORACLE','L', 5),
instr('HELLO, ORACLE','L', 2, 2)
from dual;

 

-- 문제

-- 1번. 입사일자 82년도 입사한 사람 조회

select *
from emp
where hiredate between '19820101' and '19821231';

-- substr() 이용

select *
from emp
where substr(hiredate, 1, 2) = '82';

-- 2번. 이름에 세번째 문자가 R인 사원 조회

select *
from emp
where ename like '__R%';

-- substr() 이용 -> 문자반환
select *
from emp
where substr(ename, 3, 1) = 'R';

-- instr() 이용 -> 위치반환
select *
from emp
where instr(ename,'R', 3, 1) = 3;

 

🍀  숫자함수

-- 숫자함수
-- 소수점(실수) 다루는 함수가 대부분
-- round() : 반올림
-- trunc() : 실수를 버림
-- ceil() : 강제 올림
-- floor() : 강제 내림
-- mod() : 나머지 구하기


-- round()
select 
round(1234.5678),
round(1234.5678,1), 
round(1234.5678,2)
from dual;

-- trunc()
select
trunc(1234.5678),
trunc(1234.5678, 1),
trunc(1234.5678, 2)
from dual;

-- ceil(), floor()
select
ceil(3.14),
floor(3.14)
from dual;

-- mod() 나머지
select
mod(15,6),
mod(11,2)
from dual;

-- 사원 중에 사번이 홀수인 사원
select *
from emp
where mod (empno,2) = 1;

 

🍀  날짜함수

-- 날짜함수
-- 연산이 가능

-- 현재 날짜 정보
select sysdate
from dual;

select sysdate - 1 as "어제",sysdate, sysdate + 1 as "내일"
from dual;

-- 날짜 데이터끼리 연산 가능
select trunc(sysdate - hiredate) as "근무일수", trunc((sysdate - hiredate)/365) as "근무년수"
from emp;

-- 달을 기준으로 연산
select sysdate, add_months(sysdate, 3)
from dual;

-- 문제
-- 근속 년수가 41년 이하인 사원 조회
select *
from emp
where trunc((sysdate - hiredate)/365) <= 41;

-- add_months()
select *
from emp
where add_months(hiredate, 12 * 43) <= sysdate;

 

---

✨ 형변환

-- 형변환
-- to_number(), to_char(), to_date()
--          숫자 ->  문자  -> 날짜
--              <-      <-

-- 날짜를 문자로
select sysdate, to_char(sysdate, 'YYYY/MM/DD DY HH:MI:SS')
from dual;

-- 숫자를 문자로
-- 0, 9
-- L 로컬(지역) 돈 단위로 처리
select sal, to_char(sal,'L9,999')
from emp;

-- 문자를 숫자로
select '1300' - '1000' -- 자동형변환
from dual;

select to_number('1,300', '9,999') - to_number('1,000', '9,999')
from dual;

-- 문자를 날짜로
select to_date('2024-01-01','YYYY-MM_DD')
from dual;

select *
from emp
where hiredate >= '1981/01/01';  -- to_date('1981/01/01','YYYY/MM/DD');ㅍ

 

-- 기타함수
-- nvl()

-- 조건문 형식 함수
-- decode(값, 비교값1, 결과1, 비교값2, 결과2, 비교값3, 결과3, 기타결과)
-- 비교값1, 결과1 -> 한쌍
-- 비교했을때 원하는 값이 없으면 기타결과가 출력
-- -> switch case 문과 유사하다
-- case 구문 -> 다중 if 문과 유사

select deptno,
decode(deptno,
10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES',
40, 'OFERATIONS',
'ETC'
)
from emp;

-- JOB이 manage인 사원의 급여를 10% 인상한 결과를 출력
-- JOB이 salesman 사원 급여를 5% 인상한 결과 출력
-- JOB이 analyst 사원은 급여를 동결한 결과 출력
-- 기타 JOB인 사원들은 3% 급여 인상

select job, sal,
decode(job,
'MANAGER', sal * 1.1,
'SALESMAN', sal * 1.05,
'ANALYST', sal,
sal * 1.03
)
from emp;


select job, sal,
case job 
when 'MANAGER' then sal * 1.1
when 'SALESMAN' then sal * 1.05
when 'ANALYST' then sal
else sal * 1.03 
end as upsal
from emp;

-- 범위비교
select job, sal,
case 
when sal >= 3000 and sal <= 5000 then sal * 1.1
when sal >= 2000 and sal < 3000 then sal * 1.05
when sal >= 1000 and sal < 2000 then sal
else sal * 1.03 
end as upsal
from emp;

 

 

맥에서 계정 풀어서 만들어야함.

oe계정 만들기

select date_of_birth, to_char(date_of_birth, 'YYYY-MM-DD') as birth
from customers;

oe.sql → 오라클에서 지원해주는 교육용 DB

------> 어떻게 해도 안돼서 그냥 노가다로 하나씩 집어넣었다;  자세한 내용은 아래 참조.

맥북 M1 오라클 21c 실습을 위한 새로운 계정 생성하기