9장의 제공되는 데이터를 기준으로 문제를 풀이한다.
문제
1. emp 테이블에 remark 3000바이트 크기로 필드를 삽입하시오.
2. 그룹 ABBA의 DANCING QUEEN곡의 가사를 삽입하시오.
3. 입력된 remark 속성에 저장된 영문자를 모두 소문자/대문자/첫자대문자로 조회하시오.
4. remark 속성의 글자수와 바이트 수를 조회하시오.
5. 커미션이 500이상인 사원의 이름과 급여 및 커미션을 출력하시오.
6. substr 함수를 사용하여 사원들의 입사한 년도와 입사한 달만 출력하시오.
7. substr 함수를 사용하여 4월에 입사한 사원을 출력하시오.
8. MOD 함수를 사용하여 사원번호가 짝수인 사람만 출력하시오.
9. 입사일의 년도는 2자리(YY), 월은 숫자 (MON)로 표시하고 일은 약어(DD)로 지정하여 출력하시오.
10. 올해 며칠이 지났는지 출력하시오. 현재 날짜에서 올해 1월 1일을 뺀 결과를 출력하고 TO_DATE 함수를 사용하여 데이터 형을 일치 시키시오.
11. 사원들의 사번, 이름, 상관사번을 출력하되 상관이 없는 사원에 대해서는 NULL 값 대신 0으로 출력하시오.
12. DECODE 함수로 직급에 따라 급여를 인상하도록 하시오. 직급이 '분석가'인 사원은 200, '영업사원'인 사원은 180, ' 관리자'인 사원은 150, '점원'인 사원은 130, 나머지는 0을 인상하시오.
이름, 직무, 급여, 인상급여 순으로 나타내시오.
13. 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하시오. 평균에 대해서는 정수로 반올림하시오.
14. 각 담당 업무 유형별로 급여 최고액, 최저액, 총액 및 평균액을 출력하시오.
평균에 대해서는 정수로 반올림 하시오.
15. count(*) 함수를 이용하여 담당업무가 동일한 사원 수를 출력하시오.
16. 급여 최고액, 급여 최저액의 차액을 출력하시오.
17. 업무를 표시한 다음 해당 업무에 대해 부서 번호별 급여 및 부서 10, 20, 30의 급여 총액을 각각 출력하시오.
별칭은 각 부서번호, 부서명, 총액으로 지정하시오. ( hint. IN, GROUP BY )
18. EQUL 조인을 사용하여 김민욱 사원의 부서번호와 부서 이름을 출력하시오.
19. INNER JOIN과 ON 연산자를 사용하여 사원 이름과 함께 그 사원이 소속된 부서이름과 지역 명을 출력하시오.
20. INNER JOIN과 USING 연산자를 사용하여 10번 부서에 속하는 모든 담당 업무의 고유 목록(한 번씩만 표시)을 부서의 지역명을 포함하여 출력 하시오.
21. NATURAL JOIN을 사용하여 커미션을 받는 모든 사원의 이름, 부서이름, 커미션, 지역 명을 출력하시오.
22. EQUI 조인과 WildCARD를 사용하여 이름에 ‘민’자가 포함된 모든 사원의 이름과 부서명을 출력하시오.
23. NATUAL JOIN을 이용하여 대전에 근무하는 모든 사원의 이름, 업무, 부서번호 및 부서명을 출력하시오.
24. SELF JOIN을 사용하여 사원의 이름 및 사원번호를 관리자 번호, 관리자명을 함께 출력하시오.
25. SELF JOIN을 사용하여 지정한 사원의 이름, 부서번호, 지정한 사원과 동일한 부서에서 근무하는 사원을 출력하시오. (고영우)
26. 사원 번호가 7499인 사원과 담당 업무가 같은 사원을 표시(사원 이름과 담당업무)하시오.
27. 상품테이블에서 상품코드, 상품명, 분류명, 거래처 명을 조회하시오.
28. 사원번호가 7499인 사원보다 급여가 많은 사원을 표시하시오. 사원이름과 담당 업무
29. 최소급여를 받는 사원의 이름, 담당업무 및 급여를 표시하시오.
30. 평균 급여가 가장 적은 직급의 직급 이름과 직급의 평균을 구하시오.
1번 문제
-- 1. emp 테이블에 remark 3000바이트 크기로 필드를 삽입하시오.
alter table emp add (remark varchar2 (3000));
select * from emp;
2번 문제
-- 2. 그룹 ABBA의 DANCING QUEEN곡의 가사를 삽입하시오.
update emp set remark = '아바의 댄싱퀸
Ooh
You can dance
You can jive
Having the time of your life
Ooh, see that girl
Watch that scene
Digging the dancing queen
Friday night and the lights are low
Looking out for a place to go
Where they play the right music
Getting in the swing
You come to look for a king
Anybody could be that guy
Night is young and the music''s high
With a bit of rock music
Everything is fine
You''re in the mood for a dance
And when you get the chance
You are the dancing queen
Young and sweet
Only seventeen
Dancing queen
Feel the beat from the tambourine, oh yeah
You can dance
You can jive
Having the time of your life
Ooh, see that girl
Watch that scene
Digging the dancing queen
You''re a teaser, you turn ''em on
Leave ''em burning and then you''re gone
Looking out for another
Anyone will do
You''re in the mood for a dance
And when you get the chance
You are the dancing queen
Young and sweet
Only seventeen
Dancing queen
Feel the beat from the tambourine, oh yeah
You can dance
You can jive
Having the time of your life
Ooh, see that girl
Watch that scene
Digging the dancing queen
Digging the dancing queen';
3번 문제
-- 3. 입력된 remark 속성에 저장된 영문자를 모두 소문자/대문자/첫자대문자로 조회하시오.
select lower(remark) 소문자, upper(remark) 대문자, initcap(remark) 첫자대문자 from emp;
4번 문제
-- 4. remark 속성의 글자수와 바이트 수를 조회하시오.
select length(remark) 글자수, lengthb(remark) 바이트수
from emp;
5번 문제
-- 5. 커미션이 500이상인 사원의 이름과 급여 및 커미션을 출력하시오.
select ename 사원명, sal 급여, comm 커미션 from emp
where comm >= 500;
6번 문제
-- 6. substr 함수를 사용하여 사원들의 입사한 년도와 입사한 달만 출력하시오.
select hiredate 입사일자, substr(hiredate, 1, 2) 입사년도, substr(hiredate, 4, 2) 입사달 from emp;
7번 문제
-- 7. substr 함수를 사용하여 4월에 입사한 사원을 출력하시오.
select * from emp
where substr(hiredate, 4, 2) = '04';
8번 문제
-- 8. MOD 함수를 사용하여 사원번호가 짝수인 사람만 출력하시오.
select * from emp
where mod(empno, 2) = 0;
9번 문제
-- 9. 입사일의 년도는 2자리(YY), 월은 숫자 (MON)로 표시하고 일은 약어(DD)로 지정하여 출력하시오.
select substr(hiredate, 1, 2) YY, substr(hiredate, 4, 2) MON, substr(hiredate, 7, 2) DD, hiredate 입사일 from emp;
10번 문제
-- 10. 올해 며칠이 지났는지 출력하시오. 현재 날짜에서 올해 1월 1일을 뺀 결과를 출력하고 TO_DATE 함수를 사용하여 데이터 형을 일치 시키시오.
select to_date(sysdate) - to_date('23/01/01') from dual;
11번 문제
nvl() 함수를 사용한다.
-- c가 Null이 아니면 c값으로, Null이면 r 반환
nvl(c, r)
-- 11. 사원들의 사번, 이름, 상관사번을 출력하되 상관이 없는 사원에 대해서는 NULL 값 대신 0으로 출력하시오.
select empno 사번, ename 이름, nvl(mgr, 0) 상관사번 from EMP;
12번 문제
decode() 함수를 사용한다.
-- IF문과 같은 기능을 함
-- decode(컬럼, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3.........., default)
select decode(9, 10, 'A', 9, 'B', 8, 'C', 'D')
from dual;
-- 12. DECODE 함수로 직급에 따라 급여를 인상하도록 하시오. 직급이 '분석가'인 사원은 200, '영업사원'인 사원은 180, ' 관리자'인 사원은 150, '점원'인 사원은 130, 나머지는 0을 인상하시오.
-- 이름, 직무, 급여, 인상급여 순으로 나타내시오.
select ename 이름, job 직무, sal 급여, decode(job, '분석가', sal+200, '영업사원', sal+180, '관리자', sal+150, '점원', sal+130, sal) 인상급여 from emp;
13번 문제
round() 함수를 사용한다.
-- round 함수 : 반올림
-- round 예시
select 134.567 값,
round(134.567, 0) "결과0",
round(134.567) "결과-무",
round(134.567, 1) "결과1",
round(134.567, 2) "결과2",
round(134.567, 3) "결과3",
round(134.567, -1) "결과-1",
round(134.567, -2) "결과-2",
round(134.567, -3) "결과-3"
from dual;
-- round함수와 trunc함수의 차이
-- round함수 : 반올림
-- trunc함수 : 버림
select round(345.123, -1) 결과1,
trunc(345.123, -1) 결과2 from dual;
-- 13. 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하시오. 평균에 대해서는 정수로 반올림하시오.
select * from emp;
select max(sal) 급여최고액, min(sal) 급여최저액, sum(sal) 총액, round(avg(sal)) 평균급여 from emp;
14번 문제
-- 14. 각 담당 업무 유형별로 급여 최고액, 최저액, 총액 및 평균액을 출력하시오.
-- 평균에 대해서는 정수로 반올림 하시오.
-- ~별로 일때는 group by를 사용해야함.
select job 담당업무, max(sal) "급여 최고액", min(sal) "급여 최저액", max(sal) 총액, round(avg(sal)) 평균액
from emp
group by job
order by avg(sal) desc;
15번 문제
-- 15. count(*) 함수를 이용하여 담당업무가 동일한 사원 수를 출력하시오.
select job 담당업무, count(job) 사원수 from emp
group by job
order by 2 desc;
16번 문제
-- 16. 급여 최고액, 급여 최저액의 차액을 출력하시오.
select max(sal) "급여 최고액", min(sal) "급여 최저액", (max(sal) - min(sal)) "급여 최고액-최저액" from emp;
17번 문제
-- 17. 업무를 표시한 다음 해당 업무에 대해 부서 번호별 급여 및 부서 10, 20, 30의 급여 총액을 각각 출력하시오.
-- 별칭은 각 부서번호, 부서명, 총액으로 지정하시오. ( hint. IN, GROUP BY )
select * from emp;
select * from dept;
select e.deptno 부서번호, d.dname 부서명, sum(e.sal) 총액
from emp e, dept d
where e.deptno = d.deptno
and e.deptno in(10,20,30)
group by e.deptno, d.dname
order by sum(e.sal) desc;
18번 문제
-- 18. EQUL 조인을 사용하여 김민욱 사원의 부서번호와 부서 이름을 출력하시오.
-- EQUL 조인 : 똑같은 걸 물어보는 조인
-- ex) where e.deptno = d.deptno
select e.deptno 부서번호, d.dname "부서 이름", e.ename "사원 이름" from emp e, dept d
where e.deptno = d.deptno
and e.ename = '김민욱';
19번 문제
-- inner join과 on 연산자 사용법
sele * from 테이블1
inner join 테이블2 on (테이블1.컬럼1 = 테이블2.컬럼1);
-- equl join 사용법
select * from 테이블1, 테이블2
where 테이블1.컬럼1 = 테이블2.컬럼1;
-- 19. INNER JOIN과 ON 연산자를 사용하여 사원 이름과 함께 그 사원이 소속된 부서이름과 지역 명을 출력하시오.
-- inner join : 내부 조인
select e.ename 사원이름, d.dname 소속부서, d.loc 지역명
from emp e
inner join dept d on (e.deptno = d.deptno);
20번 문제
-- inner join과 on 연산자 사용법
sele * from 테이블1
inner join 테이블2 on (테이블1.컬럼1 = 테이블2.컬럼1);
-- inner join과 using 연산자 사용법
select * from 테이블1
inner join 테이블2 using (컬럼1);
-- 20. INNER JOIN과 USING 연산자를 사용하여 10번 부서에 속하는 모든 담당 업무의 고유 목록(한 번씩만 표시)을 부서의 지역명을 포함하여 출력 하시오.
select * from dept;
select e.empno 사원번호, d.loc 지역명, e.job 담당업무
from emp e
inner join dept d
using(deptno)
where deptno = 10;
21번 문제
natural join은 등가 조인하는 방법 중 하나로, 동일한 타입과 이름을 가진 컬럼을 조인 조건으로 이용하는 조인을 간단히 표현하는 방법을 이야기한다.
별칭을 주면 오류가 발생한다.
-- inner join과 on 연산자 사용법
sele * from 테이블1
natural join 테이블2;
-- 21. NATURAL JOIN을 사용하여 커미션을 받는 모든 사원의 이름, 부서이름, 커미션, 지역 명을 출력하시오.
select emp.ename 사원이름, dept.dname 부서이름, emp.comm 커미션, dept.loc 지역명
from emp
natural join dept
where comm is not null;
22번 문제
-- 22. EQUI 조인과 WildCARD를 사용하여 이름에 ‘민’자가 포함된 모든 사원의 이름과 부서명을 출력하시오.
select e.ename 사원이름, d.dname 부서명 from emp e, dept d
where e.deptno = d.deptno
and e.ename like '%민%';
23번 문제
-- 23. NATUAL JOIN을 이용하여 대전에 근무하는 모든 사원의 이름, 업무, 부서번호 및 부서명을 출력하시오.
-- 공통되는 컬럼의 경우(deptno) 별명을 통해 참조하면 안 된다.
select emp.ename 사원이름, emp.job 업무, deptno 부서번호, dept.dname 부서명
from emp
natural join dept
where dept.loc = '대전';
24번 문제
-- 24. SELF JOIN을 사용하여 사원의 이름 및 사원번호를 관리자 번호, 관리자명을 함께 출력하시오.
select * from emp;
select e1.ename 사원이름, e1.empno 사원번호, e1.mgr "관리자 번호", e2.ename 관리자명
from emp e1, emp e2
where e1.mgr = e2.empno
order by e1.ename;
25번 문제
-- 25. SELF JOIN을 사용하여 지정한 사원의 이름, 부서번호, 지정한 사원과 동일한 부서에서 근무하는 사원을 출력하시오. (고영우)
select * from emp;
select a.ename 사원이름, b.empno 부서번호
from emp a, emp b
where a.deptno = b.deptno
and a.ename = '고영우';
26번 문제
-- 26. 사원 번호가 7369인 사원과 담당 업무가 같은 사원을 표시(사원 이름과 담당업무)하시오.
select * from emp;
select b.empno 사번, b.ename 사원이름, b.job 담당업무
from emp a, emp b
where a.empno = 7369
and a.job = b.job
and b.empno <> 7369;
-- and b.empno <> 7369; 의 경우 자기 자신은 제외
27번 문제
-- 27. 상품테이블에서 상품코드, 상품명, 분류명, 거래처 명을 조회하시오.
select * from prod;
select * from buyer;
select * from lprod;
select prod.prod_id 상품코드, prod.prod_name 상품명, lprod.lprod_nm 분류명, buyer.buyer_name 거래처명
from prod, buyer, lprod
where prod.prod_lgu = lprod.lprod_gu
and prod.prod_buyer = buyer.buyer_id;
28번 문제
-- 28. 사원번호가 7499인 사원보다 급여가 많은 사원을 표시하시오. 사원이름과 담당 업무
select * from emp;
select e1.ename 사원이름, e1.job 담당업무, e1.sal 급여
from emp e, emp e1
where e.empno = 7499
and e.sal < e1.sal;
29번 문제
-- 29. 최소급여를 받는 사원의 이름, 담당업무 및 급여를 표시하시오.
select * from emp;
select ename 사원이름, job 담당업무, sal 급여
from emp
where sal = (select min(sal) from emp);
30번 문제
-- 30. 평균 급여가 가장 적은 직급의 직급 이름과 직급의 평균을 구하시오.
select * from emp;
select job 직급이름, asd 직급평균
from (select job, round(avg(sal), 0) asd from emp
group by job order by round(avg(sal), 0) asc)
where rownum = 1;
select job 직급이름, round(avg(sal), 0) 평균
from emp
group by job
order by round(avg(sal), 0) asc;
'데이터베이스' 카테고리의 다른 글
| [Database] 12장 함수 모음1 (0) | 2023.12.22 |
|---|---|
| [Database] 11장 날짜 타입 (0) | 2023.12.19 |
| [Database] 9장 SQL 연습문제2 (0) | 2023.12.19 |
| [Database] 8장 SQL 연습문제1 (0) | 2023.12.18 |
| [Database] 7장 SQL (0) | 2023.12.15 |