Datasheet

52 Chapter 1

Introducing SQL
SQL*Plus supports all SQL statements and has its own formatting and enhancement
commands. Using this tool, you can produce interactive SQL statements and formatted
reports. SQL*Plus is the command-line interface to the database widely used by DBAs.
SQL*Plus has its own buffer where SQL statements are buffered. You can edit the buffer
using SQL*Plus editing commands. The
DESCRIBE command is used to get information on
a table, view, function, or procedure. Multiple SQL and SQL*Plus commands can be stored
in a file and can be executed as a unit. Such files are called script files.
Data in the Oracle database is managed and accessed using SQL. A
SELECT statement
is the basic form of querying or reading records from the database table. You can limit or
filter the rows using the
WHERE clause. You can use the AND and OR logical operators to join
multiple filter conditions. The
ORDER BY clause is used to sort the result set in a particular
order. You can use an ampersand (
&) character to substitute a value at runtime.
Exam Essentials
Understand the operators. Know the various operators that can be used in queries. The
parentheses around an expression change the precedence of the operators.
Understand the
WHERE clause. T h e WHERE clause specifies a condition to limit the number
or rows returned. You cannot use column alias names in this clause.
Understand the
ORDER BY clause. T h e ORDER BY clause is used to sort the result set from a
query. You can specify ascending order or descending order for the sort. Ascending order
is the default. Also know that column alias names can be used in the
ORDER BY clause. You
can also specify columns by their position.
Know how to specify string literals using the
Q/q operator. You can use the Q or q opera-
tor to specify the quote delimiters in string literals. Understand the difference between
using the
(, <, {, and [ characters and other delimiters.
Know the order of clauses in the
SELECT statement. T h e SELECT statement must have a
FROM clause. The WHERE clause, if it exists, should follow the FROM clause and precede the
ORDER BY clause.
Know the use of the DUAL table. The DUAL table is a dummy table in Oracle with one
column and one row. This table is commonly used to get the values of system variables such
as
SYSDATE or USER.
Know the characters used for pattern matching. T h e
% character is used to match zero or
more characters. The
_ character is used to match one, and only one, character. The SQL
operator used with a pattern-matching character is
LIKE.
Know the sort order of
NULL values in queries with ORDER BY clause. By default, in an
ascending-order sort, the
NULL values appear at the bottom of the result set; that is, NULLs
are sorted higher. For descending-order sorts,
NULL values appear at the top of the result
set—again,
NULL values are sorted higher.
95127c01.indd 52 2/18/09 6:37:11 AM