SQL Views Example (MC39, Spring 1999)

Note that using a view in this way is contrary to three-tier doctrine; it pushes too much of the application logic (which should be in the middle tier) down into the database.

create view in_stock as
  select title, name, actor 
    from movie_info, movie_stores, movie_actors, movie_copies 
    where movie_info.id = movie_actors.id 
      and movie_info.id = movie_id 
      and customer_id is null 
      and store_id = movie_stores.id;

select distinct title from in_stock 
  where name = 'St. Peter' and actor = 'Claude Rains';

title             
------------------
Casablanca        
Lawrence of Arabia