TOP-N 쿼리

select [rownum,] 컬럼1, 컬럼2
from (select 컬럼1, 컬럼1
      from 테이블
      order by 분석대상컬럼)
where rownum 연산자 n      

-- rownum: 각 행마다 순번 할당(1부터 시작)
-- select(subquery): 바깥 select과 컬럼이 같거나 많아야함
-- order by(subquery): 바깥 쿼리에 위치시 rownum 사용 불가. 순위 기준이 됨
		       asc(오름차순): 하위 n위까지
      		       desc(내림차순): 상위 n위까지
-- where: 결과가 n개만큼 출력됨.
	  ex) rownum <= 5   -- 5위까지 출력

• 출력을 원하는 특정순위까지의 데이터를 추출하는 것(오름차순, 내림차순 모두 가능)
• 인라인뷰, rownum을 사용함
• 중첩가능

-- 가장 최근에 입사한 순서로 5~10번째에 해당하는 사원의 사원번호와 입사일을 조회하시오
select employee_id, hire_date
from (select *
      from (select *
            from employees
            order by hire_date desc)
      where rownum <= 10		-- 최근 입사한 1~10번째 사원의 정보를 추출
      order by hire_date asc)		-- 오름차순정렬(늦->빠)
where rownum <= 6			-- 1~10위 중 10위부터 6위까지 정렬됨
order by hire_date desc;		-- 최종 내림차순 정렬(빠->늦)

분석함수

select 분석함수 over([partition by 컬럼][order by 컬럼])
from 테이블명

• 테이블 데이터를 분석하여 결과를 반환하는 함수
• 행끼리 비교연산을 가능하게 해줌

종류

순위함수

rank

• 같은 값은 같은 순위를 가짐 ex) 1위, 2위, 3위, 3위, 5위
• 특정 데이터 순위 조회

select rank('확인할데이터') within group (order by 컬럼명)
from 테이블
-- 컬럼명으로 정렬 시, '확인할데이터'의 순위를 반환
-- 확인할 데이터는 반드시 컬럼명에 속해있어야함
-- 확인할 데이터:'smith', 컬럼:salary -> 조회불가

• 전체순위 조회

select rank() over (order by 컬럼명) rank
form 테이블
-- rank컬럼이 생성되며 순위를 출력함

• 그룹별 순위 조회

select rank() over (partition by department_id
					order by salary)
from 테이블
-- 테이블 내의 salary 순위를 department_no별로 나누어 출력함

dense_rank

• 같은 값은 같은 순위를 가지나, 순위가 연속적임 ex) 1위, 2위, 3위, 3위, 4위
  

row_number

• 중복된 순위 없이 동일한 값에도 고유 순위를 부여함
  
• 값이 중복될 때 rowid가 작은 값에 먼저 순위를 부여함
  • rowid는 변경 될 수 있기때문에 반드시 유니크한 컬럼을 추가하여
    순위가 변동되지 않게 해야함

집계함수

• sum, min, max, avg, count
• 아래 링크의 다중행함수 참고
  https://velog.io/@yoonee1126/Day34-%EB%82%B4%EC%9E%A5%ED%95%A8%EC%88%98

계층형 쿼리

select [level],		-- 최상위로부터 몇번째 행인지 숫자로 나타냄
			-- 최상위 1부터 순서대로 2,3을 반환
	컬럼1, 컬럼2
from 테이블
where [조건식]
start with 조건식	-- 계층검색 시작지점(최상위계층)을 지정함
connect by prior 조건식	-- 연결조건 지정
			-- subquery 사용불가
            

• 상하 수직관계의 트리형식으로 이루어진 형태
• 하위->상위로 찾아가는 것이 더 빈번히 사용됨
• connect by prior
  • CONNECT BY PRIOR 자식컬럼 = 부모컬럼 -> 부모에서 자식으로(Top Down)
  • CONNECT BY PRIOR 부모컬럼 = 자식컬럼 -> 자식에서 부모로(Bottom Up)

-- 100번 직원의 하위에 위치하는 직원 조회
select leve, employee_id, first_name
from employees
start with employee_id = 100
connect by prior employee_id = manager_id
and level <= 2;

-- 205번 직원의 상위에 위치하고 있는 매니저 조회
select level, employee_id, first_name
from employees
start with employee_id = 205
connect by prior manager_id = employee_id;

사용자와 권한관리

• 권한: 특정sql을 실행할 수 있는 권리
  • 시스템 권한: db액세스 가능여부. 관리자만 권한의 부여 및 회수 가능
  • 객체 권한: db객체 내용을 조작하는 권한
• 롤: 사용자에게 부여할 수 있는 권한을 그룹화하고 이름을 부여한 것
  • connect role: 오라클에 접속 할 수 잇는 세션 생성 권한
  • resource: 테이블 생성,조회 권한
  • dba role: 모든 시스템 권한이 부여된 롤

신규 사용자 생성하기

• 관리자(system)계정으로 접속

alter session set"_oracle_script" = true;	-- 사용자 생성방식을 오라클 12 이전으로 설정
create user 유저이름 identified by "비밀번호";
grant 부여할 권한;

• 부여할 수 있는 권한 리스트
  

**참고: 모든 권한주기
grant connect, dba, resource to 계정명;

다른 사용자 db에 엑세스

• 권한을 부여할 계정

grant select
on 테이블명
to 부여받을 계정

• 권한을 부여받은 계정

select
form 부여받은계정.테이블명

synonym

• 동의어라는 뜻으로 db객체에 대한 별칭을 정의해서 사용
• 복잡한 이름을 짧아지므로 sql이 간단해짐
• 정의

create synonym 별칭
for 계정.테이블명

• 사용

select *
from 별칭