Exercise 21: Using Views

A VIEW is a fake table that is populated with the results of a SELECT query you write. Since every SELECT returns a table, it makes sense that you could make those tables permanent. Constructing a VIEW is similar to a combination of the CREATE TABLE statement and the sub-SELECT statement you just learned. If you find yourself frequently using a sub-select or the same query repeatedly, then consider making it a view so you can use the data better.

Here's an example that uses a query from Exercise 16:

/* test our query from Ex16 */
select pet.breed, pet.dead, count(dead) 
    from person, person_pet, pet 
    where person.id = person_pet.person_id and pet.id = person_pet.pet_id 
    group by pet.breed, pet.dead;

/* create the view */
create view dead_pets as
select pet.breed, pet.dead, count(dead) as total
    from person, person_pet, pet 
    where person.id = person_pet.person_id and pet.id = person_pet.pet_id 
    group by pet.breed, pet.dead;
  
/* try it */
select * from dead_pets where total > 10;

/* get rid of the Cats to see if it changes dead_pets */
delete from pet where breed = 'Cat';

/* see? it worked */
select * from dead_pets;

/* drop it */
drop view dead_pets;

You should notice that I added an as total column for the count(dead) function. This lets me query against that column even though it is calculated.