Views

A View is like a virtual table, created by a query. Here's the documentation for the CREATE VIEW SQL statement.

A base relation is a real table (materialized, data stored on disks). A View is a virtual table, computed when needed.

Reasons for views¶

1. Simplify or modularize queries. (Can't be a correlated query, though.)
2. Allow access control. For example, we could create a view of the Students table that only lists name, address and phone, but not GPA and such, and allow everyone to search the directory:

   CREATE VIEW Directory(Name,Address,Phone) AS 
      -- but not SSN, GPA, etc 
      SELECT Name,Address,Phone 
      FROM   Students 
   GRANT SELECT ON Directory TO ALL 

3. Customization:
   • ease of use and learning
   • security
   • logical data independence

Kinds of View¶

One dimension is whether to

• use a temporary table for the view data, and discard after the query (this may allow locks to be released more quickly), or
• merge the view computation with the outer query, which allows for updates and may allow more optimization

Another dimension is whether to keep the view data around

• Virtual (run query as needed)
• Materialized (create the table of data)

Examples¶

Here are some examples using the WMDB

Filmography

filmography-view.sql

-- Creates a filmography view for all people

use wmdb;
drop view if exists filmography;

create view filmography(name,title) as
select name,title
from person inner join credit using (nm) inner join movie using (tt);

Popular Movies

In this case, "popular" means a movie has many actors.

popular-movies.sql

-- Creates a view showing movies with lots of actors:

use wmdb;
drop view if exists popmovies;

create view popmovies(title,count) as
select title,count(title) as count
from credit inner join movie using (tt)
group by tt
order by count desc;

Popular Actors

In this case, "popular" means an actor is in many movies.

popular-actors.sql

-- Creates a view showing actors with lots of movies

use wmdb;
drop view if exists popactors;

create view popactors(name,count) as
select name,count(name) as count
from person inner join credit using (nm)
group by nm
order by count desc;

Seeing a View¶

It's useful to know that you can see what a view is:

show tables; 
describe filmography; 
show create view filmography;

Conclusion¶

If this makes your project coding easier, by all means use it, but it's not required.