CREATING VIEWS Database Management System

CREATING VIEWS Database Management System

CREATING VIEWS

view

CREATING VIEWS Database Management System : You can present logical subsets or combinations 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.

CREATE OR REPLACE VIEW simple_vu

AS SELECT Empno, Ename, sal FROM employees;

Advantages of Views

  • Views restrict access to the data because the view can display selective columns from the table.
  • Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing how to write a join statement.
  • Views provide data independence for adhoc users and application programs. One view can be used to retrieve data from several tables.
  • Views provide groups of users access to data according to their particular criteria.

 

Displaying Data from Multiple Tables

Cartesian Products

A Cartesian product is formed when:

  • A join condition is omitted
  • A join condition is invalid
  • All rows in the first table are joined to all rows in the Second table.

To avoid a Cartesian product, always include a valid join Condition in a WHERE clause.

Cartesian Products

When a join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. All rows in the first table are joined to all rows in the second table.

A Cartesian producttends to generate a large number of rows, and the resultis rarely useful. You should always include a valid join condition in a WHERE clause, unless you have a specific need to combine all rows from all tables.

Cartesian products are useful for some tests when you need to generate a large number of rows to simulate areasonable amount of data. A Cartesian product is generated if a join Condition is omitted.

Types of Group Functions

Function Description

AVG([DISTINCT | ALL]n) Average value of n, ignoring null values

COUNT({*|[DISTINCT | ALL]expr)

Number of rows, where expr evaluates to something other than null (count all selected rows using*, including duplicates and rows with nulls)

MAX([DISTINCT|ALL]expr) Maximum value of expr, ignoring null values

MIN([DISTINCT|ALL]expr) Minimum value of expr, ignoring null values

STDDEV([DISTINCT|ALL]x) Standard deviation of n, ignoring null values

SUM ([DISTINCT|ALL]n) Sum values of n, ignoring null values

VARIANCE([DISTINCT|ALL|x) Variance of n, ignoring null values

Group Functions Syntax

SELECT [column,] group_function(column), …

FROM table

[WHERE condition]

[GROUP BY column]

[ORDER BY column];

Using the AVG and SUM Functions

SELECT AVG(sal), MAX(sal),

MIN (sal), SUM (sal)

FROM emp

Using the MIN and MAX Functions

You can use MIN and MAX for any data type.

SELECT MIN(Hiredate), MAX(Hiredate)

FROM emp;

SELECT MIN(Ename), MAX(Ename) FROM emp;

The COUNT Function

The COUNT function has three formats:

  • COUNT(*)
  • COUNT(expr)
  • COUNT(DISTINCT expr)

COUNT(*) returns the number of rows in a table that satisfy the criteria of the SELECT statement, including duplicate rows and rows containing null values in any of the columns. If a WHERE clause is included in the SELECT statement, COUNT(*) returns the number of rows that satisfies the condition in the WHERE clause.

In Contrast, COUNT(eXpr) returns the number of non-nu|| values in the Column identified by expr.

COUNT(DISTINCT expr) returns the number of unique, non-nu|| values in the Columnidentified by expr.

Group functions ignore null values in the column.

Using the NVL Function with Group Functions

The NVL function forces group functions to include null values.

Group Functions and Null Values (continued)

The NVL function forces group functions to include null values. In the slide example, the average is Calculated based on all rows in the table, regardless of whether null values are Stored in the COMMISIONISSION_PCT Column. The average is calculated as the total Commisionission paid to all emp divided by the total number of emp in the company (20).

SELECT AVG(NVL(commisionission pct, 0)). FROM emp;

Creating Groups of Data:

The GROUP BY Clause Syntax

SELECT column, group_function(column)

FROM table

[WHERE condition]

[GROUP BY group by expression]

(ORDER BY column];

The GROUP BY Clause

You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group.

Using the GROUP BY Clause

All columns in the SELECT list that are not in group functions must be in the GROUP BY clause.

SELECT deptno, AVG(sal) FROM emp

GROUP BY deptno;

The GROUP BY column does not have to be in the SELECT list.

SELECT AVG(sal) FROM emp GROUP BY deptno;

Restricting Group Results

in the same way that you use the WHERE clause to restrict the rows that you select, you use the HAVING clause to restrict groups.

The HAVING Clause

You use the HAVING clause to specify which groups are to be displayed, and thus, you further restrict the groups on the basis of aggregate information.

In the syntax:

Group_condition restricts the groups of rows returned to those groups for which the specified condition is true

The Oracle server performs the following steps when you use the HAVING clause:

  1. Rows are grouped.
  2. The group function is applied to the group.
  3. The groups that match the criteria in the HAVING clause are displayed.

The HAVING clause can precede the GROUP BY clause, but it is recommisionended that you place the GROUP BY clause first because that is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.

SELECT deptno, MAX(sal)

FROM emp

GROUP BY deptno

HAVING MAX(sal)>10000;

Nesting Group Functions

Display the maximum average sal.

SELECT MAX(AVG(sal))

FROM emp GROUP BY deptno;

Nesting Group Functions

Group functions can be nested to a depth of two.

Order of the clauses

SELECT column, group_function(column)

FROM table

(WHERE condition)

[GROUP BY group_by_expression]

HAVING group_condition

[ORDER BY column];

Indexes

An Oracle server index is a schema object that can speed up the retrieval of rows by using a pointer.

Indexes can be created explicitly or automatically. If you do not have an index on the column, then a full table Scan occurs.

An index provides direct and fast access to rows in a table. Its purpose is to reduce the necessity of disk I/O by using an indexed path to locate data quickly. The index is used and maintained automatically by the Oracle server. Once an index is created, no direct activity is required by the user.

Indexes are logically and physically independent of the table they index. This means that they can be created or dropped at any time and have no effect on the base tables or other indexes.

Note: When you drop a table, corresponding indexes are also dropped.

How Are indexes Created?

Automatically: A unique index is created

automatically when you define a PRIMARY KEY or UNIOUE constraint in a table definition.

Manually: Users can Create nonunique indexes on columns to speed up access to the rows.

Types of Indexes

Two types of indexes can be created. One type is a unique index: the Oracle server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIOUE key constraint. The name of the index is the name given to the constraint.

The other type of index is a nonunique index, which a user can create. For example, you can Create a FOREIGN KEY column index for a join in a query to improve retrieval speed.

Note: You can manually Create a unique index, but it is recommended that you create a unique Constraint, which implicitly creates a unique index.

When to Create an Index

You should create an index if:      

A Column contains a wide range of values.

A Column contains a large number of null values.

One or more columns are frequently used together in a WHERE clause or a join condition.

The table is large and most queries are expected to retrieve less than 2 to 4 percent of the rows.

When Not to Create an Index

It is usually not worth Creating an index if: The table is small

The Columns are not often used as a condition in the query

Creating an Index                                               

Create an index on one or more columns.

CREATE INDEX emp_last_name_idx ON emp(Ename);

CREATE INDEX index ON table (column[,column]…);

Creating an Index

Create an index on one or more columns by issuing the CREATE INDEX statement.

in the Syntax:

index is the name of the index

table is the name of the table

Column is the name of the column in the table to be indexed

Note: You need to know the basics of the indexes. The exact usage is beyond the scope of the book.