Views ensure the security of data by restricting access to the following data:

• Specific rows of the tables.
• Specific columns of the tables.
• Specific rows and columns of the tables.
• Rows fetched by using joins.
• Statistical summary of data in a given tables.
• Subsets of another view or a subset of views and tables.

Some common examples of views are:
• A subset of rows or columns of a base table.
• A union of two or more tables.
• A join of two or more tables.
• A statistical summary of base tables.
• A subset of another view, or some combination of views and base table.

CREATING VIEWS

A view can be created by using the CREATE VIEW statement

SYNTAX

CREATE VIEW view_name [column_name (s)] AS
SELECT column_name(s)
FROM table_name
WHERE condition [WITH CHECK OPTION]

column_name specifies the name of the column to be used in view. If the column_name option is not specified, then the view is created with the same columns as specified in the select_statement.

WITH CHECK OPTION forces the data modification statements to fulfill the criteria given in the SELECT statement defining the view. It also ensures that the data is visible after the modifications are made permanent.

A view can be referenced and used from another view, from a SQL query, and from stored procedure. You can reference a view as you would reference any real SQL database table:

SELECT * FROM view_name

Example

Customer_Info


Product_Info


     CREATE VIEW V_PRODUCT_SALES AS
      SELECT A1.Customer_Name Customer_Info, SUM (A2.Sales) Product_Info
        FROM Customer_Info A1, Product_Info A2
      WHERE A1.Product_Name = A2.Product_Name
GROUP BY A1.Customer_Name

To find the contents of the VIEW

SELECT * FROM V_PRODUCT_SALES



UPDATING A VIEW

SYNTAX


CREATE OR REPLACE VIEW view_name AS
 SELECT column_name(s)
   FROM table_name
 WHERE condition

DROPPING A VIEW

SYNTAX

DROP VIEW view_name

Advantages:

1. A view hides the complexity of the database tables from end users.
2. Views take very little space to store as it is not storing the actual data.
3. A view serves as a security mechanism. This ensures that users are able to retrieve and modify only the data seen by them. Users cannot see or access the remaining data in the underlying table.
4. A view can also serves as a mechanism to simplify query execution. Complex queries can be stored in the form as a view, and data from the view can be retrieved using simple queries.

Restrictions imposed on views:

• A view can be created only in the current database.
• The name of a view must follow the rules for identifiers and must not be the same as that of the base table.
• A view can be created only if there is a SELECT permission on its base table.
• A SELECT INTO statement cannot be used in view declaration statement.
• A trigger or an index cannot be defined on a view.
• The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.

The DISTINCT keyword can be used to return only distinct (different) values.

Main Table


1.   SELECT DISTINCT Customer_Name 
         FROM customer


2. SELECT DISTINCT Customer_Name FROM customer WHERE Cus_Id = 5


3.       SELECT DISTINCT Customer_Name 
             FROM customer 
     ORDER BY Customer_Name


4.       SELECT DISTINCT Customer_Name 
            FROM customer 
    ORDER BY Customer_Name DESC


5.     SELECT DISTINCT Customer_Name, Order_Date 
          FROM customer


6.     SELECT DISTINCT Customer_Name, Order_Date 
          FROM customer 
       WHERE Cus_Id = 4


7.         SELECT DISTINCT Customer_Name, Order_Date 
              FROM customer
      ORDER BY Customer_Name, Order_Date



8.        SELECT DISTINCT Customer_Name, Order_Date 
              FROM customer
      ORDER BY Customer_Name, Order_Date DESC



9.         SELECT DISTINCT Customer_Name, Order_Date 
              FROM customer
      ORDER BY Customer_Name DESC, Order_Date DESC

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Main Table


1.     SELECT Customer_Name, SUM (Order_Price)
           FROM customer
    GROUP BY Customer_Name


2.      SELECT Customer_Name, AVG (Order_Price)
            FROM customer
    GROUP BY Customer_Name


3.      SELECT Customer_Name, SUM (Order_Price), Order_Date
            FROM customer
    GROUP BY Customer_Name, Order_Date


4.      SELECT Customer_Name, SUM (Order_Price) , Order_Date
           FROM customer
         WHERE Cus_Id=3
    GROUP BY Customer_Name, Order_Date


5.      SELECT Customer_Name, SUM (Order_Price) 
            FROM customer
    GROUP BY Customer_Name, Order_Date
    ORDER BY Customer_Name DESC