Free Oracle 1z0-071 Practice Test & Real Exam Questions, Page 1

Question #1

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. (Choose the best answer.)

You executed this UPDATE statement:

Which statement is true regarding the execution?

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

B. It would execute and restrict modifications to 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 a SELECT statement cannot be used in place of a table name.

Discussion 0

Correct Answer: B Vote an answer

Question #2

Which three are true about privileges and roles? (Choose three.)

A. PUBLIC acts as a default role granted to every user in a database.

B. PUBLIC can be revoked from a user.

C. A role is owned by the user who created it.

D. All roles are owned by the SYS schema.

E. A user has all object privileges for every object in their schema by default.

F. A role can contain a combination of several privileges and roles.

G. System privileges always set privileges for an entire database.

Discussion 0

Correct Answer: A,E,F Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #3

Which statement is true regarding the INTERSECT operator?

A. It ignores NULL values

B. Reversing the order of the intersected tables the result

C. The names of columns in all SELECT statements must be identical

D. The number of columns and data types must be identical for all SELECT statements in the query

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #4

Which statement is true about TRUNCATE and DELETE?

A. For tables with multiple indexes and triggers, DELETE is faster than TRUNCATE.

B. You can never TRUNCATE a table if foreign key constraints will be violated.

C. You can DELETE rows from a table with referential integrity constraints.

D. For large tables, DELETE is faster than TRUNCATE.

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #5

View the Exhibit and examine the structure of the CUSTOMERS table.

Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of
15% for all customers.
Customers with no credit limit should have "Not Available" displayed.
Which SQL statement would produce the required result?

A. SELECT TO_CHAR(NVL(cust_credit_limit*.15, `Not Available')) "NEW CREDIT" FROM customers;

B. SELECT NVL(TO_CHAR(cust_credit_limit*.15), `Not Available') "NEW CREDIT" FROM customers;

C. SELECT NVL(cust_credit_limit*.15, `Not Available') "NEW CREDIT" FROM customers;

D. SELECT NVL(cust_credit_limit, `Not Available')*.15 "NEW CREDIT" FROM customers;

Discussion 0

Correct Answer: B Vote an answer

Question #6

Which three statements are true about external tables? (Choose three.)

A. They can be indexed.

B. They can be temporary tables.

C. DML statements can modify them.

D. Their metadata is stored in the database.

E. They can be used in queries containing joins.

F. They can be used in queries containing sorts.

Discussion 0

Correct Answer: D,E,F Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #7

Examine the description of the PRODUCTS table:

Which three queries use valid expressions? (Choose three.)

A. SELECT product_id, (expiry_date - delivery_date) * 2 FROM products;

B. SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products;

C. SELECT product_id, expiry_date * 2 FROM products;

D. SELECT product_id, unit_price, unit_price + surcharge FROM products;

E. SELECT product_id, unit_price, 5 "Discount", unit_price + surcharge - discount FROM products;

F. SELECT product_id, unit_price | | 5 "Discount", unit_price + surcharge - discount FROM products;

Discussion 0

Correct Answer: A,B,D Vote an answer

Question #8

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

A. The argument can be a column name, variable, literal or an expression

B. They return a single result row per table

C. They can accept only one argument

D. The data type returned can be different from the data type of the argument

E. They can be nested to any level

F. They can be used only in the WHERE clause of a SELECT statement

Discussion 0

Correct Answer: A,D,E Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #9

View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.
You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order.

Which CREATE VIEW statement would create the view successfully?

A. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date;

B. CREATE OR REPLACE VIEW ord_vu (order_id,order_date) AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date;

C. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll' NO OF ITEMS' FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date WITH CHECK OPTION;

D. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date;

Discussion 0

Correct Answer: A Vote an answer

Question #10

Which statement is true about an inner join specified in the WHERE clause of a query?

A. It must have primary-key and foreign key constraints defined on the columns used in the join condition.

B. It requires the column names to be the same in all tables used for the join conditions.

C. It is applicable for only equijoin conditions.

D. It is applicable for equijoin and nonequijoin conditions.

Discussion 0

Correct Answer: D Vote an answer

Question #11

Examine the description of the PRODUCT_STATUStable:

The STATUScolumn contains the values 'IN STOCK'or 'OUT OF STOCK'for each row.
Which two queries will execute successfully? (Choose two.)

A.

B.

C.

D.

E.

F.

Discussion 0

Correct Answer: D,F Vote an answer

Question #12

Which statements are correct regarding indexes? (Choose all that apply.)

A. A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index.

B. Indexes should be created on columns that are frequently referenced as part of an expression.

C. When a table is dropped, the corresponding indexes are automatically dropped.

D. For each DML operation performed, the corresponding indexes are automatically updated.

Discussion 0

Correct Answer: A,C,D Vote an answer

Question #13

Which three statements are true reading subqueries? (Choose three.)

A. Only one column or expression can be compared between the subquery and main query.

B. The subquery and main query can retrieve data from different tables.

C. Multiple columns or expressions can be compared between the subquery and main query.

D. A subquery can have more than one main query.

E. The subquery and main query must retrieve date from the same table.

F. A Main query can have many subqueries.

Discussion 0

Correct Answer: B,C,F Vote an answer

Question #14

The BOOKS_TRANSACTIONS table exists in your schema in this database.
You execute this SQL statement when connected to your schema in your database instance.
SQL> SELECT * FROM books_transactions ORDER BY 3;
What is the result?

A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.

B. Only the three rows with the lowest values in the key column are displayed in the order that they are stored.

C. The first three rows in the table are displayed in the order that they are stored.

D. All table rows are displayed sorted in ascending order of the values in the third column.

Discussion 0

Correct Answer: D Vote an answer

Free Oracle 1z0-071 Practice Test & Real Exam Questions

Download Free Oracle 1z0-071 Demo