SQL Chap 2 - SQL* Plus Overview

Chapter 2 - SQL*Plus Overview

OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas


Review Questions

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.
----

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.

Ans: C


2. Which command in SQL*Plus is used to save the query output to a file?

A. PRINT
B. SAVE
C. REPLACE
D. SPOOL
----

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.

Ans: D


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).
----

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).

Ans: A


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.

----
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.

Ans: B


5. Which SQL*Plus command always overwrites a file?

A. SPOOL
B. RUN
C. REPLACE
D. SAVE

-----
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.

Ans: A


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

---
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.

Ans: c


7. Choose two commands that are not valid in iSQL*Plus.


A. PASSWORD
B. TTITLE
C. CONNECT
D. EXIT
---

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.

Ans: A, D.


8. Which character is used to indicate that the command is continued on the next line in SQL*Plus?


A. -
B. /
C. \
D. >

---
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.

Ans: A


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?

A.
SELECT EMPLOYEE_ID, LAST_NAMES
FROM EMPLOYEES
WHERE LAST_NAMES = FIRST_NAMES
ORDER BY LAST_NAMES

B.
SELECT EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAMES = FIRST_NAME
ORDER BY LAST_NAME

C.
SELECT EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAMES = FIRST_NAMES
ORDER BY LAST_NAME

D.
SELECT EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME = FIRST_NAME
ORDER BY LAST_NAME

----
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.

Ans: B


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

----
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.

Ans: A


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


----
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.

Ans: C


12. Which of the following is not a valid option with the SAVE command?


A. CREATE
B. REPLACE
C. APPEND
D. INSERT

---
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.

Ans: D


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.

----
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.

Ans: C


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 */.

----
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.

Ans: C


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.

----
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.

Ans: A.


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.

----
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.

Ans: 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.
----

When substitution variables are used in iSQL*Plus, a new window will open to get the values for all variables before executing the SQL.

Ans: C


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.

-----
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.

Ans: B, C


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 &&.

----
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).

Ans: B


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.

---
The WHERE clause of the query will become WHERE ENAME = John. Oracle will look
for a column named John in the EMP table and return an error. The character literal
must be enclosed in quotation marks. The WHERE clause should be written as
WHERE ENAME = '&NAME'.

Ans: B