SQL - [고급 SQL]

• TimeStamps and EXTRACT : 날짜와 시간을 다룬다.
  • 날짜와 시간 정보는 스스로 표와 DB를 만들 때 유용하다.
  • 시간 정보와 날짜 정보를 저장하는 다른 데이터 종류의 Subset이 있다는 것을 기억해두자.
  • 상황에 따라 모든 날짜, 시간, 표준시간대를 표시할지 여부를 결정해야 한다. 예를 들어 근무 시간 표를 만들고자 한다면 출퇴근 시간만 체크하면 되고 표준시간대는 굳이 쓸 필요 없을 것이다.(장기적으로 생각하기.)
• Math Functions and String Functions
• Sub-query
• Self-join

​

1. TIMESTAMP and EXTRACT

- TIMESTAMP

show all /*실행시간을 보여주는 매개변수*/ 
show timezone /*select*/ 
select now() /*현재 타임스탬프 정보 */ 
select timeofday() /*문자열로 준다.*/ 
select CURRENT_TIME /*현재 시간*/ 
select CURRENT_DATE /*현재 날짜*/

이는 우리가 직접 열 또는 표를 만들고 실제 작성일을 기록할 때 중요한 작업이 된다.

​

- EXTRACT

추출하고자 하는 Sub-Component를 입력한다.

extract(year from date_col)

[AGE]

해당 Timestamp내에서 현지까지의 시기를 계산

age(date_col)

[TO_CHAR]

Date 일자 유형을 글자로 바꿔주는(TO_CHARACTER) 함수이며 서식 구성을 마음대로!

TO_CHAR(date_col,'mm-dd-yyyy')

e.g.

 

그림 1

/*그림 1*/
select to_char(payment_date,'YYYY-mm') from payment;

'YYYY-mm'을 'MM/dd/YYYY' 형식으로 바꿔도 가능하다.

 

 

2. Math Functions and String Functions

대부분의 연산자를 활용할 수 있다.

select round(rental_rate/replacement_cost,4) * 100 as percent_cost from film;

first_name의 길이를 알 수 있으며 이외에도 다양한 것들이 있다. (||, upper,left ...)

/*문자열길이*/ 
select length(first_name) from customer; 

/*문자열 합치기*/ 
select first_name || ' ' || last_name as fullname from customer;

3. Sub-query

서브 쿼리를 쓰면 더 복잡한 쿼리를 만들 수 있으며 다른 쿼리의 결과에 대한 쿼리 실행 및 사용한다.

• 괄호 내 서브 쿼리가 먼저 계산됨
• 서브 쿼리를 사용할 때 오퍼레이터 내에서 사용해도 됨( + EXISTS)

​

e.g.,

 

그림 3

select film_id, title from film where film_id 
in (select inventory.film_id from rental inner join inventory 
on inventory.inventory_id = rental.inventory_id 
where return_date between '2005-05-29' and '2005-05-30');

 

그림 4

/*EXISTS*/
select first_name, last_name from customer as c 
where exists (select * from payment as p where p.customer_id = c.customer_id and amount > 11)

 

3. Self join

표가 자체에 합쳐져 있는 쿼리

• Self join은 같은 표에 내 여러 열의 속 여러 값을 비교할 때 유용. 이는 표 구조 및 실제 해결하려는 문제와 연관이 많기 떄문에 유용하다.
• Self join은 같은 표의 복사본 처럼 보이게 할 수 있다. 그러나, 사실 복제된 것은 아니다.
• Self join을 위한 특별 키워드가 없다는 것을 기억하라. 단순한 표준 조인처럼 보일텐데 이는 두 부분에 동시에 있는 동일한 표에서 동일한 구문을 가지고 있다.
• 같은 표를 사용하면서 셀프 조인을 사용한다면 표에 Alias를 사용하자. 그렇지 않으면 self join 안의 표 이름이 애매해진다.

​

[Syntax]

SELECT tableA.col, tableB.col

FROM table AS tableA

JOIN table AS tableB ON

tableA.some_col = tableB.other_col

​

초록 글씨를 보자. FROM 뒤 table을 tableA, JOIN뒤 table을 tableB로 지정했다.

같은 표를 두 이름으로 지정한 것 뿐이다.(복제) 단지 체계를 위해 Alias를 쓴 것 뿐.

e.g.,여기서 report_id를 해당 테이블의 컬럼을 참조해서 이름으로 바꾸고 싶다.

그림 5

 

Andrew는 report를 3번 Id에 보낸다. to charlie.

David는 report를 1번 Id에 보낸다. to Andrew

 

e.g.

그림 6

select f1.title, f2.title, f1.length from film as f1 
inner join film as f2 on f1.film_id != f2.film_id and f1.length = f2.length

self join은 같은 table을 복제를 해서 컬럼을 참조한다고 생각하면 된다.