Explain the difference between tables, views, and synonyms in SQL

Let us understand what tables, views and synonyms are in Structured Query Language (SQL).

Tables, views and synonyms

A table is a repository of data where it is a physical entity within the table. The table physically resides in the database.

Views are not part of the physical representation of the database. It is precompiled so data retrieval is faster and also provides secure accessibility mechanisms.

A synonym is an alternative 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 date and time).

Suppose there is a situation where salary is not displayed to a group of users, it is possible to create a view to display the allowed information.

Create view emp_some_details as (select empID, name, skills, date of birth from employees);

Procedure

Given below is the SQL created in the above example Procedure for Tables, Views and Synonyms -

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 Views

The advantages of using views are as follows-

• It can access data tables, views, multiple views, or their combination from a table, multiple tables.

• The view is connected to the data of its base table.

• Provides security mechanisms for data accessibility.

You can use the CREATE OR REPLACE VIEW statement to update the view.

The syntax is as follows -

sql create or replace view view-name As
SELECT column1, column2, ...
FROM table_name
WHERE condition;

To drop a view, we can use the drop view command -

DROP view view-name;

Synonyms are used as alternative names assigned to tables or views. It can be used to hide the original name and owner of the actual entity. It extends the scope of the table by allowing public access to synonyms.

The above is the detailed content of Explain the difference between tables, views, and synonyms in SQL. For more information, please follow other related articles on the PHP Chinese website!