SQL 실습 2014 FALL 데이터베이스 2014/10/04.

Presentation on theme: "SQL 실습 2014 FALL 데이터베이스 2014/10/04."— Presentation transcript:

1 SQL 실습 2014 FALL 데이터베이스 2014/10/04

2 오라클 설치 오라클은 오라클 홈페이지 www.oracle.com 에서 다운로드
오라클 11g release 2 윈도우(32-bit) 버전을 사용하며 운영체제는 Windows 7 Professional K를 기준으로 함

3 시스템 요구사항 메모리 1GB 이상 가상메모리 2GB 이상 하드디스크 여유공간 4.76GB 이상 CPU
800MHz 이상(Intel x86 계열, AMD 계열) 운영체제(윈도우즈 계열) Windows 2000 SP2 이상 Windows Server 2003 Windows Vista Windows 7 Professional K Windows Server 2008 단, WIndows NT는 지원하지 않음

4 운영체제 정보 확인

5 가상 메모리 확인 및 설정

6 오라클 설치 시작 오라클 11g 릴리즈 2 버전의 설치 파일은 두 개의 ZIP 파일로 구성
다운로드 받은 후 압축을 해제하면 database 라는 폴더가 만들어지고 그 폴더 에 들어 있는 setup.exe 파일을 실행시키면 설치가 시작됨

7 초기화면

8 보안 갱신 구성 화면

9 설치 옵션 선택 화면

10 시스템 클래스 선택 화면

11 설치 위치 및 기본 구성 선택

12 필요조건 검사 수행 화면

13 설치 옵션 요약 화면

14 제품 설치 시작 - 오라클 설치 화면

15 데이터베이스 설정 화면 오라클 인스턴스란? 오라클이 데이터베이스를 관리하는데 필요한 각종 정보들과 구체적인 관리 작업을 수행하는 여러 프로세스들

16 데이터베이스 생성 완료 화면 비밀번호를 변경할 필요가 있는 경우에만 실행

17 비밀번호 관리 화면 계정 종류 scott sys, system 자동적으로 생성되는 테스 트용 사용자 계정
기본 비밀번호는 tiger sys, system 관리자 계정 기본 비밀번호는 앞의 일반 구성화면에서의 설정과 같 음

18 설치 완료 화면

19 설치된 오라클 소프트웨어 확인

20 사용자 계정의 잠금 해제 방법 실행창에서 cmd 명령어를 입력해서 명령어 입력창을 실행

21 사용자 계정의 잠금 해제 방법 sqlplus /nolog 명령으로 SQL*Plus 를 실행

22 사용자 계정의 잠금 해제 방법 connect sys as sysdba 명령으로 시스템 관리자인 sysdba 계정으로 접속

23 사용자 계정의 잠금 해제 방법 scott의 비밀번호를 변경하려면 alter user scott account unlock; 명 령을 실행

24 SQL*Plus를 이용한 오라클 테스트 SQL*Plus 실행방법
윈도우즈의 명령어 입력창(cmd)과 비슷한 모양을 하고 있는 프로그램 SQL 명령 실행 SQL*Plus 자체 명령어 실행 PL/SQL 명령 실행 실행방법 시작 버튼 -> 모든 프로그램 -> Oracle -> 응용프로그램개발 -> SQLPlus

25 사용자 계정 scott 으로 접속하기

26 사용자 DB Table 보기

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

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

29 테이블 생성 형식 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) )

30 기본키, 외래키 설정 테이블을 생성할 기본키 역할을 하는 필드를 지정 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) )

31 기본키, 외래키 설정

32 테이블 생성(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) )

33 테이블 생성(student table)

34 테이블 생성 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) )

35 테이블 생성 professor 테이블

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

37 테이블 생성 course 테이블

38 테이블 생성 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) )

39 테이블 생성 class 테이블

40 테이블 생성 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) )

41 테이블 생성 takes 테이블

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

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

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

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

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

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

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

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

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

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

52 학사 데이터베이스의 데이터 삽입 예 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)

53 학사 데이터베이스의 데이터 삽입 예 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)

54 학사 데이터베이스의 데이터 삽입 예 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)

55 학사 데이터베이스의 데이터 삽입 예 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+')

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

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

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

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

60 레코드 삽입 시 주의사항 department 테이블에 dept_id 필드의 값이 ‘920’인 레코드가 먼저 삽입

61 레코드 수정 시 주의사항 외래키로 사용되는 필드의 값을 수정할 때
외래키가 참조하는 테이블에 삽입되어 있는 값으로만 수정이 가능 예) department 테이블에 dept_id 필드의 값이 ‘920’인 레코드만 삽입되어 있는 상황

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

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

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

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

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

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

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

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

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

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

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

73 레코드의 순서 지정(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

74 레코드의 순서 지정

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

76 재명명 연산 테이블이나 필드에 대한 재명명 실제 테이블 이름이 수정되거나 필드 이름이 바뀌는 것이 아님 질의를 처리하는 과정 동안만 일시적으로 사용 표현이 단순화하거나, 동일 이름이 존재할 경우에 사용 예) 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

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

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

79 필드의 재명명

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

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

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

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

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

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

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

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

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

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

90 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'

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

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

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

94 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

95 GROUP BY

96 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

97 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

98 GROUP BY 예) 학사 데이터베이스에서 학과별 교수 숫자와 평균 재직연수, 최대 재 직연수를 출력 (질의 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

99 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 <집계함수 조건>

100 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

101 HAVING 예) 직원 숫자가 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

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

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

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

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

106 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호')

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

108 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)

109 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)

110 EXISTS, NOT EXISTS

111 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)