오라클 중심의 SQL 배움터 13장, 16장 연습문제 풀이

13장

-- (1)
-- SQL명령문의 처리 속도를 향상 시키기 위해 필요하며 랜덤 액세스하여 저장된 데이터를 찾는다.

-- (2)
-- 고유 인덱스, 비고유 인덱스, 단일 인덱스, 결합 인덱스, DESCENDING INDEX, 함수 기반 인덱스

-- (3-1)
CREATE INDEX idx_regdate
ON member(regdate);

-- (3-2)
CREATE INDEX idx_userid
ON board(userid);

-- (3-3)
CREATE INDEX idx_interest_username
ON member(interest, username);

-- (3-4)
CREATE INDEX idx_userid_upper
ON member(UPPER(userid));

-- (4)
ALTER INDEX idx_name REBUILD;

-- (6)
DROP INDEX idx_name;

 

14장

-- (1)
/* 
RANK 함수는 1등이 2명인 경우 다음 순위는 3등으로 부여하지만
DENSE_RANK 함수는 다음 순위를 2등으로 부여한다.
*/

-- (2)
SELECT name, grade, birthdate,
       RANK() OVER(PARTITION BY grade
                   ORDER BY birthdate DESC) AS birthdate_rank
FROM student;

-- (3)
SELECT deptno, name, birthdate, birthdate_rank
FROM (SELECT deptno, name, birthdate,
      RANK() OVER(PARTITION BY deptno
                  ORDER BY birthdate DESC) birthdate_rank
      FROM student)
WHERE birthdate_rank <= 2;

-- (4)
SELECT deptno, name, weight, height, weight_rank
FROM (SELECT deptno, name, weight, height,
      RANK() OVER(PARTITION BY deptno
                  ORDER BY weight DESC) weight_rank
      FROM student)
WHERE weight_rank <= 2;

-- (5)
SELECT name, weight,
       DECODE(NTILE(3) OVER (ORDER BY weight DESC), 1, 'heavy_weight', 2, 'normal_weight', 'light_weight') weight_group,
       NTILE(3) OVER (ORDER BY weight DESC) weight_ntile
FROM student;

-- (6)
SELECT s.name, d.dname
FROM student s, (SELECT deptno, dname
                 FROM department
                 START WITH dname = '공과대학'
                 CONNECT BY PRIOR deptno=college) d
WHERE s.deptno=d.deptno
ORDER BY d.dname DESC;

-- (8)
SELECT name, sal,
       SUM(sal) OVER (ORDER BY sal
                      ROWS 1 PRECEDING) sum_sal
FROM professor;

-- (9)
SELECT deptno, dname, college, loc
FROM department
START WITH dname='공과대학'
CONNECT BY PRIOR deptno=college;

-- (10)
SELECT profno, name, hiredate, sal,
       LAG(sal, 1) OVER (ORDER BY sal DESC) - sal margin
FROM professor;