다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

Presentation on theme: "다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL"— Presentation transcript:

1 다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL
서진수 저

2 1장. SELECT 문을 이용하여 원하는 데이터 가져오기

3 1. SELECT 문장 사용하기 - DESC 명령어로 컬럼을 확인하자! SCOTT>DESC emp ;
Name Null? Type EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) 2

4 1. SELECT 문장 사용하기 - SELECT : 데이터 조회하기 - 모든 컬럼 조회하기
문법 : SELECT [ 칼럼명 또는 표현식 ] FROM [테이블명 , 뷰 명] ; - 모든 컬럼 조회하기 SCOTT>SELECT * FROM emp ; SCOTT>SELECT * 2 FROM emp ; SCOTT>SELECT * 2 FR 3 OM emp; FR * ERROR at line 2: ORA-00923: FROM keyword not found where expected 키워드는 줄 바꾸면 안됨!! 3

5 1. SELECT 문장 사용하기 대소문자 구분을 하지 않아도 실행되지만 원래는 다른 문장이다!
2. 한 줄 또는 여러 줄에 걸쳐 작성해도 되며 마지막은 ; (세미콜론) 으로 끝맺어야만 한다. SQL 키워드는 분리해서는 안 된다. SQL 키워드라 함은 SELECT , FROM , WHERE 등과 같이 SQL 에서 사용하는 미리 정해놓은 단어를 말한다. 4

6 1. SELECT 문장 사용하기 - 화면에 보기 좋게 출력하는 팁 SCOTT> COL empno FOR 9999 ;
숫자일 경우 Empno라는 컬럼의 길이를 숫자 4자리까지 들어가게 설정 SCOTT> COL ename FOR a8 ; 문자일 경우 Ename의 컬럼 길이를 8바이트까지 들어가도록 설정 5

7 1. SELECT 문장 사용하기 - 화면에 보기 좋게 출력하는 팁 SCOTT> SET line 200 ;
한 줄에 최대 200글자(byte)까지 나오도록 설정 SCOTT> SET pagesize 50 ; 한 화면에 최대 50줄까지 나오도록 설정 5

8 1. SELECT 문장 사용하기 원하는 컬럼만 조회하는 방법 SCOTT>SELECT empno , ename
2 FROM emp ; EMPNO ENAME SMITH ALLEN WARD JONES MARTIN BLAKE (이하 생략) 14 rows selected. 원하는 컬럼만 조회하는 방법 6

9 1. SELECT 문장 사용하기 표현식을 사용하여 출력하기 (리터럴 상수,Literal )
원래 컬럼 이외에 출력하기를 원하는 내용 SCOTT>SELECT name , '교수님~배고파요~' 2 FROM professor ; NAME '교수님~배고파요 조인형 교수님~배고파요~ 박승곤 교수님~배고파요~ 주승재 교수님~배고파요~ (이하 생략) 7

10 1. SELECT 문장 사용하기 - 칼럼 별칭 사용하여 출력하기 - 1 SCOTT>SELECT studno , name
2 FROM student ; STUDNO NAME 서진수 서재수 이미경 김재수 (이하 생략) 칼럼 별칭 사용 하기 전 소문자가 대문자로 출력됨 8

11 1. SELECT 문장 사용하기 - 칼럼 별칭 사용하여 출력하기 - 2
SCOTT>SELECT studno "학번" , name AS "이름" , profno 지도교수번호 2 FROM student ; 학번 이름 지도교수번호 서진수 서재수 이미경 ( 이하 생략 ) 쌍따옴표는 공백, 특수문자, 대소문자의 구분이 필요한 경우 꼭 사용 9

12 1. SELECT 문장 사용하기 - Alias 연습문제 1
emp 테이블을 사용하여 empno 를 사원번호 , ename 을 사원명 , job 을 직업 으로 별명을 설정하여 출력하세요. - Alias 연습문제 2 dept 테이블을 사용하여 deptno 를 부서# , dname 를 부서명 , loc 를 위치 로 별명을 설정하여 출력하세요. 10

13 (5) DISTINCT - 중복된 값을 제거하고 출력하기
1. SELECT 문장 사용하기 (5) DISTINCT - 중복된 값을 제거하고 출력하기 11

14 1. SELECT 문장 사용하기 DISTINCT 사용 안 함 DISTINCT 사용 함 두 컬럼 DISTINCT 사용 함 1 2
3 12

15 1. SELECT 문장 사용하기 (6) 연결(합성) 연산자 (Concatenation)로 칼럼을 붙여서 출력하기 13

16 1. SELECT 문장 사용하기 SCOTT>SELECT name || ' 님은 ' || position || ' 입니다'
2 FROM professor ; NAME||'님은'||POSITION||'입니다' 조인형 님은 정교수 입니다 박승곤 님은 조교수 입니다 송도권 님은 전임강사 입니다 양선희 님은 전임강사 입니다 김영조 님은 조교수 입니다 주승재 님은 정교수 입니다 김도형 님은 정교수 입니다 나한열 님은 조교수 입니다 김현정 님은 전임강사 입니다 심슨 님은 정교수 입니다 ( 이하 생략 ) 리터럴 문자를 사용한 경우 14

17 1. SELECT 문장 사용하기 연결 연산자 문제 1 : 학생 테이블(student)을 사용하여 모든 학생들이
‘서진수 의 키는 180 cm, 몸무게는 55 kg 입니다’ 와 같은 형식으로 출력되도록 리터럴 문자를 추가하고 , 칼럼이름은 “학생의 키와 몸무게”라는 별명으로 출력해 보세요. 15

18 1. SELECT 문장 사용하기 연결 연산자 문제 2: 홍길동 (교수) , 홍길동 ‘교수’ 이렇게 나오도록 출력해보세요 16

19 1. SELECT 문장 사용하기 (7) 산술 연산자 사용하기 : +, -, *, / 17

20 1. SELECT 문장 사용하기 - 산술 연산자 사용시 우선순위 주의 할 것! 18

21 (8) WHERE 절을 활용하여 원하는 조건만 조회하기
1. SELECT 문장 사용하기 (8) WHERE 절을 활용하여 원하는 조건만 조회하기 SQL>SELECT [ Column or Expression ] 2 FROM [ Table or View ] 3 WHERE 원하는 조건 ; 19

22 1. SELECT 문장 사용하기 - 문자열 조회할 때 주의 사항 WHERE 절의 문자는 대소문자 구분 합니다!
홑따옴표로 묶으세요! 20

23 1. SELECT 문장 사용하기 - 날짜 조회할 때 주의 사항 - 홑따옴표로 묶으세요 - 대소문자 구분 없습니다. 21

24 1. SELECT 문장 사용하기 (9) 다양한 연산자를 활용하는 방법 22 연산자 종류 설 명 = 같은 조건을 검색
설 명 = 같은 조건을 검색 != , <> 같지 않은 조건을 검색 > 큰 조건을 검색 >= 크거나 같은 조건을 검색 < 작은 조건을 검색 <= 작거나 같은 조건을 검색 BETWEEN a AND b A 와 B사이에 있는 범위 값을 모두 검색 IN(a,b,c) A 이거나 B 이거나 C 인 조건을 검색 Like 특정 패턴을 가지고 있는 조건을 검색 Is Null / Is Not Null Null 값을 검색 / Null 이 아닌 값을 검색 A AND B A 조건과 B 조건을 모두 만족하는 값만 검색 A OR B A 조건이나 B 조건 중 한가지라도 만족하는 값을 검색 NOT A A 가 아닌 모든 조건을 검색 22

25 1. SELECT 문장 사용하기 ① 비교 연산자를 사용하여 Student 테이블에서 키가(height) 180 cm 보다
크거나 같은 사람을 출력하세요 23

26 1. SELECT 문장 사용하기 ② Between 연산자를 사용하여 Student 테이블에서 몸무게가 (weight)
60kg ~ 80kg 인 사람의 이름과 체중을 출력하세요. BETWEEN 연산자는 주의 사항이 있습니다. 1. 두 개의 값 중에 작은 값이 먼저 오고 큰 값이 나중에 와야 합니다. 2. 두 개의 값을 다 포함하여 출력됩니다 24

27 1. SELECT 문장 사용하기 - BETWEEN 연산자는 아래처럼 사용 가능합니다. 25

28 1. SELECT 문장 사용하기 ③ IN 연산자를 사용하여 Student 테이블에서 101 번 학과 학생과 201 번
학과 학생들을 모두 출력하세요 이 SQL 의 WHERE 절 부분을 “ WHERE deptno1 = 101 OR deptno1 = 201 “ 로 사용할 수 도 있지만 쿼리가 너무 길어져서 간편하게 IN 연산자를 사용하는 것입니다. 26

29 1. SELECT 문장 사용하기 ④ Like 연산자를 사용하여 student 테이블에서 성이 “김” 씨인 사람을 조회하세요
% : 글자수 무관, 모든 글자 가능 _ : 글자수 1자 , 모든 글자 가능 27

30 1. SELECT 문장 사용하기 ⑤ IS NULL / IS NOT NULL 연산자를 활용하기 NULL 은 정해지지 않아서
값을 모른다는 의미임. 0 과는 다름 !! 이 부분이 NULL 임 28

31 1. SELECT 문장 사용하기 29

32 1. SELECT 문장 사용하기 ⑥ 검색조건이 두 개 이상일 경우 조회하기
student 테이블을 사용하여 4학년 중에서 키가 170 cm 이상인 사람의 이름과 학년과 키를 조회하세요. 두 가지 조건을 모두 만족하는 결과를 검색하실 때는 AND 연산자를 사용하여 조건을 적으시면 됩니다. 30

33 1. SELECT 문장 사용하기 2. student 테이블을 사용하여 1학년 이거나 또는 몸무게가 80 kg 이상인
학생들의 이름과 키와 학년과 몸무게를 출력하세요. 두 가지 조건 중 한가지만 만족하는 행을 검색하고 싶으면 OR 연산자를 사용하면 됩니다. 31

34 1. SELECT 문장 사용하기 3. Student 테이블을 사용하여 2학년 중에서 키가 180 cm 보다 크면서
몸무게가 70 kg 보다 큰 학생들의 이름과 학년과 키와 몸무게를 출력하세요. 조건이 여러 개이고 모두 만족하는 하는 경우 AND 를 여러 번 쓰면 됩니다 32

35 1. SELECT 문장 사용하기 4. Student 테이블을 사용하여 2학년 학생 중에서 키가 180 cm 보다 크거나 또는 몸무게가 70 kg 보다 큰 학생들의 이름과 학년과 키와 몸무게를 출력하세요. AND 와 OR 조건이 동시에 나올 경우에는 우선순위를 아주 조심하셔야 합니다 !!! 33

36 1. SELECT 문장 사용하기 - AND 와 OR 연산자의 우선 순위 조절 실패 사례 34

37 1. SELECT 문장 사용하기 ** 퀴즈 1 ** Professor 테이블에서 교수들의 이름을 조회하여 성 부분에 ‘ㅈ’ 이 포함된 사람의 명단을 아래와 같이 출력하세요. 1번 화면이 professor 테이블에서 이름을 조회한 화면입니다. ORDER BY 라는 구문은 정렬을 해서 보여달라는 뜻인데 뒤에 살펴봅니다. 2번 화면이 성 부분에 ‘ㅈ’ 이 들어간 사람만 출력한 화면입니다. IN (‘전민’,’조인형’,’주승재’) 이렇게 하진 않으실 거죠?? 이런 거였으면 퀴즈도 안 냈을 겁니다~^^ 능력을 보여주세요~~ 35

38 1. SELECT 문장 사용하기 Where name between ‘자%’ and ‘차%’

39 1. SELECT 문장 사용하기 (10) ORDER BY 절을 사용하여 출력 결과 정렬하기
- 한 글: 가 , 나 , 다 , 라 ….. - 영 어: A , B , C , D…… - 숫 자: 1 , 2 , 3 , 4…… - 날 짜: 예전 날짜부터 시작해서 최근 날짜로 정렬됨. ORDER BY 절을 사용하며 ASC 는 오름차순 , DESC 는 내림차순임. ASC 방식이 기본 정렬 방식임. 36

40 1. SELECT 문장 사용하기 ① student 테이블을 사용하여 1 학년 학생의 이름과 키를 출력하세요.
단 키가 작은 순서대로 출력하세요. 37

41 1. SELECT 문장 사용하기 ② Student 테이블을 사용하여 1학년 학생의 이름과 키와 몸무게를 출력하세요. 단 키는 작은 사람부터 출력하시고 몸무게는 많은 사람부터 출력하세요. 38

42 1. SELECT 문장 사용하기 ③ Student 테이블을 사용하여 1학년 학생의 이름과 생일과 키와 몸무게를
출력하세요. 단 생일이 빠른 사람 순서대로 정렬하세요. 39

43 1. SELECT 문장 사용하기 ④ 칼럼의 별명을 사용한 정렬
Student 테이블을 사용하여 1학년 학생의 이름과 키를 출력하세요. 단 이름을 오름차순으로 정렬하세요. 40

44 1. SELECT 문장 사용하기 (11) 집합 연산자 ( Set Operator) 집합 1 집합 2 결 과 41

45 1. SELECT 문장 사용하기 - 집합 연산자 종류 42 연산자 종류 내 용 UNION
내 용 UNION 두 집합을 더해서 결과를 출력. 중복 값 제거하고 정렬함 UNION ALL 두 집합을 더해서 결과를 출력. 중복 값 제거 안하고 정렬 안 함 INTERSECT 두 집합의 교집합 결과를 출력함. 정렬함 MINUS 두 집합의 차집합 결과를 출력함. 정렬함. 쿼리의 순서 중요함 42

46 ① UNION / UNION ALL ( 두 집합을 더합니다 )
1. SELECT 문장 사용하기 ① UNION / UNION ALL ( 두 집합을 더합니다 ) Student 테이블과 professor 테이블을 참조하여 101 번 학과에 소속되어 있는 학생과 교수들의 학번(교수님은 교수번호) , 이름, 학과번호를 출력하세요. 43

47 1. SELECT 문장 사용하기 Student 테이블에서 101번 학과와 201번 학과를 전공하는 학생들의 이름을 출력하세요.
정렬함/중복 값 제거 후 출력 정렬 안 함/ 중복값 모두 출력 44

48 1. SELECT 문장 사용하기 ② INTERSECT 연산자 사용하기 – 교집합 찾기
-stduent 테이블을 사용하여 101 번 학과와 201 번 학과를 복수전공하는 사람의 이름을 출력하세요. 45

49 1. SELECT 문장 사용하기 ③ MINUS 연산자 사용하기 – 큰 집합에서 작은 집합 빼기
professor 테이블에서 전체 직원의 급여를 20 % 인상하기 위한 직원 명단을 출력하려 합니다. 단 직급이 전임강사인 사람들은 명단에서 제외하세요. 46

50 위 SELECT 절은 2개의 칼럼인데 아래 SELECT 절은 1개의 칼럼으로 칼럼의 개수가 다를 경우 에러가 발생합니다.
** 집합 연산자 사용시 주의사항 1. 칼럼의 개수가 다를 경우 에러 발생함 위 SELECT 절은 2개의 칼럼인데 아래 SELECT 절은 1개의 칼럼으로 칼럼의 개수가 다를 경우 에러가 발생합니다. 47

51 1. SELECT 문장 사용하기 ** 집합 연산자 사용시 주의사항 2. 비교되는 칼럼끼리의 데이터 타입이 다를 경우
** 집합 연산자 사용시 주의사항 2. 비교되는 칼럼끼리의 데이터 타입이 다를 경우 위 SELECT 문장의 데이터 타입과 아래 SELECT 문장의 데이터 타입이 서로 다를 경우 에러가 발생합니다. 48

52 1. SELECT 문장 사용하기 수고 하셨습니다~ 다음 장에서는 2장. SQL 단일 행 함수를 살펴보겠습니다. 49