Online Access Free 1Z0-047 Exam Questions

Which statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose all that apply.)

• A.The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block.
• B.The WITH clause can hold more than one query.
• C.It can be used only with the SELECT clause.
• D.If the query block name and the table name were the same, then the table name would take precedence.

Evaluate the following CREATE SEQUENCE statement:
CREATE SEQUENCE seql START WITH 100
INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE;
The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seql.nextval FROM dual;
What is displayed by the SELECT statement?

• A.100
• B.10
• C.1
• D.an error

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

• A.They can be used in SELECT, WHERE, and ORDER BY clauses.
• B.They can accept only one argument.
• C.They can be nested up to only two levels.
• D.They can accept a column name, expression, variable name, or a user-supplied constant as arguments.
• E.They can modify the data type of the argument that is referenced.
• F.They can return multiple values of more than one data type.

Evaluate the SQL statements:
CREATE TABLE new_order
(orderno NUMBER(4),
booking_date TIMESTAMP WITH LOCAL TIME ZONE);
The database is located in San Francisco where the time zone is -8:00.
The user is located in New York where the time zone is -5:00.
A New York user inserts the following record:
INSERT INTO new_order
VALUES(1, TIMESTAMP 007-05-10 6:00:00 -5:00");
Which statement is true?

• A.When the New York user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'
• B.When the New York user selects the row, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'.
• C.When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000'
• D.When the San Francisco user selects the row, booking_date is displayed as 007-05-10 3.00.00.000000 -8:00'

View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"
FROM orde_items oi JOIN orders o
ON oi.order_id = o.order_id
GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)

• A.It would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
• B.It would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group.
• C.It would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group.
• D.It would return the subtotals for the Order Amount of every CUSTOMER_ID.
• E.It would return the subtotals for the Order Amount for every PRODUCT_ID.