Pass 1z1-071 Exam in First Attempt Guaranteed 2021 Dumps! [Q25-Q46]

Pass 1z1-071 Exam in First Attempt Guaranteed 2021 Dumps!

1z1-071 Dumps Full Questions - Exam Study Guide

NEW QUESTION 25
In the PROMOTIONS table, the PROMO_ BEGIN_DATE column is of data type and the default date format is DD-MON-RR
Which two statements are true about expressions using PROMO_ BEGIN_DATE in a query?

• A. PROMO_ BEGIN_DATE - 5 will return a date
• B. TONUMBER (PROMO BEGIN_DATE) - 5 will return a number
• C. PROMO_ BEGIN_DATE - SYSDATE will return an error
• D. PROMO_ BEGIN_DATE - SYSDATE will return a number
• E. TODATE(PROMO BEGIN_DATE *5) will return a date

Answer: A,D

 

NEW QUESTION 26
Examine this query:
SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL; What is the result?

• A. 0
• B. 1
• C. 2
• D. 3
• E. 4

Answer: B

 

NEW QUESTION 27
View the exhibit and examine the structure in ORDERSand ORDER_ITEMStables.

You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATEVIEWstatement would create the views 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
  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;
• C. 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;
• 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;

Answer: D

 

NEW QUESTION 28
Examine these SQL statements that are executed in the given order:
CREATE TABLE emp
(emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR 2 (15),
salary NUMBER (8, 2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp (emp_no));
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What will be the status of the foreign key EMP_MGR_FK?

• A. It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
• B. It will remain disabled and can be re-enabled manually.
• C. It will be enabled and deferred.
• D. It will be enabled and immediate.

Answer: B

 

NEW QUESTION 29
Examine this query:

Which two methods should you use to prevent prompting for a hire date value when this query is executed?

• A. Use the DEFINE command before executing the query.
• B. Execute the SET VERIFY OFF command before executing the query.
• C. Replace '&1' with '&&1' in the query.
• D. Execute the SET VERIFY ON command before executing the query.
• E. Use the UNDEFINE command before executing the query.
• F. Store the query in a script and pass the substitution value to the script when executing it.

Answer: D,F

 

NEW QUESTION 30
View the exhibit and examine the structure in ORDERSand ORDER_ITEMStables.

You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATEVIEWstatement would create the views 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
  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;
• C. 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;
• 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;

Answer: D

 

NEW QUESTION 31
View the Exhibit and examine the description for the PRODUCTS and SALES table.

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table with ON DELETE CASCADE option. The SALES table contains data for the last three years. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years.
Which is the valid DELETE statement?

• A. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE time_id - 3*365 = SYSDATE );
• B. DELETEFROM productsWHERE prod_id = (SELECT prod_idFROM salesWHERE SYSDATE >= time_id - 3*365 );
• C. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE SYSDATE -3*365 >= time_id);
• D. DELETEFROM productsWHERE prod_id IN (SELECT prod_idFROM salesWHERE time_id >= SYSDATE - 3*365 );

Answer: C

 

NEW QUESTION 32
Which three statements are true regarding indexes?

• A. A UNIQUE index can be altered to be non-unique.
• B. An update to a table can result in no updates to any of the table's indexes.
• C. A SELECT statement can access one or more indices without accessing any tables.
• D. A table belonging to one user can have an index that belongs to a different user,
• E. An update to a table can result in updates to any or all of the table's indexes.
• F. When a table is dropped and is moved to the RECYCLE BIN, all Indexes built on that table are permanently dropped.

Answer: B,D,F

 

NEW QUESTION 33
You need to display the date 11-oct-2007 in words as 'Eleventh of October, Two Thousand Seven'.
Which SQL statement would give the required result?

• A. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), 'fmDdthsp "of" Month, Year') FROM DUAL
• B. SELECT TO_DATE (TO_CHAR ('11-oct-2007'), 'fmDdspth "of" Month, Year')) FROM DUAL
• C. SELECT TO_CHAR ('11-oct-2007', 'fmDdspth "of" Month, Year')
  FROM DUAL
• D. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), 'fmDdspth of month, year')
  FROM DUAL

Answer: D

 

NEW QUESTION 34
Examine the structure of the MEMBERStable:
Name Null? Type
- --------------- ----------------- ---------------------------
MEMBER_ID NOT NULL VARCHAR2 (6)
FIRST_NAME VARCHAR2 (50)
LAST_NAME NOT NULL VARCHAR2 (50)
ADDRESS VARCHAR2 (50)
CITY VARCHAR2 (25)
STATE VARCHAR2 (3)
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?

• A. SELECT * FROM MEMBERS WHERE state LIKE 'A_';
• B. SELECT * FROM MEMBERS WHERE state LIKE 'A%';
• C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';
• D. SELECT * FROM MEMBERS WHERE state LIKE '%A_';

Answer: A

 

NEW QUESTION 35
Examine the commands used to create DEPARTMENT_DETAILS and
COURSE_DETAILS:

You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?

• A. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id=d. department_id)
• B. SELECT c.course_id, d.department_id FROM course_details c RIGHT OUTER JOIN
  .department_details d ON (c.depatrment_id=d.department_id)
• C. SELECT c.course_id, d.department_id FROM course_details c FULL OUTER JOIN department_details d ON (c.department_id<>d. department_id)
• D. SELECT course_id, department_id, FROM department_details d RIGHT OUTER JOIN course_details c USING (department_id)

Answer: A

 

NEW QUESTION 36
The first DROP operation is performed on PRODUCTS table using this command:
DROP TABLE products PURGE;
Then a FLASHBACK operation is performed using this command:
FLASHBACK TABLE products TO BEFORE DROP;
Which is true about the result of the FLASHBACK command?

• A. It recovers the table structure and data but not the related indexes.
• B. It recovers the table structure, data, and the indexes.
• C. It is not possible to recover the table structure, data, or the related indexes.
• D. It recovers only the table structure.

Answer: C

Explanation:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

 

NEW QUESTION 37
View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table. (Choose two.)

Evaluate this SQL statement:
ALTER TABLE emp
DROP COLUMN first_name;
Which two statements are true?

• A. The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement.
• B. The FIRST_NAME column would be dropped provided at least one column remains in the table.
• C. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
• D. The FIRST_NAME column would be dropped provided it does not contain any data.

Answer: B

 

NEW QUESTION 38
Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

• A. During execution, the Oracle server may read data from storage if the required data is not already in memory.
• B. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.
• C. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
• D. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.

Answer: A

 

NEW QUESTION 39
View the Exhibit and examine the data in the PRODUCTS table.

You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.
You issue this query:

Which statement is true?

• A. It generates an error because the condition specified for the PROD_CATEGORY column is not valid.
• B. It executes successfully but returns no result.
• C. It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
• D. It executes successfully and returns the required result.

Answer: B

 

NEW QUESTION 40
Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq
INCREMENT BY 1
START WITH 1
MAXVALUE 100000
CYCLE
CACHE 5000;
Create table ord_items(
ord_no number(4) default ord_seq.nextval not null,
Item_no number(3),
Qty number(3),
Expiry_date date,
Constraint it_pk primary key(ord_no,item_no),
Constraint ord_fk foreign key (ord_no) references orders(ord_no));
Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?

• A. IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
• B. Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.
• C. Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.
• D. Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.
• E. Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

Answer: B,D

 

NEW QUESTION 41
Examine this partial command:

Which two clauses are required for this command to execute successfully?

• A. the ACCESS PARAMETERS clause
• B. the DEFAULT DIRECTORY clause
• C. the access driver TYPE clause
• D. the REJECT LIMIT clause
• E. the LOCATION clause

Answer: B,E

 

NEW QUESTION 42
View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. (Choose all that apply.)

Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE JOB_ID = 'SA_MAN'
-------------------------------------
SELECT EMPLOYEE_ID
FROM JOB_HISTORY
WHERE JOB_ID = 'SA_MAN';
Choose two correct SET operators which would cause the query to return the desired result.

• A. UNION ALL
• B. UNION
• C. INTERSECT
• D. MINUS

Answer: A,B

 

NEW QUESTION 43
View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)

You executed this SQL statement:
SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;
Which two statements are true regarding the result?

• A. The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
• B. The values in the all columns would be returned in descending order.
• C. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
• D. The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
• E. The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.

Answer: D,E

 

NEW QUESTION 44
View the Exhibit and examine the structure of the ORDERS table.

You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?

• A. SELECT order_id, order_date FROM orders
  WHERE order_date > ALL
  (SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
• B. SELECT order_id, order_date FROM orders
  WHERE order_date >
  ANY
  (SELECT order_date FROM orders WHERE customer_id = 101);
• C. SELECT order_id, order_date FROM orders
  WHERE order_date > ALL
  (SELECT order_date FROM orders WHERE customer_id = 101);
• D. SELECT order_id, order_date FROM orders
  WHERE order_date > IN
  (SELECT order_date FROM orders WHERE customer_id = 101);

Answer: C

 

NEW QUESTION 45
View the Exhibit and examine the structure of the ORDERS table. (Choose the best answer.)

You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.
Which query would give you the desired result?

• A. SELECT order_id, order_date FROM ordersWHERE order_date > ANY(SELECT order_date FROM orders WHERE customer_id = 101);
• B. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
• C. SELECT order_id, order_date FROM ordersWHERE order_date > IN(SELECT
  order_date FROM orders WHERE customer_id = 101);
• D. SELECT order_id, order_date FROM ordersWHERE order_date > ALL(SELECT order_date FROM orders WHERE customer_id = 101);

Answer: D

 

NEW QUESTION 46
......

Oracle PL/SQL Developer Certified Associate  Free Certification Exam Material from RealExamFree with 305 Questions: https://www.realexamfree.com/1z1-071-real-exam-dumps.html

Use Real 1z1-071 - 100% Cover Real Exam Questions: https://drive.google.com/open?id=1N3r-mOJSZCteJO6MAfQ_jMrkHiwbdJbC