view in SQL

What is View in SQL || 2 ways to create view SQL

What is View in SQL

View in SQL is a virtual table that is basically a SQL dynamic table. It is created by SQL query and used for quick reference as carrying a long query in SQL is difficult, so we store it in the view as per our comfort. The main task of a view is to retrieve data and hide the complexity.

How to create a View by new view

To create a view in SQL we will first write a SQL Query like below.

select * from employees
where JOB_ID in ('SA_REP','SH_CLERK');

In the above query, we filtered our data for two JOB_ID instead of using complete SQL table. There are many situations wherein we don’t want to use complete SQL table data instead refer to specific data. After writing this query we will go in our Oracle developer and click on view as illustrated below.

What is View in SQL
What is View in SQL

After that, we will right-click on it and then click on CREATE VIEW a new pop-up will open like below.

create view sql
create view sql

Now delete everything in the query box and then paste our query which we have created above. Also, change the name and in the last check syntax if everything is ok.

mysql create view
mysql create view

After this click OK. A view has been created. You can check this by double-clicking on the view icon.

oracle create view
oracle create view

There is one more point to keep in mind if you are sharing this view with others then you need to provide access to them as well. Something like below for SQL SELECT access only. You can grant more access as well as per the business requirement.

GRANT SELECT ON EMPLOYEES_VIEW TO USER_NAME;

I would encourage you to explore the new tab which will be open by double-clicking on the view.

what is view in sql
what is view in sql

How to create Views by Oracle Query

We can also create a view by using SQL script. As mentioned above first we will write query in SQL then we add some more scripts as below.

create view employees_view1 as
(select * from employees
where JOB_ID in ('SA_REP','SH_CLERK'));

In the above oracle script, we added CREATE VIEW then mentioned the new view name as EMPLOYEES_VIEW1 the write AS followed by SQL query for which we wanted to create a view. After writing the SQL script we will run this. Now new view has been created as illustrated below.

oracle create view
oracle create view

Don’t forget to provide access to SQL view.

Difference between Table and View

PropertyTableView
DefinitionA Table is defined as a database object which is used to store data in a database. Tables store data in a logically organized row-column format.A View is a virtual table that holds data from one or more than one table. A view includes a set of SQL queries for retrieving data from different table databases.
ExistenceTables in SQL is physical entity and are actually stored in a database.Views in SQL are logical entities and not actually stored in a database.
JobThe main task of a table is to store data in a row-column format.The main task of a view is to retrieve data and hide the complexity.
SpeedAs tables are physical objects, they provide fast results.As views refer to tables for data, they provide slow results.
RelationTables in SQL are independent in nature. Until it’s a relational table.Views in SQL depend upon different tables to get results.
DML operationsIn SQL, we can perform DML operations on tables.We also perform some DML operations on view. But, eventually, the changes are made in the underlying table.
What is View in SQL

How to check Views properties

We can use oracle describe table statement to see what columns do we have in our view and what SQL data type these columns have.

Oracle view
Oracle view

You can read more about SQL from this link.

Alter views in SQL

You can alter views in SQL by two methods. Either we can go to VIEW name on the left pane and then right click on it. select EDIT and then change the existing query by our new new query.

Alter view in SQL
Alter view in sql

Or alternatively we can write a query with CREATE OR REPLACE VIEW statement as below.

create or replace view employees_view as
(SELECT
    * FROM employees);

The existing query in the view will get replaced by the new oracle query.

create or replace view
create or replace view

Drop SQL view

To drop a view we can use SQL DROP statement as mentioned below.

drop view employees_view;
drop view in sql
drop view in sql

You can read more about SQL from this link as well.

Leave a Comment