why do we use view in sql

What is a View? View can be described as virtual table which derived its data from one or more than one table columns. It is stored in the database. View can be created using tables of same database or different database. It is used to implement the security mechanism in the SQL Server. Create table Emp_Details(EmpId int, EmpName nvarchar(200),
EmpLogin nvarchar(20), EmpPassword nvarchar(20), EmploymentDate datetime ) And for example, table has the following data of employees:, etc. ) of the Employees. So he can create a view which gives the, as the output and gives permission for the view to the user. In this way, the administrator does not need to give access permission for the table to the user. Views are used for security purposes because they provide encapsulation of the name of the table. Data is in the virtual table, not stored permanently. Views display only selected data. Create View Viewname As Select Column1, Column2 From Tablename Where (Condition) Group by (Grouping Condition) having (having Condition) Create View View_Employeeinfo As s Select EmpId, EmpName, employmentdate From EmployeeInfo, statement in deriving the data for the view.

Create table EmpProjInfo (EmpId int, Projectname nvarchar(200)) Create view Vw_EmployeeProj As Select Emp_Details. EmpId, Emp_Details. EmpName, EmpProjInfo. Projectname from EmployeeInfo inner join EmpProjInfo on Emp_Details. EmpId=EmpProjInfo. EmpId If we want to alter the view, then we can use the command to alter the view. For example, Alter view Vw_EmployeeProj As Select Emp_Details. EmpId, Emp_Details. EmpName, EmpProjInfo. Projectname from Emp_Details inner join EmpProjInfo on Emp_Details. EmpId=EmpProjInfo. EmpId where Emp_Details. EmpId in (2,3,4) to get the definition about the views. For example, we can use the. system procedure to rename a view.

The syntax of the SP_Rename Old Name, New name For example, if we want to rename our view, we can write the sp_rename View_Employeeinfo, Vw_EmployeeInfo command to drop a view. For example, to drop the view, we can use the following statement: In a, a view is the of a stored on the, which the users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a, a view does not form part of the : as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view. In some databases, views are the only way to query data. Views can represent a subset of the data contained in a table.

Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table. Views can and simplify multiple tables into a single virtual table. Views can act as aggregated tables, where the aggregates data (, etc. ) and presents the calculated results as part of the data. Views can hide the complexity of data. For example, a view could appear as Sales2000 or Sales2001, transparently the actual underlying table. Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents. Depending on the engine used, views can provide extra security. Just as a (in programming) can provide, so can a database view. In another parallel with functions, database users can manipulate nested views, thus one view can aggregate data from other views.

Without the use of views, the of databases above would become much more difficult. Views can make it easier to create lossless join decomposition. Just as in a base table lack any defined ordering, rows available through a view do not appear with any default sorting. A view is a relational table, and the relational model defines a table as a set of rows. Since sets are not ordered by definition neither are the rows of a view. Therefore, an clause in the view definition is meaningless; the SQL standard ( ) does not allow an ORDER BY clause in the subquery of a CREATE VIEW command, just as it is refused in a CREATE TABLE statement. However, sorted data can be obtained from a view, in the same way as any other table as part of a query on that view. Nevertheless, some DBMS (such as ) do not abide by this SQL standard restriction.

Show More

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button
Close