Create Stored Queries using SQL

Stored queries, in the context of SQL user views, refer to the creation and storage of predefined SQL queries in the form of views. A view is a virtual table generated by a query, which can be used to access and manipulate data from one or more underlying tables, without directly interacting with the base tables themselves. Views provide abstraction, simplify complex queries, enhance security, and support data integrity. To create a stored query using SQL user views, follow these steps:

Define the query: Begin by writing a SELECT statement that retrieves the required data from the base tables, applying any necessary filters, joins, aggregations, or calculations. Ensure that the query returns the expected results by testing it against your database.
Create the view: Use the CREATE VIEW statement to define a new view based on the SELECT query. Assign a unique name to the view and include an optional column list if you wish to assign custom names to the columns in the view. The general syntax for creating a view is as follows:
```
CREATE VIEW view_name [(column_name_1, column_name_2, ...)] AS
SELECT_statement;
```
For example, to create a view that calculates the total sales by product category, you could use the following SQL statement:
```
CREATE VIEW Total_Sales_By_Category AS
SELECT 
    pc.CategoryName,
    SUM(od.UnitPrice * od.Quantity) AS TotalSales
FROM
    Products p
    JOIN OrderDetails od ON p.ProductID = od.ProductID
    JOIN ProductCategories pc ON p.CategoryID = pc.CategoryID
GROUP BY
    pc.CategoryName;
```
Use the view: Once the view is created, you can use it like a regular table in your SQL queries. You can perform SELECT, JOIN, and aggregate operations on the view, but remember that the view itself does not store any data. Instead, it retrieves the data from the base tables each time it is queried. For example, to retrieve the total sales for a specific category, you could use the following query:
```
SELECT *
FROM Total_Sales_By_Category
WHERE CategoryName = 'Electronics';
```
Manage the view: SQL provides various statements to manage views, such as ALTER VIEW for modifying the view definition, and DROP VIEW for removing the view. Keep in mind that modifying or deleting a view does not affect the underlying base tables.

User views are often referred to as stored queries because they are created with SQL used as a data-definition language (DDL) and retrieved with SQL used as a data-modeling language (DML).
In the absence of stored queries, users can simply create queries on the fly; but then they must specify what data from which tables they would have liked to look at.
User views have that specification already built in. In fact, that is what a DDL (Data Definition Language ) actually creates and stores: the specification. The designer gives the specification a name (which becomes the user view); the user uses SQL as a DML to invoke the user view by name.

Stored Procedure Background

The below answers are useful as a background for 'stored procedures'.
Is a 'stored procedure' just when I have a database table that contains queries that can be called? ie, something like this

INDEX | NAME          | QUERY
1     | show_names    | "SELECT names.first, names.last FROM names;"
2     | show_5_cities | "SELECT cities.city FROM cities LIMIT 0,5;"

There are specific benefits and disadvantages to using the natively implemented stored procedures (or delayed parameter binding).
Whether its right for you depends on what you are trying to achieve, for example performance, ease of maintenance, and access control. In summary, creating stored queries using SQL user views involves defining a SELECT query, creating a view based on the query, and then using the view in subsequent queries. Views offer a convenient way to store and reuse complex queries, while also providing an additional layer of abstraction and security.