Oracle

This page will focus on Oracle-specific syntax that won't work with MySQL

ROWNUM

Returning a restricted number of results, in this case 5 rows:

SELECT * from WHERE ROWNUM <= 5;

You can't use ROWNUM = 5 due to how Oracle processes the request, however, if you only want one row returned you can use ROWNUM = 1.

Merge

You want to conditionally insert, update, or delete records in a table depending on whether or not corresponding records exist. If a record exists, then update it, if not, insert it.

merge into emp_commission ec using (Select * for emp) emp on (ec.empno-emp.empno) when matched then update set ec.comm = 1000 delete where (sal < 2000) when not matched then insert (ec.empno,ec.ename,ec.deptno,ec.comm) values(emp.empno,emp.ename,emp.deptno,emp.comm)

All_Tab_Columns

Listing a table's columns select column_name, data_type, column_id from all_tab_colukns where owner = 'SMEAGOL' and table_name = 'EMP'

SUM OVER()

ROLLUP

grouping

TO_CHAR

LAST_DAY

TRUNC

ADD_MONTHS

Dual table

TO_NUMBER

NEXT_DAY

LEAD OVER

LAG OVER

ROW_NUMBER OVER

DENSE RANK()

PARTITION BY

CONNECT BY

WITH

SYS_CONNECT_BY_PATH