개키우는개발자 : )

테이블 구조 변경 한번 만들어진 테이블 이여도 테이블 구조를 변경 할 수 있습니다. 이 기능으로 인해 업무변화에 유연하게 대처할 수 있습니다. 실습 - LINKS 테이블 생성 CREATE TABLE LINKS ( LINK_ID SERIAL PRIMARY KEY, TITLE VARCHAR(512) NOT NULL, URL VARCHAR(1024) NOT NULL UNIQUE ); - LINKS 테이블에 ACTIVE 컬럼을 추가 ALTER TABLE LINKS ADD COLUMN ACTIVE BOOLEAN; - LINKS 테이블에 ACTIVE 컬럼을 제거 ALTER TABLE LINKS DROP COLUMN ACTIVE; - TITLE 컬럼을 LINK_TITLE 컬럼으로 변경 ALTER TABLE LINKS..

CTAS 란 CREATE TABLE AS SELECT 의 약어로써 SELECT 문을 기반으로 CREATE TABLE을 할 수 있는 CREATE문이다. 기본문법 - 새로운 테이블명의 이름을 작성 후 SELECT 문을 작성합니다. CREATE TABLE NEW_TABLE AS SELECT - 새로운 테이블명의 이름과 컬럼명을 작성 후 SELECT 문을 작성합니다. CREATE TABLE NEW_TABLE(NEW_COLUMN_1, NEW_COLUMN_2) AS SELECT - 기존의 테이블이 존재하지 않는 경우만 생성되며 생성된 후 SELECT 문을 작성합니다. CREATE TABLE IF NOT EXISTS NEW_TABLE AS SELECT 실습 - FILM 테이블과 FILM_CATEGORY 테이블을 조인..

테이블 생성 테이블은 데이터를 담는 그릇으로써 반드시 생성해야만 데이터를 저장 할 수 있습니다. 테이블 생성시 컬럼의 제약 조건 제약조건명 설명 NOT NULL 해당 제약 조건이 있는 컬럼은 NULL이 저장될 수 없습니다. UNIQUE 해당 제약 조건이 있는 컬럼의 값은 테이블 내에서 유일해야 합니다.(중복X) PRIMARY KEY 해당 제약 조건이 있는 컬럼의 값은 테이블내에서 유일해야 하고 반드시 NOT NULL 이어야 합니다. CHECK 해당 제약 조건이 있는 컬럼은 지정하는 조건에 맞는 값이 들어가야 합니다. REFERENCES 해당 제약 조건이 있는 컬럼의 값은 참조하는 테이블의 특정 컬럼에 값이 존재해야 합니다. 실습 - ACCOUNT_TEST 테이블 생성 1. USER_ID는 기본키 이고 N..

데이터 타입 테이블은 컬럼으로 이루어져 있고 컬럼은 다양한 데이터 타입을 지원합니다. 이는 RDBMS가 제 역할을 하는데 있어서 매우 중요한 부분입니다. Boolean, Character, Numeric 데이터 타입 설명 Boolean Boolean 참(true)과 거짓(false)의 값을 저장 Character CHAR 고정형 길이의 문자열을 저장. 만약CHAR(10)인데 'ABCDE'만 입력할 경우 실제로는 'ABCDE '로 뒤에 공백을 붙여 저장 됩니다. VARCHAR 가변형 길이의 문자열을 저장합니다. 만약 VARCHAR(10) 인데, 'ABCDE'만 입력할 경우 실제로 'ABCDE'만 저장합니다.( 공백X ) TEXT 대용량의 문자데이터를 저장합니다. Numeric INT 정수형 데이터를 저장합..

IMPORT 작업 IMPORT는 다른 형식의 데이터를 테이블에 넣는 작업을 말합니다. 데이터 구축시 자주 사용됩니다. 실습준비 - CATEGORY_IMPORT 테이블을 생성합니다. CREATE TABLE CATEGORY_IMPORT ( CATEGORY_ID SERIAL NOT NULL, "NAME" VARCHAR(25) NOT NULL, LAST_UPDATE TIMESTAMP NOT NULL DEFAULT NOW(), CONSTRAINT CATEGORY_IMPORT_PKEY PRIMARY KEY (CATEGORY_ID) ); 실습 - CATEGORY 테이블에서 EXPORT한 DB_CATEGORY_2.csv 파일을 CATEGORY_IMPORT 테이블로 IMPORT합니다. COPY CATEGORY_IMPOR..

EXPORT 작업 이란 EXPORT는 테이블의 데이터를 다른 형태의 데이터로 추출 하는 작업입니다. 대표적으로 CSV 형식으로 가장 많이 추출합니다. 실습 - C드라이브에 TMP폴더 생성 후 CATEGORY 테이블의 데이터를 csv형식 파일로 저장합니다. COPY CATEGORY(CATEGORY_ID,NAME,LAST_UPDATE) TO 'C:\TMP\DB_CATEGORY.csv' DELIMITER ',' CSV HEADER; TMP 디렉토리는 반드시 있어야 합니다. - TXT형식으로 저장 COPY CATEGORY(CATEGORY_ID,NAME,LAST_UPDATE) TO 'C:\TMP\DB_CATEGORY.txt' DELIMITER '|' CSV HEADER; - 컬럼 없이 csv형식으로 저장 COPY..

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..