Review Questions – Chapter 2
|
1.
|
What is wrong with the following
statements submitted in SQL*Plus?
DEFINE
V_DEPTNO = 20
SELECT
LAST_NAME, SALARY
FROM EMPLOYEES
WHERE
DEPARTMENT_ID = V_DeptNo;
A. Nothing is wrong.
The query lists the employee name and salary of the employees who belong to
department 20.
B. The DEFINE
statement declaration is wrong.
C. The substitution
variable is not preceded with the & character.
D. The substitution
variable in the WHERE clause should be V_DEPTNO instead of V_DeptNo.
|
|
2.
|
Which command in SQL*Plus is used
to save the query output to a file?
A. PRINT
B. SAVE
C. REPLACE
D. SPOOL
|
|
3.
|
How would you execute a SQL
statement in the SQL buffer of SQL*Plus? (Choose all that apply.)
A. Enter a slash
(/).
B. Enter an
ampersand (&).
C. Enter a semicolon
(;).
D. Press Ctrl+D
(^D).
|
|
4.
|
You issue the SQL*Plus command
SPOOL ON. Which task is accomplished?
A. The next screen
output from the SQL*Plus session is saved into a file named afiedt.buf.
B. The next screen
output from the SQL*Plus session is saved into a file named ON.lst.
C. The next screen
output from the SQL*Plus session is sent to the printer.
D. Nothing happens;
a filename is missing from the command.
|
|
5.
|
Which SQL*Plus command always
overwrites a file?
A. SPOOL
B. RUN
C. REPLACE
D. SAVE
|
|
6.
|
Which SQL*Plus command is used to
display a title on every page of the report?
A. TOPTITLE
B. TITLE
C. TTITLE
D. REPTITLE
|
|
7.
|
Choose two commands that are not
valid in iSQL*Plus.
A. PASSWORD
B. TTITLE
C. CONNECT
D. EXIT
|
|
8.
|
Which character is used to indicate
that the command is continued on the next line in SQL*Plus?
A. -
B. /
C. \
D. >
|
|
9.
|
You have the following SQL in the
SQL buffer of SQL*Plus:
SELECT
EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME = FIRST_NAME
ORDER
BY LAST_NAME
You perform the
following SQL*Plus commands on the buffer:
3
c/NAME/NAMES/
Which SQL command
will be in the buffer?
FROM EMPLOYEES
WHERE LAST_NAMES = FIRST_NAMES
ORDER
BY LAST_NAMES
FROM EMPLOYEES
WHERE LAST_NAMES = FIRST_NAME
ORDER
BY LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAMES = FIRST_NAMES
ORDER
BY LAST_NAMED.
FROM EMPLOYEES
WHERE LAST_NAME = FIRST_NAME
ORDER
BY LAST_NAME
|
|
10.
|
Which of the following is the
correct syntax to define a variable?
A. DEFINE
variable=value
B. DEFINE variable
datatype := value
C. DEFINE
&variable
D. DEFINE variable
value
E. None of the above
|
|
11.
|
Which SET option turns off the
display of the old and new SQL statement line when variables are used?
A. ECHO OFF
B. HEADING OFF
C. VERIFY OFF
D. FEEDBACK OFF
E. DEFINE OFF
|
|
12.
|
Which of the following is not a
valid option with the SAVE command?
A. CREATE
B. REPLACE
C. APPEND
D. INSERT
|
|
13.
|
You execute the following lines of
code in SQL*Plus:
SQL>
SELECT department_id, first_name, salary
2
FROM employees
3
WHERE first_name LIKE 'S%'
4
ORDER BY department_id, first_name
5
SQL>
COLUMN department_id FORMAT A20
SQL>
C/department_id/employee_id
Which of the
following best describes the code?
A. The department_id
in the COLUMN command is replaced with employee_id.
B. The department_id
in the COLUMN command is cleared (deleted).
C. The department_id
in the fourth line of the SELECT statement is replaced with employee_id.
D. All the
department_id occurrences in the SELECT statement are replaced with
employee_id.
|
|
14.
|
Which of the following is not a
valid method for including comments?
A. Prefix comments
with --.
B. Begin comment
line with REMARK.
C. Begin comment
line with #.
D. Include comments
between /* and */.
|
|
15.
|
Consider the following SQL:
SELECT
department_id, last_name, salary
FROM employees
ORDER
BY department_id, last_name
Which SQL*Plus
command(s) will display the total salary for each department and suppress
listing of duplicate department IDs?
A. COMPUTE SUM OF
SALARY ON DEPARTMENT_ID
BREAK
ON DEPARTMENT_ID
B. BREAK ON
DEPARTMENT_ID NODUPLICATES
COMPUTE
SUM ON SALARY FOR DEPARTMENT_ID
C. BREAK ON
DEPARTMENT_ID NODUPLICATES -
SUM
ON SALARY
D. None of the
above. SQL*Plus cannot be used to total column values.
|
|
16.
|
When using iSQL*Plus, how do you
write the query results to a file?
A. Use the SPOOL
command to specify an output filename.
B. Use the Output
drop-down button and select File.
C. Perform option A
and B.
D. Perform either
option A or B.
|
|
17.
|
What will happen when you click the
Execute button with the following SQL in iSQL*Plus?
SELECT
employee_id, last_name, first_name
FROM employees
WHERE department_id = &deptid
A. Nothing will
happen, because the statement is missing a ;.
B. An error is
produced, because substitution variables are not allowed in iSQL*Plus.
C. A new window will
be opened to accept the value for DEPTID.
D. The cursor moves
to the string input area to accept value for DEPTID.
|
|
18.
|
Which two statements regarding
substitution variables are true?
A. &variable is
defined by SQL*Plus, and its value will be available for the duration of the
session.
B. &&variable
is defined by SQL*Plus, and its value will be available for the duration of
the session.
C. &n (where n
is a any integer) variables are defined by SQL*Plus when values are passed in
as arguments to the script, and their values will be available for the
duration of the session.
D. &&variable
is defined by SQL*Plus, and its value will be available only for every
reference to that variable in the current SQL.
|
|
19.
|
The contents of the script file
MYSQL.sql are as follows:
SET
PAGES 55 LINES 80 FEEDBACK OFF
SELECT
last_name, first_name
FROM employees
WHERE employee_id = &empid;
What will happen
when you issue the START MYSQL 101 command?
A. 101 will be substituted
for the variable EMPID.
B. You will be
prompted to enter a value for EMPID.
C. An error will be
returned because EMPID is not preceded by &&.
|
|
20.
|
The EMP table is defined with the
following columns:
EMPID NUMBER (5)
ENAME VARCHAR2 (30)
JOB_TITLE VARCHAR2 (30)
You execute the
following SQL, and supply a value as shown.
SQL>
SELECT * FROM EMP
2
WHERE ENAME = &name;
Enter
value for name: John
What will be the
result?
A. All the column values
from the EMP table are displayed for the record with ENAME as John.
B. An error is
returned, because John is a character literal and must be enclosed in
quotation marks.
C. An error is
returned, because Name is a reserved word in SQL*Plus, so it cannot be used
as a variable.
D. The input value
John will be converted to uppercase, and values from the EMP table are
displayed for the record with ENAME as JOHN.
|
Answers
|
1.
|
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.
|
||
|
2.
|
D. The SPOOL command is used to
save the query results to a file. Issue SPOOL filename before the query and
SPOOL OFF after the query to save the contents. The SAVE command is used to
save the SQL statement in the buffer.
|
||
|
3.
|
A. You can execute a statement in
the SQL buffer using the slash. A semicolon will just display the buffer
again (similar to the LIST command).
|
||
|
4.
|
B. The SPOOL command is used to
save the SQL*Plus session output in a file. The SPOOL command expects a
filename or the keywords OUT or OFF. SPOOL OFF will turn off spooling; SPOOL
OUT will turn off spooling and send the output file contents to a printer. If
an extension is not specified for the filename, a default extension of .lst
is added.
|
||
|
5.
|
A. The SPOOL command always creates
a new file; it will not append to an existing file. The SAVE command will
give an error if the file exists. To overwrite an existing file, you need to
specify the REPLACE option with SAVE. REPLACE is not a valid command.
|
||
|
6.
|
C. TTITLE is used to specify a
title at the top of every page. A report title at the beginning of the report
can be specified using the REPHEADER command.
|
||
|
7.
|
A, D. Certain SQL*Plus commands are
not available in iSQL*Plus. Most of the unavailable commands are not
implemented because they are not relevant on a web interface. Some commands
are not implemented because they are not secure on the web server.
|
||
|
8.
|
A. The continuation character in
SQL*Plus is -. You do not need to use a continuation character for SQL
statements, but you need one for the SQL*Plus commands. This is because
SQL*Plus commands do not need to be terminated with ; or /, whereas SQL
statements have a terminator.
|
||
|
9.
|
B. The first SQL*Plus command, 3,
makes the third line on the buffer as the current line. The next command, c,
changes the first occurrence of NAME to NAMES.
|
||
|
10.
|
A. To define a variable, you use
the syntax DEFINE variable=value. The variable will always be the CHAR
datatype. To list the value of a variable, use DEFINE variable.
|
||
|
11.
|
C. SET VERIFY OFF will turn off the
old and new line display when variables are used. SET ECHO OFF turns off the
display of SQL statements when running scripts. SET HEADING OFF turns off the
display of column headings. SET FEEDBACK OFF turns off the feedback after
executing each SQL statement. SET DEFINE OFF turns off scanning for
substitution variables in the SQL.
|
||
|
12.
|
D. The SAVE command is used to
write the SQL buffer to a file. CREATE is the default behavior; the file
should not exist for this option to work. REPLACE overwrites the file. APPEND
adds the buffer to the end of the file if the file exists. The same options
are also valid for the STORE SET command, which is used to save the SET
environment to a file.
|
||
|
13.
|
C. C is the abbreviation for
CHANGE, which is a SQL buffer-editing command. Only SQL statements are saved
in the buffer; SQL*Plus commands are not saved. Since the SELECT statement
was the last SQL statement, the cursor stayed in the last line of that
statement. Therefore, the CHANGE command was applied on the line beginning
with the ORDER BY clause.
|
||
|
14.
|
C. Comments increase the
readability of scripts. Comments using -- or /* */ can be included anywhere
in the SQL, but REMARK should be on a line of its own. SQL*Plus ignores the
rest of the line for REMARK and -- comments.
|
||
|
15.
|
A. You need both the BREAK and
COMPUTE commands to group values and perform an operation (like sum or
average). NODUPLICATES is the default behavior for the BREAK command. You can
optionally include a LABEL clause in the COMPUTE command to replace the
default column heading.
|
||
|
16.
|
B. The SPOOL command is disabled in
iSQL*Plus. You need to select the File option from the Output drop-down list
and specify a filename. Similarly, the Load Script button can be used as the
GET command, and the Clear Screen button can be used as the CLEAR SCREEN
command.
|
||
|
17.
|
C. When substitution variables are
used in iSQL*Plus, a new window will open to get the values for all variables
before executing the SQL.
|
||
|
18.
|
B, C. When a variable is preceded
by double ampersands, SQL*Plus defines that variable. Similarly, when you
pass values to a script using the 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.
|
||
|
19.
|
B. You can pass values of
substitution variables as parameters to a script only when the substitution
variables are defined as positional variables (&1, &2, and so on).
|
||
|
|
Answers
|
1.
|
C. Column alias names enclosed in
quotation marks will appear as typed. Spaces and mixed case appear in the
column alias name only when the alias is enclosed in double quotation marks.
|
|
2.
|
A. Statements 1 and 2 will produce
the same result. You can use the column name, column alias, or column
position in the ORDER BY clause. The default sort order is ascending. For a
descending sort, you must explicitly specify that order with the DESC
keyword.
|
|
3.
|
B. In the arithmetic evaluation,
multiplication and division have precedence over addition and subtraction.
Even if you do not include the parentheses, salary*0.1 will be evaluated
first. The result is then divided by 2, and its result is added to 200.
|
|
4.
|
A, C. Character literals in the SQL
statement are enclosed in single quotation marks. Literals are concatenated
using ||. Employee Name: is a character literal, and 10 is a numeric literal.
|
|
5.
|
B. Since the numeric column is
defined with precision 7 and scale 2, you can have five digits in the integer
part and two digits after the decimal point. The digits after the decimal are
rounded.
|
|
6.
|
B. The default display format of
the DATE column is DD-MON-YY, whose length is 9. This is U.S. specific and
will be different as user settings vary.
|
|
7.
|
D. DISTINCT is used to display a
unique result row, and it should follow immediately after the keyword SELECT.
Uniqueness is identified across the row, not a single column.
|
|
8.
|
B. The WHERE clause is used to
limit the rows returned from a query. The WHERE clause condition is
evaluated, and rows are returned only if the result is TRUE. The ORDER BY
clause is used to display the result in certain order.
|
|
9.
|
B. There are three records
belonging to DEPTNO 10: EMPNO 7934 (MILLER), 7839 (KING), and 7782 (CLARK).
When you sort their names by descending order, MILLER is the first row to
display. You can use alias names and columns that are not in the SELECT
clause in the ORDER BY clause.
|
|
10.
|
D. Here, a character column is
compared against a string using the BETWEEN operator, which is equivalent to
ename >= 'A' AND ename <= 'C'. The name CLARK will not be included in
this query, because 'CLARK' is > 'C'.
|
|
11.
|
C. Column alias names cannot be
used in the WHERE clause. They can be used in the ORDER BY clause.
|
|
12.
|
A. The IN operator can be used. You
can write the WHERE clause as WHERE empno IN (7782, 7876);
|
|
13.
|
B. The FROM clause appears after
the SELECT statement, followed by WHERE and ORDER BY clauses. The FROM clause
specifies the table names, the WHERE clause limits the result set, and the
ORDER BY clause sorts the result.
|
|
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 keyword END.
|
|
16.
|
D. An asterisk (*) is used to
denote all columns in a table.
|
|
17.
|
B. The default sorting order for
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 Oracle9i. 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 indicate the column by its position
in the SELECT clause.
|
No comments:
Post a Comment