주말 1주차 날자관련 / 조인쿼리

SQL> Select ename , hiredate from emp;

ENAME      HIREDATE
---------- --------
SMITH      80/12/17
ALLEN      81/02/20
WARD       81/02/22
JONES      81/04/02
MARTIN     81/09/28
BLAKE      81/05/01
CLARK      81/06/09
SCOTT      87/04/19
KING       81/11/17
TURNER     81/09/08
ADAMS      87/05/23

ENAME      HIREDATE
---------- --------
JAMES      81/12/03
FORD       81/12/03
MILLER     82/01/23

14 개의 행이 선택되었습니다.


SQL> alter session set nls_date_format='yy/mm/dd hh24:mi:ss';

세션이 변경되었습니다.

save n;



적용 후


ENAME      HIREDATE
---------- -----------------
SMITH      80/12/17 00:00:00
ALLEN      81/02/20 00:00:00
WARD       81/02/22 00:00:00
JONES      81/04/02 00:00:00
MARTIN     81/09/28 00:00:00
BLAKE      81/05/01 00:00:00
CLARK      81/06/09 00:00:00
SCOTT      87/04/19 00:00:00
KING       81/11/17 00:00:00
TURNER     81/09/08 00:00:00
ADAMS      87/05/23 00:00:00

ENAME      HIREDATE
---------- -----------------
JAMES      81/12/03 00:00:00
FORD       81/12/03 00:00:00
MILLER     82/01/23 00:00:00

14 개의 행이 선택되었습니다.


##############################################################################

-- 근무년수 알아내기

SQL> select ename ,  months_between(sysdate , hiredate)/12 as month from emp;

ENAME           MONTH
---------- ----------
SMITH      28.1437536
ALLEN      27.9690225
WARD       27.9636461
JONES      27.8507429
MARTIN     27.3641837
BLAKE      27.7700977
CLARK       27.665259
SCOTT       21.805044
KING        27.227087
TURNER     27.4166667
ADAMS      21.7109579

ENAME           MONTH
---------- ----------
JAMES       27.181388
FORD        27.181388
MILLER     27.0442913

14 개의 행이 선택되었습니다.


#####################################################################

SQL> select sysdate , sysdate+90 , add_months(sysdate,3) from dual;

SYSDATE           SYSDATE+90        ADD_MONTHS(SYSDAT
----------------- ----------------- -----------------
09/02/08 11:27:31 09/05/09 11:27:31 09/05/08 11:27:31



##### 돌아 오는 요일 자동으로 구하는 함수 ############

SQL> Select next_day(sysdate , '금요일') from dual;

NEXT_DAY(SYSDATE,'금요일')
---------------------------
09/02/13 금요일 11:30:38
###############################################

## 요번달의 마지막날은?############################

SQL> Select last_day(sysdate) from dual;

LAST_DAY(SYSDATE)
---------------------------
09/02/28 토요일 11:32:58


###############################################




## 해당 형식으로 날짜 출력3##########################
SQL> select to_char(sysdate , 'yyyy-mm-dd') from dual;

TO_CHAR(SY
----------
2009-02-08
###############################################

########
Select to_char(sysdate , 'yyyy')||'년'|| to_char(sysdate,'mm')||'월'||to_char(sysdate,'dd')||'일'  from dual;

TO_CHAR(SYSDAT
--------------
2009년02월08일

####################################################################################



#######
SQL> select ename ,hiredate,to_char(hiredate,'Q') from emp;

ENAME      HIREDATE          T
---------- ----------------- -
SMITH      80/12/17 00:00:00 4
ALLEN      81/02/20 00:00:00 1
WARD       81/02/22 00:00:00 1
JONES      81/04/02 00:00:00 2
MARTIN     81/09/28 00:00:00 3
BLAKE      81/05/01 00:00:00 2
CLARK      81/06/09 00:00:00 2
SCOTT      87/04/19 00:00:00 2
KING       81/11/17 00:00:00 4
TURNER     81/09/08 00:00:00 3
ADAMS      87/05/23 00:00:00 2

ENAME      HIREDATE          T
---------- ----------------- -
JAMES      81/12/03 00:00:00 4
FORD       81/12/03 00:00:00 4
MILLER     82/01/23 00:00:00 1

14 개의 행이 선택되었습니다.
###########################################################################





#####################SQL> --81년도 3사분기 입사자 검색
SQL> select ename,hiredate from emp where to_char(hiredate,'Q') ='3' and to_char
(hiredate,'yyyy') ='1981';

ENAME      HIREDATE
---------- -----------------
MARTIN     81/09/28 00:00:00
TURNER     81/09/08 00:00:00
########################################################################




## numbers
SQL> select ename , to_char(sal*12,'9,999,999,999') 연봉 from emp;

ENAME      연봉
---------- --------------
SMITH               9,600
ALLEN              19,200
WARD               15,000
JONES              35,700
MARTIN             15,000
BLAKE              34,200
CLARK              29,400
SCOTT              36,000
KING               60,000
TURNER             18,000
ADAMS              13,200

ENAME      연봉
---------- --------------
JAMES              11,400
FORD               36,000
MILLER             15,600

14 개의 행이 선택되었습니다.
#############################################################################


###
SQL> select ename , to_char(sal-2000,'9,999mi') from emp;

ENAME      TO_CHA
---------- ------
SMITH      1,200-
ALLEN        400-
WARD         750-
JONES        975
MARTIN       750-
BLAKE        850
CLARK        450
SCOTT      1,000
KING       3,000
TURNER       500-
ADAMS        900-

ENAME      TO_CHA
---------- ------
JAMES      1,050-
FORD       1,000
MILLER       700-

14 개의 행이 선택되었습니다.
#############################################################################




#####  해당일 부터현재까지 지난 날짜 몇일?
SQL> --해당일 부터 현재까지 지난 날짜 몇일?
SQL> select sysdate - to_date('1982-03-12','yyyy-mm-dd') from dual;

SYSDATE-TO_DATE('1982-03-12','YYYY-MM-DD')
------------------------------------------
                                9830.51251
############################################################################




## rr 타입 1950~ 2049 맵핑.#####################################################
SQL> select sysdate - to_date('95-03-12','rr-mm-dd') from dual;

SYSDATE-TO_DATE('95-03-12','RR-MM-DD')
--------------------------------------
                            5082.51434
###########################################################################


## rr 타입 정석.##############################################################

SQL> select sysdate - to_date('1982-03-12','rrrr-mm-dd')  from dual;

SYSDATE-TO_DATE('1982-03-12','RRRR-MM-DD')
------------------------------------------
                                 9830.5194
###########################################################################



## Case 문 사용.####################################################
SQL> Select ename,
  2  case deptno
  3  when 10 then 'ACCOUNTING'
  4  when 20 then 'RESEARCH'
  5  when 30 then 'SALES'
  6  else 'OPERATION'  end "DNAM

ENAME      DNAME
---------- ----------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH

ENAME      DNAME
---------- ----------
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 개의 행이 선택되었습니다.
###############################################################################


## 디코드 사용방법.
SQL> select ename, decode(
  2  10,'A',
  3  20,'B',
  4  30,'C',
  5  'D') "DNAME" From emp;

ENAME           DNAME
---------- ----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS

ENAME           DNAME
---------- ----------
JAMES
FORD
MILLER

14 개의 행이 선택되었습니다.
#################################################################################


########################################
SQL> r
  1  select ename , decode(dept
  2  10,'A',
  3  20,'B',
  4  30,'C'
  5* ) from emp

ENAME      D
---------- -
SMITH      B
ALLEN      C
WARD       C
JONES      B
MARTIN     C
BLAKE      C
CLARK      A
SCOTT      B
KING       A
TURNER     C
ADAMS      B

ENAME      D
---------- -
JAMES      C
FORD       B
MILLER     A

14 개의 행이 선택되었습니다.
############################################################################


#############
SQL>  select distinct deptno , job from emp;

    DEPTNO JOB
---------- ---------
        20 CLERK
        30 SALESMAN
        20 MANAGER
        30 CLERK
        10 PRESIDENT
        30 MANAGER
        10 CLERK
        10 MANAGER
        20 ANALYST

9 개의 행이 선택되었습니다.

SQL> select deptno,job, count(*) from emp group by deptno , job;

    DEPTNO JOB         COUNT(*)
---------- --------- ----------
        20 CLERK              2
        30 SALESMAN           4
        20 MANAGER            1
        30 CLERK              1
        10 PRESIDENT          1
        30 MANAGER            1
        10 CLERK              1
        10 MANAGER            1
        20 ANALYST            2

9 개의 행이 선택되었습니다.
###########################################################################


## 카르티잔 곱.
## 얼라이어스  =  dept.deptno as a , dept.deptno a
########  SQL 92 에서는 이퀴 조인만##########
SQL> select
         ename , dept.deptno , dname
         from
        emp , dept
        where emp.deptno = dept.deptno;

ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
JONES              20 RESEARCH
MARTIN             30 SALES
BLAKE              30 SALES
CLARK              10 ACCOUNTING
SCOTT              20 RESEARCH
KING               10 ACCOUNTING
TURNER             30 SALES
ADAMS              20 RESEARCH

ENAME          DEPTNO DNAME
---------- ---------- --------------
JAMES              30 SALES
FORD               20 RESEARCH
MILLER             10 ACCOUNTING

14 개의 행이 선택되었습니다.
#########################################


hr 계정에서 employees ->first_name   ,  departments ->department name
SQL 92 버전 )

Select 

 first_name ,
 department_name,
 j.job_title ,
 d.department_id ,
 l.city,
 c.country_name ,
 r.region_name

from 

 employees e ,    

 departments d ,      
 locations l ,     
 countries c,      
 regions r,     
 jobs j

where e.department_id = d.department_id
        and d.LOCATION_ID = l.LOCATION_ID
        and l.country_id = c.country_id
        and c.region_id= r.region_id
        and e.job_id  = j.job_id

########################################################################

 

SQL 92

sql = Select ename , dname from emp e, dept4 d where e.deptno = d.deptno;

SQL 99  [테스팅 용도 : join 되는 테이블의 이름이 같은 컬럼이 1개 밖에 없을때] 사용:

sql = Select ename , dname from emp e natural join dept d;

개선책

sql = select ename , dname from emp e join dept d using(deptno); 
// 기본 은 inner 다  , Join 되는 테이블간의 키 컬럼 이름이 같을때.
sql = select ename , dname from emp e join dept d on (e.deptno = d.deptno);
//모든 경우 다 가능.(호환성이 가장 좋다)
 

## hr
select
 FIRST_NAME ,
 DEPARTMENT_NAME ,
 CITY,
 COUNTRY_NAME,
 REGION_NAME,
 JOB_TITLE
from
 employees e
join   departments d  using( DEPARTMENT_ID)
join LOCATIONS l  using(LOCATION_ID)
join countries c using(COUNTRY_ID)
join REGIONS r using(REGION_ID)
join JOBS j using(JOB_ID)


##  유럽에서 근무하는 사람

select
 FIRST_NAME ,
 DEPARTMENT_NAME ,
 CITY,
 COUNTRY_NAME,
 REGION_NAME,
 JOB_TITLE
from
 employees e
join   departments d  using( DEPARTMENT_ID)
join LOCATIONS l  using(LOCATION_ID)
join countries c using(COUNTRY_ID)
join REGIONS r using(REGION_ID)
join JOBS j using(JOB_ID)
where
  r. REGION_NAME  = 'Europe'

//셀프 조인  예시
  1  Select
  2     e.empno ,
  3     e.ename ,
  4     m.ename
  5  from
  6     emp e
  7* join       emp m   on(e.mgr = m.empno)

     EMPNO ENAME      ENAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING

     EMPNO ENAME      ENAME
---------- ---------- ----------
      7566 JONES      KING
      7369 SMITH      FORD

13 개의 행이 선택되었습니다.






※on 절에서는 where 절 대신 and로 대체 할수있다.



## non equijoin 예제

select ename , sal , grade
from emp join salgrade
on sal between losal and hisal

SQL> select ename , dname
        from emp join dept
        using(deptno);

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH

ENAME      DNAME
---------- --------------
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 개의 행이 선택되었습니다.



※
 SQL> r
   select ename , dname
   from emp right outer join d
   using(deptno)

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH

ENAME      DNAME
---------- --------------
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING
           OPERATIONS

15 개의 행이 선택되었습니다.







SQL> Select
    count(*)
    from employees e
    join departments d
    on e.department_id = d.department_id;

  COUNT(*)
----------
       106



    Select
    count(*)
    from employees e
    left outer join departments d
    on e.department_id = d.department_id

  COUNT(*)
----------
       107




/#########################################################

SQL> select e.empno, e.ename , m.ename
  2  from emp e join emp m
  3  on(e.mgr = m.empno);

     EMPNO ENAME      ENAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING

     EMPNO ENAME      ENAME
---------- ---------- ----------
      7566 JONES      KING
      7369 SMITH      FORD

13 개의 행이 선택되었습니다.



SQL> r
  1  select e.empno, e.ename , m.ename
  2  from emp e
  3  left outer join emp m
  4* on(e.mgr = m.empno)

     EMPNO ENAME      ENAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING

     EMPNO ENAME      ENAME
---------- ---------- ----------
      7566 JONES      KING
      7369 SMITH      FORD
      7839 KING

14 개의 행이 선택되었습니다.