Scribd Logo
Upload

Welcome to Scribd!

  • 13 views

    SQL Views

    Uploaded by

    HSKALRA
    SQL

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    SQL Views

    Uploaded by

    HSKALRA
    13 views8 pages
    SQL

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    SQL

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    13 views8 pages

    SQL Views

    Uploaded by

    HSKALRA
    SQL

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 8

    SQL Views

    Views in SQL are a kind of virtual table. A view also has rows and
    columns like tables, but a view doesn’t store data on the disk like a
    table. View defines a customized query that retrieves data from
    one or more tables, and represents the data as if it was coming
    from a single source.
    We can create a view by selecting fields from one or more tables
    present in the database. A View can either have all the rows of a
    table or specific rows based on certain conditions.
    After the view has been created , we can perform following SQL
    commands to refer to that view:

     Select
     Insert
     Update
     delete

    CREATE VIEWS in SQL


    We can create a view using CREATE VIEW statement. A View
    can be created from a single table or multiple tables.

    Syntax
    CREATE VIEW view_name AS
    SELECT column1, column2.....
    FROM table_name
    WHERE condition;

    Parameters:
     view_name: Name for the View
     table_name: Name of the table
     condition: Condition to select rows
    StudentDetails

    StudentMarks

    Example 1: Creating View from a single table


    we will create a View named DetailsView from the table
    StudentDetails. Query:

    CREATE VIEW DetailsView AS


    SELECT NAME, ADDRESS
    FROM StudentDetails
    WHERE S_ID < 5;

    To see the data in the View, we can query the view in the same
    manner as we query a table.

    SELECT * FROM DetailsView;


    Output:

    Creating View from multiple tables

    In this example we will create a View named MarksView from two


    tables StudentDetails and StudentMarks. To create a View from
    multiple tables we can simply include multiple tables in the
    SELECT statement. Query:
    CREATE VIEW MarksView AS
    SELECT StudentDetails.NAME, StudentDetails.ADDRESS,
    StudentMarks.MARKS
    FROM StudentDetails, StudentMarks
    WHERE StudentDetails.NAME = StudentMarks.NAME;

    To display data of View MarksView:

    SELECT * FROM MarksView;


    Output:
    DELETE VIEWS in SQL
    SQL allows us to delete an existing View. We can delete or drop
    View using the DROP statement.

    Syntax
    DROP VIEW view_name;

    Example
    In this example, we are deleting the View MarksView.
    DROP VIEW MarksView;

    Deleting a row from a View


    Deleting rows from a view is also as simple as deleting rows from
    a table. We can use the DELETE statement of SQL to delete rows
    from a view.
    DELETE FROM DetailsView
    WHERE NAME="Suresh";

    If we fetch all the data from DetailsView now as,


    SELECT * FROM DetailsView;
    Output:
    UPDATE VIEW in SQL
    If you want to update the existing data within the view, use
    the UPDATE statement.

    Syntax

    UPDATE view_name
    SET column1 = value1, column2 = value2...., columnN = valueN
    WHERE [condition];

    If you want to update the view definition without affecting the data,
    use the CREATE OR REPLACE VIEW statement. you can use
    this syntax

    CREATE OR REPLACE VIEW view_name AS


    SELECT column1, column2, ...
    FROM table_name
    WHERE condition;
    Rules to Update Views in SQL:

    Certain conditions need to be satisfied to update a view. If any of


    these conditions are not met, the view can not be updated.
    1. The SELECT statement which is used to create the view should
    not include GROUP BY clause or ORDER BY clause.
    2. The SELECT statement should not have the DISTINCT
    keyword.
    3. The View should have all NOT NULL values.
    4. The view should not be created using nested queries or
    complex queries.
    5. The view should be created from a single table. If the view is
    created using multiple tables then we will not be allowed to
    update the view.
    Example 1: Update View to Add or Replace a View Field

    We can use the CREATE OR REPLACE VIEW statement to add


    or replace fields from a view.

    If we want to update the view MarksView and add the field AGE to
    this View from StudentMarks Table, we can do this by:

    CREATE OR REPLACE VIEW MarksView AS


    SELECT StudentDetails.NAME, StudentDetails.ADDRESS,
    StudentMarks.MARKS, StudentMarks.AGE
    FROM StudentDetails, StudentMarks
    WHERE StudentDetails.NAME = StudentMarks.NAME;

    If we fetch all the data from MarksView now as:

    SELECT * FROM MarksView;

    Output:
    Example 2: Update View to Insert a row in a view
    We can insert a row in a View in the same way as we do in a
    table.
    INSERT INTO DetailsView(NAME, ADDRESS)
    VALUES("Suresh","Gurgaon");

    If we fetch all the data from DetailsView now as,


    SELECT * FROM DetailsView;

    Output:

    WITH CHECK OPTION Clause


    The WITH CHECK OPTION clause in SQL is a very useful clause
    for views. It applies to an updatable view.
    The WITH CHECK OPTION clause is used to prevent data
    modification (using INSERT or UPDATE) if the condition in the
    WHERE clause in the CREATE VIEW statement is not satisfied.
    If we have used the WITH CHECK OPTION clause in the CREATE
    VIEW statement, and if the UPDATE or INSERT clause does not
    satisfy the conditions then they will return an error.
    WITH CHECK OPTION Clause Example:
    In the below example, we are creating a View SampleView from
    the StudentDetails Table with a WITH CHECK OPTION clause.
    CREATE VIEW SampleView AS
    SELECT S_ID, NAME
    FROM StudentDetails
    WHERE NAME IS NOT NULL
    WITH CHECK OPTION;

    Uses of a View

    A good database should contain views for the given reasons:


    1. Restricting data access – Views provide an additional level of
    table security by restricting access to a predetermined set of
    rows and columns of a table.
    2. Hiding data complexity – A view can hide the complexity that
    exists in multiple joined tables.
    3. Simplify commands for the user – Views allow the user to
    select information from multiple tables without requiring the
    users to actually know how to perform a join.
    4. Store complex queries – Views can be used to store complex
    queries.
    5. Rename Columns – Views can also be used to rename the
    columns without affecting the base tables provided the number
    of columns in view must match the number of columns specified
    in a select statement. Thus, renaming helps to hide the names
    of the columns of the base tables.
    6. Multiple view facility – Different views can be created on the
    same table for different users.

    You might also like