VIEW, SEQUENCE & SYNONYMS in RDBMS
View
A view is a logical entity also called virtual table
which is created from the base table or main table.
The main objective of creating a view is to provide
secure access over the main table. In some cases when admin want to show some
column from the specific table, admin or database programmer can do this by
creating separate table i.e. virtual table or VIEW with the same data for each
user as per the requirement but Data Redundancy is the limitation of this
approach.
Read-only view:
A view by which user can only view records but unable to
insert, update or delete records is called read only view.
Updatable view:
A view by which user can perform DML statements like
select, insert, update or delete operation is called updatable view.
If a view definition having complete primary key field as well as Not Null key fields then view is updatable otherwise read only.
Example-
Let us take example of each view one by one by following given steps-
1. Create a table student by given command for which you want to create a VIEW.
SQL> CREATE TABLE Student
(
Enroll_No int PRIMARY KEY,
Student_Name varchar(20),
Course varchar(10),
Fees numeric(10,2)
);
2. Create a Read Only VIEW named VWR_Student by given command-
SQL>CREATE OR REPLACE VIEW VWR_Student AS Select Student_Name, Course, Fees FROM Student;
3. Create Updatable VIEW named VWU_Student by given command-
SQL>CREATE OR REPLACE VIEW VWU_Student AS Select * FROM Student;
4. Now we can check the difference between these two views one by one by given commands-
A. We can not perform insert operation by Read Only View because it does not have primary key. If we perform below insert command without primary key data than there will be an error.
SQL>INSERT INTO VWR_Student VALUES('ABC', 'BCA', 12000);
error: can not insert NULL into System.Student.Enroll_No.
B. We can perform below insert command by Updatable View because it has primary key.
SQL>INSERT INTO VWU_Student VALUES(1, 'ABC', 'BCA', 12000);
OUTPUT WINDOW of VIEW
Practical Demonstration of VIEW in Oracle
SEQUENCE
Sequence is an order set of values like 1, 3, 5, … that are generated by SQL Program in Oracle and supported by some database systems to produce unique values on requirement.
A sequence is nothing but a user-defined schema or object
that automatic generates an ordered value of numeric type.
Sequences are regularly used in database programming
skills now a days because of the requirement of a unique value in each row
inside a table in many applications and sequences provides an easy way to
generate it.
The order of numeric values are automatically generated in an ascending or descending order according to definition of Sequence with defined intervals and can be restarted when exceeds maximum value.
Example-
Let us understand Sequence in Oracle with following Steps.
1. Execute below command to create a table student for which you want to create a SEQUENCE.
SQL> CREATE TABLE Student
(
Enroll_No int PRIMARY KEY,
Student_Name varchar(20),
Course varchar(10),
Fees numeric(10,2)
);
2. Create a SEQUENCE named SEQ_Student by given command-
SQL>CREATE SEQUENCE SEQ_Student
start with 1
increment by 1
minvalue 0
maxvalue 100
 |
| Practical Demonstration of Sequence in Oracle |
SYNONYMS
1. Execute below command to create a student table.
SQL> CREATE TABLE Student
(
Enroll_No int PRIMARY KEY,
Student_Name varchar(20),
Course varchar(10),
Fees numeric(10,2)
);
2. Execute below command to insert a record in student table.
3. Execute below command to create an alternate name or synonym of student table (ex. Stud).
4. Execute any one of the below commands to select record from student table.
 |
| Practical Demonstration of Synonym in Oracle |
No comments:
Post a Comment