.. only:: html


|LS| Views
===============================================================================

When you write a query, you need to spend a lot of time and effort formulating
it. With views, you can save the definition of an SQL query in a reusable
'virtual table'.

**The goal for this lesson:** To save a query as a view.

Creating a View
-------------------------------------------------------------------------------

You can treat a view just like a table, but its data is sourced from a query.
Let's make a simple view based on the above:

.. code-block:: sql

  create view roads_count_v as
    select count(people.name), streets.name
    from people, streets where people.street_id=streets.id
    group by people.street_id, streets.name;

As you can see the only change is the :kbd:`create view roads_count_v as` part
at the beginning. We can now select data from that view:

.. code-block:: sql

  select * from roads_count_v;

Result:

.. code-block:: sql

     count |    name
    -------+-------------
         1 | Main Road
         2 | High street
         1 | Low Street
    (3 rows)

Modifying a View
-------------------------------------------------------------------------------

A view is not fixed, and it contains no 'real data'. This means you can easily
change it without impacting on any data in your database:

.. code-block:: sql

  CREATE OR REPLACE VIEW roads_count_v AS
    SELECT count(people.name), streets.name
    FROM people, streets WHERE people.street_id=streets.id
    GROUP BY people.street_id, streets.name
    ORDER BY streets.name;

(This example also shows the best practice convention of using UPPER CASE for
all SQL keywords.)

You will see that we have added an :kbd:`ORDER BY` clause so that our view rows
are nicely sorted:

.. code-block:: sql

    select * from roads_count_v;

     count |    name
    -------+-------------
         2 | High street
         1 | Low Street
         1 | Main Road
    (3 rows)

Dropping a View
-------------------------------------------------------------------------------

If you no longer need a view, you can delete it like this:

.. code-block:: sql

  drop view roads_count_v;

|IC|
-------------------------------------------------------------------------------

Using views, you can save a query and access its results as if it were a table.

|WN|
-------------------------------------------------------------------------------

Sometimes, when changing data, you want your changes to have effects elsewhere
in the database. The next lesson will show you how to do this.


.. Substitutions definitions - AVOID EDITING PAST THIS LINE
   This will be automatically updated by the find_set_subst.py script.
   If you need to create a new substitution manually,
   please add it also to the substitutions.txt file in the
   source folder.

.. |IC| replace:: In Conclusion
.. |LS| replace:: Lesson:
.. |WN| replace:: What's Next?