1Z0-071 Oracle Exam Questions and Free Practice Test

Question 31

You need to display the date 11-oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?

A. SELECT TO_CHAR (TO_DATE (’11-oct-2007’), ‘fmDdthsp “of” Month, Year’)FROM DUAL

B. SELECT TO_CHAR (‘11-oct-2007’, ‘fmDdspth “of” Month, Year’)FROM DUAL

C. SELECT TO_CHAR (TO_DATE (‘11-oct-2007’), ‘fmDdspth of month, year’)FROM DUAL

D. SELECT TO_DATE (TO_CHAR (’11-oct-2007’), ‘fmDdspth “of” Month, Year’))FROM DUAL

Correct Answer:C

Question 32

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.
1Z0-071 dumps exhibit
The PROD_ID column is the foreign key in the SALES table referencing the PRODUCTS table.
The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Examine this command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id FROM sales;
Which statement is true?

A. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

B. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

C. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.

D. The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

Correct Answer:D

Question 33

Examine this SELECT statement and view the Exhibit to see its output: (Choose two.)
1Z0-071 dumps exhibit
SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints
WHERE table_name = 'ORDERS';
Which two statements are true about the output?

A. The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.

B. The R_CONSTRAINT_NAME column contains an alternative name for the constraint.

C. In the second column, 'c' indicates a check constraint.

D. The STATUS column indicates whether the table is currently in use.

Correct Answer:AC

Question 34

Which two statements are true about sequences crated in a single instance Oracle database?

A. The numbers generated by an explicitly defined sequence can only be used to insert data in one table.

B. DELETE would remove a sequence from the database.

C. CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.

D. When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.

E. When the database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

Correct Answer:CD

Question 35

Which two statements are true regarding single row functions? (Choose two.)

A. MOD : returns the quotient of a division.

B. TRUNC : can be used with NUMBER and DATE values.

C. CONCAT : can be used to combine any number of values.

D. SYSDATE : returns the database server current date and time.

E. INSTR : can be used to find only the first occurrence of a character in a string.

F. TRIM : can be used to remove all the occurrences of a character from a string.

Correct Answer:BD

Question 36

See the Exhibit and examine the structure of the PROMOTIONS table:
1Z0-071 dumps exhibit
Using the PROMOTIONS table,
you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:

What would be the outcome?

A. It generates an error because multiple conditions cannot be specified for the WHEN clause.

B. It executes successfully and gives the required result.

C. It generates an error because CASE cannot be used with group functions.

D. It generates an error because NULL cannot be specified as a return value.

Correct Answer:B
CASE Expression
Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:
CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn ELSE else_expr]
END

START 1Z0-071 EXAM