1z0-071 Pre-Exam Practice Tests (Updated 305 Questions) [Q66-Q89]

1z0-071 Pre-Exam Practice Tests | (Updated 305 Questions)

Valid 1z0-071 Exam Q&A PDF - One Year Free Update

Benefits in Obtaining Oracle 1Z0-071: Oracle Database SQL Exam Certification

By integrating a broad range of relevant data base functionality, roles and activities, Oracle 1Z0-071 Certification enhances your experience and expertise. Oracle Database qualification prepares you through research, laboratories and experience to complete challenging and practical tasks. With this certificate, the knowledge and familiarity with Oracle RAC and Grid Infrastructure will be improved. After receiving this credential, RAC database, Clusterware and ASM environment may be installed, maintained, tuned and retrieved. 1Z0-071 practice exams are the best way to start the preparation.

The attainment of this credential offers you a strategic edge by translating your know-how into a worldwide market. 80 percent of Oracle accredited people have indicated that qualification has helped them promote, raise salaries or boost their job. You’ll still be able to get a digital badge on your LinkedIn profiles. Although you are not sure which alternative is the right one, label the best choice as your response so that any query shouldn’t be left as unanswered at 1Z0-071 practice test.

 

NEW QUESTION 66
Which three statements are true about the Oracle join and ANSI Join syntax?

• A. The SQL:1999 compliant ANSI join syntax supports natural joins.
• B. The Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
• C. The Oracle join syntax performs less well than the SQL:1999 compliant ANSI Join Answer.
• D. The Oracle join syntax supports creation of a Cartesian product of two tables.
• E. The SQL:1999 compliant ANSI join syntax supports creation of a Cartesian product of two tables.
• F. The Oracle join syntax supports natural joins.
• G. The Oracle join syntax only supports right outer joins,

Answer: A,D,E

 

NEW QUESTION 67
Examine the structure of the EMPLOYEES table.

You must display the maximum and minimum salaries of employees hired 1 year ago.
Which two statements would provide the correct output? (Choose two.)

• A. SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);
• B. SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROMemployeesWHERE hire_date < SYSDATE-365);
• C. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary));
• D. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;

Answer: B,D

 

NEW QUESTION 68
Evaluate this ALTER TABLE statement: (Choose the best answer.)
ALTER TABLE orders
SET UNUSED (order_date);
Which statement is true?

• A. The DESCRIBE command would still display the ORDER_DATE column.
• B. The ORDER_DATE column must be empty for the ALTER TABLE command to execute successfully.
• C. After executing the ALTER TABLE command, a new column called ORDER_DATE can be added to the ORDERS table.
• D. ROLLBACK can be used to restore the ORDER_DATE column.

Answer: C

 

NEW QUESTION 69
The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What must be done to fix the statement?

• A. ALL should be replaced with a list of specific privileges.
• B. WITH GRANT OPTION should be added to the statement.
• C. PUBLIC should be replaced with specific usernames.
• D. Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.

Answer: D

Explanation:
References:
http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljgrant.html

 

NEW QUESTION 70
Evaluate the following query:

What would be the outcome of the above query?

• A. It produces an error because flower braces have been used.
• B. It executes successfully and displays the literal " {'s start date was \> " for each row in the output.
• C. It produces an error because the data types are not matching.
• D. It executes successfully and introduces an 's at the end of each promo_name in the output.

Answer: D

 

NEW QUESTION 71
View the Exhibit and examine the structure of the BOOKS table.

The BOOKS table contains details of 100 books.
Examine the commands executed and their outcome:

Which statement is true?

• A. Both ROLLBACK commands restore the 101 rows that were deleted.
• B. Both ROLLBACK commands restore the 100 rows that were deleted.
• C. The first rollback restores the 101 rows that were deleted and the second rollback causes the row was inserted to be deleted and commits the changes.
• D. The first rollback restores the 100 rows that were deleted and the second rollback commits only the changes.

Answer: C

 

NEW QUESTION 72
View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?

• A. DELETEFROM ordersWHERE (SELECT order_idFROM order_items);
• B. DELETE ordersWHERE order_total < 1000;
• C. DELETE order_idFROM ordersWHERE order_total < 1000;
• D. DELETE orders o, order_items IWHERE o.order_id = i.order_id;

Answer: A

 

NEW QUESTION 73
Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the creation and storage of data in the above table structure? (Choose two.)

• A. The TRANS_VALIDITY column would have a maximum size of one character.
• B. The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
• C. The CUST_STATUS column would give an error.
• D. The CUST_STATUS column would store exactly one character.
• E. The TRANS_VALIDITY column would give an error.
• F. The CUST_CREDIT_LIMIT column would be able to store decimal values.

Answer: D,E

 

NEW QUESTION 74
Examine the description of the EMPLOYEES table:

Which query requires explicit data type conversion?

• A. SELECT SUBSTR(join_date, 1, 2) - 10 FROM employees;
• B. SELECT salary + '120.50' FROM employees;
• C. SELECT join_date + '20' FROM employees;
• D. SELECT join_date || ' ' || salary FROM employees;
• E. SELECT join_date FROM employees WHERE join_date > '10-02-2018';

Answer: E

 

NEW QUESTION 75
View the exhibit and examine the structure of the EMPLOYEES table.

You want to display all employees and their managers having 100 as the MANAGER_ID.
You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?

• A. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE m.manager_id = 100;
• B. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eWHERE m.employee_id = e.manager_id and AND e.manager_id = 100
• C. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON e.employee_id = m.manager_idWHERE m.manager_id = 100;
• D. SELECT m.last_name "Manager", e.last_name "Employee"FROM employees m JOIN employees eON m.employee_id = e.manager_idWHERE e.manager_id = 100;

Answer: D

 

NEW QUESTION 76
View the Exhibit and examine the description of the tables.

You execute this SQL statement:

Which three statements are true? (Choose three.)

• A. The statement will execute successfully and a new row will be inserted into the SALES table.
• B. A customer can exist in many countries.
• C. The statement will fail because a subquery may not be contained in a VALUESclause.
• D. The statement will fail if a row already exists in the SALEStable for product 23.
• E. The SALEStable has five foreign keys.
• F. A product can have a different unit price at different times.

Answer: A,B,E

 

NEW QUESTION 77
Examine these statements:

Which two are true? (Choose two.)

• A. Both foreign key constraint definitions must be removed from the CREATE TABLE statements, and be added with ALTER TABLEstatements once both tables are created, for the two CREATE TABLE statements to execute successfully in the order shown.
• B. The MGRcolumn in the DEPTtable will not be able to contain NULLvalues.
• C. The DEPT_FKEYconstraint definition must be removed from the CREATE TABLE DEPT statement, and be added with an ALTER TABLEstatement once both tables are created, for the two CREATE TABLE statements to execute successfully in the order shown.
• D. The CREATE TABLE EMPstatement must precede the CREATE TABLE DEPTstatement for all three statements to execute successfully.
• E. The DEPTNOcolumn in the EMPtable will be able to contain NULLvalues.
• F. All three statements execute successfully in the order shown.

Answer: A,E

 

NEW QUESTION 78
View the exhibit and examine the ORDERStable.

The ORDERStable contains data and all orders have been assigned a customer ID. Which statement would add a NOTNULLconstraint to the CUSTOMER_IDcolumn?
ALTER TABLE orders

• A. ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
  ALTER TABLE orders
• B. ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;
• C. MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);
  ALTER TABLE orders
• D. MODIFY customer_id CONSTRAINT orders_cust_nn NOT NULL (customer_id);
  ALTER TABLE orders

Answer: D

Explanation:
Explanation/Reference:

 

NEW QUESTION 79
Which three statements are true?

• A. The statement will execute successfully and a new row will be inserted into the SALES table.
• B. A product can have a different unit price at different times.
• C. A customer can exist in many countries.
• D. The SALES table has five foreign keys.
• E. The statement will fail because subquery may not be I contained in a values clause.
• F. The statement will fail if a row already exists in the SALES table for product 23.

Answer: A,B,D

 

NEW QUESTION 80
The SYSDATEfunction displays the current Oracle Server date as:
21-MAY-19
You wish to display the date as:
MONDAY, 21 MAY, 2019
Which statement will do this?
SELECT TO_DATE(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

• A. SELECT TO_CHAR(SYSDATE, 'FMDD, DAY MONTH, YYYY') FROM DUAL;
• B. SELECT TO_CHAR(SYSDATE, 'FMDAY, DDTH MONTH, YYYY') FROM DUAL;
• C. SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;
• D.

Answer: D

Explanation:
SELECT ID, TO_CHAR(Start_Date,'fmDay Month fmDD, YYYY') AS "Start Date" FROM Employee; Reference: http://www.java2s.com/Code/Oracle/Data-Type/ TOCHARDatefmDayMonthfmDDYYYYEmbeddedspacescanberemovedbyplacingthefmprefix.htm

 

NEW QUESTION 81
Which three statements are true about defining relations between tables in a relational database? (Choose three.)

• A. Every primary or unique key value must refer to a matching foreign key value.
• B. Every foreign key value must refer to a matching primary or unique key value.
• C. Foreign key columns allow null values.
• D. Unique key columns allow null values.
• E. Primary key columns allow null values.

Answer: B,E

 

NEW QUESTION 82
Which three are true about multitable INSERTstatements? (Choose three.)

• A. They can be performed on external tables using SQL* Loader.
• B. They can be performed on views.
• C. They can be performed on relational tables.
• D. They can be performed on remote tables.
• E. They can be performed only by using a subquery.
• F. They can insert each computed row into more than one table.

Answer: A,C,F

 

NEW QUESTION 83
Which three statements are true regarding subqueries? (Choose three.)

• A. Main query and subquery can get data from different tables.
• B. Main query and subquery must get data from the same tables.
• C. Subqueries can contain GROUP BY and ORDER BY clauses.
• D. Multiple columns or expressions can be compared between the main query and subquery.
• E. Only one column or expression can be compared between the main query and subquery.
• F. Subqueries can contain ORDER BY but not the GROUP BY clause.

Answer: A,C,D

Explanation:
References:
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

 

NEW QUESTION 84
View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.

You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.
Which two DELETE statements are valid (Choose two.)

• A. DELETE FROM order_items iWHERE order_id = (SELECT order_id FROM orders oWHERE i.order_id = o.order_id AND order_status IN (0,1));
• B. DELETEFROM order_itemsWHERE order_id IN (SELECT order_idFROM ordersWHERE orders_status in (0,1));
• C. DELETEFROM (SELECT * FROM order_items I,orders oWHERE i.order_id = o.order_id AND order_status IN (0,1));
• D. DELETE *FROM order_itemsWHERE order_id IN (SELECT order_id)FROM ordersWHERE order_status IN (0,1));

Answer: B,C

 

NEW QUESTION 85
The CUSTOMERStable has a CUST_CREDIT_LIMITcolumn of data type NUMBER.
Which two queries execute successfully?

• A. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not
  Available') FROM customers;
• B. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
• C. SELECT NVL2 (cust_credit_limit * .15, 'Not Available') FROM customers;
• D. SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers;
• E. SELECT NVL (cust_credit_limit * .15, 'Not Available') FROM customers;

Answer: B,C

 

NEW QUESTION 86
Evaluate the following CREATE TABLE command:

Which statement is true regarding the above SQL statement?

• A. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK would be created.
• B. It would execute successfully and only ORD_ITM_IDX index would be created.
• C. It would give an error because the USING INDEX clause cannot be used on a composite primary.
• D. It would give an error because the USING INDEX is not permitted in the CRETAE TABLE command.

Answer: B

 

NEW QUESTION 87
View the exhibit and examine the structure of the STOREStable.

You want to display the NAME of the store along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATEin the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?

• A. SELECT name, address||','||city||','||country AS full_address,
  start_date,
  property_price, property_price*115/100
  FROM stores
  WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
  <=36;
• B. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100
  FROM stores
  WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;
• C. SELECT name, concat (address| | ','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100
  FROM stores
  WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36;
• D. SELECT name, concat (address||','| |city| |', ', country) AS full_address, start_date, property_price, property_price*115/100
  FROM stores
  WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
  <=36;

Answer: D

 

NEW QUESTION 88
Examine the structure of the EMPLOYEES table.
NameNull?Type
---------------------- ------------
EMPLOYEE_IDNOT NULLNUMBER(6)
FIRST_NAMEVARCHAR2(20)
LAST_NAMENOT NULLVARCHAR2(25)
EMAILNOT NULLVARCHAR2(25)
PHONE NUMBERVARCHAR2(20)
HIRE_DATENOT NULLDATE
JOB_IDNOT NULLVARCHAR2(10)
SALARYNUMBER(8,2)
COMMISSION_PCTNUMBER(2,2)
MANAGER_IDNUMBER(6)
DEPARTMENT_IDNUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?

• A. SELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN
  employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;
• B. SELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN
  employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;
• C. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN
  employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;
• D. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN
  employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;

Answer: C

 

NEW QUESTION 89
......

Oracle Database SQL Free Update Certification Sample Questions: https://www.pass4leader.com/Oracle/1z0-071-exam.html

Trend for Oracle 1z0-071 pdf dumps before actual exam: https://drive.google.com/open?id=1HWw3AbXiUjMfTsh50L5TPRXfCMOjd8i-