anillvap
Quiz by , created more than 1 year ago

Quiz on 1Z0-047_2.pdf - Part III , created by anillvap on 28/01/2014.

161
0
0
anillvap
Created by anillvap almost 11 years ago
Close

1Z0-047_2.pdf - Part III

Question 1 of 10

1

Which statements are true? (Choose all that apply.)

Select one or more of the following:

  • A. The data dictionary is created and maintained by the database administrator.

  • B. The data dictionary views can consist of joins of dictionary base tables and user-defined

    tables.

  • C. The usernames of all the users including the database administrators are stored in the data

    dictionary.

  • D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to

    which a constraint applies.

  • E. Both USER_OBJECTS and CAT views provide the same information about all the objects that

    are owned by the user.

  • F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same

    base tables from the data dictionary

Explanation

Question 2 of 10

1

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 CATEGORY_ID 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?

Select one or more of the following:

  • A. It would execute but the output would return no rows.

  • B. It would execute and the output would display the desired result.

  • C. It would not execute because the entire WHERE clause condition is not enclosed within the

    parentheses.

  • D. It would not execute because the same column has been used in both sides of the AND logical

    operator to form the condition.

Explanation

Question 3 of 10

1

Given below is the list of meta character syntaxes and their descriptions in random order:

Meta character syntax Description

1) ^ a) Matches character not in the list

2) [^...] b) Matches character when it occurs at the beginning of a line

3) | c) Treats the subsequent meta character as a literal

4) \ d) Matches one of the characters such as the OR operator

Identify the option that correctly matches the meta character syntaxes with their descriptions.

Select one or more of the following:

  • A. 1-b, 2-a, 3-d, 4-c

  • B. 1-a, 2-b, 3-d, 4-c

  • C. 1-d, 2-b, 3-a, 4-c

  • D. 1-b, 2-c, 3-d, 2-a

Explanation

Question 4 of 10

1

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables.
Evaluate the following UPDATE statement:

UPDATE

(SELECT order_date, order_total, customer_id
FROM orders)

SET order_date = '22-mar-2007'

WHERE customer_id =

(SELECT customer_id

FROM customers

WHERE cust_last_name = 'Roberts' AND

credit_limit = 600);

Which statement is true regarding the execution of the above UPDATE statement?

Select one or more of the following:

  • A. It would not execute because two tables cannot be used in a single UPDATE statement.

  • B. It would execute and restrict modifications to only the columns specified in the SELECT

    statement.

  • C. It would not execute because a subquery cannot be used in the WHERE clause of an

    UPDATE statement.

  • D. It would not execute because the SELECT statement cannot be used in place of the table

    name.

Explanation

Question 5 of 10

1

Which statement correctly differentiates a system privilege from an object privilege?

Select one or more of the following:

  • A. System privileges can be granted only by the DBA whereas object privileges can be granted

    by DBAs or the owner of the object.

  • B. System privileges give the rights to only create user schemas whereas object privileges give

    rights to manipulate objects in a schema.

  • C. Users require system privileges to gain access to the database whereas they require object

    privileges to create objects in the database.

  • D. A system privilege is the right to perform specific activities in a database whereas an object

    privilege is a right to perform activities on a specific object in the database.

Explanation

Question 6 of 10

1

View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
Which two tasks would require subqueries? (Choose two.)

Select one or more of the following:

  • A. displaying the minimum list price for each product status

  • B. displaying all supplier IDs whose average list price is more than 500

  • C. displaying the number of products whose list prices are more than the average list price

  • D. displaying all the products whose minimum list prices are more than the average list price of

    products having the product status orderable

  • E. displaying the total number of products supplied by supplier 102071 and having product status

    OBSOLETE

Explanation

Question 7 of 10

1

Which two statements are true regarding constraints? (Choose two.)

Select one or more of the following:

  • A. A foreign key cannot contain NULL values.

  • B. A column with the UNIQUE constraint can contain NULL.

  • C. A constraint is enforced only for the INSERT operation on a table.

  • D. A constraint can be disabled even if the constraint column contains data.

  • E. All the constraints can be defined at the column level as well as the table level.

Explanation

Question 8 of 10

1

View the Exhibit and examine the description of the ORDER_ITEMS table.
The following SQL statement was written to retrieve the rows for the PRODUCT_ID that has a

UNIT_PRICE of more than 1,000 and has been ordered more than five times:

SELECT product_id, COUNT(order_id) total, unit_price

FROM order_items

WHERE unit_price>1000 AND COUNT(order_id)>5

GROUP BY product_id, unit_price;

Which statement is true regarding this SQL statement?

Select one or more of the following:

  • A. The statement would execute and give you the desired result.

  • B. The statement would not execute because the aggregate function is used in the WHERE

    clause.

  • C. The statement would not execute because the WHERE clause should have the OR logical

    operator instead of AND.

  • D. The statement would not execute because in the SELECT clause, the UNIT_PRICE column is

    placed after the column having the aggregate function.

Explanation

Question 9 of 10

1

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

Select one or more of the following:

  • A. It enables users to store the results of a query permanently.

  • B. It enables users to store the query block permanently in the memory and use it to create

    complex queries.

  • C. It enables users to reuse the same query block in a SELECT statement, if it occurs more than

    once in a complex query.

  • D. It can improve the performance of a large query by storing the result of a query block having

    the WITH clause in the user's temporary tablespace.

Explanation

Question 10 of 10

1

View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.

In the ORDERS table, ORDER_ID is the PRIMARY KEY and ORDER_DATE has the DEFAULT

value as SYSDATE.

Evaluate the following statement:

UPDATE orders

SET order_date=DEFAULT

WHERE order_id IN (SELECT order_id FROM order_items

WHERE qty IS NULL);

What would be the outcome of the above statement?

Select one or more of the following:

  • A. The UPDATE statement would not work because the main query and the subquery use

    different tables.

  • B. The UPDATE statement would not work because the DEFAULT value can be used only in

    INSERT statements.

  • C. The UPDATE statement would change all ORDER_DATE values to SYSDATE provided the

    current ORDER_DATE is NOT NULL and QTY is NULL.

  • D. The UPDATE statement would change all the ORDER_DATE values to SYSDATE irrespective

    of what the current ORDER_DATE value is for all orders where QTY is NULL.

Explanation

Previous
Next