1Z0-071 practice questions 1–10

1. Question 1 · 238 in bank

   Select 2

   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.)

   • ASELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1;
   • BSELECT DISTINCT promo_cost || ' in ' || DISTINCT promo_category FROM promotions ORDER BY 1;
   • CSELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;
   • DSELECT promo_category DISTINCT promo_cost, FROM promotions ORDER BY 2;
   • ESELECT promo_cost, promo_category FROM promotions ORDER BY 1;

   Show answer

   Answer: AC

   Explanation

   -- Distinct keyword is used to select the unique values of the column/combination of columns mentioned after the DISTINCT clause. Thus Option E is incorrect , as this does not use DISTINCT clause and might include duplicates. -- Distinct should be used once in a SELECT statement and should be used IMMEDIATELY after the SELECT clause. NOT to be used before every column. Thus Options D and B are incorrect, as the positioning of the DISTINCT clause is incorrect. -- Option A and C are correct. Option A: Even though there is a string ' has ' in everyrow returned , the combination of promo_category || ' has ' || promo_cost will still be distinct. Option C: correct use case. ORDER BY clause does not affect the uniqueness in the returned result set.

   Source: Adapted from community discussion — Oracle_373

   Next question

2. Question 2 · 238 in bank

   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?

   • AWHERE TO_NUMBER(start_date - SYSDATE) <= 25
   • BWHERE MONTHS_BETWEEN(start_date, SYSDATE) <= 25
   • CWHERE MONTHS_BETWEEN(SYSDATE, start_date) <= 25
   • DWHERE ADD_MONTHS(start_date, 25) <= SYSDATE

   Show answer

   Answer: C

   Explanation

   C is true. MONTHS_BETWEEN(date 1, date 2) Oracle gives a positive value when date 1 > date 2 and a negative when date 1 < date 2 https://www.techonthenet.com/oracle/functions/months_between.php

   Source: Adapted from community discussion — Rik92

   Next question

3. Question 3 · 238 in bank

   Examine the BRICKS table:

   

   You write this query:

   

   How many rows will the query return?

   • A4
   • B6
   • C16
   • D0
   • E1
   • F10

   Show answer

   Answer: B

   Explanation

   The correct answer is B which is 6 rows Explanation: The CROSS JOIN operation combines each row from the first table with each row from the second table. Since the BRICKS table has 4 rows, the result of the CROSS JOIN will have 4 * 4 = 16 rows. The WHERE clause filters out rows where b1.weight is not less than b2.weight. This means that only rows where the weight of the first brick is less than the weight of the second brick will be included in the result. There are 6 such combinations: (5,10), (5,15), (5,20), (10,15), (10,20), and (15,20). So the final result will have 6 rows.

   Source: Adapted from community discussion — CyberP

   Next question

4. Question 4 · 238 in bank

   Examine this query: SELECT INTERVAL ‘100’ MONTH DURATION FROM DUAL; What will be the output?

   • Aan error
   • BDURATION - ---------- +100
   • CDURATION - ---------- +08
   • DDURATION - ---------- +08-04

   Show answer

   Answer: D

   Explanation

   Explanation: The given query is using an interval literal with a value of '100' and a unit of 'MONTH'. When using the interval literal syntax, the specified value represents the number of intervals of the given unit. In this case, the query is requesting an interval of 100 months. Since there are 12 months in a year, the interval '100' months is equivalent to 8 years and 4 months. The output format for intervals in Oracle is '+YY-MM', where YY represents the number of years and MM represents the number of months. Therefore, the output will be '+08-04', indicating an interval of 8 years and 4 months. The correct answer is D. +08-04.

   Source: Adapted from community discussion — CyberP

   Next question

5. Question 5 · 238 in bank

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

   • A150
   • B200
   • C160
   • D100
   • E16

   Show answer

   Answer: B

   Next question

6. Question 6 · 238 in bank

   Select 3

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

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

   Show answer

   Answer: ABE

   Explanation

   C and F are wrong, oracle documentation says: Unlike unusable indexes, an invisible index is maintained during DML statements. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-3A66938F-73C6-4173-844E-3938A0DBBB54

   Source: Adapted from community discussion — TATSSSS

   References

   • Oracle documentation
   • Oracle documentation

   Next question

7. Question 7 · 238 in bank

   Examine this description of the EMP table:

   

   You execute this query:

   

   What is the result?

   • Aonly departments where the total salary is greater than 3000, returned in no particular order
   • Bonly departments where the total salary is greater than 3000, ordered by department
   • Call departments and a sum of the salaries of employees with a salary greater than 3000
   • Dan error

   Show answer

   Answer: D

   Next question

8. Question 8 · 238 in bank

   Select 2

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

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

   Show answer

   Answer: AE

   Next question

9. Question 9 · 238 in bank

   Select 2

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

   • ASELECT TO_DATE('2019-01-01') FROM DUAL;
   • BSELECT DATE '2019-01-01' FROM DUAL;
   • CSELECT '2019-01-01' FROM DUAL;
   • DSELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;
   • ESELECT TO_CHAR('2019-01-01') FROM DUAL;

   Show answer

   Answer: BD

   Next question

10. Question 10 · 238 in bank

    Select 2

    Examine this SQL statement:

    

    Which two are true? (Choose two.)

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

    Show answer

    Answer: AE

    Explanation

    A. The DELETE statement executes successfully even if the subquery selects multiple rows. This is because the EXISTS condition only checks for the existence of at least one row in the subquery, regardless of how many rows are returned. E. The subquery is executed for every row in the EMPLOYEES table. This is because the subquery is a correlated subquery, which means that it references a column from the outer query (e.employee_id). As a result, the subquery must be executed once for each row in the EMPLOYEES table to determine whether the EXISTS condition is true or false for that row.

    Source: Adapted from community discussion — zouve

    Next question

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

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