This is actually a two part question.
First: I was wondering if there is a way to display the information in a view I just created. I couldn't find anything online that was similar to the DISPLAY Tables query that could be used for views.
The query to create my view is:
CREATE VIEW View1 AS
WHERE shipToName = 'Jim Bob'
Secondly, once I find out how to display that specific view from above, how do I go about finding the highest "paidPrice" (a column in the CustOrder table)?
Thank you all in advance!
A view is little more than a stored
SELECT statement, but from the perspective of the client, they are mostly equivalent to real tables. To interact with a view you have created, you may simply issue
SELECT statements against it.
-- Regular SELECT statements, no different -- from querying against a real table. -- Get all rows from the view SELECT * FROM View1 -- Get the MAX() value from a column SELECT MAX(paidPrice) AS maxprice FROM View1
You may also create views which represent multiple joined tables. This is a common use case, wherein many tables are frequently joined for querying. You may use a view to handle the joins, and expose only certain columns to certain database users rather than grant full access to your schema.
CREATE VIEW joinview AS ( SELECT t1.id, t1.col1, t1.col2, -- The view will only expose the alias t1.col3 AS aliased_name, -- Use an alias to avoid a column name collision t2.col1 AS t2c1, -- The view will expose the column name without the table name t2.col99 FROM t1 INNER JOIN t2 ON t1.id = t2.t1_id );
Now the view will only expose columns as a
SELECT query would. You will no longer need to reference the individual tables, since it produces a flat output.
-- Retrieve 2 columns from the join view SELECT col99, aliased_name FROM joinview
Finally, because views act just like normal tables, you can join them to other tables or views too. Take care when assembling views with joins though, to be sure that the underlying tables are appropriately indexed. Otherwise, the views may perform poorly (just as they would for normal
SELECT queries executed without appropriate indexing).