Oracle Database SQL Certified Associate
1 Introduction to SQL
1-1 Overview of SQL
1-2 History of SQL
1-3 SQL Standards
2 SQL Data Types
2-1 Numeric Data Types
2-2 Character Data Types
2-3 Date and Time Data Types
2-4 Large Object (LOB) Data Types
2-5 Miscellaneous Data Types
3 Creating and Managing Tables
3-1 Creating Tables
3-2 Modifying Tables
3-3 Dropping Tables
3-4 Table Constraints
3-5 Temporary Tables
4 Data Manipulation Language (DML)
4-1 Inserting Data
4-2 Updating Data
4-3 Deleting Data
4-4 Selecting Data
4-5 Using Subqueries
5 Data Control Language (DCL)
5-1 Granting Privileges
5-2 Revoking Privileges
6 Data Definition Language (DDL)
6-1 Creating Tables
6-2 Altering Tables
6-3 Dropping Tables
6-4 Creating Indexes
6-5 Dropping Indexes
6-6 Creating Views
6-7 Dropping Views
7 SQL Functions
7-1 Single-Row Functions
7-2 Aggregate Functions
7-3 Group Functions
7-4 Analytical Functions
8 Joins and Subqueries
8-1 Inner Joins
8-2 Outer Joins
8-3 Self-Joins
8-4 Cross Joins
8-5 Subqueries
9 Set Operators
9-1 UNION
9-2 UNION ALL
9-3 INTERSECT
9-4 MINUS
10 Grouping and Aggregation
10-1 GROUP BY Clause
10-2 HAVING Clause
10-3 ROLLUP and CUBE
10-4 GROUPING SETS
11 Transactions and Concurrency
11-1 Transaction Control Statements
11-2 Locking and Concurrency
11-3 Isolation Levels
12 Oracle SQL Developer
12-1 Overview of Oracle SQL Developer
12-2 Using SQL Worksheet
12-3 Managing Connections
12-4 Running Scripts
13 Advanced SQL Topics
13-1 Recursive Queries
13-2 Model Clause
13-3 PIVOT and UNPIVOT
13-4 Flashback Query
14 Performance Tuning
14-1 Query Optimization
14-2 Indexing Strategies
14-3 Analyzing Query Performance
15 Security and Auditing
15-1 User Management
15-2 Role Management
15-3 Auditing SQL Statements
16 Backup and Recovery
16-1 Backup Strategies
16-2 Recovery Strategies
16-3 Using RMAN
17 Oracle Database Architecture
17-1 Overview of Oracle Database Architecture
17-2 Memory Structures
17-3 Process Structures
17-4 Storage Structures
18 PLSQL Basics
18-1 Introduction to PLSQL
18-2 PLSQL Block Structure
18-3 Variables and Data Types
18-4 Control Structures
18-5 Exception Handling
19 Oracle SQL Certification Exam Preparation
19-1 Exam Objectives
19-2 Sample Questions
19-3 Practice Tests
19-4 Exam Tips
Analytical Functions in Oracle SQL

Analytical Functions in Oracle SQL

Key Concepts

Analytical functions in Oracle SQL are powerful tools that perform calculations across a set of table rows. Unlike aggregate functions, analytical functions return multiple rows for each group. Understanding the following key concepts is essential for effectively using analytical functions:

1. OVER Clause

The OVER clause is used to define the window of rows that the analytical function operates on. It specifies the partitioning and ordering of rows.

2. PARTITION BY Clause

The PARTITION BY clause divides the result set into partitions, similar to the GROUP BY clause in aggregate functions. Analytical functions are applied to each partition independently.

3. ORDER BY Clause

The ORDER BY clause within the OVER clause specifies the order of rows within each partition. This is crucial for functions that depend on the sequence of rows, such as RANK and LAG.

4. ROWS/RANGE Clause

The ROWS or RANGE clause defines the window frame within the partition. It specifies the range of rows to include in the calculation, such as the current row and a certain number of preceding or following rows.

Detailed Explanation

1. OVER Clause

The OVER clause is the foundation of analytical functions. It defines the window of rows that the function operates on. For example, to calculate the running total of sales:

SELECT SalesDate, SalesAmount, SUM(SalesAmount) OVER (ORDER BY SalesDate) AS RunningTotal FROM Sales;

2. PARTITION BY Clause

The PARTITION BY clause divides the result set into partitions. For example, to calculate the running total of sales for each department:

SELECT Department, SalesDate, SalesAmount, SUM(SalesAmount) OVER (PARTITION BY Department ORDER BY SalesDate) AS RunningTotal FROM Sales;

3. ORDER BY Clause

The ORDER BY clause within the OVER clause specifies the order of rows within each partition. For example, to rank employees based on their sales:

SELECT EmployeeID, SalesAmount, RANK() OVER (ORDER BY SalesAmount DESC) AS SalesRank FROM Sales;

4. ROWS/RANGE Clause

The ROWS or RANGE clause defines the window frame within the partition. For example, to calculate the moving average of sales over the last 3 days:

SELECT SalesDate, SalesAmount, AVG(SalesAmount) OVER (ORDER BY SalesDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAverage FROM Sales;

Examples and Analogies

Example 1: Running Total

Imagine you are tracking the cumulative sales over time. The running total is like adding up the sales each day to see how much has been sold so far.

SELECT SalesDate, SalesAmount, SUM(SalesAmount) OVER (ORDER BY SalesDate) AS RunningTotal FROM Sales;

Example 2: Rank

Ranking employees based on their sales is like giving them a position in a sales competition. The employee with the highest sales gets rank 1, the next gets rank 2, and so on.

SELECT EmployeeID, SalesAmount, RANK() OVER (ORDER BY SalesAmount DESC) AS SalesRank FROM Sales;

Example 3: Moving Average

Calculating the moving average of sales over the last 3 days is like smoothing out the daily fluctuations to see the general trend in sales.

SELECT SalesDate, SalesAmount, AVG(SalesAmount) OVER (ORDER BY SalesDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAverage FROM Sales;