1Z0-047_2.pdf - Part III

Quiz by , created over 5 years ago

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

Created by anillvap over 5 years ago
Religious Studies- Matters of life and death
Emma Samieh-Tucker
GCSE AQA Chemistry Atomic Structure and Bonding
Of Mice and Men Characters - Key essay points
Lilac Potato
Health and Social Care Flashcards
Kelsey Phillips
New GCSE history content
Sarah Egan
1Z0-047_2.pdf - Part II
1Z0-047_2.pdf - Part I
Cold War (1945-1975)
Forces and Acceleration
Adam Collinge
Holly Bamford

Question 1

Which statements are true? (Choose all that apply.)
  • 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

Question 2

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?
  • 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.

Question 3

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.
  • 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

Question 4

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?
  • 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.

Question 5

Which statement correctly differentiates a system privilege from an object privilege?
  • 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.

Question 6

View the Exhibit and examine the data in the PRODUCT_INFORMATION table. Which two tasks would require subqueries? (Choose two.)
  • 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

Question 7

Which two statements are true regarding constraints? (Choose two.)
  • 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.

Question 8

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?
  • 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.

Question 9

Which two statements best describe the benefits of using the WITH clause? (Choose two.)
  • 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.

Question 10

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?
  • 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.