| Oracle® Database 2 Day Developer's Guide, 11g Release 1 (11.1) Part Number B28843-01 |
|
|
View PDF |
| Oracle® Database 2 Day Developer's Guide, 11g Release 1 (11.1) Part Number B28843-01 |
|
|
View PDF |
This section shows how to explore the database, retrieve information from it, change the information in existing tables, and control transaction processing.
This chapter contains the following sections:
In addition to tables, Oracle Database has many other database objects types. While some objects have many extensive management options, most of them have similar properties. For example, every object in a database belongs to only one schema, and has a unique name with that schema. For that reason, Oracle recommends that your object naming conventions and practices support clear identification of new objects and object types with appropriate schemas. All objects that you will use here belong to the same hr schema. Generally, applications work with objects in the same schema.
When you create your own objects, remember that object names cannot exceed 30 characters, and must start with a letter.
In this section, you will further familiarize yourself with the hr sample schema and its attributes, or database objects. You will learn how you can view these objects by browsing with the Oracle SQL Developer.
Start by examining some of the types of objects that each schema has.
To browse the HR schema:
Start Oracle SQL Developer.
In the SQL Developer navigation hierarchy, under the Connections tab, click the 'plus' sign next to hr_conn.
In the Connection Information dialog, authenticate the connection to the hr schema by providing the password. Click OK.
In Connections navigation hierarchy, click the 'plus' sign next to hr_conn to expand the view on the hr schema database objects.
The schema contains many objects, including tables, views, indexes, packages, procedures, functions, triggers, types, sequences, and so on. Briefly, here is a definition of each type of database object that you are likely to use:
Tables are basic units of data storage in anOracle Database, and hold all user-accessible data.
Views are customized presentations of data from one or more tables, or even other views.
Indexes are optional structures that are created to increase the performance of data retrieval on a table.
Functions are PL/SQL programming objects that can be stored and executed in the database. Functions return a value.
Procedures are PL/SQL programming objects that can be stored and executed in the database. Procedures do not return a value.
Packages contains procedures or functions that can be stored and executed in the database.
Triggers are stored procedures or functions that are associated with a table, view, or event. Triggers can be called before or after an event for follow-up action, to prevent erroneous operations, to modify new data so that it conforms to explicit business rules, or to log a record of an operation or an event.
Types associate a fixed set of properties with the values that can be used in a column of a table, or in an argument of a procedure or function. Oracle Database treats values of one data type differently from values of another data type.
Sequences are used to generate unique integers; you can use them to automatically generate primary key values.
In this section, you will learn how to find out about the properties of database tables, and how to view the data these tables contain.
An Oracle Database table is its basic data container. All data that a user can access is inside one of the tables of the database schema. Each table is two-dimensional object that has rows, which are individual records, and columns, which represent the various fields of each record.
To view a table:
In Connections navigation hierarchy, click the 'plus' sign next to Tables to expand the list of tables in the hr schema.
The expanded list of tables includes the tables countries, departments, employees, job_history, jobs, locations, and regions.
Click the employees table.
On the right-hand side of the Oracle SQL Developer window, under the Columns tab, a listing of all columns of this table appears: EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, and DEPARTMENT_ID. Each column of a table has an associated data type that defines it as character data, an integer, a floating-point number, a date, or time information. To see all properties of the column, move the horizontal scroll bar to the right.
Click the Constraints tab.
You will see all the constraints that are used on this table including the type of constraint, the constraint's referenced table, whether the constraint is enabled, and other properties.
Similarly, you can explore the various table properties by clicking on the appropriate tabs:
Grants describes the privileges for the table
Statistics describes the properties of the data in the table, such as number of records, the number of blocks in memory used by the table, average row length, and so on.
Column Statistics lists the number of distinct entries for each column, the low and high values, and so on.
Triggers lists the triggers associated with the table together with the type of trigger, the triggering event, and so on.
Dependencies lists all the objects that are dependent on this table, such as triggers and views.
Details lists other details of the table, such as creation date, owner (hr), name, partitioning information, and so on.
Indexes lists the indexes that are defined on the table columns, together with their status, type, and so on.
SQL summarizes the preceding information in the definition of the table employees; it includes column definition, indexes, and so on.
To view data in a table:
On the right-hand side of the Oracle SQL Developer window, click the Data tab.
You will see a listing of all records of this table. Each column of a table has an associated data type that defines it as character data, an integer, a floating-point number, a date, or time information. To see all properties of the column, move the horizontal scroll bar to the right.
A query is an operation that retrieves data from one or more tables or views. A top-level SELECT statement returns results of a query, and a query nested within another SQL statement is called a subquery.
This section introduces some types of queries and subqueries.
See Also:
A simple query form looks like this:
SELECT select_list FROM source_list
Here, select_list specifies the columns from which the data is retrieved, and the source_list specifies the tables or views where these columns are found. The number of columns, as well as the data type and length of each column, is determined by the elements of the select list. Note also that the select list can use SQL functions.
To see all columns in a table, use * for select_list.
Example 2-1uses the SELECT statement to return the information you previously saw by viewing the employees table in the Data window.
Example 2-1 Selecting All Columns in a Table
In the SQL Worksheet pane, enter:
SELECT * FROM employees;
Above the SQL Worksheet pane, click the Run Script icon. Alternatively, you can use the F5 shortcut key.
Click the Script Output tab, below the SQL Worksheet pane, to see the results of the query.
EMPLOYEE_ID FIRST_NAME LAST_NAME ... ---------------------- -------------------- ------------------------- ... 100 Steven King ... 101 Neena Kochhar ... 102 Lex De Haan ... ... 107 rows selected
Between running different queries, you can clear both the SQL Worksheet and Script Output panes by clicking the Eraser icon in the toolbar.
Example 2-2 shows how to use the SELECT statement to return only the columns you requested in your query, namely first_name, last_name, and hire_date.
Example 2-2 Selecting Specific Columns from a Table
SELECT first_name, last_name, hire_date FROM employees;
These are the results of the of the query.
FIRST_NAME LAST_NAME HIRE_DATE ---------------------- ------------------- ------------------------- Steven King 17-JUN-87 Neena Kochhar 21-SEP-89 Lex De Haan 13-JAN-93 ... 107 rows selected
To display a column with a new heading, you can rename a column within your report by using an alias immediately after the correct name of the column. This alias effectively renames the item for the duration of the query.
In Example 2-3, the SELECT statement returns the columns you request in your query, but with the column headings that you specified as aliases: name1, name2, and hired.
name1, name2, and hired.
Example 2-3 Using a Simple Column Alias
SELECT first_name name1, last_name name2, hire_date hired FROM employees;
The results of the query follow:
NAME1 NAME2 HIRED --------------------- -------------------- ------------------------- Steven King 17-JUN-87 Neena Kochhar 21-SEP-89 Lex De Haan 13-JAN-93 ... 107 rows selected
If the alias that you want to use contains uppercase and lowercase characters or spaces, or a combination, you must use double quotation marks (").
Example 2-4 uses a SELECT statement to return the columns with column heading aliases that you specify: First, Last, and Date Started.
Example 2-4 Using Quoted Alias Columns
SELECT first_name "First", last_name "Last", hire_date "Date Started" FROM employees;
The results of the query follow.
First Last Date Started ---------------------- -------------------- ------------------------- Steven King 17-JUN-87 Neena Kochhar 21-SEP-89 Lex De Haan 13-JAN-93 ... 107 rows selected
In addition to the SELECT and FROM keywords, other common clauses are used in queries. The WHERE clause uses comparison operators to select the rows that should be retrieved, instead of returning all the rows in the tables.
This table lists the comparison operators that can be used in the WHERE clause.
| Comparison Operator | Definition |
|---|---|
= |
Tests for equality |
!=, <> |
Tests for inequality |
> |
Tests for greater than |
>= |
Tests for greater than or equal |
< |
Tests for less than |
<= |
Tests for less than or equal |
BETWEEN a AND b |
Tests for a fit in the range between two values, inclusive |
LIKE |
Tests for a match in a string, using the wildcard symbols (%) for zero or multiple characters, or underscore (_) for a single character |
IN() |
Tests for a match in a specified list of values |
NOT IN() |
Tests that there is no match in a specified list of values |
IS NULL |
Tests that the value is null |
IS NOT NULL |
Tests that the value is not null |
The WHERE clause can test a single condition at a time, or combine multiple tests using the AND clause.
Example 2-5 shows how to use the WHERE clause to return the column values that are restricted to a single department, which has 90 for its department_id.
department_id
Example 2-5 Testing for a Single Condition
SELECT first_name "First", last_name "Last" FROM employees WHERE department_id=90;
The results of the query appear.
First Last -------------------- ------------------------- Steven King Neena Kochhar Lex De Haan 3 rows selected
Example 2-6 shows how to use the WHERE ... AND clause to return the rows that are restricted to two separate condition, to match a salary that is greater or equal to 11,000, and an assigned (not null) commission rate.
Example 2-6 Testing Multiple Conditions
SELECT first_name "First", last_name "Last", SALARY "Salary", COMMISSION_PCT "%" FROM employees WHERE salary >=11000 AND commission_pct IS NOT NULL;
The results of the query appear.
First Last Salary % -------------------- --------------------- -------------------- ----- John Russell 14000 0.4 Karen Partners 13500 0.3 Alberto Errazuriz 12000 0.3 ... 6 rows selected
Example 2-7 uses the WHERE clause to return the six rows where the last name starts with Ma: Mallin, Markle, Marlow, Marvins, Matos, and Mavris. If you use a matching expression '%ma%' instead (the text ma could appear anywhere in the column), your results would contain only three rows, for Kumar, Urman, and Vollman.
Example 2-7 Testing for a Matching String
SELECT first_name "First", last_name "Last" FROM employees WHERE last_name LIKE 'Ma%';
The results of the query appear.
First Last -------------------- ------------------------- Jason Mallin Steven Markle James Marlow ... 6 rows selected
Example 2-8 shows you how to use the WHERE ... IN clause to find employees who work in several different departments, matching the DEPARTMENT_ID to a list of values 100, 110, 120. The result will contain eight rows, with four rows matching the first value in the list, and the other two rows matching the second value in the list; there are no matches for 120.
Example 2-8 Testing for a Match in a List of Values
SELECT first_name "First", last_name "Last", department_id "Department" FROM employees WHERE department_id IN (100, 110, 120);
The results of the query appear.
First Last Department -------------------- ------------------------- ---------------------- John Chen 100 Daniel Faviet 100 William Gietz 110 ... 8 rows selected
If you want to find employees who work in a particular department, but do not know the corresponding department_id value, you must look in both the employees and departments table. Looking at an intersection of two tables is a JOIN operation.
Fully qualified column names, such as employees.employee_id, are optional. However, when queries use two or more tables that have the same column name, you must identify these column to the table. For example, the employees.department_id and departments.department_id could be used together to determine the name of a department in which an employee works.
Note that when using fully qualified column names, the query is more readable if you use an alias for the name of the table, such as d for departments. The column departments.department_id then becomes d.department_id, and employees.department_id becomes e.department_id. You must create these table aliases in the FROM clause of the query.
Example 2-9 shows the result set containing columns from two separate tables. Because the column names in the report are unique, they did not need to be qualified by the table name. However, because the WHERE clause uses the same column name from two different tables, you must qualify it.
Example 2-9 Testing for a Value in Another Table
SELECT e.first_name "First", e.last_name "Last", d.department_name "Department" FROM employees e, departments d WHERE e.department_id = d.department_id;
The results of the query appear.
First Last Department -------------------- ------------------------- ------------------------------ Jennifer Whalen Administration Michael Hartstein Marketing Pat Fay Marketing ... 106 rows selected
Regular expressions allow you to use standard syntax conventions to search for complex patterns in character sequences. A regular expression defines the search pattern by using metacharacters that specify search algorithms, and literals that specify the characters.
Regular expression functions include REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, and REGEXP_SUBSTR.
Example 2-10 shows how to find all managers. The metacharacter | indicates an OR condition, which you must use because the manager position is specified either as '%_MGR' or '%_MAN', depending on department. The option i specifies that the match should be case insensitive.
Example 2-10 Finding a Matching Data Pattern
SELECT first_name "First", last_name "Last", job_id "Job" FROM employees WHERE REGEXP_LIKE (job_id, '(_m[an|gr])', 'i');
The results of the query appear.
First Last Job -------------------- ------------------------- ---------- Nancy Greenberg FI_MGR Den Raphaely PU_MAN Matthew Weiss ST_MAN ... 14 rows selected
Example 2-11 shows how the REGEXPR_LIKE expression selects rows where the last_name has a double vowel (two adjacent occurrences of either a, e, i, o, or u). See Oracle Database SQL Language Reference for information about the REGEXP_LIKE condition.
last_name
Example 2-11 Finding a Matching Data Pattern (Adjacent Characters)
SELECT first_name "First", last_name "Last" FROM employees WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');
The results of the query appear.
First Last -------------------- ------------------------- Harrison Bloom Lex De Haan Kevin Feeney ... 8 rows selected
To find a data pattern and replace it with another one, use the REGEXPR_REPLACE. Example 2-12 replaces the phone numbers of the format 'nnn.nnn.nnnn' with the format '(nnn) nnn-nnnn'. Note that digits are matched with the metacharacter [:digit], while the metacharacter {n} indicates the number of occurrences. The metacharacter '.' typically indicates any character in an expression, so the metacharacter \ is used as an escape character and makes the following character in the pattern a literal. This result set shows the telephone numbers in the new format. See Oracle Database SQL Language Reference for information about the REGEXP_REPLACE condition.
Example 2-12 Replacing a Data Pattern
SELECT first_name "First", last_name "Last",
phone_number "Old Number",
REGEXP_REPLACE(phone_number,
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') "New Number"
FROM employees
WHERE department_id = 90;
The results of the query appear.
First Last Old Number New Number
-------------------- ---------------------- -------------------- --------------
Steven King 515.123.4567 (515) 123-4567
Neena Kochhar 515.123.4568 (515) 123-4568
Lex De Haan 515.123.4569 (515) 123-4569
3 rows selected
Example 2-13 shows how you can use the REGEXPR_SUBSTR function to find the first substring that matches a pattern. Note that the metacharacter'+' indicates multiple occurrences of the pattern. This result set extracts numerals and dashes from the street_address column. See Oracle Database SQL Language Reference for information about the REGEXP_SUBSTR expression.
Example 2-13 Returning a Substring
SELECT street_address, REGEXP_SUBSTR(street_address, '[[:digit:]-]+', 1, 1) "Street Numbers" FROM locations;
The results of the query appear.
STREET_ADDRESS Street Numbers ---------------------------------------- ---------------------- 1297 Via Cola di Rie 1297 93091 Calle della Testa 93091 2017 Shinjuku-ku 2017 ... 23 rows selected
The REGEXPR_INSTR function enables you to find the position of the first substring that matches a pattern. In Example 2-14, you use REGEXPR_INSTR to find a space character, ' '. Note that the metacharacter '+' indicates multiple occurrences of the pattern. This result set shows the position of the first space in each address. See Oracle Database SQL Language Reference for information about the REGEXP_INSTR expression.
Example 2-14 Returning a Location of a Substring
SELECT street_address, REGEXP_INSTR(street_address, '[ ]+', 1, 1) "Position" FROM locations;
The results of the query appear.
STREET_ADDRESS Position ---------------------------------------- ---------------------- 1297 Via Cola di Rie 5 93091 Calle della Testa 6 2017 Shinjuku-ku 5 ... 23 rows selected
The function REGEXPR_COUNT determines the number of times the specified character pattern repeats in a string. In Example 2-15, REGEXPR_COUNT returns the number of times the space character occurs in the street_address column of the table locations. See Oracle Database SQL Language Reference for information about the REGEXP_COUNT expression.
Example 2-15 Returning the Number of Occurrences of a Substring
SELECT street_address, REGEXP_COUNT(street_address, ' ', 1) "Number of Spaces" FROM locations;
The results of the query appear.
STREET_ADDRESS Number of Spaces ---------------------------------------- ---------------------- 1297 Via Cola di Rie 4 93091 Calle della Testa 3 2017 Shinjuku-ku 1 ... 23 rows selected
This result set shows the number of spaces in each address.
See Also:
Oracle Database SQL Language Reference for syntax of regular expressions
In SQL, the ORDER BY clause is used to identify which columns are used to sort the resulting data. The sort criteria does not have to be included in the result set, and can include expressions, column names, arithmetic operations, user-defined functions, and so on.
Example 2-16 shows an ORDER BY clause that returns the result set sorted in order of last_name, in ascending order.
Example 2-16 Use Quoted Alias Columns
SELECT first_name "First", last_name "Last", hire_date "Date Started" FROM employees ORDER BY last_name;
The results of the query appear.
First Last Date Started -------------------- ------------------------- ------------------------- Ellen Abel 11-MAY-96 Sundar Ande 24-MAR-00 Mozhe Atkinson 30-OCT-97 ... 107 rows selected
SQL arithmetic operators and other build-in functions allow you to perform calculations directly on data stored in the tables.
See Also:
Oracle Database SQL Language Reference for information on all available SQL functions
Oracle Database SQL supports the basic arithmetic operators, such as the plus sign (+) for addition, the minus sign (-) for subtraction, the asterisk (*) for multiplication, and the forward slash (/) for division. These are evaluated according to standard arithmetic rules of evaluation order.
In Example 2-17, the result set show the salary earned by employees who are eligible for commission earnings, in order of the hire date.
Example 2-17 Evaluating an Arithmetic Expression
SELECT first_name "First", last_name "Last", salary * 12 "Annual Compensation" FROM employees WHERE commission_pct IS NOT NULL ORDER BY hire_date;
The results of the query appear.
First Last Annual Compensation -------------------- ------------------------- ---------------------- Janette King 120000 Patrick Sully 114000 Ellen Abel 132000 ... 35 rows selected
Oracle Database has many numeric functions for manipulating numeric values, such as ROUND for rounding to a specified decimal or TRUNC for truncating to a specified decimal. These functions all return a single value for each row that is evaluated.
Example 2-18 shows how to determine daily pay, rounded off to the nearest cent.
Example 2-18 Rounding off Numeric Data
SELECT first_name "First", last_name "Last", ROUND(salary/30, 2) "Daily Compensation" FROM employees;
The results of the query appear.
First Last Daily Compensation -------------------- ------------------------- ---------------------- Steven King 800 Neena Kochhar 566.67 Lex De Haan 566.67 ... 107 rows selected
Example 2-19 shows how to determine daily pay that is truncated at the nearest dollar. Note that the TRUNC function does not round-up the value.
Example 2-19 Truncating Numeric Data
SELECT first_name "First", last_name "Last", TRUNC(salary/30, 0) "Daily Compensation" FROM employees;
The results of the query appear.
First Last Daily Compensation -------------------- ------------------------- ---------------------- Steven King 800 Neena Kochhar 566 Lex De Haan 566 ... 107 rows selected
See Also:
Oracle Database SQL Language Reference for information on numeric SQL functions
Oracle Database includes an extensive list of character functions for customizing character values.
These functions can change the case of a character expression to UPPER or LOWER, remove blanks, concatenate strings, and extract or remove substrings.
Example 2-20 demonstrates how to change the character case of your expression. The result set shows the results of UPPER, LOWER, and INITCAP functions.
Example 2-20 Changing the Case of Character Data
SELECT UPPER(first_name) "First upper", LOWER(last_name) "Last lower", INITCAP(email) "E-Mail" FROM employees;
The results of the query appear.
First upper Last lower E-Mail -------------------- ------------------------- ------------------------- STEVEN king Sking NEENA kochhar Nkochhar LEX de haan Ldehaan
To produce information from two separate columns or expressions in the same column of the report, you can concatenate the separate results by using the concatenation operator, ||. Note also that in Example 2-21, you are performing a 4-way join operation. This result set shows that the simple concatenation function in column Name listed the last_name value immediately after the first_name value, while the nested concatenation function in column Location separated the city and country_name values.
||.
Example 2-21 Concatenating Character Data
SELECT e.first_name || ' ' || e.last_name "Name", l.city || ', ' || c.country_name "Location" FROM employees e, departments d, locations l, countries c WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND l.country_id=c.country_id ORDER BY last_name;
The results of the query appear.
Name Location ----------------------------- -------------------------------------------- Ellen Abel Oxford, United Kingdom Sundar Ande Oxford, United Kingdom Mozhe Atkinson South San Francisco, United States of America ... 106 rows selected
You can use RTRIM and LTRIM functions to remove characters (by default, spaces) from the beginning or the end of character data, respectively. The TRIM function removes both leading and following characters. In Example 2-22, you use a type conversion function, TO_CHAR. This result set shows that all employees without a leading M in their last_name values, the MAN missing from the end of the job_id values, and the leading 0 is missing from the date_hired values.
Example 2-22 Trimming Character Data
SELECT LTRIM(last_name, 'M') "Last Name", RTRIM(job_id, 'MAN') "Job", TO_CHAR(TRIM(LEADING 0 FROM hire_date)) "Hired" FROM employees WHERE department_id=50;
The results of the query appear.
Last Name Job Hired ------------------------- ---------- --------- Weiss ST_ 18-JUL-96 Fripp ST_ 10-APR-97 Kaufling ST_ 1-MAY-95 Vollman ST_ 10-OCT-97 ourgos ST_ 16-NOV-99 ... ikkilineni ST_CLERK 28-SEP-98 Landry ST_CLERK 14-JAN-99 arkle ST_CLERK 8-MAR-00 ... arlow ST_CLERK 16-FEB-97 ... allin ST_CLERK 14-JUN-96 ... Philtanker ST_CLERK 6-FEB-00 ... Patel ST_CLERK 6-APR-98 ... atos ST_CLERK 15-MAR-98 Vargas ST_CLERK 9-JUL-98 Taylor SH_CLERK 24-JAN-98 ... Geoni SH_CLERK 3-FEB-00 ... Cabrio SH_CLERK 7-FEB-99 ... Bell SH_CLERK 4-FEB-96 Everett SH_CLERK 3-MAR-97 cCain SH_CLERK 1-JUL-98 ... 45 rows selected
You can use RPAD to add characters (by default, spaces) to the end of character data. The LPAD function adds characters to the beginning of character data.
In Example 2-23, the result set shows a simple histogram of relative salary values.
Example 2-23 Padding Character Data
SELECT first_name || ' ' || last_name "Name",
RPAD(' ', salary/1000, '$') "Salary"
FROM employees;
The results of the query appear.
Name Salary -------------------------------------- ---------------- Steven King $$$$$$$$$$$$$$$$$$$$$$$ Neena Kochhar $$$$$$$$$$$$$$$$ Lex De Haan $$$$$$$$$$$$$$$$ ... 107 rows selected
You can use SUBSTR to extract only a substring of data, specified by the starting character position and the total number of characters.
In Example 2-24, you use SUBSTR to abbreviate the first_name value to an initial, and strip the area code from the phone_number value.
Example 2-24 Extracting a Substring of Character Data
SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name "Name", SUBSTR(phone_number, 5, 8) "Phone" FROM employees;
The results of the query appear.
Name Phone ---------------------------- -------- S. King 123.4567 N. Kochhar 123.4568 L. De Haan 123.4569 ... 107 rows selected
This result set shows the first_name values abbreviated to an initial, and the phone_number values without the leading area code component.
You can use REPLACE, in combination with SUBSTR, to replace a specific substring if you know its relative location in the character data.
In Example 2-25, you use SUBSTR in the WHERE clause to replace the abbreviation for a job code.
Example 2-25 Replacing Substring of Character Data
SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name "Name", REPLACE(job_id, 'SH', 'SHIPPING') "Job" FROM employees WHERE SUBSTR(job_id, 1, 2) = 'SH';
The results of the query appear.
Name Job ----------------------- ------------------------ W. Taylor SHIPPING CLERK J. Fleaur SHIPPING_CLERK M. Sullivan SHIPPING_CLERK ... 20 rows selected
This result set shows the first_name values abbreviated to an initial, and the job_id values were replaced.
See Also:
Oracle Database SQL Language Reference for information on character SQL functions
Oracle Database has data functions for manipulating and calculating date and time data, including interval functions.
In Example 2-26, you will determine the duration of employment in a particular job for those employees who have switched to a different position. Note that the names are not unique; this is because employees may hold more than two different positions over time. See Oracle Database SQL Language Reference for information about the MONTHS_BETWEEN function.
Example 2-26 Determining the Number of Months Between Dates
SELECT e.first_name || ' ' || e.last_name "Name", TRUNC(MONTHS_BETWEEN(j.end_date, j.start_date)) "Months Worked" FROM employees e, job_history j WHERE e.employee_id = j.employee_id ORDER BY "Months Worked";
The results of the query appear.
Name Months Worked ---------------------------------------------- ------------- Jonathon Taylor 9 Payam Kaufling 11 Jonathon Taylor 11 ... 10 rows selected
You will notice that this result shows that of the employees who left the company, the shortest and the longest stays were 9 and 69 months, respectively.
In Example 2-27, you will use the EXTRACT function to determine if employees are in their sixth calendar year of continuous employment. The EXTRACT function can also be used in combination with MONTH, DATE, and so on.
Note that the SYSDATE function gives the current date of the system clock. See Oracle Database SQL Language Referencefor information about the SYSDATE function.
Example 2-27 Determining the Years Between Dates
SELECT first_name || ' ' || last_name "Name", (EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR FROM hire_date)) "Years Employed" FROM employees;
The results of the query appear.
Name Years Employed ---------------------------------------------- -------------- Steven King 20 Neena Kochhar 18 Lex De Haan 14 ... 107 rows selected
You will notice that this result shows that employee 'Steven King' has worked for the company the longest, 20 years.
In Example 2-28, you will use the last_day function to determine the last day of the month in which an employee was hired.
Example 2-28 Getting the Last Day of the Month for a Specified date
SELECT first_name || ' ' || last_name "Name", hire_date "Date Started", LAST_DAY(hire_date) "End of Month" FROM employees;
The results of the query appear.
Name Date Started End of Month ------------------------------ ------------------ ------------------------- Steven King 17-JUN-87 30-JUN-87 Neena Kochhar 21-SEP-89 30-SEP-89 Lex De Haan 13-JAN-93 31-JAN-93 ... 107 rows selected
You will notice that this result shows the correct end of the month for each hire_date value.
In Example 2-29, you will use the ADD_MONTHS function to add 6 months to the date on which an employee was hired. See Oracle Database SQL Language Reference for information about the ADD_MONTH function.
Example 2-29 Adding Months to a Date
SELECT first_name || ' ' || last_name "Name", hire_date "Date Started", ADD_MONTHS(hire_date, 6) "New Date" FROM employees;
The results of the query appear.
Name Date Started New Date ------------------------ ------------------------- ------------------------- Steven King 17-JUN-87 17-DEC-87 Neena Kochhar 21-SEP-89 21-MAR-90 Lex De Haan 13-JAN-93 13-JUL-93 ... 107 rows selected
In Example 2-30, you will use the SYSTIMESTAMP function determine the current system time and date. SYSTIMESTAMP is similar to SYSDATE, but also contains time of day information, including the time zone and fractional seconds. See Oracle Database SQL Language Referencefor information about the SYSTIMESTAMP function.
Note that instead of an hr schema table, you are using the table DUAL, a small table in the data dictionary that you can reference to guarantee a known result. See Oracle Database Concepts for information about the DUAL table and Oracle Database SQL Language Reference for information about selecting from the DUAL table.
Example 2-30 Getting the System Date and Time
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' || EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' || ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' || EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' || EXTRACT(DAY FROM SYSTIMESTAMP) || '/' || EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date" FROM DUAL;
The results of the query appear.
System Time and Date ------------------------------------------------------------ 18:25:56, 4/5/2007
Your result would change, depending on the current SYSTIMESTAMP value.
See Also:
Oracle Database SQL Language Reference or datetime functions
Oracle Database has data functions for converting between different data types. This is particularly useful when you need to display data of different data types in the same column.
There are three general types of conversion functions: for characters (TO_CHAR), for numbers (TO_NUMBER), for dates (TO_DATE) and for timestamps (TO_TIMESTAMP).
You will use the TO_CHAR function to convert a date into a desired format. Example 2-31 converts the HIRE_DATE values to a 'FMMonth DD YYYY' format; note that the FM option removes all leading or trailing blanks from the month name. Other options you could use include 'DD-MON-YYYY AD', 'MM-DD-YYYY HH24:MI:SS', and so on.
Example 2-31 Using TO_CHAR to Convert a Date Using a Format Template
SELECT first_name || ' ' || last_name "Name", TO_CHAR(hire_date, 'FMMonth DD YYYY') "Date Started" FROM employees;
The results of the query appear.
Name Date Started ---------------------------------------------- ----------------- Steven King June 17 1987 Neena Kochhar September 21 1989 Lex De Haan January 13 1993 ... 107 rows selected
Your result set lists all the hire_date values in the new format.
Example 2-32 shows how you can use two standard format tags, Short Date (DS) and Long Date (DL), to format your date.
Example 2-32 Using TO_CHAR to Convert a Date Using a Standard Format
SELECT first_name || ' ' || last_name "Name", TO_CHAR(hire_date, 'DS') "Short Date", TO_CHAR(hire_date, 'DL') "Long Date" FROM employees;
The results of the query appear.
Name Short Date Long Date --------------------------- ---------- ------------------------- Steven King 6/17/1987 Wednesday, June 17, 1987 Neera Kochhar 9/21/19889 Thursday, September 21, 1989 Lex De Haen 1/13/1993 Wednesday, January 13, 1993 ... 107 rows selected
You can use the TO_CHAR function to convert a number to a desired currency format. Example 2-33 will convert the salary values to a '$99,999.99' format. See Oracle Database SQL Language Reference for TO_CHAR.
Example 2-33 Using TO_CHAR to Convert a Number to a Currency Template
SELECT first_name || ' ' || last_name "Name", TO_CHAR(salary, '$99,999.99') "Salary" FROM employees;
The results of the query appear.
Name Salary ---------------------------------------------- ----------- Steven King $24,000.00 Neena Kochhar $17,000.00 Lex De Haan $17,000.00 ... 107 rows selected
Example 2-34 shows how you can use the TO_NUMBER function to convert a character into a number that you can subsequently use in calculations. See Oracle Database SQL Language Reference for TO_NUMBER.
Example 2-34 Using TO_NUMBER to Convert a Character to a Number
SELECT first_name || ' ' || last_name "Name",
TO_NUMBER('300') + salary "Proposed Salary"
FROM employees
WHERE SUBSTR(job_id, 4, 5) = 'CLERK';
The results of the query appear.
Name Proposed Salary ---------------------------------------------- --------------- Alexander Khoo 3400 Shelli Baida 3200 Sigal Tobias 3100 ... 45 rows selected
Your result set lists all the proposed salary values for the selected subset of employees.
You can use the TO_DATE function to convert a character data with a specified format mode into a date. In Example 2-35, you will use the format model 'Month dd, YYYY, HH:MI A.M.'; other formats include 'DD-MON-RR', 'FF-Mon-YY HH24:MI:SI', and so on.
Example 2-35 Using TO_DATE to Convert a Character Data to a Date
SELECT TO_DATE('January 5, 2007, 8:43 A.M.',
'Month dd, YYYY, HH:MI A.M.') "Date"
FROM DUAL;
The results of the query appear.
Date --------- 05-JAN-07
Your result converts the character data, interpreted by the specified format string, into a DATE type.
Example 2-36 shows how you can use the TO_TIMESTAMP method with format models such as 'DD-Mon-RR HH24:MI:SS.FF'. See Oracle Database SQL Language Reference for TO_DATE.
Example 2-36 Using TO_TIMESTAMP to Convert Character Data to a Timestamp
SELECT TO_TIMESTAMP('May 5, 2007, 8:43 A.M.',
'Month dd, YYYY, HH:MI A.M.') "Timestamp"
FROM DUAL;
The results of the query appear.
Timestamp --------------------------------------------- 05-MAY-07 08.43.00.000000000 AM
Your result converts the character data, interpreted by the specified format string, into a TIMESTAMP type.
See Also:
Oracle Database SQL Language Reference for data type conversion functions
Aggregate functions operate on groups of rows, or an entire table or view. By their nature, these functions provide statistical results for sets, and include average (AVG), count (COUNT), maximum (MAX), minimum (MIN), standard deviation (STDEV), sum (SUM), and so on.
Aggregate functions are especially powerful when used it in combination with the GROUP BY clause, where a query returns a list that is grouped by one or more columns, with a distinct result for each of the groupings.
You can also use the HAVING clause, which specifies that a query should only return rows where aggregate values meet the specified conditions.
Example 2-37 shows how you can use the COUNT function and the GROUP BY clause to determine how many people report to each manager. Note that the wildcard, *, is used to denote the counting of an entire record.
Example 2-37 Counting the Number of Rows That Satisfy an Expression
SELECT manager_id "Manager", COUNT(*) "Number of Reports" FROM employees GROUP BY manager_id;
The results of the query appear.
Manager Number of Reports
--------- -----------------
1
100 14
123 8
...
19 rows selected
Your result shows how many people report to each manager. Note that one person does not report to anyone; if you examine the data, you will see that Steven King does not have a supervisor.
Example 2-38 shows how you can also use the COUNT function with a DISTINCT option to determine how many distinct values and are in a data set. Here, you will count the number of departments that have employees.
Example 2-38 Counting a Number of Distinct Values in a Set
SELECT COUNT(DISTINCT department_id) "Number of Departments" FROM employees;
The results of the query appear.
Number of Departments --------------------- 11
Your result shows that 11 departments have employees. If you look at the departments table, you will note that it lists 27 departments.
You can use basic statistical functions, such as MIN, MAX, MEDIAN, AVG, and so on, to determine the range of salaries across the set. In Example 2-39, you will examine salaries grouped by job_id, but a similar query could be used to examine salaries across departments, locations, and so on.
job_id
Example 2-39 Determining Statistical Information
SELECT job_id "Job", COUNT(*) "#", MIN(salary) "Minimum", ROUND(AVG(salary), 0) "Average", MEDIAN(salary) "Median", MAX(salary) "Maximum", ROUND(STDDEV(salary)) "Std Dev" FROM employees GROUP BY job_id ORDER BY job_id;
The results of the query appear.
Job # Minimum Average Median Maximum Std Dev ---------- ---------- ---------- ---------- ---------- ---------- ---------- AC_ACCOUNT 1 8300 8300 8300 8300 0 AC_MGR 1 12000 12000 12000 12000 0 AD_ASST 1 4400 4400 4400 4400 0 AD_PRES 1 24000 24000 24000 24000 0 AD_VP 2 17000 17000 17000 17000 0 FI_ACCOUNT 5 6900 7920 7800 9000 766 FI_MGR 1 12000 12000 12000 12000 0 HR_REP 1 6500 6500 6500 6500 0 IT_PROG 5 4200 5760 4800 9000 1926 MK_MAN 1 13000 13000 13000 13000 0 MK_REP 1 6000 6000 6000 6000 0 ... 19 rows selected
Your result shows the statistics for 19 different jobs.
If you use the HAVING clause, you can limit your result set to only the kind of values that interest you. In Example 2-40, you see the salary budget for departments where the sum of salaries exceeds $1,000,000 annually.
Example 2-40 Limiting the Results Using the HAVING Clause
SELECT Department_id "Department", SUM(salary*12) "All Salaries" FROM employees HAVING SUM(salary * 12) >= 1000000 GROUP BY department_id;
The results of the query appear.
Department All Salaries
---------- ------------
50 1876800
80 3654000
Your result shows that only two departments have salary budgets in excess of $1,000,000.
You can use the RANK function to determine the relative ordered rank of a number, and use the PERCENT_RANK function to determine the percentile position. In Example 2-41, you determine these values for a salary of $3,000 over the subset of all employees who have a 'CLERK' designation in the job_id.
job_id
You can also examine groups using the WITHIN GROUP function.
Example 2-41 Determining RANK and PERCENT_RANK
SELECT RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank", ROUND(100 * (PERCENT_RANK(3000) WITHIN GROUP (ORDER BY salary DESC)), 0) "Percentile" FROM employees WHERE job_id LIKE '%CLERK';
The results of the query appear.
Rank Percentile
---------- ----------
20 42
Your result shows that a salary of $3,000 is the 20th highest, and that it is in the 42nd percentile among all employees who have a 'CLERK' designation.
The DENSE_RANK function works much like the RANK function, but the identical values receive the same rank, and there are no gaps in the ranking. In Example 2-42, you will determine the DENSE_RANK of $3,000 over the subset of all employees who have a 'CLERK' designation in the job_id.
Example 2-42 Determining DENSE_RANK:
SELECT DENSE_RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank" FROM employees WHERE job_id LIKE '%CLERK';
The results of the query appear.
Rank
----------
12
Your result shows that a salary of $3,000 is the 12th highest using the DESNE_RANK function. Contrast it with the 20th rank obtained in the previous example that used the RANK function.
See Also:
Oracle Database SQL Language Reference for aggregate functions
To work with NULL values, Oracle Database supplies two functions. NVL substitutes a specified value if a NULL is encountered, and NVL2 specifies two possible expressions that could be evaluated (one if none of its component variables is NULL, and another one if at least one variable is NULL).
In Example 2-43, you will use the NVL and NVL2 functions to determine what the whole annual compensation would be for each employee, if they were involved in a $300,000 sale. Note that the commission rate is a multiplier on sales volume, not on base salary. Note also that the WHERE clause limits the result set to managers.
Example 2-43 Using the NVL and NVL2 Functions
SELECT first_name || ' ' || last_name "Name",
NVL((commission_pct * 100), 0) "Comm Rate",
NVL2(commission_pct,
ROUND(salary * 12 + commission_pct * 300000, 2),
salary * 12) "With $300K Sales"
FROM employees
WHERE job_id LIKE '%_M%' AND department_id = 80;
The results of the query appear.
Name Comm Rate With $300K Sales ---------------------------------- ---------------------- ----------------- John Russell 40 288000 Karen Partners 30 252000 Alberto Errazuriz 30 234000 Gerald Cambrault 30 222000 Eleni Zlotkey 20 186000 5 rows selected
Your result shows that in the Comm Rate column, the NVL function replaces a NULL value by 0. In the With $300K Sales column, the NVL2 function generates values from two different expressions, depending on the value of the COMMISSION_PCT value.
Oracle Database provides two functions that can return values based on multiple condition values.
The CASE function is equivalent to nestled IF ... THEN ... ELSE statements, as it compares a value, an expression, or a search condition, and returns a result when it finds a match.
In Example 2-44, you will use the CASE function to view prospective salary increases that would be awarded based on the length of service with the company.
Example 2-44 Using the CASE Function
SELECT first_name || ' ' || last_name "Name",
hire_date "Date Started", salary "Current Pay",
CASE
WHEN hire_date < TO_DATE('01-Jan-90') THEN TRUNC(salary*1.15, 0)
WHEN hire_date < TO_DATE('01-Jan-95') THEN TRUNC(salary*1.10, 0)
WHEN hire_date < TO_DATE('01-Jan-00') THEN TRUNC(salary*1.05, 0)
ELSE salary END "Proposed Salary"
FROM employees;
The results of the query appear.
Name Date Started Current Pay Proposed Salary -------------------------- ---------------- --------------- ------------------- Steven King 17-JUN-87 24000 27600 Neena Kochhar 21-SEP-89 17000 19550 Lex De Haen 13-JAN-93 17000 18700 ... 107 rows selected
Your result shows that the values in the Proposed Salary column have been adjusted based on the values of Date Started.
The DECODE function compares a value or expression to search values, and returns a result when it finds a match. If a match is not found, then DECODE returns the default value, or NULL (if a default value is not specified).
In Example 2-45, you will use the DECODE function to assign possible salary increases based on the job_id value.
Example 2-45 Using the DECODE Function
SELECT first_name || ' ' || last_name "Name",
job_id "Job", salary "Current Pay",
DECODE(job_id,
'PU_CLERK', salary * 1.10,
'SH_CLERK', salary * 1.15,
'ST_CLERK', salary * 1.20,
salary) "Proposed Salary"
FROM employees;
The results of the query appear.
Name Job Current Pay Proposed Salary -------------------------- ---------------- --------------- ------------------- ... Alexander Khoo PU-CLERK 3100 3410 ... Julia Nayer ST_CLERK 3200 3840 ... Winston Taylor SH_CLERK 3200 3680 ... 107 rows selected
Your result shows that the values in the 'Proposed Salary' column have been adjusted based on the job_id value.
See Also:
Oracle Database SQL Language Reference for information about the CASE function
Oracle Database SQL Language Reference for information about the DECODE function
Adding, changing and deleting operations in the database are commonly called Data Manipulation Language (DML) statements:
An INSERT statement adds new rows to an existing table.
An UPDATE statement modifies the values of a set of existing table rows.
A DELETE statement removes existing rows from a table.
Because these statements change the data in your table, Oracle recommends that you use transaction management to group all dependent DML statements together.
When you use the INSERT statement to add a row of data to a table, the data inserted must be valid for the data type and size of each column of the table.
The general syntax of the INSERT command looks like the following. Note that the list of values has to be in the same order as the columns of the table.
INSERT INTO table_name VALUES (list_of_values_for_new_row);
In Example 2-46, you will use the INSERT function to add a new row to the employees table.
Example 2-46 Using the INSERT Statement When All Information Is Available
INSERT INTO employees VALUES (10, 'George', 'Gordon', 'GGORDON', '650.506.2222', '01-JAN-07', 'SA_REP', 9000, .1, 148, 80);
The results of the query appear.
1 row created.
Your result shows that the new row has been successfully added to the employees table.
When all of the information is not available at the time a new record is added to the database, Example 2-47 shows how you can insert values only into the specified known columns of the table and then set the remaining columns to NULL.
Note that if the columns that are set to NULL are specified with a NOT NULL constraint, this would generate an error.
Example 2-47 Using the INSERT Statement When Some Information Is Not Available
INSERT INTO employees VALUES (20, 'John', 'Keats', 'JKEATS', '650.506.3333', '01-JAN-07', 'SA_REP', NULL, .1, 148, 80);
The results of the query appear.
1 row created.
Your result shows that the new row has been successfully added to the employees table.
See Also:
Oracle Database SQL Language Reference for information about INSERT
When you use the UPDATE statement to update data in a row of a table, the new data must be valid for the data type and size of each column of the table.
The general syntax of the UPDATE command looks like the following. Note that the columns that are altered must be identified, and the matching conditions must be met.
UPDATE table_name SET column_name = value; WHERE condition;
To update information in a row that is missing data, the missing data column should be specified. In Example 2-48, you will update the salary column for a previously inserted record.
salary
Example 2-48 Using the UPDATE Statement to Add Missing Data
UPDATE employees SET salary = 8500 WHERE last_name = 'Keats';
The results of the query appear.
1 row updated.
Your result shows that the matching row has been updated.
Example 2-49 shows how you can use the UPDATE statement to update multiple rows.
Example 2-49 Using the UPDATE Statement to Change Data
UPDATE employees SET commission_pct=commission_pct + 0.05 WHERE department_id = 80;
The results of the query appear.
36 rows updated.
Your result shows that the specified rows are updated.
See Also:
Oracle Database SQL Language Reference for information about UPDATE
Using the DELETE statement, you can delete specific rows in a table. If you want to delete all the rows in the table, the empty table still exists. If you want to remove the entire table from the database, use the DROP TABLE statement.
Note that if you accidentally delete rows, you can restore the rows with the ROLLBACK statement.
Example 2-50 shows how you can use the DELETE statement to remove the data you added previously.
Note the use of the WHERE clause; without it, all the rows are deleted.
Example 2-50 Using the DELETE Statement
DELETE FROM employees WHERE hire_date = '1-Jan-2007';
The results of the query appear.
2 rows deleted.
Your result shows that the specified rows are deleted.
See Also:
Oracle Database SQL Language Reference for information about DELETE
Oracle Database SQL Language Reference for information about DROP TABLE
Oracle Database SQL Language Reference for information about ROLLBACK
Many applications model business processes that require that several different operations be performed together, or not at all. For example, if a manager left the company, a row would be inserted into the job_history table to show when that person left, and all the employees that report to that manager must be re-assigned within the employees table. This sequence of operations must be treated as a single unit, or a transaction.
The following transaction control statements manage the changes made by DML statements and group them into transactions.
The COMMIT statement ends the current transaction and makes all changes performed in the transaction permanent. COMMIT also erases all savepoints in the transaction, and releases transaction locks.
The ROLLBACK statement reverses the work done in the current transaction; it causes all data changes since the last COMMIT or ROLLBACK to be discarded. The state of the data is then "rolled back" to the state it had prior to the requested changes.
The SAVEPOINT statement identifies a point in a transaction to which you can later roll back.
Oracle recommends that you explicitly end transactions using either a COMMIT or a ROLLBACK statement. If you do not explicitly commit the transaction and the program terminates abnormally, then Oracle Database automatically rolls back the last uncommitted transaction.
An explicit COMMIT statement ends your transaction, and makes all the changes in the database permanent. Until you commit a transaction, you can see all of the changes made by you to the database, but these changes are not final or visible to other users of the database instance. Once you commit a transaction, all changes become visible to other users and their statements that execute after your transaction.
You can undo any changes made prior to an explicit COMMIT by a ROLLBACK statement.
Example 2-51 shows how to use the COMMIT statement after adding a new row to the regions table.
Example 2-51 Using the COMMIT Statement
INSERT INTO regions VALUES (5, 'Africa'); COMMIT;
The results of the query and COMMIT statement appear.
Commit complete.
If you manually check the contents of the regions table, you will see that it now has the new row.
See Also:
The ROLLBACK statement rolls back all of the transactions you have made since the last COMMIT statement. If you do not have a preceding COMMIT statement in your program, it rolls back all operations.
Example 2-52 and Example 2-53 show how to use the ROLLBACK statement to undo changes to the regions table.
Example 2-52 Changing the REGIONS Table
UPDATE regions SET region_name = 'Just Middle East' WHERE region_name = 'Middle East and Africa';
The results of the query appear.
1 row updated.
Manually check the contents of the regions table.
You will see that it now has the updated region_name value. 
Description of the illustration transaction_4.gif
Example 2-53 Performing a ROLLBACK on the Change to the REGIONS Table
ROLLBACK;
Manually check the contents of the regions table by clicking the Refresh icon.You will see that the region_name value is changed back to the original value.
See Also:
You can use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back. Because you can use as many savepoints as your application requires, you can implement greater transaction control in your application.
In Example 2-54, you will use the ROLLBACK statement after adding a new row to the regions table.
Example 2-54 Using the SAVEPOINT Statement
UPDATE regions SET region_name = 'Middle East' WHERE region_name = 'Middle East and Africa'; SAVEPOINT reg_rename; UPDATE countries SET region_id = 5 WHERE country_id = 'ZM'; SAVEPOINT zambia; UPDATE countries SET region_id = 5 WHERE country_id = 'NG'; SAVEPOINT nigeria; UPDATE countries SET region_id = 5 WHERE country_id = 'ZW'; SAVEPOINT zimbabwe; UPDATE countries SET region_id = 5 WHERE country_id = 'EG'; SAVEPOINT egypt; ROLLBACK TO SAVEPOINT nigeria; COMMIT;
The results for each UPDATE and SAVEPOINT statement follow.
1 row updated. Savepoint created.
Manually check the contents of the regions table. You may need to click the Refresh icon. You will see that it now has the updated region_name value. 
Description of the illustration transaction_8.gif
Next, manually check the contents of the countries table. You may need to click the Refresh icon. You will see that it now has the updated region_name values for 'Zambia' and 'Nigeria', but not for 'Zimbabwe' and 'Egypt'. 
Description of the illustration transaction_9.gif
You can see that the change in data was reversed by the ROLLBACK to the savepoint nigeria.
See Also:
