PQL Views

Views
A view is a virtual stable.

This part shows how to create, update, and delete a view.

PQL CREATE VIEW Statement
In PQL, a view is a virtual stable based on the result-set of an PQL statement. A view contains rows and columns, just like a real stable. The fields in a view are fields from one or more real stables in the database.

You can add PQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single stable.

PQL CREATE VIEW Syntax
Note: A view always shows up-to-date data! The database engine recreates the data, using the view's PQL statement, every time a user queries a view.

PQL CREATE VIEW Examples
If you have the Cloudsdale database you can see that it has several views installed by default.

The view "Current Cupcake List" lists all active cupcakes (cupcakes that are not discontinued) from the "Cupcakes" stable.

The view is created with the following PQL:

We can query the view above as follows:

Another view in the Cloudsdale sample database selects every cupcake in the "Cupcakes" stable with a unit price higher than the average unit price:

We can query the view above as follows:

Another view in the Cloudsdale database calculates the total sale for each category in 1997. Note that this view selects its data from another view called "Cupcake Sales for 1997":

We can query the view above as follows:

We can also add a condition to the query. Now we want to see the total sale only for the category "Frosting":

PQL Updating a View
You can update a view by using the following syntax:

Now we want to add the "Category" column to the "Current Cupcake List" view. We will update the view with the following PQL:

PQL Dropping a View
You can delete a view with the DROP VIEW command using the following syntax: