1Z0-082 practice questions 61–70

1. Question 61 · 167 in bank

   Select 2

   Which two statements are true about the ORDER BY clause? (Choose two.)

   • AOnly columns that are specified in the SELECT list can be used in the ORDER BY clause.
   • BNumeric values are displayed in descending order if they have decimal positions.
   • CNULLS are not included in the sort operation.
   • DColumn aliases can be used in the ORDER BY clause.
   • EIn a character sort, the values are case-sensitive.

   Show answer

   Answer: DE

   Explanation

   A – false; following queries are executed correctly: SELECT * FROM employees ORDER BY 1; SELECT emp_name, dept_id FROM employees ORDER BY emp_id; B – false; no mention in Oracle documentation about numeric values order C – false; Quoting Oracle documentation - options NULLS FIRST | NULLS LAST Specify whether returned rows containing null values should appear first or last in the ordering sequence. NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order. D – true; example: SELECT emp_name AS en, dept_id FROM employees ORDER BY en; E – true; example: SQL> SELECT emp_id,emp_name FROM employees WHERE UPPER(emp_name)='JONES' ORDER BY emp_name ASC; EMP_ID EMP_NAME ---------- ---------- 1 JONES 7 JONES 10 JOnes 9 Jones

   Source: Adapted from community discussion — nautil2

   Next question

2. Question 62 · 167 in bank

   Select 2

   Examine this query:

   

   Which two methods should you use to prevent prompting for a hire date value when this query is executed? (Choose two.)

   • AStore the query in a script and pass the substitution value to the script when executing it.
   • BExecute the SET verify off command before executing the query.
   • CUse the undefine command before executing the query.
   • DExecute the SET VERIFY ON command before executing the query.
   • EReplace '&1' with '&&1' in the query.
   • FUse the define command before executing the query.

   Show answer

   Answer: AF

   Explanation

   AF A-TRUE B-FALSE , has another function C False, not exist undefine command, only set define off D-False, set verify on will ensure the "old" and "new" lines appear when using a defined variable E False ,I have tried it and it asks to enter a value F-TRUE,if you use define to change the input parameter

   Source: Adapted from community discussion — Oracle2020

   Next question

3. Question 63 · 167 in bank

   Select 3

   You issued this command: DROP TABLE hr.employees; Which three statements are true? (Choose three.)

   • AAll indexes defined on HR.EMPLOYEES are dropped
   • BSynonyms for HR.EMPLOYEES are dropped.
   • CThe HR.EMPLOYEES table may be moved to the recycle bin.
   • DSequences used to populate columns in the HR.EMPLOYEES table are dropped.
   • EAll constraints defined on HR.EMPLOYEES are dropped.
   • FViews referencing HR.EMPLOYEES are dropped.

   Show answer

   Answer: ACE

   Explanation

   Correct answers: A. All indexes defined on HR.EMPLOYEES are dropped C. The HR.EMPLOYEES table may be moved to the recycle bin. E. All constraints defined on HR.EMPLOYEES are dropped.

   Source: Adapted from community discussion — sobrinho

   Next question

4. Question 64 · 167 in bank

   Select 2

   Examine the data in the CUST_NAME column of the CUSTOMERS table:

   

   You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result? (Choose two.)

   • AWHERE UPPER (SUBSTR (cust_name, INSTR (cust_name, ' ') + 1)) LIKE UPPER ('MC%')
   • BWHERE SUBSTR(cust_name, INSTR(cust_name, ' ') + 1 ) LIKE 'Mc%' OR 'MC%'
   • CWHERE SUBSTR(cust_name, INSTR(cust_name, ' ') +1) LIKE 'Mc%'
   • DWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') +1)) LIKE 'Mc%'
   • EWHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name, ' ') +1)) IN (MC%', 'Mc%)

   Show answer

   Answer: AD

   Next question

5. Question 65 · 167 in bank

   Select 3

   Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

   • AA primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.
   • BAn UNUSED column's space is reclaimed automatically when the block containing that column is next queried.
   • CAn UNUSED column's space is reclaimed automatically when the row containing that column is next queried.
   • DPartition key columns cannot be dropped.
   • EA DROP COLUMN command can be rolled back
   • FA column that is set to UNUSED still counts towards the limit of 1000 columns per table

   Show answer

   Answer: ADF

   Explanation

   • Answer A is RIGHT: Oders is Parent table with PRIMARY KEY ord_no, Order_items is child table which ord_no is REFERENCE KEY that reference ord_no of Parent table, now drop PRIMARY KEY on Orders by command: ALTER TABLE orders DROP COLUMN ORD_NO CASCADE CONSTRAINTS; • Answer D is RIGHT: ORA-12984: cannot drop partitioning column • Answer F is RIGHT: Unused Columns Count against 1000-column Table Limit Causing ORA-01792 on Compressed Table (Doc ID 2259600.1) and "ORA-01792: Maximum Number Of Columns In A Table Or View Is 1000" AND HIDDEN_COLUMN name is recreated with date and timestamp (Doc ID 2624150.1)

   Source: Adapted from community discussion — dotruonghan

   References

   • Oracle documentation

   Next question

6. Question 66 · 167 in bank

   Select 2

   Which two statements are true about segment types in an Oracle Database? (Choose two.)

   • ATemporary segments are only stored in a temporary tablespace.
   • BIndex segments always have two or more extents.
   • CUndo segments are only stored in an undo tablespace.
   • DCluster segments may contain data from multiple tables.
   • ETable segments always have two or more extents.

   Show answer

   Answer: AD

   Explanation

   Answer should be AD Why C is incorrect: When the database instance starts, the database automatically selects the first available undo tablespace. If no undo tablespace is available, then the instance starts without an undo tablespace and stores undo records in the SYSTEM tablespace https://docs.oracle.com/en/database/oracle/oracle-database/21/admin/managing-undo.html#GUID-281C5BFE-BAED-47C3-BFF7-806C0954F3EC

   Source: Adapted from community discussion — cf1f99b

   References

   • Oracle documentation

   Next question

7. Question 67 · 167 in bank

   Select 3

   Which three tasks are performed by background processes in an Oracle database instance? (Choose three.)

   • Awriting dirty database block images from the buffer cache
   • Breading database blocks into the buffer cache
   • Ccreating dedicated server connections
   • Dwriting redo to log files
   • Eregistering services with Oracle Net listeners

   Show answer

   Answer: ABD

   Next question

8. Question 68 · 167 in bank

   Select 3

   Which three statements are true about views in an Oracle Database? (Choose three.)

   • ARows inserted into a table using a view are retained in the table if the view is dropped.
   • BViews can join tables only if they belong to the same schema.
   • CViews have no object number.
   • DViews have no segment.
   • EA SELECT statement cannot contain a WHERE clause when querying a view containing a WHERE clause in its defining query.
   • FA view can be created that refers to a non-existent table in its defining query.

   Show answer

   Answer: ADF

   Next question

9. Question 69 · 167 in bank

   Select 3

   Which three statements are true about roles? (Choose three.)

   • AObject privileges may not be granted to roles.
   • BThe SET ROLE statement can enable one or more roles for a session.
   • CRoles may be granted to roles.
   • DThe SET ROLE statement can disable one or more roles for a session.
   • EAll roles granted to a user are set on by default when the user logs in
   • FRoles must be password protected.

   Show answer

   Answer: BCD

   Next question

10. Question 70 · 167 in bank

    Examine this query: SELECT TRUNC(ROUND(156.00,-2),-1) FROM DUAL What is the result?

    • A150
    • B16
    • C100
    • D160
    • E200

    Show answer

    Answer: E

    Explanation

    Correct answer: E Rounding 156.00 to the nearest hundred (-2) gives 200. Truncating 200 to the nearest ten (-1) will still give 200

    Source: Adapted from community discussion — cf1f99b

    Next question

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

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