DatabaseWhat are views in SQL?

What are views in SQL?

In this article we will learn what are views in SQL. It briefly explains about how to use the view, how to create a view , how to read it and finally implementing dropping function on view.To learn different types of joins in SQL you can visit Learn about joins in SQL.

To understand what are views in SQL study the points given below:

Introduction:

  • View is a result set obtain by sql query.
  • View contains information of real tables in the form of row and column like real table.
  • View is a virtual table that forms information dynamically depending on the sql statement.
  • In this topic we will discuss the following points:
    1. Uses of view.
    2. Creation of view.
    3. Reading a view.
    4. Dropping a view.
  • For Example: Let us assume that we have two real tables, salary and emp-profile. Now we will make a view that displays the result of combination of both real tables.

    View in sql

  1. Uses of view:
    • View store data dynamically.
    • It prevents to access real table.
    • Only selected column can be accessed in view.
    • It takes reference for real table that prevent the access of underlying tables.
    • It is a virtual table.

  2. Creation/Modification of a View :
    • The basic syntax for creation of view is :
    • CREATE VIEW view_name 
      AS
      SELECT column_name(s)
      FROM table_name
      where [condition]
      

    • In case if you need to modify the view, you can use alter keyword for replacing it.
    • Alter VIEW view_name 
      AS
      SELECT column_name(s)
      FROM table_name
      where [condition]
      

    • Let’s assume that we have two main tables, one is employee salary table and other is employee profile table.
    • Now, we need to create a view that fetchs information of both the tables and displays the result in a single virtual table:

      salary table

    • Now let’s create view as virtual table. It will fetch information from both table and result a single virtual table
    • create VIEW [dbo].[View_empdetail]
      AS
      SELECT        dbo.salary.empid, dbo.empprofile.Passwrod, dbo.empprofile.username, dbo.salary.monthlysalary, dbo.salary.department, dbo.empprofile.name
      FROM            dbo.salary INNER JOIN  dbo.empprofile ON dbo.salary.empid = dbo.empprofile.empid
      

  3. Reading a View:
    • To read information from view,syntax is given below:
    • Select * from  view_name [where condition]

    • Now we have learnt to create and read view.
    • Let’s read the information from View_empdetail view
    • select * from [dbo].[View_empdetail] 

      reading view

  4. Drop a view:
    • TO delete a view, the basic syntax is:
    • DROP VIEW view_name

Thus, we are now familiar and learnt what are views in SQL along with examples implementing its uses, creation, reading and droping functions on view.

For reference you can watch this video:

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Exclusive content

- Advertisement -

Latest article

21,501FansLike
4,106FollowersFollow
106,000SubscribersSubscribe

More article

- Advertisement -