Datasheet

62 Chapter 1

Introducing SQL
14. C. Since _ is a special pattern-matching character, you need to include the ESCAPE clause
in LIKE. The % character matches any number of characters including 0, and _ matches a
single character.
15. C. A CASE expression begins with the keyword CASE and ends with the keyword END.
16. D. An asterisk (*) is used to denote all columns in a table.
17. B. The default sorting order for a numeric column is ascending. The columns are sorted
first by salary and then by name, so the row with the lowest salary is displayed first. It is
perfectly valid to use a column in the ORDER BY clause that is not part of the SELECT clause.
18. D. In the SELECT clause, the column names should be separated by commas. An alias name
may be provided for each column with a space or using the keyword AS. The FROM clause
should appear after the SELECT clause. The WHERE clause appears after the FROM clause. The
ORDER BY clause comes after the WHERE clause.
19. D. There is no default escape character in Oracle for pattern matching. If your search
includes pattern-matching characters such as _ or %, define an escape character using the
ESCAPE keyword in the LIKE operator.
20. B. Column alias names cannot be used in the WHERE clause of the SQL statement. In the
ORDER BY clause, you can use the column name or alias name, or you can indicate the col-
umn by its position in the SELECT clause.
21. C. The query will return an error, because the substitution variable is used without an
ampersand (&) character. In this query, Oracle treats V_DEPTNO as another column name
from the table and returns an error. Substitution variables are not case sensitive.
22. B, C. When a variable is preceded by double ampersands, SQL*Plus defines that vari-
able. Similarly, when you pass values to a script using START script_name arguments,
SQL*Plus defines those variables. Once a variable is defined, its value will be available for
the duration of the session or until you use UNDEFINE variable.
23. D. % is the wild character to pattern-match for any number of characters. Option A is
almost correct, except for the SORT keyword in the ORDER BY clause, which will produce an
error since it is not a valid syntax. Option B will produce results but will sort them in the
order you want. Option C will not return any rows because LIKE is the operator for pattern
matching, not =. Option E has an error similar to Option A.
24. C. In the first SQL, the comm IN (0, NULL) will be treated as comm = 0 OR comm = NULL.
For all NULL comparisons, you should use IS NULL instead of = NULL. The first SQL will
return only one row where comm = 0, whereas the second SQL will return all the rows that
have comm = NULL as well as comm = 0.
95127c01.indd 62 2/18/09 6:37:12 AM