1Z0-071 practice questions 131–140

1. Question 131 · 238 in bank

   Select 2

   Which two are SQL features? (Choose two.)

   • Aproviding graphical capabilities
   • Bprocessing sets of data
   • Cproviding database transaction control
   • Dproviding update capabilities for data in external files
   • Eproviding variable definition capabilities

   Show answer

   Answer: BC

   References

   • Oracle documentation

   Next question

2. Question 132 · 238 in bank

   Select 4

   Which four statements are true about constraints on Oracle tables? (Choose four.)

   • AA PRIMARY KEY constraint can be added after a table has been created and populated.
   • BA FOREIGN KEY column can contain NULLs.
   • CA CHECK constraint can refer to values in other rows.
   • DA NOT NULL constraint can be defined at the table level.
   • EA UNIQUE constraint can use a pre-existing index on the constrained column or columns.
   • FA UNIQUE constraint permits NULLs.
   • GA column can have only one CHECK constraint.

   Show answer

   Answer: ABEF

   References

   • Oracle documentation

   Next question

3. Question 133 · 238 in bank

   In your session, the NLS_DATE_FORMAT is DD-MM-YYYY. There are 86400 seconds in a day. Examine this result: DATE - ----------- 02-JAN-2020 Which statement returns this?

   • ASELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '4' DAY - INTERVAL '120' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;
   • BSELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '3' MONTH + INTERVAL '7' DAY - INTERVAL '360' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;
   • CSELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '5' DAY - INTERVAL '120' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;
   • DSELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '5' DAY - INTERVAL '86410' SECOND, 'DD-MON- YYYY') AS "date" FROM DUAL;
   • ESELECT TO_CHAR(TO_DATE('29-10-2019') + INTERVAL '2' MONTH + INTERVAL '6' DAY - INTERVAL '120' SECOND, 'DD-MON-YYYY') AS "date" FROM DUAL;

   Show answer

   Answer: C

   Explanation

   Option C is the statement that returns the result 02-JAN-2020. The statement converts the string '29-10-2019' to a date using the TO_DATE function, then adds an interval of 2 months and an interval of 5 days, and subtracts an interval of 120 seconds. The resulting date is then converted to a string using the TO_CHAR function with the format model 'DD-MON-YYYY', which produces the result 02-JAN-2020. Here is the calculation: TO_DATE('29-10-2019') = October 29, 2019 + INTERVAL '2' MONTH = December 29, 2019 + INTERVAL '5' DAY = January 3, 2020 - INTERVAL '120' SECOND = January 2, 2020

   Source: Adapted from community discussion — zouve

   Next question

4. Question 134 · 238 in bank

   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

   Answer: D

   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.

   Source: Adapted from community discussion — AlexAFJ

   References

   • Oracle documentation

   Next question

5. Question 135 · 238 in bank

   Examine the contents of the EMP table:

   

   Examine this query that executes successfully:

   

   What is the result?

   • AIt will return the five employees earning the lowest salaries, in ascending order.
   • BIt will return the six employees earning the highest salaries, in descending order.
   • CIt will return the six employees earning the lowest salaries, in ascending order.
   • DIt will return the five employees earning the highest salaries, in descending order.

   Show answer

   Answer: A

   Explanation

   Option A tested in DB. If the salary contain like the below example: id Salary 1 12000 2 15000 3 16000 4 16000 5 17000 in this case, with ties option return extra rows select id,salary from emp order by salary fetch first 3 rows with ties; o/p: 1 12000 2 15000 3 16000 4 16000

   Source: Adapted from community discussion — Shrimathi

   Next question

6. Question 136 · 238 in bank

   Select 2

   You create a table by using this command: CREATE TABLE rate_list (rate NUMBER(6,2)); Which two are true about executing statements? (Choose two.)

   • AINSERT INTO rate_list VALUES (0.999) produces an error.
   • BINSERT INTO rate_list VALUES (-.9) inserts the value as -.9.
   • CINSERT INTO rate_list VALUES (87654.556) inserts the value as 87654.6.
   • DINSERT INTO rate_list VALUES (-10) produces an error.
   • EINSERT INTO rate_list VALUES (-99.99) inserts the value as 99.99.
   • FINSERT INTO rate_list VALUES (0.551) inserts the value as .55.

   Show answer

   Answer: BF

   Explanation

   100% BF correct. Have a try. CREATE TABLE rate_list (rate NUMBER(6,2)); INSERT INTO rate_list VALUES (0.999); select * from rate_list; INSERT INTO rate_list VALUES (-.9); select * from rate_list; INSERT INTO rate_list VALUES (87654.556); select * from rate_list; INSERT INTO rate_list VALUES (-10); select * from rate_list; INSERT INTO rate_list VALUES (-99.99); select * from rate_list; INSERT INTO rate_list VALUES (0.551); select * from rate_list;

   Source: Adapted from community discussion — WingL

   Next question

7. Question 137 · 238 in bank

   Select 2

   Examine this list of queries:

   

   Which two statements are true? (Choose two.)

   • A1 and 4 give different results.
   • B2 returns the value 20.
   • C3 returns an error.
   • D1 and 4 give the same result.
   • E2 and 3 give the same result.

   Show answer

   Answer: BD

   Explanation

   BD tested SELECT ROUND (TRUNC (15.193, 1)) "Results" FROM DUAL; SELECT ROUND (15.193, -1) "Results" FROM DUAL; SELECT TRUNC (15.193, -1) "Results" FROM DUAL; SELECT TRUNC (ROUND (15.193, 1)) "Results" FROM DUAL;

   Source: Adapted from community discussion — zouve

   Next question

8. Question 138 · 238 in bank

   Examine the data in the EMPLOYEES table:

   

   Which statement will compute the total annual compensation for each employee?

   • ASELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) AS annual_comp FROM employees;
   • BSELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * monthly_commission_pct) AS annual_comp FROM employees;
   • CSELECT last_name, (monthly_salary + monthly_commission_pct) * 12 AS annual_comp FROM employees;
   • DSELECT last_name, (monthly_salary * 12) + (monthly_commission_pct * 12) AS annual_comp FROM employees;

   Show answer

   Answer: A

   Next question

9. Question 139 · 238 in bank

   Examine the data in the PRODUCTS table:

   

   Examine these queries:

   

   Which queries generate the same output?

   • A1 and 3
   • B2 and 3
   • C1, 2, and 3
   • D1 and 2

   Show answer

   Answer: A

   Next question

10. Question 140 · 238 in bank

    Select 2

    Examine these statements which execute successfully:

    

    Both statements display departments ordered by their average salaries. Which two are true? (Choose two.)

    • ABoth statements will execute successfully if you add E.AVG_SAL to the select list.
    • BBoth statements will display departments with no employees.
    • COnly the second statement will execute successfully if you add E.AVG_SAL to the select list.
    • DOnly the first statement will display departments with no employees.
    • EOnly the second statement will display departments with no employees.
    • FOnly the first statement will execute successfully if you add E.AVG_SAL to the select list.

    Show answer

    Answer: BC

    Explanation

    Correct Answer: BC Tested A->The first select returns ORA-00904 D ->Wrong because both statement have d.* in the select list, so both will display departments with no employees E-> Wrong like D F -> Wrong like A

    Source: Adapted from community discussion — khaleesi89

    Next question

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

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