examples 예제

ORACLE SQL EXPERT EXAMPLES

USE WINSCP TO EXECUTE ATTACHED FILE ON LINUX

test_data.sql 첨부했음 (includes tables used in examples)

test_data.sql

1. student table, jumin 칼럼사용 8월달 이름 생년월일 출력
ANSWER)
select name, substr(jumin,1,8) from student where substr(jumin,4,1) = '8';

2. student table, 전공이 101번 이름 전화번호 지역번호 출력
ANSWER)
select name, tel, substr(tel, 1, instr(tel,')')-1)
from student
where deptno1=101;

3. student table, 101전공 이름 가운데이름 '#'표시
ANSWER)
select replace(name, substr(name, 2,1), '#')
from student
where deptno1=101;

4. student, 102전공 이름, 전화번호, 국번은'####' (국번은 4자리)
ANSWER)
select name, tel, replace(tel,substr(tel,instr(tel,')')+1,4),'####') 국번
from student
where deptno1=102

5. reg_test, 행의 첫글자 영어대문자나 숫자로 시작하는 모든행출력
ANSWER)
select * from reg_test
where regexp_like(text, '^[a-z]|^[0-9]')

6. reg_test, 행의 첫글자 숫자로 시작하지 않는 행 출력
ANSWER)
select * from reg_test
where regexp_like(text, '^[^0-9]')

7. 정규식함수 이용 '20120324' 데이터 '2012:03:24' 로 출력
ANSWER)
select regexp_replace('20120324','([[:digit:]]{4}) ([[:digit:]]{2})', '\1:\2:') from dual
select regexp_replace('20120324','([0-9]{4}) ([0-9]{2})', '\1:\2:') from dual

8. 정규식함수 이용 professor, 이름 메일주소 출력 메일주소는 @뒤에 주소만 출력
ANSWER)
select name, regexp_substr(email, '([a-z]+)(\.)([a-z]+)')
from professor
where email is not null

9. professor table, 1990년 이전 교수명 입사일 현재연봉 현재연봉에서 10%인상수 연봉 출력. 천단위 구분

ANSWER)
select name, hiredate,
to_char(pay*12, '999,999') 연봉, to_char((pay+pay*0.1)*12, '999,999') 인상연봉
from professor
where to_char(hiredate,'yyyy') < '1990'

10. student table, 101전공 이름 전화번호 지역명, 02=서울 031=경기 051=부산 052=울산 055=경남
ANSWER)
select name, tel,
decode(substr(tel,1,instr(tel,')')-1), 02, '서울',
031,'경기',
051,'부산',
052,'울산',
055,'경남')
from student
where deptno1=101

11. cal table, decode사용 달력 만들기
ANSWER)
select min(decode(day,'일',num_day)) 일,
min(decode(day,'월',num_day)) 월,
min(decode(day,'화',num_day)) 화,
min(decode(day,'수',num_day)) 수,
min(decode(day,'목',num_day)) 목,
min(decode(day,'금',num_day)) 금,
min(decode(day,'토',num_day)) 토
from cal
group by week
order by week

12. emp table, 부서별 직급병 인원수
ANSWER)
select deptno, count(decode(job, 'CLERK', 1)) CLERK,
count(decode(job, 'MANAGER', 1)) MANAGER,
count(decode(job, 'PRESIDENT', 1)) PRESIDENT,
count(decode(job, 'ANALYST', 1)) ANALYST,
count(decode(job, 'SALESMAN', 1)) SALAESMAN
from emp
group by deptno
order by deptno

13. student table, birtday 칼럼 사용 월별로 태어난 인원수 출력
ANSWER)
select count(*), count(decode(to_char(birthday,'mm'),1,1)) "1월",
count(decode(to_char(birthday,'mm'),2,1)) "2월",
count(decode(to_char(birthday,'mm'),3,1)) "3월",
count(decode(to_char(birthday,'mm'),4,1)) "4월",
count(decode(to_char(birthday,'mm'),5,1)) "5월",
count(decode(to_char(birthday,'mm'),6,1)) "6월",
count(decode(to_char(birthday,'mm'),7,1)) "7월",
count(decode(to_char(birthday,'mm'),8,1)) "8월",
count(decode(to_char(birthday,'mm'),9,1)) "9월",
count(decode(to_char(birthday,'mm'),10,1)) "10월",
count(decode(to_char(birthday,'mm'),11,1)) "11월",
count(decode(to_char(birthday,'mm'),12,1)) "12월"
from student

14. student table, tel칼럼 참고 지역별 인원수
ANSWER)
select count(*),
count(decode(substr(tel,1,instr(tel,')')-1), 02, 1)) 서울,
count(decode(substr(tel,1,instr(tel,')')-1), 031, 1)) 경기,
count(decode(substr(tel,1,instr(tel,')')-1), 051, 1)) 부산,
count(decode(substr(tel,1,instr(tel,')')-1), 052, 1)) 울산,
count(decode(substr(tel,1,instr(tel,')')-1), 053, 1)) 대구,
count(decode(substr(tel,1,instr(tel,')')-1), 055, 1)) 경남
from student

15. emp table, 부서별 직급별 급여와 합계 출력
PROBABLY AN ANSWER)
select deptno, sum(decode(job,'CLERK',sal,0)) CLERK,
sum(decode(job,'MANAGER',sal,0)) MANAGER,
sum(decode(job,'PRESIDENT',sal,0)) PRESIDENT,
sum(decode(job,'ANALYST',sal,0)) ANALYST,
sum(decode(job,'SALESMAN',sal,0)) SALESMAN,
sum(sal) 합계
from emp
group by rollup(deptno)
order by deptno

16. emp table, 10번 부서 사번 이름 급여 부서내 급여순위
ANSWER)
select deptno,empno, ename, sal,
rank() over (order by sal desc) RANK
from emp
where deptno=10

17. panmae table, 1000번 대리점 판매일자 제품코드 판매매량 누적판매금액
ANSWER)
select p_date, p_code, p_qty, p_total,
sum(p_total) over (order by p_total) 누적
from panmae
where p_store=1000

18. student table & professor table, 학생이름 지도교수번호 지도교수이름
(oracle join, ansi join)
ANSWER)
select s.name, s.profno, p.name
from student s, professor p
where s.profno=p.profno (ORACLE JOIN)

select s.name, s.profno, p.name
from student s join professor p
on s.profno=p.profno (ANSI JOIN)

19. gogak table & gift table, 고객의 마일리지 포인트 별로 받을수 있는 상품명, 고객이름
(oracle join, ansi join)
ANSWER)
select go.gname, gi.gname
from gogak go, gift gi
where go.point between gi.g_start and gi.g_end

20. student table, 101학번 평균 몸무게 많은 학생이름 몸무게
ANSWER)
select name, weight
from student
where weight >
(select avg(weight) from student where deptno1=101)

21. emp2 table, 과장 직급 최소 연봉자보다 높은 이름 직급 연봉
(연봉 천단위구분 원 표시)
ANSWER)
select name, position, to_char(pay, '999,999,999') || ' 원' 연봉
from emp2
where pay >any (select pay from emp2 where position='과장')

22. emp2 table, 부서별 평균연봉최소보다 적은 부서명 사원명 연봉 
ANSWER)
select d.dname, e.name, e.pay
from emp2 e, dept2 d
where d.dcode=e.deptno
and pay < all (select avg(pay) from emp2 group by deptno)

23. EMP3 테이블 생성
no 컬럼 number 4자리 primary key, emp3_no_pk
name 컬럼 varchar2 10byte not null, emp3_name_nn
deptno 컬럼 varchar2 6byte dept2-dcode 참조
ANSWER)
create table emp3
(no number(4) constraint emp3_no_pk primary key,
name varchar2(10) constraint emp3_name_nn not null,
deptno varchar2(6) references dept2(dcode))

24. emp4 table(child table) no column(foreign key), emp2(mother table) empno(reference key)
ANSWER)
alter table emp4
add constraint emp4_forkey foreign key(no)
references emp2(empno)

25. student table & department table, 학과별 가장큰키 이름 학과이름 inline view
ANSWER)
select d.dname, s.max_height, st.name, st.height
from (select deptno1,max(height) max_height from student group by deptno1)s,
department d, student st
where st.deptno1=d.deptno
and st.height=s.max_height
and st.deptno1=s.deptno1
order by 1

26. student table, 학생의 키가 동일 학년의 평균 키보다 큰 학생들의 학년 이름 키 해당학년 평균키
(inline view, 학년 오름차순)
ANSWER)
select st.grade, st.name, st.height, s.avg_height
from (select grade, avg(height) avg_height from student group by grade)s,
student st
where st.height > s.avg_height
and st.grade=s.grade
order by 1

27. sequence
name: seq_jumun_no
start number : 1000
end number : 1010
increment : 1
cycle, cache=2
ANSWER)
create sequence seq_jumun_no
increment by 1
start with 1000
maxvalue 1010
minvalue 990
cycle
cache 2

28. Scott 사용자의 department table 동의어 d2 생성 모든 사용자들이 공유 할수 있게
ANSWER)
sys> grant create public synonym to scott;
scott> create public synonym d2 for department;