511133
Question 1
Question
Which statements are true? (Choose all that apply.)
Answer
-
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
Question
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?
Answer
-
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
Question
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.
Answer
-
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
Question
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?
Answer
-
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
Question
Which statement correctly differentiates a system privilege from an object privilege?
Answer
-
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
Question
View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
Which two tasks would require subqueries? (Choose two.)
Answer
-
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
Question
Which two statements are true regarding constraints? (Choose two.)
Answer
-
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
Question
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?
Answer
-
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
Question
Which two statements best describe the benefits of using the WITH clause? (Choose two.)
Answer
-
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
Question
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?
Answer
-
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.
Want to create your own Quizzes for free with GoConqr? Learn more.