1Z0-082 practice questions 131–140

1. Question 131 · 167 in bank

   Select 2

   Which two are true about a SQL statement using SET operators such as UNION? (Choose two.)

   • AThe data type group of each column returned by the second query must match the data type of the corresponding column returned by the first query.
   • BThe number, but not names, of columns must be identical for all SELECT statements in the query.
   • CThe data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.
   • DThe names and number of columns must be identical for all SELECT statements in the query.
   • EThe data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.

   Show answer

   Answer: AB

   Explanation

   I would also say A and B - "...Tables must have the same number of columns presented in the same order. In addition, the data type of the corresponding column must be in the same data type group such as number or character..."

   Source: Adapted from community discussion — Rivaldo11

   References

   • Oracle documentation
   • Oracle documentation

   Next question

2. Question 132 · 167 in bank

   Select 3

   Which three statements are true about time zones, date data types, and timestamp data types in an Oracle database? (Choose three.)

   • AThe CURRENT_TIMESTAMP function returns data without time zone information
   • BA TIMESTAMP WITH LOCAL TIMEZONE data type column is stored in the database using the time zone of the session that inserted the row
   • CA TIMESTAMP data type column contains information about year, month, and day
   • DThe DBTIMEZONE function can return an offset from Universal Coordinated Time (UTC)
   • EThe SESSIONTIMEZONE function can return an offset from Universal Coordinated Time (UTC)

   Show answer

   Answer: CDE

   Explanation

   C D E: "TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP that is sensitive to time zone information. It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone information is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone." So, the data is stored with the DB Timezone, even if it's retrieved with the session timezone. A is clearly wrong by example, so the rest are true. With D and E we can also have an easy sample, while for C, which is apparently the most ambiguous, I agree with whom was saying that the TIMESTAMP data type contains indeed informations about day, month and year, and in the answer is never stated that they are the only ones present. So as it's put, it's definitely right. https://docs.oracle.com/database/121/NLSPG/ch4datetime.htm#GUID-CD2954CE-45E2-4938-A599-CCB96879510F

   Source: Adapted from community discussion — SimoneF

   References

   • Oracle documentation
   • Oracle documentation

   Next question

3. Question 133 · 167 in bank

   Select 3

   Which three statements are true about inner and outer joins? (Choose three.)

   • AA full outer join returns matched and unmatched rows
   • BAn inner join returns matched rows
   • COuter joins can only be used between two tables per query
   • DA full outer join must use Oracle syntax
   • EOuter joins can be used when there are multiple join conditions on two tables
   • FA left or right outer join returns only unmatched rows

   Show answer

   Answer: ABE

   Explanation

   The three statements that are true about inner and outer joins are: A. A full outer join returns matched and unmatched rows. B. An inner join returns matched rows. E. Outer joins can be used when there are multiple join conditions on two tables. The other statements are false: C. Outer joins can only be used between two tables per query. Outer joins can be used between any number of tables per query. D. A full outer join must use Oracle syntax. Full outer joins can be implemented in any SQL database, not just Oracle. F. A left or right outer join returns only unmatched rows. A left or right outer join returns all rows from the left or right table, respectively, even if there are no matching rows in the other table.

   Source: Adapted from community discussion — Lalala8

   Next question

4. Question 134 · 167 in bank

   In the spfile of a single instance database, LOCAL_LISTENER is set to LISTENER_1. The TNSNAMES.ORA file in $ORACLE_HOME/network/admin in the database home contains:

   

   Which statement is true?

   • ADynamic service registration cannot be used for this database instance
   • BThe LREG process registers services dynamically with the LISTENER_1 listener
   • CLISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registration
   • DThere are two listeners named LISTENER and LISTENER_1 running simultaneously using port 1521 on the same host as the database instances
   • EThe definition for LISTENER_1 requires a CONNECT_DATA section to enable dynamic service registration

   Show answer

   Answer: B

   Explanation

   Answer is B. The listener forwards client requests to supported services. These services are dynamically registered with the listener. This dynamic registration feature is called service registration. The registration is performed by the Listener Registration (LREG) process. Dynamic service registration does not require any manual configuration in the listener.ora file. Reference: https://docs.oracle.com/database/121/NETAG/listenercfg.htm#NETAG292

   Source: Adapted from community discussion — Muhab

   References

   • Oracle documentation
   • Oracle documentation
   • Oracle documentation

   Next question

5. Question 135 · 167 in bank

   Select 2

   Examine this description of the TRANSACTIONS table:

   

   Which two SQL statements execute successfully? (Choose two.)

   • ASELECT customer_id AS ג€CUSTOMER-IDג€, transaction_date AS DATE, amount + 100 ג€DUESג€ FROM transactions;
   • BSELECT customer_id AS ג€CUSTOMER-IDג€, transaction_date AS ג€DATEג€, amount + 100 DUES FROM transactions;
   • CSELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 ג€DUES AMOUNTג€ FROM transactions;
   • DSELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions;
   • ESELECT customer_id AS 'CUSTOMER-ID', transaction_date AS DATE, amount + 100 'DUES AMOUNT' FROM transactions;

   Show answer

   Answer: BD

   Explanation

   A. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS DATE, amount + 100 "DUES" FROM transactions; B. SELECT customer_id AS "CUSTOMER-ID", transaction_date AS "DATE", amount + 100 DUES FROM transactions; C. SELECT customer_id AS CUSTOMER-ID, transaction_date AS TRANS_DATE, amount + 100 "DUES AMOUNT" FROM transactions; D. SELECT customer_id CUSTID, transaction_date TRANS_DATE, amount + 100 DUES FROM transactions; E. SELECT customer_id AS ‘CUSTOMER-ID’, transaction_date AS DATE, amount + 100 ‘DUES AMOUNT’ FROM transactions;

   Source: Adapted from community discussion — zouve

   Next question

6. Question 136 · 167 in bank

   Select 2

   The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER. Which two queries execute successfully? (Choose two.)

   • ASELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers;
   • BSELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers;
   • CSELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;
   • DSELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers;
   • ESELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers;

   Show answer

   Answer: CE

   Explanation

   A) SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers; B) SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers; C) SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers; D) SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers; E) SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers; Correct C & E.

   Source: Adapted from community discussion — you1234

   Next question

7. Question 137 · 167 in bank

   Which statement is true about aggregate functions?

   • AAggregate functions can be nested to any number of levels
   • BThe AVG function implicitly converts NULLS to zero
   • CAggregate functions can be used in any clause of a SELECT statement
   • DThe MAX and MIN functions can be used on columns with character data types

   Show answer

   Answer: D

   Explanation

   A is wrong. Aggregates are not allowed on an outer query. D is correct MAX is allowed only on expressions that evaluate to built-in data types (including CHAR, VARCHAR, DATE, TIME, CHAR FOR BIT DATA, etc.). https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj27781.html#:~:text=MAX%20is%20an%20aggregate%20function,BIT%20DATA%2C%20etc.). https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj33923.html#rrefsqlj33923

   Source: Adapted from community discussion — Lingy

   References

   • Oracle documentation
   • Oracle documentation

   Next question

8. Question 138 · 167 in bank

   Select 2

   Your database instance was shut down normally and then started in NOMOUNT state. You then execute this command: ALTER DATABASE MOUNT; Which two actions are performed? (Choose two.)

   • AThe online redo logs are opened
   • BThe online data files are opened
   • CThe alert log records the execution details
   • DThe Oracle background processes are started
   • EThe initialization parameter file is read
   • FThe control file is read

   Show answer

   Answer: CF

   Explanation

   A. The online redo logs are opened - OPEN B. The online data files are opened - OPEN C. The alert log records the execution details - MOUNT D. The Oracle background processes are started - NOMOUNT E. The initialization parameter file is read - NOMOUNT F. The control file is read - MOUNT

   Source: Adapted from community discussion — baeji

   Next question

9. Question 139 · 167 in bank

   Select 2

   Which two are true about shrinking a segment online? (Choose two.)

   • AIt is not possible to shrink either indexes or Index Organized Tables (IOTs)
   • BIt always eliminates all migrated rows if any exist in the table
   • CTo shrink a table it must have a PRIMARY KEY constraint
   • DTo shrink a table it must have a UNIQUE KEY constraint
   • ETo shrink a table it must have row movement enabled
   • FIt must be in a tablespace that uses Automatic Segment Space Management (ASSM)

   Show answer

   Answer: EF

   Explanation

   The two statements that are true about shrinking a segment online are: E. To shrink a table it must have row movement enabled F. It must be in a tablespace that uses Automatic Segment Space Management (ASSM) The other statements are false: A. It is not possible to shrink either indexes or Index Organized Tables (IOTs) - It is possible to shrink indexes and IOTs, but it is more complex than shrinking a table. B. It always eliminates all migrated rows if any exist in the table - Shrinking a segment does not always eliminate all migrated rows. Only migrated rows that are no longer referenced by any other rows will be eliminated. C. To shrink a table it must have a PRIMARY KEY constraint - It is not necessary for a table to have a PRIMARY KEY constraint to be shrunk. D. To shrink a table it must have a UNIQUE KEY constraint - It is not necessary for a table to have a UNIQUE KEY constraint to be shrunk.

   Source: Adapted from community discussion — Lalala8

   Next question

10. Question 140 · 167 in bank

    Select 2

    Examine these commands:

    

    Which two statements are true about the sqlldr execution? (Choose two.)

    • AIt overwrites data in EMP with data in EMP.DAT
    • BIt uses the database buffer cache to load data
    • CIt generates a log that contains control file entries, which can be used with normal SQL*Loader operations
    • DIt generates a sql script that it uses to load data from EMP.DAT to EMP
    • EIt appends data from EMP.DAT to EMP

    Show answer

    Answer: CE

    Explanation

    C & E are correct - ..."SQL*Loader express mode generates a log file that includes a SQL*Loader control file. The log file also contains SQL scripts for creating the external table and performing the load using a SQL INSERT AS SELECT statement. Neither the control file nor the SQL scripts are used by SQL*Loader express mode. They are made available to you in case you want to use them as a starting point to perform operations using regular SQL*Loader or standalone external tables; the control file is for use with SQL*Loader, whereas the SQL scripts are for use with standalone external tables operations."

    Source: Adapted from community discussion — Rivaldo11

    References

    • Oracle documentation

    Next question

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

1Z0-082 practice, question 131. 167 questions available.