오라클 계정생성

1. 관리자(sys)로 로그인

2. 계정생성

create user scott identified by tiger; (아이디: scott / 패스워드: tiger)

3. 테이블스페이스 지정

alter user scott default tablespace users;

4. 로그인 권한, 테이블 생성 권한 지정

grant connect, resource to scott;

grant create view to scott;

roll - 권한 여러개를 묶어서 처리하는 단위.

5. scott 으로 로그인

-- 계정확인

select * from dba_users;  <-뷰

연습용 예제인 INSA 테이블 생성하는 과정

1. scott 계정으로 로그인 후 작업할 것.

명령프롬프트 > sqlpuls scott

암호 입력

2. insa.sql 파일을 가지고 실행할 것.

sql>@c:\insa.sql

3. insa 테이블 생성 확인

select * from insa;

insa.sql

insa 테이블 관련 문제.txt

order by 구문

1. 정렬기준을 제시한다. 오름차순(Ascending), 내림차순 (Descending)

2. 

select 컬럼리스트

from 테이블명

where 조건식

group by 기준컬럼

having 조건식

order by 기준컬럼 [ASC|DESC], ....;

-- employees 테이블에서 first_name을 오름차순 정렬해서 출력.

select *

from employees

order by first_name asc; --asc는 생략가능

-- employees 테이블에서 salary를 내림차순 정렬해서 출력

select *

from employees

order by salary desc;

-- employees 테이블에서 Department_id를 기준으로 정렬하되,

   같은 부서인 경우 salary를 내림차순 정렬해서 출력.

문제) employees 테이블에서 입사년도 순으로 출력하되, 같은 년도면 급여(salary)가

      많은 순으로 출력.

문제) employees 테이블에서 입사월이 7월인 경우를 출력하되, 입사일이 빠른 순으로 출력.

group by 구문

1. 행들을 가지고 그룹을 만들때 사용. 동일한 데이터를 여러개 가지고 있는 컬럼을 대상으로 그룹형성할것.

그룹을 형성하게 되면 개인식별 정보는 출력되지 않는다. 집계함수(count, sum, avg...)의 결과만을 출력하게 된다.

2.

select 컬럼리스트

from 테이블

where 조건식

group by 기준컬럼

having 조건식

order by 기준컬럼;

-- employees 테이블에서 부서아이디(department_id)별로 그룹형성해서 출력 -> 인원수

select department_id , count(*)

from employees

group by department_id;

-- employees 테이블에서 부서아이디별로 인원수 출력하되, 인원수가 많은 순으로 출력.

select department_id , count(*) as 인원수

from employees

group by department_id

order by 인원수 desc;

-- employees 테이블에서 직무id(job_id)별로 그룹 형성해서 직원수 출력.

select job_id, count(*)

from employees

group by job_id;

-- employees 테이블에서 부서아이디별로 급여 평균을 출력하되, 급여 평균이 많은 순으로 출력.

select department_id, count(*) ,round(avg(salary))

from employees

group by department_id

order by round(avg(salary)) desc;

문제) employees 테이블에서 상급자아이디(manager_id)별 부하 직원수 출력. 부하직원이 많은 순으로 출력.

select manager_id, count(*)

from employees

group by manager_id

order by count(*) desc;

문제) employees 테이블에서 입사년도(hire_date)별 직원수 출력. 입사년도 순으로 출력

select to_char(hire_date, 'yyyy'), count(*)

from employees

group by to_char(hire_date, 'yyyy')

order by to_char(hire_date, 'yyyy');

having 구문

1. group by 구문에서의 결과(집계함수)를 가지고 조건을 지정하는 것.

2.

select 컬럼리스트

from 테이블명

where 조건식

group by 기준컬럼

having 조건식

order by 기준컬럼;

-- employees 테이블에서 직무id(job_id)별로 그룹 형성해서 직원수가 10명 이상인 경우만 출력.

select job_id, count(*)

from employees

group by job_id

having count(*) >= 10;

-- employees 테이블에서 부서아이디별로 인원수 출력하되, 인원수가 10명 이상인 경우만 출력. 많은 순으로 정렬.

select department_id , count(*) as 인원수

from employees

group by department_id

having count(*) > = 10

order by 인원수 desc;

-- employees 테이블에서 직무id(job_id)별로 그룹 형성해서 직원수(count)출력 직워수가 10명 이상인 경우만 출력,

job_id가 'CLERK'으로 끝나는 경우는 제외

SELECT job_id, count(*)

FROM employees

where job_id not like '%CLERK'

group by job_id

having count(*) >= 10;

문제) employees 테이블에서 입사년도(hire_date)별 직원들의 평균(avg) 급여(salary) 출력.

단, 평균 급여가 10000 이상인 경우만 출력

select to_char(hire_date, 'yyyy'), avg(salary)

FROM employees

group by to_char(hire_date, 'yyyy')

having avg(salary) >= 10000;

문제) employees 테이블에서 직무아이디(job_id)별 직원의 평균 급여를 출력.

단, 평균 급여가 10000 이상이고, 직무가 'MAN'으로 끝나는 경우만 출력

select job_id, avg(salary)

from employees

where job_id like '%MAN'

group by job_id

having avg(salary) >= 10000;

case ~ end 구문

1. 자바에서는 switch ~ case 구문과 비슷한 역할

2. decode 함수와 유사한 역할. decode의 조건식은 = 연산만 가능하지만,

case ~ end 구문은 모든 형태의 조건식 사용 가능.

3.

CASE

WHEN 조건 THEN 결과

WHEN 조건 THEN 결과

...

[ELSE 결과]

END

-- DECODE() 사용

SELECT first_name, last_name

, DECODE(department_id, 90, 'Excutive', 60, 'IT', 50, 'Shipping')

FROM employees;

-- CASE 사용

SELECT first_name, last_name,

case department_id when 90 then 'Excutive' when 60 then 'IT' when 50 then 'Shipping' end

FROM employees;

또는

SELECT first_name, last_name,

case

when department_id = 90 then 'Excutive'

when department_id = 60 then 'IT'

when department_id = 50 then 'Shipping'

end

FROM employees;

--문제) employees 테이블에서 salary가 

10000 미만이 경우는 C등급,

10000 이상, 20000 미만인 경우 B등급,

20000 이상인 경우 A등급으로 출력.

SELECT first_name, last_name,

case

when TRUNC(salary/10000) = 0 then 'C등급'

when TRUNC(salary/10000) = 1 then 'B등급'

when TRUNC(salary/10000) >= 2 then 'A등급'

end AS salaryGrade

FROM employees;

문제) EMPLOYEES 테이블에서 급여(salary)의 수준에 따라 다른 세금 비율(tax_pct) 적용해서 

세금비율, 세금 액수(tax), 실수령액(pay)를 출력.

case~end 구문 이용

20000 이상 > 4%

10000~19999 사이 > 2 %

10000 미만 > 0%

select first_name, last_name,

case

when trunc(salary/10000) >= 2 then '4%'

when trunc(salary/10000) = 1 then '2%'

when trunc(salary/10000) = 0  then '0%'

end as "tax_pct",

case

when trunc(salary/10000) >= 2 then trunc(salary * (4/100))

when trunc(salary/10000) = 1 then trunc(salary * (2/100))

when trunc(salary/10000) = 0  then salary

end 

as "tax",

case

when trunc(salary/10000) >= 2 then trunc(salary - (salary * (4/100)))

when trunc(salary/10000) = 1 then trunc(salary - (salary * (2/100)))

when trunc(salary/10000) = 0  then salary

end 

as "pay"

from employees

Sub query (하위 질의)

1. 쿼리 내부에 쿼리가 포함된 상태.

2. 

SELECT (서브쿼리) -> 서브 쿼리 결과가 출력의 일부가 된다.

FROM (서브쿼리) -> 서브 쿼리 결과가 데이터소스가 된다.(인라인뷰)

WHERE (서브쿼리) -> 서브 쿼리 결과가 조건의 일부가 된다.

[GROUP BY 기준컬럼]

[HAVING 조건식]

[ORDER BY 기준컬럼];

-- employees 테이블에서 급여(salary)가 제일 많은(max 함수) 직원.

-> 1단계. 제일 많은 급여액을 확인.

select max(salary) from employees; -- 24000

-> 2단계. 해당 급여액을 받는 직원 확인.

select * from employees where salary = 24000;

-> 3단계. 서브쿼리 사용

select *

from employees

where salary = (

select max(salary)

from employees

);

-- employees 테이블에서 평균 급여보다 적은 급여를 받는 직원.

select avg(salary) from employees

select *

from employees

where salary < (select avg(salary) from employees);

-- employees 테이블에서 부서 id가 100번인 직원 출력

select *

from employees

where department_id= 100; -- 결과가 여러개인 경우

select *

from employees

where employee_id in (select employees_id

from employees

where department_id= 100);

--> 서브쿼리 결과가 항목은 한개이지만 테이터는 여러개 반환되는 경우

--> in연산자로 처리함.

-- 두개의 테이블에서 서로 관련된 정보 출력하는 경우

select first_name, last_name, dapartment_id

from employees;

select department_id, department_name

from departments;

--> 'Finance'부서에 속하는 직원 정보 출력

select first_name, last_name, dapartment_id

from employees

where department_id = (select department_id from departments where department_name = 'Finance');

-- 직무명(jobs.job_title)이 'Finance Manager'인 경우 직원 정보(employees.*) 출력.

select first_name, last_name

from employees

where job_id = (select job_id from jobs where job_title = 'Finance Manager');

문제) jobs.job_title이 'Finance Manager' 인 직무의 최고 급여액(jobs.max_salary) 보다 많은 급여(employees.salary)를 받는 직원의 정보 출력.

select *

from employees

where salary > (select max_salary from jobs where job_title = 'Finance Manager');

문제) employees 테이블에서 first_name이 'David'이고, last_name이 'Austin'인 직원과 같은

부서아이디(department_id), 같은 급여(salary)를 받는 직원 정보 출력

SELECT *

  FROM employees

  WHERE (department_id, salary) IN

        (SELECT department_id, salary

            FROM employees

            WHERE first_name='David' AND last_name='Austin');

--서브 쿼리가 FROM 구문에 오는 경우

SELECT first_name, last_name

, salary

, tax

, salary-tax AS pay

FROM (SELECT first_name, last_name

, salary

, salary*0.04 AS tax

FROM employees);

--서브 쿼리가 컬럼 리스트에 오는 경우

SELECT first_name, last_name, department_id

,(SELECT department_name 

FROM departments

WHERE department_id=em.department_id)

FROM employees em; --> 별칭 사용시 AS 키워드 사용하지 말것. em은 별칭임.

rank() over() 함수

1. 그룹 내에서 위치를 반환

2.

select 컬럼리스트, rank() over(정렬 기준) as rank

from 테이블;

-- employees 테이블에서 salary가 높은 순으로 정렬해서 출력. 순위 부여.

select first_name, last_name, salary, 

rank() over(order by salary desc) as rank

from employees;

-- 5등까지 출력 (서브쿼리 사용)

select *

from (select first_name, last_name, salary, rank() over(order by salary desc) as rank

from employees)

where rank <= 5;

-- 그룹별 정렬해서 순위부여. partition by 구문 추가

SELECT first_name, last_name, salary, department_id

, RANK() OVER(PARTITION BY department_id 

ORDER BY salary DESC) AS rank

FROM employees;

-- employees 테이블에서 부서아이디(department_id)별로 salary가 가장 높은 wlrdnjs cnffur

--> rank 계산. 순위 1인 경우만 출력

select *

from (SELECT first_name, last_name, salary, department_id

, RANK() OVER(PARTITION BY department_id  ORDER BY salary DESC) AS rank

FROM employees)

where rank = 1;

문제) employees 테이블에서 급여(salary) 액수 상위 10%만 출력.

select *

from (select first_name, last_name, salary,

          rank () over(order by salary desc) as rank

from employees)

where rank <= trunc((select count(*)

from employees)*0.1);

avg() over() 함수

1. 그룹별 평균 계산 전용.

-- GROUP BY 구문 사용시. 부서 id별 평균 급여 출력

select department_id, round(avg(salary))

from employees

group by department_id

order by department_id;

--> 개별 정보는 출력할 수 없다.

-- AVG() OVER() 사용시.  부서 id별 평균 급여 출력

select first_name, last_name, department_id,

avg(salary) over(partition by department_id order by department_id) as 부서별 평균

from employees

--> 개별 정보와 그룹 정보가 같이 출력된다.

-- employees 테이블에서 개인별 부서id 평균(avg함수) 급여(salary)와 개인별 급여와의 차이 출력.

select first_name, last_name, department_id, salary, salary - avgperdepartment_id as "개인별 급여와의 차이"

from (select first_name, last_name, department_id, salary,

avg(salary) over(partition by department_id order by department_id) as avgperdepartment_id

from employees);

-- employees 테이블에서 전체 급여 평균 (AVG함수)과  개인별 급여와의 차이 출력. AVG() OVER() 함수 이용.

select first_name, last_name, salary, avg(salary) over()

from employees

SUM() OVER() 함수

1. 그룹별 합 계산 전용.

-- group by 사용시 부서 ID별 급여 합 출력.

select department_id, sum(salary)

from employees

gorup by department_id;

-> 개별정보 출력불가

-- sum() over() 사용시 부서 ID별 급여 합 출력.

select first_name, last_name, department_id, sum(salary) over(partition by department_id order by department_id)

from employees;

문제) employees 테이블에서 직원 개개인의 급여와 직원 전체 급여 합과의 비중 (%) 출력