In Oracle SQL Developer, an easy way to
construct a SELECT statement is to drag
and drop a table name from the TABLES
node in the Connections Navigator into
the SQL Worksheet.
The leftmost green arrow in Figure 2 is
the Execute Statement icon. When you want
to obtain the results for a single statement,
place your cursor anywhere on the statement
line and click the Execute Statement icon.
The results appear on the Results tab, as
shown in Figure 3.
In the tool bar, the small green arrow
superimposed on the image of a piece of
paper is the Run Script icon. By clicking it,
you execute a SQL*Plus-like script consisting
of multiple statements (as I’ll illustrate in
the next article in this series). The results are
displayed on the Script Output tab, as shown
in Figure 4.
data might cause the results to include
duplicate values. Use of the DISTINCT
or UNIQUE keyword in your SELEC T list,
however, helps you eliminate duplicate data
in your result sets.
In the example in Figure 6, four rows
are returned yet only two employees are
assigned to departments. Frances Newton
and Emily Eckhardt have NULL values for
DEPARTMENT_ID.
If you want to display only the distinct
(or unique) DEPARTMENT_ID values in the
EMPLOYEE table, you can construct a SELEC T
statement like the one in Figure 7.
Using the DISTINC T keyword to query a
table containing only a few rows (as in this
example) is probably unnecessary, because
duplicate data would be obvious in the full
results. But in a table with hundreds or thousands of EMPLOYEE records, it might not be
at all obvious which departments are represented (or not).
Figure 6: Employee first and last name data with
corresponding departments
BUILD AND RUN A SELEC T STATEMENT
WI TH ORACLE APPLICATION EXPRESS
You can also construct a SELEC T statement
in the SQL Commands window of Oracle
Application Express’ SQL Workshop, a Web-based interface to the database. The SQL
Workshop SQL Commands window has no
drag-and-drop facility, so you must type
your statement explicitly. Next, click Run
to see your result set in the Results section
of SQL Workshop, as shown in Figure 5.
The results format is similar to that used
on the Results tab of the SQL Worksheet,
as you can see by comparing Figure 5 with
Figure 3.
Constructing a SELECT statement in
the SQL Commands window of the SQL
Workshop in Oracle Application Express is
similar to constructing a SELEC T statement
in SQL*Plus (as I will illustrate in the next
article in this series).
Figure 7: A DISTINCT list of the DEPARTMENT_ID
values in the EMPLOYEE table
IMPROVE READABILI T Y THROUGH
CONSIS TENT FORMAT TING
The more consistently code is formatted, the
easier it is to read. The easier code is to read,
the easier it is for people reviewing it to discover obvious or potential bugs and suggest
improvements. If your IT management
insists that all developers adhere to a standard code format, Oracle SQL Developer’s
formatting facilities can help you comply
with such mandates more easily.
For example, this article’s examples
show a mix of uppercase and lowercase
keywords. However, your environment’s
standards might dictate that you use a particular casing style. Oracle SQL Developer
provides several methods to help you
achieve consistency. At a minimum, you
can make a statement’s keywords all uppercase, lowercase, or initial-capped by highlighting the statement, right-clicking in the
Figure 8: Changing keyword case
ELIMINATE REDUNDANC Y WITH
DISTINCTION
As you know from previous installments
in this series, one of your database design
goals should be to eliminate redundancy.
Sometimes, however, the way you select
Figure 9: Keyword case changed
ORACLE MAGAZINE JANUARY/FEBRUARY 2012
