Q1. Which statement is true regarding the default behavior of the order by clause?
A. In a character sort, the values are case-sensitive.
B. NULL values are not considered at all by the sort operation.
C. Only those columns that are specified in the select list can be used in the order by clause.
D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.
Answer: A
Explanation:
Character Strings and Dates
Character strings and date values are enclosed with single quotation marks.
Character values are case-sensitive and date values are format-sensitive.
The default date display format is DD-MON-RR.
Q2. Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
A. First normal form
B. Second normal form
C. Third normal form
D. Fourth normal form
Answer: B
Q3. In which three situations does a transaction complete?
A. When a DELETE statement is executed
B. When a ROLLBACK command is executed
C. When a PL/SQL anonymous block is executed
D. When a data definition language (DDL) statement is executed
E. When a TRUNCATE statement is executed after the pending transaction
Answer: B,D,E
Q4. View the Exhibit and examine the data in the promotions table.
PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.
You need to produce a report that provides the name, cost, and start date of all promos in the post category that were launched before January 1, 2000.
Which SQL statement would you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Q5. You need to generate a list of all customer last names with their credit limits from the customers table.
Those customers who do not have a credit limit should appear last in the list.
Which two queries would achieve the required result?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B,C
Explanation:
If the ORDER BY clause is not used, the sort order is undefined, and the Oracle server may not fetch rows in the same order for the same query twice. Use the ORDER BY clause to display the rows in a specific order. Note: Use the keywords NULLS FIRST or NULLS LAST to specify whether returned rows containing null values should appear first or last in the ordering sequence. ANSWER C Sorting The default sort order is ascending:
. Numeric values are displayed with the lowest values first (for example, 1 to 999).
. Date values are displayed with the earliest value first (for example, 01-JAN-92 before 01-JAN-95).
. Character values are displayed in the alphabetical order (for example, “A” first and “Z” last).
. Null values are displayed last for ascending sequences and first for descending sequences.
-ANSWER B
. You can also sort by a column that is not in the SELECT list.
Q6. In the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'.
Evaluate the following query:
What would be the outcome?
A. Abigail PA
B. Abigail Pa
C. Abigail IS
D. An error message
Answer: B
Q7. You execute the following commands:
For which substitution variables are you prompted for the input?
A. None, because no input required
B. Both the substitution variables 'hiredate' and 'mgr_id\
C. Only 'hiredate'
D. Only 'mgr_id'
Answer: B
Q8. Which three tasks can be performed using SQL functions built into Oracle Database?
A. Displaying a date in a nondefault format
B. Finding the number of characters in an expression
C. Substituting a character string in a text expression with a specified string
D. Combining more than two columns or expressions into a single column in the output
Answer: A,B,C
Q9. View the Exhibit for the structure of the student and faculty tables.
You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements: Which statement is true regarding the outcome?
A. Only statement 1 executes successfully and gives the required result.
B. Only statement 2 executes successfully and gives the required result.
C. Both statements 1 and 2 execute successfully and give different results.
D. Both statements 1 and 2 execute successfully and give the same required result.
Answer: D
Q10. You issue the following command to drop the products table:
SQL> DROP TABLE products;
Which three statements are true about the implication of this command?
A. All data along with the table structure is deleted.
B. A pending transaction in the session is committed.
C. All indexes on the table remain but they are invalidated.
D. All views and synonyms remain but they are invalidated.
E. All data in the table is deleted but the table structure remains.
Answer: A,B,D