Oracle Database SQL practice questions

1. Question 1 of 25

   Select 3

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

   • AThe same table column can be part of a unique and non-unique index.
   • BA descending index is a type of function-based index.
   • CAn INVINSIBLE index is not maintained when DML is performed on its underlying table.
   • DIf a query filters on an indexed column then it will always be used during execution of the query.
   • EAn index can be created as part of a CREATE TABLE statement.
   • FAn UNUSABLE index is maintained when DML is performed on its underlying table.

   Show answer

   Correct answer: A, B, E

   How others answered

   • ABE100%

   Explanation

   C and F are wrong, oracle documentation says: Unlike unusable indexes, an invisible index is maintained during DML statements. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-3A66938F-73C6-4173-844E-3938A0DBBB54

   Adapted from community discussion by TATSSSS · 2024-09-19 · 2 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Oracle documentation
   • Oracle documentation

   View community discussion4

   Candidate context

   Question 1 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

   1. T

      TATSSSS

      2024-09-19

      Selected A, B, E2 upvotes

      C and F are wrong, oracle documentation says: Unlike unusable indexes, an invisible index is maintained during DML statements. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-3A66938F-73C6-4173-844E-3938A0DBBB54

   2. P

      Poke90

      2023-09-26

      Selected A, B, E1 upvote

      invisible indexes are maintained, unusable indexes are not. C and F are wrong

   3. J

      jr_C

      2023-09-12

      1 upvote

      C is false, it is explained on the link you provided on the section: Understand When to Use Unusable or Invisible Indexes

   4. P

      Poke90

      2023-09-10

      1 upvote

      F is wrong but C is correct: https://docs.oracle.com/cd/E18283_01/server.112/e17120/indexes004.htm so either ACE or BCE

2. Question 2 of 25

   Select 3

   Which three are true about scalar subquery expressions? (Choose three.)

   • AThey can be nested.
   • BThey cannot be used in the VALUES clause of an INSERT statement.
   • CA scalar subquery expression that returns zero rows evaluates to zero.
   • DThey can be used as default values for columns in a CREATE TABLE statement.
   • EA scalar subquery expression that returns zero rows evaluates to NULL.
   • FThey cannot be used in GROUP BY clauses.

   Show answer

   Correct answer: A, E, F

   How others answered

   • AEF83%
   • BEF17%

   Explanation

   Sorry to say I don't share your point here. D is falso since, as official doc from Oracle highlights, scalar subqueries are not valid expressions as default values for columns. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Scalar-Subquery-Expressions.html#GUID-475D80C3-C873-4475-AB1A-8837C5CF8CE4

   Adapted from community discussion by alelejaja · 2024-11-23 · 4 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Oracle documentation
   • Oracle documentation
   • Oracle documentation

   View community discussion4

   Candidate context

   Question 2 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

   1. Z

      zouve

      2024-01-05

      Selected A, E, F3 upvotes

      A. They can be nested. Scalar subqueries can be nested within other scalar subqueries or within other SQL expressions. E. A scalar subquery expression that returns zero rows evaluates to NULL. If a scalar subquery returns no rows, it evaluates to NULL. F. They cannot be used in GROUP BY clauses. Scalar subqueries cannot be used in GROUP BY clauses because they do not return a single value that can be used to group rows.

   2. A

      alelejaja

      2024-11-23

      4 upvotes

      Sorry to say I don't share your point here. D is falso since, as official doc from Oracle highlights, scalar subqueries are not valid expressions as default values for columns. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Scalar-Subquery-Expressions.html#GUID-475D80C3-C873-4475-AB1A-8837C5CF8CE4

   3. J

      J4vi

      2023-11-04

      2 upvotes

      AEF There is no way you could use it in GROUP BY clauses, but it says nothing about VALUES clause. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Scalar-Subquery-Expressions.html#GUID-475D80C3-C873-4475-AB1A-8837C5CF8CE4

   4. C

      Chandra1104

      2023-09-21

      1 upvote

      Its ABE. A. They can be nested. --> True B. They cannot be used in the VALUES clause of an INSERT statement. --> True C. A scalar subquery expression that returns zero rows evaluates to zero. --> False D. They can be used as default values for columns in a CREATE TABLE statement.--> False E. A scalar subquery expression that returns zero rows evaluates to NULL. --> True F. They cannot be used in GROUP BY clauses.--> False https://www.oratable.com/scalar-subquery/

3. Question 3 of 25

   Which statement is true about TRUNCATE and DELETE?

   • AFor tables with multiple indexes and triggers, DELETE is faster than TRUNCATE.
   • BYou can never TRUNCATE a table if foreign key constraints would be violated.
   • CYou can DELETE rows from a table with referential integrity constraints.
   • DFor large tables, DELETE is faster than TRUNCATE.

   Show answer

   Correct answer: B

   How others answered

   • B71%
   • C29%

   Only 71% agreed on this answer — treat it as contested rather than settled.

   Explanation

   C. A and D: False --> Truncate is better and has better performance than delete and Drop except for very small tables. B False --> You can truncate a table with a FK on Cascade. You can disable FK and then Truncate (except if FK is self-refential).

   Adapted from community discussion by aamm74200 · 2023-01-27 · 4 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Oracle documentation
   • Oracle documentation

   View community discussion4

   Candidate context

   Question 3 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

   1. A

      aamm74200

      2023-01-27

      4 upvotes

      C. A and D: False --> Truncate is better and has better performance than delete and Drop except for very small tables. B False --> You can truncate a table with a FK on Cascade. You can disable FK and then Truncate (except if FK is self-refential).

   2. V

      Viviana3184

      2022-12-29

      3 upvotes

      i think C is correct if the referential integrity constraint has the ON DELETE CASCADE option

   3. S

      Shrimathi

      2023-07-29

      2 upvotes

      C is correct, We can trucate table contains referential integrity, one by drop the constraint/ while adding constraint we can include on delete cascade . Truncate table colors cascade; this statement will run successfully. Reference https://www.examtopics.com/discussions/oracle/view/15064-exam-1z0-071-topic-1-question-268-discussion/

   4. J

      jfc1

      2023-01-26

      Selected B2 upvotes

      D is wrong . B is correct https://docs.oracle.com/database/121/SQLRF/statements_10007.htm#SQLRF01707

4. Question 4 of 25

   Select 3

   You own table DEPARTMENTS, referenced by views, indexes, and synonyms. Examine this command which executes successfully: DROP TABLE departments PURGE; Which three statements are true? (Choose three.)

   • AIt will remove the DEPARTMENTS table from the database.
   • BIt will drop all indexes on the DEPARTMENTS table.
   • CIt will remove all views that are based on the DEPARTMENTS table.
   • DIt will remove all synonyms for the DEPARTMENTS table.
   • ENeither can it be rolled back nor can the DEPARTMENTS table be recovered.
   • FIt will delete all rows from the DEPARTMENTS table, but retain the empty table.

   Show answer

   Correct answer: A, B, E

   How others answered

   • ABE88%

   Explanation

   In ORACLE SQL, you own table DEPARTMENTS, referenced by views, indexes, and synonyms. Examine this command which executes successfully: DROP TABLE departments PURGE; Which three statements are true? (Choose three.) A. It will remove the DEPARTMENTS table from the database. (True) B. It will drop all indexes on the DEPARTMENTS table. (True) C. It will remove all views that are based on the DEPARTMENTS table. (False, it will not remove the views that are based on the DEPARTMENTS table) D. It will remove all synonyms for the DEPARTMENTS table. (False, it will not remove the synonyms for the DEPARTMENTS table) E. Neither can it be rolled back nor can the DEPARTMENTS table be recovered. (True) F. It will delete all rows from the DEPARTMENTS table, but retain the empty table. (False, it will delete all rows and the table itself)

   Adapted from community discussion by subtype11 · 2023-07-28 · 2 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Oracle documentation
   • Oracle documentation

   View community discussion4

   Candidate context

   Question 4 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

   1. L

      Lee_jong_suk

      2024-06-08

      5 upvotes

      ABE. Note: Synoyms and views will get invalid, Indexes will be dropped along with the table and data will be purged and free space will be regained, dropped table can not be recovered even if you use flashback table command

   2. S

      subtype11

      2023-07-28

      2 upvotes

      In ORACLE SQL, you own table DEPARTMENTS, referenced by views, indexes, and synonyms. Examine this command which executes successfully: DROP TABLE departments PURGE; Which three statements are true? (Choose three.) A. It will remove the DEPARTMENTS table from the database. (True) B. It will drop all indexes on the DEPARTMENTS table. (True) C. It will remove all views that are based on the DEPARTMENTS table. (False, it will not remove the views that are based on the DEPARTMENTS table) D. It will remove all synonyms for the DEPARTMENTS table. (False, it will not remove the synonyms for the DEPARTMENTS table) E. Neither can it be rolled back nor can the DEPARTMENTS table be recovered. (True) F. It will delete all rows from the DEPARTMENTS table, but retain the empty table. (False, it will delete all rows and the table itself)

   3. R

      Rik92

      2023-12-26

      Selected A, B, E2 upvotes

      ABE are correct. Views and synonyms are not dropped with the table

   4. P

      pushpaksarode

      2025-03-30

      Selected A, B, E1 upvote

      It will drop DEPARTMENTS table along with all indexes as we have used PURGE in DROP statement so table cann't be rollback or recovered.

5. Question 5 of 25

   Select 3

   Which three statements are true about an ORDER BY clause? (Choose three.)

   • ABy default an ORDER BY clause sorts rows in descending order
   • BAn ORDER BY clause will always precede a HAVING clause if both are used in the same top-level query.
   • CAn ORDER BY clause always sorts NULL values last.
   • DBy default an ORDER BY clause sorts rows in ascending order.
   • EAn ORDER BY clause can perform a binary sort.
   • FAn ORDER BY clause can perform a linguistic sort.

   Show answer

   Correct answer: D, E, F

   How others answered

   • DEF100%

   Explanation

   Shouldn't it be DEF? A - FALSE; by default sorting is ASC B - FALSE; In both syntax and execution ORDER BY is always after HAVING C - FALSE; NULLS FIRST can be used to sort NULL values first D - TRUE; E, F - TRUE; parameter NLS_SORT can be changed between BINARY and linguistic_definition. https://docs.oracle.com/database/121/REFRN/GUID-04243CDA-43CA-4AF8-B59D-6FB841B307BE.htm#REFRN10127

   Adapted from community discussion by Darnun · 2024-03-29 · 4 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Oracle documentation

   View community discussion3

   Candidate context

   Question 5 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

   1. D

      Darnun

      2024-03-29

      Selected D, E, F4 upvotes

      Shouldn't it be DEF? A - FALSE; by default sorting is ASC B - FALSE; In both syntax and execution ORDER BY is always after HAVING C - FALSE; NULLS FIRST can be used to sort NULL values first D - TRUE; E, F - TRUE; parameter NLS_SORT can be changed between BINARY and linguistic_definition. https://docs.oracle.com/database/121/REFRN/GUID-04243CDA-43CA-4AF8-B59D-6FB841B307BE.htm#REFRN10127

   2. Z

      zouve

      2024-07-07

      Selected D, E, F2 upvotes

      The three statements that are true about an ORDER BY clause are: D. By default, an ORDER BY clause sorts rows in ascending order. E. An ORDER BY clause can perform a binary sort. F. An ORDER BY clause can perform a linguistic sort. An ORDER BY clause is used to sort the rows returned by a query in a specified order. By default, if no sort order is specified, the rows are sorted in ascending order. An ORDER BY clause can perform different types of sorts, including binary and linguistic sorts. A binary sort sorts the data based on the binary representation of the values being sorted. A linguistic sort sorts the data based on a specific linguistic definition, taking into account language-specific rules for character and string comparison.

   3. L

      lucemqy

      2024-11-16

      2 upvotes

      DEF correct, Null is sorted last in ascending and first in descending by default

6. Question 6 of 25

   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

   Correct answer: B, E

   How others answered

   • BE58%
   • DE42%

   Only 58% agreed on this answer — treat it as contested rather than settled.

   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.

   Adapted from community discussion by holdfaststrongly · 2022-09-20 · 16 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Oracle documentation

   View community discussion4

   Candidate context

   Question 6 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

   1. H

      holdfaststrongly

      2022-09-20

      16 upvotes

      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.

   2. J

      jm9999

      2023-09-27

      Selected B, E3 upvotes

      Synonym can be created for the whole package but not for components of the package.

   3. K

      kenan_yu

      2022-09-26

      2 upvotes

      D answer is incorrect for another reason, you actually can create synonym on an object in a PL/SQL package but in won't be active(checked): CREATE OR REPLACE PACKAGE long_package_name AS FUNCTION give_me_zero RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY long_package_name AS FUNCTION give_me_zero RETURN NUMBER IS BEGIN RETURN 0; END; END; / CREATE OR REPLACE SYNONYM pkg_func FOR long_package_name.give_me_zero; SELECT pkg_func.give_me_zero FROM dual;

   4. M

      Matvey

      2023-02-03

      3 upvotes

      You have created synonym on the entire package, not on the package object.

7. Question 7 of 25

   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

   Correct answer: D, E

   How others answered

   • DE38%
   • AD38%
   • BD25%

   Only 38% agreed on this answer — treat it as contested rather than settled.

   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.

   Adapted from community discussion by holdfaststrongly · 2022-09-20 · 9 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Oracle documentation

   View community discussion4

   Candidate context

   Question 7 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

   1. H

      holdfaststrongly

      2022-09-20

      9 upvotes

      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.

   2. D

      dilshod

      2023-06-09

      3 upvotes

      https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve040.htm#BABEGEGC

   3. H

      highwater

      2025-03-26

      Selected D, E1 upvote

      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

   4. H

      hmatinnn

      2024-01-31

      Selected D, E1 upvote

      should be DE SET AUTOCOMMIT does not alter the commit behavior when SQL*Plus exits. Any uncommitted data is committed by default.

8. Question 8 of 25

   Table HR.EMPLOYEES contains a row where the EMPLOYEE_ID is 109. User ALICE has no privileges to access HR.EMPLOYEES. User ALICE starts a session. User HR starts a session and successfully executes these statements: GRANT DELETE ON employees TO alice; UPDATE employees SET salary = 24000 WHERE employee_id = 109; In her existing session ALICE then executes: DELETE FROM hr.employees WHERE employee_id = 109; What is the result?

   • AThe DELETE command will wait for HR’s transaction to end then return an error.
   • BThe DELETE command will immediately delete the row.
   • CThe DELETE command will wait for HR’s transaction to end then delete the row.
   • DThe DELETE command will immediately return an error.

   Show answer

   Correct answer: D

   How others answered

   • D64%
   • C27%

   Only 64% agreed on this answer — treat it as contested rather than settled.

   Explanation

   I'm seeing a lot of debate here about the security, but what about data lock? nothing indicates that the HR committed the update, thus; shouldn't the row be locked?

   Adapted from community discussion by yanoolthecool · 2024-01-08 · 2 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Oracle documentation

   View community discussion4

   Candidate context

   Question 8 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

   1. Y

      yanoolthecool

      2024-01-08

      Selected D2 upvotes

      I'm seeing a lot of debate here about the security, but what about data lock? nothing indicates that the HR committed the update, thus; shouldn't the row be locked?

   2. J

      jm9999

      2023-10-11

      2 upvotes

      If you are finding C works, check to see if 'Alice' has the select any table privilege. That was an issue for me.

   3. C

      CyberP

      2023-05-29

      2 upvotes

      But in the link that you provide, half of them choose C as correct answer

   4. C

      CMjer

      2023-05-27

      Selected D1 upvote

      https://www.examtopics.com/discussions/oracle/view/22160-exam-1z0-071-topic-2-question-65-discussion/

9. Question 9 of 25

   Select 2

   Examine this partial command:

   

   Which two clauses are required for this command to execute successfully? (Choose two.)

   • Athe access driver TYPE clause
   • Bthe DEFAULT DIRECTORY clause
   • Cthe REJECT LIMIT clause
   • Dthe LOCATION clause
   • Ethe ACCESS PARAMETERS clause

   Show answer

   Correct answer: B, D

   How others answered

   • BD80%
   • AD20%

   Explanation

   TYPE: "Oracle Database provides two access drivers: ORACLE_LOADER and ORACLE_DATAPUMP. If you omit the TYPE specification, ORACLE_LOADER is the default access driver." https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm#ADMIN11705 ACCESS PARAMETERS: "To modify the default behavior of the access driver for external tables, specify access parameters." https://docs.oracle.com/en/database/oracle/oracle-database/23/sutil/oracle-external-tables-concepts.html#GUID-ACF1D3AA-1D61-4682-AEC5-42C944756E12 REJECT LIMIT : there are several examples that don't use this clause LOCATION: "For ORACLE_HIVE, the LOCATION clause is not used." https://docs.oracle.com/en/database/oracle/oracle-database/23/sutil/oracle-external-tables-concepts.html#GUID-ACF1D3AA-1D61-4682-AEC5-42C944756E12 That about LOCATION confuse me, but I tend to believe it's a very particular case, so the answer should be (BD) DEFAULT DIRECTORY and LOCATION

   Adapted from community discussion by J4vi · 2023-05-06 · 4 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

   Supporting references

   • Oracle documentation
   • Oracle documentation

   View community discussion4

   Candidate context

   Question 9 discussion

   These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

   1. J

      J4vi

      2023-05-06

      Selected B, D4 upvotes

      TYPE: "Oracle Database provides two access drivers: ORACLE_LOADER and ORACLE_DATAPUMP. If you omit the TYPE specification, ORACLE_LOADER is the default access driver." https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm#ADMIN11705 ACCESS PARAMETERS: "To modify the default behavior of the access driver for external tables, specify access parameters." https://docs.oracle.com/en/database/oracle/oracle-database/23/sutil/oracle-external-tables-concepts.html#GUID-ACF1D3AA-1D61-4682-AEC5-42C944756E12 REJECT LIMIT : there are several examples that don't use this clause LOCATION: "For ORACLE_HIVE, the LOCATION clause is not used." https://docs.oracle.com/en/database/oracle/oracle-database/23/sutil/oracle-external-tables-concepts.html#GUID-ACF1D3AA-1D61-4682-AEC5-42C944756E12 That about LOCATION confuse me, but I tend to believe it's a very particular case, so the answer should be (BD) DEFAULT DIRECTORY and LOCATION

   2. T

      TofOra

      2024-02-03

      1 upvote

      D and E. B is WRONG, there is no need to define the DEFAULT DIRECTORY, you can specify each of the files with a DIRECTORY, like: create directory testsdir as 'c:\tests'; LOGFILE testsdir:'loggile.log' BADFILE testsdir:'badfile.log' LOCATION (testsdir:'invoice_data.txt') Tried to upload the full example but the site does not accept it, gives a Cloudflare error.

   3. S

      Sbablo

      2023-04-25

      2 upvotes

      E is also correct since it species the data source of the external tables.

   4. U

      ulkaaa

      2024-07-30

      2 upvotes

      A D TRUE BECAUSE IF WE USE LOCATION THEN WE CAN USE DEFAULT DIRECTORY

10. Question 10 of 25

    Select 2

    Which two are true about the precedence of operators and conditions? (Choose two.)

    • A|| has a higher order of precedence than + (addition).
    • B+ (addition) has a higher order of precedence than * (multiplication).
    • CNOT has a higher order of precedence than AND and OR in a condition.
    • DAND and OR have the same order of precedence in a condition.
    • EOperators are evaluated before conditions.

    Show answer

    Correct answer: C, E

    How others answered

    • CE67%
    • AC33%

    Only 67% agreed on this answer — treat it as contested rather than settled.

    Explanation

    The rules are listed in descending order: Parentheses (as well as brackets in lookup expressions and IN expressions). Note that you can freely add parentheses any time you want to impose an alternative precedence or to make precedence clearer. * / + - = <> = IS (IS NULL, IS NOT NULL, IS EMPTY, IS NOT EMPTY) BETWEEN NOT AND OR

    Adapted from community discussion by CyberP · 2024-05-26 · 11 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation
    • Oracle documentation

    View community discussion4

    Candidate context

    Question 10 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. C

       CyberP

       2024-05-26

       11 upvotes

       The rules are listed in descending order: Parentheses (as well as brackets in lookup expressions and IN expressions). Note that you can freely add parentheses any time you want to impose an alternative precedence or to make precedence clearer. * / + - = <> = IS (IS NULL, IS NOT NULL, IS EMPTY, IS NOT EMPTY) BETWEEN NOT AND OR

    2. L

       LuisLikes

       2025-02-20

       Selected C, E1 upvote

       || (OR) doesn't have a highter order of precedence than + (addition). Here I'll show you why: SELECT 1+1||'1.9' AS addition_operator FROM DUAL; --output: 21.9 SELECT '1.9'||1+1 AS or_condition FROM DUAL; --output: 2.91 In the first SELECT statement is first adding up 1+1 (2) and then concatenating with '1.9', making the output '21.9'. Lastly, the second SELECT statement concatenates '1.9' with 1 ('1.91') and then adding up 1, making the output '2.91'. This proves that, in this case, whatever comes first in the statement will be calculated. If there was an order of precedence, the outputs would've been: OR first: '12.9' (1+(1||'1.9')) and '2.91' (('1.91'||1)+1). + first: '21.9' ((1+1)||'1.9') and '1.92' ('1.9'||(1+1)). So the correct answers are C and E.

    3. H

       holdfaststrongly

       2023-09-20

       2 upvotes

       This Oracle doc explains between OR, AND, and NOT. https://docs.oracle.com/en/database/other-databases/nosql-database/22.2/sqlreferencefornosql/operator-precedence.html

    4. A

       alic_alex

       2024-03-03

       Selected C, E1 upvote

       https://docs.oracle.com/cd/E49933_01/server.770/es_eql/src/ceql_expr_precedence_rules.html#:~:text=The%20rules%20are,the%20JOIN%20operators.

11. Question 11 of 25

    User HR has CREATE SESSION, CREATE ANY TABLE and UNLIMITED TABLESPACE privileges. User SCOTT has CREATE SESSION, CREATE TABLE and UNLIMITED TABLESPACE privileges. HR successfully executes this statement:

    

    HR attempts to execute: 1. INSERT INTO scott.products VALUES (1, 'LAPTOP'); SCOTT attempts to execute: 2. SELECT * FROM products; 3. INSERT INTO scott.products VALUES (2, 'HDD'); 4. CREATE SYNONYM prod FOR products; Which will execute successfully?

    • A1, 2 and 3
    • B2, 3 and 4
    • C1 only
    • D2 and 3 only

    Show answer

    Correct answer: D

    How others answered

    • D78%
    • B22%

    Explanation

    Tested, you cannot create synonyms nor views without premission even if you are trying to do it for a object in your schema or if you want to use your schema table as a underlying table for view.

    Adapted from community discussion by AlexAFJ · 2023-08-31 · 2 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation

    View community discussion4

    Candidate context

    Question 11 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. A

       AlexAFJ

       2023-08-31

       Selected D2 upvotes

       Tested, you cannot create synonyms nor views without premission even if you are trying to do it for a object in your schema or if you want to use your schema table as a underlying table for view.

    2. H

       HICH9173

       2024-08-08

       2 upvotes

       To create a private synonym in your own schema, you must have the CREATE SYNONYM system privilege. To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM

    3. S

       Shrimathi

       2023-07-19

       2 upvotes

       option D Since scott has privileges create session,table and unlimited table space, the user has not rights to create synonym unless he has create synonym privilege given to scott.

    4. L

       lucemqy

       2023-11-15

       Selected D1 upvote

       D 2,3 only You need explicit permission to create synonym even in your own schema https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-SYNONYM.html#GUID-A806C82F-1171-478E-A910-F9C6C42739B2

12. Question 12 of 25

    Select 3

    Which three actions can you perform only with system privileges? (Choose three.)

    • AQuery any table in a database.
    • BExecute a procedure in another schema.
    • CLog in to a database instance.
    • DAccess flat files, which are stored in an operating system directory, via the UTL_FILE package.
    • EUse the WITH GRANT OPTION clause.

    Show answer

    Correct answer: A, B, C

    How others answered

    • ABC67%
    • ACD33%

    Only 67% agreed on this answer — treat it as contested rather than settled.

    Explanation

    D 'WARNING:The privileges needed to access files in a directory object are operating system specific. UTL_FILE directory object privileges give you read and write access to all files within the specified directory.'

    Adapted from community discussion by JUMP56 · 2024-02-28 · 2 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation

    View community discussion4

    Candidate context

    Question 12 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. B

       billysunday1

       2024-03-29

       Selected A, B, C1 upvote

       A. Query any table in a database. True. The ability to query any table across the database requires the SELECT ANY TABLE system privilege. This is a system-level permission and not specific to objects within a user's schema. B. Execute a procedure in another schema. True. To execute a procedure in another schema, a user would need the EXECUTE ANY PROCEDURE system privilege. While direct object privileges can be granted to execute a specific procedure, the system privilege is broader and not restricted to a single schema. C. Log in to a database instance. True. Logging in to a database instance requires the CREATE SESSION system privilege. This is a fundamental system privilege that grants the user the ability to connect to the database. Answer from GPT4

    2. J

       JUMP56

       2024-02-28

       2 upvotes

       D 'WARNING:The privileges needed to access files in a directory object are operating system specific. UTL_FILE directory object privileges give you read and write access to all files within the specified directory.'

    3. J

       JUMP56

       2024-02-28

       1 upvote

       A READ ANY TABLE system privilege allows a user to query tables, views, or materialized views in any schema in the database.

    4. J

       JUMP56

       2024-02-28

       1 upvote

       https://www.examtopics.com/discussions/oracle/view/8222-exam-1z0-071-topic-1-question-235-discussion/

13. Question 13 of 25

    Select 3

    Which three are true about privileges and roles? (Choose three.)

    • ASystem privileges always set privileges for an entire database.
    • BPUBLIC can be revoked from a user.
    • CAll roles are owned by the SYS schema.
    • DA user has all object privileges for every object in their schema by default.
    • EA role is owned by the user who created it.
    • FA role can contain a combination of several privileges and roles.
    • GPUBLIC acts as a default role granted to every user in a database.

    Show answer

    Correct answer: D, F, G

    How others answered

    • DFG80%
    • ADF20%

    Explanation

    Please ignore my previous answer. D:Who Can Grant Schema Object Privileges? A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object he or she owns to any other user or role. If the grant includes the GRANT OPTION (of the GRANT command), the grantee can further grant the object privilege to other users; otherwise, the grantee can use the privilege but cannot grant it to other users. https://docs.oracle.com/cd/A58617_01/server.804/a58227/ch18.htm F: https://docs.oracle.com/cd/A97630_01/server.920/a96521/privs.htm#:~:text=A%20role%20groups%20several%20privileges,to%20help%20in%20database%20administration. G: https://docs.oracle.com/cd/A97630_01/server.920/a96521/privs.htm#:~:text=Because%20PUBLIC%20is%20accessible%20to,requires%20the%20privilege%20or%20role.

    Adapted from community discussion by billysunday1 · 2024-04-05 · 3 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation
    • Oracle documentation

    View community discussion2

    Candidate context

    Question 13 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. B

       billysunday1

       2024-04-05

       Selected D, F, G3 upvotes

       Please ignore my previous answer. D:Who Can Grant Schema Object Privileges? A user automatically has all object privileges for schema objects contained in his or her schema. A user can grant any object privilege on any schema object he or she owns to any other user or role. If the grant includes the GRANT OPTION (of the GRANT command), the grantee can further grant the object privilege to other users; otherwise, the grantee can use the privilege but cannot grant it to other users. https://docs.oracle.com/cd/A58617_01/server.804/a58227/ch18.htm F: https://docs.oracle.com/cd/A97630_01/server.920/a96521/privs.htm#:~:text=A%20role%20groups%20several%20privileges,to%20help%20in%20database%20administration. G: https://docs.oracle.com/cd/A97630_01/server.920/a96521/privs.htm#:~:text=Because%20PUBLIC%20is%20accessible%20to,requires%20the%20privilege%20or%20role.

    2. B

       bfb7c7d

       2024-11-11

       1 upvote

       Answers are DFG in terms of G option ... The PUBLIC role is automatically granted to all users, providing a set of default privileges that every user in the database has.

14. Question 14 of 25

    Select 2

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

    • AUSER_TABLES displays all tables owned by the current user.
    • BYou must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES.
    • CAll users can query DBA_TABLES successfully.
    • DYou must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in DBA_TABLES.
    • EALL_TABLES displays all tables owned by the current user.
    • FYou must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES.

    Show answer

    Correct answer: A, F

    How others answered

    • AF100%

    Explanation

    AD , the right answers. The data dictionary views that begin with DBA_ are restricted. These views can be accessed only by users with the SELECT_ANY_TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed. https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SRF73/ch2a.htm

    Adapted from community discussion by Hrishi41 · 2023-09-15 · 2 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation

    View community discussion4

    Candidate context

    Question 14 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. R

       Rik92

       2023-01-25

       4 upvotes

       AD. Tried on ORA 19. GRANT SELECT ANY_TABLE to gives access to DBA_TABLES F is incorrect because querying ALL_TABLES you can always do but only see those tables you have the rights to view, which would be a subset of DBA_TABLES

    2. J

       jm9999

       2023-10-07

       4 upvotes

       granting select any table to my user did not allow him to query dba_tables

    3. H

       holdfaststrongly

       2022-09-21

       3 upvotes

       Don't get USER_TABLE and ALL_TABLE views confused. A) USER_TABLES displays all tables owned by the current user. (True) E) ALL_TABLES displays all tables owned by the current user. (False) The ALL_TABLES view contains description of tables accessible to the user.

    4. H

       HassanMkhlalati

       2023-09-11

       Selected A, F2 upvotes

       if you don't have dba role then you will get message that DBA_TABLES doesn't exist. DBA_TABLES is a data dictionary view

15. Question 15 of 25

    Select 2

    What is true about non-equijoin statement performance? (Choose two.)

    • AThe BETWEEN condition always performs less well than using the >= and <= conditions.
    • BThe BETWEEN condition always performs better than using the >= and <= conditions.
    • CThe Oracle join syntax performs better than the SQL:1999 compliant ANSI join syntax.
    • DTable aliases can improve performance.
    • EThe join syntax used makes no difference to performance.

    Show answer

    Correct answer: D, E

    How others answered

    • DE56%
    • CD39%

    Only 56% agreed on this answer — treat it as contested rather than settled.

    Explanation

    A B incorrect because Oracle Between is converted to >= <= C Is arguable D is not true, you don't find a book saying table alias can improve performance. E is arguable C E to answer this poorly designed question

    Adapted from community discussion by subtype11 · 2022-09-20 · 4 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation
    • Oracle documentation

    View community discussion4

    Candidate context

    Question 15 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. S

       subtype11

       2022-09-20

       4 upvotes

       A B incorrect because Oracle Between is converted to >= <= C Is arguable D is not true, you don't find a book saying table alias can improve performance. E is arguable C E to answer this poorly designed question

    2. A

       Antreu

       2022-10-25

       3 upvotes

       I read that aliases do indeed improve performance, ex. when you order by a column, the parser doesn't need to check all the tables in the query, to find which one the column belongs to. Can anyone confirm this?

    3. D

       dici79

       2025-01-06

       Selected D, E1 upvote

       A and B states ALWAYS - which is not always the case. C: The join syntax doesn't make much difference with performance. D: That's correct; it helps the optimizer E: this makes sense to me - The joint syntax doesn't make a difference.

    4. L

       Lee_jong_suk

       2023-12-08

       Selected D, E1 upvote

       Table alias makes it easy for the parser to look up the columns in their respective tables. "Join" or "Where T1.id=T2.id" conditions have no difference in performance, though join is recommended to be used as the new syntax.

16. Question 16 of 25

    Select 2

    Which two are true about granting object privileges on tables, views, and sequences? (Choose two.)

    • AINSERT can be granted only on tables and sequences.
    • BDELETE can be granted on tables, views, and sequences.
    • CSELECT can be granted on tables, views, and sequences.
    • DALTER can be granted only on tables and sequences.
    • EREFERENCES can be granted only on tables.

    Show answer

    Correct answer: C, D

    How others answered

    • CD76%
    • CE24%

    Explanation

    The following OBJECT PRIV'S can be granted on tables, views and sequences--- UPDATE: tables, views. REFERENCES: tables, views ALTER: tables, sequences. SELECT: tables, views, sequences

    Adapted from community discussion by holdfaststrongly · 2023-04-06 · 10 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation

    View community discussion4

    Candidate context

    Question 16 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. H

       holdfaststrongly

       2023-04-06

       Selected C, D10 upvotes

       The following OBJECT PRIV'S can be granted on tables, views and sequences--- UPDATE: tables, views. REFERENCES: tables, views ALTER: tables, sequences. SELECT: tables, views, sequences

    2. A

       alelejaja

       2024-11-27

       Selected C, D2 upvotes

       UPDATE: tables, views. REFERENCES: tables, views ALTER: tables, sequences. SELECT: tables, views, sequences. DELETE: tables, views. INSERT: tables, views. Doc: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/GRANT.html#GUID-20B4E2C0-A7F8-4BC8-A5E8-BE61BDC41AC3__BGBCIIEG

    3. C

       CyberP

       2023-11-26

       2 upvotes

       But answer E mentioned only on Table so E is clearly wrong :D It didnt mention and "materialised views" it said Tables "only" which is wrong. So i think C & D is correct.

    4. T

       TheOracleWasTaken

       2024-02-01

       1 upvote

       C and D because you can grant References on a view. Tried out! :)

17. Question 17 of 25

    Select 2

    Which two actions can you perform with object privileges? (Choose two.)

    • ACreate roles.
    • BCreate FOREIGN KEY constraints that reference tables in other schemas.
    • CDelete rows from tables in any schema except SYS.
    • DSet default and temporary tablespaces for a user.
    • EExecute a procedure or function in another schema.

    Show answer

    Correct answer: B, E

    How others answered

    • BE63%
    • BC37%

    Only 63% agreed on this answer — treat it as contested rather than settled.

    Explanation

    BE A : False. Creating role is system privilege. B : True. Use REFERENCE privilege. C : False. Use DELETE ANY TABLE system privilege. D : False? E : True. Use EXECUTE privilege.

    Adapted from community discussion by heoj10272 · 2022-10-22 · 16 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation

    View community discussion4

    Candidate context

    Question 17 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. H

       heoj10272

       2022-10-22

       16 upvotes

       BE A : False. Creating role is system privilege. B : True. Use REFERENCE privilege. C : False. Use DELETE ANY TABLE system privilege. D : False? E : True. Use EXECUTE privilege.

    2. A

       alic_alex

       2023-03-03

       Selected B, E7 upvotes

       B, E - correct see description in the table 7-2 https://docs.oracle.com/database/121/TTSQL/privileges.htm#TTSQL341

    3. B

       bhp12

       2023-04-16

       4 upvotes

       As per your source: Privilege: DELETE; Description: Enables a user to delete from a table.. Privilege: REFERENCES; Enables a user to create a foreign key dependency on a table or materialized view So, BC

    4. P

       paddy95

       2022-11-03

       2 upvotes

       A You must have the CREATE ROLE system privilege. B Object privileges REFERENCES object type Table or materialized view Enables a user to create a foreign key dependency on a table or materialized view. The REFERENCES privilege on a parent table implicitly grants SELECT privilege on the parent table C System privileges DELETE ANY TABLE Enables a user to delete from any table in the database. D I think it’s System privileges E Object privileges: Object type PL/SQL package, procedure or function; Enables a user to execute a PL/SQL package, procedure or function directly.

18. Question 18 of 25

    Select 2

    Which two are true? (Choose two.)

    • AADD_MONTHS adds a number of calendar months to a date.
    • BCEIL requires an argument which is a numeric data type.
    • CCEIL returns the largest integer less than or equal to a specified number.
    • DLAST_DAY returns the date of the last day of the current month only.
    • ELAST_DAY returns the date of the last day of the month for the date argument passed to the function.
    • FLAST_DAY returns the date of the last day of the previous month only.

    Show answer

    Correct answer: A, E

    How others answered

    • AE93%

    Explanation

    maby AE is Right... C. CEIL returns the largest integer less than or equal to a specified number. Can change : CEIL returns the minmum integer more than or equal to a specified number

    Adapted from community discussion by Peter_Chen · 2022-09-03 · 13 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation
    • Oracle documentation
    • Oracle documentation
    • Oracle documentation

    View community discussion4

    Candidate context

    Question 18 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. P

       Peter_Chen

       2022-09-03

       13 upvotes

       maby AE is Right... C. CEIL returns the largest integer less than or equal to a specified number. Can change : CEIL returns the minmum integer more than or equal to a specified number

    2. F

       fantastic_side_eye

       2024-09-27

       3 upvotes

       The two true statements are: AE Explanation: B. CEIL is an Oracle SQL function that requires an argument which is a numeric data type. This statement is false, as CEIL can also take input arguments of type DATE or TIMESTAMP. C. CEIL is an Oracle SQL function that returns the smallest integer greater than or equal to a specified number. This statement is false, as CEIL actually returns the largest integer less than or equal to a specified number. D. LAST_DAY is an Oracle SQL function that returns the date of the last day of the current month only. This statement is false, as LAST_DAY can be used with any date argument to return the last day of that month. E. This statement is true. LAST_DAY is an Oracle SQL function that can be used with a date argument to return the date of the last day of the month for that date. For example, LAST_DAY('2023-06-26') would return '2023-06-30'. F. This statement is false. LAST_DAY can be used with any date argument to return the last day of that month, not just the previous month.

    3. M

       MILIKURAN

       2022-09-06

       Selected A, E4 upvotes

       Answer: A, E "CEIL returns smallest integer greater than or equal to n. and can use any numeric or nonnumeric data type that can be implicit converted to numeric adta type" https://docs.oracle.com/database/121/SQLRF/functions025.htm#SQLRF00614

    4. O

       Orxan_H

       2023-04-16

       4 upvotes

       Why B incorrect: This function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type. The function returns the same data type as the numeric data type of the argument. https://docs.oracle.com/database/121/SQLRF/functions025.htm#SQLRF00614

19. Question 19 of 25

    Select 2

    Which two statements are true regarding indexes? (Choose two.)

    • AAn update to a table can result in updates to any or all of the table's indexes.
    • BAn update to a table can result in no updates to any of the table's indexes.
    • CA UNIQUE index can be altered to be non-unique.
    • DWhen a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped.
    • EA table belonging to one user cannot have an index that belongs to a different user.

    Show answer

    Correct answer: A, B

    How others answered

    • AB92%

    Explanation

    D is worng: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9012.htm When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table.

    Adapted from community discussion by Kuraudio · 2022-12-10 · 6 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation

    View community discussion4

    Candidate context

    Question 19 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. K

       Kuraudio

       2022-12-10

       6 upvotes

       D is worng: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9012.htm When you drop a table, any indexes on the table are dropped and put into the recycle bin along with the table. If subsequent space pressures arise, then the database reclaims space from the recycle bin by first purging indexes. In this case, when you flash back the table, you may not get back all of the indexes that were defined on the table.

    2. K

       Kashka

       2022-09-05

       5 upvotes

       Hi Averagenoob. You can flashback the table and restore the indexes, it just will be a different name, will it not?

    3. E

       Eltelwany

       2023-03-13

       3 upvotes

       I also think D is not accurate. Because when a table is dropped and moved to the recycle bin (if the recycle bin is enabled), any associated indexes on that table are also moved to the recycle bin. The recycle bin in Oracle holds dropped objects for a period of time, during which they can be restored if needed. However, the objects in the recycle bin are not permanently dropped until the bin is purged. If an index is dropped along with a table and is moved to the recycle bin, it is not permanently dropped until the recycle bin is purged.

    4. T

       TheOracleWasTaken

       2023-08-01

       Selected A, B3 upvotes

       a and b. I tried E out and you can create an index on someone else's table

20. Question 20 of 25

    Select 2

    Which two are true about queries using set operators (UNION, UNION ALL, INTERSECT and MINUS)? (Choose two.)

    • AThe name of each column in the first SELECT list must match the name of the corresponding column in each subsequent SELECT list.
    • BNone of the set operators can be used when selecting CLOB columns.
    • CThere must be an equal number of columns in each SELECT list.
    • DEach SELECT statement in the query can have an ORDER BY clause.
    • EThe FOR UPDATE clause cannot be specified.

    Show answer

    Correct answer: C, E

    How others answered

    • CE60%
    • BE40%

    Only 60% agreed on this answer — treat it as contested rather than settled.

    Explanation

    Hi, but in official doc from oracle, i found: The set operators are subject to the following restrictions: The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table. The UNION, INTERSECT, EXCEPT, and MINUS operators are not valid on LONG columns. If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause. You cannot also specify the for_update_clause with the set operators. You cannot specify the order_by_clause in the subquery of these operators. You cannot use these operators in SELECT statements containing TABLE collection expressions.

    Adapted from community discussion by alelejaja · 2024-05-29 · 3 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation
    • Oracle documentation

    View community discussion4

    Candidate context

    Question 20 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. A

       alelejaja

       2024-05-29

       3 upvotes

       Hi, but in official doc from oracle, i found: The set operators are subject to the following restrictions: The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table. The UNION, INTERSECT, EXCEPT, and MINUS operators are not valid on LONG columns. If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause. You cannot also specify the for_update_clause with the set operators. You cannot specify the order_by_clause in the subquery of these operators. You cannot use these operators in SELECT statements containing TABLE collection expressions.

    2. M

       MariGare

       2023-08-08

       1 upvote

       B should be included into the TRUE answer: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/The-UNION-ALL-INTERSECT-MINUS-Operators.html#GUID-B64FE747-586E-4513-945F-80CB197125EE The set operators are subject to the following restrictions: The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table. The UNION, INTERSECT, and MINUS operators are not valid on LONG columns. If the select list preceding the set operator contains an expression, then you must provide a column alias for the expression in order to refer to it in the order_by_clause. You cannot also specify the for_update_clause with the set operators. You cannot specify the order_by_clause in the subquery of these operators. You cannot use these operators in SELECT statements containing TABLE collection expressions.

    3. S

       Shrimathi

       2023-07-31

       2 upvotes

       CE is the answer None of the set operator can be used with CLOB -False The set operator which is excluding duplicate rows (intersect,minus,union) will fail to compare the LOB data type. Union all operator doesn't throw any error if it contains LOB datatype

    4. J

       johannfurma

       2023-12-30

       2 upvotes

       The docu states: "The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table."

21. Question 21 of 25

    Select 2

    Which two are true about the NVL, NVL2, and COALESCE functions? (Choose two.)

    • ANVL must have expressions of the same data type.
    • BNVL can have any number of expressions in the list.
    • CNVL2 can have any number of expressions in the list.
    • DCOALESCE stops evaluating the list of expressions when it finds the first non-null value.
    • EThe first expression in NVL2 is never returned.
    • FCOALESCE stops evaluating the list of expressions when it finds the first null value.

    Show answer

    Correct answer: D, E

    How others answered

    • DE79%
    • AD21%

    Explanation

    DE is correct: D. COALESCE stops evaluating the list of expressions when it finds the first non-null value. TRUE https://www.oracletutorial.com/oracle-comparison-functions/oracle-coalesce/ E. The first expression in NVL2 is never returned. TRUE If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL2.html)

    Adapted from community discussion by CMjer · 2023-11-15 · 2 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation
    • Oracle documentation

    View community discussion4

    Candidate context

    Question 21 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. H

       HassanMkhlalati

       2024-05-04

       Selected D, E4 upvotes

       A: NVL, does implicit conversion. Could have expressions of different datatype

    2. C

       CMjer

       2023-11-15

       Selected D, E2 upvotes

       DE is correct: D. COALESCE stops evaluating the list of expressions when it finds the first non-null value. TRUE https://www.oracletutorial.com/oracle-comparison-functions/oracle-coalesce/ E. The first expression in NVL2 is never returned. TRUE If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. (https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NVL2.html)

    3. L

       lucemqy

       2024-05-13

       Selected D, E3 upvotes

       First expression of NVL2 is never returned, it's only used for evaluation.

    4. C

       CyberP

       2023-11-27

       3 upvotes

       nvl2(expression1,expression2,expression3) so even expression2 = expression1 we will not say expression1 is returned A: we can implicitly use different type of data with nvl select nvl(1,'1') from dual; it will work so A is incorrect D,E is correct.

22. Question 22 of 25

    Select 3

    An Oracle Database session has an uncommitted transaction in progress which updated 5000 rows in a table. In which three situations does the transaction complete thereby committing the updates? (Choose three.)

    • Awhen a CREATE TABLE AS SELECT statement is issued in the same session but fails with a syntax error
    • Bwhen a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
    • Cwhen the session logs out successfully
    • Dwhen a CREATE INDEX statement is executed successfully in the same session
    • Ewhen a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
    • Fwhen a COMMIT statement is issued by the same user from another session in the same database instance

    Show answer

    Correct answer: B, C, D

    How others answered

    • BCD91%

    Explanation

    B should be correct, because the session will be killed after the last transaction will be committed or the user will use rollback... A is incorrect because the statement failed with a syntax error

    Adapted from community discussion by iuliana23 · 2023-10-27 · 2 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation

    View community discussion4

    Candidate context

    Question 22 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. I

       iuliana23

       2023-10-27

       Selected B, C, D2 upvotes

       B should be correct, because the session will be killed after the last transaction will be committed or the user will use rollback... A is incorrect because the statement failed with a syntax error

    2. P

       Peter_Chen

       2023-09-24

       3 upvotes

       why is not B? C used SQLPLUS is right but use sql developer ....Maybe not

    3. J

       jm9999

       2024-10-08

       Selected B, C, D2 upvotes

       For A to be true the statement would have to be syntactically valid.

    4. R

       ramonciks

       2024-09-22

       1 upvote

       Should be BCD: A - FALSE - it is syntax error therefore does not implicitly commit: Oracle Database issues an implicit COMMIT under the following circumstances: Before any syntactically valid data definition language (DDL) statement, even if the statement results in an error After any data definition language (DDL) statement that completes without an errorhttps://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/COMMIT.html#GUID-6CD5C9A7-54B9-4FA2-BA3C-D6B4492B9EE2

23. Question 23 of 25

    Select 2

    Which two are true about using constraints? (Choose two.)

    • ANOT NULL can be specified at the column and at the table level.
    • BA table can have only one PRIMARY KEY and one FOREIGN KEY constraint.
    • CA FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parent table must have the same names.
    • DPRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level.
    • EA table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.
    • FA table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

    Show answer

    Correct answer: D, F

    How others answered

    • DF100%

    Explanation

    Is the way you specify the constraints syntactically. There are two ways... by column or by table (at the end of the statement). PK and FK can be defined in both.

    Adapted from community discussion by J4vi · 2024-05-11 · 2 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation

    View community discussion4

    Candidate context

    Question 23 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. S

       Sidoogle

       2024-02-01

       3 upvotes

       Its exception that not null constrain is not allowed to be defined at table level.

    2. R

       Roneros

       2024-09-11

       Selected D, F2 upvotes

       https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj13590.html

    3. J

       J4vi

       2024-05-11

       2 upvotes

       Is the way you specify the constraints syntactically. There are two ways... by column or by table (at the end of the statement). PK and FK can be defined in both.

    4. L

       lucemqy

       2024-11-14

       Selected D, F1 upvote

       Not NULL constraints can only be set at column level not table level. Each table have only have one primary key constraint with multiple foreign key constraints

24. Question 24 of 25

    Select 2

    Which two are true about queries using set operators such as UNION? (Choose two.)

    • AIn a query containing multiple set operators, INTERSECT always takes precedence over UNION and UNION ALL.
    • BAn expression in the first SELECT list must have a column alias for the expression.
    • CAll set operators are valid on columns of all data types.
    • DCHAR columns of different lengths used with a set operator return a VARCHAR2 whose length equals the longest char value.
    • EQueries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric).

    Show answer

    Correct answer: D, E

    How others answered

    • DE87%

    Explanation

    According to: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm "If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value." AND "In queries using set operators, Oracle does not perform implicit conversion across datatype groups. " So DE.

    Adapted from community discussion by anzac · 2022-12-08 · 4 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation
    • Oracle documentation

    View community discussion4

    Candidate context

    Question 24 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. A

       anzac

       2022-12-08

       4 upvotes

       According to: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm "If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value." AND "In queries using set operators, Oracle does not perform implicit conversion across datatype groups. " So DE.

    2. A

       acd3ea6

       2024-09-26

       Selected D, E2 upvotes

       https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/The-UNION-ALL-INTERSECT-MINUS-Operators.html#GUID-B64FE747-586E-4513-945F-80CB197125EE

    3. J

       jfc1

       2023-01-18

       Selected D, E1 upvote

       B is incorrect because it is not mandatory to have an alias for each column in the first SELECT select first_name ||''||last_name, employee_id from hr.employees union select first_name,employee_id from HR.EMPLOYEES order by 1,2; C is incorrect because the datatypes : BLOC,CLOB are not valid the set Operators. DE is the correct answer.

    4. O

       Orxan_H

       2023-04-21

       2 upvotes

       B may be correct if in the end will be "order by" clause. Therefore DE correct

25. Question 25 of 25

    Select 2

    Examine this command: TRUNCATE TABLE test; Table truncated. Which two are true? (Choose two.)

    • AThe structure of the TEST table is removed.
    • BAll the indexes on the TEST table are dropped.
    • CAll the constraints on the TEST table are dropped.
    • DRemoved rows can not be recovered using the ROLLBACK command.
    • EAll the rows in the TEST table are removed.

    Show answer

    Correct answer: D, E

    How others answered

    • DE100%

    Explanation

    DE is correct A. The structure of the TEST table is removed. wrong, only content will get removed B. All the indexes on the TEST table are dropped. indexes will stay C. All the constraints on the TEST table are dropped. wrong D. Removed rows can not be recovered using the ROLLBACK command. true E. All the rows in the TEST table are removed. true

    Adapted from community discussion by Beesho · 2023-01-15 · 2 community upvotes. Third-party contribution; reviewed by our quality filter, not independently verified.

    Supporting references

    • Oracle documentation

    View community discussion4

    Candidate context

    Question 25 discussion

    These are quality-filtered excerpts from third-party community discussion. They can be useful reasoning signals, but they are not official answers and have not been independently verified by 1Z0-071 or CertExamHQ.

    1. B

       Beesho

       2023-01-15

       2 upvotes

       DE is correct A. The structure of the TEST table is removed. wrong, only content will get removed B. All the indexes on the TEST table are dropped. indexes will stay C. All the constraints on the TEST table are dropped. wrong D. Removed rows can not be recovered using the ROLLBACK command. true E. All the rows in the TEST table are removed. true

    2. J

       jfc1

       2023-01-20

       Selected D, E2 upvotes

       https://docs.oracle.com/database/121/SQLRF/statements_10007.htm#SQLRF01707 DE is correct answer

    3. H

       HassanMkhlalati

       2023-09-14

       Selected D, E1 upvote

       The structure of the TEST table is still available. It is dropped only when drop the table

    4. C

       c1a0049

       2024-09-04

       Selected D, E1 upvote

       D, E - TRUNCATE only affects rows(data), not table definition.

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

1Z0-071 practice, question 1 of 25