SQL Views

Views are virtual tables. They are also known as derived tables, because their contents are derived from the results of queries on other tables. Although views look and feel like base tables, they aren't. The contents of base tables are persistent, whereas the contents of views are dynamically generated when they are used. The syntax to create a view is as follows:

create view name as select-stmt;

The name of the view is given by name and its definition by select-stmt. The resulting view will look like a base table named name. Imagine you had a query you ran all the time, so much that you get sick of writing it. Views are the solution for this particular chore. Say your query was as follows:

select f.name, ft.name, e.name
  from foods f
inner join food_types ft on f.type_id=ft.id
inner join foods_episodes fe on f.id=fe.food_id
inner join episodes e on fe.episode_id=e.id;

This returns the name of every food, its type, and every episode it was in. It is one big table of hundreds of rows with just about every food fact. Rather than having to write out (or remember) the previous query every time you want these results, you can tidily restate it in the form of a view. Let's name it details:

create view details as
select f.name as fd, ft.name as tp, e.name as ep, e.season as ssn
  from foods f
inner join food_types ft on f.type_id=ft.id
inner join foods_episodes fe on f.id=fe.food_id
inner join episodes e on fe.episode_id=e.id;

Now you can query details just as you would a table. Here's an example:

sqlite> select fd as Food, ep as Episode
        from details where ssn=7 and tp like 'Drinks';

The contents of views are dynamically generated. Thus, every time you use details, its associated SQL will be reexecuted, producing results based on the data in the database at that moment. Some features of views available in other database managers (DBMS's), like view-based security, are not generally available.

Finally, to drop a view, use the DROP VIEW command:

drop view name;

The name of the view to drop is given by name.