어제 문제
-- 1. student 테이블에서 1976년 1월에 태어난 학생의 이름, 학년, 생년월일 출력.
select name, grade, substr(jumin, 1, 6) as 생년월일
from student
where substr(jumin,1,4) = '7601';-- 강사님 방법
select name, grade, birthday
from student
where to_char(birthday, 'YYMMDD') like '7601%';
select name, grade, birthday
from student
where birthday between to_date('1976/01/01','YYYY/MM/DD')
and to_date('1976/01/31','YYYY/MM/DD');-- 참고 : 일부 날짜만 갖는 문자열의 날짜 파싱 결과
select to_date('1976/01', 'YYYY/MM'), -- 1976/01/01 00:00:00 (일은 default=1)
to_date('1976/12', 'YYYY/MM'), -- 1976/12/01 00:00:00
to_date('1976', 'YYYY'), -- 1976/10/01 00:00:00 (월은 default=현재 월)
to_date('07', 'MM') -- 2024/07/01 00:00:00 (연도도 현재 연도가 default)
from dual;— 2. emp 테이블에서 september 28, 1981에 입사한 사원의 이름, 업무, 입사일자를 출력.
— 단, where절에 ‘september 28, 1981’ 그대로 사용(변경 X)
— 더 좋은 쿼리는 변수(컬럼)말고 상수만 변환하는 것. 즉 sol2가 더 좋다.
— 이유1. 인덱스를 사용 가능하다.
— 이유2. 컬럼을 변환할 경우 모든 행에 변환함수가 실행되므로 성능이 저하
— 이유3. 쿼리의 가독성. 더 직관적이다.
-- sol1) 날짜를 문자로 변경 후 문자끼리 비교
select ename, job, hiredate,
to_char(hiredate, 'month DD, RRRR')
from emp
where to_char(hiredate, 'month DD, RRRR')='september 28, 1981';-- sol2) 문자를 날짜로 변경 후 날짜끼리 비교.
alter session set nls_date_language = 'american';
select ename, job, hiredate,
to_char(hiredate, 'month DD, RRRR')
from emp
where hiredate=to_date('september 28, 1981','month DD, YYYY');-- 3. student 테이블에서 jumin 컬럼을 사용하여 생년월일이 다음 사이인 학생의 정보를 출력.
-- (19760205 ~ 19760924)
select *
from student
where '19'||substr(jumin,1,6) between '19760205' and '19760924';-- 강사님 방법 - 가장 정확한(어느 DBMS에서도 오류없는)
select *
from student
where to_date(substr(jumin,1,6), 'RRMMDD') between to_date('19760205','YYYYMMDD')
and to_date('19760924','YYYYMMDD');-- 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;-- 강사님 방법
select
-- hiredate, substr(to_char(hiredate,'YYYY'),1,3)||'0' as 연대1,
trunc(to_char(hiredate, 'YYYY'), -1) as 연대2,
max(pay), min(pay), avg(pay)
from professor
group by trunc(to_char(hiredate, 'YYYY'), -1)
order by 연대2;-- 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;
select count(case when deptno=10 then 1 end) as "10_직원수",
count(case when deptno=20 then 1 end) as "20_직원수",
count(case when deptno=30 then 1 end) as "30_직원수"
from emp;수업 내용
— group by, having과 집계함수 사용 시 null에 관련된 예제 3개
-- 예제) null 집합에 대한 count->0, sum->null 을 갖는 한 건 출력
select count(comm), sum(comm)
from EMP
where comm is null;-- 예제) 공집합에 대한 count->0, sum->null을 갖는 한 건 출력
select count(comm), sum(comm)
from EMP
where comm<0;-- 예제) having 사용 시 조건에 만족하는 그룹이 없을 경우 공집한 출력
select count(comm), sum(comm)
from EMP
group by deptno
having sum(comm)>5000;— [외부 데이터(csv, txt 등) 적재]
— 1. oracle sqlldr 명령어 사용
— 2. orange 개발툴 사용
— 절차)
— 1) table 생성
drop table movie;
create table movie(
년 number,
월 number,
일 number,
지역시도 varchar2(20),
지역시군구 varchar2(20),
지역읍면동 varchar2(20),
성별 varchar2(20),
연령대 varchar2(20),
이용비율 number(7,5) -- 7자 중에 5자만 소수부로 정의
);
select *
from movie;— 2) 적재-ORANGE 기준
— Tools->load tool->테이블주인 선택->테이블이름 선택->
— 적재할 파일 선택->’첫행이 컬럼명’같은 거 필요 시 체크->
— ‘truncate before loading’체크(테이블 기존데이터 지우기)->
— F5 눌러서 실행 후 success뜨는 거 확인 (commit은 별도로 진행할 필요X)
— [연습문제]
— movie테이블에서
select *
from movie;
-- 1. 성별 이용비율 출력
select 성별, sum(이용비율)
from movie
group by 성별;-- 2. 연령대별 이용비율 출력(단, 60대이상은 출력X)
select 연령대, sum(이용비율)
from movie
where 연령대!='60대이상'
group by 연령대
order by 2 desc;-- 3. 지역시도별 이용비율 출력(단, 이용비율이 10%를 넘는 지역만 출력)
select 지역시도, sum(이용비율)
from movie
group by 지역시도
having sum(이용비율)>10;-- 4. 요일별 이용비율 출력
select 년, 월, 일,
년||to_char(월,'00')||to_char(일,'00') as 문자열,
to_date(년||to_char(월,'00')||to_char(일,'00'),'YYYY MM DD') as 날짜,
next_day(to_date(년||to_char(월,'00')||to_char(일,'00'),'YYYY MM DD'),1) as 일요일
from movie;
select case mod(일, 7) when 4 then '일요일'
when 5 then '월요일'
when 6 then '화요일'
when 0 then '수요일'
when 1 then '목요일'
when 2 then '금요일'
else '토요일'
end as 요일,
sum(이용비율)
from movie
group by mod(일, 7)
order by mod(일, 7);
-- 강사님 방법 - 결합->날짜파싱->요일출력(날짜포맷변경)
-- sol1) 날짜구분기호 삽입
select to_date(년||'/'||월||'/'||일,'YYYY/MM/DD')
from movie;
-- sol2) 월, 일 날짜를 모두 두글자로 변경
alter session set nls_date_language='korean';
select to_char(to_date(년||lpad(월,2,0)||lpad(일,2,0),'YYYYMMDD'), 'day') as 요일
from movie
group by to_char(to_date(년||lpad(월,2,0)||lpad(일,2,0),'YYYYMMDD'), 'day');— [집합연산자]
— 합집합, 교집합, 차집합의 리턴을 도와주는 표현식
— 집합 : select문 결과
— 1. 합집합 : union / union all
— union : 중복된 값을 제거하여 한 번만 출력
— **중복된 걸 처리한다->정렬을 한다.->
— union과 union all의 결과가 같을 때는 union all을 써야 속도가 빠르다.
— union all : 중복된 값 모두 출력
-- 예제) emp와 emp_test1 테이블 데이터의 합집합 출력
-- CreateTableAsSelect(씨타스) - 테이블 백업할 때 주로 사용함
create table emp_test1
as
select *
from emp
where deptno in (10, 20);
insert into emp_test1(empno, ename, sal, deptno)
values(9999, '홍길동', 5000, 20);
commit;
select ename, sal, deptno
from EMP
union
select ename, sal, deptno
from emp_test1;— 2. 교집합 : intersect
select ename, deptno
from emp
intersect
select ename, deptno
from emp_test1;
— 3. 차집합 : minus
select ename, deptno
from emp
minus
select ename, deptno
from emp_test1;
— 집합연산자 사용 시 주의사항
— 1) 각 집합의 출력 컬럼 수 일치
— 2) 각 집합의 출력 컬럼순서 일치
— 3) 각 집합에서 같은 위치에 있는 컬럼의 데이터타입 일치 – 가장 중요
— 4) 집합연산자를 사용한 쿼리문에서 각 집합에 order by 사용불가
— 맨 마지막에 전체에 대해 order by 사용 가능
— 5) group by 는 제한 없음. 대신 컬럼명 위치 잘 맞춰야해서 null 삽입
select deptno, null as job, sum(sal) as sum_sal
from EMP
group by deptno
union all
select null, job, sum(sal)
from EMP
group by job;
— [JOIN]
— 분리된 두 테이블의 데이터를 동시에 한 쿼리로 출력하거나,
— 다른 테이블의 데이터를 참조하는 조건이 필요할 경우 사용
— oracle표준과 ansi표준 존재
— oracle표준 : join에 필요한 테이블을 from절에 컴마로 나열.
— 조인조건은 일반조건과 마찬가지로 where절에 나열.
— ansi표준 : join에 필요한 테이블과 조인의 형태를 from절에 기술(컴마 전달 불가)
— 조인조건은 일반조건과 분리되어 on/using절에 나열
— inner join에서는 null을 무시함(버림)
— outer join에서는 null 포함함
— join 조건이 =면 equi join, 아니면 non_equi join
select *
from emp, dept; — cartesian product = cross join 이거 쓰면 데이터가 겁나많이 나와서 절대 쓰면 안 됨
-- 예제) emp테이블에서 사원번호, 사원명, 급여와 함께 dept테이블을 참조하여 각 직원의 부서명 출력
-- oracle 표준
select e.empno, e.ename, e.sal, d.dname
from EMP e, DEPT d -- 테이블 별칭을 정의했다면 테이블 별칭만 써야한다
where e.deptno=d.deptno;
-- ansi 표준
select e.empno, e.ename, e.sal, d.dname
from emp e join dept D
on e.deptno=d.deptno;-- 예제) emp테이블에서 30번 부서원을 제외한 직원의 사원번호, 사원명, 급여와 함께
-- dept테이블을 참조하여 각 직원의 부서명 출력
-- oracle 표준
select e.empno, e.ename, e.sal, d.dname
from EMP e, DEPT d
where e.deptno=d.deptno
and e.deptno!=30;
-- ansi 표준
select e.empno, e.ename, e.sal, d.dname
from emp e join dept D
on e.deptno=d.deptno -- join과 on은 세트다. 즉 바로 아래에 기술돼야한다
where e.deptno!=30;-- 예제) 각 학생의 학번, 이름, 학년, 제1전공명(department) 출력
-- oracle 표준
select s.studno, s.name, s.grade, d.dname
from student s, department d
where s.deptno1=d.deptno;
-- ansi 표준
select s.studno, s.name, s.grade, d.dname
from student s inner join department d
on s.deptno1=d.deptno;-- 예제) student, professor 테이블을 사용하여 각 학생의 학번, 이름, 학년, 지도교수이름 출력
-- oracle 표준
select s.studno, s.name as 학생명, s.grade, p.name as 교수명
from student s, professor p
where s.profno = p.profno;
-- ansi 표준
select s.studno, s.name as 학생명, s.grade, p.name as 교수명
from student s inner join professor p
on s.profno = p.profno;-- 예제) gogak과 gift 테이블을 이용해서 고객이름, 고객의포인트, 가져갈 수 있는 제일 좋은 상품 출력
-- oracle 표준
select o.gname, o.point, i.gname
from gogak o, gift i
where o.point between i.g_start and i.g_end;
-- ansi 표준
select o.gname, o.point, i.gname
from gogak o inner join gift i
on o.point between i.g_start and i.g_end;오늘 문제
-- 1. professor, department 테이블을 사용하여 각 교수의 이름, 입사일, 직급과 함께 소속 학과명을 출력
select * from professor;
select * from department;
select p.name, p.hiredate, p.position, d.dname
from professor p, department d
where p.deptno=d.deptno;-- 2. 아래 sql을 실행 후
-- emp, emp_sal테이블을 사용하여 각 부서별로 평균급여보다 높은 급여를 받는 직원의 이름, 입사일, 급여 출력
create table emp_sal
as
select deptno, avg(sal) as avg_sal, max(sal) as max_sal, min(sal) as min_sal
from EMP
group by deptno;
select * from emp;
select * from emp_sal;
select e.ename, e.hiredate, e.sal
from emp e, emp_sal s
where e.deptno=s.deptno and e.sal>s.avg_sal;-- 3. emp2, P_GRADE 테이블을 사용하여 각 직원의 나이를 기준으로 한 새 직급을 기존 직급과 함께 출력
-- 단, 직급이 정의되지 않는 직원 생략
select * from emp2;
select * from p_grade;
select e.name, e.position as 기존직급, p.position as 새직급
from emp2 e, P_GRADE p
where e.position is not null
and to_char(sysdate, 'YYYY')-to_char(e.birthday,'YYYY') between p.s_age and p.e_age;-- 4. student, exam_01 테이블을 사용하여 학년별 시험성적의 평균과 최고점수, 최저점수를 함께 출력
select * from student;
select * from exam_01;
select s.grade, avg(e.total), max(e.total), min(e.total)
from student s, exam_01 e
where s.studno = e.studno
group by s.grade;-- 5. 다음을 수행한 후 각 상품의 총 판매량과 총 판매금액을 상품이름과 함께 출력(panmae, product테이블 이용)
alter table panmae drop column P_TOTAL;
select * from panmae;
select * from product;
select a.p_code, sum(a.p_qty) as 총판매량, sum(r.p_price * a.p_qty) as 총판매금액
from panmae a, product r
where a.p_code = r.p_code
group by a.p_code;