이야봉 블로그

package xml;

import org.jaxen.XPath;
import org.jaxen.dom.DOMXPath;
import org.w3c.dom.Element;
import org.xml.sax.InputSource;

import javax.xml.parsers.DocumentBuilderFactory;
import java.io.StringReader;
import java.util.List;

public class JaxenMain {
    public static void main(String[] args) throws Exception {
        String xml = "<ROOT><META>메타정보</META><BOOKS><BOOK><AUTHOR>BONG</AUTHOR><TITLE>BONG'S STORY</TITLE></BOOK><BOOK><AUTHOR>HONG</AUTHOR><TITLE>HONG'S STORY</TITLE></BOOK><BOOK><AUTHOR>KIM</AUTHOR><TITLE>KIM'S STORY</TITLE></BOOK></BOOKS></ROOT>";

        XPath xpath = new DOMXPath("/ROOT/META");
        List<Element> elements = xpath.selectNodes(DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(new InputSource(new StringReader(xml))).getDocumentElement());

        for (Element element : elements) {
            System.out.println(element.getNodeName());
            System.out.println(element.getTextContent());
        }

       xpath = new DOMXPath("/ROOT/BOOKS/BOOK/AUTHOR");
        elements = xpath.selectNodes(DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(new InputSource(new StringReader(xml))).getDocumentElement());

        for (Element element : elements) {
            System.out.println(element.getNodeName());
            System.out.println(element.getTextContent());
        }

        xpath = new DOMXPath("/ROOT/BOOKS/BOOK/TITLE");
        elements = xpath.selectNodes(DocumentBuilderFactory.newInstance().newDocumentBuilder().parse(new InputSource(new StringReader(xml))).getDocumentElement());

        for (Element element : elements) {
            System.out.println(element.getNodeName());
            System.out.println(element.getTextContent());
        }
    }
}

[참고]

https://openai.com/blog/chatgpt

[쿼리]

SELECT SUBSTRING(A.PRODUCT FROM '\[(.*?)\]') AS PRODUCT_CODE,       
                 A.PRODUCT
FROM
      ( 
      	SELECT '[SM-G981N]갤럭시 S20' AS PRODUCT
        UNION ALL
      	SELECT '[SM-G991N]갤럭시 S21'
        UNION ALL
        SELECT '[SM-S901N]갤럭시 S22'
      ) A

[수행결과]

[참고]

https://popsql.com/learn-sql/postgresql/how-to-use-substring-with-regular-expressions-in-postgresql

http://daplus.net/regex-%EB%8C%80%EA%B4%84%ED%98%B8-%EC%82%AC%EC%9D%B4%EC%97%90%EC%84%9C-%ED%85%8D%EC%8A%A4%ED%8A%B8%EB%A5%BC-%EC%B6%94%EC%B6%9C%ED%95%98%EB%8A%94-%EC%A0%95%EA%B7%9C%EC%8B%9D/

https://namu.wiki/w/%EC%82%BC%EC%84%B1%20%EA%B0%A4%EB%9F%AD%EC%8B%9C/%EC%A0%9C%ED%92%88%20%EB%AA%A9%EB%A1%9D#s-1.1

[Env] 무료 PostgreSQL 호스팅 서비스 - ElephantSQL

[서비스명]

ElephantSQL

https://www.elephantsql.com/

[사용방법]

1. 회원가입(Github 또는 Google 계정으로도 가능)

2. Team 생성

3. 약관동의

4. Instance 추가(생성)

5. 접속정보 확인

6. 접속정보 세팅

https://www.elephantsql.com/​

A.xml

SELECT *
FROM T_01

SELECT *
FROM T_02

SELECT *
FROM T_03

B.xml

SELECT *
FROM V_01

SELECT *
FROM V_02

SELECT *
FROM V_03

C.xml

INSERT INTO T_01
VALUES ('A');

SELECT *
FROM T_02

SELECT *
FROM V_02

추출과정

1. 위 3개의 파일형식을 XML(3개) => TXT(3개)로 변경

D:\FolderName>ren *.xml *.txt

2. TXT파일(3개) => TXT파일(1개)로 병합(ABC.txt)

  bluefive.pairsite.com/txtcollector.htm

3. ABC.txt(병합 완료)

d:\prac\xmlS\A.xml
************************************************************************
<!-- A.xml -->
SELECT *
FROM T_01

SELECT *
FROM T_02

SELECT *
FROM T_03


d:\prac\xmlS\B.xml
************************************************************************
<!-- B.xml -->

SELECT *
FROM V_01

SELECT *
FROM V_02

SELECT *
FROM V_03


d:\prac\xmlS\C.xml
************************************************************************
<!-- C.xml -->

INSERT INTO T_01
VALUES ('A');

SELECT *
FROM T_02

SELECT *
FROM V_02

Java로 추출작업 수행

4. txt파일을 FileReader로 한 Line씩 추출하여, List1에 담기

5. Line별로 시작단어가 T_ 이거나, V_인 경우를 List2에 담기

6. List2에서 중복제거

추출결과

T_01
T_02
T_03
V_01
V_02
V_03

참조링크

 bluefive.pairsite.com/txtcollector.htm

editorizer.tistory.com/240

jeong-pro.tistory.com/69

www.dante2k.com/444

m.blog.naver.com/pgh7092/221130579329

WITH EMPP AS 
(
	SELECT 10 AS DEPTNO, 'CLERK'     AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200  AS BONUS, 3 AS CHLDN FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'MANAGER'   AS JOB, 7782 AS EMPNO, 'CLARK'  AS ENAME, 2450 AS SAL, 100  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING'   AS ENAME, 5000 AS SAL, 0    AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7902 AS EMPNO, 'FORD'   AS ENAME, 3000 AS SAL, 300  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7788 AS EMPNO, 'SCOTT'  AS ENAME, 3000 AS SAL, 600  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7876 AS EMPNO, 'ADAMS'  AS ENAME, 1100 AS SAL, 200  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7369 AS EMPNO, 'SMITH'  AS ENAME, 800  AS SAL, 200  AS BONUS, 4 AS CHLDN FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'MANAGER'   AS JOB, 7566 AS EMPNO, 'JONES'  AS ENAME, 2975 AS SAL, 300  AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'CLERK'     AS JOB, 7900 AS EMPNO, 'JAMES'  AS ENAME, 950  AS SAL, 300  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'MANAGER'   AS JOB, 7698 AS EMPNO, 'BLAKE'  AS ENAME, 2850 AS SAL, 400  AS BONUS, 1 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7521 AS EMPNO, 'WARD'   AS ENAME, 1250 AS SAL, 500  AS BONUS, 0 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0    AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7499 AS EMPNO, 'ALLEN'  AS ENAME, 1600 AS SAL, 300  AS BONUS, 2 AS CHLDN FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS, 3 AS CHLDN FROM DUAL
),
/* NON-PIVOT 결과 */
A AS (
   SELECT DEPTNO,
          JOB,
          SUM(CHLDN) AS CHLDN
   FROM EMPP
   GROUP BY DEPTNO, JOB
),
/* PIVOT 결과 */
B AS 
(
	SELECT *
	FROM 
	(
		SELECT 
		       DEPTNO, -- GROUP BY 
		       JOB,    -- GROUP BY (JOIN 불가)
		       JOB J,  -- JOIN용
		       SAL,    -- SUM(SAL)
		       BONUS   -- SUM(BONUS)
		   FROM EMPP
	)
	PIVOT
	(
		SUM(SAL) AS SAL, 
		SUM(BONUS) AS BNS
		FOR JOB IN ( 'CLERK' AS C, 
                      'ANALYST' AS A,
                       'PRESIDENT' AS P,
                       'SALESMAN' AS S,
                       'MANAGER' AS M
                       )
	) 
) /* End of B */
SELECT A.DEPTNO AS A_DEPTNO,
       A.JOB AS A_JOB,
       A.CHLDN,
       B.*
FROM A,  /* NON-PIVOT 결과 */ 
     B   /* PIVOT 결과 */
WHERE A.DEPTNO = B.DEPTNO
  AND A.JOB = B.J
ORDER BY A_DEPTNO, A_JOB;

참조
http://www.yes24.com/Product/Goods/46218018
http://blog.naver.com/PostView.nhn?blogId=silentis&logNo=220943488844

WITH EMP AS
(
	SELECT 10 AS DEPTNO, 'CLERK'     AS JOB, 7934 AS EMPNO, 'MILLER' AS ENAME, 1300 AS SAL, 200  AS BONUS FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'MANAGER'   AS JOB, 7782 AS EMPNO, 'CLARK'  AS ENAME, 2450 AS SAL, 100  AS BONUS FROM DUAL UNION ALL
	SELECT 10 AS DEPTNO, 'PRESIDENT' AS JOB, 7839 AS EMPNO, 'KING'   AS ENAME, 5000 AS SAL, 0    AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7902 AS EMPNO, 'FORD'   AS ENAME, 3000 AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'ANALYST'   AS JOB, 7788 AS EMPNO, 'SCOTT'  AS ENAME, 3000 AS SAL, 600  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7876 AS EMPNO, 'ADAMS'  AS ENAME, 1100 AS SAL, 200  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'CLERK'     AS JOB, 7369 AS EMPNO, 'SMITH'  AS ENAME, 800  AS SAL, 200  AS BONUS FROM DUAL UNION ALL
	SELECT 20 AS DEPTNO, 'MANAGER'   AS JOB, 7566 AS EMPNO, 'JONES'  AS ENAME, 2975 AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'CLERK'     AS JOB, 7900 AS EMPNO, 'JAMES'  AS ENAME, 950  AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'MANAGER'   AS JOB, 7698 AS EMPNO, 'BLAKE'  AS ENAME, 2850 AS SAL, 400  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7521 AS EMPNO, 'WARD'   AS ENAME, 1250 AS SAL, 500  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7844 AS EMPNO, 'TURNER' AS ENAME, 1500 AS SAL, 0    AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7499 AS EMPNO, 'ALLEN'  AS ENAME, 1600 AS SAL, 300  AS BONUS FROM DUAL UNION ALL
	SELECT 30 AS DEPTNO, 'SALESMAN'  AS JOB, 7654 AS EMPNO, 'MARTIN' AS ENAME, 1250 AS SAL, 1400 AS BONUS FROM DUAL
)
SELECT DEPTNO, 
       JOB,
       SUM(SAL) AS SUM_SAL,              /* (부서 & 직함별) 급여 합 */
       SUM(CASE WHEN JOB = 'ANALYST'     /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
            THEN SAL
       END) AS SUM_SAL_ANALYST,
       SUM(CASE WHEN JOB = 'CLERK'        /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
                THEN SAL
       END) AS SUM_SAL_CLERK,
       SUM(CASE WHEN JOB = 'MANAGER'      /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
                THEN SAL
       END) AS SUM_SAL_MANAGER,
       SUM(CASE WHEN JOB = 'SALESMAN'     /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
       	        THEN SAL
       END) AS SUM_SAL_SALESMAN, 
       SUM(CASE WHEN JOB = 'PRESIDENT'    /* (부서 & 직함별) 급여 합 : 행 => 열 변환 */
                THEN SAL
       END) AS SUM_SAL_PRESIDENT,
       SUM(BONUS) AS SUM_BONUS,          /* (부서 & 직함별) 보너스 합 */
       COUNT(*) AS CNT                   /* (부서 & 직함별) 인원 수 */
FROM EMP 
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB

WITH EMP AS
(
	...
    ...
    ...
)
SELECT DEPTNO, 
       JOB,
       SUM(SAL) AS SUM_SAL,             						 /* (부서 & 직함별) 급여 합 - NON-PIVOT */       
       SUM(DECODE(JOB, 'ANALYST', SAL)) AS SUM_SAL_ANALYST,      /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'CLERK', SAL)) AS SUM_SAL_CLERK,          /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'MANAGER', SAL)) AS SUM_SAL_MANAGER,      /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'SALESMAN', SAL)) AS SUM_SAL_SALESMAN,    /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(DECODE(JOB, 'PRESIDENT', SAL)) AS SUM_SAL_PRESIDENT,  /* (부서 & 직함별) 급여 합 - PIVOT(DECODE) */
       SUM(BONUS) AS SUM_BONUS,          						 /* (부서 & 직함별) 보너스 합 - NON-PIVOT */
       COUNT(*) AS CNT                  						 /* (부서 & 직함별) 인원 수  - NON-PIVOT */
FROM EMP 
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB

WITH EMP AS
(
   ...
   ...
   ...
)
SELECT DEPTNO, 
     --  JOB,   /* JOB을 그룹핑 제외 */
       SUM(SAL) AS SUM_SAL,              
       SUM(CASE WHEN JOB = 'ANALYST'    
            THEN SAL
       END) AS SUM_SAL_ANALYST,
          ...
          ...
          ...
       SUM(BONUS) AS SUM_BONUS,         
       COUNT(*) AS CNT                  
FROM EMP 
GROUP BY DEPTNO --,  JOB    /* JOB을 그룹핑 제외 */
ORDER BY DEPTNO --,  JOB    /* JOB을 그룹핑 제외 */

쿼리

SELECT GRADE       AS "학년",
       DEPTNO1     AS "학과",
       COUNT(*)    AS /* (학년 or 학과별) */ "인원 수",
       SUM(HEIGHT) AS /* (학년 or 학과별) */ "키의 합계",
       SUM(WEIGHT) AS /* (학년 or 학과별) */ "몸무게의 합계"
FROM STUDENT 
GROUP BY GROUPING SETS(GRADE, DEPTNO1)
ORDER BY GRADE, DEPTNO1

책 링크
booksr.co.kr/www.yes24.com/Product/Goods/46218018

SQL 공부를 위해 구입한 책

「SQL의 컨셉 for ORACLE」의 실습용 모델 ERD

영문으로 테이블과 컬럼을 생성한 후,  책에 제시된 한글로 COMMENT를 작성.

책 링크
「SQL의 컨셉 for ORACLE」