어제 문제
— 1. emp 테이블에서 10번 부서원만 선택하여 입사 일자를 ’01 MAY 1981’형태로 변경
desc emp;
alter session set nls_date_language = 'american';
select ename, hiredate,
to_char(hiredate, 'dd monthyyyy') as 입사일자1,
to_char(hiredate, 'DD ')||
trim(to_char(hiredate,'MONTH'))||
to_char(hiredate,' YYYY') as 입사일자2
from emp
where deptno=10;
-- char타입으로 나오기 때문에 최대값에 자리수가 맞춰져서 공백이 생기는데 해결하려면 따로따로 출력해야됨.-- 2. professor테이블에서 입사연도가 2000년보다 이른 경우 pay의 10%를 인상,
-- 2000년 이후는(2000년포함) 8%를 인상하여 인상된 연봉과 함께 이름, 입사연도 출력
select name, pay,
trunc(hiredate,'year') as 입사연도,
add_months(trunc(sysdate,'month'), -9-1224) as "2000년", hiredate-add_months(trunc(sysdate,'month'), -9-1224) as "입사연도-2000년",
case when hiredate-add_months(trunc(sysdate,'month'), -9-1224)<0 then pay1.1
else pay*1.08
end as 인상된연봉
from professor;
-- 강사님 방법
select name, pay, hiredate,
to_char(hiredate, 'YYYY') as 입사연도1, -- 문자로 리턴(result창에서 왼쪽 정렬)
extract(year from hiredate) as 입사연도2, -- 숫자로 리턴(result창에서 오른쪽 정렬)
case when to_char(hiredate,'YYYY')<2000 then pay1.1 else pay1.08
end as 인상된급여
from professor;
-- 만약 숫자같은데 앞자리에 0이 있다면 문자형식인 것 ex) 06
-- '06' != 6 이니깐 문자 숫자 다룰 때는 유의-- 3. emp테이블에서 각 직원의 이름, 직업(job), 급여와 함께 입사한 날짜의 분기를 출력
select ename, job, sal, hiredate,
case to_char(hiredate,'mm') when '01' then '1분기'
when '02' then '1분기'
when '03' then '1분기'
when '04' then '2분기'
when '05' then '2분기'
when '06' then '2분기'
when '07' then '3분기'
when '08' then '3분기'
when '09' then '3분기'
else '4분기'
end as 입사분기
from emp;
-- 강사님 방법
select ename, job, hiredate, sal,
to_char(hiredate,'mm') as 입사월,
case when to_char(hiredate,'mm') in ('01','02','03') then 1
when to_char(hiredate,'mm') in ('04','05','06') then 2
when to_char(hiredate,'mm') in ('07','08','09') then 3
else 4
end as 분기1,
to_char(hiredate, 'Q') as 분기2,
case when to_char(hiredate,'mm') between '01' and '03' then 1
when to_char(hiredate,'mm') between '04' and '06' then 2
when to_char(hiredate,'mm') between '07' and '09' then 3
else 4
end as 분기3
from emp;-- 4. emp테이블을 사용하여 연봉기준 등급을 아래의 기준에 맞게 표현하세요.
-- 2000미만 'C', 2000이상 3000이하 'B', 3000초과 'A'
-- 단, decode문, case문 각각 작성
-- decode 문에서 딱 3000인 경우를 어떻게 처리하지?? decode안에 decode로 해결했다.
select ename, sal,
-- decode(sign(trunc(sal/1000)-2),-1,'C',0,'B',1,decode(sal,3000,'B','A')) as "decode 등급",
decode(sign(sal-2000),-1,'C',0,'B',1,decode(sal,3000,'B','A')) as "decode 등급",
case when sal<2000 then 'C' when sal>3000 then 'A'
else 'B'
end as "case 등급"
from emp;
-- 강사님 방법 : decode 1번만 쓰는 방법
select ename, sal,
decode(sign(sal-2000)+sign(sal-3000), -2,'C', 2,'A','B') as "decode 등급",
case when sal<2000 then 'C' when sal>3000 then 'A'
else 'B'
end as "case 등급"
from emp;-- 5. emp 테이블을 이용하여 현재까지 근무일수를 XX년 XX개월 XX일 형태로 출력
-- ex) 400일->1년 1개월 4일
select hiredate,
months_between(sysdate, hiredate) as 근무개월수,
trunc(months_between(sysdate, hiredate)/12) as 년수,
trunc(mod(months_between(sysdate, hiredate), 12)) as 개월수,
case when to_char(sysdate,'dd')-to_char(hiredate,'dd')>=0 then to_char(sysdate,'dd')-to_char(hiredate,'dd')
else 30+to_char(sysdate,'dd')-to_char(hiredate,'dd')
end as 일수,
trunc(months_between(sysdate, hiredate)/12)||'년'||
trunc(mod(months_between(sysdate, hiredate), 12))||'개월'||
case when to_char(sysdate,'dd')-to_char(hiredate,'dd')>=0 then to_char(sysdate,'dd')-to_char(hiredate,'dd')
else 30+to_char(sysdate,'dd')-to_char(hiredate,'dd')
end||'일' as 근무일수
from emp;
-- 강사님 방법
select hiredate,
trunc(months_between(sysdate, hiredate)) as 근무개월수,
trunc(months_between(sysdate, hiredate)/12) as 년수,
trunc(mod(months_between(sysdate, hiredate), 12)) as 개월수,
-- 근무개월수를 이용해서 남은 일수를 계산함
trunc(sysdate-add_months(hiredate, trunc(months_between(sysdate, hiredate)))) as 나머지일수
from emp;수업 내용
— [변환함수]
— 1. to_char
— 1) 숫자->문자
— – 숫자의 포맷변경(천단위 구분기호 삽입, $기호 삽입)
— – 문자와 비교하기 위한 단순 타입 변경
— 숫자 포맷
— – 9 : 숫자 한 자리수 표현 포맷(부족한 자리수를 공백으로 채움)
— – 0 : 숫자 한 자리수 표현 포맷(부족한 자리수를 0으로 채움)
— – $ : $기호 삽입. ‘원’기호는 안됨.
— – , : 천단위 구분기호 삽입 ex) to_char(sal, ‘999,999,999’)
— 2) 날짜->문자
— – 날짜의 일부 추출(문자로 리턴)
— – 날짜의 출력 포맷 변경
— 2. to_number : 문자->숫자
— 문자를 숫자와 연산 또는 비교하기 위해 숫자 타입으로 변경할 때 주로 사용
— 묵시적 형변환에 의해 자주 발생
— 문자를 숫자랑 비교하면 숫자 맞춰준다. -> to_number 사용
— 3. to_date
— 1) 문자->날짜 : 날짜처럼 생긴 문자를 날짜타입으로 인식시키기 위해 사용(날짜파싱)
— 2) 숫자->날짜 : 날짜처럼 생긴 숫자를 날짜타입으로 인식시키기 위해 사용
— 사용법 : to_date(대상[, 포맷])
— 포맷은 생략이 가능하지만 웬만하면 써서 직접 지정하자
select to_date(‘2024/09/26′,’YYYY/MM/DD’)+100 from dual;
-- 예제) 보너스를 2020년 10월 1일에 부여,
-- 보너스는 보너스 부여날짜 기준으로 근속연수기본급여(sal)0.1
-- 직원의 급여와 보너스를 이름과 함께 출력
select ename, sal,
trunc(months_between(to_date('20201001','YYYYMMDD'), hiredate)/12) as 근속연수,
trunc(months_between(to_date('20201001','YYYYMMDD'), hiredate)/12)sal0.1 as 보너스
from emp;-- 예제) student 테이블에서 각 학생의 이름, 학년, 생년월일 출력
-- 단, 생년월일은 jumin컬럼을 사용하여 날짜형태로 출력하되 아래와 같은 형태로 표현
-- 10-24,1990
select name, grade, jumin,
substr(jumin, 1, 6) as 생년월일문자,
to_date(substr(jumin, 1, 6),'RRMMDD') as 생년월일날짜, -- YY로하면 2075년 나옴
to_char(to_date(substr(jumin, 1, 6),'RRMMDD'),'MM-DD,YYYY') as 생년월일출력형식
from student;
-- YY : y2k(2000년) 이후 연도 해석
-- RR : y2k 이전/이후 연도 해석
-- ~49 : 2000년대로 해석
-- 50~ : 1900년대로 해석
-- RR의 분류가 꼬우면 직접 가공해야됨-- DBMS 설정 날짜 파라미터 확인
select *
from nls_session_parameters
where PARAMETER in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE'); -- RR/MM/DD-- 세션의 날짜 포맷 변경
-- 예제) 오늘 날짜의 '일' 추출
select sysdate,
substr(sysdate,4,2) as 월1, -- 정확한 추출 불가능(DBMS세팅에 따라 달라짐)
to_char(sysdate, 'mm') as 월2, -- 정확한 추출 가능
extract(month from sysdate) as 월3 -- 정확한 추출 가능
from dual;
-- substr으로 정확한 추출이 되지 않는 이유는 눈에 보이는 날짜의 형식(orange같은 툴에 설정된 포맷)과
-- DBMS의 기본 날짜 포맷이 서로 달라서.
-- 위에서 확인했듯 DBMS 세팅은 RR/MM/DD인데 orange는 2024/10/18 12:47:19 이런 식이다.— 이걸 해결하려고 alter session set으로 설정을 바꿔버리면 쿼리를 치고싶을 때마다 세션설정을 변경해야된다.
— 즉, 해당 쿼리는 내 환경에서만 잘 작동하는 쿼리.
— 그러니 불편하면 자세를 고쳐앉으라는 것처럼 orange의 설정을 db설정이랑 맞추든가
— 아예 substr을 쓰지말자.
— [날짜비교]
— 모든 비교는 비교대상과 상수가 서로 데이터타입이 일치해야 함
— 날짜와 문자 비교 시 문자를 날짜로 바꿔서 날짜로 맞추려 함
-- 예제) 1981년 4월 10일 이전에 입사한 직원의 이름과 입사일 출력
select ename, hiredate
from emp
-- where hiredate<'1981/04/10' -- 현재 DBMS의 포맷이 RR/MM/DD로 '년/월/일 순서'이기때문에 자동형변환이 가능했다. where to_date('19810410','YYYYMMDD') > hiredate;— [그룹함수(집계함수)]
— 1. count
— 사용법 : count(대상)
— 대상에는 *또는 숫자, 문자, 날짜값을 갖는 컬럼명
— null은 무시
— 집계함수는 일반 컬럼이랑 같이 select절에 넣을 수 없다.
— null집합의 count결과는 0 리턴
-- 예제) 직원 수
desc emp;
select count(*),
count(ename),
count(empno),
count(comm)
from emp;
-- *을 이용해서 테이블의 행의 개수를 확인하려고 하면 시간이 오래 걸린다.
-- 따라서 not null속성인 컬럼만 세는게 빠르다.(empno)-- 예) null 집합의 count 결과
select count(comm)
from EMP
where comm is null;— 2. sum
— 사용법 : sum(대상)
— null은 무시
— null집합의 sum 결과는 null 리턴
select sum(sal), sum(comm)
from emp;
-- 예) null 집합의 sum 결과
select sum(comm)
from emp
where comm is null;— 3. min/max
-- null 집합의 min/max 결과는 null 리턴
select min(comm), max(comm)
from emp
where comm is null;— 4. avg
select avg(comm), -- 4명에 대한 평균
sum(comm)/count(*), -- 14명에 대한 평균
avg(nvl(comm,0)) -- 14명에 대한 평균
from emp;-- null 집합의 avg 결과는 null 리턴
select avg(comm)
from emp
where comm is null;— [group by절]
— select문의 4번째 위치
— 그룹별 연산을 위해 사용
— 주의점 : group by에 명시되지 않은 컬럼은 select절, order by절, having절에
— 집계함수를 사용하지 않고는 표현할 수 없다. 쿼리 처리순서에 따른 것.
— group by 사용 시 기본적으로, 자동적으로 정렬이 된다.
-- 예제) 부서별 급여평균
select deptno, round(avg(sal)) as 급여평균
from EMP
group by deptno;-- 예제) 부서별 최대값
select deptno, max(sal) as 최대급여
from EMP
group by deptno;select deptno, count(ename)
from EMP
group by deptno;select deptno, sum(sal)
from EMP
group by deptno
having sum(sal)>=3000;select deptno, sum(sal)
from EMP
group by deptno
order by sum(comm) desc;— [having]
— group by 이후 수행
— group by절과 작성순서를 변경할 수 있지만 실행순서는 group by가 항상 먼저다.
— group by 수행 결과에 조건을 전달하여 원하는 그룹을 선택하기 위해 사용
— 집계함수 사용 가능
-- 예제) emp 테이블에서 부서별 급여평균을 구한 뒤, 급여평균이 2000 이상인 그룹만 출력
select deptno, avg(sal)
from EMP
group by deptno
having avg(sal)>=2000;-- 예제) student 테이블에서 전공별(deptno1) 학생들의 키 평균을 출력
-- 단, 101번 학과는 제외
select round(avg(height),2), deptno1
from student
where deptno1!=101
group by deptno1;
-- having deptno1!=101;도 가능함
-- where절 사용이 정답. 어차피 제거할거면 제거하고 grouping이 성능에 더 좋다.
-- 즉, 일반조건(그룹함수 사용x)은 where절 사용이 성능이 더 좋다.
-- having은 where절에서 사용할 수 없는 그룹함수를 사용한 필터링만 쓰자.— [연습문제]
-- 1. professor 테이블을 사용하여 교수와 강사 각각의 급여 평균 출력.
select substr(position, -2) as 직업, round(avg(pay),2) as 급여평균
from professor
group by substr(position, -2);-- 2. student 테이블에서 3,4학년 학생들에 대해 성별 키평균, 몸무게평균 출력.
select substr(jumin, 7,1) as 성별,
round(avg(height),2) as 키평균,
avg(weight) as 몸무게평균
from student
where grade in (3,4)
group by substr(jumin, 7,1);-- 3. student 테이블에서 지역별 학생 수 출력.
desc student; -- not null인 컬럼 찾아서
select substr(tel, 1, instr(tel, ')')-1) as 지역번호,
count(studno) as 학생수 -- 여기에 쓰기
from student
group by substr(tel, 1, instr(tel, ')')-1)
order by 1;오늘 문제
-- 1. student 테이블에서 1976년 1월에 태어난 학생의 이름, 학년, 생년월일 출력.
select name, grade, substr(jumin, 1, 6) as 생년월일
from student
where substr(jumin,1,4) = '7601';-- 2. emp 테이블에서 september 28, 1981에 입사한 사원의 이름, 업무, 입사일자를 출력.
-- 단, where절에 'september 28, 1981' 그대로 사용(변경 X)
select ename, job, hiredate,
to_char(hiredate, 'month DD, RRRR')
from emp
where to_char(hiredate, 'month DD, RRRR')='september 28, 1981';-- 3. student 테이블에서 jumin 컬럼을 사용하여 생년월일이 다음 사이인 학생의 정보를 출력.
-- (19760205 ~ 19760924)
select *
from student
where '19'||substr(jumin,1,6) between '19760205' and '19760924';-- 4. professor 테이블에서 입사연도별 최대급여, 최소급여, 평균급여 출력.
-- 단, 입사연도는 1980, 1990, 2000, 2010년대로 표현
-- ex) 1998 -> 1990년대
desc professor;
select trunc(to_char(hiredate,'YYYY'),-1) as 입사연도,
count(profno) as 교수수,
max(pay) as 최대급여,
min(pay) as 최소급여,
round(avg(pay)) as 평균급여
from professor
group by trunc(to_char(hiredate,'YYYY'),-1)
order by 1;-- 5. emp 테이블을 이용하여 각 부서별 직원수를 출력하되 다음과 같은 형식으로 작성하여라.
-- 10_직원수 20_직원수 30_직원수 -> 컬럼명
-- ------- --------- ---------
-- 3 5 6 -> 실제 count 결과
-- 5. emp 테이블을 이용하여 각 부서별 직원수를 출력하되 다음과 같은 형식으로 작성하여라.
-- 10_직원수 20_직원수 30_직원수 -> 컬럼명
-- ------- --------- ---------
-- 3 5 6 -> 실제 count 결과
-- 모르겠어서 chatgpt 씀..
select count(empno) as "직원수",
sum(case when deptno=10 then 1 else 0 end) as "10_직원수",
sum(case when deptno=20 then 1 else 0 end) as "20_직원수",
sum(case when deptno=30 then 1 else 0 end) as "30_직원수"
from emp;
select sum(decode(deptno,10,1)) as "10_직원수",
sum(decode(deptno,20,1)) as "20_직원수",
sum(decode(deptno,30,1)) as "30_직원수"
from emp;