Oracle

Oracle interMedia Image Quick Start

2010-03-02T03:15:00.000-08:00

Oracle interMedia Image Quick Start

Object Interface Introduction

Oracle interMedia (“interMedia”) is a feature that enables Oracle Database to store, manage, and retrieve images, audio, video, or other heterogeneous media data in an integrated fashion with other enterprise information. Oracle interMedia extends Oracle Database reliability, availability, and data management to multimedia content in traditional, Internet, electronic commerce, and media-rich applications.
This article provides simple PL/SQL examples that upload, store, manipulate, and export image data inside a database using interMedia. Some common pitfalls are also highlighted. We assume only Oracle Database release 9i or later with Oracle interMedia installed (the default configuration provided by Oracle Universal Installer).
NOTE: Access to an administrative account is required in order to grant the necessary file system privileges. In the following examples, you should change the command connect / as sysdba to the appropriate connect username/password as sysdba for your site. The following examples also connect to the database using connect scott/tiger, which you should change to an actual user name and password on your system. You should also modify the definition of IMGDIR to point at the directory where you have downloaded the three
sample image files goats.gif, flowers.jpg, and dummy.dcm.

Overview of ORDImage
Oracle can store image data in either a Binary Large OBject (“BLOB”) column or an ORDImage object column. Some of the advantages of using the ORDImage object are:
1. Tight integration with Oracle’s development tools such as Oracle JDeveloper, Oracle Content
Management SDK, Oracle Application Server Portal, and so on.
2. Information such as height and width is automatically determined and stored with the image data.
An example of an ORDImage object in a database table is illustrated in the following diagram.

number ORDImage ORDImage
height (integer)
width (integer)
contentLength (integer)
fileFormat (varchar2)
contentFormat (varchar2)
compressionFormat(varchar2)
mimeType (varchar2)
source (ORDSource)

Creating a Table with an ORDImage Column
First, we create a simple table with two columns: a numeric identifier (id) and an ORDSYS.ORDImage object (image). Note that all interMedia objects and procedures are defined in the ORDSYS schema.
connect scott/tiger
create table image_table (id number primary key, image ordsys.ordimage);
Importing Images
This section shows how to bring images from the file system into the newly created table named image_table.
1. Create a directory object within the database that points to the file system directory that contains the sample image files. This is the directory where you saved the image files included with this quickstart. connect / as sysdba
create or replace directory imagedir as '/home/alamb/quickstart/';
-- For windows: create or replace directory imagedir as 'c:\quickstart';
grant read on directory imagedir to scott;
2. Create a PL/SQL procedure image_import() that inserts a new row into image_table and then imports the image data in filename into the newly created ORDImage object.
create or replace procedure image_import(dest_id number, filename varchar2) is img ordsys.ordimage;
ctx raw(64) := null;
begin
delete from image_table where id = dest_id;
insert into image_table (id, image)
values (dest_id, ordsys.ordimage.init())
returning image into img;
img.importFrom(ctx, 'file', 'IMAGEDIR', filename);
update image_table set image=img where id=dest_id;
end;
/
3. Call the newly created procedure to import 2 sample image files.
call image_import(1,’flowers.jpg’);
call image_import(2,’goats.gif’);
NOTE: The directory object is named IMAGEDIR (in uppercase letters) even if it was created with upper or lower case letters. Thus the command img.importFrom(ctx, 'file', 'imagedir', filename); will not work and the following error is returned.
ORA-22285: non-existent directory or file for FILEOPEN operation error.
NOTE: If the image you are importing is not one of interMedia’s supported formats (for example JPEG2000) the following error is returned. ORA-29400: data cartridge error IMG-00705: unsupported or corrupted input format
For information on the use of interMedia to manage non-natively supported image formats, see the appendix.

Selecting and Viewing Image Properties
Once image_table has been populated, you can access image metadata using SQL queries. In the following example, we demonstrate how to select some information from the imported images:
1. Height and width.
2. File format and compression format.
3. Content format (RGB, grayscale, and so on) and content length (bytes of image data).
connect scott/tiger
select id,
t.image.getheight(),
t.image.getwidth()
from image_table t;
select id,
t.image.getfileformat(),
t.image.getcompressionformat()
from image_table t;
select id,
t.image.getcontentformat(),
t.image.getcontentlength()
from image_table t;
The resulting output looks like the following (the formatting commands are in the included script files).
id height width
---------- ---------- ----------
1 600 800
2 375 500
id fileformat compression
---------- ------------------------------ ------------------------------
1 JFIF JPEG
2 GIFF GIFLZW
id contentformat length
---------- ------------------------------ ----------
1 24BITRGB 66580
2 8BITLUTRGBT 189337
Creating Thumbnails and Changing Formats
We next illustrate some image processing operations that can be invoked within the database. To generate a new ORDImage object from an existing one, the programmer describes the desired properties of the new image. For example, the following description generates a JPEG thumbnail image of size 75x100 pixels:
‘fileformat=jfif fixedscale=75 100’.
NOTE: Some three-letter image file extensions and the corresponding interMedia fileformat are as follows.

Extension fileformat
.jpg JFIF (9i, 10g), JPEG (10g)
.gif GIFF(9i, 10g), GIF (10g)
.tif, .tiff TIFF
.png PNGF
The following example defines image_processCopy() that adds a new row to image_table with identifier dest_id and generates an ORDImage in the new row by processCopy’ing of the ORDImage in the source row.
connect scott/tiger
create or replace procedure image_processCopy(source_id number, dest_id number, verb varchar2) is
imgSrc ordsys.ordimage;
imgDst ordsys.ordimage;
begin
delete from image_table where id = dest_id;
insert into image_table (id, image)
values (dest_id, ordsys.ordimage.init());
select image into imgSrc from image_table where id = source_id;
select image into imgDst from image_table where id = dest_id for update;
imgSrc.processCopy(verb, imgDst);
update image_table set image = imgDst where id = dest_id;
end;
/
-- Scale flowers.jpg to 10% into row with id=3
call image_processcopy(1,3,'scale=.1');
-- convert goats.gif to grayscale jpeg thumbnail into row with id=4
call image_processcopy(2,4,'fileformat=jfif contentformat=8bitgray maxscale=100 100');
-- look at our handiwork
column t.image.getfileformat() format A20;
select id, t.image.getWidth(), t.image.getHeight(), t.image.getFileFormat()
from image_table t;
The preceding example generates the following output.
ID T.IMAGE.GETWIDTH() T.IMAGE.GETHEIGHT() T.IMAGE.GETFILEFORMA
---------- ------------------ ------------------- --------------------
1 800 600 JFIF
2 500 375 GIFF
3 80 60 JFIF
4 100 75 JFIF
NOTE: The following error might be returned from ORDImage.processCopy().
ORA-29400: data cartridge error
IMG-00703: unable to read image data
ORA-28575: unable to open RPC connection to external procedure agent
In Oracle Database release 9i, JPEG (and some other less common formats) encoding and decoding requires the
external procedure agent (extproc). To fix the preceding error, the Oracle Listener needs to be configured to use
extproc. See technical note 198099.1, Configuration of the External Procedure Call for interMedia at
http://metalink.oracle.com for detailed instructions on setting up extproc. Oracle Database release 10g does not
require extproc for JPEG encoding and decoding.
If you do not want to change your Oracle Net configuration, try changing the file format to pngf as follows.
-- convert goats.gif to grayscale png thumnbail
call image_processcopy(2,5,’fileformat=pngf contentformat=8bitgray maxscale=100 100’);

Exporting Images with ORDImage.export()
Exporting image data from the database with interMedia’s export method requires that the database write to the file system. Writing to the file system requires granting JAVA permissions to your user (scott in the examples) and to the ORDSYS* schema as shown in the following example.
connect / as sysdba
create or replace directory imagedir as '/home/alamb/quickstart';
-- For windows:
--create or replace directory imagedir as 'c:\quickstart';
grant read on directory imagedir to scott;
call dbms_java.grant_permission('SCOTT','java.io.FilePermission',
'/home/alamb/quickstart/*','WRITE');
call dbms_java.grant_permission('ORDSYS','java.io.FilePermission',
'/home/alamb/quickstart/*','WRITE');
-- For windows:
--call dbms_java.grant_permission('SCOTT','java.io.FilePermission','c:\quickstart\*','WRITE');
--call dbms_java.grant_permission('ORDSYS','java.io.FilePermission','c:\quickstart\*','WRITE');
connect scott/tiger
-- Writes the image data from ORDImage with id= in image_table
-- to the file named in the IMAGEDIR directory
create or replace procedure image_export (source_id number, filename varchar2) as
imgSrc ordsys.ordimage;
ctx raw(64) := null;
begin
select image into imgSrc from image_table where id = source_id;
imgSrc.export(ctx, 'FILE', 'IMAGEDIR', filename);
end;
/
call image_export(3, 'flowers_thumbnail.jpg');
call image_export(4, 'goats_grayscale.jpg');
*NOTE: For Oracle Database releases 9.2.0.1, 9.2.0.2, and 9.2.0.3 you must change ORDSYS in the preceding export example to ORDPLUGINS. Cleaning Up To restore your database to its original state, you need to remove all of the objects that were created in this quickstart as shown in the following example.
connect / as sysdba
drop directory imagedir;
call dbms_java.revoke_permission('SCOTT','java.io.FilePermission',
'/home/alamb/quickstart/*','WRITE');
call dbms_java.revoke_permission('ORDSYS','java.io.FilePermission',
'/home/alamb/quickstart/*','WRITE');
-- For windows:
--call dbms_java.revoke_permission('SCOTT','java.io.FilePermission', 'c:\quickstart\*','WRITE');
-- call dbms_java.revoke_permission('ORDSYS','java.io.FilePermission','c:\quickstart\*','WRITE');
connect scott/tiger
drop procedure image_import;
drop procedure image_processcopy;
drop procedure image_export;
drop table image_table;

Conclusion
Using the provided ORDImage object type, we have shown how to import images into the database, write SQL queries based on image metadata (width, height, and so on), perform basic image processing, and export images to the file system.
Oracle interMedia provides more functionality than is covered in this quickstart. Refer to the following documentation for more information: Oracle interMedia User’s Guide and Reference, Release 9.0.1, Oracle interMedia Reference, 10g Release 1 (10.1), and Oracle interMedia User’s Guide, 10g Release 1 (10.1).
Additional examples and articles are available on the interMedia web page on the Oracle Technology Network at http://www.oracle.com/technology/products/intermedia/index.html.
APPENDIX: Using interMedia with Non-Natively Supported Image Formats
For image formats that interMedia understands, image properties such as height and width are automatically set when ORDImage.importFrom() is called. For image formats such as JPEG2000 that are not natively supported by interMedia, the metadata fields must be populated by the user. Metadata can be set by updating the fields of an ORDImage object either via a SQL UPDATE statement or with an object field assignment. The following example shows the procedure image_import_other() that imports an image in an unsupported format and sets the ORDImage fields to the values supplied in the arguments.
connect scott/tiger
create or replace procedure image_import_other
(dest_id number, filename varchar2, imgFileFormat varchar2,
imgHeight number, imgWidth number, imgMimeType varchar2) is
img ordsys.ordimage;
ctx raw(64) := null;
begin
delete from image_table where id = dest_id;
insert into image_table (id, image)
values (dest_id, ordsys.ordimage.init());
-- import the actual image data into the database and set the
-- other ORDImage fields manually. Note that setting the filetype
-- to 'OTHER:filetype' causes no auto-setting of fields
select image into img from image_table where id=dest_id for update;
img.fileFormat := 'OTHER:' imgFileFormat;
img.height := imgHeight;
img.width := imgWidth;
img.mimeType := imgMimeType;
img.importFrom(ctx, 'file', 'IMAGEDIR', filename);
img.contentLength := dbms_lob.getlength(img.source.localdata);
update image_table set image=img where id=dest_id;
end;
/
call image_import_other(6,'dummy.dcm', 'DICOM', 100, 200, 'application/dicom');
-- view the properties we just created (nicely formatted)
column "mimetype" format A20;
select id "id",
t.image.getWidth() "width",
t.image.getHeight() "height",
t.image.getMimeType() "mimetype",
t.image.getContentLength() "length"
from image_table t;
This example produces output similar to the following.
id width height mimetype length
---------- ---------- ---------- -------------------- ----------
1 800 600 image/jpeg 66580
2 500 375 image/gif 189337
3 80 60 image/jpeg 1918
4 100 75 image/jpeg 2156
5 100 75 image/png 5624
6 200 100 application/dicom 183
To cleanup this example, run the following.
connect scott/tiger
drop procedure image_import_other;

Creating Groups: Part 1

2010-01-31T22:21:00.002-08:00

Dividing Data into Groups

You might need to divide a table of information into groups to produce meaningful results when using group functions. For example, you might want to find the average salary for each department or the oldest hire in each job category.

GROUP BY Clause

You use the GROUP BY clause to organize rows in a table into groups. You can then use the group functions to return information for each group. For example, you can group data in the EMPLOYEES table by department number and then return the average salary for each department.

Using the Grouping Functions

2010-01-31T22:21:00.001-08:00

Help Sheila find the following information:

* Number of rows in the EMPLOYEES table
* Number of employees who earn a commission
* Total salary budget allocation
* Average salary

Group Functions: Part 2

2010-01-31T22:19:00.004-08:00

Syntax

To specify a group function, you write the name of the function followed by an argument in parentheses. The argument can be a column name, an expression, or a constant.

COUNT Function

The COUNT function returns the number of non-null values in a column. If you count the values of a primary key column, you will find the number of rows in a table because a primary key column cannot contain nulls.

Group Functions: Part 1

2010-01-31T22:19:00.003-08:00

Grouping Functions

Sheila has learned that there are several group functions that are available for use. All are ANSI-standard SQL functions.

* You can find the average of a group of values by using the AVG function.
* You can find the total of a group of values by using the SUM function.
* MAX finds a largest value. MIN returns a smallest value.
* STDDEV returns a standard deviation. VARIANCE returns the statistical variance.
* AVG , SUM , STDDEV , and VARIANCE can be used with numerical data only. You can use MAX and MIN on character, numeric, and date data types.
* You can use the COUNT function to return the number of values in a column.

Sheila will use the SQL GROUP functions in her SELECT statements to:

* Find the number of rows in a table
* Add the salaries
* Find the average, maximum, and minimum salaries
* Analyze salary information by department

Group Functions: Part 1

2010-01-31T22:19:00.001-08:00

Working with Groups of Data

2010-01-31T22:18:00.003-08:00

Grouping Data

You can use group functions in a SQL statement to display information about groups of rows in the database. A group function performs an operation on a set of data.

Group functions operate on sets of rows and return one result per group. Group functions are also called multiple-row functions (in contrast with single-row functions, which return one result for each row).

You can use a group function to operate on a set of data. The set may be an entire table or only part of the table.

In this section, Sheila creates reports that summarize data.

Using the NVL Function

2010-01-31T22:18:00.001-08:00

Sheila needs to produce a report to find employees who do not have a manager.

* Display the value of “No Manager” where the manager ID column is null.

Using the NVL Function

2010-01-31T22:17:00.001-08:00

The Null Value

An empty field is said to contain NULL. A NULL is defined as a value that is unavailable, unassigned, unknown, or inapplicable. A NULL is not the same as a zero or a space. Zero is a number, and a space is a character. The COMMISSION_PCT column in the EMPLOYEES table contains NULLs.

Columns of any data type can contain NULLs unless the column was defined as NOT NULL when the table was created. Also, primary key columns cannot have NULLs.
NVL Function

You can use the NVL function to convert a NULL to another value.

NVL Syntax

NVL ( expr1, expr2)

* expr1 is the source value or expression.
* expr2 is the target or resulting value to substitute.

Formatting Dates

2010-01-31T22:16:00.001-08:00

Sheila wants to format her dates differently. She wants to produce a report that shows the employee's start date in the format of Month + Day + Year: the date 06-OCT-05 should appear as "October 6th, 2005."

For the birthdates in the DEPENDENTS table, she wants only the year to appear.

Formatting Dates

2010-01-31T22:15:00.000-08:00

TO_CHAR Function

You can use the TO_CHAR function to customize the display of dates that are returned from a SQL SELECT statement.

The TO_CHAR function has two parameters. The first parameter is the date that you want to convert. The second parameter is the format model, which specifies the way that you want the date to look when displayed.

Many format models are available. A few are shown in the table below:

Using Date Functions

2010-01-31T22:14:00.000-08:00

Sheila needs a report that displays the ages of the employee's dependents. She also wants to see how many years an employee has been with the company. Each employee is rewarded with a gift when longevity with the company reaches 10 years.

She needs to use the SYSDATE and MONTHS_BETWEEN functions to determine this information

Date Functions: Part 2

2010-01-31T22:13:00.000-08:00

Common Date Functions

Date functions enhance your ability to calculate information about dates. Common DATE functions are listed in the table below. These functions operate on DATE data types and return date, date-time, or number values.

SYSDATE Function

SYSDATE is a special date function that returns the current date and time. You can use the function in any SQL statement.

SYSDATE is a function that contains no arguments. You can use SYSDATE just as you use a column name. The SYSDATE is displayed for every row in the table used.

Date Functions: Part 1

2010-01-31T22:12:00.000-08:00

Characteristics

Oracle XE stores dates in an internal format representing the century, year, month, day, hours, minutes, and seconds. For example, if today is October 6, 2005, at 07:10 in the morning, the current date is stored internally as October 6, 2005, 07:10:06.

The default display and input format for any date in XE is DD-MON-YY. DD stands for the day of the month, MON stands for the first three letters of the month name, and YY stands for the year (which also includes the century). Valid Oracle dates are between January 1, 4712 B.C., and December 31, A.D. 9999.

Using Numeric Functions

2010-01-31T22:11:00.000-08:00

Sheila wants to determine salaries rounded to the nearest thousandth. She then wants to compare those values to the values of salaries truncated to the thousandth position. She has looked up the syntax for the numeric functions and created a small sheet about them:

Help Sheila find this information.

Number Functions

2010-01-31T22:10:00.002-08:00

Characteristics

You use number functions to accept numeric input. The number functions return numeric values.

Function Names

The three most commonly used number functions are the following:

* ROUND: Rounds a column, an expression, or a value to n decimal places
* MOD: Returns the remainder of x divided by y
* TRUNC: Truncates a column, an expression, or a value to n decimal places

Using Character Functions

2010-01-31T22:10:00.001-08:00

Sheila needs to add more functionality to her reports. Her employee report needs to display the first name, followed by a space, followed by the last name, all in proper case. In addition, she needs the e-mail data to appear in lowercase.

She has looked up the syntax for functions and created a small sheet on them:

Character Functions: Part 2

2010-01-31T22:07:00.002-08:00

Case-Manipulation Functions

You use case-manipulation functions to change the case of character strings. The types of case-manipulation functions are LOWER , UPPER , and INITCAP .

Character-Manipulation Functions

You use character-manipulation functions to manipulate character strings. The types of character-manipulation functions are CONCAT , SUBSTR , INSTR , LPAD , RPAD , TRIM , and REPLACE .

Character Functions: Part 1

2010-01-31T22:07:00.001-08:00

Characteristics

Single-row character functions accept character data as input and return either character or numeric values. Character functions that return character values return values of the same data type as their input argument. Character functions that return number values can take any character data type as their input argument.

Types

There are two types of single-row character functions:

* Case-manipulation functions
* Character-manipulation functions

Using Functions to Customize Reports

2010-01-31T22:06:00.002-08:00

A SQL function is a program that performs an operation on data. SQL functions provide a powerful way to perform operations when retrieving data from a table. For example, you may need to display employee names in uppercase in a report. Or you may need to display employees' hire dates with the name of the month spelled out.

Many functions work on specific data types. Each value manipulated by Oracle XE has a data type. Data types provide a way to define the behavior of data. When you create a table, you must specify a data type for each of its columns.

The three main types of data that functions work on are:

*
Character
*
Number
*
Date

Joining Multiple Tables

2010-01-31T22:06:00.001-08:00

Help Sheila build the following reports:

* A listing of each employee's first name, last name, dependent names, and the name of the department where the employee works
* A listing of each employee and the name of that employee's manager

Joining Multiple Tables

2010-01-31T22:04:00.002-08:00

Joining Multiple Tables

A three-way join is a join of three tables. You can join as many tables as needed to retrieve information. For example, you might want to find employees, their depedents' names, and the department names for those emloyees. This requires accessing three tables:- EMPLOYEES, DEPENDENTS, and DEPARTMENTS.

Syntax

In the FROM clause, you identify the tables you want to join.

FROM table1
JOIN table2 ON conditon_x
JOIN table3 ON condition_y

Example

SELECT e.last_name, d.first_name, w.department_name
FROM employees e
JOIN dependents d
ON d.relative_id = e.employee_id
JOIN departments w
ON w.department_id = e.department_id

Self-Joining Tables

The ON clause can also be used to join columns that have different names (in the same table or in a different table). For example, you can perform a self-join of the EMPLOYEES table based on the EMPLOYEE_ID and MANAGER_ID columns.

Applying Additional Conditions to a Join

2010-01-31T22:04:00.001-08:00

Help Sheila build a report to find the hire date and start date of all employees who earn more than 10,000.00. She can find the hire date in the EMPLOYEES table and the start date in the JOB_HISTORY table.

Applying Additional Conditions to a Join

2010-01-31T22:02:00.002-08:00

Additional Conditions

You can apply additional conditions to the join. For example, you may want to join the EMPLOYEES and DEPARTMENTS tables and, in addition, display 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.

Using the AND Clause

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)
AND e.manager_id = 149

Using the WHERE clause

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

Aliases

In the examples shown, aliases are used to identify the table names. In the FROM clause, an abbreviation is provided after the table name. This is called an alias. After the alias is set up in the FROM clause, you can refer to it throughout the statement.

Joining Tables and Identifying Columns

2010-01-31T22:02:00.001-08:00

Help Sheila build a report to find the hire dates and start dates of all employees. She can find the hire dates in the EMPLOYEES table and the start dates in the JOB_HISTORY table.

Joining Tables and Identifying Columns

2010-01-31T22:01:00.000-08:00

ON Clause

You use the ON clause to specify the join condition when joining two tables or joining a table to itself. This enables you to separate the join condition from any search or filter conditions in the WHERE clause. The column names need not match between the tables; however, the data types must match.

Syntax

SELECT [DISTINCT] * | column [alias], ...
FROM table1 JOIN table2
ON col_name_1 = col_name_2

For example, Shelia might need to evaluate the hire dates and start dates of all employees. She can find the hire dates in the EMPLOYEES table and the start dates in the JOB_HISTORY table. These two columns are named differently. The ON clause is as follows:

FROM employees JOIN job_history
ON employees.hire_date = job_history.start_date

Simple Joins

2010-01-31T22:00:00.000-08:00

Sheila needs to build a report that displays the city locations of the departments.

Watch Sheila create these reports.

Simple Joins

2010-01-31T21:58:00.004-08:00

Natural Joins

A natural join enables you to display data from two tables when a value in one column of one table corresponds directly to a value in another column in the second table.

In a natural join, the two tables include one or more columns that have the same name and data types. A natural join retrieves all rows from the two tables that have equal values in all matched columns. Frequently, this type of join involves primary key and foreign key columns.

Syntax

SELECT [DISTINCT] * | column [alias], ...
FROM table NATURAL JOIN ;

Joining Two Tables with a USING Clause

The USING clause enables you to specify the columns to be used for a join between two tables. The column names must be the same for both tables and must have compatible data types. Use the USING clause if your tables contain more than one column whose names match to explicitly identify the name of the columns that you want to join.

Syntax

SELECT [DISTINCT] * | column [alias], ...
FROM table1 JOIN table2
USING common_col_name;

Joining Tables

2010-01-31T21:58:00.003-08:00

Sometimes you need to display data from more than one table. To do this, you use SELECT statements. The FROM clause of your query contains the names of the tables from which you are retrieving data. Because information comes from more than one table, this is called a JOIN between the tables involved.

For example, in the EMPLOYEES table, the DEPARTMENT_ID column represents the department number for an employee. In the DEPARTMENTS table, there is a DEPARTMENT_ID column as well as a DEPARTMENT_NAME column. You can join the EMPLOYEES and DEPARTMENTS tables by using the DEPARTMENT_ID column to produce a report that shows the employees' names and department names.

Retrieving Rows

2010-01-31T21:58:00.001-08:00

Sheila needs to build reports that display the following information:

* Employees in a specified department
* Employees who work for a specified manager
* Employees who earn less than 8,000.00

Watch Sheila create these reports.

Retrieving Rows

2010-01-31T21:57:00.001-08:00

You can use the selection capability of SQL to choose the rows that you want to retrieve from a table. You can specify various criteria to select the rows that you want to see.

You can restrict the number of rows that are retrieved from the database by using a WHERE clause in a SQL statement. By inserting a WHERE clause into a SQL statement, you can specify a condition that must be met, and only the rows that meet the condition are returned.

When using a WHERE clause:

* The WHERE clause directly follows the FROM clause in the SQL statement syntax
* The WHERE clause consists of the WHERE keyword and a condition or conditions.
* The condition in a WHERE clause specifies a comparison of values that limits the rows that are returned by a query

Retrieving Columns

2010-01-31T21:56:00.002-08:00

Sheila needs to build the following report:

It is time for benefits review. To gather information on each employee, HR needs a report with the employees' first names, last names, IDs, salaries, and e-mail addresses. The data needs to be sorted by employee salary.

Watch Sheila build this report...

Retrieving Columns

2010-01-31T21:56:00.001-08:00

You can use the projection capability of SQL to choose the columns in a table that you want to retrieve. You can retrieve selected columns or all columns from a table.

Writing a Basic Query

2010-01-31T21:55:00.002-08:00

Sheila wants to see the data in the HR tables. She would like to view the data in the EMPLOYEES and DEPARTMENTS tables.

Watch Sheila retrieve data from the DEPARTMENTS and EMPLOYEES tables:

Sorting Data

Now that Sheila can see the data, she wants to sort it. She would like to see the data in the EMPLOYEES table sorted by last names.

Writing a Basic Query

2010-01-31T21:55:00.001-08:00

Writing SELECT Statements

When Sheila writes a basic query, two mandatory clauses in the SELECT statement syntax are required: a SELECT clause and a FROM clause.

The SELECT statement syntax is displayed. The SELECT clause specifies the columns that you want to display. The FROM clause specifies the tables that contain those columns.

SELECT [DISTINCT] * | column [alias], ...
FROM table;

Building Queries

2010-01-31T21:54:00.001-08:00

Sheila's manager has asked her to write several reports for human resources.

Sheila starts by accessing the interface where she can build her queries. She can either:

* Use the SQL Workshop tool to type her SELECT statements
* Use the Query Builder tool to build the query graphically

Watch Sheila navigate to the SQL Workshop and Query Builder tools.

Building Queries: Part 2

2010-01-31T21:53:00.002-08:00

Sheila will use the SQL SELECT statement to extract data from a database. With the SELECT statement, you can choose to see all or some of the data in a table.
SELECT Statement Types

SELECT statements have three capabilities:
* Selection: Identifying rows
* Projection: Identifying columns
* Join: Retrieving data from multiple tables

Building Queries

2010-01-31T21:53:00.001-08:00

All operations on information in an Oracle database are performed by using SQL statements. A SQL statement is a string of SQL text. The SQL SELECT statement is the command that retrieves data from the XE tables.

Sheila will use the SQL SELECT statement to:

* Write queries
* Create SELECT statements that restrict rows
* Sort output
* Display data from many tables
* Customize output with functions embedded in the SELECT statement

Accessing Data

2010-01-31T21:52:00.003-08:00

In this module, you learn how to retrieve data from tables. The SQL SELECT statement is used to access and report data back from the XE tables. This is known as "querying" the data. In XE, you can either write SELECT statements using the SQL Workshop tool, or you can use the Query Builder tool to build queries with a GUI interface.

You will watch Sheila write several reports for the Human Resources department. Sheila will use both the SQL SELECT statement in the SQL Workshop tool and the Query Builder tool to access data in the Human Resources tables.

Removing a Copy of a Table

2010-01-31T21:52:00.001-08:00

Sheila realizes that she does not need copies of the LOCATIONS and EMPLOYEES tables. She decides to remove them.

Watch Sheila remove the EMPLOYEES_COPY table by using the Object Browser.

Removing Tables

2010-01-31T21:51:00.003-08:00

You can discard a table if you no longer find it useful. The DROP TABLE statement removes the definition of the table from the database.

Deleting all of the records in a table is different from DROP TABLE. After deleting all the records, the column and constraint information still remains. But DROP TABLE results in the removal of the table definition along with the rows.

In Oracle Database XE, you can remove a table in either of the following ways:

* Select Object Browser > Browse > Tables. Click the table name, and then click DROP.
* Select SQL > SQL commands > Enter command. Type the SQL statement, and then click Run.

Implementing Business Rules

2010-01-31T21:51:00.001-08:00

Sheila's manager wants to ensure that data integrity is handled on the new tables and columns that she has created. The manager has asked Sheila to add constraints with the following rules:

* In the DEPENDENTS table, have a primary key constraint on the Id column
* Allow the Gender column to hold the value of M or F
* Tie the DEPENDENTS table to the EMPLOYEES table so that the RelativeId column must hold a valid value from the EMPLOYEES Id column
* Disable the constraint between the DEPENDENTS and EMPLOYEES tables

Watch Sheila manage the constraints on the DEPENDENTS table by using the Object Browser.

Creating Constraints

2010-01-31T21:50:00.000-08:00

Constraints can be enforced at two levels:

* Column level
* Table level

A constraint can be created with either of the following statements:

* CREATE TABLE
* ALTER TABLE

With the ALTER TABLE statement, you can disable or enable the imposed constraint without dropping it or re-creating it:

* Disable a constraint by using the DISABLE clause.
* Enable a constraint by using the ENABLE clause.

Identifying Constraints

2010-01-31T21:48:00.002-08:00

# PRIMARY KEY

The PRIMARY KEY constraint is a column or a set of columns that uniquely identifies each row in a table. This constraint enforces uniqueness of the column or column combination. It ensures that no column that is part of the primary key can contain a null value. A null value is a value that does not exist.

For example, in the DEPENDENTS table, the column ID is the primary key.This column will not allow either a duplicate value or a null value.

# FOREIGN KEY

The FOREIGN KEY constraint designates a column or a combination of columns as a foreign key. It establishes a relationship between a primary key or a unique key in the same table or different table. A foreign key enforces that the value within the column matches the value in the relationship column.

For example, the RelativeId column in the DEPENDENTS table refers to the EMPLOYEES table. You cannot delete a record in the EMPLOYEES table whose RelativeId is used in the DEPENDENTS table. Also, with a non-existing RelativeId in the EMPLOYEES table, you cannot insert a record into the DEPENDENTS table.

# CHECK

The CHECK constraint enforces integrity by restricting the values to be inserted in a column. It defines a condition that each row must sastisfy. You can define multiple check constraints on a single column. Also, you can apply a single check constraint to multiple columns.

For example, when you impose the CHECK constraint on the Gender column in the DEPENDENTS table, you can specify that the only valid values are either M or F.

# UNIQUE

The UNIQUE constraint requires that no two rows of a table can have duplicate values in a specified column or a set of columns. A table can have more than one unique key. If the UNIQUE constraint comprises more than one column, then the group of columns is called a coomposite key.

For example, you can impose the UNIQUE constraint on the Email column of the EMPLOYEES table. You ensure that each employee has a unique email ID.

Managing Constraints

2010-01-31T21:48:00.001-08:00

What Are Constraints?

Data integrity ensures the consistency and correctness of data stored in a database. Such integrity can be enforced by incorporating business rules. Constraints are the rules that are enforced on data stored in a table.

Why Should I Use Constraints?

You can use constraints to do the following:

* Enforce rules on the data in a table whenever a row is updated, inserted, or deleted from that table
* Prevent the deletion of a table if there are dependencies from other tables

Adding a New Column

2010-01-31T21:47:00.002-08:00

Sheila's company wants to keep a record of employee birthdates. To do this, Sheila needs to add a new column, BirthDate, to the EMPLOYEES table. This column will have the DATE data type.

Watch Sheila add a new column to the EMPLOYEES table by using the Object Browser.

Modifying Tables

2010-01-31T21:47:00.001-08:00

You can modify tables using the SQL ALTER TABLE statement. You may need to change the table structure due to any of the following reasons:

* You omitted a column.
* Your column definition needs to be changed.
* You need to remove columns.

The ALTER TABLE statement is used to:

* Add a new column
* Modify an existing column
* Define a default value for the new column
* Drop a column
* Manage constraints

In Oracle Database XE, you can modify tables:

* Using the Object Browser
* Using the SQL Workshop tool

Creating a Copy of a Table

2010-01-31T21:46:00.002-08:00

Sheila wants to copy the EMPLOYEES table so that she can practice without affecting the real data. Watch Sheila create a copy of the table by using the Object Browser.

Sheila wants to have a copy the LOCATIONS table. Watch Sheila create a copy of the table by using the appropriate SQL statement.

Creating Tables Using SQL

2010-01-31T21:46:00.001-08:00

Sheila needs to create the AUDIT_RECORD_TB1 table. This table will contain two columns. The user_value column is of the data type varchar2, and the date_recorded column is of the data type timestamp. Later, Sheila will use this table to record audit information when the salary column in the EMPLOYEES table changes.

Watch Sheila create the table AUDIT_RECORD_TB1 by using the SQL CREATE statement.

Creating Tables by Using the Object Browser

2010-01-31T21:45:00.001-08:00

Sheila needs to create the DEPENDENTS table, which will contain the following columns: Id, FirstName, LastName, BirthDate, Relation, Gender, Benefits, and RelativeId.

In the DEPENDENTS table, no two rows have the same ID. The Gender column holds only one value of M or F. Also, the Benefits column stores large blocks of character data.

Watch Sheila create the DEPENDENTS table using the Object Browser.

Creating Tables

2010-01-31T21:44:00.000-08:00

You create tables with the SQL CREATE TABLE statement. With Oracle Database XE, you have two options for creating tables.

* Use the graphical interface that generates the SQL statement
* Enter the CREATE TABLE statement in the SQL Workshop tool

When creating tables, you must provide:

* Table name
* Column name(s)
* Data types for each column

Guidelines for creating tables:

* Table and column naming rules

Must start with a letter, which is followed by a sequence of letters, numbers, _, #, or $

Must be 1 to 30 characters long

Must not be an Oracle server reserved password

* Most common data types

VARCHAR2
NUMBER
DATE
TIMESTAMP
CHAR

You can also set up constraints on your columns to control the data in them.

Working with Database Objects

2010-01-31T21:40:00.000-08:00

Oracle Database XE provides an organized mechanism for storing, managing, and retrieving information.Tables are the basic storage structure for holding business data. In this module, you learn how to create tables and work with them.

You may want to modify data entered in tables. You may also want to maintain integrity with the data. Sometimes, you may want to remove tables that are no longer useful.

Now that Sheila has the Oracle Database XE software installed and working, and has familiarized herself with the tables in the HR schema, her next task is to build some tables and database objects. In the demonstrations, you watch Sheila create and modify tables, manage constraints, and remove tables.

Relational data base

2010-01-27T12:11:00.000-08:00

What Is a Relational Database?

The concept of a relational database was originally developed back in 1970 by Dr. E.F. Codd. He
laid down the theory of relational databases in his seminal paper entitled “A Relational Model of
Data for Large Shared Data Banks,” published in Communications of the ACM (Association for
Computing Machinery), Vol. 13, No. 6, June 1970.
The basic concepts of a relational database are fairly easy to understand. A relational database
is a collection of related information that has been organized into tables. Each table stores data in
rows; the data is arranged into columns. The tables are stored in database schemas, which are
areas where users may store their own tables. A user may grant permissions to other users so they can access their tables. Most of us are familiar with data being stored in tables

Introducing the Structured Query Language (SQL)

Structured Query Language (SQL) is the standard language designed to access relational
databases. SQL should be pronounced as the letters “S-Q-L.”

NOTE
“S-Q-L” is the correct way to pronounce SQL according to the American National Standards Institute. However, the single word “sequel” is frequently used instead.

SQL is based on the groundbreaking work of Dr. E.F. Codd, with the first implementation of
SQL being developed by IBM in the mid-1970s. IBM was conducting a research project known as System R, and SQL was born from that project. Later, in 1979, a company then known as Relational Software Inc. (known today as Oracle Corporation) released the first commercial version of SQL. SQL is now fully standardized and recognized by the American National Standards Institute. SQL uses a simple syntax that is easy to learn and use. You’ll see some simple examples of its use in this chapter. There are five types of SQL statements, outlined in the following list:

Query statements retrieve rows stored in database tables. You write a query using the

SQL SELECT statement.
Data Manipulation Language (DML) statements modify the contents of tables. There are
three DML statements:
INSERT adds rows to a table.
UPDATE changes rows.
DELETE removes rows.

Data Definition Language (DDL) statements define the data structures, such as tables,
that make up a database. There are five basic types of DDL statements:

CREATE creates a database structure. For example, CREATE TABLE is used to create
a table; another example is CREATE USER, which is used to create a database user.

ALTER modifies a database structure. For example, ALTER TABLE is used to modify
a table.

TRUNCATE deletes all the rows from a table.

What Is a Relational Database

2009-03-21T14:15:00.000-07:00