1Z0-071 practice questions 211–220

1. Question 211 · 238 in bank

   Examine the description of the EMPLOYEES table:

   

   Which statement will execute successfully, returning distinct employees with non-null first names?

   • ASELECT first_name, DISTINCT last_name FROM employees WHERE first_name <> NULL;
   • BSELECT first_name, DISTINCT last_name FROM employees WHERE first_name IS NOT NULL;
   • CSELECT DISTINCT * FROM employees WHERE first_name IS NOT NULL;
   • DSELECT DISTINCT * FROM employees WHERE first_name <> NULL;

   Show answer

   Answer: C

   Next question

2. Question 212 · 238 in bank

   Select 2

   Table EMPLOYEES contains columns including EMPLOYEE_ID, JOB_ID and SALARY. Only the EMPLOYEE_ID column is indexed. Rows exist for employees 100 and 200. Examine this statement:

   

   Which two statements are true? (Choose two.)

   • AEmployees 100 and 200 will have the same SALARY as before the update command.
   • BEmployee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200.
   • CEmployee 200 will have JOB_ID set to the same value as the JOB_ID of employee 100.
   • DEmployees 100 and 200 will have the same JOB_ID as before the update command.
   • EEmployee 100 will have SALARY set to the same value as the SALARY of employee 200.
   • FEmployee 200 will have SALARY set to the same value as the SALARY of employee 100.

   Show answer

   Answer: BE

   Next question

3. Question 213 · 238 in bank

   Examine these two queries and their output: SELECT deptno, dname FROM dept;

   

   SELECT emame, job, deptno FROM emp ORDER BY deptno;

   

   Now examine this query:

   

   How many rows will be displayed?

   • A64
   • B6
   • C3
   • D12

   Show answer

   Answer: B

   Explanation

   B is correct answer From first table it will take 2 records, from second table it will take 3 records. After cross join we will have 6 records (2x3).

   Source: Adapted from community discussion — Artur1991

   Next question

4. Question 214 · 238 in bank

   You want to return the current date and time from the user session, with a data type of TIMESTAMP WITH TIME ZONE. Which function will do this?

   • ASYSDATE
   • BCURRENT_TIMESTAMP
   • CLOCALTIMESTAMP
   • DCURRENT_DATE

   Show answer

   Answer: B

   Explanation

   Reference: https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG004

   Next question

5. Question 215 · 238 in bank

   You have been tasked to create a table for a banking application. One of the columns must meet three requirements: 1) Be stored in a format supporting date arithmetic without using conversion functions 2) Store a loan period of up to 10 years 3) Be used for calculating interest for the number of days the loan remains unpaid Which data type should you use?

   • AINTERVAL YEAR TO MONTH
   • BTIMESTAMP WITH TIMEZONE
   • CINTERVAL DAY TO SECOND
   • DTIMESTAMP WITH LOCAL TIMEZONE
   • ETIMESTAMP

   Show answer

   Answer: C

   Next question

6. Question 216 · 238 in bank

   Select 2

   Which two are true about a SQL statement using SET operators such as UNION? (Choose two.)

   • AThe data type group of each column returned by the second query must match the data type group of the corresponding column returned by the first query.
   • BThe names and number of columns must be identical for all select statements in the query.
   • CThe data type of each column returned by the second query must be implicitly convertible to the data type of the corresponding column returned by the first query.
   • DThe data type of each column returned by the second query must exactly match the data type of the corresponding column returned by the first query.
   • EThe number, but not names, of columns must be identical for all select statements in the query.

   Show answer

   Answer: AE

   Explanation

   c is incorrect ->The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table. i think D E are correct The UNION, INTERSECT, and MINUS operators are not valid on LONG columns. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/The-UNION-ALL-INTERSECT-MINUS-Operators.html#GUID-B64FE747-586E-4513-945F-80CB197125EE

   Source: Adapted from community discussion — Viviana3184

   References

   • Oracle documentation

   Next question

7. Question 217 · 238 in bank

   Select 2

   Examine this SQL statement:

   

   Which two are true? (Choose two.)

   • AAll existing rows in the ORDERS table are updated.
   • BThe subquery is executed before the UPDATE statement is executed.
   • CThe subquery is not a correlated subquery.
   • DThe subquery is executed for every updated row in the ORDERS table.
   • EThe UPDATE statement executes successfully even if the subquery selects multiple rows.

   Show answer

   Answer: AD

   Explanation

   DE A. All existing rows in the ORDERS table are updated. False. Only the rows that match from both tables B. The subquery is executed before the UPDATE statement is executed. False C. The subquery is not a correlated subquery. This is a correlated subquery- it will fetch records from outer query first, then run subquery related to that outer query record and then perform action. So it will work for more then one record. D. The subquery is executed for every updated row in the ORDERS table. True E. The UPDATE statement executes successfully even if the subquery selects multiple rows. True

   Source: Adapted from community discussion — holdfaststrongly

   Next question

8. Question 218 · 238 in bank

   Select 2

   Which two are true about queries using set operators such as UNION? (Choose two.)

   • AIn a query containing multiple set operators, INTERSECT always takes precedence over UNION and UNION ALL.
   • BAn expression in the first SELECT list must have a column alias for the expression.
   • CAll set operators are valid on columns of all data types.
   • DCHAR columns of different lengths used with a set operator return a VARCHAR2 whose length equals the longest char value.
   • EQueries using set operators do not perform implicit conversion across data type groups (e.g. character, numeric).

   Show answer

   Answer: DE

   Explanation

   According to: https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries004.htm "If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value." AND "In queries using set operators, Oracle does not perform implicit conversion across datatype groups. " So DE.

   Source: Adapted from community discussion — anzac

   References

   • Oracle documentation
   • Oracle documentation

   Next question

9. Question 219 · 238 in bank

   Select 2

   Examine this business rule: Each student can work on multiple projects and each project can have multiple students. You must design an Entity Relationship (ER) model for optimal data storage and allow for generating reports in this format: STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true? (Choose two.)

   • APROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
   • BSTUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the projects entity.
   • CAn associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the students and projects entities.
   • DThe ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into one-to-many relationships.
   • EThe ER must have a one-to-many relationship between the STUDENTS and PROJECTS entities.

   Show answer

   Answer: CD

   Next question

10. Question 220 · 238 in bank

    Select 3

    Which three are key components of an Entity Relationship Model? (Choose three.)

    • Aan activity
    • Ba table
    • Ca relationship
    • Dan attribute
    • Ea unique identifier
    • Fan entity

    Show answer

    Answer: CDF

    Next question

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

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