Let us understand what table, view and synonym in the structured query language (SQL) are.
A table is a repository of data, where in the table it is a physical entity. A table resides physically in the database.
A view is not a part of the database’s physical representation. It is precompiled, so that data retrieval behaves faster and also provides a secure accessibility mechanism.
A synonym is an alternate name assigned to a table, view, sequence or program unit.
Create table employee (empID integer primary key, name varchar2(30), skill varchar2(30), salary number(20), DOB datetime).
Let’s say there is a scenario where salary is not shown to a group of users, a view may be created to display allowable information.
Create view emp_some_details as (select empID,name,skill,DOB from employee);
Given below is an program for creating a table, view and synonym in the SQL for the above mentioned example −
create table employee (empID integer primary key, name varchar2(30), skill varchar2(30), salary number(20), DOB datetime); insert into employee values(100,'AAA','JAVA',30000,2020-08-20); insert into employee values(101,'BBB','PHP',35000,2020-07-02); select * from employee; create view [salary_hide] as select empID,name,skill,DOB from employee ; select * from salary_hide;
You will get the following output −
100|AAA|JAVA|30000|1992 101|BBB|PHP|35000|2011 100|AAA|JAVA|1992 101|BBB|PHP|2011
The advantages of using a view are as follows −
It may access data from a table, multiple tables, view, multiple views or combinations of these.
A view connects to the data of its base table(s).
Provides a secure mechanism of data accessibility.
A view can be updated with the CREATE OR REPLACE VIEW statement.
The syntax is as follows −
sql create or replace view view-name As SELECT column1, column2, ... FROM table_name WHERE condition;
To delete view, we can use drop view command −
DROP view view-name;
Synonym is used as an alternate name assigned to a table or view. It may be used to shadow the original name and owner of the actual entity. It extends the reach of tables, by allowing public access to the synonym.