ORACLE SQL BASIC

1. ORACLE SQL BASIC

2. 목차 1. 테이블 테이블 구성. 기본키, 외래키 데이터타입 2. SQL DML, DDL, DCL 기본 SQL 문장 3. 함수 문자, 숫자, 날짜형함수, NULL함수, 변환함수 4. 조인 JOIN 이란. ANSI 조인 5. 그룹쿼리 GROUP BY HAVING절 6. 서브쿼리 WHERE 절 FROM 절 7. 계층형 쿼리

3. 테이블 • 테이블 : database의 가장 기본이 되는 데이터의 집합체 로우(행,ROW,가로축)와 컬럼(열,COLUMN,세로축)으로 구성되어 있는 2차원적인 평면 구조 • 테이블 생성 구성 요소 - TABLEName : 많은 테이블을 구분하기 위해서 지정 - Column Name : 한 테이블 안에 구체적으로 존재하게 될 데이터를 구분 짓는 이름 - Type(데이터 타입) : 데이터가 존재하기 위해서는 문자인지, 숫자인지 또는 날짜인지 구분 - Size(데이터 크기) : 데이터 타입이 결정되면 타입의 크기를 정의 문자와 숫자인 경우에만 크기 지정 - Null : 데이터가 없는 상태, Not Null은 Null을 허용 안하겠다는 의미이고 항상 데이터를 입력 • 기본적인 테이블 생성 패턴 CREATE TABLE 테이블명 ( 컬럼1 컬럼1데이터타입(size) not null, 컬럼2 컬럼2데이터타입(size) ) ;

4. 기본키, 외래키 • Primary key (기본키) : 테이블의 데이터들을 유일하게 식별하기 위한 무결성 제약조건 (UNIQUE+NOT NULL) • Foreign key (외래키) : 다른 테이블의 Primary key를 참조하는 칼럼으로 지정한 제약조건 • Foreign key 제약조건은 두 테이블 간의 관계를 선언함으로써 데이터무결성을 보장해주는 역할 • 외래키 테이블이 참조하는 기준 테이블의 컬럼은 반드시 primary key 제약조건 설정 • 기준 테이블: departments CREATE TABLE departments (department_id number(20) not null, CONSTRAINT pk명 PRIMARY KEY (department_id(컬럼명)); • 외래 키 테이블 : employees CREATE TABLE employees (employee_id number(20) not null, employee_name varchar2(10) not null, department_id number(20) not null CONSTRANIT fk명 FOREIGN KEY (department_id(컬럼명)) REFERENCES departments(department_id(참조컬럼명));

5. 참조무결성 Primary Key Foreign Key Primary Key NULL

6. 데이터 타입  데이터가 존재할 때 어떤 형태로 존재하는지 결정하는 테이블 구성요소

7. SQL 1. SQL( Structured Query Language) • 데이터에 ACCESS 하기 위해 데이터베이스와 통신하는 언어 • 데이터베이스에서 데이터를 조회, 입력, 수정, 삭제, 저장하는데 사용되는 질의 언어 • 어느 일정한 틀이나 패턴이 있는 구조화된(structured) 언어 • 패턴에 맞게 조건들을 선언한다는 의미에서 선언적(declarative) 언어

8. 2. SQL 역할 (DML, DDL, DCL) • DML(Data Manipulation Language) - 데이터의 추출, 저장, 삭제 등 데이터를 조작하는 역할 - SELECT, INSERT, UPDATE, DELETE - 데이터 조회 : SELECT - 데이터 저장 : INSERT(신규 데이터 입력) * 입력 시 테이블의 컬럼 타입과 입력시킬 데이터 값의 타입이 일치 SELECT last_name, first_name From employees Where hire_date >= ‘2008-07-14’; Select 컬럼1, 컬럼2 .. From 테이블1,테이블2.. Where 조건들; Insert into 테이블명1 [컬럼1, 컬럼2…] values (값1, 값2…);

9. 데이터 저장 : UPDATE(기존 데이터 수정) • 데이터 삭제 : DELETE • COMMIT : 데이터 변경작업(INSERT, UPDATE, DELETE)을 한뒤 COMMIT을 실행하면 변경된 데이터들이 데이터 파일에 반영 • ROLLBACK : COMMIT과 반대의 개념으로 변경된 데이터들을 변경 전 상태로 되돌리는 역할 단, COMMIT 후에는 ROLLBACK이 되지 않음 Update 테이블명 set 변경컬럼1 = 변경하고자 하는 값, 변경컬럼2 = 변경하고자 하는 값 Where 조건절; Delete [from] 테이블 Where 조건;

10. DDL (Data Definition Language) - 데이터가 저장되는 테이블이나 각종 객체들을 생성하는 역할 객체 생성 : CREATE 객체 삭제 : DROP 객체 변경 : ALTER Create table UNION ( Employee_id NUMBER(10) NOT NULL, Name VARCHAR2(10) NOT NULL, Email VARCHAR2(20) NOT NULL, Phone_number VARCHAR2(20), Hire_date DATE NOT NULL); Drop table 테이블명 Drop index 인덱스명 컬럼 추가, 컬럼명 변경, 컬럼 default 값 변경

11. 데이터 삭제 : TRUNCATE • TRUNCATE 과 DELETE, DROP 의 차이점 • TRUNCATE 데이터를 삭제했을 경우 ROLLBACK을 사용하여 데이터를 복구할 수 없음 TRUNCATE가 실행되면서 자동 COMMIT, 테이블에 있는 인덱스나 트리거에 영향을주지 않고 데이터만 삭제, 테이블의 구조 보존 • DROP 데이터를 삭제함은 물론 테이블 구조까지 소멸 • DELETE 테이블의 데이터를 삭제(rollback 가능(undo를 남김) ) . • DCL (Data Control Language) - 데이터베이스에 있는 데이터의 접근을 제어하는 언어 권한 허용 : GRANT 권한 제거 : REVOKE Truncate table 테이블명

12. SQL 문장의구성요소들 • 예약어 : SELECT, FROM ,WHERE 등 오라클 DBMS에서 특정 용도로 사용되는 단어들 • 연산자 : 데이터 항목들을 조작해서 결과를 산출해내는 역할을 수행하는 것 (+,-,*,/,>,<,>=,<=등) • 의사컬럼 : 테이블에 있는 일반적인 컬럼처럼 행동하기는 하지만 실제로 테이블에 저장되어 있지 않은 컬럼 ROWNUM – 각각의 로우들에 대한 순서값을 가리키는 의사컬럼 (employees 테이블에서 10건의 정보만 조회하고 싶을때) SELECT employee_id, last_name, rownum FROM employees WHERE ROWNUM <= 10 ROWID – 테이블에 저장된 각 로우들의 주소값을 가진 의사컬럼 <ROWNUM이나 ROWID와 같은 의사컬럼들은 SELECT나 WHERE 절에서는 사용가능 하지만 INSERT, UPDATE 문장에서 값으로는 사용될 수 없습니다.> • 함수 : 특정 연산을 수행하고 그 결과를 돌려주는 기능을 하는 객체

13. 기본 SELECT 문 SELECT : 조회해라 distinct : 중복되는 컬럼 값 중 하나씩만 추출해라. [ ] : 생략 가능하다는 의미. { } : * (모든 컬럼) , COLUMN 중 하나를 보여줘라. Alias : 선택한 * 나 column 에 별칭을 줄 수 있다. FROM : 데이터가 저장된 테이블로부터 가져와라. 테이블명 : 내가 원하는 데이터가 있는 테이블의 이름. Where : 테이블에 있는 데이터들을 걸러내는 필터역할 (조회되는 행을 제한) SELECT [distinct] { * , column alias } from 테이블 명 Where [컬럼명1] [연산자] [값1] [and | or] [컬럼명2] [연산자] [값2] …

14. Distinct • 중복되는 컬럼 값 중에서 하나씩만 추출 SELECT [distinct] { * , column alias } from 테이블 명

15. Order by SELECT [distinct] { * , column alias } from 테이블 명 order by [컬럼명1], [컬럼명2] [asc/desc]

16. Alias SELECT employee_id 사원번호, first_name as 이름, salary “월급”, from employees;

17. Where절 • WHERE 컬럼 = 값, 내용 - 컬럼의 실 데이터와 조건에 주는 값이나 내용이 연산자(=,>,<,>=,<=)와 일치하는 것만 조회 • 조건절을 구성하는 데이터 조건 범위 항목들 - 연산자, AND, OR, 범위조건, IN, EXIST, NOT, NULL SELECT employee_id 사원번호, first_name ||’ ‘|| last_name 이름 From employees Where [컬럼명1] [연산자] [값1] [and | or] [컬럼명2] [연산자] [값2] SELECT * FROM employees WHERE employee_id = 101 and salary < 10000;

18. 연산자 • 우선순위 ( ), *, /, +, - 순으로 우선순위가 결정 • 곱하기, 나누기는 더하기, 빼기 보다 우선순위가 빠름 • 괄호를 사용하면 우선순위가 임의로 바뀜 • 같은 우선 순위일 경우 좌측에서 우측 순으로 빠름

19. AND와 OR

20. 범위 조건

21. IN • 한 컬럼에 여러 개의 데이터를 동시에 비교해야 할 경우 • IN을 사용하지 않으면 OR을 사용 • 반드시 괄호와 함께 쓰이며, 괄호 안에는 비교할 값의 리스트나 서브쿼리가 올 수 있음 • IN을 사용하려는 컬럼만 SELECT문에서 사용

22. EXIST • IN 과 비슷한 용도로 사용되지만, SELECT절에 컬럼 내용은 아무 의미없고 조인내용이 중요 • EXIST는 특정 컬럼값이 존재하는지 여부 체크 • 괄호 안에는 오직 서브쿼리만 올 수 있음 • 서브쿼리의 WHERE절에서 비교할 기준테이블의 컬럼과 조인을 맺어야 하며, 서브쿼리의 결과로 반환되는 로우가 1개라도 있을 경우 EXIST 조건을 만족

23. NULL, NOT

24. SQL 함수 • 함수 : 조회/수정/삭제/입력시 자주 사용되고 데이터 값을 다양하게 변환하는데 사용

25. 단일행 함수

28. 그룹 함수(집계함수) • 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과값을 반환하는 함수

29. 조인 • 각각의 테이블들을 연관 지어서 원하는 데이터만을 가져올 수 있게 하는 것이 JOIN • 두 개 이상의 테이블을 사용할 경우 반드시 해주어야 하는 것이 JOIN • 조인은 두 개 이상의 테이블을 FROM절에서 선언한 후에 WHERE 절에서 각 테이블의 연관되는 컬럼들끼리 EQUAL(=)로써 정의합니다. • OUTER JOIN  기본적인 JOIN은 양쪽테이블의 컬럼 내용이 같을 경우에 만족하는데 기준이 되는 테이블을 중심으로 관계된 컬럼의 데이터가 없더라도 조회 (데이터 없는 쪽에(+))

30. Select employees.first_name, employees.last_name,employees.email, departments.department_name From employees, departments Where employees.department_id = departments.department_id Select emp.first_name, emp.last_name, emp.email, dep.department_id, dep.department_name From employees emp, department dep, jobs job, location loc Where emp.department_id = dep.department_id AND emp.job_id = job.job_id AND dep.location_id = loc.location_id AND loc.state_province = ‘캘리포니아’; • locations 테이블에서 state_porvince 값이 캘리포니아 로우를 찾는다. • 찾은 location_id 값과 같은 값을 가진 데이터를 departments 테이블에서 찾아 조인을 한다. • 2의 결과와 employees 테이블을 비교하여 (department_id 값이 같은 것) 조인한다. • 3의 결과와 jobs 테이블을 비교하여 조인하여 최종 쿼리결과를 얻는다.

32. 그룹함수(group by) • 데이터를 그룹으로 묶어 합계, 평균, 최대값, 최소값 등의 집계성 데이터를 나타내는 기능 select department_id, count(*) from employees group by department_id; • 부서명을 함께 조회 select a.department_id, b.department_name, count(*) from employees a, departments b where a.department_id=b.department_id group by a.department_id, b.department_name order by a.department_id;

33. Group by 절 1. select department_id, sum(salary), count(salary), avg(salary) from employees group by department_id; - select 절에서 컬럼 사용시 group by에 선언된 컬럼 외에는 모두 group 함수를 사용 2. select department_id, job_id, sum(salary), avg(salary) from employees group by department_id; - group by문은 group by 절에 선언되지 않은 컬럼을 사용했을 때 에러를 발생 (group by 절에 job_id 추가) 3. select job_id, to_char(sum(salary), ‘999,999’) total, to_char(avg(salary), ‘999,999’) avg from employees where department_id = 80 group by job_id order by job_id; -employees 테이블에서 department_id가 80인 부서를 job_id별로 그룹화하여 부서별로 급여합계와 평균을 조회

34. 그룹함수(having 절) • Having 절은 group by 절의 조건절 • 그룹함수를 where 절에 사용한 SQL 문장 select department_id, count(*) from employees where department_id is not null and count(*) <= 5 group by department_id order by department_id; Error : 그룹함수는 허가되지 않습니다. - 그룹함수는 where 절에 올 수 없음, 구하고자 하는 쿼리와 같이 조건에 그룹함수의 결과가 필요한 경우 HAVING 절에 명시 - SELECT문은 WHERE절을 통해 조회내용의 범위(조건)를 결정하고, GROUP BY절은 HAVING절을 통해 조회내용의 범위(조건)를 제한

35. 그룹함수(group by, having) SQL 문장의 실행순서를 보면 WHERE 조건이 먼저 처리되고 이 조건으로 걸러진 데이터들을 대상으로 grouping이 수행되고 그 이후에 HAVING 조건을 처리하기 때문에 대부분의 일반조건은 WHERE 절에 명시하는 것이 성능적인 측면에서 바람직하다. (NULL을 제외한 department_id 컬럼을 그룹화하여 5보다 작거나 같은 값을 count(*) 하여 오름차순으 로 정렬하여 보여주세요.) Select department_id, count(*) from employees Where department_id IS NOT NULL GROUP BY department_id HAVING count(*) <= 5 ORDER BY department_id;

36. 서브쿼리 하나의 SQL 문장 내부에 존재하는 또 다른 SELECT 문장 서브쿼리는 왜 사용하나? 전체 사원들 중 평균 급여보다 낮은 급여를 받는 사원들의 명단을 추출해야 한다면 어떻게 작성 할 것인가? 먼저, 평균월급이 얼마인지 구한 뒤에 이 값보다 월급이 작은 사원들의 명단을 추출하면 된다. SQL> select ROUND(AVG(salary)) from employees; ROUND(AVG(SALARY)) ------------------------- 6462 여기서 나온 결과값으로, SQL> SELECT employee_id, first_name, last_name from employees Where salary < 6462; Where 절에 평균값을 넣으면 해결되지만 where절에서는 집계함수를 사용할 수 없다. 두 문장을 합치기 위해서 평균값을 구하는 select 문을 where 절에 넣는 서브쿼리 SQL> select employee_id, first_name, last_name from employees Where salary < ( select ROUND(AVG(salary)) from employees);

37. 인라인뷰(from절의 서브쿼리) • FROM 절에 테이블 대신 select문을 사용하는 것, select 문을 일종의 테이블처럼 인식하는 것 • 평균 급여보다 높고 최대 급여보다 낮은 월급을 받는 사원 리스트를 조회하는 쿼리 • 평균 및 최대 급여액을 구하는 서브쿼리를 인라인뷰로 만든 쿼리 SELECT a.employee_id, a.first_name || ‘ ‘ || a.last_name names, a.salary, ROUND(b.avgs), b.maxs FROM employees a, ( SELECT AVG(salary) avgs, MAX(salary) maxs FROM employees ) b WHERE a.salary BETWEEN b.avgs AND b.maxs ORDER BY a.salary DESC; SELECT * FROM ( SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary ASC ) WHERE ROWNUM < 11 ;

38. 계층형 쿼리 1. Start with 조건1 : 루트노드를 식별한다. 조건 1을 만족하는 모든 row들은 루트노드가 된다. 2. Connect by 조건2 : 부모와 자식노드들 간의 관계를 명시하는 부분 조건 2에는 반드시 prior 연산 자를 포함시켜야 한다. 3. Prior : connect by 절에서 해당 컬럼의 부모 로우를 식별하는데 사용 parent_id 에도 item_id 가 존재하기에 prior를 item_id 앞에 위치한다. • SELECT LPAD( ‘ ‘, 2*(LEVEL-1)) || item_name item_names • FROM BOM • START WITH parent_id IS NULL • CONNECT BY PRIOR item_id = parent_id;

39. Select b.job_title “직위”, LPAD( ‘ ‘, 4*(LEVEL -1) || a.first_name || ‘ ‘ || a.last_name “성명”, c.department_name “부서”, d.city || ‘, ‘ || d.state_province “부서위치” From employees a, jobs b, departments c, locations d Where a.job_id = b.job_id and a.department_id = c.department_id and c.location_id = d.location_id Start with a.manager_id IS NULL Connect by a.manager_id = prior a.employee_id; 오라클이 계층형 쿼리를 처리하는 순서 1. 조인이 사용되었다면 가장 먼저 조인을 처리 2. Connect by 조건 처리 3. 나머지 조건 (where절에서 조인 이외의 조건) 처리

40. 감사합니다