1Z0-071 practice questions 81–90

1. Question 81 · 238 in bank

   Select 2

   Which two statements are true about INTERVAL data types?

   • AINTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
   • BINTERVAL DAY TO SECOND columns support fractions of seconds.
   • CINTERVAL YEAR TO MONTH columns support yearly intervals.
   • DThe YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
   • EINTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
   • FThe value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.

   Show answer

   Answer: BC

   Next question

2. Question 82 · 238 in bank

   Select 2

   Which two statements are true about the data dictionary?

   • AThe data dictionary is accessible when the database is closed.
   • BThe data dictionary does not store metadata in tables.
   • CViews with the prefix ALL_, DBA_ and USER_ are not all available for every type of metadata.
   • DViews with the prefix DBA_ display only metadata for objects in the SYS schema.
   • EViews with the prefix ALL_ display metadata for objects to which the current user has access.

   Show answer

   Answer: AE

   References

   • Oracle documentation

   Next question

3. Question 83 · 238 in bank

   Select 2

   Examine the description of the CUSTOMERS table:

   

   You need to display last names and credit limits of all customers whose last name starts with A or B in lower or upper case, and whose credit limit is below 1000. Examine this partial query: SELECT cust_last_name, cust_credit_limit FROM customers Which two WHERE conditions give the required result?

   • AWHERE UPPER(cust_last_name) IN (‘AX’, ‘B%’) AND cust_credit_limit < 1000;
   • BWHERE (UPPER(cust_last_name) LIKE ‘A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND ROUND(cust_credit_limit) < 1000;
   • CWHERE UPPER(cust_last_name) BETWEEN UPPER(‘A%’ AND ‘B%’) AND ROUND(cust_credit_limit) < 1000;
   • DWHERE (INITCAP(cust_last_name) LIKE ‘A%’ OR INITCAP(cust_last_name) LIKE ‘B%’) AND cust_credit_limit < 1000;
   • EWHERE (UPPER(cust_last_name) LIKE INITCAP(‘A’) OR UPPER(cust_last_name) LIKE INITCAP(‘B’)) AND ROUND(cust_credit_limit) < ROUND(1000);

   Show answer

   Answer: BD

   Next question

4. Question 84 · 238 in bank

   Select 2

   Which two statements are true about substitution variables?

   • AA substitution variable can be used only in a SELECT statement.
   • BA substitution variable used to prompt for a column name must be enclosed in double quotation marks.
   • CA substitution variable can be used with any clause in a SELECT statement.
   • DA substation variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session.
   • EA substitution variable prefixed with & always prompts only once for a value in a session.
   • FA substitution variable used to prompt for a column name must be enclosed in single quotation marks.

   Show answer

   Answer: CD

   Next question

5. Question 85 · 238 in bank

   Select 2

   Which two are true about scalar subquery expressions?

   • AThey can return at most one row.
   • BYou must enclose them in parentheses.
   • CYou cannot correlate them with a table in the parent statement.
   • DThey can return two columns.
   • EYou can use them as a default value for a column.

   Show answer

   Answer: AB

   References

   • Oracle documentation

   Next question

6. Question 86 · 238 in bank

   Select 2

   Examine the description PRODUCTS table:

   

   Examine the description of the NEW_PRODUCTS table:

   

   Which two queries execute successfully?

   • ASELECT prod_id FROM products - UNION ALL - SELECT prod_id, prod_name FROM new_products;
   • BSELECT prod_id, exp_date FROM products UNION ALL - SELECT prod_id, NULL FROM new_products;
   • CSELECT * FROM products - MINUS - SELECT prod_id, FROM new_products;
   • DSELECT prod_id, prod_name FROM products INTERSECT - SELECT 100, prod_name FROM new_products;
   • ESELECT * FROM products - UNION - SELECT * FROM new_products;

   Show answer

   Answer: BE

   Explanation

   B,E create table products (prod_id char(2), prod_name char(4), exp_date timestamp(6)); create table new_products (prod_id char(4), prod_name varchar2(10), exp_date DATE); /*A NOOK 01789. 00000 - "query block has incorrect number of result columns"*/ SELECT prod_id FROM products UNION ALL SELECT prod_id, prod_name FROM new_products; /*B OK.*/ SELECT prod_id, exp_date FROM products UNION ALL SELECT prod_id, NULL FROM new_products; /*C NOOK 01789. 00000 - "query block has incorrect number of result columns"*/ SELECT * FROM products MINUS SELECT prod_id FROM new_products; /*D NOOK 01790. 00000 - "expression must have same datatype as corresponding expression"*/ SELECT prod_id, prod_name FROM products INTERSECT SELECT 100, prod_name FROM new_products; /*E OK*/ SELECT * FROM products UNION SELECT * FROM new_products;

   Source: Adapted from community discussion — SantiBZ_07032022_1744

   Next question

7. Question 87 · 238 in bank

   Select 3

   Which three statements are true about Data Manipulation Language (DML)?

   • AUPDATE statements can have different subqueries to specify the values for each updated column.
   • BINSERT INTO…SELECT…FROM statements automatically commit.
   • CDML statements require a primary key be defined on a table.
   • DDELETE statements can remove multiple rows based on multiple conditions.
   • EINSERT statements can insert NULLs explicitly into a column.

   Show answer

   Answer: ADE

   Next question

8. Question 88 · 238 in bank

   Select 2

   Which two are true about multitable INSERT statements?

   • AThe conditional INSERT FIRST statement always inserts a row into a single table.
   • BThe unconditional INSERT ALL statement must have the same number of columns in both the source and target tables.
   • CThey can transform a row from a source table into multiple rows in a target table.
   • DThe conditional INSERT ALL statement inserts rows into a singe table by aggregating source rows.
   • EThey always use subqueries.

   Show answer

   Answer: CE

   Next question

9. Question 89 · 238 in bank

   Which statement is true about using functions in WHERE and HAVING?

   • AUsing aggregate functions in the WHERE clause requires a subquery.
   • BUsing aggregate functions in the HAVING clause requires a subquery.
   • CUsing single-row functions in the WHERE clause requires a subquery.
   • DUsing single-row functions in the HAVING clause requires a subquery.

   Show answer

   Answer: A

   Explanation

   --B WRONG --select avg(salary) from emp group by dept_id having avg(salary) > 1000; --C WRONG --select last_name from emp where upper(last_name) like 'A%'; --D WRONG --Having is Used for Agg Functions. Single-row functions do not require grouping.

   Source: Adapted from community discussion — ArslanAltaf

   Next question

10. Question 90 · 238 in bank

    Select 2

    You execute these commands:

    

    Which two, used independently, can replace so the query returns 1?

    • AROLLBACK;
    • BROLLBACK TO SAVEPOINT post_insert;
    • CROLLBACK TO post_insert;
    • DCOMMIT;
    • ECOMMIT TO SAVEPOINT post_insert;

    Show answer

    Answer: BC

    Next question

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

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