1Z0-071 Free Practice Test

Which two statements are true regarding working with dates? (Choose two.)

1. A. The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century.
2. B. The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century.
3. C. The default internal storage of dates is in character format.
4. D. The default internal storage of dates is in numeric format.

Correct Answer: AD

View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

The PROD_ID column is the foreign key in the SALES tables, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE 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 regarding the above command?

1. A. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
2. B. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
3. C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
4. D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.

Correct Answer: A

Which two statements are true regarding multiple-row subqueries? (Choose two.)

1. A. They can contain group functions.
2. B. They always contain a subquery within a subquery.
3. C. They use the < ALL>They can be used to retrieve multiple rows from a single table only.
4. D. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

Correct Answer: AE

View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?

1. A. It would not execute because the entire WHERE clause is not enclosed within parentheses.
2. B. It would execute but would return no rows.
3. C. It would not execute because the same column has been used twice with the AND logical operator.
4. D. It would execute and return the desired.

Correct Answer: B

Evaluate the following statement. INSERT ALL
WHEN order_total < 10000>WHEN order_total > 10000 AND order_total < 20000>WHEN order_total > 200000 THEN INTO large_orders
SELECT order_id, order_total, customer_id FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

1. A. Each row is evaluated by the first WHEN clause and if the condition is false then the row would be evaluated by the subsequent when clauses.
2. B. All rows are evaluated by all the three WHEN clauses.
3. C. Each row is evaluated by the first WHEN clause and if the condition is true, then the row would be evaluated by the subsequent when clauses.
4. D. The INSERT statement will return an error because the ELSE clause is missing.

Correct Answer: B