1Z0-082 practice questions 31–40

1. Question 31 · 167 in bank

   Select 3

   Which three statements are true about Deferred Segment Creation in Oracle databases? (Choose three.)

   • AIt is supported for Index Organized Tables (IOTs) contained in locally managed tablespaces.
   • BIt is the default behavior for tables and indexes.
   • CSessions may dynamically switch back and forth from DEFERRED to IMMEDIATE segment creation.
   • DIndexes inherit the DEFERRED to IMMEDIATE segment creation attribute from their parent table
   • EIt is supported for SYS-owned tables contained in locally managed tablespaces.

   Show answer

   Answer: BCD

   References

   • Oracle documentation

   Next question

2. Question 32 · 167 in bank

   Select 3

   Examine these statements executed in a single Oracle session:

   

   Which three statements are true? (Choose three.)

   • AThe code for pen is 10.
   • BThere is no row containing fountain pen.
   • CThere is no row containing pen.
   • DThe code for fountain pen is 3.
   • EThe code for pen is l.
   • FThere is no row containing pencil.

   Show answer

   Answer: ADF

   Explanation

   Correct answer are: A,D,F. Before SAVEPOINT b; command was issued, content of the table is: SQL> SELECT * FROM product; PCODE PNAME ------ -------------------- 10 pen 3 fountain pen The command "ROLLBACK TO SAVEPOINT b; " returns the table to the state created by the SAVEPOINT b;

   Source: Adapted from community discussion — nautil2

   Next question

3. Question 33 · 167 in bank

   Examine the description of the SALES1 table:

   

   SALES2 is a table with the same description as SALES1. Some sales data is contained erroneously in both tables. You must display rows from SALES1 and SALES2 and wish to see the duplicates too. Which set operator generates the required output?

   • AUNION ALL
   • BSUBTRACT
   • CMINUS
   • DUNION
   • EINTERSECT

   Show answer

   Answer: A

   References

   • Oracle documentation

   Next question

4. Question 34 · 167 in bank

   You want to write a query that prompts for two column names and the WHERE condition each time it is executed in a session but only prompts for the table name the first time it is executed. The variables used in your query are never undefined in your session. Which query can be used?

   • ASELECT &&col1, &&col2 FROM &table WHERE &&condition = &&cond;
   • BSELECT &col1, &col2 FROM &&table WHERE &condition;
   • CSELECT &col1, &col2 FROM ג€&tableג€ WHERE &condition;
   • DSELECT '&&col1', '&&col2' FROM &table WHERE '&&condition' = '&cond';
   • ESELECT &&col1, &&col2 FROM &table WHERE &&condition;

   Show answer

   Answer: B

   Explanation

   Correct answer: B Tested in SQL-Developer, col1, col2 and condition will be prompted everytime, while table will only be prompted the first time you execute this statement

   Source: Adapted from community discussion — adoptc94

   Next question

5. Question 35 · 167 in bank

   Select 2

   In the promotions table, the PROMO_BEGIN_DATE column is of data type DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a query? (Choose two.)

   • ATO_NUMBER(PROMO_BEGIN_DATE) - 5 will return a number.
   • BPROMO_BEGIN_DATE - SYSDATE will return an error.
   • CPROMO_BEGIN_DATE - SYSDATE will return a number.
   • DPROMO_BEGIN_DATE - 5 will return a date.
   • ETO_DATE(PROMO_BEGIN_DATE * 5) will return a date.

   Show answer

   Answer: CD

   Next question

6. Question 36 · 167 in bank

   Select 2

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

   • AA non-unique index can be altered to be unique.
   • BAn update to a table can result in no updates to any of the table's indexes.
   • CThe RECYCLE BIN never contains indexes.
   • DAn update to a table can result in updates to any or all of the table's indexes.
   • EA table belonging to one user cannot have an index that belongs to a different user.

   Show answer

   Answer: BD

   Next question

7. Question 37 · 167 in bank

   Select 3

   Which three statements are true regarding single row subqueries? (Choose three.)

   • AA SQL statement may have multiple single row subquery blocks.
   • BThey can be used in the WHERE clause.
   • CThey must be placed on the left side of the comparison operator or condition.
   • DThey can be used in the HAVING clause.
   • EThey must be placed on the right side of the comparison operator or condition.
   • FThey must return a row to prevent errors in the SQL statement.

   Show answer

   Answer: ABD

   Explanation

   ABD seems to be correct. A,B: TRUE SELECT column1, (SELECT MAX(column2) FROM table1) AS max_value, (SELECT COUNT(*) FROM table2) AS row_count FROM table3 WHERE column4 = (SELECT MIN(column4) FROM table4); C: FALSE - column name is placed on the left side of the condition/operator, while subquery is placed on the right side of it. D: TRUE SELECT deptno, MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal) > ( SELECT MIN(sal) FROM emp WHERE deptno = 30); E: FALSE, a subquery does not need to be surrounded by a condition or operator, see example for A+B answer F: FALSE, single-row subquery can return no row without raising an error SELECT ename, init, bdate FROM employees WHERE bdate > (SELECT bdate FROM employees WHERE empno = 99999);

   Source: Adapted from community discussion — nautil2

   Next question

8. Question 38 · 167 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.
   • CA synonym can be created on an object in a package.
   • DAll private synonym names must be unique in the database.
   • EA synonym can have a synonym.

   Show answer

   Answer: BE

   Explanation

   A: FALSE, a user needs privilege CREATE PUBLIC SYNONYM to do it B: TRUE SQL> SELECT owner, object_type, object_name, object_id FROM dba_objects WHERE object_id=20770; OWNER OBJECT_TYPE OBJECT_NAME OBJECT_ID -------------------- ----------- ---------------- ---------- GSMADMIN_INTERNAL SYNONYM DDL_REQUESTS_PWD 20770 C: FALSE; D: FALSE SQL> SELECT object_name, object_type, object_id, owner FROM dba_objects WHERE object_type='SYNONYM' AND owner LIKE 'KOTELNIK%'; OBJECT_NAME OBJECT_TYPE OBJECT_ID OWNER --------------- ----------- ---------- ----------- EMPLOYEES_S SYNONYM 76119 KOTELNIK1 EMPLOYEES_S SYNONYM 76128 KOTELNIK2E: E: TRUE, https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-SYNONYM.html#GUID-A806C82F-1171-478E-A910-F9C6C42739B2 Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for ... another synonym

   Source: Adapted from community discussion — nautil2

   References

   • Oracle documentation

   Next question

9. Question 39 · 167 in bank

   Select 2

   Examine this command: SQL> ALTER TABLE ORDERS SHRINK SPACE COMPACT Which two statements are true? (Choose two.)

   • AThe SHRINK operation causes rows to be moved to empty space starting from the beginning of the ORDERS segment.
   • BQueries and DML statements are allowed on ORDERS while the SHRINK is executing.
   • CDependent indexes become UNUSABLE.
   • DOnly queries are allowed on ORDERS while the shrink is executing.
   • EThe high-water mark (HWM) of ORDERS is adjusted.
   • FThe SHRINK operation causes rows to be moved to empty space starting toward the end of the ORDERS segment.

   Show answer

   Answer: AB

   References

   • Oracle documentation
   • Oracle documentation

   Next question

10. Question 40 · 167 in bank

    Select 2

    The sales table has columns PROD_ID and QUANTITY_SOLD of data type number. Which two queries execute successfully? (Choose two.)

    • ASELECT COUNT(prod_id) FROM sales WHERE quantity_sold > 55000 GROUP BY prod_id;
    • BSELECT COUNT(prod_id) FROM sales GROUP BY prod_id WHERE quantity_sold > 55000;
    • CSELECT prod_id FROM sales WHERE quantity_sold > 55000 AND COUNT(*) > 10 GROUP BY COUNT(*) > 10;
    • DSELECT prod_id FROM sales WHERE quantity_sold > 55000 GROUP BY proa_id HAVING COUNT(*) > 10;
    • ESELECT prcd_id FROM sales WHERE quantity_sold > 55000 AND COUNT (*) > 10 GROUP BY prod_id HAVING COUNT(*) > 10;

    Show answer

    Answer: AD

    Next question

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

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