1z0-071 practice test

1. Question 1 · 1

   Examine the description of the PROMOTIONS table: You want to display the unique promotion costs in each promotion category. Which two queries can be used? (Choose two.)

   Choose all answers that apply.

   

   • SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
   • SELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
   • SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
   • SELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;
   • SELECT promo_cost, promo_category FROM promotions ORDER BY 1;

2. Question 2 · 1

   The STORES table has a column START_DATE of data type DATE, containing the date the row was inserted. You only want to display details of rows where START_DATE is within the last 25 months. Which WHERE clause can be used?

   Choose one answer.

   • WHERE TO_NUMBER(start_date - SYSDATE) <= 25
   • WHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25
   • WHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25
   • WHERE ADD_MONTHS(start_date, 25) <= SYSDATE

3. Question 3 · 1

   Examine the BRICKS table: You write this query: How many rows will the query return?

   Choose one answer.

   

   

   • 4
   • 6
   • 16
   • 0
   • 1
   • 10

4. Question 4 · 1

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

   Choose one answer.

   • 150
   • 200
   • 160
   • 100
   • 16

5. Question 5 · 1

   Which three statements are true about indexes and their administration in an Oracle database? (Choose three.)

   Choose all answers that apply.

   • The same table column can be part of a unique and non-unique index.
   • A descending index is a type of function-based index.
   • An INVINSIBLE index is not maintained when DML is performed on its underlying table.
   • If a query filters on an indexed column then it will always be used during execution of the query.
   • An index can be created as part of a CREATE TABLE statement.
   • An UNUSABLE index is maintained when DML is performed on its underlying table.

6. Question 6 · 1

   Examine this description of the EMP table: You execute this query: What is the result?

   Choose one answer.

   

   

   • only departments where the total salary is greater than 3000, returned in no particular order
   • only departments where the total salary is greater than 3000, ordered by department
   • all departments and a sum of the salaries of employees with a salary greater than 3000
   • an error

7. Question 7 · 1

   Which two are true about virtual columns? (Choose two.)

   Choose all answers that apply.

   • They can be indexed.
   • They can be referenced in the column expression of another virtual column.
   • They cannot have a data type explicitly specified.
   • They can be referenced in the set clause of an update statement as the name of the column to be updated.
   • They can be referenced in the where clause of an update or delete statement.

8. Question 8 · 1

   A session's NLS_DATE_FORMAT is set to DD Mon YYYY. Which two queries return the value 1 Jan 2019? (Choose two.)

   Choose all answers that apply.

   • SELECT TO_DATE('2019-01-01') FROM DUAL;
   • SELECT DATE '2019-01-01' FROM DUAL;
   • SELECT '2019-01-01' FROM DUAL;
   • SELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;
   • SELECT TO_CHAR('2019-01-01') FROM DUAL;

9. Question 9 · 1

   Examine this SQL statement: Which two are true? (Choose two.)

   Choose all answers that apply.

   

   • The DELETE statement executes successfully even if the subquery selects multiple rows.
   • The subquery is executed before the DELETE statement is executed.
   • The subquery is not a correlated subquery.
   • All existing rows in the EMPLOYEES table are deleted.
   • The subquery is executed for every row in the EMPLOYEES table.

10. Question 10 · 1

    Examine this constraint information: Which three statements are true? (Choose three.)

    Choose all answers that apply.

    

    • The SALARY column must have a value.
    • The DEPTNO column in the EMP table can contain NULLS.
    • The COMMISION column can contain negative values.
    • The DEPTNO column in the EMP table can contain the value 1.
    • The MANAGER column is a foreign key referencing the EMPNO column.
    • The DNAME column has a unique constraint.
    • An index is created automatically in the MANAGER column.

11. Question 11 · 1

    Which three are true about scalar subquery expressions? (Choose three.)

    Choose all answers that apply.

    • They can be nested.
    • They cannot be used in the VALUES clause of an INSERT statement.
    • A scalar subquery expression that returns zero rows evaluates to zero.
    • They can be used as default values for columns in a CREATE TABLE statement.
    • A scalar subquery expression that returns zero rows evaluates to NULL.
    • They cannot be used in GROUP BY clauses.

12. Question 12 · 1

    Which two are true about creating tables in an Oracle database? (Choose two.)

    Choose all answers that apply.

    • Creating an external table will automatically create a file using the specified directory and file name.
    • A system privilege is required.
    • The same table name can be used for tables in different schemas.
    • A primary key constraint is mandatory.
    • A CREATE TABLE statement can specify the maximum number of rows the table will contain.

13. Question 13 · 1

    Examine this partial statement: SELECT ename, sal, comm FROM emp Now examine this output: Which ORDER BY clause will generate the displayed output?

    Choose one answer.

    

    • ORDER BY comm DESC NULLS LAST, ename
    • ORDER BY NVL(coram, 0) ASC NULLS FIRST, ename
    • ORDER BY NVL(coram, 0) ASC NULLS LAST, ename
    • ORDER BY NVL(ccmm, 0) DESC, ename

14. Question 14 · 1

    Examine the description of the CUSTOMERS table: Which two SELECT statements will return these results: (Choose two.)

    Choose all answers that apply.

    

    

    • SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE ‘MA*’;
    • SELECT customer_name FROM customers WHERE customer_name = ‘*Ma*’;
    • SELECT customer_name FROM customers WHERE customer_name LIKE ‘Ma*’;
    • SELECT customer_name FROM customers WHERE UPPER(customer_name) LIKE ‘MA%’;
    • SELECT customer_name FROM customers WHERE customer_name LIKE ‘%a%’;
    • SELECT customer_name FROM customers WHERE customer_name LIKE ‘Ma%’;
    • SELECT customer_name FROM customers WHERE customer_name LIKE ‘*Ma*’;

15. Question 15 · 1

    The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8,2). Evaluate this SQL statement: SELECT TO_CHAR(unit_price, '$9,999') FROM product_information; Which two statements are true about the output? (Choose two.)

    Choose all answers that apply.

    • A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,0236.
    • A row whose UNIT_PRICE column contains the value 1023.95 will be displayed as $1,024.
    • A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as $1,023.
    • A row whose UNIT_PRICE column contains the value 10235.95 will be displayed as #######.
    • A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,023.

Paid access to all 217 questions

Each purchase applies to this certification. Prices are one-time payments, not monthly subscriptions.