In SQL one of the very useful database object is View. 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.
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.
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
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.
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 their & need anything you can contact us there or comment below!