Aggregate Functions

SELECT col1, FUNC(col2), FUNC(FUNC(col3))
FROM tableName
WHERE col1 = 0;

Print

dbms_output.put_line('string of text'||theColNameVar) 

Count

SELECT COUNT(*)
FROM tableName;
#returns the number of none empty rows

SELECT COUNT(*)
FROM tableName
WHERE col = 1;

Sum

SELECT SUM(col)
FROM tableName;
# gives sum of all rows in specified column

Max and Min

SELECT MAX(col)
FROM tableName;
# returns max num from the row

SELECT MIN(col)
FROM tableName;
# returns min num from the row

Average

SELECT AVG(col)
FROM tableName;
# returns avg of col

Round

SELECT ROUND(col, 0)
FROM tableName;
# note 0 is the number of decimal places col is rounded to
# doesnt effect table value

To char (print system date in any format)

SELECT to_char(sysdate, 'DD-MM-YYYY HH24:MI:SS') as REQ_DATE from dual;

SELECT to_char(sysdate, 'Day-Month-Year HH24:MI:SS') as REQ_DATE from dual;

to_char(number, '999,999.99')

Format

format(number, numberOfDecimalPlaces)

Replace

SELECT REPLACE('SQL Tutorial', 'T', 'M');
# result: SQL MuMorial

isNull

isNull(colName) # returns 1 if not null 0 if null

Replacing Null values

NVL2( string1, value_if_not_null, value_if_null )

Extract

SELECT EXTRACT(MONTH FROM "2017-06-15");

Roll up + Cube

# groups by all conbinations of colName1 and colName 2
GROUP BY ROLLUP(colName1, colName2);

# groups by all conbinations of colName1 and colName 2
GROUP BY CUBE(colName1, colName2);