날짜

months_between(날짜1, 날짜2)

💡 두 날짜 사이의 개월수를 반환합니다. 날짜 1이 더 최근의 날짜여야 합니다.

• 코드

  select 
      w.workerId,
      w.name,
      d.name,
      w.hiredate,
      round(months_between(sysdate, to_date(hiredate)),0) 근무개월,
      rpad(substr(w.jumin,1,8), 14, '*') 주민번호
  from worker w
  join department d on d.id = w.id
  where round(months_between(sysdate, to_date(hiredate)),0) >= 60 and w.jumin is not null
  order by 근무개월 desc, w.name;

---

시퀀스

다른 레코드와 구별할 순번을 위하여 1부터 1씩 차례로 증가하는 값을 위하여 사용합니다.

방법

create sequence 시퀀스 이름 [start with 값 increment by 값 maxvalue 값]

→ start with : 시작하는 값

→ increment by : 증감값

→ maxxvalue : 최대값

💡 이 두개를 생략하면 기본값이 1씩이다. 최대값을 생략하면 시스템이 허용하는데까지 발행할 수 있다.

시퀀스 사용하기

시퀀스이름.nextval

주의

insert를 실패해도 시퀀스는 새로운 번호를 발행한다.

---

rownum

select 문으로 조회한 결과에 대하여 차례대로 행번호를 붙이기 위하여 사용하는 속성

코드

select rownum, w.workerId, w.name
from worker w
join department d on d.id = w.id
where d.name like '%개발%';

// 도서번호별로 판매량을 출력
// ↓ A
select bookid, count(*) cnt
from orders
group by bookid
order by cnt desc;

// A의 결과에 행번호를 붙여서 2번째 행의 cnt 출력
// select cnt from (A) where rownum <= 2; 
// <= 2는 되는데 = 2는 안된다.
// 정렬한 것에다가 행번호를 붙여서 다시 서브쿼리로 만들어야 한다.
select cnt
from(
    select rownum n, b.*
    from (
        select bookid, count(*) cnt
        from orders
        group by bookid
        order by cnt desc
        ) b
    )
where n = 2;

// 도서번호별로 판매량중에서 판매량이 D보다 크거나 같은 도서번호 조회
select bookid
from (
    select bookid, count(*) cnt
    from orders
    group by bookid
    order by cnt desc
    )
where cnt >= (
            select cnt
            from(
                select rownum n, b.*
                from (
                    select bookid, count(*) cnt
                    from orders
                    group by bookid
                    order by cnt desc
                    ) b
                )
            where n = 2
            ); 

// book테이블로부터 bookid가 E에 해당하는 도서정보를 조회
select * from book
where bookid in (
            select bookid
            from (
                select bookid, count(*) cnt
                from orders
                group by bookid
                order by cnt desc
                )
            where cnt >= (
                    select cnt
                    from(
                        select rownum n, b.*
                        from (
                            select bookid, count(*) cnt
                            from orders
                            group by bookid
                            order by cnt desc
                            ) b
                        )
                    where n = 2
                        )
                );

---

서브쿼리

sql문 안에 포함되는 또 다른 sql문을 말합니다.

서브쿼리 위치

• select 절 : 스칼라 서브쿼리 or 상관서브쿼리
• from 절 : 인라인 뷰
• where 절 : 중첩쿼리

스칼라 서브쿼리

select 절에 컬럼이름 오는 곳에 사용되는 서브쿼리를 말한다. 단일행이 오도록 표현해야 한다.

💡 메인쿼리와 서브쿼리의 조건식이 필요하다.

→ 상관서브쿼리

• 코드

  // 상관서브쿼리
  select 
      custId,
      (select name from customer c where c.custId = o.custid) 이름,
      sum(saleprice)
  from orders o
  group by custId;
  
  select 
      (select b.bookName from book b where o.bookId = b.bookId) 도서명별,
      count(*)
  from orders o
  group by bookId;
  
  select 
      (select d.name from department d where d.id = w.id) 부서별,
      count(w.workerId),
      avg(w.salary),
      max(w.salary)
  from worker w
  group by id;

인라인 뷰

서브쿼리가 from 절에 오는 경우를 말한다. 실제로는 존재하지 않는 select한 결과를 가상의 테이블이라고 한다.

중첩 쿼리

서브쿼리가 where 절에 오는 경우를 말한다. 이때는 서브쿼리의 건수가 단일행인지 아닌지에 따라 단일행연산자, 다중행연산자를 구분하여 사용해야 한다.

• =, >, <, ≥, ≤ 연산자는 단일행 연산자이며 서브쿼리의 건수가 한건(단일행)일 때에 사용할 수 있다.
• 서브쿼리의 건수가 여러건(다중)일 때에는 = 대신에 in을 써야 한다.

💡 >, <, ≥, ≤는 any(some), all과 같이 사용해야 한다.

다중행 연산자

서브쿼리의 건수가 여러건일 때 사용하는 연산자를 말한다.

이 때 =는 사용할 수 없고 여러 값중 하나만 만족해도 될 때는 in, some, any를 사용해야 한다. 모두 만족해야 한다면 all을 사용한다.