Scribd Logo
Upload

Welcome to Scribd!

  • 75 views

    Lab 09: Views

    Uploaded by

    hashir mahboob
    The document discusses views in a database management system. Views allow users to access data from tables or other views in a way that simplifies or restricts access. Views can reduce complexity, prevent access to sensitive columns, and improve query performance through indexing. Views are defined using a CREATE VIEW statement followed by a SELECT query. Views function like tables but are not stored separately - they are just stored queries. Views can limit updates and inserts to only rows that meet the view's criteria using WITH CHECK OPTION. Views can be edited or dropped using ALTER VIEW or DROP VIEW statements. Exercises are provided to demonstrate creating, updating, selecting from, and dropping views.

    Copyright:

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

    Lab 09: Views

    Uploaded by

    hashir mahboob
    75 views3 pages
    The document discusses views in a database management system. Views allow users to access data from tables or other views in a way that simplifies or restricts access. Views can reduce complexity, prevent access to sensitive columns, and improve query performance through indexing. Views are defined using a CREATE VIEW statement followed by a SELECT query. Views function like tables but are not stored separately - they are just stored queries. Views can limit updates and inserts to only rows that meet the view's criteria using WITH CHECK OPTION. Views can be edited or dropped using ALTER VIEW or DROP VIEW statements. Exercises are provided to demonstrate creating, updating, selecting from, and dropping views.

    Original Description:

    Lab 9

    Original Title

    CSL-220-Lab09-12052020-123653pm

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses views in a database management system. Views allow users to access data from tables or other views in a way that simplifies or restricts access. Views can reduce complexity, prevent access to sensitive columns, and improve query performance through indexing. Views are defined using a CREATE VIEW statement followed by a SELECT query. Views function like tables but are not stored separately - they are just stored queries. Views can limit updates and inserts to only rows that meet the view's criteria using WITH CHECK OPTION. Views can be edited or dropped using ALTER VIEW or DROP VIEW statements. Exercises are provided to demonstrate creating, updating, selecting from, and dropping views.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    75 views3 pages

    Lab 09: Views

    Uploaded by

    hashir mahboob
    The document discusses views in a database management system. Views allow users to access data from tables or other views in a way that simplifies or restricts access. Views can reduce complexity, prevent access to sensitive columns, and improve query performance through indexing. Views are defined using a CREATE VIEW statement followed by a SELECT query. Views function like tables but are not stored separately - they are just stored queries. Views can limit updates and inserts to only rows that meet the view's criteria using WITH CHECK OPTION. Views can be edited or dropped using ALTER VIEW or DROP VIEW statements. Exercises are provided to demonstrate creating, updating, selecting from, and dropping views.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    You are on page 1of 3

    CSL-220: Database Management System

    Lab 09: Views

    Objective(s) :
    To learn the Views

    Views
    We should be able to use views to:
     Reduce apparent database complexity for end users
     Prevent sensitive columns from being selected, while still affording access to other
    importantdata
     Add additional indexing to your database to speed query performance

    A view is, at its core, really nothing more than a stored query. What’s great is that you can mix and
    match your data from base tables (or other views) to create what will, in most respects, functionjust
    like another base table. You can create a simple query that selects from only one table andleaves
    some columns out, or you can create a complex query that joins several tables and makesthem appear
    as one
    Syntax
    The syntax for creating a view
    CREATE VIEW <view name>
    AS
    <SELECT statement>
    Example 1
    We’ll call this one our customer phone list, and create it as CustomerPhoneList_vw
    CREATE VIEW CustomerPhoneList_vw
    AS
    SELECT CustomerName, Contact, Phone
    FROM Customers
    Example 2
    Our manager would like to beable to do simple queries that will tell him or her.What orders have
    been placed for what parts and who placed them. So, we create a view that they can perform very
    simple queries

    CREATE VIEW CustomerOrders_vw


    AS
    SELECT cu.CompanyName,o.OrderID,o.OrderDate,od.ProductID,p.ProductName,od.Quantity,
    od.UnitPrice,od.Quantity * od.UnitPrice AS ExtendedPrice
    FROM Customers AS cu
    INNER JOIN Orders AS o
    Departemnent of Computer Sciences 2/3 Semester Spring 2016
    CSL-220: Database Management System Lab 09: Views
    ON cu.CustomerID = o.CustomerID
    INNER JOIN [Order Details] AS od
    ON o.OrderID = od.OrderID
    INNER JOIN Products AS p
    ON od.ProductID = p.ProductID

    Limit What’s Inserted into Views—WITH CHECK OPTION


    The rules are simple—in order to update or insert data using the view, the resulting row mustqualify
    to appear in the view results. Restated, the inserted or updated row must meet any WHERE criterion
    that’s used in the SELECT statement that underlies your view.

    CREATE VIEW OregonShippers_vw


    AS
    SELECT ShipperID,
    CompanyName,
    Phone
    FROM Shippers
    WHERE Phone LIKE ‘(503)%’
    OR Phone LIKE ‘(541)%’
    OR Phone LIKE ‘(971)%’
    WITH CHECK OPTION
    Editing Views
    The only differences between using the ALTER VIEW statement and the CREATE VIEW statement
    are:
     ALTER VIEW expects to find an existing view, whereas CREATE doesn’t.
     ALTER VIEW retains any permissions that have been established for the view.
     ALTER VIEW retains any dependency information.

    Dropping Views
    It doesn’t get much easier than this:
    DROP VIEW <view name>, [<view name>,[ ...n]]
    And it’s gone.
    Departemnent of Computer Sciences 3/3 Semester Spring 2016
    CSL-220: Database Management System Lab 09: Views

    Exercises

    1. Create a view to display records of deptno 10 from EMP table with the following attributes:
    empno,ename,sal, deptno.

    2. Display all the records from view created in question 1.

    3. Through view created in question 1, update the deptno of employee 7782 from 10 to 20 and
    then display all the records from the same view and note which employee record is missing
    now, which was displayed in question 2.

    4. Create any simple view with base table EMP in such a way that records can only be displayed
    but can never be manipulated through this view.

    5. Create a view which displays the ename, dname and sal of all the employees of deptno 20.

    6. Create a PL/SQL program to display highest salary using view created in question 5.

    7. Create a view MY_VU based on the table EMP55 which does not exists in the schema. Now
    create the table EMP55 from EMP table having records of deptno=10. Now select all the
    records from the view MY_VU.

    You might also like