Question 6 of 86

You want to display 5 percent of the employees with the highest salaries in the EMPLOYEES table.

Which query will generate the required result?

    Correct Answer:

    To display the top 5 percent of employees with the highest salaries, the correct query must order the employees by their salaries in descending order and then fetch the top 5 percent rows. Option B does this correctly by ordering the results by salary in descending order and using the FETCH FIRST 5 PERCENT ROWS ONLY clause to retrieve the specified percentage of rows. Option A orders the salaries in ascending order, which is incorrect for fetching the highest salaries. Options C and D have syntax errors. Therefore, the correct query is the one in Option B.

Question 7 of 86

In the customers table, the CUST_CITY column contains the value 'Paris' for the CUST_FIRST_NAME 'Abigail'.

Evaluate the following query:

What would be the outcome?

    Correct Answer: A

    The query uses the INITCAP function to capitalize the first letter of the customer's first name and concatenate it with a space and the last two characters of the customer's city converted to uppercase. The CUST_CITY is 'Paris' and the last two characters of 'Paris' are 'is', which when converted to uppercase becomes 'IS'. Therefore, the full output is 'Abigail IS'.

Question 8 of 86

View the Exhibit and evaluate the structure and data in the CUST_STATUS table.

You issue the following SQL statement:

Which statement is true regarding the execution of the above query?

    Correct Answer: C

    The SQL statement makes use of several Oracle SQL functions: NULLIF and NVL2. The NULLIF function returns NULL if the two arguments are equal; otherwise, it returns the first argument. Here, NULLIF(amt_spent, credit_limit) will return NULL if 'amt_spent' is equal to 'credit_limit'. The NVL2 function works by returning the second value if the first argument is not NULL, and the third value if the first argument is NULL. In this query, if NULLIF returns NULL (which happens when 'amt_spent' is equal to 'credit_limit'), NVL2 will return the third argument, which is 1000. If NULLIF does not return NULL, NVL2 will take 'amt_spent' and return 0. Therefore, the query will display a bonus of 1000 for all customers whose 'amt_spent' equals 'credit_limit' or 'amt_spent' is NULL.

Question 9 of 86

You execute the following commands:

For which substitution variables are you prompted for the input?

    Correct Answer: D

    When executing the given SQL commands, you will be prompted for input for the substitution variable 'mgr_id', but not for 'hiredate'. This is because 'hiredate' has been defined and initialized with the value '01-APR-2011' using the DEFINE command. Therefore, the query will not prompt for its value again. However, there is no definition for 'mgr_id', so the system will prompt for its value during the execution of the SQL query.

Question 10 of 86

View the Exhibit and examine the data in the employees table:

You want to display all the employee names and their corresponding manager names.

Evaluate the following query:

Which join option can be used in the blank in the above query to get the required output?

    Correct Answer: A

    The INNER JOIN option is the correct answer. It allows you to display all the employee names along with their corresponding manager names by finding rows that have matching values in both tables. Since every employee in the given data has a manager and there are no rows that need to be included from just one table or the other, an INNER JOIN is sufficient to get the required output.