1Z0-071 Online Practice Questions and Answers

Which three actions can you perform by using the ORACLE DATAPUMP access driver?

A. Create a directory object for an external table.

B. Read data from an external table and load it into a table in the database.

C. Query data from an external table.

D. Create a directory object for a flat file.

E. Execute DML statements on an external table.

F. Read data from a table in the database and insert it into an external table.

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

A. SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;

B. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;

C. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;

D. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;

The BOOKS_TRANSACTIONS table exists in your database.

SQL>SELECT * FROM books_transactions ORDER BY 3;

What is the outcome on execution?

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

B. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.

C. Rows are displayed in the order that they are stored in the table only for the first three rows.

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

View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.

Evaluate the following MERGE statement:

MERGE_INTO orders_master o USING monthly_orders m ON (o.order_id = m.order_id) WHEN MATCHED THEN UPDATE SET o.order_total = m.order_total DELETE WHERE (m.order_total IS NULL) WHEN NOT MATCHED THEN INSERT VALUES (m.order_id, m.order_total)

What would be the outcome of the above statement?

A. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2, 3 and 4.

B. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 4.

C. The ORDERS_MASTER table would contain the ORDER_IDs 1, 2 and 3.

D. The ORDERS_MASTER table would contain the ORDER_IDs 1 and 2.

View the exhibit and examine the structure 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) 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) "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;

D. 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 WHITH CHECK OPTION;

View the Exhibit and examine the description for the SALES and CHANNELS tables. (Choose the best answer.)

You issued this SQL statement:

Which statement is true regarding the result?

A. The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.

B. The statement will fail because a subquery cannot be used in a VALUES clause.

C. The statement will execute and a new row will be inserted in the SALES table.

D. The statement will fail because the VALUES clause is not required with the subquery.

View the exhibit and examine the structure and data in the INVOICE table.

Which two SQL statements would execute successfully? (Choose two.)

A. SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice

B. SELECT AVG(inv_date) FROM invoice

C. SELECT MAX(inv_date), MIN(cust_id) FROM invoice

D. SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

Examine the contents of the EMP table:

Examine this query that executes successfully: What is the result?

A. It will return the five employees earning the lowest salaries, in ascending order.

B. It will return the six employees earning the highest salaries, in descending order.

C. It will return the six employees earning the lowest salaries, in ascending order.

D. It will return the five employees earning the highest salaries, in descending order.

The SYSDATE function displays the current Oracle Server date as:

21 -MAY-19

You wish to display the date as:

MONDAY, 21 MAY, 201 9

Which statement will do this?

A. SELECT TO _ CHAR (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;

B. SELECT TO _ DATE (SYSDATE, ' FMDAY, DD MONTH, YYYY') FROM DUAL;

C. SELECT TO_ CHAR (SYSDATE, ' FMDD, DAY MONTH, YYYY') FROM DUAL;

D. SELECT TO_ CHAR (SYSDATE, ' FMDAY, DDTH MONTH, YYYY') FROM DUAL;

Which two queries execute successfully?

A. SELECT prod_id, exp_date FROM products UNION ALL SELECT prod_id, NULL FROM new_products;

B. SELECT prod_id, prod_name FROM products INTERSECT SELECT 100, prod_name FROM newproducts;

C. SELECT * FROM products UNION SELECT * FROM new_products;

D. SELECT k FROM products MINUS SELECT prod_id FROM new_products;

E. SELECT prod_id FROM products UNION ALL SELECT prod_id, prod_name FROM new_products;

You need to allow user ANDREW to:

1.

Modify the TITLE and ADDRESS columns of your CUSTOMERS table.

2.

GRANT tha permission to other users.

Which statement will do this?

A. GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;

B. GRANT UPDATE ON customers. title, customers.address TO andrew WITH GRANT OPTION;

C. GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;

D. GRANT UPDATE (title, address) ON customers TO andrew;

E. GRANT UPDATE ON customers. title, customers.address TO andrew;

F. GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION:

Which two statements are true about conditional INSERT ALL?

A. Each row returned by the subquery can be inserted into only a single target table.

B. It cannot have an ELSE clause.

C. The total number of rows inserted is always equal to the number of rows returned by the subquery

D. A single WHEN condition can be used for multiple INTO clauses.

E. Each WHEN condition is tested for each row returned by the subquery.

Examine this SQL statement:

Which two are true?

A. The subquery is executed before the UPDATE statement is executed.

B. All existing rows in the ORDERS table are updated.

C. The subquery is executed for every updated row in the ORDERS table.

D. The UPDATE statement executes successfully even if the subquery selects multiple rows.

E. The subquery is not a correlated subquery.

You execute this command:

ALTER TABLE employees SET UNUSED (department_id);

Which two are true?

A. A query can display data from the DEPARTMENT_ID column.

B. The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

C. The DEPARTMENT_ID column is set to null for all tows in the table

D. A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.

E. No updates can be made to the data in the DEPARTMENT_ID column.

F. The DEPARTMENT_ID column can be recovered from the recycle bin

Examine the description of the EMPLOYEES table:

Which two statements will run successfully?

A. SELECT 'The first_name is '' || first_name || '' FROM employees ;

B. SELECT 'The first_name is '''||first_name ||'''' FROM employees ;

C. SELECT 'The first_name is ''' ||first_name||''' FROM employees ;

D. SELECT 'The first_name is '|| first_name|| '' FROM employees;

E. SELECT 'The first_name is \'' || first_name || '\'' FROM employees;