SQL 정리 - (1)

패스트 캠퍼스 모두를 위한sql 강의를 정리하였습니다.

PostgreSQL은 확장 가능성 및 표준 준수를 강조하는 객체-관계형 데이터베이스 관리 시스템(ORDBMS)의 하나이다.[위키백과]

DBeaver는 SQL 클라이언트이자 데이터베이스 관리 도구이다. [위키백과]

---

조인과 집계 데이터 정리

• 조인
  • INNER JOIN () on
  • full outer JOIN () on
  • left,right JOIN () on
  • cross join
• 집계 데이터
  • group by (group by한 컬럼은 select할 수 있지만 나머지는 집계함수를 사용해야함)
  • having (group by에 대한 조건)
  • grouping set
  • roll up (group by와 사용)
  • order by
  • cube(grouping set보다 간결)
  • 분석함수(avg, count) OVER (PARTITION BY B.GROUP_NAME, oreder by())
  • ROW_NUMBER,rank, dense_rank () OVER ( PARTITION BY B.GROUP_NAME ORDER BY A.PRICE)
  • (first,last) value
  • reg, lead

---

select

A.first_name<br> 

,A.last_name<br> 

,A.email<br> 

from

 customer A #ALIAS는 가독성을 위해서 사용

;

---

select

 first_name 

,last_name 

from 

customer 

order by first_name asc

; #오름차순

---

select

 first_name 

,last_name 

from 

customer 

order by first_name desc

; #내림차순

---

select 사용시에 distinct를 사용하면 중복 값을 제외한 결과값이 출력된다

slect

distinct column_1 from table_name;

---

데이터 필터

SELECT

 LAST_NAME --3

, FIRST_NAME

FROM

  CUSTOMER --1

WHERE

FIRST_NAME = 'Jamie'; --2 #조건을 걸어줌

AND LAST_NAME = 'Rice'; #AND를 사용해서 두가지 조건을 걸어줌

---

SELECT

   FILM_ID
 
 , TITLE
 
 , RELEASE_YEAR

FROM

 FILM

ORDER BY FILM_ID –ORDER BY를 한 결과중에서

LIMIT 5 ; –5건만 OFFSET 3; – (0,1,2,3,) 4번째 부터 뽑아달라는 명령어

---

SELECT

 FILM_ID

, TITLE

FROM

  FILM

ORDER BY TITLE

• FETCH FIRST ROW ONLY #최초의 한건만 가져옴
• FETCH FIRST (n) ROW ONLY #n개를 가져옴

;

#LIMIT절 이용이랑 비슷함

---

SELECT

CUSTOMER_ID

, RENTAL_ID

, RETURN_DATE
  
FROM RENTAL
 
    WHERE
   
    CUSTOMER_ID NOT IN (1, 2)#1과 2가 아닌것 not이 없으면 1 or 2       

    ORDER BY RETURN_DATE DESC;# or보다 in조건이 좋다

---

#서브쿼리

SELECT

  FIRST_NAME  #뽑아라

, LAST_NAME

FROM CUSTOMER #customer에서

WHERE CUSTOMER_ID IN ( #조건

                    SELECT
				    
                    CUSTOMER_ID
				    
                    FROM
				    
                    RENTAL
				    
                    WHERE 
                    
                    CAST (RETURN_DATE AS DATE) = '2005-05-27' );

---

SELECT

    CUSTOMER_ID
  
   , PAYMENT_ID
   
   , AMOUNT
   
FROM

PAYMENT
  
WHERE AMOUNT BETWEEN 8 AND 9; #BETWEEN을 이용(등호가 포함됨)

Like : 같은 문자열을 출력해줌

• LIKE ‘%, _, %%, ‘등을 사용함

null값 찾을 때

Where Is null 사용

outer 조인 : 특정 컬럼을 기준으로 출력하면서 다른 컬럼은 특정 컬럼과 교집합되는 부분만 출력함.

self join 사용

SELECT

F1.TITLE

, F2.TITLE

, F1.LENGTH

FROM

FILM F1

INNER JOIN FILM F2

ON F1.FILM_ID <> F2.FILM_ID

AND F1. LENGTH = F2.LENGTH;

self join을 사용하는 이유(밑에건 작동 안함)

SELECT *

FROM film f1 –테이블을 조회

WHERE f1.LENGTH = f1.length

AND F1.FILM_ID <> f1.FILM_ID

;

---

full outer join은 left right inner join을 합한것

group by grouping grouping sets 사용법

SELECT

    CASE WHEN GROUPING(BRAND) = 0 AND GROUPING(SEGMENT) = 0 THEN '브랜드별+등급별'
		 
         WHEN GROUPING(BRAND) = 0 AND GROUPING(SEGMENT) = 1 THEN '브랜드별'
	     
         WHEN GROUPING(BRAND) = 1 AND GROUPING(SEGMENT) = 0 THEN '등급별'
		 
         WHEN GROUPING(BRAND) = 1 AND GROUPING(SEGMENT) = 1 THEN '전체합계'
		 
         ELSE '' 
		 
         END AS "집계기준"	
 , BRAND
 
 , SEGMENT
 
 , SUM (QUANTITY)

FROM

  SALES

GROUP BY

GROUPING SETS

(

(BRAND, SEGMENT)

, (BRAND)

, (SEGMENT)

, ()

)

ORDER BY BRAND, SEGMENT;

---

group by roll up

SELECT

BRAND

, SEGMENT

, SUM (QUANTITY)

FROM

SALES

GROUP BY

ROLLUP (BRAND, SEGMENT)

ORDER BY

BRAND, SEGMENT;

–group by별 합계 + rollup절에 맨 앞에 쓴 컬럼 기준의 합계도 나오고 + 전체 합계도 나왔다

---

cube는 grouping sets의 기능을 해준다.

SELECT

BRAND, SEGMENT

,sum(QUANTITY)

FROM SALES

GROUP BY

CUBE (BRAND, SEGMENT)

ORDER BY

BRAND, SEGMENT;

---

first value, last value

SELECT

   A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE
 
 , FIRST_VALUE (A.PRICE) OVER 
 
 (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE DESC ) 

​
​ AS LOWEST_PRICE_PER_GROUP

FROM PRODUCT A

INNER JOIN PRODUCT_GROUP B

ON (A.GROUP_ID = B.GROUP_ID);

---

SELECT

A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE

, LAST_VALUE (A.PRICE) OVER 

(PARTITION BY B.GROUP_NAME ORDER BY A.PRICE DESC 

RANGE BETWEEN UNBOUNDED PRECEDING

AND UNBOUNDED FOLLOWING) 

AS LOWEST_PRICE_PER_GROUP

FROM PRODUCT A

INNER JOIN PRODUCT_GROUP B

ON (A.GROUP_ID = B.GROUP_ID);

---

연월 기준 뽑기

SELECT

TO_CHAR(RENTAL_DATE, 'YYYY') ,

TO_CHAR(RENTAL_DATE, 'MM') ,

TO_CHAR(RENTAL_DATE, 'DD')

,count(*) 

FROM

RENTAL

GROUP BY

ROLLUP(

TO_CHAR(RENTAL_DATE, 'YYYY') ,

TO_CHAR(RENTAL_DATE, 'MM') ,

TO_CHAR(RENTAL_DATE, 'DD') ) ;

---

select

A.CUSTOMER_ID 

,row_number () over (order byCOUNT(A.RENTAL_ID) desc) as RENTAL_RANK ,COUNT(*) RENTAL_COUNT

,B.FIRATS_NAME

,B.LAST_NAME

from

RENTAL A ,

CUSTOMER B

where

A.CUSTOMER_ID = B.CUSTOMER_ID

group by

A.CUSTOMER_ID

order by

RENTAL_COUNT desc

limit 1 ;

작동안되는 이유 : group by를 쓰면 집계함수를 사용해야함

select

A.CUSTOMER_ID ,

row_number () over (order by COUNT(A.RENTAL_ID) desc) as RENTAL_RANK ,

COUNT(*) RENTAL_COUNT ,

max(B.first_name) first_name ,

max(B.LAST_NAME) last_name

from

RENTAL A ,

CUSTOMER B

where

A.CUSTOMER_ID = B.CUSTOMER_ID

group by

A.CUSTOMER_ID

order by

RENTAL_COUNT desc

limit 1 ;