1Z0-071 practice questions 71–80

1. Question 71 · 238 in bank

   Select 2

   Which two statements are true about indexes and their administration in an Oracle database? (Choose two.)

   • AA new index can be created or an existing one reused when a primary key constraint is created.
   • BAn INVINSIBLE index is maintained by DML operations on the underlying table.
   • CIf a query filters on an indexed column, the index will always be accessed during execution of the query.
   • DA DROP INDEX statement always prevents updates to the table during the drop operation.
   • EThe same table column cannot be part of a unique and non-unique index.

   Show answer

   Answer: AB

   Explanation

   c. No, that statement is not necessarily true. If a query filters on an indexed column, the optimizer will decide whether or not to use the index based on factors such as the selectivity of the filter and the size of the table. In some cases, a full table scan may be more efficient than using an index.

   Source: Adapted from community discussion — zouve

   Next question

2. Question 72 · 238 in bank

   Select 3

   Examine this incomplete query: SELECT DATE '2019-01-01' + FROM DUAL; Which three clauses can replace to add 12 hours to the date? (Choose three.)

   • AINTERVAL '0.5' DAY
   • BINTERVAL '720' MINUTE
   • CINTERVAL '11:60' HOUR TO MINUTE
   • DINTERVAL '12:00' HOUR TO SECOND
   • EINTERVAL '0 12' DAY TO HOUR
   • FINTERVAL '12' HOUR

   Show answer

   Answer: BEF

   Explanation

   /*A NOOK*/ select date'2019-01-01' + interval '0.5' day from dual; /*01867. 00000 - "the interval is invalid" *Cause: The character string you specified is not a valid interval. *Action: Please specify a valid interval.*/ /*B OK*/ select date'2019-01-01' + interval '720' MINUTE from dual; /*C NOOK*/ select date'2019-01-01' + interval '11:60' HOUR TO MINUTE from dual; /*ORA-01851: los minutos deben estar comprendidos entre 0 y 59 01851. 00000 - "minutes must be between 0 and 59"*/ /*D NOOK*/ select date'2019-01-01' + interval '12:00' HOUR TO SECOND from dual; /*ORA-01867: el intervalo no es válido 01867. 00000 - "the interval is invalid" *Cause: The character string you specified is not a valid interval.*/ /*E OK*/ select date'2019-01-01' + interval '0 12' DAY TO HOUR from dual; /*F OK*/ select date'2019-01-01' + interval '12' HOUR from dual;

   Source: Adapted from community discussion — SantiBZ_07032022_1744

   Next question

3. Question 73 · 238 in bank

   Select 2

   Which two are true about the data dictionary? (Choose two.)

   • AThe data dictionary is constantly updated to reflect changes to database objects, permissions, and data.
   • BAll user actions are recorded in the data dictionary.
   • CAll users have permissions to access all information in the data dictionary by default.
   • DThe SYS user owns all base tables and user-accessible views in the data dictionary.
   • EBase tables in the data dictionary have the prefix DBA_.

   Show answer

   Answer: AD

   Next question

4. Question 74 · 238 in bank

   Select 2

   Which two statements are true about the DUAL table: (Choose two.)

   • AIt can display multiple rows but only a single column.
   • BIt can be accessed only by the SYS user.
   • CIt can be accessed by any user who has the SELECT privilege in any schema.
   • DIt can display multiple rows and columns.
   • EIt consists of a single row and single column of VARCHAR2 data type.
   • FIt can be used to display only constants or pseudo columns.

   Show answer

   Answer: CE

   References

   • Oracle documentation

   Next question

5. Question 75 · 238 in bank

   Examine these statements and the result:

   

   Now examine this command:

   

   What must replace MISSING CLAUSE for CUSTOMER_SEQ.NEXTVAL to return 11?

   • ANOCACHE
   • BINCREMENT BY 10
   • CSTART WITH 11
   • DMINVALUE 11
   • ECYCLE 11

   Show answer

   Answer: B

   Explanation

   I creating sequence - create sequence customer_seq cache 10; What I get from alter sequence customer_seq: A. NOCACHE – NEXTVAL 1 B. INCREMENT BY 10 - NEXTVAL 10. C. START WITH 11 - ORA-02283: cannot alter starting sequence number D. MINVALUE 11 - ORA-04007: MINVALUE cannot be made to exceed the current value E. CYCLE 11 - ORA-00933: SQL command not properly ended

   Source: Adapted from community discussion — Ramunele

   Next question

6. Question 76 · 238 in bank

   Select 2

   Examine the description of the ORDER_ITEMS table:

   

   Examine this incomplete query:

   

   Which two can replace so the query completes successfully?

   • Aquantity * unit_price
   • Bquantity
   • Ctotal_paid
   • Dproduct_id
   • Equantity, unit_price

   Show answer

   Answer: AC

   Next question

7. Question 77 · 238 in bank

   Which set of commands will prompt only once for the name of the table to use in the query?

   • APROMPT Enter table name &x - SELECT employee_id FROM &x WHERE last_name = ‘King’;
   • BDEFINE x = ‘employees’ PROMPT Enter table name &x - SELECT employee_id FROM &x WHERE last_name = ‘King’;
   • CPROMPT Enter table name &x - SELECT employee_id FROM &&x WHERE last_name = ‘King’;
   • DPROMPT Enter table name &&x - SELECT employee_id FROM &x WHERE last_name = ‘King’;

   Show answer

   Answer: D

   Next question

8. Question 78 · 238 in bank

   The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2. The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson. Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

   • ASELECT REPLACE(REPLACE(cust_last_name, ‘son’, ‘’), ‘An’, ‘O’) FROM customers;
   • BSELECT REPLACE(TRIM(TRAILING ‘son’ FROM cust_last_name), ‘An’, ‘O’) FROM customers;
   • CSELECT REPLACE(SUBSTR(cust_last_name, -3), ‘An’, ‘O’) FROM customers;
   • DSELECT INITCAP(REPLACE(TRIM(‘son’ FROM cust_last_name), ‘An’, ‘O’)) FROM customers;

   Show answer

   Answer: A

   Explanation

   create table customers (cust_last_name varchar2(10)) select * from customers; insert into customers values ('Anderson') insert into customers values('Ausson') select replace (replace(cust_last_name,'son',''),'An','O') from customers; -- + select replace (trim(trailing 'son' from cust_last_name),'An','O') from customers; -- trim should have only one character select replace (substr (cust_last_name,-3),'An','O') from customers; -- both values are 'son' select initcap(replace(trim('son' from cust_last_name),'An','O')) from customers; -- trim should have only one character

   Source: Adapted from community discussion — NICK9525

   Next question

9. Question 79 · 238 in bank

   Select 2

   Examine the description of the PRODUCT_STATUS table:

   

   The STATUS column contains the values IN STOCK or OUT OF STOCK for each row. Which two queries will execute successfully?

   • ASELECT prod_id || q”’s not available” FROM product_status WHERE status = ‘OUT OF STOCK’;
   • BSELECT prod_id || q’(‘s not available)’ ‘CURRENT AVAILABILITY’ FROM product_status WHERE status = ‘OUT OF STOCK’;
   • CSELECT prod_id q’s not available” FROM product_status WHERE status = ‘OUT OF STOCK’;
   • DSELECT prod_id “CURRENT AVAILABILITY” || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’;
   • ESELECT prod_id || q’(‘s not available)’ FROM product_status WHERE status = ‘OUT OF STOCK’;
   • FSELECT prod_id || q’(‘s not available)’ “CURRENT AVAILABILITY” FROM product status WHERE status = ‘OUT OF STOCK’;

   Show answer

   Answer: EF

   Explanation

   Tested column aliases cannot before the concatination unless you want to also inclunde them which means you need to add another ||

   Source: Adapted from community discussion — Cecilia_Soso

   Next question

10. Question 80 · 238 in bank

    Select 2

    Which two are true about transactions in the Oracle Database? (Choose two.)

    • ADML statements always start new transactions.
    • BDDL statements automatically commit only data dictionary updates caused by executing the DDL.
    • CA session can see uncommitted updates made by the same user in a different session.
    • DA DDL statement issued by a session with an uncommitted transaction automatically commits that transaction.
    • EAn uncommitted transaction is automatically committed when the user exits SQL*Plus.

    Show answer

    Answer: DE

    Explanation

    A. DML statements always start new transactions. (X). Not always B. DDL statements automatically commit only data dictionary updates caused by executing the DDL. (X) Not "only" data dictionary C. A session can see uncommitted updates made by the same user in a different session. (X) in the same session D. A DDL statement issued by a session with an uncommitted transaction automatically commits that transaction. E. An uncommitted transaction is automatically committed when the user exits SQL*Plus.

    Source: Adapted from community discussion — holdfaststrongly

    References

    • Oracle documentation

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

1Z0-071 practice, question 71. 238 questions available.