Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 51:

  Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS tables:

  

  You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use?

  A. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=c. department_id);

  B. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=c. department_id);

  C. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=d. department_id);

  D. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=d. department_id);

  Correct Answer: B

• Question 52:

  Which two tasks can be performed by using Oracle SQL statements? (Choose two.)

  A. changing the password for an existing database user

  B. connecting to a database instance

  C. querying data from tables in different databases

  D. starting up a database instance

  E. executing operating system (OS) commands in a session

  Correct Answer: AC

  References: http://www.techonthenet.com/oracle/password.php https://docs.oracle.com/cd/B28359_01/server.111/b28324/tdpii_distdbs.htm

• Question 53:

  View the exhibit for the structure of the STUDENT and FACULTY tables.

  

  You need to display the faculty name followed by the number of students handled by the faculty at the base location.

  Examine the following two SQL statements:

  Statement 1

  SQL>SELECT faculty_name, COUNT(student_id)

  FROM student JOIN faculty

  USING (faculty_id, location_id)

  GROUP BY faculty_name;

  Statement 2

  SQL>SELECT faculty_name, COUNT(student_id)

  FROM student NATURAL JOIN faculty

  GROUP BY faculty_name;

  Which statement is true regarding the outcome?

  A. Only statement 2 executes successfully and gives the required result.

  B. Only statement 1 executes successfully and gives the required result.

  C. Both statements 1 and 2 execute successfully and give different results.

  D. Both statements 1 and 2 execute successfully and give the same required result.

  Correct Answer: B

• Question 54:

  Which statement is true about transactions?

  A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.

  B. Each Data Definition Language (DDL) statement executed forms a single transaction.

  C. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.

  D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.

  Correct Answer: B

  References: https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038

• Question 55:

  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;

  Correct Answer: C

• Question 56:

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

  

  Which two tasks would require subqueries or joins to be executed in a single statement?

  A. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

  B. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'

  C. listing of customers who do not have a credit limit and were born before 1980

  D. finding the number of customers, in each city, whose marital status is 'married'.

  E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

  Correct Answer: AE

• Question 57:

  View the exhibit and examine the ORDERS table.

  

  The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

  A. ALTER TABLE orders MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

  B. ALTER TABLE orders ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

  C. ALTER TABLE orders MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);

  D. ALTER TABLE orders ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

  Correct Answer: C

• Question 58:

  Examine the structure of the INVOICE table.

  

  Which two SQL statements would execute successfully?

  A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete') FROM invoice;

  B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available') FROM invoice;

  C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate) FROM invoice;

  D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available') FROM invoice;

  Correct Answer: AC

• Question 59:

  Which three statements are true about the ALTER TABLE....DROP COLUMN.... command?

  A. A column can be dropped only if it does not contain any data.

  B. A column can be dropped only if another column exists in the table.

  C. A dropped column can be rolled back.

  D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.

  E. A parent key column in the table cannot be dropped.

  Correct Answer: BDE

• Question 60:

  View the exhibit and examine the description of the PRODUCT_INFORMATION table.

  

  Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

  A. SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price is NULL

  B. SELECT COUNT (NVL(list_price, 0)) FROM product_information WHERE list_price is NULL

  C. SELECT COUNT (list_price) FROM product_information WHERE list_price i= NULL

  D. SELECT COUNT (list_price) FROM product_information WHERE list_price is NULL

  Correct Answer: B