Programming Practice/SQL 썸네일형 리스트형 개념 1. Foreign key (FK)1) 정의A FOREIGN KEY in 1 table point to a PRIMARY/UNIQUE key in another tableex) EMPLOYEE / DEPARTMENT table 이 있고, DEPARTMENT table 의 Primary Key(PK) 가 department_id 일 때, EMPLOYEE table 의 department_id 는 foreign key가 된다.EMPLOYEE table은 DEPARTMENT table을 참조(reference)한다고 한다. DEPARTMENT table에 데이터가 먼저 들어가야 하므로, DEPARTMENT table은 부모 테이블, EMPLOYEE table은 자식 테이블이 된다. DEPARTMENT tabl.. 더보기 일시 차이 구하기 절차1) 값이 char 라면, date 형태로 변경TO_DATE(값, 'YYYYMMDDHH24MISS')2) date 간 차이 구하기NUMTODSINTERVAL(DATE1 - DATE2, 'DAY')3) 원하는 포맷에 맞춰 SUBSTRSUBSTR(NUMTODSINTERVAL(DATE1-DATE2, 'DAY'), 12,8) 더보기 대용량의 데이터 한꺼번에 테이블에 집어넣기 1. 언제 사용하는가? 특정 프로그램이 여러 로직 처리 및 계산을 해서 특정 테이블에 데이터를 넣는다고 하자. 상황에 따라 특정 프로그램이 구동을 잘못해서 특정 테이블에 입력했던 데이터를 지우고, 프로그램을 통해 다시 입력해야 한다고 하자. 이때 삭제하는 내역은 반드시 여러 형태로 백업을 해놓아야 한다. (csv 파일 형태로 백업을 많이 한다.) 프로그램을 통해 다시 데이터를 입력하는 작업이 제대로 되지 않았을 경우, 백업해놓은 데이터를 다시 테이블에 입력해야 하는데, 그때 사용할 수 있는 유용한 방법이다. 2. 어떻게 사용하는가? 1) 백업할 데이터를 csv 파일로 내린다. 이때 내리는 프로그램에 따라 csv 파일에 테이블 컬럼에 매핑되는 데이터 이외에 row number 등이 추가될 수 있는데, 테이.. 더보기 서버에서 직접 수행한 쿼리 결과를 텍스트 파일로 저장하기 1. 언제 사용하는가?특정 Oracle 서버에 PL/SQL Developer 나 Orange, Toad 와 같은 tool을 이용해 접근이 불가한 경우가 있다.이럴 경우 sqlplus를 이용해 직접 서버에서 쿼리를 수행해야 한다.이때 select 쿼리의 경우, 그 결과가 Terminal 창에 나타나는데, 해당 결과를 텍스트 파일로 내려야 할 때가 있다.아래와 같이 사용한다. 2. 어떻게 사용하는가?1) sqlplus 접속하기sqlplus 계정/패스워드@DB명ex) sqlplus hr/hr@ORCL 2) 쿼리 실행 및 쿼리 결과 저장spool 경로/텍스트파일명.txt@경로/쿼리명.sqlspool off ex) spool ./result.txt terminal 에서 sqlplus에 들어갔던 폴더에 result.. 더보기 특정 기간의 모든 날짜에 대한 데이터 보여주기 특정 기간의 모든 날짜에 대한 데이터 보여줘야 할 때가 있다. 즉, 특정 기간의 모든 날짜에 대해 매핑되는 데이터가 있으면 매핑되는 데이터를 보여주고, 그렇지 않으면 NULL을 보여줘야 할 때가 있다. 아래와 같이 사용한다. SELECT A.HIRE_DATE, B.SUM_SALARY FROM (SELECT TO_CHAR(TO_DATE('20070315', 'YYYYMMDD')+LEVEL-1, 'YYYYMMDD') HIRE_DATE FROM DUAL CONNECT BY LEVEL 더보기 조회된 여러 결과를 같은 COLUMN 에 표시하기 1. 언제 쓰나? 비슷한 성격의 데이터가 각기 다른 테이블에 들어있는데, 그것을 동일한 컬럼에 매핑해서 결과로 보여줘야 할 때 2. 어떻게 쓰나? 조회된 결과를 UNION ALL 한다. 3. 예시 SELECT 'IT_EMPLOYEES' AS DVS , SUM(A.SALARY) AS SALARY_SUM FROM IT_EMPLOYEES A UNION ALL SELECT 'BANKING_EMPLOYEES' , SUM(B.SALARY) FROM BANKING_EMPLOYEES B 더보기 한 ROW씩 조회된 여러 결과를 옆으로 늘려서 표시 1. 어떻게 쓰나? ㄱ. 옆으로 늘려서 표시하고자 하는 컬럼을 정의하고 ㄴ. 조회된 여러 결과에서 옆으로 늘려서 표시하고자 하는 컬럼이 해당되는 경우는 매핑을 하고, 그렇지 않은 경우는 0 으로 설정해서 UNION ALL을 한다. ㄷ. UNION ALL 된 결과를 SUM 한다. 2. 참고사항 조회되는 경우가 늘어날 때마다 쿼리를 수정해야 하기 때문에 유지보수에는 좋지 않다. 3. 예시 SELECT SUM(EMPLOYEE_ID_100) AS EMPLOYEE_ID_100 ,SUM(SALARY_100) AS SALARY_100 ,SUM(EMPLOYEE_ID_101) AS EMPLOYEE_ID_101 ,SUM(SALARY_101) AS SALARY_101 FROM ( SELECT A.EMPLOYEE_ID AS .. 더보기 백업 테이블에 데이터를 입력할 때 SELECT 한 내역을 특정 테이블에 바로 INSERT 1. 어떻게 쓰나? 예시와 같이 사용 2. 참고사항 INSERT 쿼리에서 VALUES 가 없음 3. 예시 CREATE TABLE INSERT_TEST(EMPLOYEE_ID NUMBER, NAME VARCHAR2(80), SALARY NUMBER); INSERT INTO INSERT_TEST(EMPLOYEE_ID, NAME, SALARY) SELECT A.EMPLOYEE_ID, A.FIRST_NAME || ' ' || A.LAST_NAME, SALARY FROM EMPLOYEES A WHERE A.EMPLOYEE_ID < 150; INSERT INTO INSERT_TEST SELECT A.EMPLOYEE_ID, A.FIRST_NAME || ' ' || A.LAST_NAME, SALARY FROM EMPLO.. 더보기 [oracle] 입력과 수정을 동시에 하고 싶을 때 1. 어떻게 쓰나?MERGE INTO 대상 테이블 USING (원본 테이블)ON (조건)WHEN MATCHED THEN merge_update 절WHEN NOT MATCHED THEN merge_insert 절 2. 참고사항1) 대상 테이블, 원본 테이블에 ALIAS 주기2) merge_update 절 : update 대상 table 과 조건이 없음 (table 과 조건은 이미 MERGE 구문에서 명시)3) merge_insert 절 : table 이 없음, values 구문이 특이함 3. 예시 MERGE INTO INSERT_TEST A USING ( SELECT Z.EMPLOYEE_ID, Z.FIRST_NAME || ' ' || Z.LAST_NAME AS NAME, Z.SALARY FROM EMPLO.. 더보기 [oracle]집계 함수 결과를 GROUP BY를 하지 않은 ROW에서 표시하고 싶을 때 1. 어떻게 쓰나?OVER (PARTITION BY 컬럼1, 컬럼2, 컬럼3) 2. 참고사항집계의 기준이 되는 항목들에 동일한 값이 표시된다. 3. 예시 SELECT A.JOB_ID , A.DEPARTMENT_ID , A.MANAGER_ID , SUM(A.SALARY) OVER(PARTITION BY A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID) AS SUM_SALARY FROM EMPLOYEES A ORDER BY A.JOB_ID, A.DEPARTMENT_ID, A.MANAGER_ID; 4. 조회결과 더보기 이전 1 2 다음
