Sequence in SQL | PART 2

In Oracle, "sequencing" can be applied in different contexts depending on the type of operation or function you're performing. Below are various types of sequencing used in Oracle, along with simple examples:

1. Sequence Objects

A sequence is an Oracle object used to generate unique numbers in sequence, typically for use in primary keys or similar purposes.

Example: Creating a Sequence

CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1;

This will create a sequence starting from 1 and incrementing by 1 every time it is called.

Example: Using a Sequence

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (emp_seq.NEXTVAL, 'John', 'Doe');

Here, emp_seq.NEXTVAL generates the next number in the sequence, which can be used for the employee_id.

1. Ordering Data with ORDER BY

You can control the order in which rows are retrieved from the database using the ORDER BY clause. The ordering can be done in ascending or descending order.

Example: Ordering by Salary in Ascending Order

SELECT employee_id, first_name, salary
FROM employees
ORDER BY salary ASC;

Example: Ordering by Last Name in Descending Order

SELECT employee_id, first_name, last_name
FROM employees
ORDER BY last_name DESC;

1. ROWNUM and Pagination

ROWNUM is a pseudocolumn that assigns a unique number to each row in the result set, based on the sequence in which Oracle processes the query.

Example: Limiting Results to the First 5 Rows

SELECT employee_id, first_name, salary
FROM employees
WHERE ROWNUM <= 5;

This query will return the first 5 rows from the result set.

1. Ranking and Windowing Functions

Oracle provides windowing functions like ROW_NUMBER(), RANK(), and DENSE_RANK() to sequence rows within partitions or groups of data.

Example: Using ROW_NUMBER() for Row Sequencing

SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

This query assigns a row number (row_num) based on the descending order of salaries.

Example: Using RANK() for Ranking Rows

SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Here, RANK() assigns a rank to each row based on the salary, with ties (i.e., employees with the same salary) receiving the same rank.

1. Sequence in PL/SQL

In PL/SQL, sequences can be combined with procedural logic to control the flow and order of execution.

Example: Using a Sequence in a PL/SQL Block

DECLARE
new_emp_id NUMBER;
BEGIN
new_emp_id := emp_seq.NEXTVAL;
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (new_emp_id, 'Jane', 'Smith');
END;

This block generates a new employee ID using the sequence and inserts a new employee.

1. Sequencing in SQL with LEAD() and LAG()

These analytic functions allow you to access data from subsequent or preceding rows, enabling complex row-based sequencing and calculations.

Example: Using LEAD() to Find the Next Employee's Salary

SELECT employee_id, salary, LEAD(salary, 1) OVER (ORDER BY salary ASC) AS next_salary
FROM employees;

LEAD() returns the salary of the next employee in the sequence.

Example: Using LAG() to Find the Previous Employee's Salary

SELECT employee_id, salary, LAG(salary, 1) OVER (ORDER BY salary ASC) AS previous_salary
FROM employees;

LAG() returns the salary of the previous employee in the sequence.

1. Sequence of Operations in SQL Queries

Oracle processes SQL statements in a particular sequence, known as the logical query processing order. Although queries are written in a specific way, Oracle internally processes them in a different sequence:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

Example: Query with Logical Sequence

SELECT department_id, COUNT()
FROM employees
WHERE salary > 5000
GROUP BY department_id
HAVING COUNT() > 10
ORDER BY department_id;

1. Trigger Sequencing

Triggers in Oracle can be used to sequence or control the execution of certain operations before or after database actions (e.g., BEFORE INSERT, AFTER DELETE).

Example: Sequencing with Triggers

CREATE OR REPLACE TRIGGER emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.employee_id := emp_seq.NEXTVAL;
END;

This trigger ensures that every new employee inserted gets a unique employee_id from the sequence.

Summary of Sequencing Types:

Sequence Objects: Auto-generate unique numbers.

ORDER BY: Sort data in ascending or descending order.

ROWNUM: Limit and sequence rows.

Ranking Functions (ROW_NUMBER(), RANK()): Rank rows within partitions.

PL/SQL Sequence: Use sequence logic in procedural blocks.

Analytic Functions (LEAD(), LAG()): Sequence data between adjacent rows.

Logical Processing Order: Internal sequence in which SQL queries are executed.

Trigger Sequencing: Automate and control data sequencing using triggers.

Each of these types of sequencing is useful for organizing and manipulating data efficiently in Oracle SQL.