1Z0-071 practice questions 51–60

1. Question 51 · 238 in bank

   Select 2

   Which two queries return the string Hello! We're ready? (Choose two.)

   • ASELECT "Hello! We're ready" FROM DUAL;
   • BSELECT 'Hello! We're ready' FROM DUAL;
   • CSELECT q'!Hello! We're ready!' FROM DUAL;
   • DSELECT q'[Hello! We're ready]' FROM DUAL;
   • ESELECT 'Hello! We\'re ready’ ESCAPE '\' FROM DUAL;

   Show answer

   Answer: CD

   Explanation

   A - ORA-00904 ERROR. B - ORA-00923 ERROR. C - Hello! We're ready D - Hello! We're ready E - ORA-00923 So correct: CD. I'm not sure what kind of Oracle Compiling tools use the people from other comments, but these are definitely the correct answers using Oracle Developer...

   Source: Adapted from community discussion — Orakol

   Next question

2. Question 52 · 238 in bank

   Select 3

   Which three statements are true about the DESCRIBE command? (Choose three.)

   • AIt can be used from SQL Developer.
   • BIt displays the PRIMARY KEY constraint for any column or columns that have that constraint.
   • CIt displays all constraints that are defined for each column.
   • DIt displays the NOT NULL constraint for any columns that have that constraint.
   • EIt can be used only from SQL* Plus.
   • FIt can be used to display the structure of an existing view.

   Show answer

   Answer: ADF

   Next question

3. Question 53 · 238 in bank

   Select 2

   Which two statements are true about dropping views? (Choose two.)

   • AThe creator of a view to be dropped must have the DROP ANY VIEW privilege.
   • BData selected by a view’s defining query is deleted from its underlying tables when the view is dropped.
   • CViews referencing a dropped view become invalid.
   • DRead only views cannot be dropped.
   • ECASCADE CONSTRAINTS must be specified when referential integrity constraints on other objects refer to primary or unique keys in the view to be dropped.

   Show answer

   Answer: CE

   Next question

4. Question 54 · 238 in bank

   Select 2

   Which two are true about the MERGE statement? (Choose two.)

   • AThe WHEN NOT MATCHED clause can be used to specify the deletions to be performed.
   • BThe WHEN NOT MATCHED clause can be used to specify the updates to be performed.
   • CThe WHEN NOT MATCHED clause can be used to specify the inserts to be performed.
   • DThe WHEN WATCHED clause can be used to specify the inserts to be performed.
   • EThe WHEN WATCHED clause can be used to specify the updates to be performed.

   Show answer

   Answer: CE

   References

   • Oracle documentation

   Next question

5. Question 55 · 238 in bank

   Select 2

   Which two statements are true regarding non-equijoins? (Choose two.)

   • ATable aliases must be used.
   • BThe SQL:1999 compliant ANSI join syntax must be used.
   • CThe USING clause can be used.
   • DThe Oracle join syntax can be used.
   • EThe ON clause can be used.

   Show answer

   Answer: CE

   Next question

6. Question 56 · 238 in bank

   Select 2

   Examine the description of the PRODUCTS table which contains data:

   

   Which two are true? (Choose two.)

   • AThe PROD_NAME column cannot have a DEFAULT clause added to it.
   • BThe EXPIRY_DATE column cannot be dropped.
   • CThe EXPIRY_DATE column data type can be changed to TIMESTAMP.
   • DThe PROD_ID column can be renamed.
   • EThe PROD_ID column data type can be changed to VARCHAR2(2).

   Show answer

   Answer: CD

   Explanation

   100% C AND D HAVE A TRY: CREATE TABLE PRODUCTS_156 ( PROD_ID NUMBER(2) NOT NULL, PROD_NAME VARCHAR2(20), EXPIRY_DATE DATE ); INSERT INTO PRODUCTS_156 VALUES (1, 'Product 1', TO_DATE('2023-12-31', 'YYYY-MM-DD')); ALTER TABLE PRODUCTS_156 MODIFY EXPIRY_DATE TIMESTAMP; alter table products_156 rename column prod_id to product_id; alter table products_156 modify product_id varchar(2); (CHANGE DATA TYPE NOT ALLOWED WITH EXISTING DATA IN THE TABLE).

   Source: Adapted from community discussion — WingL

   Next question

7. Question 57 · 238 in bank

   Examine this query: SELECT SUBSTR(SYSDATE, 1, 5) "Result" FROM DUAL; Which statement is true?

   • AIt fails unless the expression is modified to SUBSTR(TO_CHAR(SYSDATE), 1, 5).
   • BIt fails unless the expression is modified to SUBSTR(TO_CHAR(TRUNC(SYSDATE), 1, 5).
   • CIt fails unless the expression is modified to TO_CHAR(SUBSTR(SYSDATE), 1, 5)).
   • DIt executes successfully with an implicit data type conversion.

   Show answer

   Answer: D

   Next question

8. Question 58 · 238 in bank

   Which statement is true about the INTERSECT operator used in compound queries?

   • AIt processes NULLs in the selected columns.
   • BIt ignores NULLs.
   • CINTERSECT is of lower precedence than UNION or UNION ALL.
   • DMultiple INTERSECT operators are not possible in the same SQL statement.

   Show answer

   Answer: A

   Next question

9. Question 59 · 238 in bank

   Select 3

   You currently have an active transaction in your session and have been granted SELECT access to V$TRANSACTION. Executing: SELECT xid, status FROM v$transaction; in your session returns:

   

   In which three situations will re-executing this query still return a row but with a different XID, indicating a new transaction has started? (Choose three.)

   • Aafter successfully executing a CREATE TABLE statement followed by a CREATE INDEX statement
   • Bafter successfully executing a TRUNCATE statement followed by a DML statement
   • Cafter successfully executing a DML statement following a failed DML statement
   • Dafter successfully executing a CREATE TABLE AS SELECT statement followed by a SELECT FOR UPDATE statement
   • Eafter successfully executing a COMMIT or ROLLBACK followed by a DML statement
   • Fafter successfully executing a COMMIT or ROLLBACK followed by a SELECT statement

   Show answer

   Answer: BDE

   Explanation

   BDE is correct, tested all on DB. F is incorrect as SELECT is a limited DML statement which means it can only access data in the database, it cannot manipulate data.

   Source: Adapted from community discussion — Darnun

   Next question

10. Question 60 · 238 in bank

    Select 2

    Which two statements are true about Oracle synonyms? (Choose two.)

    • AAny user can create a PUBLIC synonym.
    • BA synonym has an object number.
    • CAll private synonym names must be unique in the database.
    • DA synonym can be created on an object in a package.
    • EA synonym can have a synonym.

    Show answer

    Answer: BE

    Explanation

    A. Any user can create a PUBLIC synonym.( x) Must have Create Publc Syn Priv. B. A synonym has an object number. C. All private synonym names must be unique in the database. (X) Unique in the schema. D. A synonym can be created on an object in a package. (X) A schema object can't be in a package E. A synonym can have a synonym.

    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 51. 238 questions available.