sqlplus
and then press the key Enter.SQL>
prompt, you can enter and run SQL*Plus commands, SQL statements, PL/SQL statements, and operating system commands.exit
and press the key Enter.SELECT
statement, and exits SQL*Plus. User input is bold.java
.exe
in step 1.java
.exe
(for example, C:Program FilesJavajdk1.6.0_021binjava.exe
). Either type the full path after the prompt or browse to it, and then press the key Enter.Success
.HR
account and connect to Oracle Database Express Edition as the user HR
, who owns the HR
sample schema that the examples and tutorials in this document use.HR
.HR
account and reset its password.HR
schema is installed, the HR
account is locked and its password is expired. You can connect to Oracle Database as the user HR
only if the HR
account is unlocked.ALTER
USER
system privilege.ALTER
USER
system privilege.SQL>
prompt, unlock the HR
account and reset its password:HR
account is unlocked and its password is password
.HR
with the password password
.HR
HR
from SQL*Plus.HR
account.HR
at step 3 and the password for the HR
account at step 4.HR
.HR
connectionHR
from SQL Developer.HR
account.hr_conn
.hr_conn
.)HR
.HR
account.HR
.HR
from SQL*Plus, see 'Connecting to Oracle Database Express Edition as User HR from SQL*Plus' and then return to this section.HR
schema by querying the static data dictionary view USER_OBJECTS
.HR
schema.USER_OBJECTS
HR
DESCRIBE
to view the properties of the columns of the EMPLOYEES
table in the HR
schema and the SQL statement SELECT
to view the data. To view other properties of the table, use static data dictionary views (for example, USER_CONSTRAINTS
, USER_INDEXES
, and USER_TRIGGERS
).EMPLOYEES
table in the HR
schema.EMPLOYEES
table in the HR
schema.DESCRIBE
HR
schema—that is, how to browse the HR
schema.HR
from SQL Developer, see 'Connecting to Oracle Database Express Edition as User HR from SQL Developer' and then return to this tutorial.hr_conn
icon, click the plus sign (+).hr_conn
information expands (see the information that follows 'Click OK' in step 2).hr
.hr
.hr_conn
information expands: The plus sign becomes a minus sign (-), and under the hr_conn
icon, a list of schema object types appears—Tables, Views, Indexes, and so on. (If you click the minus sign, the hr_conn
information collapses: The minus sign becomes a plus sign, and the list disappears.)HR
EMPLOYEES
table in the HR
schema.HR
schema, see 'Tutorial: Viewing HR Schema Objects with SQL Developer' and then return to this tutorial.HR
schema appears.EMPLOYEES
table, click the SQL tab. The SQL statement appears in a pane named EMPLOYEES
. To close this pane, click the x to the right of the name EMPLOYEES
.HR
from SQL Developer. For instructions, see 'Connecting to Oracle Database Express Edition as User HR from SQL Developer'.SELECT
statement, selects data from one or more tables or views.select_list
specifies the columns from which the data is to be selected, and the source_list
specifies the tables or views that have these columns.SQL>
prompt.ORDER
BY
clause. For more information, see 'Sorting Selected Data'.SELECT
statementhr_conn
pane:hr_conn
, select that value from the menu.hr_conn
and two subpanes, Worksheet and Query Builder. In the Worksheet, you can enter a SQL statement.SELECT
statement).hr_conn
tab, click the icon Clear.EMPLOYEES
table.hr_conn
is there, select it. Otherwise, click the icon SQL Worksheet, as in 'Running Queries in SQL Developer'.EMPLOYEES
table.SELECT
*
on tables with columns that store sensitive data, such as passwords or credit card information.FIRST_NAME
, LAST_NAME
, and DEPARTMENT_ID
of the EMPLOYEES
table.hr_conn
is there, select it. Otherwise, click the icon SQL Worksheet, as in 'Running Queries in SQL Developer'.SELECT
statement, including the column alias (c_alias
)WHERE
clause in the SELECT
statement. The condition in the WHERE
clause can be any SQL condition (for information about SQL conditions, see Oracle Database SQL Language Reference).WHERE
clause:WHERE
clause:SELECT
statement, including the WHERE
clauseORDER
BY
clause.LAST_NAME
, in ascending order (the default).ORDER
BY
clause and double-click the name of the column to sort.SELECT
statement, including the ORDER
BY
clauseFIRST_NAME
, LAST_NAME
, and DEPARTMENT_NAME
of every employee. FIRST_NAME
and LAST_NAME
are in the EMPLOYEES
table, and DEPARTMENT_NAME
is in the DEPARTMENTS
table. Both tables have DEPARTMENT_ID
. You can use the query in Example 2-12. Such a query is called a join.FROM
clause, you can use them earlier in the query, as in the following example:select_list
of a query can include SQL expressions, which can include SQL operators and SQL functions. These operators and functions can have table data as operands and arguments. The SQL expressions are evaluated, and their values appear in the results of the query.+
(addition), -
(subtraction), *
(multiplication), and /
(division).LAST_NAME
, SALARY
(monthly pay), and annual pay for each employee in department 90, in descending order of SALARY
.ROUND
to display the daily pay of each employee in department 100, rounded to the nearest cent.TRUNC
to display the daily pay of each employee in department 100, truncated to the nearest dollar.'a'||'b'='ab'
. You can use this operator to combine information from two columns or expressions in the same column of the report, as in the query in Example 2-16.UPPER
, INITCAP
, and LOWER
display their character arguments in uppercase, initial capital, and lowercase, respectively.LAST_NAME
in uppercase, FIRST_NAME
with the first character in uppercase and all others in lowercase, and EMAIL
in lowercase.DATE
, time stamp, and interval values. Each datetime function returns a single value for each row that is evaluated. The datetime functions that SQL supports are listed and described in Oracle Database SQL Language Reference.DATE
and time stamp value, Oracle Database Express Edition stores this information:TIMESTAMP
WITH
TIME
ZONE
or TIMESTAMP
WITH
LOCAL
TIME
ZONE
.DATE
data type, see Oracle Database SQL Language Reference.TIMESTAMP
data type, see Oracle Database SQL Language Reference.EXTRACT
and SYSDATE
functions to show how many years each employee in department 100 has been employed. The SYSDATE
function returns the current date of the system clock as a DATE
value. For more information about the SYSDATE
function, see Oracle Database SQL Language Reference. For information about the EXTRACT
function, see Oracle Database SQL Language Reference.SYSTIMESTAMP
function to display the current system date and time. The SYSTIMESTAMP
function returns a TIMESTAMP
value. For information about the SYSTIMESTAMP
function, see Oracle Database SQL Language Reference.FROM
clause of the query, DUAL
, is a one-row table that Oracle Database Express Edition creates automatically along with the data dictionary. Select from DUAL
when you want to compute a constant expression with the SELECT
statement. Because DUAL
has only one row, the constant is returned only once. For more information about selecting from DUAL
, see Oracle Database SQL Language Reference.SYSTIMESTAMP
value, but have this format:TO_CHAR
function to convert HIRE_DATE
values (which are of type DATE
) to character values that have the format FMMonth
DD
YYYY
. FM
removes leading and trailing blanks from the month name. FMMonth
DD
YYYY
is an example of a datetime format model. For information about datetime format models, see Oracle Database SQL Language Reference.TO_NUMBER
function to convert POSTAL_CODE
values (which are of type VARCHAR2
) to values of type NUMBER
, which it uses in calculations.GROUP
BY
clause, which groups query results by one or more columns, with a result for each group.COUNT
function and the GROUP
BY
clause to show how many people report to each manager. The wildcard character, *
, represents an entire record.HAVING
clause of the query.JOB_ID
.NULL
-related functions facilitate the handling of NULL
values. The NULL
-related functions that SQL supports are listed and described in Oracle Database SQL Language Reference.'B'
. If an employee receives no commission (that is, if COMMISSION_PCT
is NULL
), the NVL
function substitutes 'Not Applicable' for NULL
.'B'
, using the NVL2
function: If COMMISSION_PCT
is not NULL
, the income is the salary plus the commission; if COMMISSION_PCT
is NULL
, income is only the salary.NVL
functionNVL2
functionCASE
expression lets you use IF
... THEN
... ELSE
logic in SQL statements without invoking subprograms. There are two kinds of CASE
expressions, simple and searched.CASE
expression to show the country name for each country code.CASE
expression to show proposed salary increases, based on length of service.CASE
expressionsCASE
expressionsDECODE
function compares an expression to several search values. Whenever the value of the expression matches a search value, DECODE
returns the result associated with that search value. If DECODE
finds no match, then it returns the default value (if specified) or NULL
(if no default value is specified).DECODE
function to show proposed salary increases for three different jobs. The expression is JOB_ID
; the search values are 'PU_CLERK'
, 'SH_CLERK'
, and 'ST_CLERK'
; and the default is SALARY
.DECODE
function can be any of the SQL numeric or character types. Oracle automatically converts the expression and each search value to the data type of the first search value before comparing. Oracle automatically converts the return value to the same data type as the first result. If the first result has the data type CHAR
or if the first result is NULL
, then Oracle converts the return value to the data type VARCHAR2
.DECODE
functionapex_5.1_en.zip
from the Oracle Application Express download page. If the development will include languages other than English, download apex_5.1.zip
from the Oracle Application Express download page. See:apex_5.1_en.zip
as follows, preserving directory names:$ unzip apex_5.1_en.zip
apex_5.1_en.zip
in Windows Explorerapex_5.1.zip
as follows, preserving directory names:$ unzip
apex_5.1.zip
apex_5.1.zip
in Windows ExplorerC:TEMP
.apex
.SYS
specifying the SYSDBA
role. For example:apexins.sql
passing the following four arguments in the order shown:tablespace_apex
is the name of the tablespace for the Oracle Application Express application user.tablespace_files
is the name of the tablespace for the Oracle Application Express files user.tablespace_temp
is the name of the temporary tablespace or tablespace group.images
is the virtual directory for Oracle Application Express images. For installations using EPG, /i/
is the required value for the images argument. To support future Oracle Application Express upgrades, define the virtual image directory as /i/
.C:TEMP
in Windows, before starting SQL*Plus:SP2-0310: unable to open file 'apexins.sql
'apxrtins.sql
passing the following arguments in the order shown:tablespace_apex
is the name of the tablespace for the Oracle Application Express application user.tablespace_files
is the name of the tablespace for the Oracle Application Express files user.tablespace_temp
is the name of the temporary tablespace or tablespace group.images
is the virtual directory for Oracle Application Express images. To support future Oracle Application Express upgrades, define the virtual image directory as /i/
.APEX_050100
- The account that owns the Oracle Application Express schema and metadata.FLOWS_FILES
- The account that owns the Oracle Application Express uploaded files.APEX_PUBLIC_USER
- The minimally privileged account is used for Oracle Application Express configuration with Oracle REST Data Services or Oracle HTTP Server and mod_plsql
.ANONYMOUS
is used.FLOWS_FILES
already exists and APEX_PUBLIC_USER
is created if it does not already exist.