Question: 1
Which of the following
statements contains an error?
A. SELECT
* FROM emp WHERE empid = 493945;
B. SELECT
empid FROM emp WHERE empid= 493945;
C. SELECT
empid FROM emp;
Answer:
Option D
Reason:
There is no FROM clause after the SELECT list in the query.
Question:
2
Which of the following
correctly describes how to specify a column alias?
A. Place
the alias at the beginning of the statement to describe the table.
B. Place
the alias after each column, separated by white space, to describe the column.
C. Place
the alias after each column, separated by a comma, to describe the column.
D. Place
the alias at the end of the statement to describe the table.
Answer:
Option B
Reason:
Alias names for columns have to be specified just after the column in the
SELECT list. It has to be separated from the column name with a white space.
Example:
SELECT sysdate my_date FROM dual;
In the
above example my_date field is the alias for the pseudo-column sysdate in the
SELECT list.
Question: 3
The NVL function
A. Assists
in the distribution of output across multiple columns.
B. Allows
the user to specify alternate output for non-null column values.
C. Allows
the user to specify alternate output for null column values.
D. Nullifies
the value of the column output.
Answer:
Option C
Reason:
The NVL function is used to substitute NULL values with a meaningful value to
be returned by the query.
Example:
SELECT ename, NVL(last_salary,0) FROM emp;
Question: 4
Output from a table
called PLAYS with two columns, PLAY_NAME and AUTHOR, is shown below. Which of
the following SQL statements produced it?
PLAY_TABLE
-------------------------------------
"Midsummer Night's Dream", SHAKESPEARE
"Waiting For Godot", BECKETT
"The Glass Menagerie", WILLIAMS
-------------------------------------
"Midsummer Night's Dream", SHAKESPEARE
"Waiting For Godot", BECKETT
"The Glass Menagerie", WILLIAMS
A. SELECT
play_name || author FROM plays;
B. SELECT
play_name, author FROM plays;
C. SELECT
play_name||', ' || author FROM plays;
D. SELECT
play_name||', ' || author PLAY_TABLE FROM plays;
Answer:
Option D
Reason: Though Option C might be confused with Option D for
the correct answer, a closer evaluation of the output with both of the queries
will reveal that Option D is the correct one. If you have not still got the
gist of what made Option D the answer, check that the column header in the
output is having PLAY_TABLE. As this field is not in the table, it could be
only an alias for the column which have did the work. There is no column-alias
for Option C, but it is present in Option D.
Question: 5
Issuing the
DEFINE_EDITOR="emacs" will produce which outcome?
A. The
emacs editor will become the SQL*Plus default text editor.
B. The
emacs editor will start running immediately.
C. The
emacs editor will no longer be used by SQL*Plus as the default text editor.
D. The emacs editor will be
deleted from the system.
Answer:
Option A
Reason: This SQL *Plus command will set the value for the
variable DEFINE_EDITOR. Later on if we are using the text editor to edit the
queries typed previously, SQL *Plus uses the value set in this variable to
identify what is the text editor which has to be used.
Question: 6
The user issues the
following statement. What will be displayed if the EMPID selected is 60494?
SELECT DECODE(empid,38475,
‘Terminated’,60494, ‘LOA’, ‘ACTIVE’) FROM emp;
A. 60494
B. LOA
C. Terminated
D. ACTIVE
Answer:
Option B
Reason:
The DECODE function can evaluate multiple conditions at one stretch. The above
DECODE function evaluates to the following IF sequence.
Example:
IF empid = 38475 Then
Return ‘Terminated’;
ELSIF empid = 60494 Then
Return ‘LOA’;
ELSE
Return ‘ACTIVE’;
END IF;
Question: 7
A. TRUE
B. FALSE
Answer:
Option B
Reason: There is an extra parenthesis before the TO_CHAR
function to make the select statement syntactically incorrect.
Question: 8
The appropriate table to
use when performing arithmetic calculations on values defined within the SELECT
statement (not pulled from a table column) is
A. EMP
B. The
table containing the column values
C. DUAL
D. An
Oracle-defined table
Answer:
Option C
Reason:
DUAL is an Oracle pseudo-table which can be used either for arithmetic
calculations or for selecting pseudo-columns like SYSDATE from this table.
Example:
SELECT sysdate+10 FROM DUAL;
Question: 9
Which of the following is not a group function?
A. avg( )
B. sqrt( )
C. sum( )
D. max( )
Answer:
Option B
Reason:
sqrt() function is the only function among the list that accepts one value
instead of a set of values. If we issue the sqrt() function against a set of
records in a table, it will return square root for each of the records in the
table. But if we issue the same command against avg(), sum(), max() functions
it will return one row only based on the function logic.
Example: SELECT avg(sal) FROM emp;
SELECT
sqrt(144) FROM dual;
SELECT
sum(sal) FROM emp;
SELECT max(sal) FROM emp;
Question: 10
The default character for specifying runtime variables in SELECT
statements is
A. Ampersand
B. Ellipses
C. Quotation
marks
D. Asterisk
Answer:
Option A
Reason:
& is the variable we can use inside SQL *Plus to accept values from user.
Example:
INSERT INTO emp VALUES (&empno, ‘&ename’, &sal);
In the
above INSERT statement, SQL *Plus will prompt each time for three variables
viz., &empno, &ename and &sal. Then it swaps the user entered
values with the &variables and inserts the same in the table.
