Oracle 10g Reference

Using Single-Row Functions to Customize Output

Objectives

Functions make the basic query block more powerful, and they are used to manipulate data values. This is the first of two lessons that explore functions. It focuses on single-row character, number, and date functions, as well as those functions that convert data from one type to another (for example, conversion from character data to numeric data).

SQL Functions

Functions are a very powerful feature of SQL. They can be used to do the following:

•         Perform calculations on data

•         Modify individual data items

•         Manipulate output for groups of rows

•         Format dates and numbers for display

•         Convert column data types

SQL functions sometimes take arguments and always return a value.

Note: Most of the functions that are described in this lesson are specific to the Oracle version of SQL.

There are two types of functions:

•         Single-row functions

•         Multiple-row functions

Single-Row Functions

These functions operate on single rows only and return one result per row. There are different types of single-row functions. This lesson covers the following ones:

•         Character

•         Number

•         Date

•         Conversion

•         General

Multiple-Row Functions

Functions can manipulate groups of rows to give one result per group of rows. These functions are also known as group functions (covered in lesson 4).

Note: For more information and a complete list of available functions and their syntax, see Oracle SQL Reference.

Single-Row Functions

Single-row functions are used to manipulate data items. They accept one or more arguments and return one value for each row that is returned by the query. An argument can be one of the following:

•         User-supplied constant

•         Variable value

•         Column name

•         Expression

Features of single-row functions include:

•         Acting on each row that is returned in the query

•         Returning one result per row

•         Possibly returning a data value of a different type than the one that is referenced

•         Possibly expecting one or more arguments

•         Can be used in SELECT, WHERE, and ORDER BY clauses; can be nested

In the syntax:

function_name is the name of the function

arg1, arg2                   is any argument to be used by the function. This can be                                                                    represented by a column name or expression.

This lesson covers the following single-row functions:

•         Character functions: Accept character input and can return both character and number values

•         Number functions: Accept numeric input and return numeric values

•         Date functions: Operate on values of the DATE data type (All date functions return a value of DATE data type except the MONTHS_BETWEEN function, which returns a number.)

•         Conversion functions: Convert a value from one data type to another

•         General functions:

-          NVL

-          NVL2

-          NULLIF

-          COALESCE

-          CASE

-          DECODE

Character Functions

Single-row character functions accept character data as input and can return both character and numeric values. Character functions can be divided into the following:

•         Case-manipulation functions

•         Character-manipulation functions

 

Case-Manipulation Functions

LOWER, UPPER, and INITCAP are the three case-conversion functions.

•         LOWER: Converts mixed-case or uppercase character strings to lowercase

•         UPPER: Converts mixed-case or lowercase character strings to uppercase

•         INITCAP: Converts the first letter of each word to uppercase and remaining letters to lowercase

SELECT 'The job id for '||UPPER(last_name)||' is '

            ||LOWER(job_id) AS "EMPLOYEE DETAILS"

FROM   employees;

Note: The functions discussed in this lesson are only some of the available functions.

Using Case-Manipulation Functions

The slide example displays the employee number, name, and department number of employee Higgins.

The WHERE clause of the first SQL statement specifies the employee name as higgins. Because all the data in the EMPLOYEES table is stored in proper case, the name higgins does not find a match in the table, and no rows are selected.

The WHERE clause of the second SQL statement specifies that the employee name in the EMPLOYEES table is compared to higgins, converting the LAST_NAME column to lowercase for comparison purposes. Because both names are now lowercase, a match is found and one row is selected. The WHERE clause can be rewritten in the following manner to produce the same result:

...WHERE last_name = 'Higgins'

The name in the output appears as it was stored in the database. To display the name in uppercase, use the UPPER function in the SELECT statement.

SELECT employee_id, UPPER(last_name), department_id

FROM   employees

WHERE  INITCAP(last_name) = 'Higgins';

Character-Manipulation Functions

CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, and TRIM are the character-manipulation functions that are covered in this lesson.

•         CONCAT: Joins values together (You are limited to using two parameters with CONCAT.)

•         SUBSTR: Extracts a string of determined length

•         LENGTH: Shows the length of a string as a numeric value

•         INSTR: Finds the numeric position of a named character

•         LPAD: Pads the character value right-justified

•         RPAD: Pads the character value left-justified

•         TRIM: Trims heading or trailing characters (or both) from a character string (If trim_character or trim_source is a character literal, you must enclose it in single quotation marks.)

Note: You can use functions such as UPPER and LOWER with ampersand substitution. For example, use UPPER('&job_title') so that the user does not have to enter the job title in a specific case.

Using the Character-Manipulation Functions

The slide example displays employee first names and last names joined together, the length of the employee last name, and the numeric position of the letter a in the employee last name for all employees who have the string REP contained in the job ID starting at the fourth position of the job ID.

Example

Modify the SQL statement in the slide to display the data for those employees whose last names end with the letter n.

SELECT employee_id, CONCAT(first_name, last_name) NAME,

LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?"

FROM   employees

WHERE  SUBSTR(last_name, -1, 1) = 'n';

Number functions accept numeric input and return numeric values. This section describes some of the number functions.

 

Note: This list contains only some of the available number functions.

For more information, see “Number Functions” in Oracle SQL Reference.

ROUND Function

The ROUND function rounds the column, expression, or value to n decimal places. If the second argument is 0 or is missing, the value is rounded to zero decimal places. If the second argument is 2, the value is rounded to two decimal places. Conversely, if the second argument is –2, the value is rounded to two decimal places to the left (rounded to the nearest unit of 10).

The ROUND function can also be used with date functions. You will see examples later in this lesson.

DUAL Table

The DUAL table is owned by the user SYS and can be accessed by all users. It contains one column, DUMMY, and one row with the value X. The DUAL table is useful when you want to return a value once only (for example, the value of a constant, pseudocolumn, or expression that is not derived from a table with user data). The DUAL table is generally used for SELECT clause syntax completeness, because both SELECT and FROM clauses are mandatory, and several calculations do not need to select from actual tables.

TRUNC Function

The TRUNC function truncates the column, expression, or value to n decimal places.

The TRUNC function works with arguments similar to those of the ROUND function. If the second argument is 0 or is missing, the value is truncated to zero decimal places. If the second argument is 2, the value is truncated to two decimal places. Conversely, if the second argument is –2, the value is truncated to two decimal places to the left. If the second argument is –1, the value is truncated to one decimal place to the left.

Like the ROUND function, the TRUNC function can be used with date functions.

MOD Function

The MOD function finds the remainder of the first argument divided by the second argument. The slide example calculates the remainder of the salary after dividing it by 5,000 for all employees whose job ID is SA_REP.

Note: The MOD function is often used to determine if a value is odd or even.

Oracle Date Format

The Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds.

The default display and input format for any date is DD-MON-RR. Valid Oracle dates are between January 1, 4712 B.C., and December 31, 9999 A.D.

In the example in the slide, the HIRE_DATE column output is displayed in the default format DD-MON-RR. However, dates are not stored in the database in this format. All the components of the date and time are stored. So, although a HIRE_DATE such as 17-JUN-87 is displayed as day, month, and year, there is also time and century information associated with the date. The complete data might be June 17, 1987, 5:10:43 p.m

This data is stored internally as follows:

            CENTURY     YEAR     MONTH     DAY   HOUR    MINUTE    SECOND

            19                    87            06                17        17           10                43

Centuries and the Year 2000

When a record with a date column is inserted into a table, the century information is picked up from the SYSDATE function. However, when the date column is displayed on the screen, the century component is not displayed (by default).

The DATE data type always stores year information as a four-digit number internally: two digits for the century and two digits for the year. For example, the Oracle database stores the year as 1987 or 2004, and not just as 87 or 04.

SYSDATE Function

SYSDATE is a date function that returns the current database server date and time. You can use SYSDATE just as you would use any other column name. For example, you can display the current date by selecting SYSDATE from a table. It is customary to select SYSDATE from a dummy table called DUAL.

Example

Display the current date using the DUAL table.

SELECT SYSDATE
FROM   DUAL;

Arithmetic with Dates

Because the database stores dates as numbers, you can perform calculations using arithmetic operators such as addition and subtraction. You can add and subtract number constants as well as dates.

You can perform the following operations:

 

The example in the slide displays the last name and the number of weeks employed for all employees in department 90. It subtracts the date on which the employee was hired from the current date (SYSDATE) and divides the result by 7 to calculate the number of weeks that a worker has been employed.

Note: SYSDATE is a SQL function that returns the current date and time. Your results may differ from the example.

If a more current date is subtracted from an older date, the difference is a negative number.

Date functions operate on Oracle dates. All date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.

•         MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

•         ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative.

•         NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.

•         LAST_DAY(date): Finds the date of the last day of the month that contains date

•         ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.

•         TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

This list is a subset of the available date functions. The format models are covered later in this lesson. Examples of format models are month and year.

For example, display the employee number, hire date, number of months employed, six-month review date, first Friday after hire date, and last day of the hire month for all employees who have been employed for fewer than 80 months.

SELECT employee_id, hire_date,

            MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,

            ADD_MONTHS (hire_date, 6) REVIEW,

            NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY(hire_date)

FROM   employees

WHERE  MONTHS_BETWEEN (SYSDATE, hire_date) < 80;

The ROUND and TRUNC functions can be used for number and date values. When used with dates, these functions round or truncate to the specified format model. Therefore, you can round dates to the nearest year or month.

Example

Compare the hire dates for all employees who started in 1997. Display the employee number, hire date, and start month using the ROUND and TRUNC functions.

SELECT employee_id, hire_date,

            ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH')

FROM   employees

WHERE  hire_date LIKE '%97';

Conversion Functions

In addition to Oracle data types, columns of tables in an Oracle database can be defined using ANSI, DB2, and SQL/DS data types. However, the Oracle server internally converts such data types to Oracle data types.

In some cases, the Oracle server uses data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user.

Implicit data type conversions work according to the rules that are explained in the next two slides.

Explicit data type conversions are done by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type; the second data type is the output.

Note: Although implicit data type conversion is available, it is recommended that you do explicit data type conversion to ensure the reliability of your SQL statements.

Implicit Data Type Conversion

The assignment succeeds if the Oracle server can convert the data type of the value used in the assignment to that of the assignment target.

For example, the expression hire_date > '01-JAN-90' results in the implicit conversion from the string '01-JAN-90' to a date.

In general, the Oracle server uses the rule for expressions when a data type conversion is needed in places that are not covered by a rule for assignment conversions.

For example, the expression salary = '20000' results in the implicit conversion of the string '20000' to the number 20000.

Note: CHAR to NUMBER conversions succeed only if the character string represents a valid number.

Explicit Data Type Conversion

SQL provides three functions to convert a value from one data type to another:

 

Displaying a Date in a Specific Format

Previously, all Oracle date values were displayed in the DD-MON-YY format. You can use the TO_CHAR function to convert a date from this default format to one that you specify.

Guidelines

•         The format model must be enclosed by single quotation marks and is case sensitive.

•         The format model can include any valid date format element. Be sure to separate the date value from the format model by a comma.

•         The names of days and months in the output are automatically padded with blanks.

•         To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

•         You can format the resulting character field with the iSQL*Plus COLUMN command (covered in a later lesson).

SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired

FROM   employees

WHERE  last_name = 'Higgins';

Element	Result
YYYY	Full year in numbers
YEAR	Year spelled out (in English)
MM	Two-digit value for month
MONTH	Full name of the month
MON	Three-letter abbreviation of the month
DY	Three-letter abbreviation of the day of the week
DAY	Full name of the day of the week
DD	Numeric day of the month

Use the formats that are listed in the following tables to display time information and literals and to change numerals to spelled numbers.

 

Using the TO_CHAR Function with Dates

The SQL statement in the slide displays the last names and hire dates for all the employees. The hire date appears as 17 June 1987.

Example

Modify the slide example to display the dates in a format that appears as “Seventeenth of June 1987 12:00:00 AM.”

SELECT  last_name,

            TO_CHAR(hire_date,

                        'fmDdspth "of" Month YYYY fmHH:MI:SS AM')

            HIREDATE

FROM    employees;

Using the TO_CHAR Function with Numbers

When working with number values such as character strings, you should convert those numbers to the character data type using the TO_CHAR function, which translates a value of NUMBER data type to VARCHAR2 data type. This technique is especially useful with concatenation.

Number Format Elements

If you are converting a number to the character data type, you can use the following format elements:

 

Guidelines

•         The Oracle server displays a string of number signs (#) in place of a whole number whose digits exceed the number of digits that is provided in the format model.

•         The Oracle server rounds the stored decimal value to the number of decimal places that is provided in the format model.

Using the TO_NUMBER and TO_DATE Functions

You may want to convert a character string to either a number or a date. To accomplish this task, use the TO_NUMBER or TO_DATE functions. The format model that you choose is based on the previously demonstrated format elements.

The fx modifier specifies exact matching for the character argument and date format model of a TO_DATE function:

•         Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.

•         The character argument cannot have extra blanks. Without fx, Oracle ignores extra blanks.

•         Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without fx, numbers in the character argument can omit leading zeros.

RR Date Format Element

The RR date format is similar to the YY element, but you can use it to specify different centuries. Use the RR date format element instead of YY so that the century of the return value varies according to the specified two-digit year and the last two digits of the current year. The table in the slide summarizes the behavior of the RR element.

 

Example of RR Date Format

To find employees who were hired before 1990, the RR format can be used. Because the current year is greater than 1999, the RR format interprets the year portion of the date from 1950 to 1999.

The following command, on the other hand, results in no rows being selected because the YY format interprets the year portion of the date in the current century (2090).

SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-yyyy')

FROM   employees

WHERE  TO_DATE(hire_date, 'DD-Mon-yy') < '01-Jan-1990';

no rows selected

Nesting Functions

Single-row functions can be nested to any depth. Nested functions are evaluated from the innermost level to the outermost level. Some examples follow to show you the flexibility of these functions.

The slide example displays the last names of employees in department 60. The evaluation of the SQL statement involves three steps:

1.         The inner function retrieves the first eight characters of the last name.

Result1 = SUBSTR (LAST_NAME, 1, 8)

2.         The outer function concatenates the result with _US.

Result2 = CONCAT(Result1, '_US')

3.         The outermost function converts the results to uppercase.

The entire expression becomes the column heading because no column alias was given.

Example

Display the date of the next Friday that is six months from the hire date. The resulting date should appear as Friday, August 13th, 1999. Order the results by hire date.

      SELECT   TO_CHAR(NEXT_DAY(ADD_MONTHS

            (hire_date, 6), 'FRIDAY'),

            'fmDay, Month DDth, YYYY')

            "Next 6 Month Review"

   FROM      employees

   ORDER BY  hire_date;

General Functions

These functions work with any data type and pertain to the use of null values in the expression list.

 

NVL Function

To convert a null value to an actual value, use the NVL function.

Syntax

NVL (expr1, expr2)

In the syntax:

•         expr1 is the source value or expression that may contain a null

•         expr2 is the target value for converting the null

You can use the NVL function to convert any data type, but the return value is always the same as the data type of expr1.

NVL Conversions for Various Data Types

 

Using the NVL Function

To calculate the annual compensation of all employees, you need to multiply the monthly salary by 12 and then add the commission percentage to the result:

SELECT last_name, salary, commission_pct,

            (salary*12) + (salary*12*commission_pct) AN_SAL

FROM   employees;

Notice that the annual compensation is calculated for only those employees who earn a commission. If any column value in an expression is null, the result is null. To calculate values for all employees, you must convert the null value to a number before applying the arithmetic operator. In the example in the slide, the NVL function is used to convert null values to zero.

Using the NVL2 Function

The NVL2 function examines the first expression. If the first expression is not null, then the NVL2 function returns the second expression. If the first expression is null, then the third expression is returned.

Syntax

            NVL2(expr1, expr2, expr3)

In the syntax:

•         expr1 is the source value or expression that may contain null

•         expr2 is the value that is returned if expr1 is not null

•         expr3 is the value that is returned if expr1 is null

In the example shown in the slide, the COMMISSION_PCT column is examined. If a value is detected, the second expression of  SAL+COMM is returned. If the COMMISSION_PCT column holds a null value, the third expression of SAL is returned.

The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG. If the data types of expr2 and expr3 are different, the Oracle server converts expr3 to the data type of expr2 before comparing them unless expr3 is a null constant. In the latter case, a data type conversion is not necessary. The data type of the return value is always the same as the data type of expr2, unless expr2 is character data, in which case the return value’s data type is VARCHAR2

Using the NULLIF Function

The NULLIF function compares two expressions. If they are equal, the function returns null. If they are not equal, the function returns the first expression. You cannot specify the literal NULL for the first expression.

Syntax

            NULLIF (expr1, expr2)

In the syntax:

•         expr1 is the source value compared to expr2

•         expr2 is the source value compared with expr1 (If it is not equal to expr1, expr1 is returned.)

In the example shown in the slide, the length of the first name in the EMPLOYEES table is compared to the length of the last name in the EMPLOYEES table. When the lengths of the names are equal, a null value is displayed. When the lengths of the names are not equal, the length of the first name is displayed.

Note: The NULLIF function is logically equivalent to the following CASE expression. The CASE expression is discussed on a subsequent page:

CASE WHEN expr1 = expr 2 THEN NULL ELSE expr1 END

The COALESCE function returns the first non-null expression in the list.

Syntax

            COALESCE (expr1, expr2, ... exprn)

In the syntax:

•         expr1 returns this expression if it is not null

•         expr2 returns this expression if the first expression is null and this expression is not null

•         exprn returns this expression if the preceding expressions are null

All expressions must be of the same data type.

In the example shown in the slide, if the MANAGER_ID value is not null, it is displayed. If the MANAGER_ID value is null, then the COMMISSION_PCT is displayed. If the MANAGER_ID and COMMISSION_PCT values are null, then the value –1 is displayed.

Conditional Expressions

Two methods used to implement conditional processing (IF-THEN-ELSE logic) in a SQL statement are the CASE expression and the DECODE function.

Note: The CASE expression complies with ANSI SQL. The DECODE function is specific to Oracle syntax.

CASE Expression

CASE expressions let you use IF-THEN-ELSE logic in SQL statements without having to invoke procedures.

In a simple CASE expression, the Oracle server searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and if an ELSE clause exists, then the Oracle server returns else_expr. Otherwise, the Oracle server returns null. You cannot specify the literal NULL for all the return_exprs and the else_expr.

All of the expressions ( expr, comparison_expr, and return_expr) must be of the same data type, which can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

In the SQL statement in the slide, the value of JOB_ID is decoded. If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary.

The same statement can be written with the DECODE function.

This is an example of a searched CASE expression. In a searched CASE expression, the search occurs from left to right until an occurrence of the listed condition is found, and then it returns the return expression. If no condition is found to be true, and if an ELSE clause exists, the return expression in the ELSE clause is returned; otherwise, NULL is returned.

SELECT last_name,salary,

(CASE WHEN salary<5000 THEN 'Low'

      WHEN salary<10000 THEN 'Medium'

      WHEN salary<20000 THEN 'Good'

      ELSE 'Excellent'

END) qualified_salary

FROM employees;

DECODE Function

The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic that is used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.

If the default value is omitted, a null value is returned where a search value does not match any of the result values.

Using the DECODE Function

In the SQL statement in the slide, the value of JOB_ID is tested. If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary.

SELECT last_name, job_id, salary,

       DECODE(job_id, 'IT_PROG',  1.10*salary,

                      'ST_CLERK', 1.15*salary,

                      'SA_REP',   1.20*salary,

              salary)

       REVISED_SALARY

FROM   employees;

The same statement can be expressed in pseudocode as an IF-THEN-ELSE statement:

   IF job_id = 'IT_PROG'     THEN  salary = salary*1.10

   IF job_id = 'ST_CLERK'    THEN  salary = salary*1.15

   IF job_id = 'SA_REP'      THEN  salary = salary*1.20

   ELSE salary = salary

Lesson 4 - Reporting Aggregated Data Using the Group Functions

Group functions operate on sets of rows to give one result per group.

Types of Group Functions

Each of the functions accepts an argument. The following table identifies the options that you can use in the syntax:

 

Guidelines for Using Group Functions

•         DISTINCT makes the function consider only nonduplicate values; ALL makes it consider every value, including duplicates. The default is ALL and therefore does not need to be specified.

•         The data types for the functions with an expr argument may be CHAR, VARCHAR2, NUMBER, or DATE.

•         All group functions ignore null values. To substitute a value for null values, use the NVL, NVL2, or COALESCE functions.

Using the Group Functions

You can use AVG, SUM, MIN, and MAX functions against columns that can store numeric data. The example in the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives.

You can use the MAX and MIN functions for numeric, character, and date data types. The slide example displays the most junior and most senior employees.

The following example displays the employee last name that is first and the employee last name that is last in an alphabetized list of all employees:

  SELECT MIN(last_name), MAX(last_name)

  FROM   employees;

Note: The AVG, SUM, VARIANCE, and STDDEV functions can be used only with numeric data types. MAX and MIN cannot be used with LOB or LONG data types.

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 satisfy the condition in the WHERE clause.

In contrast, COUNT(expr) returns the number of non-null values that are in the column identified by expr.

COUNT(DISTINCT expr) returns the number of unique, non-null values that are in the column identified by expr.

Examples

1.         The slide example displays the number of employees in department 50.

2.         The slide example displays the number of employees in department 80 who can earn a commission.

DISTINCT Keyword

Use the DISTINCT keyword to suppress the counting of any duplicate values in a column.

The example in the slide displays the number of distinct department values that are in the EMPLOYEES table.

Group Functions and Null Values

All group functions ignore null values in the column.

The NVL function forces group functions to include null values.

Examples

1. The average is calculated based on only those rows in the table where a valid value is stored in the COMMISSION_PCT column. The average is calculated as the total commission that is paid to all employees divided by the number of employees receiving a commission (four).
2. The average is calculated based on all rows in the table, regardless of whether null values are stored in the COMMISSION_PCT column. The average is calculated as the total commission that is paid to all employees divided by the total number of employees in the company (20).

Creating Groups of Data

Until this point in our discussion, all group functions have treated the table as one large group of information.

At times, however, you need to divide the table of information into smaller groups. This can be done by using the GROUP BY clause.

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.

In the syntax:

group_by_expression specifies columns whose values determine the basis for
                                                            grouping rows

Guidelines

•         If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.

•         Using a WHERE clause, you can exclude rows before dividing them into groups.

•         You must include the columns in the GROUP BY clause.

•         You cannot use a column alias in the GROUP BY clause.

Using the GROUP BY Clause

When using the GROUP BY clause, make sure that all columns in the SELECT list that are not group functions are included in the GROUP BY clause. The example in the slide displays the department number and the average salary for each department. Here is how this SELECT statement, containing a GROUP BY clause, is evaluated:

•         The SELECT clause specifies the columns to be retrieved, as follows:

-          Department number column in the EMPLOYEES table

-          The average of all the salaries in the group that you specified in the GROUP BY clause

•         The FROM clause specifies the tables that the database must access: the EMPLOYEES table.

•         The WHERE clause specifies the rows to be retrieved. Because there is no WHERE clause, all rows are retrieved by default.

•         The GROUP BY clause specifies how the rows should be grouped. The rows are grouped by department number, so the AVG function that is applied to the salary column calculates the average salary for each department.

The GROUP BY column does not have to be in the SELECT clause. For example, the SELECT statement in the slide displays the average salaries for each department without displaying the respective department numbers. Without the department numbers, however, the results do not look meaningful.

You can use the group function in the ORDER BY clause:

  SELECT   department_id, AVG(salary)

 FROM     employees

 GROUP BY department_id

 ORDER BY AVG(salary);

Groups Within Groups

Sometimes you need to see results for groups within groups. The slide shows a report that displays the total salary that is paid to each job title in each department.

The EMPLOYEES table is grouped first by department number and then by job title within that grouping. For example, the four stock clerks in department 50 are grouped together, and a single result (total salary) is produced for all stock clerks in the group.

You can return summary results for groups and subgroups by listing more than one GROUP BY column. You can determine the default sort order of the results by the order of the columns in the GROUP BY clause. In the slide example, the SELECT statement containing a GROUP BY clause is evaluated as follows:

•         The SELECT clause specifies the column to be retrieved:

-          Department number in the EMPLOYEES table

-          Job ID in the EMPLOYEES table

-          The sum of all the salaries in the group that you specified in the GROUP BY clause

•         The FROM clause specifies the tables that the database must access: the EMPLOYEES table.

•         The GROUP BY clause specifies how you must group the rows:

-          First, the rows are grouped by department number.

-          Second, the rows are grouped by job ID in the department number groups.

So the SUM function is applied to the salary column for all job IDs in each department number group.

Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause.

The WHERE clause cannot be used to restrict groups. use the HAVING clause to restrict groups:

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. To find the maximum salary in each of the departments that have a maximum salary greater than $10,000, you need to do the following:

1.         Find the average salary for each department by grouping by department number.

2.         Restrict the groups to those departments with a maximum salary greater than $10,000.

Restricting Group Results with the HAVING Clause

You use the HAVING clause to specify which groups are to be displayed, thus further restricting 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 recommended 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.

Using the HAVING Clause

The slide example displays department numbers and maximum salaries for those departments with a maximum salary that is greater than $10,000.

You can use the GROUP BY clause without using a group function in the SELECT list.

If you restrict rows based on the result of a group function, you must have a GROUP BY clause as well as the HAVING clause.

The following example displays the department numbers and average salaries for those departments with a maximum salary that is greater than $10,000:

   SELECT   department_id, AVG(salary)

   FROM     employees

   GROUP BY department_id

   HAVING   max(salary)>10000;

Nesting Group Functions

Group functions can be nested to a depth of two.

Lesson 5 - Displaying Data from Multiple Tables

Obtaining Data from Multiple Tables

Sometimes you need to use data from more than one table. In the slide example, the report displays data from two separate tables:

•         Employee IDs exist in the EMPLOYEES table.

•         Department IDs exist in both the EMPLOYEES and DEPARTMENTS tables.

•         Department names exist in the DEPARTMENTS table.

To produce the report, you need to link the EMPLOYEES and DEPARTMENTS tables and access data from both of them.

Use a join to query data from more than one table:

SELECT         table1.column, table2.column

FROM table1

[NATURAL JOIN table2] |

[JOIN table2 USING (column_name)] |

[JOIN table2

  ON (table1.column_name = table2.column_name)]|

[LEFT|RIGHT|FULL OUTER JOIN table2

  ON (table1.column_name = table2.column_name)]|

[CROSS JOIN table2];

NATURAL JOIN

•         The NATURAL JOIN clause is based on all columns in the two tables that have the same name.

•         It selects rows from the two tables that have equal values in all matched columns.

•         If the columns having the same names have different data types, an error is returned.

In the syntax:

table1.column denotes the table and column from which data is retrieved

NATURAL JOIN joins two tables based on the same column name

JOIN table USING column_name performs an equijoin based on the column name

JOIN table ON table1.column_name performs an equijoin based on the condition in the ON clause, = table2.column_name

LEFT/RIGHT/FULL OUTER is used to perform outer joins

CROSS JOIN returns a Cartesian product from the two tables

Creating Natural Joins

You can join tables automatically based on columns in the two tables that have matching data types and names. You do this by using the keywords NATURAL JOIN.

Note: The join can happen on only those columns that have the same names and data types in both tables. If the columns have the same name but different data types, then the NATURAL JOIN syntax causes an error.

Retrieving Records with Natural Joins

In the example in the slide, the LOCATIONS table is joined to the DEPARTMENT table by the LOCATION_ID column, which is the only column of the same name in both tables. If other common columns were present, the join would have used them all.

SELECT department_id, department_name,

       location_id, city

FROM   departments

NATURAL JOIN locations ;

Natural Joins with a WHERE Clause

Additional restrictions on a natural join are implemented by using a WHERE clause. The following example limits the rows of output to those with a department ID equal to 20 or 50:

   SELECT  department_id, department_name,

           location_id, city

   FROM    departments

   NATURAL JOIN locations

   WHERE   department_id IN (20, 50);

USING Clause

Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. The columns that are referenced in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement.

SELECT employees.employee_id, employees.last_name,

       departments.location_id, department_id

FROM   employees JOIN departments

USING (department_id) ;

For example, the following statement is valid:

SELECT l.city, d.department_name

FROM   locations l JOIN departments d USING (location_id)

WHERE  location_id = 1400;

The following statement is invalid because the LOCATION_ID is qualified in the WHERE clause:

SELECT l.city, d.department_name

FROM locations l JOIN departments d USING (location_id)

WHERE d.location_id = 1400;

ORA-25154: column part of USING clause cannot have qualifier

The same restriction also applies to NATURAL joins. Therefore, columns that have the same name in both tables must be used without any qualifiers.

To determine an employee’s department name, you compare the value in the DEPARTMENT_ID column in the EMPLOYEES table with the DEPARTMENT_ID values in the DEPARTMENTS table. The relationship between the EMPLOYEES and DEPARTMENTS tables is an equijoin; that is, values in the DEPARTMENT_ID column in both tables must be equal. Frequently, this type of join involves primary and foreign key complements.

Note: Equijoins are also called simple joins or inner joins.

Qualifying Ambiguous Column Names

You need to qualify the names of the columns with the table name to avoid ambiguity. Without the table prefixes, the DEPARTMENT_ID column in the SELECT list could be from either the DEPARTMENTS table or the EMPLOYEES table. It is necessary to add the table prefix to execute your query:

SELECT employees.employee_id, employees.last_name,

       departments.department_id, departments.location_id

FROM   employees JOIN departments

ON     employees.department_id = departments.department_id;

If there are no common column names between the two tables, there is no need to qualify the columns. However, using the table prefix improves performance, because you tell the Oracle server exactly where to find the columns.

Note: When joining with the USING clause, you cannot qualify a column that is used in the USING clause itself. Furthermore, if that column is used anywhere in the SQL statement, you cannot alias it.

Using Table Aliases

Qualifying column names with table names can be very time consuming, particularly if table names are lengthy. You can use table aliases instead of table names. Just as a column alias gives a column another name, a table alias gives a table another name. Table aliases help to keep SQL code smaller, therefore using less memory.

SELECT e.employee_id, e.last_name,

       d.location_id, department_id

FROM   employees e JOIN departments d

USING (department_id) ;

Notice how table aliases are identified in the FROM clause in the example. The table name is specified in full, followed by a space and then the table alias. The EMPLOYEES table has been given an alias of e, and the DEPARTMENTS table an alias of d.

Guidelines

•         Table aliases can be up to 30 characters in length, but shorter aliases are better than longer ones.

•         If a table alias is used for a particular table name in the FROM clause, then that table alias must be substituted for the table name throughout the SELECT statement.

•         Table aliases should be meaningful.

•         The table alias is valid for only the current SELECT statement.

ON Clause

Use the ON clause to specify a join condition. This lets you specify join conditions separate from any search or filter conditions in the WHERE clause.

Creating Joins with the ON Clause

In this example, the DEPARTMENT_ID columns in the EMPLOYEES and DEPARTMENTS table are joined using the ON clause. Wherever a department ID in the EMPLOYEES table equals a department ID in the DEPARTMENTS table, the row is returned.

You can also use the ON clause to join columns that have different names.

SELECT e.employee_id, e.last_name, e.department_id,

       d.department_id, d.location_id

FROM   employees e JOIN departments d

ON     (e.department_id = d.department_id);

Selfjoins

Joining a Table to Itself

Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need to join the EMPLOYEES table to itself, or perform a self join. For example, to find the name of Lorentz’s manager, you need to:

•         Find Lorentz in the EMPLOYEES table by looking at the LAST_NAME column

•         Find the manager number for Lorentz by looking at the MANAGER_ID column. Lorentz’s manager number is 103.

•         Find the name of the manager with EMPLOYEE_ID 103 by looking at the LAST_NAME column. Hunold’s employee number is 103, so Hunold is Lorentz’s manager.

In this process, you look in the table twice. The first time you look in the table to find Lorentz in the LAST_NAME column and MANAGER_ID value of 103. The second time you look in the EMPLOYEE_ID column to find 103 and the LAST_NAME column to find Hunold.

The ON clause can also be used to join columns that have different names, within the same table or in a different table.

SELECT e.last_name emp, m.last_name mgr

FROM   employees e JOIN employees m

ON    (e.manager_id = m.employee_id);

The example shown is a self-join of the EMPLOYEES table, based on the EMPLOYEE_ID and MANAGER_ID columns.

Applying Additional Conditions to a Join

You can apply additional conditions to the join.

The example shown performs a join on the EMPLOYEES and DEPARTMENTS tables and, in addition, displays only employees who have a manager ID of 149. To add additional conditions to the ON clause, you can add AND clauses. Alternatively, you can use a WHERE clause to apply additional conditions:

SELECT e.employee_id, e.last_name, e.department_id,

       d.department_id, d.location_id

FROM   employees e JOIN departments d

ON    (e.department_id = d.department_id)

WHERE  e.manager_id = 149;

Nonequijoins

A nonequijoin is a join condition containing something other than an equality operator.

The relationship between the EMPLOYEES table and the JOB_GRADES table is an example of a nonequijoin. A relationship between the two tables is that the SALARY column in the EMPLOYEES table must be between the values in the LOWEST_SALARY and HIGHEST_SALARY columns of the JOB_GRADES table. The relationship is obtained using an operator other than equality (=).

SELECT e.last_name, e.salary, j.grade_level

FROM   employees e JOIN job_grades j

ON     e.salary

       BETWEEN j.lowest_sal AND j.highest_sal;

The slide example creates a nonequijoin to evaluate an employee’s salary grade. The salary must be between any pair of the low and high salary ranges.

It is important to note that all employees appear exactly once when this query is executed. No employee is repeated in the list. There are two reasons for this:

•         None of the rows in the job grade table contain grades that overlap. That is, the salary value for an employee can lie only between the low salary and high salary values of one of the rows in the salary grade table.

•         All of the employees’ salaries lie within the limits that are provided by the job grade table. That is, no employee earns less than the lowest value contained in the LOWEST_SAL column or more than the highest value contained in the HIGHEST_SAL column.

Note: Other conditions (such as <= and >=) can be used, but BETWEEN is the simplest. Remember to specify the low value first and the high value last when using BETWEEN.

Table aliases have been specified in the slide example for performance reasons, not because of possible ambiguity.

Outer Joins

Returning Records with No Direct Match with Outer Joins

If a row does not satisfy a join condition, the row does not appear in the query result. For example, in the equijoin condition of EMPLOYEES and DEPARTMENTS tables, department ID 190 does not appear because there are no employees with that department ID recorded in the EMPLOYEES table. Instead of seeing 20 employees in the result set, you see 19 records.

To return the department record that does not have any employees, you can use an outer join.

INNER Versus OUTER Joins

Joining tables with the NATURAL JOIN, USING, or ON clauses results in an inner join. Any unmatched rows are not displayed in the output. To return the unmatched rows, you can use an outer join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other table satisfy the join condition.

There are three types of outer joins:

•         LEFT OUTER

•         RIGHT OUTER

•         FULL OUTER

Example of LEFT OUTER JOIN

This query retrieves all rows in the EMPLOYEES table, which is the left table even if there is no match in the DEPARTMENTS table.

SELECT e.last_name, e.department_id, d.department_name

FROM   employees e LEFT OUTER JOIN departments d

ON   (e.department_id = d.department_id) ;

Example of RIGHT OUTER JOIN

This query retrieves all rows in the DEPARTMENTS table, which is the right table even if there is no match in the EMPLOYEES table.

SELECT e.last_name, e.department_id, d.department_name

FROM   employees e RIGHT OUTER JOIN departments d

ON    (e.department_id = d.department_id) ;

Example of FULL OUTER JOIN

This query retrieves all rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table. It also retrieves all rows in the DEPARTMENTS table, even if there is no match in the EMPLOYEES table.

SELECT e.last_name, d.department_id, d.department_name

FROM   employees e FULL OUTER JOIN departments d

ON   (e.department_id = d.department_id) ;

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 product tends to generate a large number of rows, and the result is rarely useful. You should always include a valid join condition 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 a reasonable amount of data.

A Cartesian product is generated if a join condition is omitted. The example in the slide displays employee last name and department name from the EMPLOYEES and DEPARTMENTS tables. Because no join condition has been specified, all rows (20 rows) from the EMPLOYEES table are joined with all rows (8 rows) in the DEPARTMENTS table, thereby generating 160 rows in the output.

SELECT last_name, department_name

FROM   employees

CROSS JOIN departments ;

Creating Cross Joins

•         The CROSS JOIN clause produces the cross-product of two tables.

•         This is also called a Cartesian product between the two tables.

Lesson 6 - Using Subqueries to Solve Queries

Using a Subquery to Solve a Problem

Suppose you want to write a query to find out who earns a salary greater than Abel’s salary.

To solve this problem, you need two queries: one to find how much Abel earns, and a second query to find who earns more than that amount.

You can solve this problem by combining the two queries, placing one query inside the other query.

The inner query (or subquery) returns a value that is used by the outer query (or main query). Using a subquery is equivalent to performing two sequential queries and using the result of the first query as the search value in the second query.

Subquery Syntax

SELECT         select_list

FROM table

WHERE          expr operator

                                    (SELECT        select_list

                               FROM                  table);

•         The subquery (inner query) executes once before the main query (outer query).

•         The result of the subquery is used by the main query.

Subquery Syntax

A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. You can build powerful statements out of simple ones by using subqueries. They can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself.

You can place the subquery in a number of SQL clauses, including the following:

•         WHERE clause

•         HAVING clause

•         FROM clause

In the syntax:

            operator includes a comparison condition such as >, =, or IN

      Note: Comparison conditions fall into two classes: single-row operators
(>, =, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL).

The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The subquery generally executes first, and its output is used to complete the query condition for the main (or outer) query.

Guidelines for Using Subqueries

•         A subquery must be enclosed in parentheses.

•         Place the subquery on the right side of the comparison condition for readability.

•         With Oracle8i and later releases, an ORDER BY clause can be used and is required in the subquery to perform Top-N analysis.

-          Before Oracle8i, however, subqueries could not contain an ORDER BY clause. Only one ORDER BY clause could be used for a SELECT statement; if specified, it had to be the last clause in the main SELECT statement.

•         Two classes of comparison conditions are used in subqueries: single-row operators and
multiple-row operators.

Types of Subqueries

•         Single-row subqueries: Queries that return only one row from the inner SELECT statement

•         Multiple-row subqueries: Queries that return more than one row from the inner SELECT statement

Note: There are also multiple-column subqueries, which are queries that return more than one column from the inner SELECT statement.

Single-Row Subqueries

A single-row subquery is one that returns one row from the inner SELECT statement. This type of subquery uses a single-row operator. The slide gives a list of single-row operators.

Example

Display the employees whose job ID is the same as that of employee 141:

   SELECT last_name, job_id

   FROM   employees

   WHERE  job_id =

                   (SELECT job_id

                    FROM   employees

                    WHERE  employee_id = 141);

Executing Single-Row Subqueries

A SELECT statement can be considered as a query block. The example in the slide displays employees whose job ID is the same as that of employee 141 and whose salary is greater than that of employee 143.

The example consists of three query blocks: the outer query and two inner queries. The inner query blocks are executed first, producing the query results ST_CLERK and 2600, respectively. The outer query block is then processed and uses the values that were returned by the inner queries to complete its search conditions.

Both inner queries return single values (ST_CLERK and 2600, respectively), so this SQL statement is called a single-row subquery.

Note: The outer and inner queries can get data from different tables.

Using Group Functions in a Subquery

SELECT last_name, job_id, salary

FROM   employees

WHERE  salary =

                (SELECT MIN(salary)

                 FROM   employees);

You can display data from a main query by using a group function in a subquery to return a single row. The subquery is in parentheses and is placed after the comparison condition.

The example in the slide displays the employee last name, job ID, and salary of all employees whose salary is equal to the minimum salary. The MIN group function returns a single value (2500) to the outer query.

The HAVING Clause with Subqueries

You can use subqueries not only in the WHERE clause but also in the HAVING clause. The Oracle server executes the subquery, and the results are returned into the HAVING clause of the main query.

The SQL statement in the slide displays all the departments that have a minimum salary greater than that of department 50.

SELECT   department_id, MIN(salary)

FROM     employees

GROUP BY department_id

HAVING   MIN(salary) >

                       (SELECT MIN(salary)

                        FROM   employees

                        WHERE  department_id = 50);

Errors with Subqueries

One common error with subqueries occurs when more than one row is returned for a single-row subquery.

In the SQL statement in the slide, the subquery contains a GROUP BY clause, which implies that the subquery will return multiple rows, one for each group that it finds. In this case, the result of the subquery are 4400, 6000, 2500, 4200, 7000, 17000, and 8300.

The outer query takes those results and uses them in its WHERE clause. The WHERE clause contains an equal (=) operator, a single-row comparison operator that expects only one value. The = operator cannot accept more than one value from the subquery and, therefore, generates the error.

To correct this error, change the = operator to IN.

Problems with Subqueries

A common problem with subqueries occurs when no rows are returned by the inner query.

In the SQL statement in the slide, the subquery contains a WHERE clause. Presumably, the intention is to find the employee whose name is Haas. The statement is correct but selects no rows when executed.

There is no employee named Haas. So the subquery returns no rows. The outer query takes the results of the subquery (null) and uses these results in its WHERE clause. The outer query finds no employee with a job ID equal to null, and so returns no rows. If a job existed with a value of null, the row is not returned because comparison of two null values yields a null; therefore, the WHERE condition is not true.

Multiple-Row Subqueries

Subqueries that return more than one row are called multiple-row subqueries. You use a multiple-row operator, instead of a single-row operator, with a multiple-row subquery. The multiple-row operator expects one or more values:

   SELECT last_name, salary, department_id

   FROM   employees

   WHERE  salary IN (SELECT   MIN(salary)

                     FROM     employees

                     GROUP BY department_id);

Example

Find the employees who earn the same salary as the minimum salary for each department.

The inner query is executed first, producing a query result. The main query block is then processed and uses the values that were returned by the inner query to complete its search condition. In fact, the main query appears to the Oracle server as follows:

   SELECT last_name, salary, department_id

   FROM   employees

   WHERE  salary IN (2500, 4200, 4400, 6000, 7000, 8300, 8600, 17000);

The ANY operator (and its synonym, the SOME operator) compares a value to each value returned by a subquery. The slide example displays employees who are not IT programmers and whose salary is less than that of any IT programmer. The maximum salary that a programmer earns is $9,000.

<ANY means less than the maximum. >ANY means more than the minimum. =ANY is equivalent to IN.

The ALL operator compares a value to every value returned by a subquery. The slide example displays employees whose salary is less than the salary of all employees with a job ID of IT_PROG and whose job is not IT_PROG.

>ALL means more than the maximum, and <ALL means less than the minimum.

The NOT operator can be used with IN, ANY, and ALL operators.

Returning Nulls in the Resulting Set of a Subquery

The SQL statement in the slide attempts to display all the employees who do not have any subordinates. Logically, this SQL statement should have returned 12 rows. However, the SQL statement does not return any rows. One of the values returned by the inner query is a null value, and, therefore, the entire query returns no rows.

The reason is that all conditions that compare a null value result in a null. So whenever null values are likely to be part of the results set of a subquery, do not use the NOT IN operator. The NOT IN operator is equivalent to <> ALL.

Notice that the null value as part of the results set of a subquery is not a problem if you use the IN operator. The IN operator is equivalent to =ANY. For example, to display the employees who have subordinates, use the following SQL statement:

    SELECT emp.last_name

    FROM   employees emp

    WHERE  emp.employee_id  IN

                              (SELECT mgr.manager_id

                               FROM   employees mgr);

Alternatively, a WHERE clause can be included in the subquery to display all employees who do not have any subordinates:

SELECT last_name FROM employees

WHERE  employee_id NOT IN

                        (SELECT manager_id

                         FROM   employees

                         WHERE  manager_id IS NOT NULL);

Lesson 7 - Using the Set Operators

Set Operators

Set operators combine the results of two or more component queries into one result. Queries containing set operators are called compound queries.

 

All set operators have equal precedence. If a SQL statement contains multiple set operators, the Oracle server evaluates them from left (top) to right (bottom) if no parentheses explicitly specify another order. You should use parentheses to specify the order of evaluation explicitly in queries that use the INTERSECT operator with other set operators.

UNION Operator

The UNION operator returns all rows that are selected by either query. Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows.

Guidelines

•         The number of columns and the data types of the columns being selected must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.

•         UNION operates over all of the columns being selected.

•         NULL values are not ignored during duplicate checking.

•         The IN operator has a higher precedence than the UNION operator.

•         By default, the output is sorted in ascending order of the first column of the SELECT clause.

Using the UNION Operator

The UNION operator eliminates any duplicate records. If records that occur in both the EMPLOYEES and the JOB_HISTORY tables are identical, the records are displayed only once. Observe in the output shown in the slide that the record for the employee with the EMPLOYEE_ID 200 appears twice because the JOB_ID is different in each row.

Consider the following example:

  SELECT  employee_id, job_id, department_id

  FROM    employees

  UNION

  SELECT  employee_id, job_id, department_id

  FROM    job_history;

In the preceding output, employee 200 appears three times. Why? Notice the DEPARTMENT_ID values for employee 200. One row has a DEPARTMENT_ID of 90, another 10, and the third 90. Because of these unique combinations of job IDs and department IDs, each row for employee 200 is unique and therefore not considered to be a duplicate. Observe that the output is sorted in ascending order of the first column of the SELECT clause (in this case, EMPLOYEE_ID).

UNION ALL Operator

Use the UNION ALL operator to return all rows from multiple queries.

Guidelines

The guidelines for UNION and UNION ALL are the same, with the following two exceptions that pertain to UNION ALL:

•         Unlike UNION, duplicate rows are not eliminated and the output is not sorted by default.

•         The DISTINCT keyword cannot be used.

In the example, 30 rows are selected. The combination of the two tables totals to 30 rows. The UNION ALL operator does not eliminate duplicate rows. UNION returns all distinct rows selected by either query. UNION ALL returns all rows selected by either query, including all duplicates. Consider the query on the slide, now written with the UNION clause:

  SELECT   employee_id, job_id,department_id
  FROM     employees
  UNION
  SELECT   employee_id, job_id,department_id
  FROM     job_history
  ORDER BY employee_id;

The preceding query returns 29 rows. This is because it eliminates the following row (because it is a duplicate):

INTERSECT Operator

Use the INTERSECT operator to return all rows that are common to multiple queries.

Guidelines

•         The number of columns and the data types of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.

•         Reversing the order of the intersected tables does not alter the result.

•         INTERSECT does not ignore NULL values.

In the example in this slide, the query returns only the records that have the same values in the selected columns in both tables.

What will be the results if you add the DEPARTMENT_ID column to the SELECT statement from the EMPLOYEES table and add the DEPARTMENT_ID column to the SELECT statement from the JOB_HISTORY table and run this query? The results may be different because of the introduction of another column whose values may or may not be duplicates.

Example

            SELECT employee_id, job_id, department_id

            FROM   employees

            INTERSECT

            SELECT employee_id, job_id, department_id

            FROM   job_history;

Employee 200 is no longer part of the results because the EMPLOYEES.DEPARTMENT_ID value is different from the JOB_HISTORY.DEPARTMENT_ID value.

MINUS Operator

Use the MINUS operator to return rows returned by the first query that are not present in the second query (the first SELECT statement MINUS the second SELECT statement).

Note: The number of columns and the data types of the columns being selected by the SELECT statements in the queries must be identical in all the SELECT statements used in the query. The names of the columns need not be identical.

In the example in the slide, the employee IDs in the JOB_HISTORY table are subtracted from those in the EMPLOYEES table. The results set displays the employees remaining after the subtraction; they are represented by rows that exist in the EMPLOYEES table but do not exist in the JOB_HISTORY table. These are the records of the employees who have not changed their jobs even once.

Set Operator Guidelines

•         The expressions in the select lists of the queries must match in number and data type. Queries that use UNION, UNION ALL, INTERSECT, and MINUS operators in their WHERE clause must have the same number and type of columns in their SELECT list. For example:

 SELECT employee_id, department_id

 FROM   employees

 WHERE  (employee_id, department_id)

        IN (SELECT  employee_id, department_id

            FROM    employees

            UNION

            SELECT  employee_id, department_id

            FROM    job_history);

•         The ORDER BY clause:

-          Can appear only at the very end of the statement

-          Will accept the column name, an alias, or the positional notation

•         The column name or alias, if used in an ORDER BY clause, must be from the first SELECT list.

•         Set operators can be used in subqueries.

The Oracle Server and Set Operators

When a query uses set operators, the Oracle server eliminates duplicate rows automatically except in the case of the UNION ALL operator. The column names in the output are decided by the column list in the first SELECT statement. By default, the output is sorted in ascending order of the first column of the SELECT clause.

The corresponding expressions in the select lists of the component queries of a compound query must match in number and data type. If component queries select character data, the data type of the return values is determined as follows:

•         If both queries select values of data type CHAR, the returned values have data type CHAR.

•         If either or both of the queries select values of data type VARCHAR2, the returned values have data type VARCHAR2.

Matching the SELECT Statements

Because the expressions in the select lists of the queries must match in number, you can use dummy columns and the data type conversion functions to comply with this rule. In the slide, the name location is given as the dummy column heading. The TO_NUMBER function is used in the first query to match the NUMBER data type of the LOCATION_ID column retrieved by the second query. Similarly, the TO_DATE function in the second query is used to match the DATE data type of the HIRE_DATE column retrieved by the first query.

SELECT department_id, TO_NUMBER(null)

       location, hire_date

FROM   employees

UNION

SELECT department_id, location_id,  TO_DATE(null)

FROM   departments;

SELECT employee_id, job_id,salary

FROM   employees

UNION

SELECT employee_id, job_id,0

FROM   job_history;

Lesson 8 - Manipulating Data

Data Manipulation Language

Data manipulation language (DML) is a core part of SQL. When you want to add, update, or delete data in the database, you execute a DML statement. A collection of DML statements that form a logical unit of work is called a transaction.

Consider a banking database. When a bank customer transfers money from a savings account to a checking account, the transaction might consist of three separate operations: decrease the savings account, increase the checking account, and record the transaction in the transaction journal. The Oracle server must guarantee that all three SQL statements are performed to maintain the accounts in proper balance. When something prevents one of the statements in the transaction from executing, the other statements of the transaction must be undone.

•         A DML statement is executed when you:

–        Add new rows to a table

–        Modify existing rows in a table

–        Remove existing rows from a table

•         A transaction consists of a collection of DML statements that form a logical unit of work.

Adding a New Row to a Table

You can add new rows to a table by issuing the INSERT statement.

INSERT INTO           table [(column [, column...])]

VALUES                    (value [, value...]);

In the syntax:

table                is the name of the table

column                        is the name of the column in the table to populate

value                is the corresponding value for the column

INSERT INTO departments(department_id,

       department_name, manager_id, location_id)

VALUES (70, 'Public Relations', 100, 1700);

Note: This statement with the VALUES clause adds only one row at a time to a table.

Because you can insert a new row that contains values for each column, the column list is not required in the INSERT clause. However, if you do not use the column list, the values must be listed according to the default order of the columns in the table, and a value must be provided for each column.

   DESCRIBE  departments

   For clarity, use the column list in the INSERT clause.
Enclose character and date values in single quotation marks; it is not recommended that you enclose numeric values in single quotation marks.

Number values should not be enclosed in single quotation marks, because implicit conversion may take place for numeric values that are assigned to NUMBER data type columns if single quotation marks are included.

Methods for Inserting Null Values

 

Be sure that you can use null values in the targeted column by verifying the Null? status with the iSQL*Plus DESCRIBE command.

The Oracle server automatically enforces all data types, data ranges, and data integrity constraints. Any column that is not listed explicitly obtains a null value in the new row.

Common errors that can occur during user input:

•         Mandatory value missing for a NOT NULL column

•         Duplicate value violates uniqueness constraint

•         Foreign key constraint violated

•         CHECK constraint violated

•         Data type mismatch

•         Value too wide to fit in column

Inserting Special Values by Using SQL Functions

You can use functions to enter special values in your table.

The slide example records information for employee Popp in the EMPLOYEES table. It supplies the current date and time in the HIRE_DATE column. It uses the SYSDATE function for current date and time.

You can also use the USER function when inserting rows in a table. The USER function records the current username.

INSERT INTO employees (employee_id,

                 first_name, last_name,

                 email, phone_number,

                 hire_date, job_id, salary,

                 commission_pct, manager_id,

                 department_id)

VALUES                       (113,

                 'Louis', 'Popp',

                 'LPOPP', '515.124.4567',

                 SYSDATE, 'AC_ACCOUNT', 6900,

                 NULL, 205, 100);

Inserting Specific Date and Time Values

The DD-MON-YY format is usually used to insert a date value. With this format, recall that the century defaults to the current century. Because the date also contains time information, the default time is midnight (00:00:00).

If a date must be entered in a format other than the default format (for example, with another century or a specific time), you must use the TO_DATE function.

The example in the slide records information for employee Raphealy in the EMPLOYEES table. It sets the HIRE_DATE column to be February 3, 1999. If you use the following statement instead of the one shown in the slide, the year of the hire date is interpreted as 2099.

   INSERT INTO employees

   VALUES      (114,

                'Den', 'Raphealy',

                'DRAPHEAL', '515.127.4561',

                '03-FEB-99',

                'AC_ACCOUNT', 11000, NULL, 100, 30);

If the RR format is used, the system provides the correct century automatically, even if it is not the current one.

INSERT INTO employees

VALUES      (114,

             'Den', 'Raphealy',

             'DRAPHEAL', '515.127.4561',

             TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),

             'AC_ACCOUNT', 11000, NULL, 100, 30);

Creating a Script to Manipulate Data

You can save commands with substitution variables to a file and execute the commands in the file. The slide example records information for a department in the DEPARTMENTS table.

Run the script file and you are prompted for input for each of the & substitution variables. After entering a value for the substitution variable, click the Continue button. The values that you input are then substituted into the statement. This enables you to run the same script file over and over but supply a different set of values each time you run it.

INSERT INTO departments

           (department_id, department_name, location_id)

VALUES     (&department_id, '&department_name',&location);

Copying Rows from Another Table

You can use the INSERT statement to add rows to a table where the values are derived from existing tables. In place of the VALUES clause, you use a subquery.

Syntax

INSERT INTO table [ column (, column) ] subquery;

In the syntax:

table                is the table name

column                        is the name of the column in the table to populate

subquery          is the subquery that returns rows to the table

The number of columns and their data types in the column list of the INSERT clause must match the number of values and their data types in the subquery. To create a copy of the rows of a table, use SELECT * in the subquery:

INSERT INTO copy_emp

   SELECT *

   FROM   employees;

For more information, see “SELECT” (“subqueries” section) in the Oracle Database SQL Reference.

INSERT INTO sales_reps(id, name, salary, commission_pct)

  SELECT employee_id, last_name, salary, commission_pct

  FROM   employees

  WHERE  job_id LIKE '%REP%';

Updating Rows

You can modify existing rows by using the UPDATE statement.

In the syntax:

table                is the name of the table

column                        is the name of the column in the table to populate

value                is the corresponding value or subquery for the column

condition         identifies the rows to be updated and is composed of column names,                                              expressions, constants, subqueries, and comparison operators

Confirm the update operation by querying the table to display the updated rows.

For more information, see “UPDATE” in the Oracle Database SQL Reference.

Note: In general, use the primary key to identify a single row. Using other columns can unexpectedly cause several rows to be updated. For example, identifying a single row in the EMPLOYEES table by name is dangerous, because more than one employee may have the same name.

UPDATE                    table

SET                 column = value [, column = value, ...]

[WHERE                    condition];

UPDATE employees

SET    department_id = 70

WHERE  employee_id = 113;

UPDATE        copy_emp

SET    department_id = 110;

The UPDATE statement modifies specific rows if the WHERE clause is specified. The slide example transfers employee 113 (Popp) to department 70.

If you omit the WHERE clause, all the rows in the table are modified.

Note: The COPY_EMP table has the same data as the EMPLOYEES table.

Updating Two Columns with a Subquery

You can update multiple columns in the SET clause of an UPDATE statement by writing multiple subqueries.

Syntax

  UPDATE table

 SET     column  =

                                       (SELECT     column

                                        FROM table

                                        WHERE condition)

           [ ,

            column  =

                                       (SELECT     column

                                        FROM table

                                        WHERE condition)]

    [WHERE  condition ]         ;

Note: If no rows are updated, the message “0 rows updated” is returned.

UPDATE   employees

SET      job_id  = (SELECT  job_id

                    FROM    employees

                    WHERE   employee_id = 205),

         salary  = (SELECT  salary

                    FROM    employees

                    WHERE   employee_id = 205)

WHERE    employee_id    =  114;

Updating Rows Based on Another Table

You can use subqueries in UPDATE statements to update rows in a table. The example in the slide updates the COPY_EMP table based on the values from the EMPLOYEES table. It changes the department number of all employees with employee 200’s job ID to employee 100’s current department number.

UPDATE  copy_emp

SET     department_id  =  (SELECT department_id

                           FROM employees

                           WHERE employee_id = 100)

WHERE   job_id         =  (SELECT job_id

                           FROM employees

                           WHERE employee_id = 200);

Deleting Rows

You can remove existing rows by using the DELETE statement.

In the syntax:

table                is the table name

condition         identifies the rows to be deleted and is composed of column names,                                               expressions, constants, subqueries, and comparison operators

Note: If no rows are deleted, the message “0 rows deleted” is returned.

For more information, see “DELETE” in the Oracle Database SQL Reference.

You can delete specific rows by specifying the WHERE clause in the DELETE statement. The slide example deletes the Finance department from the DEPARTMENTS table. You can confirm the delete operation by displaying the deleted rows using the SELECT statement.

    SELECT  *

    FROM    departments

    WHERE   department_name = 'Finance';

    no rows selected.

If you omit the WHERE clause, all rows in the table are deleted. The second example in the slide deletes all the rows from the COPY_EMP table, because no WHERE clause has been specified.

Example

Remove rows identified in the WHERE clause.

    DELETE FROM  employees WHERE employee_id = 114;

    1 row deleted.

    DELETE FROM  departments WHERE department_id IN (30, 40);

    2 rows deleted.

•         All rows in the table are deleted if you omit the WHERE clause:

DELETE FROM  copy_emp;

Deleting Rows Based on Another Table

You can use subqueries to delete rows from a table based on values from another table. The example in the slide deletes all the employees who are in a department where the department name contains the string Public. The subquery searches the DEPARTMENTS table to find the department number based on the department name containing the string Public. The subquery then feeds the department number to the main query, which deletes rows of data from the EMPLOYEES table based on this department number.

DELETE FROM employees

WHERE  department_id =

                (SELECT department_id

                 FROM   departments

                 WHERE  department_name

                        LIKE '%Public%');

TRUNCATE Statement

TRUNCATE TABLE table_name;

TRUNCATE TABLE copy_emp;

•         Removes all rows from a table, leaving the table empty and the table structure intact

•         Is a data definition language (DDL) statement rather than a DML statement; cannot easily be undone

•         Syntax:

Using a Subquery in an INSERT Statement

                                     

INSERT INTO

        (SELECT employee_id, last_name,

                email, hire_date, job_id, salary,

                department_id

         FROM   employees

         WHERE  department_id = 50)

VALUES (99999, 'Taylor', 'DTAYLOR',

        TO_DATE('07-JUN-99', 'DD-MON-RR'),

        'ST_CLERK', 5000, 50);

Database Transactions

The Oracle server ensures data consistency based on transactions. Transactions give you more flexibility and control when changing data, and they ensure data consistency in the event of user process failure or system failure.

Transactions consist of DML statements that make up one consistent change to the data. For example, a transfer of funds between two accounts should include the debit to one account and the credit to another account in the same amount. Both actions should either fail or succeed together; the credit should not be committed without the debit.

Transaction Types

 

When Does a Transaction Start and End?

A transaction begins when the first DML statement is encountered and ends when one of the following occurs:

•         A COMMIT or ROLLBACK statement is issued.

•         A DDL statement, such as CREATE, is issued.

•         A DCL statement is issued.

•         The user exits iSQL*Plus.

•         A machine fails or the system crashes.

After one transaction ends, the next executable SQL statement automatically starts the next transaction.

A DDL statement or a DCL statement is automatically committed and therefore implicitly ends a transaction.

Advantages of COMMIT and ROLLBACK

With the COMMIT and ROLLBACK statements, you have control over making changes to the data permanent.

Explicit Transaction Control Statements

You can control the logic of transactions by using the COMMIT, SAVEPOINT, and ROLLBACK statements.

 

Rolling Back Changes to a Marker

You can create a marker in the current transaction by using the SAVEPOINT statement, which divides the transaction into smaller sections. You can then discard pending changes up to that marker by using the ROLLBACK TO SAVEPOINT statement.

If you create a second savepoint with the same name as an earlier savepoint, the earlier savepoint is deleted.

Implicit Transaction Processing

 

System Failures

When a transaction is interrupted by a system failure, the entire transaction is automatically rolled back. This prevents the error from causing unwanted changes to the data and returns the tables to their state at the time of the last commit. In this way, the Oracle server protects the integrity of the tables.

From iSQL*Plus, a normal exit from the session is accomplished by clicking the Exit button. With SQL*Plus, a normal exit is accomplished by typing the command EXIT at the prompt. Closing the window is interpreted as an abnormal exit.

Committing Changes

Every data change made during the transaction is temporary until the transaction is committed.

The state of the data before COMMIT or ROLLBACK statements are issued can be described as follows:

•         Data manipulation operations primarily affect the database buffer; therefore, the previous state of the data can be recovered.

•         The current user can review the results of the data manipulation operations by querying the tables.

•         Other users cannot view the results of the data manipulation operations made by the current user. The Oracle server institutes read consistency to ensure that each user sees data as it existed at the last commit.

•         The affected rows are locked; other users cannot change the data in the affected rows.

Make all pending changes permanent by using the COMMIT statement. Here is what happens after a COMMIT statement:

•         Data changes are written to the database.

•         The previous state of the data is no longer available with normal SQL queries.

•         All users can view the results of the transaction.

•         The locks on the affected rows are released; the rows are now available for other users to perform new data changes.

•         All savepoints are erased.

Example

Remove departments 290 and 300 in the DEPARTMENTS table, and update a row in the EMPLOYEES table. Make the data change permanent.

    DELETE FROM departments

    WHERE  department_id IN (290, 300);

    1 row deleted.

   UPDATE  employees

     SET   department_id = 80

     WHERE employee_id = 206;

   1 row updated.

  COMMIT;

  Commit Complete.

Rolling Back Changes

Discard all pending changes by using the ROLLBACK statement, which results in the following:

•         Data changes are undone.

•         The previous state of the data is restored.

•         Locks on the affected rows are released.

DELETE FROM test;

25,000 rows deleted.

ROLLBACK;

Rollback complete.

DELETE FROM test WHERE  id = 100;

1 row deleted.

SELECT * FROM   test WHERE  id = 100;

No rows selected.

COMMIT;

Commit complete.

Statement-Level Rollback

Part of a transaction can be discarded by an implicit rollback if a statement execution error is detected. If a single DML statement fails during execution of a transaction, its effect is undone by a statement-level rollback, but the changes made by the previous DML statements in the transaction are not discarded. They can be committed or rolled back explicitly by the user.

The Oracle server issues an implicit commit before and after any DDL statement. So, even if your DDL statement does not execute successfully, you cannot roll back the previous statement because the server issued a commit.

Terminate your transactions explicitly by executing a COMMIT or ROLLBACK statement.

Read Consistency

Database users access the database in two ways:

•         Read operations (SELECT statement)

•         Write operations (INSERT, UPDATE, DELETE statements)

You need read consistency so that the following occur:

•         The database reader and writer are ensured a consistent view of the data.

•         Readers do not view data that is in the process of being changed.

•         Writers are ensured that the changes to the database are done in a consistent way.

•         Changes made by one writer do not disrupt or conflict with changes that another writer is making.

The purpose of read consistency is to ensure that each user sees data as it existed at the last commit, before a DML operation started.

Implementation of Read Consistency

Read consistency is an automatic implementation. It keeps a partial copy of the database in undo segments. The read-consistent image is constructed from committed data from the table and old data being changed and not yet committed from the undo segment.

When an insert, update, or delete operation is made to the database, the Oracle server takes a copy of the data before it is changed and writes it to an undo segment.

All readers, except the one who issued the change, still see the database as it existed before the changes started; they view the undo segment’s “snapshot” of the data.

Before changes are committed to the database, only the user who is modifying the data sees the database with the alterations. Everyone else sees the snapshot in the undo segment. This guarantees that readers of the data read consistent data that is not currently undergoing change.

When a DML statement is committed, the change made to the database becomes visible to anyone issuing a select statement after the commit is done. The space occupied by the old data in the undo segment file is freed for reuse.

If the transaction is rolled back, the changes are undone:

•         The original, older version of the data in the undo segment is written back to the table.

•         All users see the database as it existed before the transaction began.

Lesson 9 - Using DDL Statements to Create and Manage Tables

Object	Description
Table	Basic unit of storage; composed of rows
View	Logically represents subsets of data from one or more tables
Sequence	Generates numeric values
Index	Improves the performance of some queries
Synonym	Gives alternative names to objects

Naming Rules

Table names and column names:

•         Must begin with a letter

•         Must be 1–30 characters long

•         Must contain only A–Z, a–z, 0–9, _, $, and #

•         Must not duplicate the name of another object owned by the same user

•         Must not be an Oracle server–reserved word

CREATE TABLE Statement

You create tables to store data by executing the SQL CREATE TABLE statement. This statement is one of the DDL statements, which are a subset of SQL statements used to create, modify, or remove Oracle database structures. These statements have an immediate effect on the database, and they also record information in the data dictionary.

To create a table, a user must have the CREATE TABLE privilege and a storage area in which to create objects. The database administrator uses data control language statements to grant privileges to users (DCL statements are covered in a later lesson).

CREATE TABLE [schema.]table

          (column datatype [DEFAULT expr][, ...]);

In the syntax:

schema                                                Is the same as the owner’s name

table                                                    is the name of the table

DEFAULT expr                      Specifies a default value if a value is omitted in the INSERT                                                                                   statement

column                                                Is the name of the column

datatype                                   Is the column’s data type and length

Referencing Another User’s Tables

A schema is a collection of objects. Schema objects are the logical structures that directly refer to the data in a database. Schema objects include tables, views, synonyms, sequences, stored procedures, indexes, clusters, and database links.

If a table does not belong to the user, the owner’s name must be prefixed to the table. For example, if there are schemas named USERA and USERB, and both have an EMPLOYEES table, then if USERA wants to access the EMPLOYEES table that belongs to USERB, he  must prefix the table name with the schema name:

SELECT *

FROM   userb.employees;

If USERB wants to access the EMPLOYEES table that is owned by USERA, he must prefix the table name with the schema name:

SELECT *

FROM   usera.employees;

DEFAULT Option

When you define a table, you can specify that a column be given a default value by using the DEFAULT option. This option prevents null values from entering the columns if a row is inserted without a value for the column. The default value can be a literal, an expression, or a SQL function (such as SYSDATE or USER), but the value cannot be the name of another column or a pseudocolumn (such as NEXTVAL or CURRVAL). The default expression must match the data type of the column.

CREATE TABLE hire_dates
        (id          NUMBER(8),

         hire_date DATE DEFAULT SYSDATE);

Creating Tables

The example in the slide creates the DEPT table, with four columns: DEPTNO, DNAME, LOC, and CREATE_DATE. The CREATE_DATE column has a default value. If a value is not provided for an INSERT statement, the system date is automatically inserted.

It further confirms the creation of the table by issuing the DESCRIBE command.

Because creating a table is a DDL statement, an automatic commit takes place when this statement is executed.

CREATE TABLE dept
        (deptno      NUMBER(2),

         dname       VARCHAR2(14),

         loc         VARCHAR2(13),

         create_date DATE DEFAULT SYSDATE);

Data Types

When you identify a column for a table, you need to provide a data type for the column. There are several data types available:

 

Guidelines

•         A LONG column is not copied when a table is created using a subquery.

•         A LONG column cannot be included in a GROUP BY or an ORDER BY clause.

•         Only one LONG column can be used per table.

•         No constraints can be defined on a LONG column.

•         You might want to use a CLOB column rather than a LONG column.

Other Datetime Data Types

 

Note: These datetime data types are available with Oracle9i and later releases. For detailed information about the datetime data types, see the topics “TIMESTAMP Datatype,” “INTERVAL YEAR TO MONTH Datatype,” and “INTERVAL DAY TO SECOND Datatype” in the Oracle SQL Reference.

TIMESTAMP Data Type

TIMESTAMP[(fractional_seconds_precision)]

TIMESTAMP[(fractional_seconds_precision)]

WITH TIME ZONE

TIMESTAMP[(fractional_seconds_precision)]

WITH LOCAL TIME ZONE

The TIMESTAMP data type is an extension of the DATE data type. It stores the year, month, and day of the DATE data type plus hour, minute, and second values. This data type is used for storing precise time values.

The fractional_seconds_precision optionally specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6.

Example

In this example, a table is created named NEW_EMPLOYEES, with a column START_DATE that has a data type of TIMESTAMP:

CREATE TABLE new_employees

  (employee_id NUMBER,

   first_name VARCHAR2(15),

   last_name VARCHAR2(15),

   ...

   start_date TIMESTAMP(7),

   ...);

Suppose that two rows are inserted in the NEW_EMPLOYEES table. The displayed output shows the differences. (A DATE data type defaults to display the DD-MON-RR format.):

TIMESTAMP Data Type (continued)

  SELECT start_date

  FROM   new_employees;

 

  17-JUN-03 12.00.00.000000 AM

  21-SEP-03 12.00.00.000000 AM

TIMESTAMP WITH TIME ZONE Data Type

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time-zone displacement in its value. The time-zone displacement is the difference (in hours and minutes) between local time and UTC (Universal Time Coordinate, formerly known as Greenwich Mean Time). This data type is used for collecting and evaluating date information across geographic regions.

For example,

   TIMESTAMP '2003-04-15 8:00:00 -8:00'

is the same as

   TIMESTAMP '2003-04-15 11:00:00 -5:00'

That is, 8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern Standard Time.

This can also be specified as follows:

   TIMESTAMP '2003-04-15 8:00:00 US/Pacific'

TIMESTAMP WITH LOCAL TIME ZONE Data Type

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that includes a time-zone displacement in its value. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time-zone displacement is not stored as part of the column data. When users retrieve the data, it is returned in the users' local session time zone. The time-zone displacement is the difference (in hours and minutes) between local time and UTC.

Unlike TIMESTAMP WITH TIME ZONE, you can specify columns of type TIMESTAMP WITH LOCAL TIME ZONE as part of a primary or unique key, as in the following example:

CREATE TABLE time_example

    (order_date TIMESTAMP WITH LOCAL TIME ZONE);

   INSERT INTO time_example VALUES('15-JAN-04 09:34:28 AM');

   SELECT *

   FROM   time_example;

   ORDER_DATE

   ----------------------------

   15-JAN-04 09.34.28.000000 AM

The TIMESTAMP WITH LOCAL TIME ZONE type is appropriate for two-tier applications in which you want to display dates and times using the time zone of the client system.

INTERVAL YEAR TO MONTH Data Type

INTERVAL YEAR [(year_precision)] TO MONTH

INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields.

Use INTERVAL YEAR TO MONTH to represent the difference between two datetime values, where the only significant portions are the year and month. For example, you might use this value to set a reminder for a date that is 120 months in the future, or check whether 6 months have elapsed since a particular date.

In the syntax:

                                      year_precision                       is the number of digits in the YEAR datetime field.
                                                                                    The default value of year_precision is 2.

Examples

•         INTERVAL '123-2' YEAR(3) TO MONTH

Indicates an interval of 123 years, 2 months

•         INTERVAL '123' YEAR(3)

Indicates an interval of 123 years 0 months

•         INTERVAL '300' MONTH(3)

Indicates an interval of 300 months

•         INTERVAL '123' YEAR

Returns an error because the default precision is 2, and 123 has 3 digits

CREATE TABLE time_example2

(loan_duration INTERVAL YEAR (3) TO MONTH);

INSERT INTO time_example2 (loan_duration)

  VALUES (INTERVAL '120' MONTH(3));

SELECT TO_CHAR( sysdate+loan_duration, 'dd-mon-yyyy')

FROM   time_example2;        --today’s date is 26-Sep-2001

INTERVAL DAY TO SECOND Data Type

INTERVAL DAY [(day_precision)]

   TO SECOND [(fractional_seconds_precision)]

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds.

Use INTERVAL DAY TO SECOND to represent the precise difference between two datetime values. For example, you might use this value to set a reminder for a time that is 36 hours in the future, or to record the time between the start and end of a race. To represent long spans of time, including multiple years, with high precision, you can use a large value for the days portion.

In the syntax:

day_precision                          Is the number of digits in the DAY datetime                                     field. Accepted values are 0 to 9. The default              is 2.

fractional_seconds_precision              Is the number of digits in the fractional part of                      the SECOND datetime field. Accepted values                     are 0 to 9. The default is 6.

Examples

•         INTERVAL '4 5:12:10.222' DAY TO SECOND(3)

                                      Indicates 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.

•         INTERVAL '180' DAY(3)

Indicates 180 days.

•         INTERVAL '4 5:12:10.222' DAY TO SECOND(3)

Indicates 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second

•         INTERVAL '4 5:12' DAY TO MINUTE

Indicates 4 days, 5 hours, and 12 minutes

•         INTERVAL '400 5' DAY(3) TO HOUR

Indicates 400 days and 5 hours.

•         INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)

Indicates 11 hours, 12 minutes, and 10.2222222 seconds.

CREATE TABLE time_example3

  (day_duration INTERVAL DAY (3) TO SECOND);

  INSERT INTO time_example3 (day_duration)

  VALUES (INTERVAL '180' DAY(3));

  SELECT sysdate + day_duration "Half Year"

  FROM   time_example3;        --today’s date is 26-Sep-2001

Constraints

The Oracle server uses constraints to prevent invalid data entry into tables.

You can use constraints to do the following:

•         Enforce rules on the data in a table whenever a row is inserted, updated, or deleted from that table. The constraint must be satisfied for the operation to succeed.

•         Prevent the deletion of a table if there are dependencies from other tables

•         Provide rules for Oracle tools, such as Oracle Developer

Data Integrity Constraints

 

Constraint Guidelines

All constraints are stored in the data dictionary. Constraints are easy to reference if you give them a meaningful name. Constraint names must follow the standard object-naming rules. If you do not name your constraint, the Oracle server generates a name with the format SYS_Cn, where n is an integer so that the constraint name is unique.

Constraints can be defined at the time of table creation or after the table has been created.

•         Syntax:

CREATE TABLE [schema.]table

      (column datatype [DEFAULT expr]

      [column_constraint],

      ...

      [table_constraint][,...]);

•         Column-level constraint:

column [CONSTRAINT constraint_name] constraint_type,

CREATE TABLE employees(

  employee_id  NUMBER(6)

    CONSTRAINT emp_emp_id_pk PRIMARY KEY,

  first_name   VARCHAR2(20),

  ...);

•         Table-level constraint:

column,...

  [CONSTRAINT constraint_name] constraint_type

  (column, ...),

CREATE TABLE employees(

  employee_id  NUMBER(6),

  first_name   VARCHAR2(20),

  ...

  job_id       VARCHAR2(10) NOT NULL,

  CONSTRAINT emp_emp_id_pk

    PRIMARY KEY (EMPLOYEE_ID));

The slide gives the syntax for defining constraints when creating a table. You can create the constraints at either the column level or table level. Constraints defined at the column level are included when the column is defined. Table-level constraints are defined at the end of the table definition and must refer to the column or columns on which the constraint pertains in a set of parentheses.

NOT NULL constraints must be defined at the column level.

Constraints that apply to more than one column must be defined at the table level.

In the syntax:

schema                           Is the same as the owner’s name

table                               Is the name of the table

DEFAULT expr            Specifies a default value to use if a value is omitted in the                         INSERT statement

column                           Is the name of the column

datatype                        Is the column’s data type and length

column_constraint         Is an integrity constraint as part of the column definition

table_constraint             Is an integrity constraint as part of the table definition

Defining Constraints (continued)

Constraints are usually created at the same time as the table. Constraints can be added to a table after its creation and also temporarily disabled.

Both slide examples create a primary key constraint on the EMPLOYEE_ID column of the EMPLOYEES table.

1.                                   The first example uses the column-level syntax to define the constraint.

2.                                   The second example uses the table-level syntax to define the constraint.

More details about the primary key constraint are provided later in this lesson.

NOT NULL Constraint

The NOT NULL constraint ensures that the column contains no null values. Columns without the NOT NULL constraint can contain null values by default. NOT NULL constraints must be defined at the column level.

UNIQUE Constraint

CREATE TABLE employees(

    employee_id      NUMBER(6),

    last_name        VARCHAR2(25) NOT NULL,

    email            VARCHAR2(25),

    salary           NUMBER(8,2),

    commission_pct   NUMBER(2,2),

    hire_date        DATE NOT NULL,

... 

    CONSTRAINT emp_email_uk UNIQUE(email));

A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique—that is, no two rows of a table can have duplicate values in a specified column or set of columns. The column (or set of columns) included in the definition of the UNIQUE key constraint is called the unique key. If the UNIQUE constraint comprises more than one column, that group of columns is called a composite unique key.

UNIQUE constraints enable the input of nulls unless you also define NOT NULL constraints for the same columns. In fact, any number of rows can include nulls for columns without NOT NULL constraints because nulls are not considered equal to anything. A null in a column (or in all columns of a composite UNIQUE key) always satisfies a UNIQUE constraint.

Note: Because of the search mechanism for UNIQUE constraints on more than one column, you cannot have identical values in the non-null columns of a partially null composite UNIQUE key constraint.

UNIQUE constraints can be defined at the column level or table level. A composite unique key is created by using the table-level definition.

The example in the slide applies the UNIQUE constraint to the EMAIL column of the EMPLOYEES table. The name of the constraint is EMP_EMAIL_UK.

Note: The Oracle server enforces the UNIQUE constraint by implicitly creating a unique index on the unique key column or columns.

PRIMARY KEY Constraint

•         Column-level constraint:

column [CONSTRAINT constraint_name] constraint_type,

CREATE TABLE employees(

  employee_id  NUMBER(6)

    CONSTRAINT emp_emp_id_pk PRIMARY KEY,

  first_name   VARCHAR2(20),

  ...);

•         Table-level constraint:

column,...

  [CONSTRAINT constraint_name] constraint_type

  (column, ...),

CREATE TABLE employees(

  employee_id  NUMBER(6),

  first_name   VARCHAR2(20),

  ...

  job_id       VARCHAR2(10) NOT NULL,

  CONSTRAINT emp_emp_id_pk

    PRIMARY KEY (EMPLOYEE_ID));

A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be created for each table. The PRIMARY KEY constraint is a column or set of columns that uniquely identifies each row in a table. This constraint enforces uniqueness of the column or column combination and ensures that no column that is part of the primary key can contain a null value.

Note: Because uniqueness is part of the primary key constraint definition, the Oracle server enforces the uniqueness by implicitly creating a unique index on the primary key column or columns

FOREIGN KEY Constraint

CREATE TABLE employees(

    employee_id      NUMBER(6),

    last_name        VARCHAR2(25) NOT NULL,

    email            VARCHAR2(25),

    salary           NUMBER(8,2),

    commission_pct   NUMBER(2,2),

    hire_date        DATE NOT NULL,

...

    department_id    NUMBER(4),

    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)

      REFERENCES departments(department_id),

    CONSTRAINT emp_email_uk UNIQUE(email));

The FOREIGN KEY (or referential integrity) constraint designates a column or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table.

In the example in the slide, DEPARTMENT_ID has been defined as the foreign key in the EMPLOYEES table (dependent or child table); it references the DEPARTMENT_ID column of the DEPARTMENTS table (the referenced or parent table).

Guidelines

•         A foreign key value must match an existing value in the parent table or be NULL.

•         Foreign keys are based on data values and are purely logical, rather than physical, pointers.

FOREIGN KEY constraints can be defined at the column or table constraint level. A composite foreign key must be created by using the table-level definition.

The example in the slide defines a FOREIGN KEY constraint on the DEPARTMENT_ID column of the EMPLOYEES table, using table-level syntax. The name of the constraint is EMP_DEPTID_FK.

The foreign key can also be defined at the column level, provided the constraint is based on a single column. The syntax differs in that the keywords FOREIGN KEY do not appear. For example:

CREATE TABLE employees

(...

department_id NUMBER(4) CONSTRAINT emp_deptid_fk

REFERENCES departments(department_id),

...

)

The foreign key is defined in the child table, and the table containing the referenced column is the parent table. The foreign key is defined using a combination of the following keywords:

•         FOREIGN KEY is used to define the column in the child table at the table-constraint level.

•         REFERENCES identifies the table and column in the parent table.

•         ON DELETE CASCADE indicates that when the row in the parent table is deleted, the dependent rows in the child table are also deleted.

•         ON DELETE SET NULL converts foreign key values to null when the parent value is removed.

The default behavior is called the restrict rule, which disallows the update or deletion of referenced data.

Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.

CHECK Constraint

The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as query conditions, with the following exceptions:

•         References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns

•         Calls to SYSDATE, UID, USER, and USERENV functions

•         Queries that refer to other values in other rows

A single column can have multiple CHECK constraints that refer to the column in its definition. There is no limit to the number of CHECK constraints that you can define on a column.

CHECK constraints can be defined at the column level or table level.

  CREATE TABLE employees

     (...

      salary NUMBER(8,2) CONSTRAINT emp_salary_min

                         CHECK (salary > 0),

     ...

The CREATE TABLE Example

CREATE TABLE employees

    ( employee_id    NUMBER(6)

        CONSTRAINT     emp_employee_id   PRIMARY KEY

    , first_name     VARCHAR2(20)

    , last_name      VARCHAR2(25)

        CONSTRAINT     emp_last_name_nn  NOT NULL

    , email          VARCHAR2(25)

        CONSTRAINT     emp_email_nn      NOT NULL

        CONSTRAINT     emp_email_uk      UNIQUE

    , phone_number   VARCHAR2(20)

    , hire_date      DATE

        CONSTRAINT     emp_hire_date_nn  NOT NULL

    , job_id         VARCHAR2(10)

        CONSTRAINT     emp_job_nn        NOT NULL

    , salary         NUMBER(8,2)

        CONSTRAINT     emp_salary_ck     CHECK (salary>0)

    , commission_pct NUMBER(2,2)

    , manager_id     NUMBER(6)

    , department_id  NUMBER(4)

        CONSTRAINT     emp_dept_fk       REFERENCES

           departments (department_id));

Creating a Table from Rows in Another Table

A second method for creating a table is to apply the AS subquery clause, which both creates the table and inserts rows returned from the subquery.

In the syntax:

table                               is the name of the table

column                           is the name of the column, default value, and integrity constraint

subquery                        is the SELECT statement that defines the set of rows to be inserted into                                                                the new table

Guidelines

•         The table is created with the specified column names, and the rows retrieved by the SELECT statement are inserted into the table.

•         The column definition can contain only the column name and default value.

•         If column specifications are given, the number of columns must equal the number of columns in the subquery SELECT list.

•         If no column specifications are given, the column names of the table are the same as the column names in the subquery.

•         The column data type definitions and the NOT NULL constraint are passed to the new table. The other constraint rules are not passed to the new table. However, you can add constraints in the column definition.

CREATE TABLE table

                                        [(column, column...)]

AS subquery;

CREATE TABLE         dept80

  AS
    SELECT  employee_id, last_name,  

            salary*12 ANNSAL,

            hire_date
    FROM    employees
    WHERE   department_id = 80;

ALTER TABLE Statement

After you create a table, you may need to change the table structure for any of the following reasons:

•         You omitted a column.

•         Your column definition needs to be changed.

•         You need to remove columns.

You can do this by using the ALTER TABLE statement.

Dropping a Table

The DROP TABLE statement removes the definition of an Oracle table. When you drop a table, the database loses all the data in the table and all the indexes associated with it.

Syntax

DROP TABLE table

In the syntax, table is the name of the table.

Guidelines

•         All data is deleted from the table.

•         Any views and synonyms remain but are invalid.

•         Any pending transactions are committed.

•         Only the creator of the table or a user with the DROP ANY TABLE privilege can remove a table.

Note: The DROP TABLE statement, once executed, is irreversible. The Oracle server does not question the action when you issue the DROP TABLE statement. If you own that table or have a high-level privilege, then the table is immediately removed. As with all DDL statements, DROP TABLE is committed automatically.

DROP TABLE dept80;

Lesson 10 - Creating Other Schema Objects

What Is a View?

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.

Advantages of Views

•         Views restrict access to the data because the view can display selected 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 ad hoc 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.

Simple Views and 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 operations 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 operations through the view

Creating a View

You can create a view by embedding a subquery in the CREATE VIEW statement.

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

  [(alias[, alias]...)]

 AS subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

In the syntax:

OR REPLACE                       Re-creates the view if it already exists

FORCE                                   Creates the view regardless of whether or not the base tables exist

NOFORCE                             Creates the view only if the base tables exist (This is the default.)

view                                         Is the name of the view

alias                                         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                                  Is a complete SELECT statement (You can use aliases for the                                                            columns in the SELECT list.)

WITH CHECK OPTION                   Specifies that only those rows that are accessible to the view can                                                       be inserted or updated

constraint                                Is the name assigned to the CHECK OPTION constraint

WITH READ ONLY             ensures that no DML operations can be performed on this view

The example in the slide creates a view that contains the employee number, last name, and salary for each employee in department 80.

CREATE VIEW           empvu80

 AS SELECT  employee_id, last_name, salary

    FROM    employees

    WHERE   department_id = 80;

You can display the structure of the view by using the iSQL*Plus DESCRIBE command.

DESCRIBE empvu80

Guidelines for Creating a View

•         The subquery that defines a view can contain complex SELECT syntax, including joins, groups, and subqueries.

•         If you do not specify a constraint name for a view created with the WITH CHECK OPTION, the system assigns a default name in the format SYS_Cn.

•         You can use the OR REPLACE option to change the definition of the view without dropping and re-creating it or regranting object privileges previously granted on it.

You can control the column names by including column aliases in the subquery.

The example in the slide creates a view containing the employee number (EMPLOYEE_ID) with the alias ID_NUMBER, name (LAST_NAME) with the alias NAME, and annual salary (SALARY) with the alias ANN_SALARY for every employee in department 50.

As an alternative, you can use an alias after the CREATE statement and before the SELECT subquery. The number of aliases listed must match the number of expressions selected in the subquery.

CREATE OR REPLACE VIEW   salvu50 (ID_NUMBER, NAME, ANN_SALARY)

  AS SELECT  employee_id, last_name, salary*12

     FROM    employees

     WHERE   department_id = 50;

View created.

CREATE VIEW           salvu50

 AS SELECT  employee_id ID_NUMBER, last_name NAME,

            salary*12 ANN_SALARY

    FROM    employees

    WHERE   department_id = 50;

Retrieving Data from a View

You can retrieve data from a view as you would from any table. You can display either the contents of the entire view or just specific rows and columns.

Modifying a View

With the OR REPLACE option, a view can be created even if one exists with this name already, thus replacing the old version of the view for its owner. This means that the view can be altered without dropping, re-creating, and regranting object privileges.

Note: When assigning column aliases in the CREATE OR REPLACE VIEW clause, remember that the aliases are listed in the same order as the columns in the subquery.

CREATE OR REPLACE VIEW empvu80

  (id_number, name, sal, department_id)

AS SELECT  employee_id, first_name || ' '

           || last_name, salary, department_id

   FROM    employees

   WHERE   department_id = 80;

Creating a Complex View

The example in the slide creates a complex view of department names, minimum salaries, maximum salaries, and average salaries by department. Note that alternative names have been specified for the view. This is a requirement if any column of the view is derived from a function or an expression.

You can view the structure of the view by using the iSQL*Plus DESCRIBE command. Display the contents of the view by issuing a SELECT statement.

CREATE OR REPLACE VIEW dept_sum_vu

  (name, minsal, maxsal, avgsal)

AS SELECT   d.department_name, MIN(e.salary),

            MAX(e.salary),AVG(e.salary)

   FROM     employees e JOIN departments d

   ON       (e.department_id = d.department_id)

   GROUP BY d.department_name;

Performing DML Operations on a View

You can perform DML operations on data through a view if those operations follow certain rules.

You can remove a row from a view unless it contains any of the following:

•         Group functions

•         A GROUP BY clause

•         The DISTINCT keyword

•         The pseudocolumn ROWNUM keyword

You can modify data through a view unless it contains any of the conditions mentioned in the previous slide or columns defined by expressions (for example, SALARY * 12).

•         Columns defined by expressions

You can add data through a view unless it contains any of the items listed in the slide. You cannot add data to a view if the view contains NOT NULL columns without default values in the base table. All required values must be present in the view. Remember that you are adding values directly to the underlying table through the view.

•         NOT NULL columns in the base tables that are not selected by the view

Using the WITH CHECK OPTION Clause

CREATE OR REPLACE VIEW empvu20

AS SELECT                 *

   FROM     employees

   WHERE    department_id = 20

   WITH CHECK OPTION CONSTRAINT empvu20_ck ;

It is possible to perform referential integrity checks through views. You can also enforce constraints at the database level. The view can be used to protect data integrity, but the use is very limited.

The WITH CHECK OPTION clause specifies that INSERTs and UPDATEs performed through the view cannot create rows that the view cannot select, and therefore it enables integrity constraints and data validation checks to be enforced on data being inserted or updated. If there is an attempt to perform DML operations on rows that the view has not selected, an error is displayed, along with the constraint name if that has been specified.

UPDATE empvu20

SET    department_id = 10

WHERE  employee_id = 201;

causes:

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

Note: No rows are updated because if the department number were to change to 10, the view would no longer be able to see that employee. With the WITH CHECK OPTION clause, therefore, the view can see only employees in department 20 and does not allow the department number for those employees to be changed through the view.

Denying DML Operations

You can ensure that no DML operations occur on your view by creating it with the WITH READ ONLY option. The example in the next slide modifies the EMPVU10 view to prevent any DML operations on the view.

CREATE OR REPLACE VIEW empvu10

    (employee_number, employee_name, job_title)

AS SELECT                 employee_id, last_name, job_id

   FROM     employees

   WHERE    department_id = 10

   WITH READ ONLY ;

Removing a View

You use the DROP VIEW statement to remove a view. The statement removes the view definition from the database. Dropping views 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.

In the syntax:

view                               is the name of the view

DROP VIEW view;

Sequences

A sequence is a database object that creates integer values. You can create sequences and then use them to generate numbers.

A sequence is a user-created database object that can be shared by multiple users to generate integers.

You can define a sequence to generate unique values or to recycle and use the same numbers again.

A typical usage for sequences is to create a primary key value, which must be unique for each row. The sequence is generated and incremented (or decremented) by an internal Oracle routine. This can be a time-saving object because it can reduce the amount of application code needed to write a sequence-generating routine.

Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.

CREATE SEQUENCE sequence

       [INCREMENT BY n]

       [START WITH n]

       [{MAXVALUE n | NOMAXVALUE}]

       [{MINVALUE n | NOMINVALUE}]

       [{CYCLE | NOCYCLE}]

       [{CACHE n | NOCACHE}];

In the syntax:

sequence                                  Is the name of the sequence generator

INCREMENT BY n               Specifies the interval between sequence numbers, where                              n is an integer (If this clause is omitted, the sequence                                   increments by 1.)

START WITH n                     Specifies the first sequence number to be generated (If  this                         clause is omitted, the sequence starts with 1.)

MAXVALUE n                      Specifies the maximum value the sequence can generate

NOMAXVALUE                   Specifies a maximum value of 10^27 for an ascending                                  sequence and –1 for a descending sequence (This is the                                   default option.)

MINVALUE n                       Specifies the minimum sequence value

NOMINVALUE                    Specifies a minimum value of 1 for an ascending sequence                           and –(10^26) for a descending sequence (This is the default                     option.)

CYCLE | NOCYCLE            Specifies whether the sequence continues to generate                                                                                               values after reaching its maximum or minimum value                                                                                                (NOCYCLE is the default option.)

CACHE n | NOCACHE                    Specifies how many values the Oracle server preallocates                                                                                         and keeps in memory (By default, the Oracle server                                                                                      caches 20 values.)

The example in the slide creates a sequence named DEPT_DEPTID_SEQ to be used for the DEPARTMENT_ID column of the DEPARTMENTS table. The sequence starts at 120, does not allow caching, and does not cycle.

Do not use the CYCLE option if the sequence is used to generate primary key values, unless you have a reliable mechanism that purges old rows faster than the sequence cycles.

For more information, see “CREATE SEQUENCE” in the Oracle SQL Reference.

Note: The sequence is not tied to a table. Generally, you should name the sequence after its intended use. However, the sequence can be used anywhere, regardless of its name.

CREATE SEQUENCE dept_deptid_seq

                INCREMENT BY 10

                START WITH 120

                MAXVALUE 9999

                NOCACHE

                NOCYCLE;

NEXTVAL and CURRVAL Pseudocolumns

After you create your sequence, it generates sequential numbers for use in your tables. Reference the sequence values by using the NEXTVAL and CURRVAL pseudocolumns.

The NEXTVAL pseudocolumn is used to extract successive sequence numbers from a specified sequence. You must qualify NEXTVAL with the sequence name. When you reference sequence.NEXTVAL, a new sequence number is generated and the current sequence number is placed in CURRVAL.

The CURRVAL pseudocolumn is used to refer to a sequence number that the current user has just generated. NEXTVAL must be used to generate a sequence number in the current user’s session before CURRVAL can be referenced. You must qualify CURRVAL with the sequence name. When you reference sequence.CURRVAL, the last value returned to that user’s process is displayed.

Rules for Using NEXTVAL and CURRVAL

You can use NEXTVAL and CURRVAL in the following contexts:

•         The SELECT list of a SELECT statement that is not part of a subquery

•         The SELECT list of a subquery in an INSERT statement

•         The VALUES clause of an INSERT statement

•         The SET clause of an UPDATE statement

You cannot use NEXTVAL and CURRVAL in the following contexts:

•         The SELECT list of a view

•         A SELECT statement with the DISTINCT keyword

•         A SELECT statement with GROUP BY, HAVING, or ORDER BY clauses

•         A subquery in a SELECT, DELETE, or UPDATE statement

•         The DEFAULT expression in a CREATE TABLE or ALTER TABLE statement

Using a Sequence

The example in the slide inserts a new department in the DEPARTMENTS table. It uses the DEPT_DEPTID_SEQ sequence to generate a new department number as follows.

You can view the current value of the sequence:

INSERT INTO departments(department_id,

            department_name, location_id)

VALUES      (dept_deptid_seq.NEXTVAL,

            'Support', 2500);

SELECT                        dept_deptid_seq.CURRVAL

FROM                           dual;

Caching Sequence Values

You can cache sequences in memory to provide faster access to those sequence values. The cache is populated the first time you refer to the sequence. Each request for the next sequence value is retrieved from the cached sequence. After the last sequence value is used, the next request for the sequence pulls another cache of sequences into memory.

Gaps in the Sequence

Although sequence generators issue sequential numbers without gaps, this action occurs independent of a commit or rollback. Therefore, if you roll back a statement containing a sequence, the number is lost.

Another event that can cause gaps in the sequence is a system crash. If the sequence caches values in memory, then those values are lost if the system crashes.

Because sequences are not tied directly to tables, the same sequence can be used for multiple tables. If you do so, each table can contain gaps in the sequential numbers.

Modifying a Sequence

If you reach the MAXVALUE limit for your sequence, no additional values from the sequence are allocated and you will receive an error indicating that the sequence exceeds the MAXVALUE. To continue to use the sequence, you can modify it by using the ALTER SEQUENCE statement.

Syntax

  ALTER  SEQUENCE          sequence

       [INCREMENT BY n]

       [{MAXVALUE n | NOMAXVALUE}]

       [{MINVALUE n | NOMINVALUE}]

       [{CYCLE | NOCYCLE}]

       [{CACHE n | NOCACHE}];

In the syntax, sequence is the name of the sequence generator.

Guidelines for Modifying a Sequence

•         You must be the owner or have the ALTER privilege for the sequence to modify it. You must be the owner or have the DROP ANY SEQUENCE privilege to remove it.

•         Only future sequence numbers are affected by the ALTER SEQUENCE statement.

•         The START WITH option cannot be changed using ALTER SEQUENCE. The sequence must be dropped and re-created to restart the sequence at a different number.

•         Some validation is performed. For example, a new MAXVALUE that is less than the current sequence number cannot be imposed.

Indexes

Indexes are database objects that you can create to improve the performance of some queries. Indexes can also be created automatically by the server when you create a primary key or unique constraint.

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. After an index is created, no direct activity is required by the user.

Indexes are logically and physically independent of the table that 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.

Types of Indexes

Two types of indexes can be created.

Unique index: The Oracle server automatically creates this index when you define a column in a table to have a PRIMARY KEY or a UNIQUE key constraint. The name of the index is the name that is given to the constraint.

Nonunique index: This is an index that 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.

Creating an Index

CREATE INDEX index

ON table (column[, column]...);

CREATE INDEX         emp_last_name_idx

ON                                          employees(last_name);

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

More Is Not Always Better

Having more indexes on a table does not produce faster queries. Each DML operation that is committed on a table with indexes means that the indexes must be updated. The more indexes that you have associated with a table, the more effort the Oracle server must make to update all the indexes after a DML operation.

When to Create an Index

Therefore, you should create indexes only if:

•         The column contains a wide range of values

•         The column contains a large number of null values

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

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

Remember that if you want to enforce uniqueness, you should define a unique constraint in the table definition. A unique index is then created automatically.

Removing an Index

DROP INDEX index;

You cannot modify indexes. To change an index, you must drop it and then re-create it.

Remove an index definition from the data dictionary by issuing the DROP INDEX statement. To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.

In the syntax, index is the name of the index.

Note: If you drop a table, indexes and constraints are automatically dropped but views and sequences remain.

Synonyms

Synonyms are database objects that enable you to call a table by another name. You can create synonyms to give an alternative name to a table.

Creating a Synonym for an Object

To refer to a table that is owned by another user, you need to prefix the table name with the name of the user who created it, followed by a period. Creating a synonym eliminates the need to qualify the object name with the schema and provides you with an alternative name for a table, view, sequence, procedure, or other objects. This method can be especially useful with lengthy object names, such as views.

CREATE [PUBLIC] SYNONYM synonym

FOR    object;

CREATE SYNONYM  d_sum

FOR  dept_sum_vu;

In the syntax:

PUBLIC                                 Creates a synonym that is accessible to all users

synonym                                  Is the name of the synonym to be created

object                                       Identifies the object for which the synonym is created

Guidelines

•         The object cannot be contained in a package.

•         A private synonym name must be distinct from all other objects that are owned by the same user.

The slide example creates a synonym for the DEPT_SUM_VU view for quicker reference.

The database administrator can create a public synonym that is accessible to all users. The following example creates a public synonym named DEPT for Alice’s DEPARTMENTS table:

CREATE PUBLIC SYNONYM  dept

FOR    alice.departments;

Synonym created.

Removing a Synonym

To remove a synonym, use the DROP SYNONYM statement. Only the database administrator can drop a public synonym.

DROP PUBLIC SYNONYM  dept;

Synonym dropped.

Lesson 11 - Managing Objects with Data Dictionary Views

The Data Dictionary

User tables are tables created by the user and contain business data, such as EMPLOYEES. There is another collection of tables and views in the Oracle database known as the data dictionary. This collection is created and maintained by the Oracle server and contains information about the database. The data dictionary is structured in tables and views, just like other database data. Not only is the data dictionary central to every Oracle database, but it is an important tool for all users, from end users to application designers and database administrators.

You use SQL statements to access the data dictionary. Because the data dictionary is read-only, you can issue only queries against its tables and views.

You can query the dictionary views that are based on the dictionary tables to find information such as:

•         Definitions of all schema objects in the database (tables, views, indexes, synonyms, sequences, procedures, functions, packages, triggers, and so on)

•         Default values for columns

•         Integrity constraint information

•         Names of Oracle users

•         Privileges and roles that each user has been granted

•         Other general database information

Data Dictionary Structure

Underlying base tables store information about the associated database. Only the Oracle server should write to and read these tables. You rarely access them directly.

There are several views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information (such as user or table names) using joins and WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables.

The Oracle user SYS owns all base tables and user-accessible views of the data dictionary. No Oracle user should ever alter (UPDATE, DELETE, or INSERT) any rows or schema objects contained in the SYS schema, because such activity can compromise data integrity.

The data dictionary consists of sets of views. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes. For example, there is a view named USER_OBJECTS, another named ALL_OBJECTS, and a third named DBA_OBJECTS.

These three views contain similar information about objects in the database, except that the scope is different. USER_OBJECTS contains information about objects that you own or created. ALL_OBJECTS contains information about all objects to which you have access. DBA_OBJECTS contains information on all objects that are owned by all users. For views that are prefixed with ALL or DBA, there is usually an additional column in the view named OWNER to identify who owns the object.

There is also a set of views that is prefixed with v$. These views are dynamic in nature and hold information about performance. Dynamic performance tables are not true tables, and they should not be accessed by most users. However, database administrators can query and create views on the tables and grant access to those views to other users. This course does not go into details about these views.

View naming convention:

View Prefix	Purpose
USER	User’s view (what is in your schema; what you own)
ALL	Expanded user’s view (what you can access)
DBA	Database administrator’s view (what is in everyone’s schemas)
V$	Performance-related data

Start with DICTIONARY. It contains the names and descriptions of the dictionary tables and views.

DESCRIBE DICTIONARY

SELECT *

FROM   dictionary

WHERE  table_name = 'USER_OBJECTS';

USER_OBJECTS:

•         Query USER_OBJECTS to see all of the objects that are owned by you

•         Is a useful way to obtain a listing of all object names and types in your schema, plus the following information:

–        Date created

–        Date of last modification

–        Status (valid or invalid)

ALL_OBJECTS:

•         Query ALL_OBJECTS to see all objects to which you have access

SELECT object_name, object_type, created, status

FROM   user_objects

ORDER BY object_type;

USER_TABLES View

You can use the USER_TABLES view to obtain the names of all of your tables. The USER_TABLES view contains information about your tables. In addition to providing the table name, it contains detailed information on the storage.

The TABS view is a synonym of the USER_TABLES view. You can query it to see a listing of tables that you own:

SELECT table_name

FROM  tabs;

Note: For a complete listing of the columns in the USER_TABLES view, see “USER_TABLES” in the Oracle Database Reference.

You can also query the ALL_TABLES view to see a listing of all tables to which you have access.

Column Information

You can query the USER_TAB_COLUMNS view to find detailed information about the columns in your tables. While the USER_TABLES view provides information on your table names and storage, detailed column information is found in the USER_TAB_COLUMNS view.

This view contains information such as:

•         Column names

•         Column data types

•         Length of data types

•         Precision and scale for NUMBER columns

•         Whether nulls are allowed (Is there a NOT NULL constraint on the column?)

•         Default value

By querying the USER_TAB_COLUMNS table, you can find details about your columns such as the names, data types, data type lengths, null constraints, and default value for a column.

The example shown displays the columns, data types, data lengths, and null constraints for the EMPLOYEES table. Note that this information is similar to the output from the iSQL*Plus DESCRIBE command.

SELECT column_name, data_type, data_length,

       data_precision, data_scale, nullable

FROM   user_tab_columns

WHERE  table_name = 'EMPLOYEES';

Constraint Information

You can find out the names of your constraints, the type of constraint, the table name to which the constraint applies, the condition for check constraints, foreign key constraint information, deletion rule for foreign key constraints, the status, and many other types of information about your constraints.

• USER_CONSTRAINTS describes the constraint definitions on your tables.

•      USER_CONS_COLUMNS describes columns that are owned by you and that are specified in constraints.

USER_CONSTRAINTS: Example

In the example shown, the USER_CONSTRAINTS view is queried to find the names, types, check conditions, name of the unique constraint that the foreign key references, deletion rule for a foreign key, and status for constraints on the EMPLOYEES table.

The CONSTRAINT_TYPE can be:

•         C (check constraint on a table)

•         P (primary key)

•         U (unique key)

•         R (referential integrity)

•         V (with check option, on a view)

•         O (with read-only, on a view)

The DELETE_RULE can be:

•         CASCADE: If the parent record is deleted, the child records are deleted too.

•         NO ACTION: A parent record can be deleted only if no child records exist.

The STATUS can be:

•         ENABLED: Constraint is active.

•         DISABLED: Constraint is made not active.

SELECT constraint_name, constraint_type,

       search_condition, r_constraint_name,

       delete_rule, status

FROM   user_constraints

WHERE  table_name = 'EMPLOYEES';

Querying USER_CONS_COLUMNS

To find the names of the columns to which a constraint applies, query the USER_CONS_COLUMNS dictionary view. This view tells you the name of the owner of a constraint, the name of the constraint, the table that the constraint is on, the names of the columns with the constraint, and the original position of column or attribute in the definition of the object.

Note: A constraint may apply to more than one column.

You can also write a join between the USER_CONSTRAINTS and USER_CONS_COLUMNS to create customized output from both tables.

SELECT constraint_name, column_name

FROM   user_cons_columns

WHERE  table_name = 'EMPLOYEES';

Views in the Data Dictionary

After your view is created, you can query the data dictionary view called USER_VIEWS to see the name of the view and the view definition. The text of the SELECT statement that constitutes your view is stored in a LONG column. The LENGTH column is the number of characters in the SELECT statement. By default, when you select from a LONG column, only the first 80 characters of the column’s value are displayed. To see more than 80 characters, use the iSQL*Plus command SET LONG:

SET LONG 1000

SELECT DISTINCT view_name FROM user_views;

SELECT text FROM user_views

WHERE view_name = 'EMP_DETAILS_VIEW';

In the examples in the slide:

1.                                   The USER_VIEWS columns are displayed. Note that this is a partial listing.

2.                                   The names of your views are retrieved.

3.                                   The SELECT statement for the EMP_DETAILS_VIEW is displayed from the dictionary.

Data Access Using Views

When you access data using a view, the Oracle server performs the following operations:

•         It retrieves the view definition from the data dictionary table USER_VIEWS.

•         It checks access privileges for the view base table.

•         It converts the view query into an equivalent operation on the underlying base table or tables. In other words, data is retrieved from, or an update is made to, the base tables.

USER_SEQUENCES View

The USER_SEQUENCES view describes all sequences that are owned by you. When you create the sequence, you specify criteria that are stored in the USER_SEQUENCES view. The columns in this view are:

•         SEQUENCE_NAME: Name of the sequence

•         MIN_VALUE: Minimum value of the sequence

•         MAX_VALUE: Maximum value of the sequence

•         INCREMENT_BY: Value by which sequence is incremented

•         CYCLE_FLAG: Does sequence wrap around on reaching limit?

•         ORDER_FLAG: Are sequence numbers generated in order?

•         CACHE_SIZE: Number of sequence numbers to cache

•         LAST_NUMBER: Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is likely to be greater than the last sequence number that was used.

SELECT                        sequence_name, min_value, max_value,

                                      increment_by, last_number

FROM                           user_sequences;

Confirming Sequences

After creating your sequence, it is documented in the data dictionary. Because a sequence is a database object, you can identify it in the USER_OBJECTS data dictionary table.

You can also confirm the settings of the sequence by selecting from the USER_SEQUENCES data dictionary view.

Viewing the Next Available Sequence Value Without Incrementing It

If the sequence was created with NOCACHE, it is possible to view the next available sequence value without incrementing it by querying the USER_SEQUENCES table.

USER_SYNONYMS View

The USER_SYNONYMS dictionary view describes private synonyms (synonyms that are owned by you).

You can query this view to find your synonyms. You can query ALL_SYNONYMS to find out the name of all of the synonyms that are available to you and the objects on which these synonyms apply.

The columns in this view are:

•         SYNONYM_NAME: Name of the synonym

•         TABLE_OWNER: Owner of the object that is referenced by the synonym

•         TABLE_NAME: Name of the table or view that is referenced by the synonym

•         DB_LINK: Name of the database link reference (if any)

SELECT *

FROM   user_synonyms;

Adding Comments to a Table

You can add a comment of up to 4,000 bytes about a column, table, view, or snapshot by using the COMMENT statement. The comment is stored in the data dictionary and can be viewed in one of the following data dictionary views in the COMMENTS column:

•         ALL_COL_COMMENTS

•         USER_COL_COMMENTS

•         ALL_TAB_COMMENTS

•         USER_TAB_COMMENTS

Syntax

COMMENT ON TABLE table | COLUMN table.column

    IS 'text';

In the syntax:

table                               Is the name of the table

column                           Is the name of the column in a table

text                                           Is the text of the comment

COMMENT ON TABLE employees

IS 'Employee Information';

You can drop a comment from the database by setting it to empty string (''):

  COMMENT ON TABLE  employees IS ' ';

Lesson 12 - Controlling User Access

In a multiple-user environment, you want to maintain security of the database access and use. With Oracle server database security, you can do the following:

•         Control database access.

•         Give access to specific objects in the database.

•         Confirm given and received privileges with the Oracle data dictionary.

•         Create synonyms for database objects.

Database security can be classified into two categories: system security and data security. System security covers access and use of the database at the system level such as the username and password, the disk space allocated to users, and the system operations that users can perform. Database security covers access and use of the database objects and the actions that those users can have on the objects.

Privileges

Privileges are the right to execute particular SQL statements. The database administrator (DBA) is a high-level user with the ability to create users and grant users access to the database and its objects. Users require system privileges to gain access to the database and object privileges to manipulate the content of the objects in the database. Users can also be given the privilege to grant additional privileges to other users or to roles, which are named groups of related privileges.

Schemas

A schema is a collection of objects such as tables, views, and sequences. The schema is owned by a database user and has the same name as that user.

System Privileges

More than 100 distinct system privileges are available for users and roles. System privileges typically are provided by the database administrator.

Typical DBA Privileges

 

Creating a User

The DBA creates the user by executing the CREATE USER statement. The user does not have any privileges at this point. The DBA can then grant privileges to that user. These privileges determine what the user can do at the database level.

CREATE USER user                                                    

IDENTIFIED BY   password;

CREATE USER  USER1

IDENTIFIED BY   USER1;

The slide gives the abridged syntax for creating a user.

In the syntax:

user                                         Is the name of the user to be created

Password                       Specifies that the user must log in with this password

Typical User Privileges

After the DBA creates a user, the DBA can assign privileges to that user.

 

GRANT privilege [, privilege...]                                

TO user [, user| role, PUBLIC...];

In the syntax:

privilege                                              Is the system privilege to be granted

user                                |role|PUBLIC            Is the name of the user, the name of the role, or PUBLIC                                                                             designates that every user is granted the privilege

Note: Current system privileges can be found in the SESSION_PRIVS dictionary view.

Granting System Privileges

The DBA uses the GRANT statement to allocate system privileges to the user. After the user has been granted the privileges, the user can immediately use those privileges.

In the example in the slide, user Scott has been assigned the privileges to create sessions, tables, sequences, and views.

GRANT  create session, create table,

       create sequence, create view

TO     scott;

What Is a Role?

A role is a named group of related privileges that can be granted to the user. This method makes it easier to revoke and maintain privileges.

A user can have access to several roles, and several users can be assigned the same role. Roles are typically created for a database application.

Creating and Assigning a Role

First, the DBA must create the role. Then the DBA can assign privileges to the role and assign the role to users.

Syntax

                                      CREATE   ROLE  role;

In the syntax:

                                      role    Is the name of the role to be created

After the role is created, the DBA can use the GRANT statement to assign the role to users as well as assign privileges to the role.

Creating a Role

The example in the slide creates a manager role and then enables managers to create tables and views. It then grants Bell and Kochhar the role of managers. Now Bell and Kochhar can create tables and views.

If users have multiple roles granted to them, they receive all the privileges associated with all the roles.

CREATE ROLE manager;

GRANT create table, create view                     

TO manager;

GRANT manager TO BELL, KOCHHAR;    

Changing Your Password

The DBA creates an account and initializes a password for every user. You can change your password by using the ALTER USER statement.

ALTER USER HR                                           

IDENTIFIED BY employ;

Syntax

ALTER USER user IDENTIFIED BY password;

In the syntax:

user                                                     Is the name of the user

password                                 Specifies the new password

Although this statement can be used to change your password, there are many other options. You must have the ALTER USER privilege to change any other option.

For more information, see the Oracle Database10g SQL Reference manual.

Object Privileges

An object privilege is a privilege or right to perform a particular action on a specific table, view, sequence, or procedure. Each object has a particular set of grantable privileges. The table in the slide lists the privileges for various objects. Note that the only privileges that apply to a sequence are SELECT and ALTER. UPDATE, REFERENCES, and INSERT can be restricted by specifying a subset of updatable columns. A SELECT privilege can be restricted by creating a view with a subset of columns and granting the SELECT privilege only on the view. A privilege granted on a synonym is converted to a privilege on the base table referenced by the synonym.

Granting Object Privileges

Different object privileges are available for different types of schema objects. A user automatically has all object privileges for schema objects contained in the user’s schema. A user can grant any object privilege on any schema object that the user owns to any other user or role. If the grant includes WITH GRANT OPTION, then the grantee can further grant the object privilege to other users; otherwise, the grantee can use the privilege but cannot grant it to other users.

In the syntax:

object_priv                                          Is an object privilege to be granted

ALL                                                                Specifies all object privileges

columns                                                           Specifies the column from a table or view on which
                                                privileges are granted

ON object                                            Is the object on which the privileges are granted

TO                                                                               Identifies to whom the privilege is granted

PUBLIC                                                         Grants object privileges to all users

WITH GRANT OPTION       Enables the grantee to grant the object privileges to other                                                                               users and roles

Guidelines

•         To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.

•         An object owner can grant any object privilege on the object to any other user or role of the database.

•         The owner of an object automatically acquires all object privileges on that object.

The first example in the slide grants users Sue and Rich the privilege to query your EMPLOYEES table. The second example grants UPDATE privileges on specific columns in the DEPARTMENTS table to Scott and to the manager role.

If Sue or Rich now want to use a SELECT statement to obtain data from the EMPLOYEES table, the syntax they must use is:

SELECT  * FROM HR.employees;

Alternatively, they can create a synonym for the table and issue a SELECT statement from the synonym:

CREATE SYNONYM emp FOR HR.employees;

SELECT * FROM emp;

Passing On Your Privileges

WITH GRANT OPTION Keyword

A privilege that is granted with the WITH GRANT OPTION clause can be passed on to other users and roles by the grantee. Object privileges granted with the WITH GRANT OPTION clause are revoked when the grantor’s privilege is revoked.

GRANT                        object_priv [(columns)]

 ON                                         object

 TO                                          {user|role|PUBLIC}

 [WITH GRANT OPTION];

GRANT  select

ON     employees

TO     sue, rich;

GRANT  update (department_name, location_id)

ON     departments

TO     scott, manager;

GRANT  select, insert

ON     departments

TO     scott

WITH   GRANT OPTION;

The example in the slide gives user Scott access to your DEPARTMENTS table with the privileges to query the table and add rows to the table. The example also shows that Scott can give others these privileges.

GRANT  select

ON                                  alice.departments

TO                                   PUBLIC;

PUBLIC Keyword

An owner of a table can grant access to all users by using the PUBLIC keyword.

The second example allows all users on the system to query data from Alice’s DEPARTMENTS table.

Confirming Granted Privileges

If you attempt to perform an unauthorized operation, such as deleting a row from a table for which you do not have the DELETE privilege, the Oracle server does not permit the operation to take place.

If you receive the Oracle server error message “Table or view does not exist,” then you have done either of the following:

•         Named a table or view that does not exist

•         Attempted to perform an operation on a table or view for which you do not have the appropriate privilege

You can access the data dictionary to view the privileges that you have. The chart in the slide describes various data dictionary views.

Data Dictionary View         Description ROLE_SYS_PRIVS System privileges granted to roles ROLE_TAB_PRIVS Table privileges granted to roles USER_ROLE_PRIVS           Roles accessible by the user USER_TAB_PRIVS_MADE Object privileges granted on the user’s           objects USER_TAB_PRIVS_RECD Object privileges granted to the user USER_COL_PRIVS_MADE Object privileges granted on the             columns of the user’s objects USER_COL_PRIVS_RECD Object privileges granted to the user on         specific columns USER_SYS_PRIVS System privileges granted to the user

 

Revoking Object Privileges

You can remove privileges granted to other users by using the REVOKE statement. When you use the REVOKE statement, the privileges that you specify are revoked from the users you name and from any other users to whom those privileges were granted by the revoked user.

REVOKE {privilege [, privilege...]|ALL}

ON                                  object

FROM   {user[, user...]|role|PUBLIC}

[CASCADE CONSTRAINTS];

In the syntax:

CASCADE is required to remove any referential integrity constraints made to the CONSTRAINTS object by means of the REFERENCES privilege

Note: If a user were to leave the company and you revoke his privileges, you must regrant any privileges that this user may have granted to other users. If you drop the user account without revoking privileges from it, then the system privileges granted by this user to other users are not affected by this action.

The example in the slide revokes SELECT and INSERT privileges given to user Scott on the DEPARTMENTS table.

REVOKE  select, insert

ON      departments

FROM    scott;

Note: If a user is granted a privilege with the WITH GRANT OPTION clause, that user can also grant the privilege with the WITH GRANT OPTION clause, so that a long chain of grantees is possible, but no circular grants (granting to a grant ancestor) are permitted. If the owner revokes a privilege from a user who granted the privilege to other users, then the revoking cascades to all the privileges granted.

For example, if user A grants a SELECT privilege on a table to user B including the WITH GRANT OPTION clause, user B can grant to user C the SELECT privilege with the WITH GRANT OPTION clause as well, and user C can then grant to user D the SELECT privilege. If user A revokes privileges from user B, then the privileges granted to users C and D are also revoked.