First, Let’s understand what is VIEW in SQL?
A VIEW is a data object in SQL which does not contain any data. Its contents are the resultant of a base table. They are operated just like the base table but they don’t contain any data of their own.
A view can be accessed with the use of SQL SELECT statement like a table. A view can also be made up by selecting data from more than one tables.
SQL Syntax :
SQL> CREATE VIEW view_name AS
Advantage of VIEW
To restrict Data Access
To make complex query easy
To provide data Independence
To present different views of the same data
Create VIEW in SQL
Suppose in our whole employee database, we want to create the view for those employees who are working in city LONDON.
SQL > CREATE VIEW emp_london
AS Select * from employees
WHERE location = ‘London’;
Now we can query from this emp_London view like this-
SQL > Select first_name, salary form emp_london
Where dept_id = 80;
Create VIEW with Aggregate Function
We can also create the VIEW using aggregate functions. Let’s create the VIEW from Employees table.
SQL > CREATE VIEW emp_all
AS Select Dept_id, location, SUM(Salary) from employees
GROUP BY Location;
Create VIEW with JOINS
Now let’s see, How can we create the VIEW using JOINS in SQL.
SQL > CREATE VIEW ordersview
AS SELECT ord_num, ord_amount, a.agent_code, agent_name, cust_name
FROM orders a, customer b, agents c