KOSTA 교육 24일차(JOIN, 다중 컬럼, 다중 로우, FROM절 서브쿼리, INDEX, VIEW, SEQUENCE, PL/SQL)

18.11.29(목)

 

배운 내용

 

1. 다중 컬럼, 다중 로우
2. FROM절 서브쿼리
3. INDEX
4. VIEW
5. SEQUENCE
6. PL/SQL

---

1. 다중 컬럼, 다중 로우 서브쿼리

다중 컬럼, 다중 로우 서브쿼리는?
하나 이상의 행을 반환하는 서브쿼리를 말한다.

• 단일 행 연산자를 사용할 수 없고 다중 행 연산만 사용 가능하다.
• IN : 검색된 값 중에 하나만 일치하면 참
• ANY : 검색된 값 중에 조건에 맞는 것이 하나 있으면 참
• ALL : 검색된 값과 조건이 모두 일치해야 참

• 컬럼 > ALL(서브쿼리) -> 컬럼 > MAX : 가장 큰값 보다 크다
  컬럼 < ALL(서브쿼리) -> 컬럼 > MIN : 가장 작은값 보다 작다
• 컬럼 > ANY(서브쿼리) -> 컬럼 > MIN : 가장 작은값 보다 크다
  컬럼 < ANY(서브쿼리) -> 컬럼 > MAX : 가장 큰값 보다 작다

 

• 위의 예문을 참고하여 풀이하면 어렵지 않게 풀 수 있다.
• 60번이 부서번호인 최대급여자를 먼저 구한 후 보다 급여가 높은 사원을 구할 수 있어 서브쿼리로 MAX()함수를 이용해 구한다.
  다음 salary를 비교하는데 여기서 최대보다 높아야 함으로 >과 ALL를 선언하여 출력한다.
• 아래 예제는 위와 반대의 경우이므로 <과 ANY를 선언하여 출력한다.
• 첫 번째 퀴즈는 10번 부서의 가장 작은 급여자를 먼저 파악하는 서브쿼리를 작성하고 적게 받는 급여자보다 적게 받아야하니 <과 ALL을 선언하여 출력한다.
• 두 번째 퀴즈는 01번 부서원의 보너스를 먼저 구하고 같은 사원을 비교하기 때문에 IN 을 사용하여 참이면 출력할 수 있도록한다.

 

중요한 부분이기 때문에 심화문제를 통해 더 공부해보자

 

• ‘손하늘'의 관리자, 업무를 먼저 파악해야 하므로 서브쿼리에 WHERE절에 손하늘을 정의하고 SELECT문에 관리자, 업무를 선언한다.
  그리고 메인쿼리 WHERE절에 관리자, 업무를 선언하여 출력한다.
• 화학과의 평점을 먼저 서브쿼리에 선언하고 메인쿼리에서 화학을 제외한 평점을 선언하여 출력한다.
• 화학과의 평점과 학년을 서브쿼리에 선언하고 메인쿼리에는 화학을 제외한 같은 학년인원을 출력하기 위해 WHERE절에 학년, 평점을 선언하고 출력한다.
• 먼저 핵화학의 과목의 평균을 알아야 할 필요성이 있어 서브쿼리에 핵화학의 과목번호를 나타내고 또 다른 서브쿼리를 만들어 해당 과목의 평균을 구해준다.
  메인쿼리에서 교수까지 출력되어야 하기 때문에 서로의 테이블을 JOIN하여 GROUP BY로 그룹을 만들어 해당 그룹에 해당되는 값만 출력되게 하고 HAVING절로 조건을 비교하여 출력한다.

2. FROM절 서브쿼리

FROM절 서브쿼리는?
FROM절에 사용되는 서브쿼리이다.

대표적으로 사용되는 ROWNUM에 대해서 실습해보자

 

대표 예제

• ROWNUM이란 셀의 순서를 나타내는 것이다.
  만약 입사한 순서를 5명 구하고 싶다면 어떻게 해야할까?
• 먼저 입사한 명단을 알아야 할 것이고 서브쿼리를 만든 다음 
  그 서브쿼리를 FROM절에 선언한다.
  그리고 그 서브쿼리괄호 뒤 임의의 별칭을 붙여준다.
  다음 WHERE절을 이용하여 ROWNUM의 크기를 정해주고
  SELECT문에서 ROWNUM을 선언하고 별칭.*을 선언하여 출력하면 5명에 대한 정보가 나온다.
• 대표적으로 마지막 문제를 풀이하면,
  기말고사 과목별 평균을 먼저 서브쿼리로 선언한다.
  이 때 과목번호와, 과목명을 그룹으로 하여 과목별로 성적이 나열되게 하고 SELECT에 AVG()함수를 이용하여 평균을 출력한다.
  그리고 이 결과를 ROWNUM ≤ 3의 값을 이용하여 3개만 출력한다.

3. INDEX(인덱스)

인덱스는?
테이블 내의 원하는 레코드를 빠르게 찾을 수 있도록 만든 데이터 구조이다.

인덱스의 특성

• B*TREE는 ORACLE 데이터베이스에서 가장 많이 쓰이고 기본이다.
  여기서 B는 BALANCE를 뜻하고 실행 시 최상위 Node를 찾아서 하위 Node를 검색한다.
• 데이터가 적을 때는 꼭 사용하지 않아도 되지만 데이터가 많다면 사용하여 검색시간을 단축하고 일정하게 검색할 수 있도록 도와준다.
• 생성 방법 : CREATE INDEX 인덱스 명 ON 테이블명( 컬럼1, 컬럼2…. );

 

인덱스 생성간 고려해야할 조건

• WHERE절이나 JOIN 조건으로 자주 사용되는지 확인한다.
• 모든 값이 칼럼 내에서 UNIQUE한 경우인지 확인한다.
• 넓은 범위의 값을 가진 칼럼인지 확인한다.

 

대표 예제

• 기존 테이블 emp00이 있다. 예를들면 emp00의 테이블을 실행시켰을 때 1초가 걸렸다고 가정한다.
• emp00_employee_id_idx를 인덱스명으로 선언하고 emp(employee_id)의 테이블(컬럼)을 선언하여 인덱스를 생성하였다.
• 다시 테이블을 실행시키면 1초보다 시간은 줄어들고 출력 시 일정한 시간으로 검색되는 것을 알 수 있다.

4. VIEW(뷰)

뷰는?
항상 반복적으로 쓰이는 문을 뷰로 생성하여 테이블처럼 사용 가능하게 만든 것이다.

• 보안적인 요소로 사용된다.
• 생성 방법 : CREATE [OR REPLACE] VIEW 뷰이름 AS 쿼리;

 

대표 예제

• CREATE VIEW를 사용하여 뷰를 생성하는데 뷰이름, AS 뒤로 쿼리를 위치시켜 생성한다.
• 이렇게 생성된 뷰는 SELECT * FROM 뷰이름; 으로 출력할 수 있다.
• 삭제는 DROP VIEW 뷰이름; 을 통하여 삭제가 가능하다.

5. SEQUENCE(시퀀스)

시퀀스는?
숫자를 1씩 추가하는 역할을 한다.

• 보통 PRIMARY KEY 값을 생성하기 위해서 사용한다.
• 생성방법 : CREATE SEQUENCE Sequence이름;

 

• CREATE SEQUENCE 시퀀스 이름을 통해 생성한다.
• 삽입은 INSERT INTO문을 사용하여 삽입하는데 시퀀스이름.NEXTVAL 을 통해 이 값은 1씩 증가하는 값을 가진다.
• 시퀀스를 실행하면 총 3개를 추가했기 때문에 1,2,3 순으로 나오게 된다.

6. PL/SQL

PL/SQL은?
오라클에 내장되어 있는 Procedure Language이며 변수정의, 조건처리, 반복처리를 지원한다.

• output에서 출력을 위해 set serveroutput on; 을 꼭 선언한다.
• 선언부(DECLARE) — 실행부(BEGIN ~ END) — 예외처리부(Exeption)
  순으로 진행된다.

 

대표 예제

• 먼저 set serveroutpur on;을 선언하여 출력될 수 있도록 한다.
• 시작부로 DECLARE를 선언해준다.
• 변수선언은 변수를 선언하고 타입을 선언하는데 이 때 :=로 값을 선언해준다.
• 상수선언은 CONSTANT를 사용하여 선언한다.
• 실행부로 BEGIN을 선언한다.
• 출력을 위해 dbms_output.out_line();를 통하여 변수와 상수를 출력한다.
• 마지막으로 END로 종료한다.

 

여기서 특정 테이블의 로우를 검색해서 변수에 할당 후 출력도 가능하다.
예제로 살펴보자

 

• SELECT에 기존 테이블의 출력하고싶은 값을 선언한다.
• 여기서 선언한 값에 변수를 INTO문을 통하여 순서대로 선언한다.
• FROM 테이블을 선언하고
• WHERE문을 사용하여 조건을 선언한다.
• 출력문을 통하여 출력한다.

 

위의 두가지의 예제를 통해서 배운 지식으로 최종 예제를 보면서 공부해보자

 

• 사원번호 100번의 사원이름과 부서명을 출력하는 문제이다.
• 먼저 이름과 부서명을 담을 변수를 선언해준다.
• SELECT문에 출력하고싶은 변수를 선언하고 INTO문을 통해 변수를 삽입한다.
• FROM, JOIN을 통해 각 테이블의 별칭을 선언하고 ON에 연결, WHERE문에 조건을 선언하여 100번인 인원을 출력할 수 있도록 하였다.
• 출력문을 통해 변수를 선언해주면 해당 값이 출력된다.

오늘 교육 간 느낀점

오늘은 서브쿼리를 끝내고 인덱스, 뷰, 시퀀스, PL/SQL를 배웠다.
전에 배운 것들이 이어져서 확실히 이해하고 있어야만 오늘의 진도를 잘 이해할 수 있었다. 다행히도 전의 내용을 파악하고 있었는지 이해하는데 엄청 어렵지는 않았다.

 

특히 PL/SQL은 자바에서는 자동완성 기능이 있어서 기능을 이용하여 결과를 출력할 수 있었는데 이 부분이 SQL에선 부족하여 직접 다 작성하여 출력해야하는 불편함을 느꼈다. 하지만 변수선언, 출력방법 이 두가지만 차이가 나고 나머지 실행부 에서는 기존과 크게 다르지 않아서 이해하는데 큰 어려움을 없었다.

 

하지만 다음 주에 심화과정을 배우고 모델링을 배우면 얘기가 달라지겠지만..
다음 주를 완벽히 대비하기 위해서 주말에 SQL을 열심히 복습해야겠다!