TIL_210412_SQL활용(집합연산, 집계함수, 중첩질의, VIEW, SEQUENCE)

목차

집합연산 - UNION, INTERSECT, MINUS
JOIN
집계함수 - COUNT, SUM, AVG, MAX, MIN
GROUP BY
HAVING절
NULL 처리
중첩 질의
뷰 (VIEW)
시퀀스

 

---

 

 

집합연산 - UNION, INTERSECT, MINUS

관계대수의 집합 연산인 합집합, 교집합, 차집합에 해당하는 연산자

 

형식

SELECT문 1  집합연산자 SELECT문 2

 

UNION

합집합을 뜻하여 중복된 데이터는 제거된다. 중복을 허용하고 싶으면 UNION ALL 키워드를 사용한다.

조건은  select문 1과 select문 2의 필드 갯수, 데이터 타입이 일치해야 한다

 

UNION 예시 1

학생 테이블 이름과 교수 테이블 교수 이름 합쳐서 출력(중복 제거)

SELECT  NAME  FROM STUDENT
UNION
SELECT  NAME  FROM PROFESSOR;

 

 

 

UNION 예시 2

학생 테이블과 교수 테이블의 학과번호를 합쳐서 출력(중복허용)

SELECT DEPT_ID FROM STUDENT
UNION ALL
SELECT DEPT_ID FROM PROFESSOR;

 

INTERSECT 

교집합을 뜻하는 키워드이다

 

INTERSECT 예시 

컴공과 학생들 중 교과목에 상관없이 학점을 a+받은 학생들의 학번 출력 

(교집합으로 결과값 확인 전 

조건절 이용시
SELECT  S.STU_ID
FROM   STUDENT S, DEPARTMENT D, TAKES T
WHERE  S.DEPT_ID = D.DEPT_ID AND 
            T.STU_ID  = S.STU_ID   AND
            DEPT_NAME = '컴퓨터공학과'  AND GRADE = 'A+';


INTERSECT 이용시
SELCT           STU_ID
FROM           STUDENT S, DEPARTMENT D
WHERE         S.DEPT_ID = D.DEPT_ID AND 
                  DEPT_NAME = '컴퓨터공학과'
INTERSECT
SELECT         STU_ID
FROM          TAKES
WHERE         GRADE = 'A+';

 

 

 

MINUS

차집합을 뜻하는 키워드이다

 

MINUS 예시

산업공학과 학생들 중 한번이라도 a+받지 못한 학생들 학번 검색

SELECT   STU_ID
FROM    STUDENT S, DEPARTMENT D
WHERE   S.DEPT_ID=D.DEPT_ID AND D.DEPT_NAME = '산업공학과'
MINUS
SELECT   STU_ID
FROM    TAKES
WHERE   GRADE = 'A+';

 

---

 

JOIN

두 테이블로부터 공통 속성을 기준으로 속성값이 같은 레코드를 수평으로 결합하는 연산

 

 

1) 자연조인

서로 다른 테이블에서 같은 이름을 갖는 두 필드에 대한 동등조인의 중복된 필드 중 하나를 제거해 단순히 표현한 연산

예) 고객 테이블 - 고객번호, 이름, 주소

     주문 테이블 - 주문번호, 고객번호, 도서번호

     => 동등조인 필드명 : 고객번호, 이름, 주소, 주문번호, 도서번호 (고객번호 필드는 1개만 존재)

 

2) 외부조인

자연조인시 조인에 실패한 레코드들을 모두 보여주되 값이 없는 대응 속성에는 NULL 값을 채워서 반환 

 

(1) 왼쪽 외부조인

연산자의 왼쪽에 위치한 테이블의 각 레코드에 대해 오른쪽 테이블에 조인 조건에 부합하는 레코드가 없을 경우

검색 결과에 포함, 생성되는 결과 레코드에서 오른쪽 테이블의 나머지 필드에는 NULL 삽입 

 

예) 모든 교과목들에 대해 교과목명, 개설갯수, 개설 년도, 개설 학기를 검색

SELECT TITLE, CREDIT, YEAR, SEMESTER
FROM COURSE LEFT OUTER JOIN CLASS
USING (COURSE_ID);

같은 표현
SELECT  TITLE, CREDIT, YEAR, SEMESTER
FROM   COURSE C1, CLASS C2
WHERE  C1.COURSE_ID = C2.COURSE_ID (+);

 

(2)오른쪽 외부조인 예시

SELECT  TITLE, CREDIT, YEAR, SEMESTER
FROM  COURSE RIGHT OUTER JOIN CLASS
USING  (COURSE_ID);

같은 표현

SELECT  TITLE, CREDIT, YEAR, SEMESTER
FROM  COURSE C1, CLASS C2
WHERE C1.COURSE_ID (+) = C2.COURSE_ID;

 

 

(3) 완전 외부조인

양쪽 테이블에 서로 일치하는 레코드가 없을 경우, 해당 레코드들도 결과 테이블에 포함시키며

나머지 필드에 대해서는 모두 NULL을 삽입

SELECT  TITLE, CREDIT, YEAR, SEMESTER
FROM  COURSE FULL OUTER JOIN CLASS
USING  (COURSE_ID);

 

 

---

 

집계함수

통계연산의 기능을 제공한다 (COUNT, SUM, AVG, MAX, MIN)

※주의사항 : SELECT절과 HAVING절에서만 사용 가능하며 FROM, WHERE절에서는 사용 불가하다

                 일반 필드와 같이 사용 불가하다 (집계함수는 결과값 1개지만, 일반 필드 결과는 여러개일 수 있기에)

                  예) SELECT SUM(YEAR), NAME FROM STUDENT => 오류 발생 

                 SUM, AVG는 숫자형 데이터 타입을 갖는 필드에만 적용 가능하다

                

 

1) COUNT : 데이터 갯수

형식

COUNT (DISTINCT 필드명) : 해당 필드에 값이 몇개인지 출력
                                    서로 구별되는 값의 개수가 필요한 경우에만 사용
                                    NULL값은 계산에서 제외

(단 필드명에는 필드명 대신 * 가 사용된 경우, 레코드의 개수를 계산)

그리고 자바에서 쿼리문 결과를 불러올 때 rs.getString(count(*)) => 안에 count(*)를 인식할 수 없으므로

                                                       rs.getString(1)         => 인덱스 번호 사용해야 한다

 

 

COUNT 예시 (1)

STUDENT 테이블에서 DEPT_ID 필드에 값이 몇 개인지를 출력 (중복O)

SELECT COUNT (DEPT_ID)
FROM STUDENT;

COUNT 예시 (2)

student 테이블에서 dept_id 필드에 값이 몇 개인지를 출력 (중복X)

SELECT COUNT (DISTINCT DEPT_ID)
FROM STUDENT;

 

2) SUM : 합계를 구하는 집계함수

 

형식

SUM (필드명)

 

SUM 예시)

전체 교수의 재직연수 합

 

3) AVG : 평균을 구하는 집계함수

 

형식

AVG (필드명)

AVG 예시)

전체 교수의 평균 재직연수 출력

 

4) MAX, MIN : 최대값 / 최소값을 구하는 집계함수

 

MAX 예시) 학생 테이블에서 학년수가 가장 높은 값 출력 

 

 

MIN 예시) 학생 테이블에서 학년수가 가장 낮은 값 출력

 

 

---

 

GROUP BY

그룹별로 집계함수 적용 가능, SELECT문에서 WHERE절 다음에 위치

(WHERE절 없으면 FROM절 다음에 위치)

예) 학과별 학생 수, 부서별 최대 급여

 

형식

GROUP BY 필드리스트

 

GROUP BY 예시1)

student 테이블에서 학과번호별로 레코드의 개수 출력

select dept_id, count(*)
from student
group by dept_id;

 

GROUP BY 예시2)

학과번호 대신 department 테이블과 조인하여 학과 이름 수를 출력

select dept_name, count(*)
from student s, department d
where s.dept_id=d.dept_id
group by dept_name;

 

GROUP BY 예시3)

부서별 인원, 평균급여, 최대급여, 최소급여 출력 

select      dname, count(*), avg(sal), max(sal), min(sal)
from       dept, emp
where     emp.deptno=dept.deptno
group by dname;

 

 

GROUP BY 예시4)

학사 db에서 학과별 교수 숫자 평균 재직연수, 최대 재직연수를 출력 

select dept_name 학과명, count(*) 인원수, avg(2021-year_emp) 평균재직연수, max(2021-year_emp) 최대재직연수 
from professor p, department d 
where p.dept_id=d.dept_id 
group by dept_name;

 

GROUP BY 예시5)

평균 재직연수가 10년 이상인 학과에 대해서만 교수 숫자와 평균 재직연수, 최대재직연수 

select dept_name 학과명, count(*) 인원수, avg(2021-year_emp) 평균재직연수, max(2021-year_emp) 최대재직연수 
from professor p, department d 
where p.dept_id=d.dept_id
group by dept_name
having avg(2021-year_emp) >= 10;

※ 그룹에 대한 조건이 있을 경우 GROUP BY절 뒤에 HAVING절 붙임 

 

 

 

---

HAVING절

GROUP BY절에 다른 조건이 있을 때 사용하며 무조건 GROUP BY절이 있어야만 사용 가능하다

 

Q) WHERE절, HAVING절, GROUP BY절이 함께 있을 경우엔 ?

1. WHERE절에 명시된 조건을 만족하는 레코드들 검색

2. GROUP BY절에 명시된 필드값이 서로 일치하는 레코드들끼리 그룹 지어 집계함수 적용

3. 마지막으로 집계함수 적용한 결과들 중 HAVING절 만족하는 결과만 골라서 출력

 

 

---

 

NULL 처리

 

형식

필드명 IS NULL
필드명 IS NOT NULL

 

NULL처리 예시1)

takes 테이블에서 아직 학점이 부여되지 않은 학생의 학번 검색 

 

 

NULL 처리 예시2)

takes 테이블에서 학점a+가 아닌 학생들 학번 검색  ( <> : 같지 않다는 의미 )

 

 

 

---

중첩 질의

SQL문을 다른 SQL문 안에 중첩하여 사용하는 질의

내부질의, 부질의 (내부에 포함된 SQL문), 외부질의 (부질의를 내부적으로 갖는 SQL문) 이 있다

부질의(서브쿼리)는 외부 질의의 FROM절이나 WHERE절에 위치 

 

중첩질의 예시1)

301호 강의실에서 개설된 강좌의 과목명을 출력

SELECT       TITLE
FROM        COURSE
WHERE      COURSE_ID IN (
SELECT       DISTINCT COURSE_ID
FROM       CLASS
WHERE      CLASSROOM = '301호'); 

 

Q) 쿼리 실행 과정 ?

서브 쿼리부터 실행됨 -> 외부 쿼리가 실행됨 ->  외부쿼리 결과의 레코드 행들이 차례대로 서브쿼리 결과와

비교되며 결과를 출력

 

 

중첩질의 예시2)

2012년 2학기에 개설되지 않은 과목명 검색 

SELECT    TITLE
FROM     COURSE 
WHERE   COURSE_ID NOT IN (
SELECT   COURSE_ID 
FROM    CLASS
WHERE   YEAR = 2012 AND SEMESTER = 2);

 

 

 

---

 

뷰 (VIEW)

기존 테이블로부터 생성되는 가상의 테이블, 사용하기 위해선 권한 부여가 선순위다

 

기능

- 특정 사용자에게 테이블의 내용 중 일부를 숨길 수 있기 때문에 보안 효과

- 복잡한 질의 결과를 뷰로 만들어 사용해 간단히 표현할 수 있음

 

형식

CREATE (OR REPLACE) VIEW 뷰이름 AS SELECT 구문 
OR REPLACE 키워드 : 뷰이름과 같은 뷰가 존재할 경우 기존 뷰를 지우고 새로 생성

 

VIEW 예시)

student 테이블에서 컴공과 학생들 정보만 추출해 뷰를 생성 

create    view cs_student as
select    stu_id, resident_id, name, year,address, s.dept_id
from     student s, department d
where    s.dept_id=d.dept_id AND d.dept_name='컴퓨터공학과';

 

 

생성된 VIEW에 데이터 추가 (파생된 VIEW 테이블의 없는 필드는 원본 테이블에선 NULL로 삽입)

※ 원본 테이블에서 파생된 VIEW에 데이터 삽입시 -> 원본 테이블에도 데이터 자동으로 추가

※ 원본 테이블에서 데이터 추가시                     -> 파생된 VIEW에도 데이터 삽입

insert into v_takes(stu_id, class_id) values('1292502', 'C101-01');

 

 

읽기 전용 VIEW 생성

※ 읽기 전용으로 VIEW 생성시 수정이나 삽입 불가

※ 단, 원본 테이블에서 데이터 수정시 파생 VIEW 테이블에도 데이터 수정

create or replace view v_takes as
select  stu_id, class_id
from takes
with read only;

읽기 전용 VIEW는 데이터 수정, 삽입, 삭제 불가

 

---

 

시퀀스

테이블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기

시퀀스를 기본 키로 사용하면 사용자의 부담 줄일 수 있다

 

기본 형식 (1~6은 순서 상관 x)

CREATE SEQUENCE sequence_name
            (1)              START WITH n
            (2)              INCREMENT BY n
            (3)              MAXVALUE n  |   NOMAXVALUE
            (4)              MINVALUE n   |   NOMINVALUE
            (5)              CYCLE            |  NOCYCLE
            (6)              CACHE n        |  NOCACHE

(1) : 시퀀스 번호의 시작값을 지정
      만약 1부터 시작되는 시퀀스 생성하려면 start with 1이라고 기술

 

(2) : 연속적 시퀀스 번호의 증가치를 지정할 때 사용
      만약 1씩 증가하는 시퀀스 생성하려면 increment by 1로 기술 

 

(3) : 시퀀스가 가질 수 있는 최대값 지정
      NOMAXVALUE - order by asc일 경우 1027승, desc일 경우 -1

 

(4) : 시퀀스가 가질 수 있는 최소값 지정 
      NOMINVALUE - order by asc일 경우 1, desc일 경우 1026승

 

(5) : 지정된 시퀀스 값이 최대값까지 증가가 완료되게 되면 다시 START WITH
      옵션에 지정한 시작 값에서 시퀀스를 시작
      NOCYCLE 은 증가가 완료되게 되면 에러를 발생

 

(6) : 메모리상의 시퀀스 값을 관리하도록 하는 것, 기본 값은 20
      NOCACHE 는 원칙적으로 메모리 상에서 시퀀스를 관리하지 않는다

 

 

1) USER_SEQUENCES;

생성된 시퀀스 객체에 대한 정보를 저정하는 데이터 딕셔너리

 

2) currval

시퀀스 현재 값 알아내기 위해 사용 (반드시 앞에 시퀀스명 있어야함)
   

3) nextval

다음 값을 알아내기 위해 사용  (반드시 앞에 시퀀스명 있어야함)

 

nextval, currval을 사용할 수 있는 경우
1. 서브 쿼리가 아닌 select문
2. insert 문의 select절
3. insert 문의 value절
4. update문의 set절

 

nextval, currval을 사용할 수 없는 경우
1. view의 select절
2. distinct 키워드가 있는 select절
3. group by,having,order by 절이 있는 select절
4. select,delete,update의 서브 쿼리
5. create table, alter table 명령의 default 값

 

시퀀스 예시 1)

부서 번호를 자동으로 부여해주는 시퀀스 객체 생성

create sequence dept_deptno_seq
increment by 10
start with 10;

데이터 딕셔너리 user_sequences로 현재 사용 중인 시퀀스 객체 정보 

select sequence_name,
min_value,
max_value,
increment_by,
cycle_flag
from user_sequences;

 

nextval로 새로운 값 생성 & 시퀀스의 현재 값을 알기 위해 currval 사용

(DUAL : 듀얼 테이블은 어떤 값을 확인해보고 싶을 때 실행하며 실제론 존재 X)

 

 

시퀀스 예시2)

사원번호 생성하는 시퀀스 객체 생성하고 이를 기본키인 사원 번호에 적용
시작값이 1, 증가값이 1, 최대값이 100000이 되는 emp_seq 시퀀스 생성

create sequence emp_seq
start with 1
increment by 1
maxvalue 100000;

 

 

생성된 시퀀스 사용하기 위해 사원 번호를 기본키로 설정해 emp01이란 이름으로 새롭게 생성

create table emp01(
empno number(4) primary key,
ename varchar(10),
hiredate date);

 

생성한 emp_seq 시퀀스로부터 사원번호를 자동으로 할당받아 데이터를 추가하는 문장

insert into emp01 values(emp_seq.nextval, 'julia' ,sysdate);
insert into emp01 values(emp_seq.nextval, 'susan ',sysdate);
insert into emp01 values(emp_seq.nextval, 'jamie' ,sysdate);

데이터를 추가하면서 emp_seq 시퀀스로부터 사원번호를 자동으로 할당받았는지 emp01 테이블 내용 확인

select * from emp01;