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'