Group By

group by attr1, attr2

group by ROLLUP(attr1, attr2)

group by CUBE(attr1, attr2)

Rank, Dense_RANK, ROW_number:

RANK() OVER (ORDER BY PRICE) as "PR",
ROW_NUMBER() OVER (ORDER BY PRICE) as "PR"
DENSE_RANK() OVER (ORDER BY C DESC NULLS LAST) as "R"
DENSE_RANK() OVER (PARTITION by C ORDER BY P) as "PR"
RANK() OVER (ORDER BY P) as "PR"
avg(C) OVER() AS "AC"
MIN(Y) KEEP (DENSE_RANK FIRST ORDER BY Y) as "FirstItem"
MIN(Y) KEEP (DENSE_RANK LAST ORDER BY Y) as "LASTItem"

Hierarchical Query:

START WITH employee_id = 102
CONNECT BY FOLLOWING m_id = e_id;

Keep First or Last Row

KEEP (DENSE_RANK FIRST ORDER BY …)
KEEP (DENSE_RANK LAST ORDER BY …)

PARTITION BY on RANK/Dense_Rank

RANK()
RANK() OVER
RANK() OVER PARTITION BY
DENSE RANK() OVER
DENSE RANK() OVER PARTITION BY
PARTITION BY …. ORDER BY
ROW_NUMBER() OVER (ORDER By …)
ROWS BETWEEN Unbounded Preceding and CURRENT ROW
RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND INTERVAL '5' DAY Following
ROWS BETWENN 1 PRECEDING and 1 FOLLOWING
DENSE RANK() OVER (PARTITION BY .....)
DENSE RANK() OVER (PARTITION BY .....ORDER BY ...)