뭐든지 다 알아보자

SELECT EMPNO , ENAME , SAL , COUNT(*) OVER (
    ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 100 FOLLOWING) AS C1 FROM EMP
WHERE DEPTNO = 20

SELECT TOP (4) WITH TIES * FROM EMP ORDER BY SAL DESC;

SELECT * FROM (SELECT EMPNO , ENAME , SAL , RANK() OVER (ORDER BY SAL DESC) AS RN FROM EMP)
WHERE RN <=4;

SELECT * FROM
             (SELECT EMPNO, ENAME , SAL , ROW_NUMBER() over (ORDER BY SAL DESC) AS RN FROM EMP)
WHERE RN <=3

SELECT EMPNO, ENAME, SAL FROM EMP
ORDER BY SAL DESC OFFSET 3 ROWS;

SELECT EMPNO, ENAME, SAL FROM EMP
ORDER BY SAL DESC FETCH FIRST 3 ROWS ONLY ;

SELECT EMPNO, ENAME, SAL FROM EMP
ORDER BY SAL DESC FETCH NEXT 10 ROWS WITH TIES;

SELECT A.ENAME , B.EMPNO , B.ENAME FROM EMP A,  EMP B
WHERE A.ENAME = 'JONES'

SELECT A.ENAME , B.EMPNO , B.ENAME FROM EMP A,  EMP B
WHERE A.ENAME = 'JONES' AND B.MGR = A.EMPNO;

SELECT EMPNO, ENAME,  MGR FROM EMP
START WITH ENAME = 'JONES'
CONNECT BY MGR = PRIOR EMPNO

CREATE TABLE EMP (
  JOB varchar2(20) NOT NULL,
  DEPTNO VARCHAR2(10),
  SAL VARCHAR2(9));

INSERT INTO EMP (JOB, DEPTNO, SAL)
SELECT 'ANALYST', 10, 3000 FROM DUAL UNION ALL
SELECT 'CLERK', 10, 1000 FROM DUAL UNION ALL
SELECT 'CLERK', 20, 2000 FROM DUAL UNION ALL
SELECT 'CLERK', 20, 1500 FROM DUAL UNION ALL
SELECT 'ANALYST', 10, 4000 FROM DUAL UNION ALL
SELECT 'CLERK', 20, 1200 FROM DUAL UNION ALL
SELECT 'CLERK', 10, 800 FROM DUAL;

SELECT  * FROM (SELECT JOB , DEPTNO , SAL FROM EMP WHERE
DEPTNO IN (10,20))
PIVOT (SUM(SAL) FOR DEPTNO IN (10,20)) ORDER BY JOB

SELECT REGEXP_SUBSTR('ABC' , 'A.+') AS C1,
       REGEXP_SUBSTR('ABC' , 'A.+?') AS C2
FROM DUAL

SELECT REGEXP_SUBSTR('ABD' , 'AB|CD') AS C1,
       REGEXP_SUBSTR('ABD' , 'A(B|C)D') AS C2
FROM DUAL

SELECT REGEXP_SUBSTR('HTTP://WWW.ABC.COM/EFG' , '[^:/]+',1,2) AS C1 FROM DUAL

SELECT REGEXP_REPLACE('1A2B3C4D' , '\D') AS C1 FROM DUAL

SELECT * FROM T2;

SELECT 1 FROM T2 X;

SELECT * FROM T1;

SELECT DISTINCT A.C1 FROM T1 A

SELECT C1 FROM T1 MINUS SELECT C1 FROM T2;

UPDATE T1
SET C1 = CASE
           WHEN C1 = 1 AND ROWNUM = 1 THEN 1
           WHEN C1 = 1 AND ROWNUM = 2 THEN 2
           ELSE 2
         END;

SELECT ROWNUM, T1.*
FROM T1;

CREATE TABLE T1 (
    C1 VARCHAR2(1),
    C2 DATE,
    C3 NUMBER(1)
);

INSERT INTO T1 (C1, C2, C3) VALUES ('A', TO_DATE('2050-01-01', 'YYYY-MM-DD'), 1);
INSERT INTO T1 (C1, C2, C3) VALUES ('A', TO_DATE('2050-01-02', 'YYYY-MM-DD'), 1);
INSERT INTO T1 (C1, C2, C3) VALUES ('B', TO_DATE('2050-01-01', 'YYYY-MM-DD'), 1);
INSERT INTO T1 (C1, C2, C3) VALUES ('B', TO_DATE('2050-01-02', 'YYYY-MM-DD'), 1);
INSERT INTO T1 (C1, C2, C3) VALUES ('C', TO_DATE('2050-01-01', 'YYYY-MM-DD'), 1);
INSERT INTO T1 (C1, C2, C3) VALUES ('C', TO_DATE('2050-01-02', 'YYYY-MM-DD'), 1);

SELECT C1 , C2 , SUM(C3) AS C3 FROM T1 GROUP BY ROLLUP(C2, C1);

SELECT C1 , C2 , SUM(C3) AS C3 FROM T1 GROUP BY ROLLUP( (C1, C2));

SELECT C1 , C2 , SUM(C3) AS C3 FROM T1 GROUP BY CUBE(C1, C2);

SELECT C1 , C2 , SUM(C3) AS C3 FROM T1 GROUP BY GROUPING SETS ( (C1, C2) , C1 

CREATE TABLE 고객 (
    고객번호 INT,
    고객명 VARCHAR2(50)
);

CREATE TABLE 주문 (
    주문번호 INT,
    고객번호 INT,
    주문금액 INT
);

INSERT INTO 고객 (고객번호, 고객명) VALUES (1, '김대원');
INSERT INTO 고객 (고객번호, 고객명) VALUES (2, '노영미');
INSERT INTO 고객 (고객번호, 고객명) VALUES (3, '김경진');
INSERT INTO 고객 (고객번호, 고객명) VALUES (4, '박하연');

INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2001,1,40000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2002,2,15000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2003,2,7000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2004,2,8000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2005,2,20000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2006,3,5000);
INSERT INTO 주문 (주문번호, 고객번호 , 주문금액) VALUES (2007,3,9000);

SELECT * FROM EMP
WHERE SAL > (SELECT SAL FROM (SELECT SAL, ROW_NUMBER() OVER (ORDER BY  SAL DESC) AS RK FROM EMP WHERE SAL < 1300) WHERE RK = 1);

SELECT EMPNO, ENAME , SAL , DEPTNO FROM EMP
WHERE (DEPTNO , SAL) IN (SELECT DEPTNO,  MAX(SAL) FROM EMP
                                                  GROUP BY DEPTNO);

SELECT SAL , LPAD(SAL, 4 , '0') FROM EMP;

SELECT MAX(LPAD(SAL, 4 , '0') || ENAME) FROM EMP WHERE LPAD(SAL, 4 , '0')=1250;

UPDATE T2
SET C1 = 4
WHERE C1 = 2;

CREATE TABLE T1 (
    C1 INT
);

CREATE TABLE T2 (
    C1 INT
);

CREATE TABLE T3 (
    C1 INT
);
INSERT INTO T1 (C1) VALUES (1);
INSERT INTO T1 (C1) VALUES (2);
INSERT INTO T1 (C1) VALUES (3);


INSERT INTO T2 (C1) VALUES (1);
INSERT INTO T2 (C1) VALUES (2);

INSERT INTO T3 (C1) VALUES (2);
INSERT INTO T3 (C1) VALUES (3);

SELECT C1 FROM T1 UNION ALL SELECT C1 FROM T2

SELECT C1 FROM T1 UNION SELECT C1 FROM T2

SELECT C1 FROM T1 MINUS SELECT C1 FROM T2

SELECT C1 FROM T1 INTERSECT SELECT C1 FROM T2

ALTER TABLE T1 MODIFY C1 VARCHAR2(10);

UPDATE T1 SET C1 = CASE WHEN C1 = 1 THEN 'A'
                        WHEN C1 = 2 THEN 'A'
                        WHEN C1 = 3 THEN 'B'
                   END;

ALTER TABLE T1 ADD C2 NUMBER(10);

UPDATE T1 SET C2 = 1;

ALTER TABLE T1 ADD C2 NUMBER(10);
UPDATE T1 SET C2 = 1;


UPDATE T2 SET C1 = CASE WHEN C1 = 1 THEN 'B'
                        WHEN C1 = 4 THEN 'C'
                   END;

INSERT INTO T2 (C1) VALUES ('C');
ALTER TABLE T2 ADD C2 NUMBER(10);
UPDATE T2 SET C2 = 1;

SELECT * FROM T2;

SELECT C1 , SUM(C2) AS C2 , SUM(C3) AS C3
FROM (SELECT C1 , C2 , NULL AS C3 FROM T1
                                  UNION ALL
                                  SELECT C1 , NULL AS C2 ,C3 FROM T2)
GROUP BY C1 ORDER BY C1;