DA-ASSOC practice questions 21–30

1. Question 21 · 65 in bank

   A data analyst runs the following command: INSERT INTO stakeholders.suppliers TABLE stakeholders.new_suppliers; What is the result of running this command?

   • AThe suppliers table now contains both the data it had before the command was run and the data from the new_suppliers table, and any duplicate data is deleted.
   • BThe command fails because it is written incorrectly.
   • CThe suppliers table now contains both the data it had before the command was run and the data from the new_suppliers table, including any duplicate data.
   • DThe suppliers table now contains the data from the new_suppliers table, and the new_suppliers table now contains the data from the suppliers table.
   • EThe suppliers table now contains only the data from the new_suppliers table.

   Show answer

   Answer: C

   Next question

2. Question 22 · 65 in bank

   A business analyst has been asked to create a data entity/object called sales_by_employee. It should always stay up-to-date when new data are added to the sales table. The new entity should have the columns sales_person, which will be the name of the employee from the employees table, and sales, which will be all sales for that particular sales person. Both the sales table and the employees table have an employee_id column that is used to identify the sales person. Which of the following code blocks will accomplish this task?

   • A//IMG//
   • B//IMG//
   • C//IMG//
   • D//IMG//
   • E//IMG//

   Show answer

   Answer: D

   Next question

3. Question 23 · 65 in bank

   A data analyst has been asked to use the below table sales_table to get the percentage rank of products within region by the sales:

   

   The result of the query should look like this:

   

   Which of the following queries will accomplish this task?

   • A//IMG//
   • B//IMG//
   • C//IMG//
   • D//IMG//
   • E//IMG//

   Show answer

   Answer: B

   Next question

4. Question 24 · 65 in bank

   In which of the following situations should a data analyst use higher-order functions?

   • AWhen custom logic needs to be applied to simple, unnested data
   • BWhen custom logic needs to be converted to Python-native code
   • CWhen custom logic needs to be applied at scale to array data objects
   • DWhen built-in functions are taking too long to perform tasks
   • EWhen built-in functions need to run through the Catalyst Optimizer

   Show answer

   Answer: C

   Next question

5. Question 25 · 65 in bank

   Consider the following two statements: Statement 1:

   

   Statement 2:

   

   Which of the following describes how the result sets will differ for each statement when they are run in Databricks SQL?

   • AThe first statement will return all data from the customers table and matching data from the orders table. The second statement will return all data from the orders table and matching data from the customers table. Any missing data will be filled in with NULL.
   • BWhen the first statement is run, only rows from the customers table that have at least one match with the orders table on customer_id will be returned. When the second statement is run, only those rows in the customers table that do not have at least one match with the orders table on customer_id will be returned.
   • CThere is no difference between the result sets for both statements.
   • DBoth statements will fail because Databricks SQL does not support those join types.
   • EWhen the first statement is run, all rows from the customers table will be returned and only the customer_id from the orders table will be returned. When the second statement is run, only those rows in the customers table that do not have at least one match with the orders table on customer_id will be returned.

   Show answer

   Answer: B

   Next question

6. Question 26 · 65 in bank

   A data analyst has created a user-defined function using the following line of code: CREATE FUNCTION price(spend DOUBLE, units DOUBLE) RETURNS DOUBLE - RETURN spend / units; Which of the following code blocks can be used to apply this function to the customer_spend and customer_units columns of the table customer_summary to create column customer_price?

   • ASELECT PRICE customer_spend, customer_units AS customer_price FROM customer_summary
   • BSELECT price - FROM customer_summary
   • CSELECT function(price(customer_spend, customer_units)) AS customer_price FROM customer_summary
   • DSELECT double(price(customer_spend, customer_units)) AS customer_price FROM customer_summary
   • ESELECT price(customer_spend, customer_units) AS customer_price FROM customer_summary

   Show answer

   Answer: E

   Next question

7. Question 27 · 65 in bank

   A data analyst has been asked to count the number of customers in each region and has written the following query:

   

   If there is a mistake in the query, which of the following describes the mistake?

   • AThe query is using count(*), which will count all the customers in the customers table, no matter the region.
   • BThe query is missing a GROUP BY region clause.
   • CThe query is using ORDER BY, which is not allowed in an aggregation.
   • DThere are no mistakes in the query.
   • EThe query is selecting region, but region should only occur in the ORDER BY clause.

   Show answer

   Answer: B

   Next question

8. Question 28 · 65 in bank

   A data analyst is processing a complex aggregation on a table with zero null values and their query returns the following result:

   

   Which of the following queries did the analyst run to obtain the above result?

   • A//IMG//
   • B//IMG//
   • C//IMG//
   • D//IMG//
   • E//IMG//

   Show answer

   Answer: E

   Next question

9. Question 29 · 65 in bank

   Which of the following is a benefit of Databricks SQL using ANSI SQL as its standard SQL dialect?

   • AIt has increased customization capabilities
   • BIt is easy to migrate existing SQL queries to Databricks SQL
   • CIt allows for the use of Photon's computation optimizations
   • DIt is more performant than other SQL dialects
   • EIt is more compatible with Spark's interpreters

   Show answer

   Answer: B

   Next question

10. Question 30 · 65 in bank

    How can a data analyst determine if query results were pulled from the cache?

    • AGo to the Query History tab and click on the text of the query. The slideout shows if the results came from the cache.
    • BGo to the Alerts tab and check the Cache Status alert.
    • CGo to the Queries tab and click on Cache Status. The status will be green if the results from the last run came from the cache.
    • DGo to the SQL Warehouse (formerly SQL Endpoints) tab and click on Cache. The Cache file will show the contents of the cache.
    • EGo to the Data tab and click Last Query. The details of the query will show if the results came from the cache.

    Show answer

    Answer: A

    Next question

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

DA-ASSOC practice, question 21. 65 questions available.