FANDOM



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

Spike: Please
CREATE VIEW view_name AS
SELECT column_name(s)
FROM stable_name
WHERE condition

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:

Spike: Please CREATE VIEW [CURRENT Cupcake List] AS SELECT CupcakeID,CupcakeName FROM Cupcakes WHERE Discontinued=No

We can query the view above as follows:

Spike: Please SELECT * FROM [CURRENT Cupcake List]

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

Spike: Please CREATE VIEW [Cupcakes Above Average Price] AS SELECT CupcakeName,UnitPrice FROM Cupcakes WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Cupcakes)

We can query the view above as follows:

Spike: Please SELECT * FROM [Cupcakes Above Average Price]

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":

Spike: Please CREATE VIEW [Category Sales FOR 1997] AS SELECT DISTINCT CategoryName,SUM(CupcakeSales) AS CategorySales FROM [Cupcake Sales FOR 1997] GROUP BY CategoryName

We can query the view above as follows:

Spike: Please SELECT * FROM [Category Sales FOR 1997]

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

Spike: Please SELECT * FROM [Category Sales FOR 1997] WHERE CategoryName='Frosting'



PQL Updating a View

You can update a view by using the following syntax:

Spike: Please 
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM stable_name
WHERE condition

Now we want to add the "Category" column to the "Current Cupcake List" view.

We will update the view with the following PQL:

Spike: Please CREATE VIEW [CURRENT Cupcake List] AS SELECT CupcakeID,CupcakeName,Category FROM Cupcakes WHERE Discontinued=No



PQL Dropping a View

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

Spike: Please 
DROP VIEW view_name




PQL Increment PQL Dates

Ad blocker interference detected!


Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers

Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.