Views in sql:- We can present logical subsets or combination of data by creating views of tables. A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the data dictionary.
Advantages of Views:-
1. Views restrict access to the data because the view can display selective columns from the table.
2. Views allow users to make simple queries to retrieve the results from complicated queries. For example, views allow users to query information from multiple tables without knowing how to write a join statement.
3. Views provide data independence for adhoc users and application programs. One view can be used to retrieve data from several tables.
4. Views provide groups of users access to data according to their particular criteria.
5. Views avoid data redundancy.
6. Views provide data security.
Simple Views v/x complex Views:-There are two classifications for views : simple and complex. The basic difference is related to the DML ( insert, update, and delete ) operations.
A simple view is one that
– Derives data from only one table.
– Contains no functions or groups of data.
– Can perform DML through the view.
A complex view is one that
– Derives data from many tables.
– Contains functions or groups of data.
– Does not always allow DML through view.
Creating Views:- We can create a view by embedding a subquery within the CREATE VIEW statement.
CREATE [OR REPLACE] VIEW view[ (alias [, alisas ]…) ]AS subquery;
In the Syntax
OR REPLACE :Re-creates the view if it already exists
View-: Is the name of the view
Alias: It Specifies names for the expressions selected by the view’s query (the number of aliases must match the number of expressions selected by the view).
Subquery: it Is a complete SELECT statement (we can user aliases for the columns in the SELECT list.).
CREATE VIEW empvu10
AS SELECT empno, ename, job,FROM emp WHERE deptno = 10;
View created.
Creating a Complex View:-A complex view is one that derives data from many tables or contains functions or contains groups of data.
CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)AS SELECT d.dname, MIN(e.sal),MAX(e.sal),AVG(e.sal)FROM emp ne, dept d WHERE e.deptno = d.deptno GROUP BY d.dname;
Complex View created.
Performing DML Operations on a View:- We can perform DML operations on data through a view if those operations follow certain rules.
Rules for removing data from a view-:We can remove a row from a view unless it contains any of the following:
– Group functions
– A GROUP BY clause
– The pseudo column ROWNUM keyword
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Server selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
Rule for modifying data in a view -:We can modify data in a view unless it contains any of the following:
– Group functions
– A GROUP BY clause
– The pseudocolumn ROWNUM keyword
– Columns defined by expressions
Rules for adding data in a view: We can add data in a view unless it contains any of the following:
– Group functions
– A GROUP BY clause
– The pseudocolumn ROWNUM keyword
– columns defined by expressions
– There are NOR NULL columns, without a default value, in the base tables that are not selected by the view.
Removing a View:– we use the DROP VIEW statement to remove a view. The statement removes the view definition from the database. Dropping view has no effect on the tables on which the view was based. Views or other applications based on deleted views become invalid. Only the creator or a user with the DROP ANY VIEW privilege can remove a view.
DROP VIEW view;
DROP VIEW empvu10;
View dropped.
Inline Views:
– An inline view is a subquery with as alias(correlation name) that we can use within a SQL statement.
– An inline view is similar to using a named subquery in the FROM clause of the main query.
– An inline view is not a schema object.
An inline view in the FROM clause of a SELECT statement defines a data source for the SELECT statement.
“Top-N” Analysis:- Top-N queries are useful in scenarios where the need is to display only the n top-most or the n bottom-most records from a table based on a condition. This result set can be used for further analysis. For example using Top-N analysis you can perform the following types of queries:
– The top three earners in the company.
– The four most recent recruits in the company.
– The top two sales representatives who have sold the maximum number of products.
– The top three products that have had the maximum sales in the last six months.
Top-N queries use a consistent nested query structure with the elements described below:
– subquery or an inline view to generate the sorted list of data. The subquery or the inline-view included the ORDER BY clause to ensure that the ranking is in the desired order. For results retrieving the largest values, a DESC parameter is needed.
– Outer query to limit the number of rows in the final result set. The outer query includes the following components:
– The ROWNUM pseudo-column, which assigns a sequential value starting with 1 to each of the rows returned from the subquery.
– WHERE clause which specifies the n rows to be returned. The outer WHERE clause must use a < or <= operator.