집합연산
합집합(UNION,UNION ALL)
교집합(INTERSECT)
차집합(MINUS)
형식 )
select문1 집합연산자 select문2
select문1 과 select문2 의 필드개수와 데이터타입이 서로 같아야함
데이터
-- 부서 테이블 제거하기
DROP TABLE EMP;
-- 사원 테이블 제거하기
DROP TABLE DEPT;
-- 급여 테이블 제거하기
DROP TABLE SALGRADE;
-- 부서 테이블 제거하기
DROP TABLE EMPLOYEE;
-- 사원 테이블 제거하기
DROP TABLE DEPARTMENT;
-- 급여 테이블 제거하기
DROP TABLE SALGRADE;
-- 부서 테이블 생성하기
CREATE TABLE DEPT(
DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14),
LOC VARCHAR2(13) ) ;
-- 사원 테이블 생성하기
CREATE TABLE EMP(
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
-- 급여 테이블 생성하기
CREATE TABLE SALGRADE(
GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );
-- 사원 테이블에 샘플 데이터 추가하기
INSERT INTO DEPT VALUES(10, '경리부', '서울');
INSERT INTO DEPT VALUES(20, '인사부', '인천');
INSERT INTO DEPT VALUES(30, '영업부', '용인');
INSERT INTO DEPT VALUES(40, '전산부', '수원');
-- 부서 테이블에 샘플 데이터 추가하기
INSERT INTO EMP VALUES(1001, '김사랑', '사원', 1013, to_date('2007-03-01','yyyy-mm-dd'), 300, NULL, 20);
INSERT INTO EMP VALUES(1002, '한예슬', '대리', 1005, to_date('2007-04-02','yyyy-mm-dd'), 250, 80, 30);
INSERT INTO EMP VALUES(1003, '오지호', '과장', 1005, to_date('2005-02-10','yyyy-mm-dd'), 500, 100, 30);
INSERT INTO EMP VALUES(1004, '이병헌', '부장', 1008, to_date('2003-09-02','yyyy-mm-dd'), 600, NULL, 20);
INSERT INTO EMP VALUES(1005, '신동협', '과장', 1005, to_date('2005-04-07','yyyy-mm-dd'), 450, 200, 30);
INSERT INTO EMP VALUES(1006, '장동건', '부장', 1008, to_date('2003-10-09','yyyy-mm-dd'), 480, NULL, 30);
INSERT INTO EMP VALUES(1007, '이문세', '부장', 1008, to_date('2004-01-08','yyyy-mm-dd'), 520, NULL, 10);
INSERT INTO EMP VALUES(1008, '감우성', '차장', 1003, to_date('2004-03-08','yyyy-mm-dd'), 500, 0, 30);
INSERT INTO EMP VALUES(1009, '안성기', '사장', NULL, to_date('1996-10-04','yyyy-mm-dd'),1000, NULL, 20);
INSERT INTO EMP VALUES(1010, '이병헌', '과장', 1003, to_date('2005-04-07','yyyy-mm-dd'), 500, NULL, 10);
INSERT INTO EMP VALUES(1011, '조향기', '사원', 1007, to_date('2007-03-01','yyyy-mm-dd'), 280, NULL, 30);
INSERT INTO EMP VALUES(1012, '강혜정', '사원', 1006, to_date('2007-08-09','yyyy-mm-dd'), 300, NULL, 20);
INSERT INTO EMP VALUES(1013, '박중훈', '부장', 1003, to_date('2002-10-09','yyyy-mm-dd'), 560, NULL, 20);
INSERT INTO EMP VALUES(1014, '조인성', '사원', 1006, to_date('2007-11-09','yyyy-mm-dd'), 250, NULL, 10);
-- 급여 테이블에 샘플 데이터 추가하기
INSERT INTO SALGRADE VALUES (1, 700,1200);
INSERT INTO SALGRADE VALUES (2, 1201,1400);
INSERT INTO SALGRADE VALUES (3, 1401,2000);
INSERT INTO SALGRADE VALUES (4, 2001,3000);
INSERT INTO SALGRADE VALUES (5, 3001,9999);
합집합(union)
=> 결과값에서 중복값을 제거된 상태로 출력된다.
/* student 테이블의 학생이름과 profeesor 테이블의 교수이름 합쳐 출력 */
SQL> SELECT name FROM student UNION SELECT name FORM professor;
NAME
--------------------
고희석
김광식
김우주
김정현
김태석
김현정
박광수
박철수
박철재
백태성
이태규
장민석
최성희
union all
=> 중복 허용
/* student 테이블의 학생이름과 profeesor 테이블의 학과번호를중복 허용하여 출력 */
SQL> SELECT dept_id FROM student UNION ALL SELECT dept_id FROM professor;
DEPT_ID
--------------------
920
920
920
923
923
923
925
925
920
920
923
923
925
925
교집합(INTERSECT)
SQL> /* 컴퓨터공학과 학생들 중에서 교과목에 상관없이 학점을 'A+'받은 학생들의 학번을 검색 */
SQL> select s.stu_id
2 from student s, department d, takes t
3 where s.dept_id=d.dept_id AND t.stu_id=s.stu_id
4 AND d.dept_name='컴퓨터공학과' AND t.grade='A+';
STU_ID
--------------------
1292001
1292003
=> 조건절로만 확인한 것
INTERSECT 사용
SQL> select stu_id
2 from student s, department d
3 where s.dept_id=d.dept_id AND dept_name='컴퓨터공학과'
4 INTERSECT
5 select stu_id
6 from takes
7 where grade='A+';
STU_ID
--------------------
1292001
1292003
=> 출력값이 같다.
차집합(MINUS)
SQL> /* 산업공학과 학생들 중에서 한번이라도 A+을 받지 못한 학생들의 학번 검색 */
SQL> /* 산업공학과 학생들의 학번을 검색(학생테이블, 학과테이블) */
SQL> /* 성적테이블 에서는 A+인 학생들의 학번 검색 */
테이블1)
SQL> select stu_id
2 from student s, department d
3 where s.dept_id=d.dept_id AND dept_name='산업공학과';
STU_ID
--------------------
1292301
1292303
1292305
테이블2)
SQL> select stu_id
2 from takes
3 where grade='A+';
STU_ID
--------------------
1292001
1292003
1292303
이 두 결과값을 빼면
뺀 값의 결과값에서(1292301, 1292305, 1292001, 1292003) 성적 비교한 후
A+가 아닌 학생들만 남게된다.
SQL> select stu_id
2 from student s, department d
3 where s.dept_id=d.dept_id AND dept_name='산업공학과'
4 MINUS
5 select stu_id
6 from takes
7 where grade='A+';
STU_ID
--------------------
1292301
1292305
JOIN
=> 두 테이블로부터 특정 조건을 만족하는 레코드들을 하나의 레코드로 결합하는 연산
즉, 테이블간의 결합
형식 ) 테이블이름1 조건식 테이블이름2
필드간의 동등비교(=)가 대부분이며 동등조인이라 함
자연조인(natural join)
=> 서로다른 테이블에서 같은 이름을 갖는 두 필드에 대한 동등 조인 중 하나의 필드를 제거하여
단순히 표현한 연산
외부조인(outer join)
=> 조인 조건에 만족되지 않은 레코드까지 검색 결과에 포함시키기 위한 방법
서로 매치되지 않는 필드에 대해서는 NULL을 입력함
종류)
왼쪽 외부조인(left outer join)
오른쪽 외부조인(right outer join)
완전 외부조인(full outer join)
첫번째 예시를 위한 데이터
Class 테이블
CLASS_ID COURSE_ID YEAR SEMESTER DI PROF_ID CLASSROOM ENROLL
-------------------- -------------------- ---------- ---------- -- -------------------- ------------------ ----------
C101-01 C101 2012 1 A 92301 301호 40
C102-01 C102 2012 1 A 92001 209호 30
C103-01 C103 2012 1 A 92501 208호 30
C103-02 C103 2012 1 B 92301 301호 30
C501-01 C501 2012 1 A 92501 103호 45
C501-02 C501 2012 1 B 92502 204호 25
C301-01 C301 2012 2 A 92502 301호 30
C302-01 C302 2012 2 A 92501 209호 45
C502-01 C502 2012 2 A 92001 209호 30
C502-02 C502 2012 2 B 92301 103호 26
Course테이블
COURSE_ID TITLE CREDIT
-------------------- ---------------------------------------- ----------
C101 전산개론 3
C102 자료구조 3
C103 데이터베이스 4
C301 운영체제 3
C302 컴퓨터구조 3
C303 이산수학 4
C304 객체지향언어 4
C501 인공지능 3
C502 알고리즘 2
SQL> SELECT title, credit, year, semester
2 FROM course, class
3 WHERE course.course_id=class.course_id;
TITLE CREDIT YEAR SEMESTER
---------------------------------------- ---------- ---------- ----------
전산개론 3 2012 1
자료구조 3 2012 1
데이터베이스 4 2012 1
데이터베이스 4 2012 1
인공지능 3 2012 1
인공지능 3 2012 1
운영체제 3 2012 2
컴퓨터구조 3 2012 2
알고리즘 2 2012 2
알고리즘 2 2012 2
왼쪽 외부조인 첫번째 방법(left outer join)
SQL> SELECT title, credit,year,semester
2 FROM course LEFT OUTER JOIN class
3 USING(course_id); => where course.course_id=class.course_id; 같음
=> course 테이블을 기준으로 class 테이블을 왼쪽으로 조인(결합) 시키면서 공통값은 course_id를 사용(USING)하겠다.
TITLE CREDIT YEAR SEMESTER
---------------------------------------- ---------- ---------- ----------
전산개론 3 2012 1
자료구조 3 2012 1
데이터베이스 4 2012 1
데이터베이스 4 2012 1
인공지능 3 2012 1
인공지능 3 2012 1
운영체제 3 2012 2
컴퓨터구조 3 2012 2
알고리즘 2 2012 2
알고리즘 2 2012 2
객체지향언어 4
이산수학 4
=> 객체지향언어,이산수학 year, semester null값
: class테이블에는 객체지향언어와 이산수학의 데이터을 가지고 있지 않기 때문에 NULL값으로 표현됨
두번째 방법
SQL> SELECT title, credit,year,semester
2 FROM course, class
3 WHERE course.course_id=class.course_id (+);
=> using 함수 대신에 where절에서 (+)을 이용하여 조인 시킴
TITLE CREDIT YEAR SEMESTER
---------------------------------------- ---------- ---------- ----------
전산개론 3 2012 1
자료구조 3 2012 1
데이터베이스 4 2012 1
데이터베이스 4 2012 1
인공지능 3 2012 1
인공지능 3 2012 1
운영체제 3 2012 2
컴퓨터구조 3 2012 2
알고리즘 2 2012 2
알고리즘 2 2012 2
객체지향언어 4
이산수학 4
=> 왼쪽 조인은 왼쪽테이블의 값이 다 나온다.
그리고 오른쪽테이블에 그 값이 없으면 NULL값이 출력됨
(왼쪽테이블은 정보가 있지만 오른쪽 테이블에는 그 정보가 없기 때문에)
오른쪽 조인(right outer join)
SQL> SELCET title,credit,year,semester
2 FROM course RIGHT OUTER JOIN class
3 USING(course_id);
TITLE CREDIT YEAR SEMESTER
---------------------------------------- ---------- ---------- ----------
전산개론 3 2012 1
자료구조 3 2012 1
데이터베이스 4 2012 1
데이터베이스 4 2012 1
운영체제 3 2012 2
컴퓨터구조 3 2012 2
인공지능 3 2012 1
인공지능 3 2012 1
알고리즘 2 2012 2
알고리즘 2 2012 2
=> course테이블을 기준으로 class 테이블 오른쪽으로 조인
: 객체지향언어,이산수학 이 데이터를 가지고 있지 않기 때문에 출력x
완전 외부조인(full outer join)
양쪽 테이블에서 서로 일치하는 레코드가 없을 경우
해달 레코드들로 결과 테이블에 포함시키며 나머지 필드에 대해서는 모드 null을 삽입
집계 함수(aggregate function)
=> 통계연산 기능 제공
종류 )
데이터 개수 : COUNT
합 : SUM
평균 : AVG
최대값 : MAX
최소값 : MIN
* SELECT절과 HAVING절에서 사용가능
sum avg는 숫자형 데이터타입을 갖는 필드에만 적용가능
count
형식)
COUNT(distinct<필드이름>) 전체 검색을 할 경우 필드이름을 (*)
=> 해당 필드에 값이 몇 개인지 출력
distinct (중복제거) 서로 구별되는 값의 개수가 필요한경우 사용
NULL값은 계산에서 제외된다.
예시 데이터)
SQL> select * from student;
STU_ID RESIDENT_ID NAME YEAR ADDRESS DEPT_ID
-------------------- ---------------------------- -------------------- ---------- -------------------- --------------------
1292001 900424-1825409 김광식 3 서울 920
1292002 900305-1730021 김정현 3 서울 920
1292003 891021-2308302 김현정 4 대전 920
1292301 890902-2704012 김현정 2 대구 923
1292303 910715-1524390 박광수 3 광주 923
1292305 921011-1809003 김우주 4 부산 923
1292501 900825-1506390 박철수 3 대전 925
1292502 911011-1809003 백태성 3 서울 925
SQL> select count(dept_id)
2 from student;
COUNT(DEPT_ID)
--------------
8
=> 중복값을 허용시켜서 개수를 구함
SQL> select count(distinct(dept_id))
2 from student;
=> 중복값 제거
COUNT(DISTINCT(DEPT_ID))
-------------
3
sum
형식)
sum(필드이름)
예시 데이터)
PROF_ID RESIDENT_ID NAME DEPT_ID POSITION YEAR_EMP
-------------------- ---------------------------- -------------------- -------------------- -------------------- ----------
92001 590327-1839240 이태규 920 교수 1997
92002 690702-1350026 고희석 920 부교수 2003
92301 741011-2765501 최성희 923 부교수 2005
92302 750728-1102458 김태석 923 교수 1999
92501 620505-1200546 박철재 925 조교수 2007
92502 740101-1830264 장민석 925 부교수 2005
예) 전체 교수들의 재직연수 합
SQL> select sum(2021-year_emp)
2 from professor;
SUM(2021-YEAR_EMP)
------------------
110
avg
형식)
avg(필드이름)
예) 전체 교수들의 재직연수 평균
SQL> select avg(2021-year_emp)
2 from professor;
AVG(2021-YEAR_EMP)
------------------
18.3333333
max // min
max(필드이름) // min(필드이름)
SQL> select max(year) from student;
MAX(YEAR)
----------
4
SQL> select min(year) from student;
MIN(YEAR)
----------
2
SQL> select name,sum(year) from student;
select name,sum(year) from student
*
ERROR at line 1:
ORA-00937: not a single-group group function
집계함수는 결과값을 1개만 출력하고 name은 전부 출력하기 때문에 에러 발생
HAVING
: where절과 having절 group by절을 모두 함께 사용할 경우
where절에 명시된 조건을 만족하는 레코드 검색
group by절에 명시된 필드의 값이 서로 일치하는 레코드 끼리 그룹을 지어 집계함수 적용
그 집계함수를 적용한 결과들 중에서 having절을 만족하는 결과만 골라 출력
즉, having을 사용하기 위해서는 반드시 group by가 사용되어야한다.
널의 처리 (NULL)
널(null) 검색
형식)
필드명 is null
필드명 is not null
/* A+이 아닌 학생들 번호 출력 */
SQL> SELECT stu_id
2 FROM takes
3 WHERE grade <> 'A+';
STU_ID
--------------------
1292001
1292001
1292002
1292002
1292002
1292003
1292301
1292303
1292303
=> where 절
같다 : =
다르다 : <>
중첩 질의(nested query)
SQL문을 다른 SQL문 안에 중첩하여 사용하는 질의
복잡한 질의를 쉽게 표현할 수 있는 수단을 제공
내부 질의(inner query),부 질의 (subquery)
=> 내부에 포함된 SQL문
외부질의(outer query)
=> 부질의를 내부적으로 갖는 SQL문
** 부 질의는 외부 질의의 from 절이나 where 절에 위치
/* 301호 강의실에서 개설된 강좌의 과목명을 출력 */
첫번째 방법)
SQL> SELECT distinct title
2 FROM course c1, class c2
3 WHERE c1.course_id=c2.course_id
4 AND classroom='301호';
TITLE
----------------------------------------
데이터베이스
전산개론
운영체제
두번째 방법)
=> in을 통한 select(검색)을 한번더 한다.
SQL> SELECT title
2 FROM course
3 WHERE course_id in (select distinct course_id
4 FROM class
5 WHERE classroom='301호');
=> in대신에 not in을 사용하면 포함하지 않는값
TITLE
----------------------------------------
전산개론
데이터베이스
운영체제
뷰(view)
: 기존 테이블들로부터 생성되는 가상의 테이블
테이블처럼 물리적으로 생성되는 것이 아니라 기존의 테이블들을 조합하여
사용자에게 실제로 존재하는 테이블인 것처럼 보이게 하는것
기능)
특정 사용자에게 테이블의 내용 중 일부를 숨실 수 있기 때문에 보안의 효과
복잡한 질의의 결과를 뷰로 만들어서 사용하게 되면 질의를 간단히 표현할 수 있다.
형식)
create view 뷰이름 as select문
create or replace view 뷰이름 as select문
or replace : 뷰이름과 같은 뷰가 이미 존재하는 경우 기존 뷰를 지우고 새로 생성(뷰를 수정할때)
select문은 뷰에 사용될 select문
데이터 예시)
SQL> select * from takes;
STU_ID CLASS_ID GRADE
-------------------- -------------------- ----------
1292001 C101-01 B+
1292001 C103-01 A+
1292001 C301-01 A
1292002 C102-01 A
1292002 C103-01 B+
1292002 C502-01 C+
1292003 C103-02 B
1292003 C501-02 A+
1292301 C102-01 C+
1292303 C102-01 C
1292303 C103-02 B+
1292303 C501-01 A+
SQL> create view v_takes as
2 select stu_id, class_id
3 from takes;
View created. -> V_TAKES VIEW 테이블 확인시 table이 아닌 view라고 표기 되어있음
* 뷰 쪽 데이터를 수정하게 되면 원본 데이터도 같이 수정된다.
또한 뒤에 옵션을 추가할 수 있다.
create or replace view v_takes as
2 select stu_id, class_id
3 from takes
4 with read only;
=> with read only를 추가시키므로써 이 뷰는 읽기만 가능하다.
뷰를 삭제할때는 테이블을 지우는것과 같다
drop view 뷰이름;
시퀀스(SEQUENCE)
: 오라클에서는 행을 구분하기 위해서 기본키(PK)를 두고 있다.
기본키는 중복된 값을 가질 수 있으므로 항상 유일한 값을 가져야 하고,
기본키가 유일한 값을 갖도록 사용자가 직접 값을 생성해내려면 불편하기 때문에 시퀀스를 사용
시퀀스는 테이블 내의 유일한 숫자를 자동으로 생성하는 자동번호 발생기이므로
시퀀스를 기본키로 사용하게 되면 사용자의 부담을 줄일 수 있다.
ex) 게시판에 글번호 같은거 -> 자동으로 증가시켜줌
형식)
create sequence 시퀀스이름 옵션;
옵션)
start with n
=> 시퀀스 번호의 시작 값 지정 ex) 만약 1부터 시작되는 시퀀스를 생성하려면 start with 1;
increment by n
=> 연속적인 시퀀스 번호의 증가치를 지정할 때 ex) 1씩 증가 increment by 1;
maxvalue n 또는 nomaxvalue n
=> 시퀀스가 가질 수 있는 최대값 지정
nomaxvalue를 지정하게 되면 ascending 순서일 경우 1027제곱, descending일경우 -1
minvalue n 또는 nominvalue n
=> 시퀀스 최소값 지정
ascending 1 descending일경우 1026제곱
cycle 또는 nocycle
=> 지정된 시퀀스값이 최대값까지 증가 완료되면 다시 start with 옵션에 지정한 시작 값에서 시퀀스 시작
nocycle : 증가가 완료되면 에러발생시킴
cache n 또는 nocache
=> 메모리상의 시퀀스 값을 관리하도록 하는것(기본20)
nocache는 원칙적으로 메모리상에서 시퀀스를 관리하지 않는다.
삭제)
drop sequence 시퀀스명;
currval : 시퀀스의 현재값을 알아내기 위해 사용(반드시 nextval가 먼저 실행되어야만 currval값이 나온다.)
nextval :다음값을 알아내기 위해 사용(즉, 해당 시퀀스의 값을 증가시키고 싶을때)
사용하기 위해서
서브 쿼리가 아닌 select문
insert문의 select절
insert 문의 value절
update문의 set
사용할수 없는 경우
view의 select절
distinct 키워드가 있는 select문
group by,having,order by 절이 있는 select문
select,delete,update의 서브 쿼리
create table, alter table 명령의 default값
nextval로 새로운 값 생성
select 시퀀스명.nextval from dual;
SQL> select se_test.nextval from dual;
NEXTVAL
----------
10
현재의 값을 알아내기 위해 currval
select 시퀀스명.currval from dual;
SQL> select se_test.currval from dual;
CURRVAL
----------
10
** dual은 실제로 존재하지 않는 테이블로 값을 미리 확인하기 위한 가상의 테이블
시퀀스 조회
SQL> select sequence_name,
2 min_value,
3 max_value,
4 increment_by,
5 cycle_flag
6 from user_sequences;
출력값)
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CY
---------------------------------------- ---------- ---------- ------------ --
EMP_ESQ 1 100000 1 N
SE_TEST 1 1.0000E+28 10 N
예제)
사원번호를 생성하는 시퀀스 객체를 생성하여 이를 기본키로 사용하는 사원 테이블-> 사원번호에 사용
시작값이 1이고 1씩 증가하며 최대가 100000 이 되는 시퀀스 emp_seq생성
테스트 테이블)
SQL> create table emp01(
2 empno number(4) primary key,
3 empme varchar(10),
4 hiredate date
5 );
SQL> select * from emp01;
EMPNO EMPME HIREDATE
---------- -------------------- --------
1 ONE 21/04/12
2 TWO 21/04/12
3 THREE 21/04/12
4 FOUR 21/04/12
5 FIVE 21/04/12
시퀀스를 사용하여 데이터 삽입
SQL> insert into emp01
2 values(emp_seq.nextval,'ONE',sysdate);
'Learn > KH정보교육원' 카테고리의 다른 글
| [KH정보교육원 당산] 25일 ( 오라클 함수 ) (0) | 2021.04.13 |
|---|---|
| [KH정보교육원 당산] 연습문제4 (SQL활용) (0) | 2021.04.12 |
| [KH정보교육원 당산] 23일차 (오라클 - 쿼리문 실습) (0) | 2021.04.09 |
| [KH정보교육원 당산] 22일차 (데이터베이스) (0) | 2021.04.08 |
| [KH정보교육원 당산] 연습문제3 (데이터베이스) (0) | 2021.04.07 |