개키우는개발자 : )

UPSERT 문 INSERT를 시도할때 조건(상황)에 따라 UPDATE를 할 수 있는 구문입니다. 복잡한 업무 처리에 자주 사용 됩니다. 기본문법 - INSERT가 충돌 시 다른 액션을 취합니다. INSERT INTO TABLE_NAME(COLUMN_1) VALUES(VALUE_1) ON CONFLICT TARGET ACTION; 실습준비 - CUSTOMERS 테이블 생성 후 데이터 저장 CREATE TABLE CUSTOMERS ( CUSTOMER_ID SERIAL PRIMARY KEY, NAME VARCHAR UNIQUE, EMAIL VARCHAR NOT NULL, ACTIVE BOOL NOT NULL DEFAULT TRUE ); INSERT INTO CUSTOMERS (NAME,EMAIL) VALUE..

DELETE 문 테이블의 특정 데이터를 삭제하거나 테이블 내에서 존재하는 모든 데이터를 삭제 할 수 있습니다. 기본문법 삭제할 테이블 작성 후 조건 입력 DELETE FROM TARGET_TABLE A WHERE 조건식; 실습 - LINK테이블의 ID가 5인 행을 삭제 DELETE FROM LINK WHERE ID = 5; COMMIT; - DELETE JOIN 사용하여 LINK_TMP 테이블과 LINK 테이블의 ID값으로 조인 후 삭제 DELETE FROM LINK_TMP A USING LINK B WHERE A.ID = B.ID; COMMIT; 중복값 데이터 삭제 - LINK테이블의 모든 데이터를 삭제 DELETE FROM LINK; COMMIT; - 경고 문구를 확인하면 모든데이터는 삭제가 됩니다.

UPDATE JOIN 문 UPDATE시 다른 테이블의 내용을 참조 하고 싶을 때 UPDATE JOIN 문을 사용 합니다. 복잡한 업무를 처리하는데 매우 유용한 방법 입니다. 기본문법 - UPDATE할 테이블을 작성 후 특정 컬럼을 UPDATE합니다. 참조 테이블을 작성하고 JOIN조건을 입력합니다. UPDATE TARGET_TABLE A SET A.COLUMN_1 = 표현식 FROM REF_TABLE B WHERE A.COLUMN_1 = B.COLUMN_1; 실습준비 - PRODUCT_SEGMENT 테이블 생성 후 데이터 저장 CREATE TABLE PRODUCT_SEGMENT ( ID SERIAL PRIMARY KEY, SEGMENT VARCHAR NOT NULL, DISCOUNT NUMERIC (4,..

UPDATE 문법 테이블의 존재하는 데이터를 수정하는 작업이다. 업무를 처리하는데 필수적인 것이며 동시성에 유의해야 한다. 기본문법 - 업데이트 할 테이블을 작성하고 수정할 컬럼과 데이터를 입력 후 조건을 입력 UPDATE TABLE_NAME SET COLUMN_1 = VALUE1, COLUMN_2 = VALUE2 WHERE 조건; - UPDATE는 대상 행에 대해서 락을 잡습니다. - 락이란 다른 사용자는 해당 행에 대해서 작업을 할 수 없습니다. - UPDATE를 한 후 빠르게 COMMIT을 하지 않는다면 RDBMS의 동시성이 낮아집니다. - SELECT 에 명시적(선택적)으로 락을 잡을 수 있습니다. 예를 들어 인터넷으로 영화를 예매 할 때 여러 인원이 제한된 좌석을 선택하는 상황이라면 동시에 A좌..

INSERT 란? 테이블이 만들어지면 빈 공간이 만들어 지는 것이며 테이블 안에 데이터를 저장하는것 입니다. 기본문법 - 테이블 이름만 작성하면 테이블에 포함되어 있는 컬럼 순서대로 입력 INSERT INTO TABLE_NAME VALUES ( VALUE1, VALUE2, VALUE3, ... ); - 테이블의 특정 컬럼을 작성하면 컬럼을 지정하여 저장할 수 있습니다. INSERT INTO TABLE_NAME ( COLUMN1, COLUMN2 ) VALUES ( VALUE1, VALUE2 ); 실습준비 - LINK 테이블 만들기 CREATE TABLE LINK( ID SERIAL PRIMARY KEY, URL VARCHAR(255) NOT NULL, NAME VARCHAR(255) NOT NULL, DE..

실습문제 - SQL문은 EXCEPT 연산을 사용하여 재고가 없는 영화를 구하고 있습니다. 해당 SQL문은 EXCEPT연산을 사용하지 말고 같은 결과를 조회 하십시오. 문제 SQL SELECT FILM_ID, TITLE FROM FILM EXCEPT SELECT DISTINCT INVENTORY.FILM_ID, TITLE FROM INVENTORY INNER JOIN FILM ON FILM.FILM_ID = INVENTORY.FILM_ID ORDER BY TITLE; 재고가 없는 영화를 조회합니다. 첫번째 방법 SELECT A.FILM_ID, A.TITLE FROM FILM A WHERE NOT EXISTS ( SELECT 1 FROM INVENTORY B, FILM C WHERE B.FILM_ID = C..

실습문제 - FILM 테이블을 2번이나 스캔하고 있다. FILM 테이블을 한번만 SCAN하여 동일한 결과 집합을 구하는 SQL을 작성하세요. 문제 SQL SELECT FILM_ID, TITLE, RENTAL_RATE FROM FILM WHERE RENTAL_RATE > ( SELECT AVG(RENTAL_RATE) FROM FILM ) 1. 평균값을 집계함수를 활용하여 AVG값을 보여줍니다. 2. 인라인 뷰 서브쿼리를 활용하여 FILM 테이블 A가 됩니다. 3. 조건을 넣어 원하는 결과값을 조회 합니다. SELECT FILM_ID, TITLE, RENTAL_RATE FROM ( SELECT FILM_ID, TITLE, RENTAL_RATE, AVG(A.RENTAL_RATE) OVER() AS AVG_RE..

EXISTS 연산자란? 서브쿼리 내에 집합이 존재하는지 존재 여부만을 판단한다. 존재 여부만을 판단하므로 연산 시 부하가 줄어든다. 실습 - CUSTOMER 테이블 에서 지불내역이 있고 11달러를 초과한 고객을 조회합니다. SELECT FIRST_NAME, LAST_NAME FROM CUSTOMER C WHERE EXISTS ( SELECT 1 FROM PAYMENT P WHERE P.CUSTOMER_ID = C.CUSTOMER_ID AND P.AMOUNT > 11 ) ORDER BY FIRST_NAME, LAST_NAME 해당 집합이 존재하기만 하면 더이상 연산을 멈추므로 성능상 유리합니다. - CUSTOMER 테이블 에서 지불내역이 있고 11달러를 초과한 적이 없는 고객을 조회합니다. SELECT F..

ALL 연산자란? 값을 서브 쿼리에 의해 반환된 값 집합과 비교한다. ALL 연산자는 서브쿼리의 모든 값이 만족을 해야만 조건이 성립된다. 실습 - FILM 테이블의 영화 분류별 상영시간이 가장 긴 영화의 모든 상영시간 보다 크거나 같아야만 조건 성립, 영화 분류별 상영시간이 가장 긴 상영시간을 구합니다. SELECT TITLE, LENGTH FROM FILM WHERE LENGTH >= ALL ( SELECT MAX(LENGTH) FROM FILM A, FILM_CATEGORY B WHERE A.FILM_ID = B.FILM_ID GROUP BY B.CATEGORY_ID ) 상영시간이 가장 긴 영화는 185분 인것을 알 수 있습니다. ALL이 없으면 ERROR가 납니다. - FILM 테이블의 평가 기준..

ANY 연산자란? 값을 서브 쿼리에 의해 반환된 값 집합과 비교한다. ANY 연산자는 서브쿼리의 값이 어떠한 값이라도 만족을 하면 조건이 성립됩니다. 실습 - FILM 테이블의 영화 분류별 상영시간이 가장 긴 영화의 제목 및 상영시간을 조회합니다. 상영시간이 가장 긴 상영 시간을 구합니다. SELECT TITLE, LENGTH FROM FILM WHERE LENGTH >= ANY ( SELECT MAX(LENGTH) FROM FILM A, FILM_CATEGORY B WHERE A.FILM_ID = B.FILM_ID GROUP BY B.CATEGORY_ID ) 만약 ANY 연산자가 없다면 에러를 발생합니다. SELECT TITLE, LENGTH FROM FILM WHERE LENGTH >= ( SELEC..

서브쿼리 란? 서브쿼리는 SQL문 내에서 메인 쿼리가 아닌 하위에 존재하는 쿼리를 말하며, 서브쿼리를 활용함으로써 다양한 결과를 도출 할 수 있다. 실습 - FILM 테이블의 RENTAL_RATE 의 평균값을 구하고, 계산된 평균값 보다 큰 RENTAL_RATE 의 값을 조회합니다. 1. FILM 테이블의 RENTAL_RATE컬럼의 평균값을 구하기. SELECT AVG(RENTAL_RATE) FROM FILM; 2. RENTAL_RATE의 평균 보다 큰 RENTAL_RATE 구하기 SELECT FILM_ID, TITLE, RENTAL_RATE FROM FILM WHERE RENTAL_RATE > 2.98; 3. 2개의 SQL을 결합하여 하나의 SQL문으로 두번째의 결과값과 같은 결과를 조회합니다. 중첩 ..

EXCEPT 연산 이란? 맨위에 SELECT문의 결과 집합에서 그 아래에 있는 SELECT문의 결과 집합을 제외한 결과를 반환합니다. LEFT ONLY 기본문법 두개의 SELECT문 간 컬럼의 개수는 동일해야 하고 해당 순서의 열에는 서로 호환되는 데이터 유형 이어야 합니다. SELECT COLUMN_1_1, COLUMN_1_2 FROM TABLE_NAME_1 EXCEPT SELECT COLUMN_2_1, COLUMN_2_2 FROM TABLE_NAME_2 ORDER BY ... 실습 - INVENTORY 테이블과 FILM테이블을 FILM_ID 컬럼 기준으로 INNER JOIN 합니다. SELECT DISTINCT INVENTORY.FILM_ID, TITLE FROM INVENTORY INNER JOIN..

INTERSECT 연산 이란? 두 개 이상의 SELECT 문들의 결과 집합을 하나의 결과 집합으로 결합 합니다. INTERSECT 연산자에 의해 조회된 결과는 A와B의 교집합의 값을 반환 합니다. 기본문법 두개의 SELECT문 간 컬럼의 개수는 동일해야 하며 해당 순서의 열에는 서로 호환되는 데이터 유형 이어야 합니다. ORDER BY로 정렬하고자 할 경우 맨 마지막 SELECT문에 ORDER BY절을 사용합니다. SELECT COLUMN_1_1, COLUMN_1_2 FROM TABLE_NAME_1 INTERSECT SELECT COLUMN_2_1, COLUMN_2_2 FROM TABLE_NAME_2 실습 - KEYS 테이블의 EMPLOYEE_ID컬럼 기준 값과 HIPOS 테이블의 EMPLOYEE_ID컬..

UNION 이란? 두 개 이상의 SELECT 문들의 결과 집합을 단일 결과 집합으로 결합하며 결합 시 중복된 데이터는 제거된다. 기본 문법 두 개의 SELECT문 간 컬럼의 개수는 동일해야 하며 해당 순서의 열에는 서로 호환되는 데이터 유형 이어야 합니다. 두 개의 SELECT문에서 중복되는 데이터 값이 있다면 중복은 제거된다. ORDER BY로 정렬하고자 할 경우 맨 마지막 SELECT문에 ORDER BY 절을 사용한다. SELECT COLUMN_1_1, COLUMN_1_2 FROM TABLE_NAME_1 UNION SELECT COLUMN_2_1, COLUMN_2_2 FROM TABLE_NAME_2; 실습 - SALES2007_1 , SALES2007_2 UNION 연산 조회 SELECT * FROM..

실습준비1 SALES2007_1 테이블 만들기 CREATE TABLE SALES2007_1 ( NAME VARCHAR(50), AMOUNT NUMERIC(15,2) ); SALES2007_1 데이터 저장 INSERT INTO SALES2007_1 VALUES ('MIKE',15000.25), ('JON',132000.75), ('MARY',10000) commit; SALES2007_2 테이블 만들기 CREATE TABLE SALES2007_2 ( NAME VARCHAR(50), AMOUNT NUMERIC(15,2) ); SALES2007_2 데이터 저장 INSERT INTO SALES2007_2 VALUES ('MIKE',120000.25), ('JON',142000.75), ('MARY',10000)..

RENTAL과 CUSTOMER 테이블을 이용하여 현재까지 가장 많이 RENTAL을 한 고객의 고객ID, 렌탈순위, 누적렌탈 횟수, 이름까지 출력하세요. SELECT * FROM RENTAL; SELECT * FROM CUSTOMER; - MAX , WHERE SELECT ROW_NUMBER() OVER ( ORDER BY COUNT(A.RENTAL_ID) DESC) AS RENTAL_RANK , MAX(B.FIRST_NAME) AS FIRST_NAME, MAX(B.LAST_NAME) AS LAST_NAME, A.CUSTOMER_ID, COUNT(*) RENTAL_COUNT FROM RENTAL A, CUSTOMER B WHERE A.CUSTOMER_ID = B.CUSTOMER_ID GROUP BY A.C..

RENTAL 테이블을 이용하여 연, 연월, 연월일, 전체 각각의 기준으로 RENTAL_ID 기준 렌탈이 일어난 횟수를 출력하세요. (전체 데이터 기준으로 모든 행을 출력합니다) SELECT * FROM RENTAL; - 연별 조회 SELECT TO_CHAR(RENTAL_DATE, 'YYYY'), COUNT(*) FROM RENTAL GROUP BY TO_CHAR(RENTAL_DATE, 'YYYY') ORDER BY TO_CHAR(RENTAL_DATE, 'YYYY') - 연월 조회 SELECT TO_CHAR(RENTAL_DATE, 'YYYYMM'), COUNT(*) FROM RENTAL GROUP BY TO_CHAR(RENTAL_DATE, 'YYYYMM') ORDER BY TO_CHAR(RENTAL_DAT..

실습 테이블 만들기 https://dog-developers.tistory.com/154 LAG,LEAD 함수 특정 집합 내에서 결과 건수의 변화 없이 해당 집합안에서 특정 컬럼의 이전 행의 값 혹은 다음 행의 값을 구합니다. 실습 LAG 1. PRODUCT 테이블과 PRODUCT_GROUP 테이블을 GROUP_ID 기준으로 INNER JOIN 합니다. 2. PRICE의 이전 행의 값을 구합니다. 3. 현재 행에서 이전행의 PRICE값을 뺍니다. 4. GROUP_NAME 컬럼 기준으로 PRICE컬럼으로 정렬 합니다. SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE, LAG(A.PRICE, 1) OVER (PARTITION BY B.GROUP_NAME ORDER BY A.P..

실습 테이블 만들기 https://dog-developers.tistory.com/154 ROW_NUMBER, RANK, DENSE_RANK 특정 집합 내에서 결과 건수의 변화 없이 해당 집합안에서 특정 컬럼의 순위를 구하는 함수입니다. 실습 ROW_NUMBER - 집합 내의 순위를 GROUP_NAME 컬럼 기준으로 구하고 GROUP_NAME 기준의 각 순위는 PRICE 컬럼 기준으로 정렬 합니다. SELECT A.PRODUCT_NAME, B.GROUP_NAME, A.PRICE, ROW_NUMBER () OVER (PARTITION BY B.GROUP_NAME ORDER BY A.PRICE) FROM PRODUCT A INNER JOIN PRODUCT_GROUP B ON (A.GROUP_ID = B.GR..

실습 테이블 만들기 https://dog-developers.tistory.com/154 AVG 란? 분석함수 AVG() 부터 본격적으로 분석함수를 알아 볼 수 있습니다. 기본문법 - 사용하고자 하는 분석함수를 쓰고 대상 컬럼을 기재 후 PARTITION BY에서 값을 구하는 기준 컬럼을 작성 후 ORDER BY에서 정렬 컬럼을 기재합니다. SELECT C1, 분석함수(C2,C3,...) OVER(PARTITION BY C4 ORDER BY C5) FROM TABLE_NAME; 실습 - PRODUCT 테이블의 전체 PRICE 평균 구하기. select AVG (PRICE) from PRODUCT; - PRODUCT_GROUP 테이블과 PRODUCT 테이블을 GROUP_ID컬럼 기준으로 INNER JOIN..

실습 테이블 만들기 https://dog-developers.tistory.com/154 분석 함수란? 특정 집합 내에서 결과 건수의 변화 없이 해당 집합안에서 합계 및 카운트 등을 계산할 수 있는 함수 이다. 실습 - PRODUCT 테이블의 데이터 개수를 조회 SELECT COUNT(*) FROM PRODUCT; 데이터의 리스트의 개수를 조회한다 - PRODUCT 테이블의 데이터와 개수를 같이 조회 SELECT COUNT(*) OVER(), A.* FROM PRODUCT A; 모든 개수와 데이터를 조회합니다.

실습준비 1 PRODUCT_GROUP 테이블 생성 CREATE TABLE PRODUCT_GROUP ( GROUP_ID SERIAL PRIMARY key, GROUP_NAME VARCHAR (255) NOT NULL ) PRODUCT_GROUP 데이터 저장 INSERT INTO PRODUCT_GROUP (GROUP_NAME) VALUES ('SMARTPHONE'), ('LAPTOP'), ('TABLET') commit; PRODUCT 테이블 만들기 CREATE TABLE PRODUCT ( PRODUCT_ID SERIAL PRIMARY KEY, PRODUCT_NAME VARCHAR(255) NOT NULL, PRICE DECIMAL (11,2), GROUP_ID INT NOT NULL, FOREIGN KEY..

CUBE 란? 지정된 GROUPING 컬럼의 다차원 소계를 생성하는데 사용된다. 간단한 문법으로 다차원 소계를 출력할 수 있다. 기본문법 - CUBE절에서 다차원 소계를 할 열을 지정한다. (지정한 그룹의 모든 경우에 수에 대한 소계와 총계를 구한다.) SELECT C1,C2,C3, 집계함수(C4) FROM TABLE_NAME GROUP BY CUBE (C1, C2, C3); - 특정 컬럼만 분리,선택하여 CUBE 지정을 할 수 있다. SELECT C1,C2,C3, 집계함수(C4) FROM TABLE_NAME GROUP BY C1, CUBE (C2, C3); CUBE 절은 GROUPING SETS 절로 표현하면 총 8개의 소계가 발생합니다. CUBE(C1,C2,C3) -> GROUPING SETS ( (..

ROLL UP 이란? 지정된 GROUPING 컬럼의 소계를 생성하는데 사용된다. 간단한 문법으로 다양한 소계를 출력합니다. 기본문법 소계 「명사」 : 전체가 아닌 어느 한 부분만을 셈한 합계. - 소계를 생성할 컬럼을 지정한 후 순서에 따라 결과값이 달라질 수 있다. (제일 앞에 놓인 것에 소계를 구함) SELECT C1,C2,C3, 집계함수(C4) FROM TABLE_NAME GROUP BY ROLLUP (C1,C2,C3); - 특정 컬럼을 제외한 부분적,선택적 ROLLUP도 가능 합니다. SELECT C1,C2,C3, 집계함수(C4) FROM TABLE_NAME GROUP BY C1 ROLLUP (C2, C3); 실습 - SALES 테이블의 BRAND,SEGMENT 컬럼 기준으로 ROLL UP을 합니..

실습테이블 만들기 https://dog-developers.tistory.com/140 GROUPING SET 이란? 여러 개의 UNION ALL을 이용한 SQL과 같은 결과를 도출할 수 있다. 실습 - SALES 테이블을 BRAND 와 SEGMENT 컬럼 기준으로 GROUP BY 합니다. SELECT BRAND, SEGMENT, SUM (QUANTITY) FROM SALES GROUP BY BRAND, SEGMENT; 일반적인 조회와 값이 별다른 변화가 없습니다. - SALES 테이블을 BRAND 컬럼 기준으로 GROUP BY 합니다. SELECT BRAND, SUM (QUANTITY) FROM SALES GROUP BY BRAND; 그룹화된 BRAND 의 QUANTITY의 합계를 보여줍니다. - SA..