sql-tips.md

Table of Contents

• SQL Commands
• Joins
• Common Table Expression

Useful SQL Commands

• alter user MYUSER account unlock - unlock an account
• inline view query

SELECT * 
  FROM ( SELECT deptno, count(*) emp_count
         FROM emp
         GROUP BY deptno ) emp,
       dept
 WHERE dept.deptno = emp.deptno;

• to_date usage - see more examples

TO_DATE('2003/07/09', 'yyyy/mm/dd')

• between dates

create_datetime between TO_DATE('2016/07/25 09:00', 'yyyy/mm/dd HH24:MI') and TO_DATE('2016/07/25 10:50', 'yyyy/mm/dd HH24:MI') 

Joins

• reference: http://www.techonthenet.com/oracle/joins.php

Outer join to select everything from table1 but only matches from table2, otherwise

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

Common Table Expression

• https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
• http://www.morganslibrary.org/reference/with.html

String parsing in SQL

• https://www.periscopedata.com/blog/string-parsing-in-sql.html

Oracle specific

• plsql check to see if a column exists before adding

select count(*) into vRowCount
	from DBA_TAB_COLUMNS
    	where OWNER = '{owner}'
	and TABLE_NAME = '{table}'
	and COLUMN_NAME = '{column}' ;

-- Create the new column
IF vRowCount <= 0 THEN

• tnsping mysid -- check that a SID exists in your configured tnsnames.ora
• kill a session -- todo: fill this out

select username, user#, sid, serial#, inst_id from gv$session where machine =
 
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

• who locked an account - https://community.oracle.com/thread/1016170

select OS_USERNAME, USERNAME , USERHOST, RETURNCODE, TIMESTAMP
from dba_audit_session
where to_date(TIMESTAMP, 'DD-Mon-YY') in (select to_date(TIMESTAMP, 'DD-Mon-YY')
from dba_audit_session
where to_date(TIMESTAMP,'DD-Mon-YY') = to_date(sysdate, 'DD-Mon-YY'))
and RETURNCODE = 28000;

• use dba_errors to find SQL issues:

select name,sequence,line,position,text
from dba_errors
where owner='{schema}'
and name='{object_name}';  -- like a package name for instance

• change user password

ALTER USER user_name IDENTIFIED BY new_password;

• between statement

SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE ('2003/01/01', 'yyyy/mm/dd')
AND TO_DATE ('2003/12/31', 'yyyy/mm/dd');