[하위질의(SubQuery)]

: 하나의 쿼리에 다른 쿼리가 포함되는 구조, ()로처리

 

1) 단일행 서브쿼리(단일행반환) :  > , < , >=, <= , <>

     Main Query

         Sub  Query      ----->   1 개결과

 

2) 다중행 서브쿼리(여러행반환) : in, any, all

     Main Query

          Sub Query      ----->   여러개의 결과

     

       < any : 비교대상중 최대값보다 작음

       > any : 비교대상중 최소값보다 큼  

                  (ex. 과장직급의 급여를 받는 사원조회)

       =  any : in연산자와 동일

       <  all   : 비교대상중 최소값보다 작음

       >  all   : 비교대상중 최대값보다 큼

                  (ex. 모든과장들의 직급보다 급여가 많은 사원조회)

 

 

3) 상관쿼리(correlated  subquery)  

  : 메인쿼리에서 고려된 각 후보 행들에 대해 서브쿼리가 다른 결과를 반환해야하는경우

    (메인쿼리에서 처리되는 각 행들의 값에 따라 응답이 달라져야하는 경우)에 유용하다

      exists,  not exists : 존재 여부에 따라 true,false을 반환

--------------------------------------------------------------

        

 

 

ex1) Neena사원의 부서명을 알아내시오

select department_id from employees where first_name='Neena';

select department_name from departments where department_id=90;

 

select department_name from departments

where department_id = (select department_id

from employees

where first_name='Neena');

 

ex2) Neena사원의 부서에서 Neena사원보다 급여를 많이 받는 사원들을 구하시오

                          (90)          (17000)

 

last_name    department_id    salary

---------------------------------------

select last_name, department_id, salary

from employees

where department_id = (select department_id

from employees

where first_name='Neena')

and

salary > (select salary from employees where first_name='Neena');

 

[문제1] 최저급여를 받는 사원들의 이름과 급여를 구하시오

 

[문제2] 부서별 급여 합계 중 최대급여를 받는 부서의 부서명과 급여합계를 구하시오(group by)

DEPARTMENT_NAME    SUM(SALARY)

-------------------------    -----------

Sales                     304500

 

ex3) Austin과 같은 부서이면서 같은 급여를 받는 사원들의

      이름, 부서명, 급여를 구하시오(60부서, 4800달러)

select last_name, department_name, salary

from employees

left join departments using(department_id)

where department_id = (select department_id

from employees

where last_name='Austin')

and

salary = (select salary from employees where last_name='Austin');

 

ex4) 'ST_MAN' 직급보다 급여가 많은 'IT_PROG' 직급 직원들을 조회하시오

last_name   job_id   salary

-----------------------------

select last_name, job_id, salary

from employees

where job_id = 'IT_PROG'

and

salary >any(select salary from employees where job_id='ST_MAN');

 

 

[문제3] 'IT_PROG' 직급 중 가장 많이 받는 사원의 급여보다 더 많은 급여를 받는 'FI_ACCOUNT' 또는 'SA_REP' 직급 직원들을 조회하시오

      조건1) 급여 순으로 내림차순 정렬하시오

      조건2) 급여는 세자리 마다 콤마(,) 찍고 화폐단위 '원’을 붙이시오

      조건3) 타이틀은 사원명, 업무ID, 급여로 표시하시오

 

사원명    업무ID    급여

--------------------------

             :

ex5) 'IT_PROG'와 같은 급여를 받는 사원들의 이름,업무ID,급여를 전부 구하시오

select last_name, job_id, salary

from employees

where salary in(select salary from employees where job_id='IT_PROG');

 

ex6) 전체직원에 대한 관리자와 직원을 구분하는 표시를 하시오(in, not in이용)

사원번호      이름       구분

-------------------------------------

100          King      관리자

 

방법1(in 연산자)

select employee_id as 사원번호, last_name as 이름,

case

    when employee_id in(select manager_id from employees)

        then '관리자'

        else '직원'

    end as 구분

from employees

order by 3,1;

 

★ 방법2(union, in, not in 연산자)

select employee_id as 사원번호, last_name as 이름, '관리자' as 구분

from employees

where employee_id in(select manager_id from employees)

union

select employee_id 사원번호, last_name 이름, '직원' as 구분

from employees

where employee_id not in(select manager_id from employees where manager_id is not null)

order by 3,1;

★ 방법3(상관쿼리이용)

-- 메인쿼리 한행을 읽고 해당값을 서브쿼리에서 참조하여 서브쿼리결과에 존재하면 true를 반환

 

select employee_id as 사원번호, last_name as 이름, '관리자' as 구분

from employees e

where exists(select null from employees where e.employee_id=manager_id)

union

select employee_id as 사원번호, last_name as 이름, '직원' as 구분

from employees e

where not exists(select null from employees where e.employee_id=manager_id)

order by 3,1;

 

[문제4] 자기 업무id(job_id)의 평균급여를 받는 직원들을 조회하시오

      조건1) 평균급여는 100단위 이하 절삭

급여는 세자리마다 콤마, $표시

      조건2) 사원이름(last_name), 업무id(job_id), 직무(job_title), 급여(salary) 로 표시하시오

      조건3) 급여순으로 오름차순 정렬하시오

 

ex7) group by rollup : a,b별 집계(Subtotal 구하기)

부서별, 직무ID별 급여평균구하기(동일부서에 대한 직무별 평균급여)

조건1) 반올림해서 소수 2째자리까지 구하시오

조건2) 제목은 Job_title, Department_name, Avg_sal로 표시하시오

select department_name, job_title, round(avg(salary), 2) as "Avg_sal"

from employees

join departments using(department_id)

join jobs using(job_id)

group by rollup(department_name, job_title);

 

ex8) group by cube :  a별 집계 또는 b별 집계

부서별, 직무ID별 급여평균구하기(부서를 기준으로 나타내는 평균급여)   

select department_name, job_title, round(avg(salary), 2) as "Avg_sal"

from employees

join departments using(department_id)

join jobs using(job_id)

group by cube(department_name, job_title);

 

ex9) group by grouping sets

직무별 평균급여와 전체사원의 평균급여를 함께 구하시오

select job_title, round(avg(salary), 2) as "Avg_sal"

from employees

join departments using(department_id)

join jobs using(job_id)

group by grouping sets((job_title),()); ← () All Rows의 역활

 

 

 

'JAVA 개발 공부 정보' 카테고리의 다른 글

SQL-수업4  (0) 2018.03.23
SQL-수업자료3  (0) 2018.03.20
SQL 연습  (0) 2018.03.15
Chapter04-스프링JDBC  (0) 2018.03.15
Oracle-SQL  (0) 2018.03.14

[SET operator]-집합연산자

두개 이상의 쿼리결과를 하나로 결합시키는 연산자

 

1. UNION      : 양쪽쿼리를 모두 포함(중복 결과는 1번만 포함) 합집합

2. UNION ALL  : 양쪽쿼리를 모두 포함(중복 결과도 모두 포함)

3. INTERSECT  : 양쪽쿼리 결과에 모두 포함되는 행만 표현 교집합

4. MINUS      : 쿼리1결과에 포함되고 쿼리2결과에는 포함되지 않는 행만 표현 차집합

 

오라클의 집합연산자(SET operator) UNION, INTERSECT, MINUSorder by 한다

→ 컬럼이 많으면 order by 하므로 느려진다. 수가 작은 튜플로 가공 후 사용하는게 좋다

→ UNION ALL 는 order by 하지 않고 무조건 합해준다

   order by를 하려면 두번째 쿼리에 작성해야 한다

 

 

create table employees_role as select * from employees where 1=0; ← 테이블 구조만 복사

 

select * from employees_role;

 

insert into employees_role values(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',

'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',

'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(101, 'Nee', 'Ko', 'NKOCHHAR', '515.123.4568',

'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

 

insert into employees_role values(200, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568',

'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(200, 'Nee', 'Kochhar', 'NKOCHHAR', '515.123.4568',

'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);

insert into employees_role values(300, 'GilDong', 'Conan', 'CONAN', '010-123-4567',

'2009-03-01', 'IT_PROG', 23000.00, NULL, 100, 90);

 

ex1) union

employee_id, last_name 같을 경우 중복제거 하시오  110 레코드

select employee_id, last_name from employees

union

select employee_id, last_name from employees_role;

 

ex2) union all

employee_id, last_name 같을경우 중복을 허용 하시오 113 레코드

select employee_id, last_name from employees

union all

select employee_id, last_name from employees_role;

 

select salary  from employees where department_id=10

union all

select salary  from employees where department_id=30 order by 1;

 

ex3) minus

employees_role 중복되는 레코드는 제거하고 employees에만 있는 사원명단을 구하시오 (, employee_id, last_name 표시) 106 레코드

 

select employee_id, last_name from employees

minus

select employee_id, last_name from employees_role;

 

ex4) intersect

     employees employees_role에서 중복되는 레코드의 사원명단을 구하시오

     (, employee_id, last_name 표시)   1 레코드

select employee_id, last_name from employees

intersect

select employee_id, last_name from employees_role;

 

[문제1] employees와 employees_role에서 레코드의 사원명단을 구하시오

조건1) 사원이름, 업무ID, 부서ID을 표시하시오

조건2) employees 에서는 부서ID가 10인사원만

       employees_role에서는 업무ID가 IT_PROG만 검색

조건3) 중복되는 레코드는 제거

 

 

ex5) SET operator과 IN operator관계

job_title이   'Stock Manager' 또는  'Programmer'인 사원들의 사원명과 job_title을 표시하시오

 

last_name       job_title

--------------------------------

Kaufling        StockManager

Hunlod         Programmer

           :

 

 

 

방법1(join, in연산자 이용)

select last_name, job_title

from employees

join jobs using(job_id)

where job_title in('Stock Manager','Programmer');

 

방법2(join, union 이용)

select last_name, job_title

from employees

join jobs using(job_id)

where job_title='Stock Manager'

union

select last_name, job_title

from employees

join jobs using(job_id)

where job_title='Programmer'

order by 2;

 

ex9) 컬럼명이 다른경우의 SET operator

쿼리1과 쿼리2의 select 목록은 반드시동일(컬럼갯수,데이터타입)해야 하므로 이를 위해 Dummy Column을 사용할수 있다

 

select last_name, employee_id, hire_date

from employees

where department_id=20

union

select department_name, department_id, NULL

from departments

where department_id=20;

 

 

'JAVA 개발 공부 정보' 카테고리의 다른 글

SQL-수업5  (1) 2018.03.24
SQL-수업자료3  (0) 2018.03.20
SQL 연습  (0) 2018.03.15
Chapter04-스프링JDBC  (0) 2018.03.15
Oracle-SQL  (0) 2018.03.14

※ select (해석순서 1 → 2 → 3 → 4 → 5)

 

select [distinct] [컬럼1,컬럼2,.....][as 별명][ || 연산자][*]  --- 6

from 테이블명     --- 1

[where 조건절]    --- 2

[group by컬럼명]  --- 3

[having 조건절]   --- 4

[order by 컬럼명 asc|desc ]  --- 5

 

group by : 그룹함수(max,min,sum,avg,count..)와 같이 사용

having : 묶어놓은 그룹의 조건절

------------------------------------------------------------------

ex1) 사원테이블에서 급여의 평균을 구하시오

     조건)소수이하는 절삭, 세자리마다 콤마(,)

 

     사원급여평균

     ------------

            6,461

select to_char(trunc(avg(salary),0), '99,999') as 사원급여평균 from employees;

 

ex2)부서별 급여평균을 구하시오

    조건1) 소수이하는 반올림

    조건2) 세자리마다콤마, 화페단위 \를 표시

    조건3)  부서코드        평균급여

             ---------------------------

                 10               \8,600

    조건4) 부서별로 오름차순정렬하시오

    조건5) 평균급여가 5000이상인 부서만 표시하시오

 

select department_id as 부서코드,

        to_char(round(avg(salary),0),'L99,999,999') as 평균급여

from employees

group by department_id    

having avg(salary)>=5000

order by department_id asc;

 

ex3) 부서별 급여평균을 구해서 사원명(last_name),부서별 급여평균을 출력하시오 - X

select last_name, avg(salary)

from employees

group by department_id;

last_name 때문에 error

group by절에 없는것을 select에 조회하면 error

 

[문제1] 업무ID별 급여의 합계를 구해서 업무ID, 급여합계를 출력하시오

 

ex4) 비효율적인 having절

10과 20 부서에서 최대급여를 받는사람의 최대급여를 구하여 정렬하시오

 

department_id     max_salary

-----------------------------

 10                     4400

 20                    13000

 

select department_id, max(salary) as max_salary

from employees

group by department_id

having department_id in(10,20)

order by department_id;

전체 부서에 대해 그룹을 잡아서 최대급여를 구한후에 부서가 10 과 20인것을 추려내기 때문인것

 

 

select department_id, max(salary) as max_salary

from employees

where department_id in(10,20)

group by department_id

order by department_id;

부서번호가 10과 20인 것만 골라내서 그룹잡기 때문에 속도가 좀 빠르다

 

 

조인(join)

EMPLOYEES           DEPARTMENTS            JOIN

                                               

            +                  =              

                                               

                                               

 

 

※ 종류

1. Inner join : 같은것 끼리만 연결

2. Outer join : 한쪽을 기준(모두포함)해서 연결

                left  join : 왼쪽컬럼 모두포함

                right join : 오른쪽컬러 모두포함

3. full join : 왼쪽,오른쪽 모두 포함

4. self join : 자기자신 테이블과 연결

5. cross join : 모든 경우의 수로 연결

6. non equijoin : 범위에 속하는지 여부를 확인

7. n개 테이블 조인 : 여러개의 테이블 조인

 

※ 방법

1. 오라클 구문전용

2. Ansi 표준구문

 

ex5) inner join : 같은것끼리만 조인

사원테이블과 부서테이블에서 부서가 같을 경우 사원번호, 부서번호, 부서이름을 출력하시오

 

방법1(오라클전용구문)

select  employee_id,

        employees.department_id,

        department_name

from employees, departments

where employees.department_id = departments.department_id;  

 

방법2(오라클전용구문)

select  e.employee_id,

        e.department_id,

        d.department_name

from employees e, departments d

where e.department_id = d.department_id;

 

방법3(Ansi표준)

select employee_id, department_id, department_name

from employees

join departments using(department_id);

 

[문제2] 부서테이블(DEPARTMENTS d) 위치테이블(LOCATIONS l) 연결하여          부서가 위치한 도시를 알아내시오

 

department_id     city

----------------------------------

10                Seattle

 

 

 

 

 

ex6) outer join(left) : 왼쪽 테이블은 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 107레코드

 

방법1(오라클전용구문)

select e.last_name, d.department_id, d.department_name

from employees e, departments d

where e.department_id=d.department_id(+);

 

방법2(Ansi표준)

select last_name, department_id, department_name

from employees

left join departments using(department_id);

 

ex7) outer join(right) : 오른쪽 테이블은 모두포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 122레코드

 

방법1(오라클전용구문)

select e.last_name, d.department_id, d.department_name

from employees e, departments d

where e.department_id(+)=d.department_id;

 

방법2(Ansi표준)

select last_name, department_id, department_name

from employees

right join departments using(department_id);

 

ex8) full join(right) : 왼쪽,오른쪽 테이블을 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 123레코드

 

--방법1(오라클전용구문) : 없다

--방법2(Ansi표준)

select last_name, department_id, department_name

from employees

full join departments using(department_id);

 

ex9) inner join : 두개의 컬럼이 일치 하는 경우

        부서ID와 매니저ID가  같은 사원을 연결 하시오

       (관련테이블 : departments, employees)  : 32 레코드

 

        last_name     department_id   manager_id

        ------------------------------------------

 

방법1(오라클전용구문)

select e.last_name, d.department_id, d.manager_id

from employees e, departments d

where e.department_id=d.department_id and e.manager_id=d.manager_id;

 

방법2(Ansi표준)

select last_name, department_id, manager_id

from employees

inner join departments using(department_id, manager_id);

 

ex10) 내용은 같은데 컬럼명이 다른 경우에 조인으로 연결하기

       departments(location_id) , locations2(loc_id)

 

테이블 복사

create table locations2 as select * from locations;

select * from locations2;

alter table locations2 rename column location_id to loc_id;

 

방법1(오라클전용구문)

select d.department_id, l.city

from departments d, locations2 l

where d.location_id=l.loc_id;

 

방법2(Ansi표준)

select department_id, city

from departments

join locations2 on(location_id=loc_id);

 

방법3(Ansi표준)

select department_id, city

from departments  d

join locations2  l  on(d.location_id=l.loc_id);

 

ex11) self 조인 : 자기자신의 테이블과 조인하는경우 사원과 관리자를 연결하시오

 

사원번호   사원이름      관리자

----------------------------------

   101      Kochhar      King  

 

    EMPLOYEES                       EMPLOYEES

employee_id, last_name(사원이름)     last_name(관리자)

조건  employee_id = manager_id

 

select employee_id,manager_id, last_name from employees;  → e

select employee_id, last_name from employees; m

 

방법1

select  e.employee_id as 사원번호,

e.last_name as 사원이름,

m.last_name as 관리자

from employees e, employees m

where m.employee_id=e.manager_id;

 

방법2

select  e.employee_id as 사원번호,

e.last_name as 사원이름,

m.last_name as 관리자

from employees e

join employees m on(m.employee_id=e.manager_id); 

 

ex12) cross join : 모든행에 대해 가능한 모든조합을 생성하는 조인

select * from countries, locations; 575레코드

 

select * from countries cross join locations;

 

ex13) Non Equijoin (넌 이큐조인)

컬럼값이 같은 경우가 아닌 범위에 속하는지 여부를 확인 할 때

on ( 컬럼명 between 컬럼명1 and 컬럼명2)

 

create table salgrade(

salvel varchar2(2),

lowst number,

highst number); 

 

insert into salgrade values('A', 20000, 29999);

insert into salgrade values('B', 10000, 19999);

insert into salgrade values('C', 0, 9999);

commit;

 

select * from salgrade;

 

select last_name, salary, salvel

from employees

join salgrade on(salary between lowst and highst)

order by salary desc;

 

ex14) n(여러)개의 테이블은 조인

업무ID 같은 사원들의 사원이름, 업무내용, 부서이름을 출력하시오

(EMPLOYEES, JOBS, DEPARTMENTS 테이블을 조인)

 

<분석>

EMPLOYEES        JOBS      DEPARTMENTS

------------------------------------------------------------

department_id      job_id      department_id

job_id

<출력>

last_name      job_title       department_name

--------------------------------------------------------------

select last_name, job_title, department_name

from employees

join departments using(department_id)

join jobs using(job_id);

 

[문제3] 위치ID, 부서ID를 연결해서 사원이름,도시,부서이름을 출력하시오

        (관련테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS)

조건1 : 사원이름, 도시, 부서이름로 제목을 표시하시오   

조건2 : Seattle 또는 Oxford 에서 근무하는 사원

조건3 : 도시순으로 오름차순정렬하시오

 

사원이름               부서이름

---------------------------------------------

Hall            Oxford       Sales

 

[문제4] 부서ID, 위치ID, 국가ID를 연결해서 다음과 같이 완성하시오

        (관련테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS, COUNTRIES)

조건1 : 사원번호,사원이름,부서이름,도시,도시주소,나라명로 제목을 표시하시오

조건2 : 도시주소에  Ch 또는 Sh 또는 Rd가 포함되어 있는 데이터만 표시하시오

조건3 : 나라명, 도시별로 오름차순 정렬하시오

조건4 : 모든 사원을 포함한다

 

※ select (해석순서 1 → 2 → 3 → 4 → 5)

 

select [distinct] [컬럼1,컬럼2,.....][as 별명][ || 연산자][*]  --- 6

from 테이블명     --- 1

[where 조건절]    --- 2

[group by컬럼명]  --- 3

[having 조건절]   --- 4

[order by 컬럼명 asc|desc ]  --- 5

 

group by : 그룹함수(max,min,sum,avg,count..)와 같이 사용

having : 묶어놓은 그룹의 조건절

------------------------------------------------------------------

ex1) 사원테이블에서 급여의 평균을 구하시오

     조건)소수이하는 절삭, 세자리마다 콤마(,)

 

     사원급여평균

     ------------

            6,461

select to_char(trunc(avg(salary),0), '99,999') as 사원급여평균 from employees;

 

ex2)부서별 급여평균을 구하시오

    조건1) 소수이하는 반올림

    조건2) 세자리마다콤마, 화페단위 \를 표시

    조건3)  부서코드        평균급여

             ---------------------------

                 10               \8,600

    조건4) 부서별로 오름차순정렬하시오

    조건5) 평균급여가 5000이상인 부서만 표시하시오

 

select department_id as 부서코드,

        to_char(round(avg(salary),0),'L99,999,999') as 평균급여

from employees

group by department_id    

having avg(salary)>=5000

order by department_id asc;

 

ex3) 부서별 급여평균을 구해서 사원명(last_name),부서별 급여평균을 출력하시오 - X

select last_name, avg(salary)

from employees

group by department_id;

last_name 때문에 error

group by절에 없는것을 select에 조회하면 error

 

[문제1] 업무ID별 급여의 합계를 구해서 업무ID, 급여합계를 출력하시오

 

ex4) 비효율적인 having절

10과 20 부서에서 최대급여를 받는사람의 최대급여를 구하여 정렬하시오

 

department_id     max_salary

-----------------------------

 10                     4400

 20                    13000

 

select department_id, max(salary) as max_salary

from employees

group by department_id

having department_id in(10,20)

order by department_id;

전체 부서에 대해 그룹을 잡아서 최대급여를 구한후에 부서가 10 과 20인것을 추려내기 때문인것

 

 

select department_id, max(salary) as max_salary

from employees

where department_id in(10,20)

group by department_id

order by department_id;

부서번호가 10과 20인 것만 골라내서 그룹잡기 때문에 속도가 좀 빠르다

 

 

조인(join)

EMPLOYEES           DEPARTMENTS            JOIN

                                               

            +                  =              

                                               

                                               

 

 

※ 종류

1. Inner join : 같은것 끼리만 연결

2. Outer join : 한쪽을 기준(모두포함)해서 연결

                left  join : 왼쪽컬럼 모두포함

                right join : 오른쪽컬러 모두포함

3. full join : 왼쪽,오른쪽 모두 포함

4. self join : 자기자신 테이블과 연결

5. cross join : 모든 경우의 수로 연결

6. non equijoin : 범위에 속하는지 여부를 확인

7. n개 테이블 조인 : 여러개의 테이블 조인

 

※ 방법

1. 오라클 구문전용

2. Ansi 표준구문

 

ex5) inner join : 같은것끼리만 조인

사원테이블과 부서테이블에서 부서가 같을 경우 사원번호, 부서번호, 부서이름을 출력하시오

 

방법1(오라클전용구문)

select  employee_id,

        employees.department_id,

        department_name

from employees, departments

where employees.department_id = departments.department_id;  

 

방법2(오라클전용구문)

select  e.employee_id,

        e.department_id,

        d.department_name

from employees e, departments d

where e.department_id = d.department_id;

 

방법3(Ansi표준)

select employee_id, department_id, department_name

from employees

join departments using(department_id);

 

[문제2] 부서테이블(DEPARTMENTS d) 위치테이블(LOCATIONS l) 연결하여          부서가 위치한 도시를 알아내시오

 

department_id     city

----------------------------------

10                Seattle

 

 

 

 

 

ex6) outer join(left) : 왼쪽 테이블은 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 107레코드

 

방법1(오라클전용구문)

select e.last_name, d.department_id, d.department_name

from employees e, departments d

where e.department_id=d.department_id(+);

 

방법2(Ansi표준)

select last_name, department_id, department_name

from employees

left join departments using(department_id);

 

ex7) outer join(right) : 오른쪽 테이블은 모두포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 122레코드

 

방법1(오라클전용구문)

select e.last_name, d.department_id, d.department_name

from employees e, departments d

where e.department_id(+)=d.department_id;

 

방법2(Ansi표준)

select last_name, department_id, department_name

from employees

right join departments using(department_id);

 

ex8) full join(right) : 왼쪽,오른쪽 테이블을 모두 포함하여 조인

사원테이블과 부서테이블에서 부서번호가 같은 사원을 조인하시오 123레코드

 

--방법1(오라클전용구문) : 없다

--방법2(Ansi표준)

select last_name, department_id, department_name

from employees

full join departments using(department_id);

 

ex9) inner join : 두개의 컬럼이 일치 하는 경우

        부서ID와 매니저ID가  같은 사원을 연결 하시오

       (관련테이블 : departments, employees)  : 32 레코드

 

        last_name     department_id   manager_id

        ------------------------------------------

 

방법1(오라클전용구문)

select e.last_name, d.department_id, d.manager_id

from employees e, departments d

where e.department_id=d.department_id and e.manager_id=d.manager_id;

 

방법2(Ansi표준)

select last_name, department_id, manager_id

from employees

inner join departments using(department_id, manager_id);

 

ex10) 내용은 같은데 컬럼명이 다른 경우에 조인으로 연결하기

       departments(location_id) , locations2(loc_id)

 

테이블 복사

create table locations2 as select * from locations;

select * from locations2;

alter table locations2 rename column location_id to loc_id;

 

방법1(오라클전용구문)

select d.department_id, l.city

from departments d, locations2 l

where d.location_id=l.loc_id;

 

방법2(Ansi표준)

select department_id, city

from departments

join locations2 on(location_id=loc_id);

 

방법3(Ansi표준)

select department_id, city

from departments  d

join locations2  l  on(d.location_id=l.loc_id);

 

ex11) self 조인 : 자기자신의 테이블과 조인하는경우 사원과 관리자를 연결하시오

 

사원번호   사원이름      관리자

----------------------------------

   101      Kochhar      King  

 

    EMPLOYEES                       EMPLOYEES

employee_id, last_name(사원이름)     last_name(관리자)

조건  employee_id = manager_id

 

select employee_id,manager_id, last_name from employees;  → e

select employee_id, last_name from employees; m

 

방법1

select  e.employee_id as 사원번호,

e.last_name as 사원이름,

m.last_name as 관리자

from employees e, employees m

where m.employee_id=e.manager_id;

 

방법2

select  e.employee_id as 사원번호,

e.last_name as 사원이름,

m.last_name as 관리자

from employees e

join employees m on(m.employee_id=e.manager_id); 

 

ex12) cross join : 모든행에 대해 가능한 모든조합을 생성하는 조인

select * from countries, locations; 575레코드

 

select * from countries cross join locations;

 

ex13) Non Equijoin (넌 이큐조인)

컬럼값이 같은 경우가 아닌 범위에 속하는지 여부를 확인 할 때

on ( 컬럼명 between 컬럼명1 and 컬럼명2)

 

create table salgrade(

salvel varchar2(2),

lowst number,

highst number); 

 

insert into salgrade values('A', 20000, 29999);

insert into salgrade values('B', 10000, 19999);

insert into salgrade values('C', 0, 9999);

commit;

 

select * from salgrade;

 

select last_name, salary, salvel

from employees

join salgrade on(salary between lowst and highst)

order by salary desc;

 

ex14) n(여러)개의 테이블은 조인

업무ID 같은 사원들의 사원이름, 업무내용, 부서이름을 출력하시오

(EMPLOYEES, JOBS, DEPARTMENTS 테이블을 조인)

 

<분석>

EMPLOYEES        JOBS      DEPARTMENTS

------------------------------------------------------------

department_id      job_id      department_id

job_id

<출력>

last_name      job_title       department_name

--------------------------------------------------------------

select last_name, job_title, department_name

from employees

join departments using(department_id)

join jobs using(job_id);

 

[문제3] 위치ID, 부서ID를 연결해서 사원이름,도시,부서이름을 출력하시오

        (관련테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS)

조건1 : 사원이름, 도시, 부서이름로 제목을 표시하시오   

조건2 : Seattle 또는 Oxford 에서 근무하는 사원

조건3 : 도시순으로 오름차순정렬하시오

 

사원이름               부서이름

---------------------------------------------

Hall            Oxford       Sales

 

[문제4] 부서ID, 위치ID, 국가ID를 연결해서 다음과 같이 완성하시오

        (관련테이블 : EMPLOYEES, LOCATIONS2, DEPARTMENTS, COUNTRIES)

조건1 : 사원번호,사원이름,부서이름,도시,도시주소,나라명로 제목을 표시하시오

조건2 : 도시주소에  Ch 또는 Sh 또는 Rd가 포함되어 있는 데이터만 표시하시오

조건3 : 나라명, 도시별로 오름차순 정렬하시오

조건4 : 모든 사원을 포함한다

 

 

'JAVA 개발 공부 정보' 카테고리의 다른 글

SQL-수업5  (1) 2018.03.24
SQL-수업4  (0) 2018.03.23
SQL 연습  (0) 2018.03.15
Chapter04-스프링JDBC  (0) 2018.03.15
Oracle-SQL  (0) 2018.03.14

oracle hr/hr로 연습하기

[문제4] 2005년 이전에 고용된 사원을 찾으시오

last_name              hire_date

-------------------------

King              17-6 -2003                    

De Haan          13-1 -2001                    

Greenberg        17-8 -2002

select last_name, to_char(hire_date,'dd-mon-yyyy')as hire_date from employees where hire_date<'2005-01-01';

 

==============

[문제5] 커미션(commission_pct)을 받지 않은 사원의 인원수를 구하시오

ex19) employees테이블에서 없는부서포함해서,총 부서의 수를 구하시오(답 : 12개)

      (nvl사용)

 

select nvl(commission_pct,0) from employees;
select count((nvl(commission_pct,0))) from employees where commission_pct is null;
select count(*) from employees where commission_pct is null;

 

 

=================

[문제6] 급여가 10000미만이면 초급, 20000미만이면 중급 외면 고급을 출력하시오 (case 사용)

      조건1) 컬럼명은  '구분'으로 하시오

      조건2) 제목은 사원번호, 사원명, 구 

      조건3) 구분(오름차순)으로 정렬하고, 같으면 사원명(오름차순)으로 정렬하시오

 

ex21) rank함수 : 전체값을 대상으로 순위를 구함

      rank(표현식) within group(order by 표현식)

      rank() over(쿼리파티션)  → 전체순위를 표시

 

select employee_id as 사원번호,
       last_name as 사원명,
       case
       when salary<10000 then '초급'
       when salary<20000 then '중급'
       else '고급'
       end "구 분"
       from employees
       order by  3,2;

 

===================

 

[문제7] 사원테이블에서 사원번호, 이름, 급여, 커미션, 연봉을 출력하시오

        조건1) 연봉은 $ 표시와 세자리마다 콤마를 사용하시오

        조건2) 연봉 = 급여 * 12 + (급여 * 12 * 커미션)

        조건3) 커미션을 받지 않는 사원도 포함해서 출력하시오

select employee_id as 사원번호,
       last_name as 사원명,
       salary as 급여,
       commission_pct as 커미션,
       to_char(salary * 12 + (salary * 12 * nvl(commission_pct,0)),'$9,999,999') as 연봉
from employees;

 

================

 

[문제8] 매니저가 없는 사원의 매니저id를, 1000번으로 표시

        조건1) 제목은 사원번호,이름,매니저ID

        조건2) 모든 사원을 표시하시오      

        사원번호     이름      매니저ID

         ---------------------------------

         100           King        1000

 

select employee_id as 사원번호,
       last_name as 이름,
       nvl(manager_id,1000) as 매니저 ID
from employees;

 

 

'JAVA 개발 공부 정보' 카테고리의 다른 글

SQL-수업4  (0) 2018.03.23
SQL-수업자료3  (0) 2018.03.20
Chapter04-스프링JDBC  (0) 2018.03.15
Oracle-SQL  (0) 2018.03.14
JAVA-myBatis  (0) 2018.03.14

★ 스프링과 JDBC
: 스프링은 JDBC를 비롯하여 ORM프레임워크(myBatis, 하이버네이트, JPA(Java Persistence API))를 지원한다
: 스프링의 목표는 인터페이스에 의한 개발인데 DAO는 데이터베이스에서 데이터를 읽거나 쓰는 수단을 제공하기 위해 존재하며, 반드시 인터페이스를 통해 외부에 제공돼야 한다.
서비스 객체는 인터페이스를 통해서 DAO에 접근한다
서비스 객체를 특정 데이터 액세스 구현체에 결합시키지 않음으로써 테스트를 용이하게 한다
DAO인터페이스는 DAO구현과 서비스 객체 사이에서 느슨한 결합이 유지될 수 있게 한다

서비스 객체         → DAO 인터페이스
UserInsertAction         UserDAO               
                           ↑
                      DAO 구현 객체
                        UserDAOImpl

: 스프링은 데이터베이스 연동을 위한 템플릿 클래스를 제공함으로써
Connection, Statement(PreparedStatement), ResultSet등을 생성하고 처리한 다음 close(반환)하는 JDBC의 중복된 코드를 줄일 수 있다
: JDBC는 무조건 SQLException의 예외만 발생하므로 정확히 Connection에서 발생했는지 아니면 Statement에서 발생했는지 따져봐야한다. JdbcTemplate클래스는 SQLException이 발생하면 스프링이 제공하는 예외 클래스중 알맞는 것으로 변환해서 발생한다.
스프링은 JDBC보다 다양한 예외 계층을 제공하고 어떤 퍼시스턴스 솔루션과도 연관성을 갖지 않는다
스프링을 사용하면 퍼시스턴스 기술과 관계없이 일관성있게 예외를 발생시킬 수 있다
: 스프링의 DataAccessException는 비검사형 예외(try~catch블럭을 사용하지 않아도 컴파일이 되는 예외)로서 반드시 잡아서 처리할 필요가 없다
: 스프링이 제공하는 DataSource를 설정하는 3가지 방법
1. 커넥션풀을 이용한 DataSource 설정
스프링이 직접적으로 커넥션풀을 제공하진 않지만 DBCP(Jakarta Commons Database Connection Pool) API와 같은 커넥션 풀 라이브러리를 이용
DBCP에는 풀링 기능을 제공하는 다양한 데이터 소스가 포함되 있지만 BasicDataSource가 가장 많이 사용된다
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
     <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
     <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />
     <property name="username" value="java8" />
     <property name="password" value="itbank" />
     <property name="initialSize" value="5" /> → 해당 풀이 시작될 때 생성할 커넥션 수, 0은 무제한
     <property name="maxActive" value="10" /> → 해당 풀에서 동시에 제공할 수 있는 최대 커넥션 수, 0은 무제한
</bean>

2. JNDI을 이용한 DataSource 설정
WebLogic이나 JBoss와 같은 JEE 어플리케이션 서버를 사용할 경우
톰캣이난 Resin등의 웹 컨테이너를 사용할 경우
<jee:jndi-lookup id="dataSource" jndi-name="oracle" resource-ref="true" />
<jee:jndi-lookup>태그를 이용 - 스프링2.0부터 지원
jndi-name 프로퍼티는 JDNI에 있는 리소스의 이름을 지정한다
jndi-name프로퍼티만 지정된 경우에는 데이터소스는 지정된 jndi-name을 그대로 이용해서 검색한다
자바 애플리케이션 서버에서 가동되는 경우에는  resource-ref=true이면 jndi-name값의 앞에 "java:comp/env"가 붙은 이름을 사용한다
<jee:jndi-lookup>태그를 사용하지 않고 JndiObjectFactoryBean클래스를 이용할 수 있다

3. DriverManager을 이용한 DataSource 설정
: 스프링에 설정할 수 있는 가장 단순한 데이터 소스는 JDBC드라이버를 통해 정의된 것이다
- DriverManagerDataSource
애플리케이션이 커넥션을 요청할 때마다 새로운 커넥션을 반환한다
DBCP의 BasicDataSource와는 달리 커넥션은 풀링되지 않는다
멀티스레드에서도 동작은 하지만 커넥션이 필요할 때마다 새로 커넥션을 생성하므로 심각한 성능 저하를 유발한다.
- SingleConnectionDataSource
커넥션을 요청하면 항상 동일한 커넥션을 반환한다
커넥션 풀링 기능은 없지만 오직 하나의 커넥션만을 풀링하는 데이터소스
사용할 수 있는 커넥션이 오직 하나뿐이라서 멀티스레드 애플리케이션에서는 제대로 동작하지 않을 것이다
: 스프링의 JDBC 프레임워크는 자원관리와 예외 처리를 대신 해주므로 JDBC코드가 훨씬 간결해진다
스프링은 단순 반복적인 데이터 액세스 코드를 템플릿 클래스 뒤로 추상화해 숨긴다
1. JdbcTemplate
스프링의 가장 기본적인 JDBC 템플릿
색인된 파라미터(indexed parameter)기반의 쿼리를 통해 데이터베이스를 쉽게 액세스하는 기능을 제공
2. NamedParameterJdbcTemplate
SQL과 값들을 색인된 파라미터 대신 명명된 파라미터(named parameter)로 바인딩하여 쿼리를 수행할 수 있게 해준다


jdbcTemplate을 DAO에 와이어링
public class UserDAOImpl implements UserDAO {
     private JdbcTemplate jdbcTemplate;
     public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
          this.jdbcTemplate = jdbcTemplate;
     }
}
<bean id="userDao" class="user.dao.UserDAOImpl">
     <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
     <constructor-arg ref="dataSource" />
</bean>
---------------------------------------------------
SimpleJdbcTemplate 빈을 각 DAO 클래스의 프로퍼티에 연결해 줘야 했다
그런데 여러개의 DAO클래스를 작성하는 경우라면 많은 중복코드가 발생한다
모든 DAO 객체에 대해 SimpleJdbcTemplate 프로퍼티를 갖는 공통부모클래스를 작성한다

★ 스프링이 제공하는 기반클래스
JdbcTemplate - JdbcDaoSupport
NamedParameterJdbcTemplate - NamedParameterJdbcDaoSupport
SimpleJdbcTemplate - SimpleJdbcDaoSupport

SimpleJdbcDaoSupport 에서는 getSimpleJdbcTemplate()메소드를 제공하므로
SimpleJdbcTemplate을 편리하게 사용할 수 있다
-----------------------------------------------------
public class jdbcMemberDAO extends SimpleJdbcDaoSupport  implements MemberDAO {
 public void insertArticle(MemberDTO dto){
       getSimpleJdbcTemplate().update(...)
 }
}

<bean id="memberDao" class="com.dao.jdbcMemberDAO">
     <property name="dataSource" ref="dataSource" />
</bean>
-----------------------------------------------------
SimpleJdbcDaoSupport로 부터 jdbcMemberDAO를 상속받은 dataSource프로퍼티에 데이터소스를 직접 와이어링할 수 있다
jdbcMemberDAO에 그 자신의 dataSource프로퍼티가 설정되면 jdbcMemberDAO는 자신이 사용할 SimpleJdbcTemplate 인스턴스를 내부적으로 생성해 둔다.
따라서 JdbcTemplate빈을 스프링에 명시적으로 선언할 필요가 사라진다
<bean id="memberDao" class="com.dao.jdbcMemberDAO">
     <property name="dataSource" ref="dataSource" />
</bean>
-----------------------------------------------------
Project : Chapter04
          Chapter04_SpringMaven
Package  : user.main
Class    : HelloSpring.java - main()
Package   : user.action
Interface : UserAction.java - public void execute();
Class     : UserInsertAction.java
            UserSelectAction.java
            UserUpdateAction.java
            UserDeleteAction.java
Package   : user.bean
Class     : UserDTO.java
Package   : user.dao
Interface : UserDAO.java
Class     : UserDAOImpl.java
 
src
     applicationContext.xml
-----------------------------------------------------
★ JAR  3개 추가
commons-collections-4.1.jar
commons-dbcp-1.4.jar
commons-pool-1.6.jar
spring-jdbc-4.3.10.RELEASE.jar
spring-tx-4.3.10.RELEASE.jar
ojdbc6.jar
-----------------------------------------------------
★ 테이블 작성
create table usertable (
     name   varchar2(30),
     id   varchar2(30) primary key,
     pwd   varchar2(30)
);
-----------------------------------------------------

'JAVA 개발 공부 정보' 카테고리의 다른 글

SQL-수업자료3  (0) 2018.03.20
SQL 연습  (0) 2018.03.15
Oracle-SQL  (0) 2018.03.14
JAVA-myBatis  (0) 2018.03.14
mvnrepository  (0) 2018.03.14

[연산자]

=  : 같다

!=,  ^=,  <> : 같지않다

>=, <=, >, < : 크거나같다, 작거나같다, 크다, 작다

and, or, between and, in, like, is null/ is not null

 

select

select  [distinct] [컬럼1, 컬럼2.......][*]

from 테이블명

[where 조건절]

[order by 컬럼명 asc|desc ]

 

order by : 정렬

          asc  - 오름차순(생략가능)

          desc - 내림차순

컬럼명 : 숫자로도 가능

 

------------------------------------------------------------

ex1) 사원명,부서ID,입사일을 부서별로 내림차순 정렬하시오

select last_name, department_id, hire_date

from employees

order by 2 desc;

 

ex2) 사원명, 부서ID, 입사일을  부서별로 내림차순 정렬하시오

       같은부서가 있을때는  입사일순으로 정렬하시오

select last_name, department_id, hire_date

from employees

order by 2 desc, 3 asc;

 

 

 

 

[문제1] 사원들의 연봉을 구한후 연봉순으로 내림차순정렬하시오

select first_name as "이  름", salary*12 as "연 봉" from employees

order by 2 desc;

이 름     연 봉    

King      28800

 

 

 [단일행 함수]

1. 숫자함수 : mod, round, trunc, ceil

2. 문자함수 : lower, upper, length, substr, ltrim, rtrim, trim

3. 날짜함수 : sysdate, add_month, month_between

4. 변환함수

   (1) 암시적(implict)변환:자동

       VARCHAR2 또는 CHAR    ------>     NUMBER

       VARCHAR2 또는 CHAR    ------>     DATE

       NUMBER                ------>     VARCHAR2

       DATE                  ------>     VARCHAR2  

 

    (2) 명시적(explict)변환:강제

                    TO_NUMBER           TO_DATE

                 <------            ------>

     NUMBER      CHARACTER           DATE

                 ----->             <------

                 TO_CHAR              TO_CHAR

 

-날짜형식-

YYYY : 네자리연도(숫자)         (ex.  2005) 

YEAR : 연도(문자)

MM : 두자리 값으로 나타낸 달    (ex.   01, 08, 12)

MONTH : 달 전체이름             (ex.   January)

MON : 세자리 약어로 나타낸 달   (ex.   Jan) 

DY : 세자리 약어로 나타낸 요일  (ex. Mon)

DAY : 요일전체                  (ex. Monday)

DD : 숫자로 나타낸 달의 일      (ex. 31, 01)

HH, HH24(24시간제)

MI

SS

-숫자형식-

9 : 숫자를 표시

0 : 0을 강제로 표시

$ : 부동$기호를 표시

L : 부동 지역통화기호 표시

.  : 소수점출력

,  : 천단위 구분자 출력

 

5. 그룹(집합)함수 : avg, sum, max, min, count

6. 기타함수 : nvl, dcode, case

 

 

ex1) 이름을 소문자로 바꾼후 검색

'Higgins'사원의 사원번호, 이름, 부서번호를 검색하시오

select employee_id, last_name, department_id

from employees

where lower(last_name)='higgins';

 

ex2) 10을 3으로 나눈 나머지구하시오(mod)

select mod(10,3) from dual; → 가상의 테이블

 

ex3) 35765.357을 반올림(round)

위치가 n일 때 n이 양수이면 (n+1)에서 반올림이 되고

n이 음수이면 n의 위치에서 반올림 된다

select round(35765.357, 2) from dual;   //35765.36

select round(35765.357, 0) from dual;   //35765

select round(35765.357, -3) from dual;  //36000

 

ex4) 35765.357을 내림(trunc)

위치가 n일 때 n이 양수이면 (n+1)에서 반올림이 되고

n이 음수이면 n의 위치에서 반올림 된다

select trunc(35765.357, 2) from dual;   //35765.35

select trunc(35765.357, 0) from dual;   //35765

select trunc(35765.357, -3) from dual;   //35000

 

ex5) concat('문자열1','문자열2) : 문자열의 결합(문자열1+문자열2)

select concat('Hello', '  World') from dual;

 

ex6) length('문자열')  : 문자열의 길이

     lengthb('문자열') : 문자열의 길이

 

create table text (

str1  char(20),

str2  varchar2(20));

 

char : 고정문자길이

varchar2 : 가변문자길이

 

insert into text(str1,str2) values('angel','angel');

insert into text(str1,str2) values('사천사','사천사');

commit;

 

 

select  lengthb(str1), lengthb(str2) from text;

20   5

20   9

 

select  length(str1), length(str2) from text;

20   5

14   3

 

 

 

 

ex7)

select length('korea') from dual;

select length('코리아') from dual;

 

select lengthb('korea') from dual;

select lengthb('코리아') from dual;

 

ex8) 지정한 문자열 찾기 : instr(표현식, 찾는문자, [위치]) 1:(생략가능), -1:

select instr('HelloWorld', 'W') from dual;   //6

 

select instr('HelloWorld', 'o',-5) from dual; //5

 

select instr('HelloWorld', 'o',-1) from dual; //7

 

ex9) 지정한 길이의 문자열을 추출 : substr(표현식,시작,[갯수])

select substr('I am very happy', 6, 4) from dual;//very

 

select substr('I am very happy', 6) from dual;//very happy

 

[문제2] 사원의 레코드를 검색하시오(concat,  length)

      조건1) 이름과 성을 연결하시오(concat)

      조건2) 구해진 이름의 길이를 구하시오(length)

      조건3) 성이 n으로 끝나는 사원(substr)

 

employee_id      name           length

-----------------------------------------------------

102              LexDe Haan       10

 

ex10) 임의의 값이 지정된범위내에 어느 위치 : width_bucket(표현식,최소값,최대값,구간)

최소-최대값을 설정하고 10개의 구간을 설정후 위치찾기

0-100까지의 구간을 나눈후 74가 포함되어있는구간을 표시하시오

select width_bucket(74, 0, 100, 10) from dual;   //8

ex11) 공백제거 : ltrim(왼), rtrim(오른), trim(양쪽)

select rtrim('test   ')||'exam' from dual;

 

ex12) sysdate : 시스템에 설정된 시간표시

select sysdate from dual;

select to_char(sysdate, 'YYYY"" MM"" DD""') as 오늘날짜 from dual;

select to_char(sysdate, 'HH"" MI"" SS""') as 오늘날짜 from dual;

select to_char(sysdate, 'HH24"" MI"" SS""') as 오늘날짜 from dual;

 

ex13) add_months(date, 달수):날짜에 달수 더하기

select add_months(sysdate, 7) from dual;

 

ex14) last_day(date) : 해당달의 마지막날

select last_day(sysdate) from dual;

select last_day('2004-02-01') from dual;

select last_day('2005-02-01') from dual;

 

[문제3] 오늘부터 이번달 말까지 총 남은 날수를 구하시오

 

ex15) months_between(date1,date2) : 두 날짜사이의 달수

select round(months_between('95-10-21', '94-10-20'), 0) from dual; 자동 형변환

 

명시적인 변환(강제)

select last_name, to_char(salary, 'L99,999.00')

from employees

where last_name='King';

 

ex16)

select to_char(to_date('97/9/30', 'YY-MM-DD') , 'YYYY-MON-DD') from dual;← 2097

select to_char(to_date('97/9/30', 'RR-MM-DD') , 'RRRR-MON-DD') from dual; 1997

 

select to_char(to_date('17/9/30', 'YY-MM-DD') , 'YYYY-MON-DD') from dual; 2017

select to_char(to_date('17/9/30', 'RR-MM-DD') , 'RRRR-MON-DD') from dual; 2017

 

 

 

[문제4] 2005년 이전에 고용된 사원을 찾으시오

last_name              hire_date

-------------------------

King              17-6 -2003                    

De Haan          13-1 -2001                    

Greenberg        17-8 -2002

 

ex17) fm형식:형식과 데이터가 반드시 일치해야함(fm - fm사이값만 일치)

      fm를 표시하면 숫자앞의 0을 나타나지 않는다.

select last_name, hire_date from employees where hire_date='05/09/30';

select last_name, hire_date from employees where hire_date='05/9/30';

 

select to_char(sysdate, 'YYYY-MM-DD') from dual;

select to_char(sysdate, 'YYYY-fmMM-DD') from dual;

 

select to_char(to_date('2011-03-01','YYYY-MM-DD'), 'YYYY-MM-DD') from dual; 2011-03-01

select to_char(to_date('2011-03-01','YYYY-MM-DD'), 'YYYY-fmMM-DD') from dual; 2011-3-1

select to_char(to_date('2011-03-01','YYYY-MM-DD'), 'YYYY-fmMM-fmDD') from dual; 2011-3-01

 

ex18) count(컬럼명), max(컬럼명), min(컬럼명), avg(컬럼명), sum(컬럼명) 함수

employees테이블에서 급여의 최대, 최소, 평균, 합을 구하시오

조건) 평균은 소수이하절삭, 합은 세자리마다 콤마찍고 \표시

select max(salary),

min(salary),

trunc(avg(salary),0),

to_char(sum(salary), 'L9,999,999') from employees;

 

[문제5] 커미션(commission_pct)을 받지 않은 사원의 인원수를 구하시오

 

ex19) employees테이블에서 없는부서포함해서,총 부서의 수를 구하시오(답 : 12개)

      (nvl사용)

select  department_id from employees;  107

select  count(department_id) from employees;  106

select  count(*) from employees;

select  count(distinct department_id) from employees; 11

select count(distinct nvl(department_id, 0)) from employees; 12

select distinct nvl(department_id, 0) from employees; nvl null값을 0으로 대치

 

ex20) decode(표현식, 검색1,결과1, 검색2,결과2....[default])

          : 표현식과 검색을 비교하여 결과 값을 반환 다르면 default

 

      ② case  value  when  표현식  then  구문1

                             when  표현식 then  구문2

                      else  구문3

           end case

 

 

 

 

 

업무 id가 'SA_MAN' 또는 SA_REP'이면 'Sales Dept' 그 외 부서이면 'Another'로 표시

조건) 분류별로 오름차순정렬

select job_id, decode(job_id,

                     'SA_MAN', 'Sales Dept',

                     'SA_REP', 'Sales Dept',

                     'Another') "분류"

from employees

order by 2;

-------------------------------------------

select job_id, case job_id

                    when 'SA_MAN' then 'Sales Dept'

                    when 'SA_REP' then 'Sales Dept'

                    else 'Another'

                    end "분류"

from employees

order by 2;      

 

select job_id, case

                    when job_id='SA_MAN' then 'Sales Dept'

                    when job_id='SA_REP' then 'Sales Dept'

                    else 'Another'

                    end "분류"

from employees

order by 2;                                         

 

[문제6] 급여가 10000미만이면 초급, 20000미만이면 중급 외면 고급을 출력하시오 (case 사용)

      조건1) 컬럼명은  '구분'으로 하시오

      조건2) 제목은 사원번호, 사원명, 구 

      조건3) 구분(오름차순)으로 정렬하고, 같으면 사원명(오름차순)으로 정렬하시오

 

ex21) rank함수 : 전체값을 대상으로 순위를 구함

      rank(표현식) within group(order by 표현식)

      rank() over(쿼리파티션)  → 전체순위를 표시

 

급여가 3000인 사람의 상위 급여순위를 구하시오

select rank(3000) within group(order by salary desc) "rank" from employees;

 

전체사원의 급여순위를 구하시오

select employee_id, salary, rank() over(order by salary desc)"rank" from employees;

 

ex22) first_value함수 : 정렬된 값중에서 첫번째값 반환

      first_value(표현식) over(쿼리파티션)

전체사원의 급여와 함께 각부서의 최고급여를 나타내고 비교하시오

select employee_id,

salary,

department_id,

first_value(salary) over(partition by department_id order by salary desc) "highsal_deptID"

from employees;

 

 

PARTITION BY 절은 GROUP BY 절과 동일한 역할을 진행 합니다.

, GROUP BY 절을 사용하지 않고 필요한 집합으로 (WINDOW) 행들을 그룹화 시킴

Partition by 절을 사용 함으로 GROUP BY 절 없이 다양한 GROUPING 집합의 집계 결과들을 함께 출력 할 수 있습니다.

ORDER BY 절은 Partition by 로 정의된 WINDOW 내에서의 행들의 정렬 순서를 정의 한다.

SQL> select  employee_id,

last_name,

salary,

department_id,

              row_number( ) over ( PARTITION BY department_id ORDER BY salary DESC ) rnum

      from employees ;

 

 

 

부서별 급여를 내림차순으로 정렬 했을 경우 Row Number

 

EMPLOYEE_ID   LAST_NAME   SALARY    DEPARTMENT_ID    RNUM

---------------  --------------  ----------  ------------------  ----------

      200         Whalen          4400         10             1

      201         Hartstein        13000        20             1

      202         Fay              6000         20             2

      114         Raphaely        11000         30            1

      115         Khoo             3100         30            2

      116         Baida             2900         30            3

      117         Tobias            2800         30            4

      118         Himuro           2600         30            5

 부서 번호가 바뀔 때 Row Number 는 새로 시작 되는 것을 확인 할 수 있습니다.

NULL 값은 정렬 시 가장 큰 값으로 인식 (기본설정)

 

[문제7] 사원테이블에서 사원번호, 이름, 급여, 커미션, 연봉을 출력하시오

        조건1) 연봉은 $ 표시와 세자리마다 콤마를 사용하시오

        조건2) 연봉 = 급여 * 12 + (급여 * 12 * 커미션)

        조건3) 커미션을 받지 않는 사원도 포함해서 출력하시오

 

[문제8] 매니저가 없는 사원의 매니저id를, 1000번으로 표시

        조건1) 제목은 사원번호,이름,매니저ID

        조건2) 모든 사원을 표시하시오      

        사원번호     이름      매니저ID

         ---------------------------------

         100           King        1000

 

 


'JAVA 개발 공부 정보' 카테고리의 다른 글

SQL 연습  (0) 2018.03.15
Chapter04-스프링JDBC  (0) 2018.03.15
JAVA-myBatis  (0) 2018.03.14
mvnrepository  (0) 2018.03.14
Git hub! 깃허브 회원가입! 링크!  (0) 2018.03.14

myBatis
ORM(Object Relational Mapping) 프레임워크는 데이터베이스와 객체와의 관계를 맵핑시켜 퍼시스턴스 로직 처리를 도와주는 프레임워크이다.
대표적으로 iBatishibernate가 있다
 
myBatisSQL쿼리문, 예외처리, 트랙잭션 관리들을 XML형식으로 관리한다
POJO(Plain Old Java Object)객체와 테이블의 컬럼들을 편리하고 빠르고 정확하게 매칭할 수 있다.
 
myBatis의 목표와 특징은 쉽고, 간단하고, 의존성이 적다는 것이다
SQL문과 자바코드를 분리함으로 인해 자바 개발자는 쿼리문을 신경 쓰지 않아도 된다.
 
myBatis는 자바오브젝트와 SQL문 사이의 자동매핑 기능을 지원하는 ORM 프레임워크이다
자바코드와 SQL를 분리하므로서 SQL문의 변경이 있을때마다 자바를 수정하지 않아도 되고 컴파일을 하지 않아도 된다
 
http://blog.mybatis.org
 
Java Project : myBatisTest
Package : user.main
Class : UserMain.java (public static void main(~~~))
Package : user.action
Interface : UserAction.java
Class : UserInsertAction.java
UserSelectAction.java
UserUpdateAction.java
UserDeleteAction.java
Package : user.dao
UserDAO.java
userMapper.xml
src : mybatis-config.xml
 
테이블 작성
 
create table usertable(
name varchar2(30) not null,
id varchar2(30) primary key,
pwd varchar2(30) not null);
 

'JAVA 개발 공부 정보' 카테고리의 다른 글

Chapter04-스프링JDBC  (0) 2018.03.15
Oracle-SQL  (0) 2018.03.14
mvnrepository  (0) 2018.03.14
Git hub! 깃허브 회원가입! 링크!  (0) 2018.03.14
JAVA-SQL  (0) 2018.03.14

http://mvnrepository.com/

spring context 검색

<!-- Spring -->
  <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>4.3.10.RELEASE</version>
  </dependency>


spring-webmvc 검색


<!-- https://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
<dependency>
    <groupId>org.springframework</groupId> 요.


    <artifactId>spring-webmvc</artifactId>
    <version>4.3.10.RELEASE</version>
</dependency>


 jstl 검색

<!-- https://mvnrepository.com/artifact/javax.servlet.jsp.jstl/jstl -->
<dependency>
    <groupId>javax.servlet.jsp.jstl</groupId>
    <artifactId>jstl</artifactId>
    <version>1.2</version>
</dependency>

디펜던시 오류시 프로젝트 우클릭 import - install~~~ - 정보 입력
프로젝트 클린 - pom.xml 복사 붙여넣기 ->> 끝!


'JAVA 개발 공부 정보' 카테고리의 다른 글

Oracle-SQL  (0) 2018.03.14
JAVA-myBatis  (0) 2018.03.14
Git hub! 깃허브 회원가입! 링크!  (0) 2018.03.14
JAVA-SQL  (0) 2018.03.14
[JAVA] JSON - Jackson Databind  (0) 2018.02.26

1. 깃허브 회원가입!


https://github.com/



'JAVA 개발 공부 정보' 카테고리의 다른 글

JAVA-myBatis  (0) 2018.03.14
mvnrepository  (0) 2018.03.14
JAVA-SQL  (0) 2018.03.14
[JAVA] JSON - Jackson Databind  (0) 2018.02.26
[java-note] 회원정보 jqeury 연습 소스  (0) 2018.02.26

★ SQL문

1. 데이타 조작어(DML : Data Manipulation Language)

    : insert, update, delete, merge

2. 데이타 정의어(DDL : Data Definition Language)

    : create, alter, drop, rename, truncate    

3. 데이타검색

    : select 

4. 트랜젝션제어

    : commit, rollback, savepoint

5. 데이타 제어어(DCL : Data Control Language)

    : grant,  revoke



※ select

[형식]

 select [distinct] [컬럼1,컬럼2,.....][as 별명][ || 연산자][*]

 from 테이블명

 [where 조건절] 


distinct : 중복제거

* : 모든

조건절 : and,or,like,in,between and,is null,is not null



ex1) employees테이블의 모든 사원의 사원번호,이름(last_name),급여 검색

select employee_id, last_name, salary from employees;


ex2)별명붙이기(as는 생략가능)

    employees테이블의 모든 사원의 사원번호,이름(last_name),급여 검색

    조건) title 사원번호, 이름 ,급여로 출력할것

select employee_id as 사원번호 , last_name as "이  름", salary as "급  여" 

from employees;



ex3) employee테이블에서 사원번호,이름,연봉을 구하시오

     조건1) 연봉 = 급여 * 12

     조건2) 제목을 사원번호, 이름, 연봉으로 출력

select employee_id as 사원번호 , last_name as "이  름", salary*12 as "연  봉"

from employees;


ex4) 연결연산자( || ) : 컬럼을 연결해서 출력

     frist_name과  last_name을 연결해서 출력하시오

       이   름

     ------------

     Ellen   Abel

select  first_name||'  '||last_name  as  "이  름" from employees;



[문제1] 다음처럼 출력하시오

   사원번호    이  름        연 봉

   -----------------------------------------------------

    100      Steven King   288000달러 


[문제2]다음처럼 출력하시오 (last_name, job_id)

     Employee  Detail

     --------------------

     King  is  a  SA_REP

     King  is  a  AD_PRES



ex5) distinct(중복제거)

     employees 테이블에서 부서ID를 출력하시오

select distinct department_id from employees;


ex6) 10번부서 또는 90번부서 사원들의 이름, 입사일, 부서ID를 출력하시오

select last_name, hire_date, department_id

from employees

where department_id=10  or  department_id=90;


ex7) 급여가 2500이상 3500미만인 사원의 이름(last), 입사일, 급여를 검색하시오

select last_name, hire_date, salary

from employees

where salary>=2500  and  salary<3500;



[문제3] 급여가 2500이하 이거나 3000이상이면서 90번 부서인 

사원의 이름, 급여, 부서ID를 출력하시오.

조건1) 제목은 사원명, 월  급, 부서코드로 하시오

조건2) 급여앞에 $를 붙이시오

조건3) 사원명은 first_name과 last_name을 연결해서 출력하시오

ex8) 'King'사원의 모든 컬럼을 표시하시오

select  *  from employees  where  last_name='King';

→ 문자열 검색할 때는 대,소문자를 구분


like  : 문자를 포함

'%d' - d로 끝나는

      'a%' - a로 시작하는

      '%test% - test가 포함되어있는


      '_a%'  - 두번째 글자가 a로 시작하고 나머지는 무시

      '__a%' - 세번째 글자가 a로 시작하고 나머지는 무시


ex9) 업무ID에 MAN이 포함되어있는 사원들의 이름,업무ID,부서ID를 출력하시오

select last_name, job_id, department_id

from employees

where job_id like '%MAN%';


ex10) 업무ID가  IT로 시작하는 사원들의 이름, 업무ID, 부서ID를 출력하시오

select last_name, job_id, department_id

from employees

where job_id like 'IT%';


ex11) is null / is not null 

커미션을 받는 사원들의 이름과 급여, 커미션을 출력하시오

select last_name, salary, commission_pct

from employees

where commission_pct is not null;

커미션을 받지 않는 사원들의 이름과 급여, 커미션을 출력하시오

select last_name, salary, commission_pct

from employees

where commission_pct is null;


ex12) in연산자(or연산자의 다른표현)

업무ID가  FI_MGR이거나  FI_ACCOUNT인 사원들의 사원번호,이름,직무를 출력하시오

select employee_id, last_name, job_id

from employees

where job_id='FI_MGR' or job_id='FI_ACCOUNT';



select employee_id, last_name, job_id

from employees

where job_id in('FI_MGR', 'FI_ACCOUNT');


ex13) between연산자(and연산자의 다른 표현) : 초과,미만에서는 사용할수 없다

급여가  10000이상  20000이하인 사원의 사원번호,이름,급여를 출력하시오

select employee_id, last_name, salary

from employees

where salary>=10000 and salary<=20000;


select employee_id, last_name, salary

from employees

where salary between 10000 and 20000;



[문제4] 업무ID가 'SA_REP' 이거나 'AD_PRES' 이면서 급여가 10000를 

초과하는 사원들의 이름,업무ID,급여를 출력하시오


이름        업무ID            급  여

--------------------------------------

King       AD_PRES            24000원



[문제5] Employees테이블의 업무ID가 중복되지 않게 표시하는 질의를 작성하시오


  JOB_ID

-----------

AC_ACCOUNT

AC_MGR

     :



[문제6] 입사일이 2005년인 사원들의 사원번호, 이름, 입사일을 표시하시오



+ Recent posts