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.
After that, we will right-click on it and then click on CREATE VIEW a new pop-up will open like below.
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.
After this click OK. A view has been created. You can check this by double-clicking on the view icon.
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.
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.
Don’t forget to provide access to SQL view.
Difference between Table and View
| Property | Table | View |
| Definition | A 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. |
| Existence | Tables 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. |
| Job | The 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. |
| Speed | As tables are physical objects, they provide fast results. | As views refer to tables for data, they provide slow results. |
| Relation | Tables in SQL are independent in nature. Until it’s a relational table. | Views in SQL depend upon different tables to get results. |
| DML operations | In 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. |
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.
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.
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.
Drop SQL view
To drop a view we can use SQL DROP statement as mentioned below.
drop view employees_view;
You can read more about SQL from this link as well.