Tycoon/오라클 SQL과 PL／SQL (개정4판) (完)

[오라클 SQL과 PL/SQL] 3장 연습문제 (그룹 함수 1 ~ 15)

nyamnmm 2025. 1. 5. 02:05
『 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL (개정 4판) 』
저자: 서진수, 김균도
사용 프로그램: sqlplus
※ 본 게시글에 작성된 문제는 책 본문과 일치하지 않는 부분이 존재하며, 풀이 또한 정확하지 않습니다.

 

 

1.
emp 테이블을 사용하여 사원 중에서 급여(sal)와 보너스(comm)를 합친 금액이 가장 많은 경우와 가장 적은 경우 , 평균 금액을 구하세요. 단 보너스가 없을 경우는 보너스를 0 으로 계산하고 출력 금액은 모두 소수점 첫째 자리까지만 나오게 하세요.

 

문제풀이
SELECT	MAX(sal+NVL(comm, 0)) "MAX",
	MIN(sal+NVL(comm, 0)) "MIN",
    	ROUND(AVG(sal+NVL(comm, 0)), 1) "AVG"
FROM emp;

 

실행결과

 

 

2. Student 테이블의 birthday 컬럼을 참조해서 아래와 같이 월별로 생일자수를 출력하세요.

 

문제풀이
SELECT	COUNT(*) || 'EA' "TOTAL",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '01', 1)) || 'EA' "JAN",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '02', 1)) || 'EA' "FEB",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '03', 1)) || 'EA' "MAR",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '04', 1)) || 'EA' "APR",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '05', 1)) || 'EA' "MAY",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '06', 1)) || 'EA' "JUN",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '07', 1)) || 'EA' "JUL",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '08', 1)) || 'EA' "AUG",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '09', 1)) || 'EA' "SEP",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '10', 1)) || 'EA' "OCT",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '11', 1)) || 'EA' "NOV",
	COUNT(DECODE(TO_CHAR(birthday, 'mm'), '12', 1)) || 'EA' "DEC"
FROM Student;

 

실행결과

 

 

3. Student 테이블의 tel 컬럼을 참고하여 아래와 같이 지역별 인원수를 출력하세요.
단, 02-SEOUL, 031-GYEONGGI, 051-BUSAN, 052-ULSAN, 053-DAEGU, 055-GYEONGNAM으로 출력하세요.

 

문제풀이
SELECT	COUNT(tel) "TOTAL",
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '02', 1)) "SEOUL",
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '031', 1)) "GUEONGGI",
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '051', 1)) "BUSAN",
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '052', 1)) "ULSAN",
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '053', 1)) "DAEGU",
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '055', 1)) "GYEONGNAM"
FROM Student;

 

실행결과

 

 

4. 먼저 emp 테이블에 아래의 두 건의 데이터를 입력 하신 후 작업하세요.
Emp 테이블을 사용하여 아래의 화면과 같이 부서별로 직급별로 급여 합계 결과를 출력하세요.
insert into emp (empno, deptno, ename, sal) values (1000, 10, 'Tiger', 3600);
insert into emp (empno, deptno, ename, sal) values (2000, 30, 'Cat', 3000);

 

문제풀이
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(NVL2(job, sal, 0)) "TOTAL"
FROM emp GROUP BY ROLLUP(deptno);

 

실행결과

 

 

5. emp 테이블을 사용하여 직원들의 급여와 전체 급여의 누적 급여금액이 아래와 같도록 출력하세요.
단 급여를 오름차순으로 정렬해서 출력하세요.

 

문제풀이
SELECT deptno, ename, sal, SUM(sal) OVER(ORDER BY sal) "TOTAL" FROM emp;

 

실행결과

 

 

6. fruit 테이블을 아래와 같은 형태로 출력하세요.

문제풀이
SELECT	MAX(SUM(DECODE(name, 'apple', price))) "APPLE",
	MAX(SUM(DECODE(name, 'grape', price))) "GRAPE",
	MAX(SUM(DECODE(name, 'orange', price))) "ORANGE"
FROM fruit GROUP BY name;

 

실행결과

 

 

7. Student 테이블의 Tel 컬럼을 사용하여 아래와 같이 지역별 인원수와 전체대비 차지하는 비율을 출력하세요.
(단, 02-SEOUL, 031-GYEONGGI, 051-BUSAN, 052-ULSAN, 053-DAEGU, 055-GYEONGNAM으로 출력하세요.)
문제풀이
SELECT
	COUNT(tel) || 'EA (' || (COUNT(tel)/COUNT(tel)*100) || '%)' "TOTAL", 
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '02', 1)) || 'EA (' || (COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '02', 1))/COUNT(tel)*100) || '%)' "SEOUL", 
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '031', 1)) || 'EA (' || (COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '031', 1))/COUNT(tel)*100) || '%)' "GYEONGGI", 
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '051', 1)) || 'EA (' || (COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '051', 1))/COUNT(tel)*100) || '%)' "BUSAN", 
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '052', 1)) || 'EA (' || (COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '052', 1))/COUNT(tel)*100) || '%)' "ULSAN", 
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '053', 1)) || 'EA (' || (COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '053', 1))/COUNT(tel)*100) || '%)' "DAEGU", 
	COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '055', 1)) || 'EA (' || (COUNT(DECODE(SUBSTR(tel, 1, INSTR(tel, ')')-1), '055', 1))/COUNT(tel)*100) || '%)' "GYEONGNAM"
FROM Student;

 

실행결과

 

 

8. emp 테이블을 사용하여 아래와 같이 부서별로 급여 누적 합계가 나오도록 출력하세요.
(단, 부서번호로 오름차순 출력하세요.)

 

문제풀이
SELECT	deptno,
	ename,
	sal,
	SUM(sal) OVER(PARTITION BY deptno ORDER BY sal) "TOTAL"
FROM emp;

 

실행결과

 

 

9. emp 테이블을 사용하여 아래와 같이 각 사원의 급여액이 전체 직원 급여총액에서 몇 %의 비율을 차지하는지 출력하세요. 단 급여 비중이 높은 사람이 먼저 출력되도록 하세요.

 

문제풀이
SELECT	deptno,
	ename,
	sal,
	SUM(sal) OVER(ORDER BY sal DESC) "TOTAL_SAL",
	ROUND((RATIO_TO_REPORT(SUM(sal)) OVER())*100, 2) "%"
FROM emp
GROUP BY deptno, ename, sal;

 

실행결과

 

 

10. emp 테이블을 조회하여 아래와 같이 각 직원들의 급여가 해당 부서 합계금액에서 몇 %의 비중을 차지하는지를 출력하세요. 단 부서번호를 기준으로 오름차순으로 출력하세요.

 

문제풀이
SELECT	deptno,
	ename,
	sal,
	SUM(sal) OVER(PARTITION BY deptno) "SUM_DEPT",
	ROUND((RATIO_TO_REPORT(SUM(sal)) OVER(PARTITION BY deptno))*100, 2) "%"
FROM emp
GROUP BY deptno, ename, sal;

 

실행결과

 

 

11.
loan 테이블을 사용하여 1000번 지점의 대출 내역을 출력하되 대출일자, 대출종목코드, 대출건수, 대출총액, 누적대출금액을 아래와 같이 출력하세요.

 

문제풀이
SELECT	l_date "대출일자",
	l_code "대출종목코드",
	l_qty "대출건수",
	l_total "대출총액",
	SUM(l_total) OVER(ORDER BY l_date) "누적대출금액"
FROM loan
WHERE l_store = 1000;

 

실행결과

 

 

12.
loan 테이블을 사용하여 전체 지점의 대출종목코드, 대출지점, 대출일자, 대출건수, 대출액을 대출코드와 대출지점별로 누적 합계를 구하세요.

 

문제풀이
SELECT	l_code "대출종목코드",
	l_store "대출지점",
	l_date "대출일자",
	l_qty "대출건수",
	l_total "대출액",
	SUM(l_total) OVER(PARTITION BY l_code, l_store ORDER BY l_date) "누적대출금액"
FROM loan;

 

실행결과

 

 

 

13. loan 테이블을 조회하여 1000번 지점의 대출 내역을 대출 코드별로 합쳐서 대출일자, 대출구분코드, 대출건수, 대출총액, 코드별 누적대출금액을 아래와 같이 출력하세요.

 

문제풀이
SELECT	l_date "대출일자",
	l_code "대출구분코드",
	l_qty "대출건수",
	l_total "대출총액",
	SUM(l_total) OVER(PARTITION BY l_code ORDER BY l_total) "누적대출금액"
FROM loan
WHERE l_store = 1000;

 

실행결과

 

 

14.
Professor 테이블에서 각 교수들의 급여를 구하고 각 교수의 급여액이 전체 교수의 급여 합계에서 차지하는 비율을 출력하세요.

 

문제풀이
SELECT	deptno,
	name,
	pay,
	SUM(pay) OVER() "TOTAL_PAY",
	ROUND((RATIO_TO_REPORT(SUM(pay)) OVER()) * 100, 2) "PARTIO %"
FROM professor
GROUP BY deptno, name, pay
ORDER BY pay DESC;

※ 괄호 사용이 많아지므로 마지막에 괄호를 정확히 닫았는지 반드시 확인할 것

 

실행결과

 

 

15. Professor 테이블을 조회하여 학과번호 , 교수명 , 급여 , 학과별 급여 합계를 구하고 각 교수의 급여가 해당 학과별 급여 합계에서 차지하는 비율을 출력하세요

 

문제풀이
SELECT	deptno,
	name,
	pay,
	SUM(pay) OVER(PARTITION BY deptno) "TOTAL_PAY",
	ROUND((RATIO_TO_REPORT(SUM(pay)) OVER(PARTITION BY deptno)) * 100, 2) "PARTIO %"
FROM professor
GROUP BY deptno, name, pay
ORDER BY deptno;

 

실행결과

 

'Tycoon > 오라클 SQL과 PL／SQL (개정4판) (完)' 카테고리의 다른 글

[오라클 SQL과 PL/SQL] 6장 연습문제 (DML 1 ~ 4)  (0) 2025.01.05
[오라클 SQL과 PL/SQL] 5장 연습문제 (DDL 명령과 딕셔너리 1 ~ 9)  (0) 2025.01.05
[오라클 SQL과 PL/SQL] 4장 연습문제 (조인(Join) 1 ~ 6)  (0) 2025.01.05
[오라클 SQL과 PL/SQL] 2장 퀴즈문제 (단일행 함수)  (0) 2025.01.05
[오라클 SQL과 PL/SQL] 1장 연습문제 (연결 연산자 1 ~ 3)  (1) 2025.01.03

'Tycoon/오라클 SQL과 PL／SQL (개정4판) (完)'의 다른글

  • 현재글[오라클 SQL과 PL/SQL] 3장 연습문제 (그룹 함수 1 ~ 15)

관련글

티스토리툴바