• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Computer Notes

Library
    • Computer Fundamental
    • Computer Memory
    • DBMS Tutorial
    • Operating System
    • Computer Networking
    • C Programming
    • C++ Programming
    • Java Programming
    • C# Programming
    • SQL Tutorial
    • Management Tutorial
    • Computer Graphics
    • Compiler Design
    • Style Sheet
    • JavaScript Tutorial
    • Html Tutorial
    • Wordpress Tutorial
    • Python Tutorial
    • PHP Tutorial
    • JSP Tutorial
    • AngularJS Tutorial
    • Data Structures
    • E Commerce Tutorial
    • Visual Basic
    • Structs2 Tutorial
    • Digital Electronics
    • Internet Terms
    • Servlet Tutorial
    • Software Engineering
    • Interviews Questions
    • Basic Terms
    • Troubleshooting
Menu

Header Right

Home » Sql » Tutorials » SQL VIEW
Next →
← Prev

SQL VIEW

By Dinesh Thakur

A VIEW is a virtual table, through which a selective portion of the data from one or more tables can be seen. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity. A view is stored as a SELECT statement in the database. DML operations on a view like INSERT, UPDATE, DELETE affects the data in the original table upon which the view is based.

 A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. A view is a specific representation of data from one or more tables. The tables referred in the views are known as Base tables. Creating a view does not take any storage space as only the query is stored in the data dictionary and the actual data is not stored anywhere.

For example: suppose that in the EMF table we want that ‘Manager’ should have access to the detail of employee’s names, job and department. But he should not have any access to the salaries of all the employees. This can be done with the help of Views. We can create View according to the individual’s need. We will create the view for the manager under which we do not include salary column. This helps the database administrator to limit the access to different kinds of users.

We’ll be covering the following topics in this tutorial:

  • Uses of Views
  • Creating Views 
  • Modifying Views 
  • Renaming Columns

Uses of Views

The reasons for using views in applications can be many like;
• Reducing complexity.
• Security is increased – sensitive information can be excluded from a view
• Renaming the table columns- by giving the different names to columns while creating views.
• Views can represent a subset of the data contained in a table.
• Views can join and simplify multiple tables into a single virtual table.
• Views take very little space to store; the database contains only the definition of a view, not a copy of all the data it presents.
• Different views can be created on the same base table for different users.

Syntax:
 
CREATE [OR REPLACE] VIEW view_name 
AS 
SELECT column_list FROM table_name [WHERE condition] 
[WITH CHECK OPTION]
[CONSTRAINT constrain_name] 
[WITH READ ONLY]; 

In the Syntax, View _name specifies the name of the view and must follow the rules for identifiers. 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. Select_statement specifies the SELECT Statement that defines a view.

The view may use the data contained in other views and tables. 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. WITH READ ONLY restrict the DML operations to be performed on the view.

Creating Views 

To create a view, you must meet the following requirements:

To create a view in your schema, you must have the CREATE VIEW privilege. To create a view in another user’s schema, you must have the CREATE ANY VIEW system privilege. You can acquire these privileges explicitly or through a role.

The owner of the view (whether it is you or another user) must have been explicitly granted privileges to access all objects referenced in the view definition. The owner cannot have obtained these privileges through roles. Also, the functionality of the view depends on the privileges of the view owner. For example, if the owner of the view has only the INSERT privilege for Scott’s EMP table, then the view can be used only to insert new rows into the EMP table, not to SELECT, UPDATE, or DELETE rows.

Examples:

Consider the EMPLOYEES table and create a View for the ‘RAMAN’; she cannot access the Salary of all the employees.

SQL > DESC EMPLOYEES; 
SQL > CREATE OR REPLACE VIEW RAMAN AS SELECT EID, NAME FROM EMPLOYEES; 

On Execution the view with Name ‘Raman’ is created. Once you create a view now it is assumed to be a Table. You can now perform the various DML operations on the View. But make sure if you impose the CHECK OPTIONS, then you cannot perform any operation. Remember that it will also copy the Records contained in the base table. Display all the records in the view.

SQL> SELECT * FROM RAMAN; 

After execution this command will display all the records of EMP table as when view created all the records automatically copied into the view. It will display all the records.

Read Only Option

If you use read only option in the view while creation then you cannot perform any DML operation on the view. If you try to do this then error message will encounter. WITH READ ONLY restrict the DML operations to be performed on the view

Consider the EMPLOYEES table and create a view for the ‘RAMANDEEP’; she cannot access the Salary of all the employees. Use the constraint ‘WITH READ ONLY.

SQL> create view ramandeep as select eid, name from employees with read only; 

After execution a view with the name ‘ramandeep’ will be created. In the view because we use the option WITH READ ONLY so it restricts the DML operations to be executed.

Modifying Views 

You can use the OR REPLACE option to modify the view. If the view exists it will be replaced with the new definition or a new view will be created. We can use Create or Replace option to create views instead of dropping the view and recreating it as with this option the privileges granted on the view are preserved, but the dependent stored programs and view become invalid.

The view will become invalid whenever the base table is altered. Vie can recompile a view using the Alter view statement, but oracle automatically recompiles the view once it is accessed. On recompiling the dependent objects become invalid.

SQL> ALTER VIEW View Name; 
Restrictions on DML Statements 

There are certain rules that have to be followed to perform the DML operations. They are as under:

You cannot insert the rows into view if the base table has certain columns with NOT NULL constraint that does not appear in the view.

If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.

If a view is defined with WITH CHECK OPTION, a row cannot be inserted into. or updated in, the base table (using the view), if the view cannot select the row from the base table.

If view contains WITH READ ONLY Constraint then you cannot perform any DML operations.

Renaming Columns

Views can be used to rename the columns without effecting the base tables provided that the number of columns in a view must match the number of columns specified in the select statement.

Create a view RAMAN from the table employees with different column names,

SQL> CREATE OR REPLACE UIEW RAMAN (ENO, ENAME) AS SELECT EID, NAME FROM EMPLOYEES;

View Created

After execution the view RAMAN will be created with the different column names.

 

You’ll also like:

  1. JSTL SQL sql:update Tag
  2. JSTL SQL sql:dateParam Tag
  3. JSTL SQL sql:param Tag
  4. JSTL SQL sql:query Tag
  5. What is a Database View
Next →
← Prev
Like/Subscribe us for latest updates     

About Dinesh Thakur
Dinesh ThakurDinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps.

Dinesh Thakur is a Freelance Writer who helps different clients from all over the globe. Dinesh has written over 500+ blogs, 30+ eBooks, and 10000+ Posts for all types of clients.


For any type of query or something that you think is missing, please feel free to Contact us.


Primary Sidebar

SQL Tutorials

SQL Tutorials

  • SQL - Home
  • SQL - Select
  • SQL - Create
  • SQL - View
  • SQL - Sub Queries
  • SQL - Update
  • SQL - Delete
  • SQL - Order By
  • SQL - Select Distinct
  • SQL - Group By
  • SQL - Where Clause
  • SQL - Select Into
  • SQL - Insert Into
  • SQL - Sequence
  • SQL - Constraints
  • SQL - Alter
  • SQL - Date
  • SQL - Foreign Key
  • SQL - Like Operator
  • SQL - CHECK Constraint
  • SQL - Exists Operator
  • SQL - Drop Table
  • SQL - Alias Syntax
  • SQL - Primary Key
  • SQL - Not Null
  • SQL - Union Operator
  • SQL - Unique Constraint
  • SQL - Between Operator
  • SQL - Having Clause
  • SQL - Isnull() Function
  • SQL - IN Operator
  • SQL - Default Constraint
  • SQL - Minus Operator
  • SQL - Intersect Operator
  • SQL - Triggers
  • SQL - Cursors

Advanced SQL

  • SQL - Joins
  • SQL - Index
  • SQL - Self Join
  • SQL - Outer Join
  • SQL - Join Types
  • SQL - Cross Join
  • SQL - Left Outer Join
  • SQL - Right Join
  • SQL - Drop Index
  • SQL - Inner Join
  • SQL - Datediff() Function
  • SQL - NVL Function
  • SQL - Decode Function
  • SQL - Datepart() Function
  • SQL - Count Function
  • SQL - Getdate() Function
  • SQL - Cast() Function
  • SQL - Round() Function

Other Links

  • SQL - PDF Version

Footer

Basic Course

  • Computer Fundamental
  • Computer Networking
  • Operating System
  • Database System
  • Computer Graphics
  • Management System
  • Software Engineering
  • Digital Electronics
  • Electronic Commerce
  • Compiler Design
  • Troubleshooting

Programming

  • Java Programming
  • Structured Query (SQL)
  • C Programming
  • C++ Programming
  • Visual Basic
  • Data Structures
  • Struts 2
  • Java Servlet
  • C# Programming
  • Basic Terms
  • Interviews

World Wide Web

  • Internet
  • Java Script
  • HTML Language
  • Cascading Style Sheet
  • Java Server Pages
  • Wordpress
  • PHP
  • Python Tutorial
  • AngularJS
  • Troubleshooting

 About Us |  Contact Us |  FAQ

Dinesh Thakur is a Technology Columinist and founder of Computer Notes.

Copyright © 2023. All Rights Reserved.