SQL.

Presentation on theme: "SQL."— Presentation transcript:

1 SQL

2 질의어와 SQL Structured Query Language
1974년 IBM의 System R project에서 개발된 Sequel이란 언어에 기초 표준 질의어로 채택되어 널리 쓰이는 관계형 질의언어 1986년 ANSI와 ISO에서 표준 질의어로 채택 1992년 SQL2(SQL-92) 발표 2003년 SQL3발표 (최신) 관계 대수나 관계 해석은 확실한 이론적 배경을 제공하나 상용으로 쓰이기에는 어렵고 적절치 않음 SQL은 자연어와 유사하고 비절차적 언어이므로 사용하기 용이 함

3 SQL의 구성: DDL & DML SQL은 크게 DDL과 DML로 구성됨
데이터 정의 언어 (DDL: Data Definition Language) 데이터 저장 구조를 명시하는 언어 테이블 스키마의 정의, 수정, 삭제 데이터 조작 언어 (DML: Data Manipulation Language) 사용자가 데이터를 접근하고 조작할 수 있게 하는 언어 레코드의 검색(search), 삽입(insert), 삭제(delete), 수정(update)

4 데이터 정의 언어 테이블 생성 (create table) 기본키, 외래키 설정 테이블 삭제 (drop table)
테이블 수정 (alter table)

5 데이터 정의 언어 종류 필드의 Data type 종류 테이블 생성 테이블 삭제 테이블 수정 분류 표준 SQL 오라클 설명 문자
char(n) 길이가 n byte인 고정길이 문자열 오라클의 경우 최대 2000byte까지 지정 가능 varchar(n) varchar2(n) 최대 길이가 n byte인 가변길이 문자열 오라클의 경우 최대 4000byte까지 지정 가능 숫자 int 정수형 float 부동 소수 날짜 시간 date 년, 월, 일을 갖는 날짜형 오라클의 경우 날짜의 기본 형식은 ‘yy/mm/dd’이다. time timestamp 년, 월, 일, 시, 분, 초를 갖는 날짜시간형

6 테이블 생성 형식 department 테이블을 생성하는 SQL문
<필드리스트>는 ‘필드명 데이터타입’ department 테이블을 생성하는 SQL문 키워드 not null은 해당 필드에 널을 허용하지 않는다는 것을 의미함 create table <테이블이름> (<필드리스트>) (질의 1) create table department ( dept_id varchar2(10) not null, dept_name varchar2(14) not null, office varchar2(10) )

7 기본키, 외래키 설정 pk_department: 제약식의 이름 테이블을 생성할 기본키 역할을 하는 필드를 지정 (질의 2)
create table department ( dept_id varchar2(10), dept_name varchar2(20) not null, office varchar2(20), constraint pk_department primary key(dept_id) )

8 테이블 생성(student table) not null과 기본키를 지정한 student 테이블 생성 예 (질의 3)
create table student ( stu_id varchar2(10), resident_id varchar2(14) not null, name varchar2(10) not null, year int, address varchar2(10), dept_id varchar2(10), constraint pk_student primary key(stu_id) )

9 테이블 생성(student table) 외래키까지 포함된 student 테이블 생성 예 (질의 3)
create table student ( stu_id varchar2(10), resident_id varchar2(14) not null, name varchar2(10) not null, year int, address varchar2(10), dept_id varcahr2(10), constraint pk_student primary key(stu_id), constraint fk_student foreign key(dept_id) references department(dept_id) )

10 테이블 생성 professor 테이블 (질의 5) create table professor (
prof_id varchar2(10) , resident_id varchar2(14) not null, name varchar2(10) not null, dept_id varchar2(10), position varchar2(10), year_emp int, constraint pk_professor primary key(prof_id), constraint fk_professor foreign key(dept_id) references department(dept_id) )

11 테이블 생성 course 테이블 (질의 6) create table course (
course_id varchar2(10) , title varchar2(14) not null, credit int, constraint pk_course primary key(course_id) )

12 테이블 생성 class 테이블 (질의 7) create table class ( class_id varchar2(10) ,
course_id varchar2(10), year int, semester int, division char(1), prof_id varchar2(10), classroom varchar2(9), enroll int, constraint pk_class primary key(class_id), constraint fk_class1 foreign key(course_id) references course(course_id), constraint fk_class2 foreign key(prof_id) references professor(prof_id) )

13 테이블 생성 takes 테이블 (질의 8) create table takes ( stu_id varchar2(10) ,
class_id varchar2(10), grade char(5), constraint pk_takes primary key(stu_id, class_id), constraint fk_takes1 foreign key(stu_id) references student(stu_id), constraint fk_takes2 foreign key(class_id) references class(class_id) )

14 테이블 삭제 형식 주의 다른 테이블에서 외래키로 참조되는 경우에는 삭제할 수 없음 예
class 테이블은 takes 테이블에서 외래키로 참조됨 takes 테이블을 삭제하기 전에는 class 테이블을 삭제할 수 없음 drop table <테이블이름>

15 테이블 수정 alter table <테이블이름> add <추가할필드> (질의 9)
기존 테이블에 새로운 필드를 추가하거나 기존 필드를 삭제 필드 추가 형식 예) student 테이블에 age 필드를 추가 alter table <테이블이름> add <추가할필드> (질의 9) alter table student add age int

16 테이블 수정 필드 삭제 형식 예) alter table <테이블이름> drop column <삭제할필드>
(질의 10) alter table student drop column age

17 기본키, 외래키 관련 주의사항 외래키를 필드로 갖는 테이블을 생성 할 때
외래키가 참조하는 테이블을 먼저 생성 예) 만일 department 테이블이 존재하지 않는 상태에서 student 테이블을 먼저 생성하면 오류발생

18 기본키, 외래키 관련 주의사항 테이블을 삭제할 때도 같은 문제가 발생함 예)
member 테이블이 있는 한 dept 테이블을 삭제할 수 없음 dept 테이블을 삭제하려면 member 테이블을 먼저 삭제하던지, 외래키를 해 제해야 함 name dept_id 김광식 100 김현정 101 조영수 dept_id name 100 컴퓨터공학과 101 산업공학과 외래키로 참조함 member dept

19 데이터 조작 언어 레코드 삽입 레코드 수정 레코드 삭제 레코드 검색

20 레코드 삽입 형식 <필드리스트> <값리스트>
삽입에 사용될 테이블의 필드들 <값리스트> <필드리스트>의 순서에 맞춰 삽입될 값 <필드리스트>에 나열되지 않은 필드에 대해서는 널값이 입력됨 <필드리스트>를 생략할 경우 <값리스트>에는 테이블을 생성할 때 나열 한 필드의 순서에 맞춰서 값을 나열 insert into <테이블이름> (<필드리스트>) values (<값리스트>)

21 레코드 삽입 예 (질의 11) insert into department (dept_id, dept_name, office)
values ('920', '컴퓨터공학과', '201호') 입력확인을 위한 SQL 명령

22 레코드 삽입 삽입 명령문에 필드 이름을 나열할 경우 그 순서는 테이블을 생성할 때 지정한 순서와 반드시 일치할 필요는 없음
삽입 명령문에 필드 이름을 나열할 경우 그 순서는 테이블을 생성할 때 지정한 순서와 반드시 일치할 필요는 없음 예) (질의 11)과 동일한 SQL department 테이블의 필드들 중에서 office 필드를 생략하는 경우 생략된 필드에는 널이 입력 단, not null로 설정된 필드는 null 값이 들어갈 수 없는 필드이기 때문에 insert문의 <필드리스트>에서 생략할 수 없음 (질의 12) insert into department (office, dept_id, dept_name) values ('201호', '920', '컴퓨터공학과') (질의 13) insert into department (dept_id, dept_name) values ('920', '컴퓨터공학과')

23 레코드 삽입 <필드리스트>를 사용하지 않고 데이터를 삽입하는 예 (질의 14)
insert into department values ('923', '산업공학과', '207호')

24 학사 데이터베이스의 데이터 삽입 예 insert into department values('920', '컴퓨터공학과', '201호') insert into department values('923', '산업공학과', '207호') insert into department values('925', '전자공학과', '308호') insert into student values(' ', ' ', '김광식', 3, '서울', 920) values(' ', ' ', '김정현', 3, '서울', 920) values(' ', ' ', '김현정', 4, '대전', 920) values(' ', ' ', '김현정', 2, '대구', 923) values(' ', ' ', '박광수', 3, '광주', 923) values(' ', ' ', '김우주', 4, '부산', 923) values(' ', ' ', '박철수', 3, '대전', 925) values(' ', ' ', '백태성', 3, '서울', 925)

25 학사 데이터베이스의 데이터 삽입 예 insert into professor
values('92001', ' ', '이태규', '920', '교수', 1997) values('92002', ' ', '고희석', '920', '부교수', 2003) values('92301', ' ', '최성희', '923', '부교수', 2005) values('92302', ' ', '김태석', '923', '교수', 1999) values('92501', ' ', '박철재', '925', '조교수', 2007) values('92502', ' ', '장민석', '925', '부교수', 2005) insert into course values('C101', '전산개론', 3) insert into course values('C102', '자료구조', 3) insert into course values('C103', '데이터베이스', 4) insert into course values('C301', '운영체제', 3) insert into course values('C302', '컴퓨터구조', 3) insert into course values('C303', '이산수학', 4) insert into course values('C304', '객체지향언어', 4) insert into course values('C501', '인공지능', 3) insert into course values('C502', '알고리즘', 2)

26 학사 데이터베이스의 데이터 삽입 예 insert into class values('C101-01', 'C101', 2012, 1, 'A', '92301', '301호', 40) insert into class values('C102-01', 'C102', 2012, 1, 'A', '92001', '209호', 30) insert into class values('C103-01', 'C103', 2012, 1, 'A', '92501', '208호', 30) insert into class values('C103-02', 'C103', 2012, 1, 'B', '92301', '301호', 30) insert into class values('C501-01', 'C501', 2012, 1, 'A', '92501', '103호', 45) insert into class values('C501-02', 'C501', 2012, 1, 'B', '92502', '204호', 25) insert into class values('C301-01', 'C301', 2012, 2, 'A', '92502', '301호', 30) insert into class values('C302-01', 'C302', 2012, 2, 'A', '92501', '209호', 45) insert into class values('C502-01', 'C502', 2012, 2, 'A', '92001', '209호', 30) insert into class values('C502-02', 'C502', 2012, 2, 'B', '92301', '103호', 26)

27 학사 데이터베이스의 데이터 삽입 예 insert into takes values(' ', 'C101-01', 'B+') insert into takes values(' ', 'C103-01', 'A+') insert into takes values(' ', 'C301-01', 'A') insert into takes values(' ', 'C102-01', 'A') insert into takes values(' ', 'C103-01', 'B+') insert into takes values(' ', 'C502-01', 'C+') insert into takes values(' ', 'C103-02', 'B') insert into takes values(' ', 'C501-02', 'A+') insert into takes values(' ', 'C102-01', 'C+') insert into takes values(' ', 'C102-01', 'C') insert into takes values(' ', 'C103-02', 'B+') insert into takes values(' ', 'C501-01', 'A+')

28 레코드 수정 형식 <수정내역> <조건> update <테이블이름>
대상 테이블의 필드에 들어가는 값을 수정하기위한 산술식 ‘,’를 이용해서 여러 필드에 대한 수정 내역을 지정 <조건> 대상이 되는 레코드에 대한 조건을 기술 관계대수에서 선택 연산의 조건식과 같은 의미 테이블의 모든 레코드에 대해 수정을 적용하려면 where 절을 생략 update <테이블이름> set <수정내역> where <조건>

29 레코드 수정 예) student 테이블에서 모든 학생들의 학년을 하나씩 증가
예) professor 테이블에서 ‘고희석’ 교수의 직위를 ‘교수’로 수정하고 학과번호를 ‘923’으로 수정 (질의 16) update student set year = year + 1 (질의 17) update professor set position='교수‘, dept_id='923' where name='고희석‘

30 레코드 삭제 형식 예) professor 테이블에서 이름이 ‘김태석’인 교수를 삭제
where절에 지정된 조건을 만족하는 레코드를 삭제 where절이 생략되면 테이블에서 모든 레코드를 삭제 예) professor 테이블에서 이름이 ‘김태석’인 교수를 삭제 delete문을 이용하여 테이블의 모든 레코드를 삭제하더라도 테이블은 삭제되지 않 음 delete from <테이블이름> where <조건> (질의 18) delete from professor where name='김태석'

31 레코드 삽입 시 주의사항 외래키로 사용되는 필드에 대해 데이터를 삽입할 때
참조하는 테이블의 해당 필드에 그 값을 먼저 삽입해야 함 예) department 테이블이 생성되긴 했지만 아직 레코드가 삽입되지 않은 상태에 서 다음질의의 실행 결과 (질의 19) insert into student values (' ', ' ', '김정현', 3, '서울', '920')

32 레코드 삽입 시 주의사항 department 테이블에 dept_id 필드의 값이 ‘920’인 레코드가 먼저 삽입
(질의 19-1) insert into department values (‘920’, ‘컴퓨터공학과’, ‘201호’); insert into student values (' ', ' ', '김정현', 3, '서울', '920');

33 레코드 수정 시 주의사항 외래키로 사용되는 필드의 값을 수정할 때
외래키가 참조하는 테이블에 삽입되어 있는 값으로만 수정이 가능 예) department 테이블에 dept_id 필드의 값이 ‘920’인 레코드만 삽입되어 있는 상황이라면 ? (질의 19-2) update student set dept_id = ??? where dept_id = ‘920’

34 레코드 삭제 시 주의사항 외래키로 참조되는 필드를 가지고 있는 테이블에서 레코드를 삭제할 경우에도 오류가 발생할 수 있음
외래키로 참조되는 필드를 가지고 있는 테이블에서 레코드를 삭제할 경우에도 오류가 발생할 수 있음 student 테이블에서 외래키로 참조하는 department 테이블의 레코드에 대한 삭제 시도

35 레코드 검색 SQL에서 가장 많이 사용하고, 중요하며, 복잡함 종류 기본 구조 재명명 연산 LIKE 연산자 집합 연산 외부조인
집계 함수 널의 처리 중첩 질의

36 기본 구조 형식 select from where 질의 결과로 출력할 필드들의 리스트, 관계대수의 추출연산에
질의 실행과정에 필요한 테이블들의 리스트를, 관계대수의 카티션 프로덕 트 where 검색되어야 하는 레코드에 대한 조건, 관계대수의 선택연산에서 생략 가능 select <필드리스트> from <테이블리스트> where <조건>

37 기본 구조 예) 의미 from절에 나열된 department 테이블과 student 테이블을 카티션 프로덕트
where절에 지정된 조건식을 만족하는 레코드만 선택 같은 이름의 필드가 두 개 이상의 테이블에 나타날 때 혼동을 피하기 위해 ‘테이 블이름.필드이름’으로 표현 최종적으로 name 필드와 dept_name 필드의 값만을 추출하라 (질의 20) select name, dept_name from department, student where department.dept_id = student_dept_id

38 기본 구조 예) student 테이블에서 모든 학생들의 주소를 추출 (질의 21) select address
from student

39 기본 구조 중복된 레코드를 제거하고 검색하려면 distinct를 사용 예) student 테이블에서 모든 학생들의 주소를 추출
(질의 22) select distinct address from student

40 기본 구조 from 절에 나타난 테이블에서 모든 필드의 값을 추출할 경우에는 select 절에 모든 필드를 명시할 필요 없이 '*'를 사용 예) student 테이블에서 모든 레코드의 모든 필드 값을 추출 (질의 23) select * from student

41 기본 구조 select절에 필드이름 외에 산술식이나 상수의 사용이 가능
예) professor 테이블에서 교수의 이름과 현재까지의 재직연수를 검색 (질의 24) select name, 2012-year_emp from professor

42 기본 구조 from 절에 두 개 이상의 테이블이 포함된 질의
예) select문은 학생들의 이름, 학번, 그리고 소속 학과의 이름을 검색 (질의 25) select student.name, student.stu_id, department.dept_name from student, department where student.dept_id = department.dept_id

43 기본 구조 from 절에 두 개 이상의 테이블을 포함하는 질의
특정 조건이 없는 순수한 카티션 프러덕트보다는 테이블 간의 레코드에 대 한 관계가 명시된 조인이나 자연조인이 대부분 조인 질의

44 기본 구조 예) 컴퓨터공학과 3학년 학생들의 학번을 검색 (질의 26) select student.stu_id
from student, department where student.dept_id = department.dept_id and student.year = 3 and department.dept_name='컴퓨터공학과'

45 레코드의 순서 지정(order by) 검색 결과를 정렬하여 출력하는 기능
select문 맨 마지막에 다음과 같은 order by절을 추가 형식 오름차순을 기본으로 하며 <필드리스트>에 여러 개의 필드를 나열 할 경우 나열된 순서대로 정렬 예) student 테이블에서 3, 4학년 학생들의 이름과 학번을 검색 학생 이름(name 필드)으로 오름차순으로 정렬하고 같은 이름에 대해서는 학 번의 오름차순으로 정렬 order by <필드리스트> (질의 27) select name, stu_id from student where year = 3 or year = 4 order by name, stu_id

46 레코드의 순서 지정 내림차순은 해당 필드 이름 뒤에 desc 라는 키워드를 삽입 (질의 28)
select name, stu_id from student where year = 3 or year = 4 order by name desc, stu_id

47 재명명 연산 테이블이나 필드에 대한 재명명 실제 테이블 이름이 수정되거나 필드 이름이 바뀌는 것이 아님 질의를 처리하는 과정 동안만 일시적으로 사용 표현이 단순화하거나, 동일 이름이 존재할 경우에 사용 예) student 테이블과 department 테이블을 조인하여 학생들의 이 름과 소속 학과 이름을 검색 (질의 29) select student.name, department.dept_name from student, department where student.dept_id = department.dept_id (질의 30) select s.name, d.dept_name from student s, department d where s.dept_id = d.dept_id

48 재명명 연산 동일 테이블이 두 번 사용되는 예 예) student 테이블에서 ‘김광식’ 학생과 주소가 같은 학생들의 이름 과 주소를 검색 (질의 31) select s2.name from student s1, student s2 where s1.address = s2.address and s1.name = '김광식'

49 필드의 재명명 질의 실행 결과를 출력할 때 원래 필드의 이름 대신 재명명된 이름으 로 출력시키고자 할 때 사용
예) 교수들의 이름과 직위, 재직연수를 출력 (질의 32) select name, position, 2012-year_emp from professor (질의 33) select name 이름, position 직위, 2012-year_emp 재직연수 from professor

50 LIKE 연산자 문자열에 대해서는 일부분만 일치하는 경우를 찾아야 할 때 사용 ‘=‘ 연산자 대신에 ‘like’연산자를 이용함
‘=‘는 정확히 일치하는 경우에만 사용 형식 <필드이름>에 지정된 <문자열패턴>이 들어 있는지를 판단 문자열 패턴 종류 _ : 임의의 한 개 문자를 의미한다 % : 임의의 여러 개 문자를 의미한다 예) ‘%서울%’ : ‘서울’이란 단어가 포함된 문자열 ‘%서울’ : ‘서울’이란 단어로 끝나는 문자열 ‘서울%’ : ‘서울’이란 단어로 시작하는 문자열 ‘_ _ _’ : 정확히 세 개의 문자로 구성된 문자열 ‘_ _ _%’ : 최소한 세 개의 문자로 구성된 문자열 where <필드이름> like <문자열패턴>

51 LIKE 연산자 student 테이블에서 김씨 성을 가진 학생들을 찾는 질의 (질의 34) select *
from student where name like '김%'

52 LIKE 연산자 student 테이블에서 여학생들만을 검색 (질의 35) select * from student
where resident_id like '%-2%'

53 집합연산 관계대수의 집합 연산인 합집합, 교집합, 차집합에 해당하는 연산자 union intersect minus 형식 조건
<select문 1> 과 <select문 2>의 필드의 개수와 데이터타입이 서로 같 아야 함 <select문 1> <집합연산자> <select문 2>

54 UNION 예) student 테이블의 학생 이름과 professor 테이블의 교수 이름을 합쳐서 출력 (질의 36)
select name from student union select name from professor

55 UNION ALL union 연산자는 연산 결과에 중복되는 값이 들어갈 경우 한번만
중복을 제거하고 싶지 않다면 union 연산자 대신 union all 연산자 를 사용한다. 예) student 테이블과 professor 테이블에서 학과번호를 중복을 허 용하여 출력

56 INTERSECT 예) 컴퓨터공학과 학생들 중에서 교과목에 상관없이 학점을 'A+' 받 은 학생들의 학번을 검색 발상의 전환
‘컴퓨터공학과’에 다니는 학생들의 학번과 takes 테이블에서 학점이 'A+'인 학생들의 학번의 교집합 (질의 37) 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+' (질의 40) select stu_id from student s, department d where s.dept_id = d.dept_id and dept_name='컴퓨터공학과' intersect from takes where grade = 'A+';

57 MINUS 예) 산업공학과 학생들 중에서 한번이라도 'A+'를 받지 못한 학생들 의 학번을 검색 (질의 41)
select stu_id from student s, department d where s.dept_id = d.dept_id and dept_name='산업공학과' minus select stu_id from takes where grade = 'A+'

58 외부조인(outer join) 예) 모든 교과목들에 대해 교과목명, 학점수, 개설 년도, 개설 학기를 검색 (질의 42)
예) 모든 교과목들에 대해 교과목명, 학점수, 개설 년도, 개설 학기를 검색 (질의 42) select title, credit, year, semester from course, class where course.course_id = class.course_id

59 외부조인(outer join) 강좌로 개설된 적이 있는 교과목에 대해서만 검색됨
‘이산수학’, ‘객체지향언어’ 교과목들은 class 테이블에 저장되어 있지 않기 때문에 검색 결과에 포함되지 못함

60 왼쪽 외부조인(left outer join)
연산자의 왼쪽에 위치한 테이블의 각 레코드에 대해서 오른쪽 테이블에 조인 조건에 부합하는 레코드가 없을 경우에도 검색 결과에 포함 생성되는 결과 레코드에서 오른쪽 테이블의 나머지 필드에는 널이 삽입 'course left outer join class‘ course 테이블과 class 테이블에 대해 왼쪽 외부조인을 적용 'using (course_id)‘ 조인 조건이 ‘course.course_id = class.course_id'라는 것을 의미 (질의 43) select title, credit, year, semester from course left outer join class using (course_id)

61 오른쪽 외부조인(right outer join)
(질의 45) select title, credit, year, semester from course right outer join class using (course_id)

62 완전 외부조인(full outer join)
양쪽 테이블에서 서로 일치하는 레코드가 없을 경우, 해당 레코드들도 결과 테이블에 포함시키며 나머지 필드에 대해서는 모두 널을 삽입 (질의 47) select title, credit, year, semester from course full outer join class using (course_id)

63 집계 함수(aggregate function)
통계연산 기능 제공 예) 컴퓨터공학과 학생들은 모두 몇 명인가? 교수들의 평균 재직연수는 몇 년인가? 종류 count : 데이터의 개수를 구한다. sum : 데이터의 합을 구한다. avg : 데이터의 평균 값을 구한다. max : 데이터의 최대 값을 구한다. min : 데이터의 최소 값을 구한다. SELECT 절과 HAVING절(뒤에 설명)에서만 사용가능 sum, avg는 숫자형 테이터 타입을 갖는 필드에만 적용가능

64 COUNT 형식 예) student 테이블에서 3학년 학생이 몇 명인지 출력 해당 필드에 값이 몇 개인지 출력
distinct: 서로 구별되는 값의 개수가 필요한 경우에만 사용 NULL은 계산에서 제외됨 단, <필드이름>에는 필드 이름 대신 '*'가 사용된 경우에는 레코드의 개수를 계산 예) student 테이블에서 3학년 학생이 몇 명인지 출력 count( distinct <필드이름> ) (질의 48) select count(*) from student where year = 3

65 COUNT 예) student 테이블에서 dept_id 필드에 값이 몇 개인지를 출력 (질의 49)
select count(dept_id) from student

66 COUNT distinct 키워드를 사용하면 중복되는 데이터를 제외한 개수를 리턴
예) count(dept_id) 대신 count(distinct dept_id)를 사용 (질의 49) select count(distinct dept_id) from student

67 COUNT 예) 컴퓨터공학과의 학생 수를 출력 (질의 50) select count(*)
from student s, department d where s.dept_id = d.dept_id and d.dept_name = '컴퓨터공학과'

68 SUM 형식 예) 전체 교수들의 재직연수 합 sum(<필드이름>) (질의 51)
select sum( year_emp) from professor

69 SUM 예) emp dept 필드 이름 설명 EMPNO 사원번호 ENAME 사원이름 JOB 업무 MGR 관리자번호
HIREDATE 입사날짜 SAL 급여 COMM 커미션 DEPTNO 부서코드 dept 필드 이름 설명 DEPTNO 부서코드 DNAME 부서이름 LOC 위치

70 SUM emp 테이블에 저장된 모든 직원들의 급여 합을 출력
업무(job 필드)가 ‘ANALYST'인 직원들의 급여의 합을 출력 부서 이름이 ‘RESEARCH'인 직원들의 급여의 합을 출력 (질의 52) select sum(sal) from emp (질의 53) select sum(sal) from emp where job = 'ANALYST' (질의 54) select sum(sal) from emp e, dept d where e.deptno = d.deptno and dname = 'RESEARCH'

71 AVG 형식 예) 전체 교수의 평균 재직연수를 출력 avg(<필드이름>) (질의 55)
select avg( year_emp) from professor

72 MIN, MAX 형식 예) 부서 이름이 ‘ACCOUNTING''인 직원들 중에서 최대 급여가 얼마인지 출력
(질의 56) select max(sal) from emp e, dept d where e.deptno = d.deptno and dname = 'ACCOUNTING'

73 GROUP BY select 절에 집계 함수가 사용될 경우 다른 필드는 select 절에 사용할 수가 없음 다음 질의는 오류
select ename, max(sal) from emp 지금까지의 SQL은 전체 레코드들을 대상으로 평균, 합, 최대값/최소 값 만을 출력 GROUP BY를 이용하면 그룹별로 집계함수 적용 가능 예) ‘학과별 학생 수’, ‘부서별 최대 급여’

74 GROUP BY 형식 예) student 테이블에서 학과번호(dept_id 필드)별로 레코드의 개수를 출 력
group by 절은 select문에서 where절 다음에 위치 group by 에 지정된 필드의 값이 같은 레코드들끼리 그룹을 지어 각 그룹별로 집계 함수를 적용한 결과를 출력 예) student 테이블에서 학과번호(dept_id 필드)별로 레코드의 개수를 출 력 group by 절에 사용된 필드를 select 절에 추가하여 사용할 수 있음 group by <필드리스트> (질의 57) select dept_id, count(*) from student group by dept_id

75 GROUP BY 예) 학과번호 대신 department 테이블과 조인하여 학과 이름이 출력 되도록 (질의 57)을 수정
(질의 58) select dept_name, count(*) from student s, department d where s.dept_id = d.dept_id group by dept_name

76 GROUP BY 예) emp, dept 테이블에서 부서별 직원수, 평균급여, 최대급여, 최소 급여를 출력 (질의 59)
select dname, count(*), avg(sal), max(sal), min(sal) from emp e, dept d where e.deptno = d.deptno group by dname

77 GROUP BY 예) 학사 데이터베이스에서 학과별 교수 숫자와 평균 재직연수, 최대 재직연수를 출력 (질의 60)
예) 학사 데이터베이스에서 학과별 교수 숫자와 평균 재직연수, 최대 재직연수를 출력 (질의 60) select dept_name, count(*), avg( year_emp), max( year_emp) from professor p, department d where p.dept_id = d.dept_id group by dept_name

78 HAVING 그룹에 대한 조건을 명시할 때 사용
예) 평균 재직연수가 10년 이상인 학과에 대해서만 교수 숫자와 평균 재직연수, 최대 재직연수를 출력 다음은 오류 Group에 대한 조건은 where절에 사용하지 못함 HAVING절을 이용해야 함 형식 (질의 61) select dept_name, count(*), avg( year_emp), max( year_emp) from professor p, department d where p.dept_id = d.dept_id and avg( year_emp) >= 10 group by dept_name having <집계함수 조건>

79 HAVING 예) having 절을 이용하여 다시 작성 (질의 62)
select dept_name, count(*), avg( year_emp), max( year_emp) from professor p, department d where p.dept_id = d.dept_id group by dept_name having avg( year_emp) >= 10

80 HAVING 예) 직원 숫자가 5명 이상인 부서에 대해서 부서별 직원수, 평균급여, 최대급여, 최소급여를 출력 (질의 63)
예) 직원 숫자가 5명 이상인 부서에 대해서 부서별 직원수, 평균급여, 최대급여, 최소급여를 출력 (질의 63) select dname, count(*), avg(sal), max(sal), min(sal) from emp e, dept d where e.deptno = d.deptno group by dname having count(*) >= 5

81 HAVING where절과 having절, group by절을 모두 함께 사용할 경우

82 널(null)의 처리 널을 검색하는 방법 형식 예) takes 테이블에서 아직 학점이 부여되지 않은 학생의 학번을 검색
<필드이름> is null <필드이름> is not null (질의 64) select stu_id from takes where grade is null

83 널의 처리 예) takes 테이블에서 학점이 'A+'가 아닌 학생들의 학번을 검색
grade 필드의 값이 널인 레코드에 대해서는 질의 결과에 포함되지 않음 하지만 count(*)는 특정 필드가 아닌 레코드 전체에 대한 연산이므로 널의 존 재 여부와는 무관함 (질의 65) select stu_id from takes where grade <> 'A+'

84 중첩 질의(nested query) SQL문을 다른 SQL문 안에 중첩하여 사용하는 질의
복잡한 질의를 쉽게 표현할 수 있는 수단을 제공 내부질의(inner query), 부질의(subquery) 내부에 포함된 SQL문 외부질의(outer query) 부 질의를 내부적으로 갖는 SQL문 부 질의는 외부 질의의 from 절이나 where 절에 위치 종류 in, not in =some, <=some, <some, >some, >=some, <>some (some 대신 any를 사용해도 됨) =all, <=all, <all, >all, >=all, <>all exists, not exists

85 IN, NOT IN 예) '301호' 강의실에서 개설된 강좌의 과목명을 출력 부 질의 외부 질의 (질의 66)
키워드 in 뒤에 나오는 SQL문으로서 class 테이블에서 강의실이 '301호'인 교과목 번호 를 검색 외부 질의 course 테이블에서 course_id 필드의 값이 부 질의의 검색 결과에 포함되는 경우(in)에 만 과목명을 출력 (질의 66) select title from course where course_id in (select distinct course_id from class where classroom = '301호')

86 IN, NOT IN 동일한 표현 (질의 67) select distinct title
from course c1, class c2 where c1.course_id = c2.course_id and classroom = '301호'

87 IN, NOT IN 예) 2012년 2학기에 개설되지 않은 과목명을 검색 (질의 68) select title
from course where course_id not in (select distinct course_id from class where year = 2012 and semester = 2)

88 SOME, ALL =some <=some =all <=all
지정된 필드의 값이 부 질의 검색 결과에 존재하는 임의의 값과 같은지를 나타낼 때 사용 in과 같은 의미 <=some 부 질의의 검색 결과에 존재하는 임의의 값보다 작거나 같은지를 나타낼 때 사용 =all 지정된 필드의 값이 부 질의 검색 결과에 포함된 모든 값과 같은지를 판단 <=all 지정된 필드의 값이 부 질의 검색 결과에 포함된 모든 값보다 작거나 같은 지를 판단

89 SOME, ALL 예) 가장 많은 수강 인원을 가진 강좌를 검색 (질의 69)
select c1.course_id, title, year, semester, prof_id from class c1, course c2 where c1.course_id = c2.course_id and enroll >= all (select enroll from class)

90 EXISTS, NOT EXIST 부 질의 검색 결과에 최소한 하나 이상의 레코드가 존재하는지의 여부 를 표현 exists
최소한 한 개의 레코드가 존재하면 참이 되고 그렇지 않으면 거짓 not exists 부 질의의 결과에 레코드가 하나도 없으면 참이 되고 하나라도 존재하면 거 짓 예) '301호' 강의실에서 개설된 강좌의 과목명을 출력 (질의 70) select title from course where exists (select * from class where classroom = '301호' and course.course_id = class.course_id)

91 EXISTS, NOT EXIST (질의 68)을 not exists로 표현 가능 (질의 71) select title
from course where not exists (select * from class where year = 2012 and semester = 2 and course.course_id = class.course_id)

92 뷰 (view) 기존 테이블들로부터 생성되는 가상의 테이블
테이블처럼 물리적으로 생성되는 것이 아니라 기존의 테이블들을 조 합하여 사용자에게 실제로 존재하는 테이블인 것처럼 보이게 함 기능 특정 사용자에게 테이블의 내용 중 일부를 숨길 수 있기 때문에 보안의 효 과 복잡한 질의의 결과를 뷰로 만들어서 사용하게 되면 질의를 간단히 표현 할 수 있음

93 뷰 생성 생성된 뷰는 테이블과 동등하게 사용 형식
or replace 키워드를 추가하면 <뷰이름>과 같은 뷰가 이미 존재하는 경우 기존의 뷰를 지우고 새로 생성 <select문> 뷰 생성에 사용될 select문 create or replace view <뷰이름> as <select문>

94 뷰 생성 예) takes 테이블에서 grade 필드를 제외한 나머지 필드만으로 구성 된 뷰를 생성 (질의 72)
create or replace view v_takes as select stu_id, class_id from takes

95 뷰 생성 예) student 테이블에서 컴퓨터공학과 학생들 레코드만 추출하여 뷰 를 생성 (질의 73)
create or replace view cs_student as select s.stu_id, s.resident_id, s.name, s.year, s.address, s.dept_id from student s, department d where s.dept_id = d.dept_id and d.dept_name = '컴퓨터공학과'

96 뷰 사용 예) v_takes 뷰에 대해 select문을 실행 (질의 74) select * from v_takes
where stu_id = ‘ ’;

97 뷰 사용 뷰에 대해서 insert, update, delete문을 실행 예) v_takes 뷰에 대해 레코드를 삽입
v_takes 뷰에 포함되지 않은 grade 필드에는 널이 삽입 (질의 74) insert into v_takes values (' ', 'C101-01')

98 뷰 사용 형식 drop view <뷰이름>

99 뷰 삭제 읽기 전용 뷰 뷰를 생성할 때 insert, update, delete문과 같은 데이터 조작 언어의 사용을 불 가능하게 하려면, with read only 키워드를 추가