View in SQL

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
SELECT column_name(s)
FROM table_name
WHERE condition;

 

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  

        WHERE a.cust_code=b.cust_code  

        AND a.agent_code=c.agent_code; 

 

0 Comment

Leave a Reply