sql-sample.txt

SELECT * FROM TAB

-----------------------------------------------------------------------------------------------------------

INSERT INTO TABLENAME VALUE ('TESTVALUE')

-----------------------------------------------------------------------------------------------------------

UPDATE TABLENAME 
SET COLNAME = 1
WHERE CONDITION = 2

-----------------------------------------------------------------------------------------------------------

DELETE FROM TABLE
WHERE CONDITION = 'ALL'

-----------------------------------------------------------------------------------------------------------

DELETE FROM TABLE
WHERE CONDITION = 'ALL'
AND VALUE = (SELECT VALUE FROM TAB)

-----------------------------------------------------------------------------------------------------------

SELECT ORG_ID, ORG_NM 
FROM OM_ORGANIZATION
WHERE ORG_LEVEL = '3'
AND (TO_CHAR(SYSDATE, 'YYYYMMDD') BETWEEN ORG_STA_YMD AND END_YMD)
ORDER BY ORG_SORT

-----------------------------------------------------------------------------------------------------------

SELECT  
EC_CLPS_DV_CD as STAFF_GBN,
EC_NM as STAFF_CLASS,
EC_GRP as ORG_STAFF_GBN,
ODR as ORDER_SEQ,
ORGZ_ID as ORG_ID,
EMP_NO as STAFF_EMP_ID,
USE_AYN as USE_YN,
REG_DTM,
RGR_ID,
UPDT_DTM,
UTUR_ID
FROM ETCH005M
WHERE EC_CLPS_DV_CD IN
(
    SELECT EC_DV as staff_gbn
    FROM ETCH004M
    WHERE
    (
        TO_DATE (SCH_REG_DT , 'YYYY-MM-DD') BETWEEN TO_DATE (SYSDATE, 'YYYY-MM-DD') AND
        TO_DATE (SYSDATE, 'YYYY-MM-DD')
    )
    GROUP BY EC_DV
    UNION SELECT EC_CLPS_DV_CD as staff_gbn
    FROM ETCH005M
    WHERE NVL (USE_AYN, 'Y') <> 'N'
)
ORDER BY ODR

-----------------------------------------------------------------------------------------------------------

SELECT EC_DV as staff_gbn
FROM ETCH004M
WHERE
(
    TO_DATE (SCH_REG_DT , 'YYYY-MM-DD') BETWEEN TO_DATE (SYSDATE, 'YYYY-MM-DD') AND
    TO_DATE (SYSDATE, 'YYYY-MM-DD')
)
GROUP BY EC_DV
UNION SELECT EC_CLPS_DV_CD as staff_gbn
FROM ETCH005M
WHERE NVL (USE_AYN, 'Y') <> 'N'

-----------------------------------------------------------------------------------------------------------

SELECT  
REG_NO AS REGIS_SEQNO,
to_char(REG_DTM,'yyyy-MM-dd') AS REGIS_DT,
to_char(REG_DTM,'HH24miss') AS REGIS_TM,
to_char(UPDT_DTM,'yyyyMMdd') AS MODIFY_DT,
to_char(UPDT_DTM,'HH24miss') AS MODIFY_TIME,
RGR_ID AS REGIS_ID,
RGR_NM AS REGIS_NAME,
ANW_NM AS REPLY_NAME,
ANW_ID AS REPLY_ID,
ANS_DT AS REPLY_DT,
TXT AS CONTENTS,
NVL(ATC_FL, '') AS ACCTFILE,
NTT_TYP AS BOARD_TYPE,
TITL AS TITLE,
INQ_CNT AS CNT,
COUNT(*) OVER() AS TOTAL_CNT
FROM ETCH002M
WHERE MASKING_YB = 'Y'

------------------------------------------

UPDATE ETCH002M
SET MASKING_YB = 'N'
WHERE MASKING_YB = 'Y'

-----------------------------------------------------------------------------------------------------------

UPDATE etch002m
SET updt_dtm = SYSDATE,
utur_id = 0912026,
anw_nm = '',
anw_id = '',
ans_dt = '',
txt = '<p>testㅏ</p><p><br></p><p>asd,asdfaasd~!@#%@W%sdfsdfsdd""""''''</p>',
atc_fl = '',
titl = test
WHERE reg_no = 24831;

-----------------------------------------------------------------------------------------------------------

INSERT INTO etob004m
(
    tsk_no, dept_cd, tsk_dv_cd, tsk_nm, oppb_gd_cd, txt, stt_dtm,
    end_dtm, rgr_id, reg_dtm, utur_id, updt_dtm
)
VALUES
(
    '2019-00436', '015000', '001', 'test', '001', '<p>123</p>', TO_DATE ('2019-08-08 09:00:00', 'yyyy-MM-dd HH24:mi:ss'),
    TO_DATE ('2019-08-08 18:00:00', 'yyyy-MM-dd HH24:mi:ss'), '1507030', SYSDATE, '1507030', SYSDATE
)

-----------------------------------------------------------------------------------------------------------

DELETE FROM ETCH006M 
WHERE EMP_NO IN ('123', '124', '125', '126', '127', '128')

-----------------------------------------------------------------------------------------------------------

DELETE FROM ETCH009M
WHERE PK_SEQ = '2019-00370'

-----------------------------------------------------------------------------------------------------------

SELECT
ROWNUM ROW_NUMBER,
seq,
TO_CHAR (regdate, 'YYYY-MM-DD HH24:mi') AS regdate,
content,
subject,
send_email,
send_flag,
send_name,
dept_code,
dept_name,
list_seq,
email,
NAME,
send_date
FROM
(
    SELECT
    a.seq,
    a.regdate,
    a.content,
    a.subject,
    a.send_email,
    a.send_flag,
    a.send_name,
    a.dept_code,
    a.dept_name,
    b.list_seq,
    b.email,
    b.NAME,
    b.send_date
    FROM
    ev_send_form a INNER JOIN ev_send_list b ON a.seq = b.seq
    ORDER BY a.seq DESC, list_seq
)

-----------------------------------------------------------------------------------------------------------

SELECT COUNT (*)
FROM etch013m
WHERE rgr_id = '1507030' AND
reg_no = NVL
(
    (
        SELECT reg_no
        FROM etch002m
        WHERE masking_yb = 'Y'
    ), -1
) AND
reg_dtm + 7 > SYSDATE

-----------------------------------------------------------------------------------------------------------

SELECT COUNT (*)
FROM etch013m
WHERE rgr_id = '1507030'
AND reg_no = NVL
(
    (
        SELECT reg_no
        FROM etch002m
        WHERE masking_yb = 'Y'
    ), -1
) AND
reg_no2 = NVL
(
    (
        SELECT reg_no
        FROM etch002m
        WHERE masking_yb = 'Y'
    ), -1
)

-----------------------------------------------------------------------------------------------------------

SELECT COUNT (*)
FROM
(
    SELECT *
    FROM etch002m
    WHERE masking_yb = 'Y'
)
WHERE rgr_id = '1507030' AND
reg_no = NVL
(
    (
        SELECT reg_no
        FROM etch002m
        WHERE masking_yb = 'Y'
    ), -1
) AND
reg_no2 = NVL
(
    (
        SELECT reg_no
        FROM etch002m
        WHERE masking_yb = 'Y'
    ), -1
)

-----------------------------------------------------------------------------------------------------------

SELECT A, B FROM
(
    SELECT A, B
    FROM FROMTABLE
    WHERE SUBCONDITION = 'SUBCONDITION'
)
WHERE C =
(
    SELECT C
    FROM WHERETABLE
    WHERE WHERECONDITION = 'WHERECONDITION'
)
ORDER BY A

-----------------------------------------------------------------------------------------------------------

SELECT AAA.EQUIP_ID, C.SESSION_ID, E.COLLECT_ITEM_ID
FROM
(
    SELECT A.EQUIP_ID, A.SECTOR_ID, B.EQUIP_MODEL_ID
    FROM TB_EQUIP A
    INNER JOIN TB_EQUIP_MODEL B ON A.EQUIP_MODEL_ID = B.EQUIP_MODEL_ID
    INNER JOIN TB_EQUIP_TYPE C ON B.EQUIP_TYPE_ID = C.EQUIP_TYPE_ID
    WHERE A.USE_YN = 'Y' AND
    C.EQUIP_TYPE_ID = 'equipTypeId' AND
    A.SECTOR_ID =
    (
        SELECT SECTOR_ID FROM TB_SECTOR
        WHERE CONTAINER_NAME = 'containerName' AND
        GATEWAY = 'gateway' AND
        CONTAINER = 'container'
    )
) AAA
LEFT OUTER JOIN TB_RESOURCE C ON AAA.SECTOR_ID = C.SECTOR_ID
INNER JOIN TB_EQUIP_ITEM D ON AAA.EQUIP_MODEL_ID = D.EQUIP_MODEL_ID
INNER JOIN TB_EQUIP_COLLECT_ITEM E ON D.COLLECT_ITEM_ID = E.COLLECT_ITEM_ID

-----------------------------------------------------------------------------------------------------------

SELECT A.a, C.b, E.c FROM
(
    SELECT A.a, A.select_id, B.id
    FROM table A
    INNER JOIN joinTable B ON A.id = B.id
    INNER JOIN joinTable2 C ON B.id2 = C.id2
    WHERE A.yn = 'Y' AND C.id2 = 'id' AND
    A.select_id =
    (
        SELECT select_id
        FROM selector_table
        WHERE c_name = 'con_name' AND
        gateway = 'gateway' AND
        CONTAINER = 'container'
    )
) A
LEFT OUTER JOIN table_resource C ON A.select_id = C.select_id
INNER JOIN item D ON A.id = D.id
INNER JOIN table_item E ON D.c = E.c

-----------------------------------------------------------------------------------------------------------

SELECT DISTINCT VAL FROM
(
    (
        SELECT FIELD1 AS VAL
        FROM TABLE1
        WHERE CONDITION1 = 'CONDITION1'
    ) UNION ALL
    (
        SELECT FIELD2
        FROM TABLE1
        WHERE CONDITION2 = 'CONDITION2'
    ) UNION ALL
    (
        SELECT FIELD3
        FROM TABLE3
        WHERE CONDITION3 = 'CONDITION3'
    ) UNION ALL
    (
        SELECT FIELD3
        FROM TABLE3
        WHERE CONDITION3 = 'CONDITION3'
    )
) T

-----------------------------------------------------------------------------------------------------------

SELECT * FROM
(
    SELECT A, B, C
    FROM SUBQUERYTABLE
    WHERE CONDITION IN ('A', 'B', 'C') AND
    CONDITION IN
    (
        SELECT CONDITION FROM ANOTHER
        UNION
        SELECT CONDITION FROM UNIONTABLE
    )
)
UNION ALL
(
    SELECT * FROM TAB2
    UNION
    SELECT * FROM TAB3
)