SQL

SELECT

SELECT column (as something)
FROM table
WHERE somgthing -- name != 'CHA' AND number > 1 OR NOT number = 1; 
                -- column IN(1, 2, 3); column BETWEEN date AND date;
                -- column LIKE '%CHA%'; column like '_CHA_';
ORDER BY something; -- asc / desc

SELECT DISTINCT column -- Deduplication
FROM table;

SELECT column
FROM table
GROUP BY column
HAVING something;

STRUCTURE

DESC table; -- Check structure

FUNCTION

-- NUMBER FUNCTION
dual table -- dumy table

ABS(n) -- return absolute value

FLOOR(n) -- Return an integer equal to or less than

CEIL(n) -- Return an integer equal to or larger than

ROUND(n, i) -- Round n from decimal i+1 position

TRUNC(n, i) -- Discard n in decimal i+1 position

POWER(n1, n2) -- As a result of n1 being repeated n2 times,

MOD(n1, n2) -- the remaining value of n1 divided by n2

-- CHARACTER FUNCTION
INITCAP(char) /* Convert only the first character (excluding spaces and numbers) of Char 
                to upper case letters and the rest to lower case letters. */
                
LOWER(char) -- Convert char to lowercase
                
UPPER(char) -- Convert char to uppercase
                
CONCAT(char1, char2) -- Attach char1 and char2
                
SUBSTR(char, pos, len) -- Return cut by length from posth letter of char
          
TRIM() -- clearance removal

LTRIM(char1, char2) -- find and return char2 from the left 
                    -- side of char1 and delete it from char1

RTRIM(char1, char2)

LPAD(char1, n ,char2) -- Fill char2 from left of char1 until n-digit number.

RPAD(char1, n, char2) -- Find char2 in char1 and change it to char3.

LENGTH(char)

LENGTHB(char) -- Return the Byte Length of the Char

DECODE(input1, search1, result1, search2, result2, ..., default) -- be similar to if sentence

-- DATE FUNCTION
SYSDATE -- return Syetem date

ADD_MONTHS(date, int) -- Returns the date plus the number of months in the date

MONTHS_BETWEEN(date1, date2) -- Returns the number of months between two dates, based on date1

LAST_DAY(date)

ROUND(date, format) -- Returns the date rounded to format

TRUNC(date, format)

NEXT_DAY(date, char) -- Returns the date of the week specified in char by date

-- TRANSFORM FUNCTION
TO_CHAR(char or date, format)

TO_NUMBER(data, format)

TO_DATE(char, format)

TO_TIMESTAMP(char, format)

/*
 YYYY
 YY
 MM
 DD
 DDD
 D
 HH24
 HH12
 MI
 SS */

-- NULL FUNCTION
NVL(input1, input2) -- If input1 is NULL, return input2.

NVL2(input1, input2, input3) -- If input1 is NULL, return input2.
                             -- If input1 is not NULL, return input3.
                             
LNNVL() -- Returns TRUE if the result of the conditional expression is 
        -- FALSE or UNKNOWN, or FALSE if the result is TRUE.

NULLIF(input1, input2) -- If input1 and input2 are the same value, return NULL 
                       -- and if different, return input1.

-- GROUP FUNCTION
COUNT()

SUM()

AVG()

MAX()

MIN()

JOIN

-- EQUI JOIN
SELECT ?
FROM ?, ?
WHERE ? = ?;

-- INNER JOIN
SELECT ?
FROM ?, ?
ON ? = ?;

-- NATURAL JOIN
SELECT ?
FROM ? NATURAL JOIN ?
USING (?);

-- NON EQUI JOIN
SELECT ?
FROM ?, ?
WHERE ? BETWEEN ? AND ?;

-- SELF JOIN
SELECT ?
FROM ? a, ? b
WHERE a = b;

-- LEFT OUTER JOIN
SELECT ?
FROM ?, ?
WHERE ? = ?(+);

-- RIGHT OUTER JOIN
SELECT ?
FROM ?, ?
WHERE ?(+) = ?;

-- FULL OUTER JOIN
SELECT ?
FROM ? FULL OUTER JOIN ?
ON ? = ?;

SUBQUERY

SELECT ?
FROM ?
WHERE ? IN(=) ( SELECT
                FROM
                WHERE);

DDL

CREATE TALBE ? (
    ? VARCHAR2(20) PRIMARY KEY, 
    ? NUMBER(20)
);

CREATE TABLE ?
AS SELECT ?
FROM ?
WHERE ?;

DROP TABLE ? WHERE ? = ?;

ALTER TABLE ADD(MODIFY, DROP) CONSTRAINTNAME (?); -- Unable to change to a small value when modified

DML

INSERT INTO table VALUES();

INSERT INTO table (column name, ...) VALUES(...);

INSERT ALL INTO table [(column name, ...)] VALUES(...)
SELECT ? FROM ?;

UPDATE (table name) SET (column name)=? WHERE ?;

MERGE INTO ? a
USING ? b
ON (a.? = b.?)
WHEN MATCHED THEN
	UPDATE SET a.? = a.?+b.?
WHEN NOT MATCHED THEN
	INSERT VALUES(...);

INTEGRITY

ALTER TABLE ? ADD CONSTRAINT constraint_name ?(column_name);

ALTER TABLE ? CASCADE CONSTRAINT;

ALTER TABLE ? drop ?;

ALTER TABLE ? DISABLE CONSTRAINT ?;

ALTER TABLE ? ENABLE CONSTRAINT ?;

CREATE TABLE ?(
    ? CONSTRAINT ? CHECK(? BETWEEN ? AND ?),
    ? DEFAULT ?
);

VIEW

CONNECT SYSTEM/MANAGER
GRANT CREATE VIEW TO ?;
CONN ?

CREATE VIEW ? AS SELECT * FROM ?;

CREATE VIEW ?
AS
SELECT ?, ?
FROM ? a, ? b
WHERE ?.a=?.b;

DROP VIEW ?;

ROWNUM

SELECT rownum, ?
FROM (SELECT rownum, ?
	    FROM ?
    	ORDER BY ? asc);

SEQUENCE

CREATE SEQUENCE ?;

INSERT INTO ? VALUES(?.NEXTVAL);

SELECT ?CURRVAL FROM dual;

DROP SEQUENCE ?;