SQL .

SQL Views

Q: What the heck is a SQL view and what do you do with it?
A: A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view. This is because a view isn't really a table itself, but only a way to look at part of the original table.
First you must create the view. From then on, just as if it were a table, you can use the view.

Step 1 - create the view

   
 Create View ViewTomHanksFilms (Film_Title)
 As Select Film_Title
   From Thespian_Film_Table,
  Where Thespian_Last_Name equals "Hanks"
    and Thespian_First_Name equals "Tom";
Note: That this example has only one column - Film_Title - but multiple columns can be used.

Step 2 - use the view

   
 Select *
   from ViewTomHanksFilms;
   
 Apollo 13,
 Bachelor Party,
 Big,
 Bonfire of the Vanities,
 Dragnet,
 Forrest Gump,
 Joe Versus the Volcano,
 Nothing in Common,
 Philadelphia,
 Punchline,
 Saving Private Ryan,
 Sleepless in Newark,
 Splash,
 The Burbs,
 The Man with one Red Shoe,
 The Moneypit,
 Turner and Hooch,
 You've got Mail,
 Volunteers

Step 3 - update the view

   
 Update ViewTomHanksFilms
    set Film_Title = "Sleepless in Seattle"
  where Film_Title = "Sleepless in Newark";

Step 4 - check the view for the update

   
 Select *
   from ViewTomHanksFilms;
   
 Apollo 13,
 Bachelor Party,
 Big,
 Bonfire of the Vanities,
 Dragnet,
 Forrest Gump,
 Joe Versus the Volcano,
 Nothing in Common,
 Philadelphia,
 Punchline,
 Saving Private Ryan,
 Sleepless in Seattle,
 Splash,
 The Burbs,
 The Man with one Red Shoe,
 The Moneypit,
 Turner and Hooch,
 You've got Mail,
 Volunteers
...and you can see that "Sleepless in Newark" was changed to "Sleepless in Seattle".
Note: The table the view is a view of, Thespian_Film_Table, will also have the Movie Title "Sleepless in Newark" changed to "Sleepless in Seattle".
Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.
Comment by TheMadProfessor on 2012-07-24 Rate this Comment

One thing to keep in mind where updating views is concerned is that not all views are updatable. A good rule of thumb is, if a view row includes any data computed from multiple rows of the underlying table(s) (such as via a column function), it is probably not updatable.

Comment by geekyChik on 2008-05-07 Rate this Comment

Hi!
I've taken Access Database course, and currently in an MsSQL Server2000 course.
As good as the textbook is in explaining T-SQL and providing examples of the statements and functions. I have to say this is the first explanation of a VIEW that I actually understood! Thanks, I may just up my grade now! =)

 
Sign in to comment on SQL Views.