Oracle Oracle Certifications 1Z0-071 Questions & Answers

• Question 371:

  Which two statements cause changes to the data dictionary? (Choose two.)

  A. DELETE FROM scott.emp;

  B. ALTER SESSION SET NLS_DATE_FORMAT = `DD/MM/YYYY';

  C. GRANT UPDATE ON scott.emp TO fin manager;

  D. SELECT * FROM user_tab_prive;

  E. TRUNCATE TABLE emp;

  Correct Answer: BC

• Question 372:

  You execute these commands:

  CREATE TABLE customers (customer id INTEGER, customer name VARCHAR2 (20));

  INSERT INTO customers VALUES (1`Custmoer1 `);

  SAVEPOINT post insert;

  INSERT INTO customers VALUES (2, 'Customer2 `);

  SELECTCOUNT (*) FROM customers;

  Which two, used independently, can replace so the query retums 1?

  A. ROLLBACK;

  B. COMMIT;

  C. ROLIBACK TO SAVEPOINT post_ insert;

  D. CONOIT TO SAVEPOINT post_ insert;

  E. ROLLEBACK TO post_ insert;

  Correct Answer: CE

• Question 373:

  Examine this business rule:

  Each student can work on multiple projects and each project can have multiple students.

  You must design an Entity Relationship (ER) model for optimal data storage and allow for generating reports in this format:

  STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK

  Which two statements are true? (Choose two.)

  A. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.

  B. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity.

  C. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the students and projects entities.

  D. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into one-to-many relationships.

  E. The ER must have a one-to-many relationship between the STUDENTS and PROJECTS entities.

  Correct Answer: CD

• Question 374:

  Examine the description of the EMPLOYEES table

  

  Which two queries return the highest salary in the table?

  A. SELECT MAX(salary) FROM employees GROUP By department_id HAVING MAX(salary) = MAX(MAX(salary));

  B. SELECT MAX(salary) FROM employees GROUP By department_id;

  C. SELECT department_id, MAX(salary) FROM employees GROUP By department_id;

  D. SELECT MAX(salary) FROM employees;

  E. SELECT MAX(MAX(salary)) FROM employees GROUP By department_id;

  Correct Answer: DE

• Question 375:

  Examine the description of the PROMOTIONS TABLE:

  

  You want to display the unique promotion costs in each promotion category.

  Which two queries can be used?

  A. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;

  B. SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;

  C. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

  D. SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;

  E. SELECT promo_cost, promo_category FROM promotions ORDER BY 1;

  Correct Answer: AC

• Question 376:

  Which three statements are true about Structured Query Language (SQL)?

  A. It guarantees atomicity, consistency, isolation, and durability (ACID) features

  B. It best supports relational databases

  C. It is used to define encapsulation and polymorphism for a relational table

  D. It requires that data be contained in hierarchical data storage

  E. It is the only language that can be used for both relational and object-oriented databases

  F. It provides independence for logical data structures being manipulated from the underlying physical data storage

  Correct Answer: ABF

• Question 377:

  Examine the data in the ORDERS table:

  

  Examine the data in the INVOICES table:

  

  Examine this query:

  SELECT order_ id, order_ date FROM orders

  INTERSECT

  SELECT order_ 1d, order_ date FROM invoices;

  Which two rows will it return?

  A. 3

  B. 2

  C. 1

  D. 5 01-MAR-2019

  E. 4 01-FEB-2019

  F. 3 01-JAN-2019

  Correct Answer: CE

• Question 378:

  Examine this query Which executes successfully:

  SELECT job, deptno FROM emp

  UNION ALL

  SELECT job, deptno FROM jobs_ history;

  What will be the result?

  A. It will return rows common to both SELECT statements.

  B. It will return rows from both SELECT statements after eliminating duplicate rows.

  C. It will return rows that are not common to both SELECT statements.

  D. It will return rows from both SELECT statements including duplicate rows.

  Correct Answer: D

• Question 379:

  You execute this command: TRUNCATE TABLE depts; Which two are true?

  A. It retains the indexes defined on the table.

  B. It drops any triggers defined on the table.

  C. A Flashback TABLE statement can be used to retrieve the deleted data.

  D. It retains the integrity constraints defined on the table.

  E. A ROLLBACK statement can be used to retrieve the deleted data.

  F. It always retains the space used by the removed rows

  Correct Answer: AD

• Question 380:

  Examine the description of the CUSTOMERS table:

  

  You need to display last names and credit limits of all customers whose last name starts with A or B In lower or upper case, and whose credit limit is below 1000.

  Examine this partial query:

  SELECT cust_last_nare, cust_credit_limit FROM customers

  Which two WHERE conditions give the required result?

  A. WHERE UPPER(cust_last_name) IN (`AX', `B%') AND cust_credit_limit < 1000;

  B. WHERE (UPPER(cust_last_name) LIKE `A%' OR UPPER(cust_last_name) LIKE `B%') AND ROUND(cust_credit_limit) < 1000;

  C. WHERE UPPER(cust_last_name) BETWEEN UPPER(`A%' AND `B%') AND ROUND(cust_credit_limit) < 1000;

  D. WHERE (INITCAP(cust_last_name) LIKE `A%' OR INITCAP(cust_last_name) LIKE `B%') AND cust_credit_limit < 1000;

  E. WHERE (UPPER(cust_last_name) LIKE INITCAP(`A') OR UPPER(cust_last_name) LIKE INITCAP(`B')) AND ROUND(cust_credit_limit) < ROUND(1000);

  Correct Answer: BD