Oracle

This page will focus on Oracle-specific syntax

ROWNUM

Returning a specified number of rows.

Example of ROWNUM

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.

All_Tab_Columns

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

OVER()

The OVER analytic_clause is used to operate on a query result set. It is computed last, so after the different grouping/where/having clauses, the over clause is processed. You can specify analytic functions with this clause in the select list or ORDER BY clause. Can use OVER with a number of different analytic functions (SUM, AVG, etc). And you can add clauses inside the () of over, such as PARTITION. If you leave the parentheses empty, then the OVER clause will apply over the entire results set.

Syntax of OVER()

( [PARTITION BY <...>] [ORDER BY <....>] [<window_clause>] )

Examples

SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary

This returns the salary of the reports for each manager

SELECT empno, deptno, sal, AVG(sal) OVER () AS avg_sal

EMPNO DEPTNO SAL AVG_SAL

---

7369 20 800 2073.21429 7499 30 1600 2073.21429

This example returns the same value for AVG_SAL for each row

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

MODEL