SQL View Create Alter & Drop

SQL VIEW is very useful database object .  We will learn SQL CREATE VIEW , ALTER VIEW & DROP VIEW Statements today with the conceptual understanding of it as well. Now , If you have our College database you can do basic SQL queries practice . You can use our Sample College Database script & generate one database in your system.

SQL View

                              View is the virtual table formed as a result of a query

Word Virtual needs an explanation , so here it goes. The tables are stored physically in our database systems. They’re assigned a space with reference address & everything computers do to store data in hard disks.
Views are different , because they are built on top of tables & they are filled from the data of these tables. Just a Simple SELECT Query can be formed as view or you can create a long complex query & store it as a view . Next time you wont have to write the SQL Query to obtain same data. Just call your view & it will give you the data. Think of it as an arbitrary layer that you can create on tables & only appears when you call it.

 

The reason they are so wonderful is that they do not disturb original data or operations such as write or read from their original tables. This is very useful when you want to create a distinction between users who only needs to read data or write data.

 

Another advantage is the data security. If you want your users to read only part of table hiding few columns & making visible only needed ones . Create a view and only allow their user to access that View. These are some basic level uses of Views. I will write detailed article on Advantages of SQL Views

Let’s understand this by explanations & create SQL views with examples.

Also Read : Table vs View -All You Need to Know

WHY CREATE SQL VIEW

Take this example of Inner Join we discussed .



SELECT Student.PersonID As StudentID ,
 Student.FirstName,
 Student.LastName ,
 Course.Title ,
 Grade.Grade
 FROM [School].[dbo].[Person] AS Student 
 
 INNER JOIN StudentGrade AS Grade
 ON Student.PersonID=Grade.StudentID

JOIN Course AS Course
 ON Course.CourseID =Grade.CourseID

We learnt about SQL Joins in previous post. Now advantage of creating a view is that once you have written a basic SQL query , you only have to create a view and give it a name. See below ,

CREATE SQL VIEW SYNTAX

CREATE VIEW viewname  AS  query

VIEW EXAMPLE

I have created a view on three tables which I have used previously in inner join example  .

 
CREATE VIEW dbo.StudentResult AS 
SELECT Student.PersonID As StudentID , Student.FirstName,
       Student.LastName , Course.Title , Grade.Grade 
FROM [School].[dbo].[Person] AS Student 
INNER JOIN StudentGrade AS Grade ON Student.PersonID=Grade.StudentID 
INNER JOIN Course AS Course ON Course.CourseID =Grade.CourseID 

Take a long breath & give it a moment . In the start we said that the view is just a virtual table . So if you’re wondering how to call a view or use the view to fetch data. After creating the view , treat it just like a table for reading data.

See Below

 SELECT *
 FROM dbo.StudentResult

Now , instead of re-writing the query again to fetch same data . I will just use my VIEW to have it. Because , it behaves like a table , i can use it for joins or as a data source for applications.

ALTER SQL VIEW SYNTAX & EXAMPLE

ALTER  is the DDL command (In case you don’t know what is ddl? See our post on DDL & DML) and it is used to as name suggest alter the state of database objects. Why do we need to Alter View?
To add new columns or to change column names or just to rewrite the query.  Same Syntax as of Create View. Just Add Alter keyword here

 
ALTER VIEW dbo.StudentResult AS 
SELECT Student.PersonID As StudentID , 
Student.FirstName, Student.LastName , Course.Title , Grade.Grade 

FROM [School].[dbo].[Person] AS Student 
LEFT JOIN StudentGrade AS Grade ON Student.PersonID=Grade.StudentID 
LEFT JOIN Course AS Course ON Course.CourseID =Grade.CourseID 

 

I have changed the join type and now my view will give me results for left join .

ALTER SQL VIEW SYNTAX & EXAMPLE

 

DROP VIEW dbo.StudentResult 

All of this is basic View Concepts. Views are a topic of huge discussion . Their use is of utmost importance & Views are heavily used for reporting purposes. BI Talks BI is composing a detailed e-book on such topics & best practices for reporting . Views will be discussed heavily in the book because this article is for people who want to learn sql at the beginner level. Soon we will write on advance level. If you have enjoyed the article ! Subscribe Us & stay Tuned for more !
We are live on twitter & Quora as well with the name BI Talks BI. If you are an active member there & need anything you can contact us there or comment below!

Also Read : Table vs View -All You Need to Know

Happy Learning!
Cheers!

Advertisements
One Comment

Leave a Reply

%d bloggers like this: