Explain the difference between a table, view and synonym in SQL

Let us understand what table, view and synonym in the structured query language (SQL) are.

Table, view and synonym

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.

Example

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);

Program

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;

Output

You will get the following output −

100|AAA|JAVA|30000|1992
101|BBB|PHP|35000|2011
100|AAA|JAVA|1992
101|BBB|PHP|2011

Advantages of view

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.

Bhanu Priya

Updated on: 03-Jul-2021

3K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started