UNIT-2 Notes

Sequence:
A sequence in SQL is a database object that generates a series of unique numbers, typically
used to create auto-incrementing values like primary keys. SQL sequences are an essential
feature of relational database management systems (RDBMS) used to generate unique
numeric values in a sequential order. These values are widely used for generating primary
keys, unique keys, and other numeric identifiers in databases. SQL sequences offer
flexibility, performance, and ease of use, making them indispensable in managing and
organizing data in large-scale applications.

Syntax

CREATE SEQUENCE sequence_name

START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CYCLE|NOCYCLE ;

Creating a Sequence in Ascending Order

CREATE SEQUENCE sequence_1

start with 1
increment by 1
minvalue 0
maxvalue 100
cycle;

Creating a Sequence in Descending Order

CREATE SEQUENCE sequence_2

start with 100
increment by -1
min value 1
max value 100
cycle;

 Oracle and PostgreSQL support sequences natively.

 MySQL does not use sequences — it uses AUTO_INCREMENT instead.

 SQL Server supports sequences from version 2012 onward.

1. Referencing a Sequence
Once a sequence is created, you use it to generate values.

Syntax: -- Oracle / PostgreSQL

SELECT sequence_name.NEXTVAL FROM dual; - Oracle

SELECT NEXTVAL('sequence_name'); - PostgreSQL

SELECT NEXT VALUE FOR sequence_name; - SQL Server

 NEXTVAL: Returns the next value and increments the sequence.

 CURRVAL: Returns the current value (Oracle, PostgreSQL only), after NEXTVAL is
called.

Example:

-- Create table

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

Name VARCHAR(100)

);

-- Create sequence

CREATE SEQUENCE emp_seq

START WITH 1000

INCREMENT BY 1;

-- Insert using sequence

INSERT INTO Employees (EmployeeID, Name)

VALUES (emp_seq.NEXTVAL, 'Alice'); -- Oracle

INSERT INTO Employees (EmployeeID, Name)

VALUES (NEXTVAL('emp_seq'), 'Bob'); -- PostgreSQL

Altering a Sequence:

You can modify a sequence’s properties, such as its increment value, cache size, min/max
values, etc.
✅ Syntax:

-- Oracle / PostgreSQL

ALTER SEQUENCE emp_seq

INCREMENT BY 5

MAXVALUE 10000

CACHE 20;

-- SQL Server

ALTER SEQUENCE emp_seq

RESTART WITH 2000

INCREMENT BY 2

MAXVALUE 9999;

3. Dropping a Sequence

To remove a sequence from the database:

-- All major RDBMS

Syntax DROP SEQUENCE sequence name

Ex DROP SEQUENCE emp_seq;

Snapshot
SQL snapshots are a recent read-only copy of the table from the database or a subset of
rows/columns of a table. The SQL statement that creates and subsequently maintains a
snapshot normally reads data from the source database server.

A snapshot (or materialized view) is a database object that stores the results of a query
physically, unlike a regular view which stores only the query definition.

This allows you to:

 Improve performance for complex joins and aggregations

 Work with data offline or asynchronously

 Reduce load on remote or large table

Create Snapshot in SQL

 CREATE SNAPSHOT snapshot_name AS
SELECT * FROM your_source_table;

Example

CREATE SNAPSHOT emp_snap

AS SELECT * FROM emp;

Complex Snapshot

In complex snapshot, a row may be based on more than one row in a remote table
via GROUP BY operation or result of Multi-Table Join. This consists of joined tables, views, or
grouped and complex SELECT statement queries.

CREATE SNAPSHOT sampleSnps1

AS SELECT student.rollno, student.name

FROM student

UNION ALL

SELECT new_student.rollno, new_student.name

FROM new_student;

Benefits of Using a Snapshot (Materialized View)

 Improved performance: Faster queries on large datasets since the result is

precomputed.

 Reduced load: Decreases the load on the underlying tables and allows for better
performance when reporting.

 Offline access: You can use the snapshot even when the base tables are not available
(depending on refresh frequency).

Altering a Snapshot (Materialized View)

You can alter a snapshot (materialized view) to modify properties like refresh behavior,
storage options, or even the query that defines it.

Syntax to Alter a Snapshot

ALTER snapshot snapshot_name

REFRESH FAST -- Change refresh method to fast
START WITH SYSDATE -- Change refresh start time
NEXT SYSDATE + 7 -- Change refresh interval (e.g., weekly refresh)
CACHE -- Enable caching for improved performance

Example

ALTER snapshot emp_snapshot

REFRESH COMPLETE -- Change to complete refresh (rebuilds the entire snapshot)
START WITH SYSDATE -- Keep the immediate refresh
NEXT SYSDATE + 2 -- Refresh every 2 days
CACHE -- Enable caching for faster access

Dropping a Snapshot (Materialized View)

If a snapshot is no longer needed, it can be removed from the database. This will delete the
materialized view and free up the associated storage.

Syntax to Drop a Snapshot (Materialized View):

DROP snapshot snapshot_name;

Example DROP snapshot emp_snapshot;

What Is User ID in SQL?

In SQL, User ID can refer to either:

1. A column in a user-defined table that stores a user's unique identifier (e.g., in a

Users table).

2. A system function or variable that returns the ID of the current database

user/session (database-level context).

Syntax -- MySQL

CREATE TABLE Users (

UserID INT AUTO_INCREMENT PRIMARY KEY,

Username VARCHAR(100),

Email VARCHAR(100));

PRIVILEDGES –
The authority or permission to access a named object as advised manner, for example,
permission to access a table. Privileges can allow permitting a particular user to connect to
the database. In, other words privileges are the allowance to the database by the database
object. In SQL, privileges (also called permissions) are rights or authorizations given to users
or roles that determine what actions they can perform on database objects.

Types:

System privileges — A system privilege is the right to perform an activity on a specific type
of object. for example, the privilege to delete rows of any table in a database is system
privilege. There are a total of 60 different system privileges. System privileges allow users to
CREATE, ALTER, or DROP the database objects.

Examples (especially in Oracle or SQL Server):

 CREATE TABLE

 CREATE USER

 ALTER ANY TABLE

 DROP ANY VIEW

Object privilege — An object privilege is a privilege to perform a specific action on a

particular table, function, or package. For example, the right to delete rows from a table is
an object privilege. Object privilege allows the user to INSERT, DELETE, UPDATE, or SELECT
the data in the database object.

Granting Priviledge

Granting privileges means giving permission to a user or role to perform specific actions on
database objects (such as tables, views, procedures)

SYNTAX GRANT privilege_name

ON object_name

TO user_or_role;

EXAMPLE GRANT SELECT, UPDATE

ON Employees

TO John;

This allows user John to read (SELECT) and add (INSERT) data into the Employees table.

Revoking Privileges-
Revoking privileges means taking away previously granted permissions from a user or role to
restrict their access to database objects.

Syntax REVOKE privilege_name

ON object_name

FROM user_or_role;

Example -- Revoke UPDATE permission from John on Employees table

REVOKE UPDATE

ON Employees

FROM John;

Cascade and Restrict options

Cascade: The CASCADE option means that any dependent objects or records will be
automatically deleted or dropped when the referenced object is deleted or dropped.

Use Cases:

 Dropping a table/view that is referenced by another object.

 Deleting a parent row in a foreign key relationship (with ON DELETE CASCADE).

Example 1: Drop with CASCADE

DROP TABLE Employees CASCADE;

This will drop the Employees table and any dependent objects (like foreign key constraints or
views.

Example 2: Foreign Key with ON DELETE CASCADE

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

CustomerID INT,

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

ON DELETE CASCADE

);

If a customer is deleted, all their orders will also be deleted automatically

RESTRICT:-
The RESTRICT option prevents the operation from being completed if there are any
dependencies (e.g., other objects or rows that rely on it).

Example 1: Drop with RESTRICT

DROP TABLE Employees RESTRICT;

This will only drop the table if no other objects depend on it. If any constraints or views
reference it, the operation fails.

Example 2: Foreign Key (default is RESTRICT behavior)

If you don't specify ON DELETE CASCADE, then by default, most SQL systems restrict deletion
of a parent row if dependent rows exist.

-- Attempting to delete a customer referenced in Orders will fail

DELETE FROM Customers WHERE CustomerID = 1;

TYPES OF VIEW:-

Vertical View:- A vertical view is created by selecting only specific columns (i.e., vertical
slices of the table).

This limits what data (columns) users can see, often used for data security or customized
reporting.

Example: Suppose you have a table:

CREATE TABLE Employees (

EmployeeID INT,

Name VARCHAR(100),

Department VARCHAR(50),

Salary DECIMAL(10, 2),

Email VARCHAR(100));

Vertical View (select only Name and Department):

CREATE VIEW vertical_view_employees AS

SELECT Name, Department

FROM Employees;
This view hides sensitive fields like Salary and Email.
Horizontal View:-

A horizontal view is created by selecting only specific rows (i.e., horizontal slices of the
table), typically using a WHERE clause.

This limits which rows (records) users can access.

Horizontal View (select only HR employees)

CREATE VIEW horizontal_view_employees AS

SELECT *

FROM Employees

WHERE Department = 'HR';

This view returns only employees in the HR department.