Oracle 1z1-071 Practice Verified Answers - Pass Your Exams For Sure! [2021]
Valid Way To Pass Oracle PL/SQL Developer Certified Associate's 1z1-071 Exam
NEW QUESTION 183
Which two statements will convert the string Hello world to ello wozid?
- A. SELECT INITCAP(TRIM('H' FROM 'Hello World')) FROM DUAL;
- B. SELECT LOWER (SUBSTR('Hello World, 2, 1)) FROM DUAL;
- C. SELECT LOWER (SUBSTR('Hello World', 2)) FROM DUAL;
- D. SELECT SUBSTR('Hello world', 2) FROM DUAL;
- E. SELECT LOWER(TRIM('H' FROM 'Hello World')) FROM DUAL;
Answer: C,E
NEW QUESTION 184
Which three statements are true reading subquenes?
- A. A subquery can have more than one main query.
- B. The subquery and main query must retrieve date from the same table.
- C. The subquery and main query can retrieve data from different tables.
- D. Multiple columns or expressions can be compared between the subquery and main query.
- E. Only one column or expression can be compared between the subquery and main query.
- F. A Main query can have many subqueries.
Answer: C,D,F
NEW QUESTION 185
Examine the description of the BOOKS table:
The table has 100 rows.
Examine this sequence of statements issued in a new session:
Which two statements are true?
- A. The second ROLLBACK command replays the delete.
- B. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.
- C. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.
- D. The second ROLLBACK command undoes the insert.
- E. The second ROLLBACK command does nothing.
Answer: C
NEW QUESTION 186
Which three statements are true reading subquenes?
- A. The subquery and main query must retrieve date from the same table.
- B. The subquery and main query can retrieve data from different tables.
- C. A subquery can have more than one main query
- D. Multiple columns or expressions can be compared between the subquery and main query.
- E. Only one column or expression can be compared between the subquery and main query.
- F. A Main query can have many subqueries.
Answer: B,D,F
NEW QUESTION 187
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_DATE (TO_CHAR ('11-oct-2007'), 'fmDdspth "of" Month, Year'))FROM DUAL
- B. SELECT TO_CHAR ('11-oct-2007', 'fmDdspth "of" Month, Year')FROM DUAL
- C. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), 'fmDdthsp "of" Month, Year')FROM DUAL
- D. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), 'fmDdspth of month, year')FROM DUAL
Answer: D
NEW QUESTION 188
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), MAX(salary)FROM (SELECT salary FROMemployeesWHERE hire_date < SYSDATE-365);
- B. SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);
- C. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;
- D. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary));
Answer: A,C
NEW QUESTION 189
View the Exhibit and examine the structure of the PRODUCT table.
Which two tasks would require subqueries? (Choose two.)
- A. display all suppliers whose list price is more than 1000
- B. display the number of products whose list prices are more than the average list price
- C. display the minimum list price for each product status
- D. display the total number of products supplied by supplier 102 and have product status as 'OBSOLETE'
- E. display all products whose minimum list price is more than the average list price of products having the status 'orderable'
Answer: B,E
NEW QUESTION 190
View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
Which two tasks would require subqueries? (Choose two.)
- A. displaying all supplier IDs whose average list price is more than 500
- B. displaying the number of products whose list prices are more than the average list price
- C. displaying the minimum list price for each product status
- D. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
- E. displaying all the products whose minimum list prices are more than average list price of products having the status orderable
Answer: B,E
NEW QUESTION 191
A non-correlated subquery can be defined as __________. (Choose the best answer.)
- A. A SELECT statement that can be embedded in a clause of another SELECT statement only.
- B. A set of sequential queries, all of which must return values from the same table.
- C. A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query.
- D. A set of sequential queries, all of which must always return a single value.
Answer: C
NEW QUESTION 192
View the Exhibit and examine the structure of the PRODUCTS table. (Choose the best answer.)
You must display the category with the maximum number of items.
You issue this query:
SQL > SELECT COUNT(*), prod_category_id
FROM products
GROUP BY prod_category_id
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM porducts);
What is the result?
- A. It executes successfully and gives the correct output.
- B. It executes successfully but does not give the correct output.
- C. It generates an error because = is not valid and should be replaced by the IN operator.
- D. It generate an error because the subquery does not have a GROUP BY clause.
Answer: D
NEW QUESTION 193
Examine this query:
SELECT SUBSTR (SYSDATE,1,5) 'Result' FROM DUAL
Which statement is true?
- A. It executes successfully with an implicit data type conversion
- B. It fails unless the expression is modified to SUBSTR (TO_ CHAR(SYSDATE),1,5)
- C. It fails unless the expression is modified to SUBSTR (TO_ CHAR(TRUNC(SYSDATE)),1,5)
- D. It fails unless the expression is modified to TO-CHAR(SUNBSTR(SYSDATE,1,5)
Answer: A
NEW QUESTION 194
Which three statements are true about GLOBAL TEMPORARY TABLES?
- A. A GLOBAL TEMPORARY TABLE cannot have a PUBLIC SYNONYM.
- B. A trigger can be created on a GLOBAL TEMPORARY TABLE.
- C. A GLOBAL TEMPORARY TABLE can have multiple indexes.
- D. A GLOBAL TEMPORARY TABLE can have only one index.
- E. A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view.
- F. Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO.
Answer: B,E,F
NEW QUESTION 195
View the exhibit and examine the structure of ORDERSand CUSTOMERStables.
Which INSERT statement should be used to add a row into the ORDERStable for the customer whose CUST_LAST_NAMEis Robertsand CREDIT_LIMITis 600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.
- A. INSERT INTO orders
VALUES (1,'10-mar-2007', 'direct',
( SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), 1000); - B. INSERT INTO orders (order_id, order_date, order_mode,
( SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), order_total)
VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000); - C. INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_total FROM orders o, customers c WHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600) VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000);
- D. INSERT INTO orders (order_id, order_date, order_mode,
( SELECT customer_id
FROM customers
WHERE cust_last_name='Roberts' AND credit_limit=600), order_total)
VALUES (1,'10-mar-2007', 'direct', &&customer_id, 1000);
Answer: A
NEW QUESTION 196
Evaluate the following query:
SQL> SELECT TRUNC (ROUND (156.00, -1),-1)
FROM DUAL;
What would be the outcome?
- A. 0
- B. 1
- C. 2
- D. 3
- E. 4
Answer: D
Explanation:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions135.htm
https://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_2127.htm
NEW QUESTION 197
Which three statements are true about sequences in a single instance Oracle database?
- A. Two or more tables cannot have keys generated from the same sequence.
- B. A sequence's unallocated cached values are lost if the instance shuts down.
- C. A sequence can issue duplicate values.
- D. A sequence can only be dropped by a DBA.
- E. A sequence number that was allocated can be rolled back if a transaction fails.
- F. Sequences can always have gaps.
Answer: B,C,F
NEW QUESTION 198
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, 'FMDAY, DD MONTH, YYYY') FROM DUAL;
- B. SELECT TO_CHAR(SYSDATE, 'FMDAY, DDTH MONTH, YYYY') FROM DUAL;
- C.
- D. SELECT TO_CHAR(SYSDATE, 'FMDD, DAY MONTH, YYYY') FROM DUAL;
Answer: C
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 199
......
Oracle 1z1-071 Pre-Exam Practice Tests | Prep4sureGuide: https://www.prep4sureguide.com/1z1-071-prep4sure-exam-guide.html
1z1-071 practice test questions, answers, explanations: https://drive.google.com/open?id=1VtLX0W8rvrhtwzhSLMe-Y_FPlM_mE8Rb