Oracle SQL

Analytics

Obtain month:

EXTRACT(month FROM a.birth_date)

Obtain year:

TO_CHAR(a.birth_date, 'YYYY')

Obtain week number:

TO_CHAR(a.birth_date, 'WW')

Get 30 days ago:

ADD_MONTHS(SYSDATE, -1)

Convert a number to a character

TO_CHAR(NUMBER_FIELD)

Concatenate a field

CONCAT('a', 'b')

TO_CHAR(a) || '_' || TO_CHAR(b)

Replace empy data/missing with string

NVL(FIELD, 0)

Convert...

Decode probabilistic into binary

CASE

WHEN FIELD <= 0.5 THEN 0

ELSE 1

END AS FIELD_BINARY

Look for substring

LONG_STRING_COL LIKE '%CAT%'

Datetime

Get SYSDATE

SELECT SYSDATE FROM DUAL

...

Queries

Put data in ascending (and descending) order on one column

SELECT column1 FROM TABLE ORDER BY column1;

SELECT column1 FROM TABLE ORDER BY column1 DESC;

Having clause to select rows

HAVING DATE = MAX(DATE);

Date is a day of the where

SELECT * FROM TABLE WHERE

TO_CHAR(COLUMN, 'DY') = 'SAT' or

TRIM(INITCAP(TO_CHAR(COLUMN, 'DAY'))) = 'Friday'

Get last Friday (on a Friday)

NEXT_DAY(TRUNC(SYSDATE),'FRIDAY') - 7

NEXT_DAY(TRUNC(SYSDATE - 1), 'FRIDAY') - 7

Get first N rows

SELECT * FROM employees WHERE ROWNUM < N;

Use ctes

WITH CTE AS (SELECT * FROM TABLE),

CTE2 AS (SELECT a.* FROM CTE a)

SELECT b.* FROM CTE2 b

Table management

Delete rows from table

DELETE FROM TABLE WHERE X IS MISSING;

Delete a column from a table

ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME, COLUMN_NAME2;

Copy table

CREATE TABLE COPIED_TABLE AS SELECT * FROM TABLETOCOPY;

Create an index

CREATE INDEX IX ON TABLE (COLUMN)

Create view

CREATE OR REPLACE VIEW V_TABLENAME AS ...

DB

See space in a schema

SELECT

tablespace_name

,ROUND (SUM (bytes) / 1024 / 1024 / 1024, 2) GB

FROM dba_segments

WHERE owner = 'SCHEMA'

GROUP BY tablespace_name;

See what jobs are running

SELECT x.sid,

x.serial#,

x.username,

x.sql_id,

x.status,

x.sql_child_number,

optimizer_mode,

hash_value,

address,

sql_exec_start,

sql_text

FROM v$sqlarea sqlarea,

v$session x

WHERE x.sql_hash_value = sqlarea.hash_value

AND x.sql_address = sqlarea.address

AND x.username = 'USERNAME'