1Z0-071 practice questions 221–230

1. Question 221 · 238 in bank

   Select 2

   Examine the data in the ORDERS table:

   

   Examine the data in the INVOICES table:

   

   Examine this query:

   

   • A2
   • B1
   • C3
   • D5 01-MAR-2019
   • E3 01-JAN-2015
   • F4 01-FEB-2015

   Show answer

   Answer: BF

   Explanation

   drop table orders; create table orders(order_id number , order_date date); insert into orders values(1,null); insert into orders values(2,null); insert into orders values(3,'1-jan-2019'); insert into orders values(4,'1-feb-2019'); insert into orders values(5,'1-mar-2019'); create table invoices( invoice_id number, order_id number, order_date date); insert into invoices values(1,1,null); insert into invoices values(2,2,'01-jan-2019'); insert into invoices values(3,3,null); insert into invoices values(4,4,'01-feb-2019'); insert into invoices values(5,5,'01-APR-2019'); SELECT ORDER_ID , ORDER_DATE FROM ORDERS INTERSECT SELECT ORDER_ID , ORDER_DATE FROM INVOICES; ANSWER IS B,F.

   Source: Adapted from community discussion — abdullah_barham

   Next question

2. Question 222 · 238 in bank

   Select 2

   Which two will execute successfully? (Choose two.)

   • ASELECT COALESCE(0, SYSDATE) FROM DUAL;
   • BSELECT NVL('DATE', SYSDATZ) FROM DUAL;
   • CSELECT COALESCE('DATE', SYSDATE) FROM DUAL;
   • DSELECT NVL('DATE', 200) FROM (SELECT NULL AS "DATE" FROM DUAL);
   • ESELECT COALESCE('DATE', SYSDATE) FRCM (SELECT NULL AS "DATE" FROM DUAL);

   Show answer

   Answer: BD

   Next question

3. Question 223 · 238 in bank

   Select 3

   Which three statements are true about a self join? (Choose three.)

   • AIt must be an equijoin.
   • BThe ON clause must be used.
   • CIt must be an inner join.
   • DIt can be an outer join.
   • EThe ON clause can be used.
   • FThe query must use two different aliases for the table.

   Show answer

   Answer: DEF

   Next question

4. Question 224 · 238 in bank

   You execute this query: SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE), 'MON'), 'dd "Monday for" fmMonth rrrr') FROM DUAL; What is the result?

   • AIt executes successfully but does not return any result.
   • BIt returns the date for the first Monday of the next month.
   • CIt returns the date for the last Monday of the current month.
   • DIt generates an error.

   Show answer

   Answer: B

   References

   • Oracle documentation

   Next question

5. Question 225 · 238 in bank

   Select 2

   Which two statements are true about the WHERE and HAVING clauses in a SELECT statement? (Choose two.)

   • AWHERE and HAVING clauses can be used in the same statement only if applied to different table columns.
   • BThe WHERE clause can be used to exclude rows after dividing them into groups.
   • CThe HAVING clause can be used with aggregating functions in subqueries.
   • DThe WHERE clause can be used to exclude rows before dividing them into groups.
   • EAggregating functions and columns used in HAVING clauses must be specified in the SELECT list of a query.

   Show answer

   Answer: CD

   Next question

6. Question 226 · 238 in bank

   Select 2

   Which two are true about global temporary tables? (Choose two.)

   • AIndexes can be created on them.
   • BBackup and recovery operations are available for these tables.
   • CTheir data is always stored in the default temporary tablespace of the user who created them.
   • DIf the ON COMMIT clause is transaction-specific, all rows in the table are deleted after each COMMIT OR ROLLBACK.
   • EThey can be created only by a user with the DBA role, but can be accessed by all users who can create a session.
   • FIf the ON COMMIT clause is session-specific, the table is dropped when the session is terminated.

   Show answer

   Answer: AD

   Explanation

   A D are correct: D. If the ON COMMIT clause is transaction-specific, all rows in the table are deleted after each COMMIT OR ROLLBACK. ''all rows in the table are deleted'' = TRUNCATE

   Source: Adapted from community discussion — CMjer

   Next question

7. Question 227 · 238 in bank

   Select 3

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

   • AA combination of object and system privileges can be granted to a role.
   • BAll types of schema objects have associated object privileges.
   • CSchema owners can grant object privileges on objects in their schema to any other user or role.
   • DOnly users with the DBA role can create roles.
   • EOnly users with the GRANT ANY PRIVILEGE privilege can grant and revoke system privileges from other users.
   • FObject privileges granted on a table automatically apply to all synonyms for that table.

   Show answer

   Answer: ACF

   References

   • Oracle documentation

   Next question

8. Question 228 · 238 in bank

   Select 2

   Which two statements are true about TRUNCATE and DELETE? (Choose two.)

   • ADELETE can use a WHERE clause to determine which row(s) should be removed.
   • BTRUNCATE can use a WHERE clause to determine which row(s) should be removed.
   • CTRUNCATE leaves any indexes on the table in an UNUSABLE state.
   • DThe result of a TRUNCATE can be undone by issuing a ROLLBACK.
   • EThe result of a DELETE can be undone by issuing a ROLLBACK.

   Show answer

   Answer: AE

   Explanation

   A. DELETE can use a WHERE clause to determine which row(s) should be removed. ---> Is correct B. TRUNCATE can use a WHERE clause to determine which row(s) should be removed. ---> Its wrong bcoz truncate will delete every rows in a table except the table definitions. C. TRUNCATE leaves any indexes on the table in an UNUSABLE state. ----> its wrong truncate will remove everything except table definition or skeleton of the table D. The result of a TRUNCATE can be undone by issuing a ROLLBACK.-----> Its wrong bcoz TRUNCATE is a ddl statement whereas it can be done for DELETE since its dml statement E. The result of a DELETE can be undone by issuing a ROLLBACK. ----> Its correct bcoz DELETE is DML statement and each delete of row are recorded in transaction log or the redo logs.

   Source: Adapted from community discussion — devml

   Next question

9. Question 229 · 238 in bank

   Select 2

   Examine the description of the EMPLOYEES table:

   

   Which two statements will insert a row into the EMPLOYEES table? (Choose two.)

   • AINSERT INTO employees VALUES (101, 'John', 'Smith', 12000, SYSDATE);
   • BINSERT INTO employees VALUES (101, 'John', 'Smith', 10, 12000, SYSDATE);
   • CINSERT INTO employees (employee_id, salary, first_name, hiredate, last_name) VALUES (101, 12100, 'John', SYSDATE, 'Smith');
   • DINSERT INTO employees (employee_id, first_name, last_name, salary, hiresate) VALUES ( (SELECT 101, 'John', 'Smith'. 12000, SYSDATE FROM dual) );
   • EINSERT INTO employees SELECT 101, 'John', 'Smith', 12000, (SELECT SYSDATE FROM dual), 10 FROM dual;
   • FINSERT INTO employees VALUES (101, 'John', ' ', 12000, SYSDATE, 10);

   Show answer

   Answer: CE

   Explanation

   F is wrong because the column last_name has a not null constraint, in this case Oracle will not accept '' as value for the last_name column CE is the correct answer

   Source: Adapted from community discussion — jfc1

   Next question

10. Question 230 · 238 in bank

    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

    Answer: DE

    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

    Source: Adapted from community discussion — Beesho

    References

    • Oracle documentation

    Next question

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

1Z0-071 practice, question 221. 238 questions available.