Certified Data Analyst Associate Practice Test

Certified Data Analyst Associate Practice Test practice questions, page 5 of 6, with answer review and explanations.

Question 21

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

  1. The 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.
  2. The command fails because it is written incorrectly.
  3. The 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.
  4. The suppliers table now contains the data from the new_suppliers table, and the new_suppliers table now contains the data from the suppliers table.
  5. The suppliers table now contains only the data from the new_suppliers table.

Question 22

A data engineer is working with a nested array column products in table transactions. They want to expand the table so each unique item in products for each row has its own row where the transaction_id column is duplicated as necessary. They are using the following incomplete command: Which of the following lines of code can they use to fill in the blank in the above code block so that it successfully completes the task?

  1. array distinct(products)
  2. explode(products)
  3. reduce(products)
  4. array(products)
  5. flatten(products)

Question 23

A data analysis team is working with the table_bronze SQL table as a source for one of its most complex projects. A stakeholder of the project notices that some of the downstream data is duplicative. The analysis team identifies table_bronze as the source of the duplication. Which of the following queries can be used to deduplicate the data from table_bronze and write it to a new table table_silver?

  1. CREATE TABLE table_silver AS - SELECT DISTINCT * FROM table_bronze;
  2. CREATE TABLE table_silver AS - INSERT * FROM table_bronze;
  3. CREATE TABLE table_silver AS - MERGE DEDUPLICATE * FROM table_bronze;
  4. INSERT INTO TABLE table_silver - SELECT * FROM table_bronze;
  5. INSERT OVERWRITE TABLE table_silver SELECT * FROM table_bronze;

Question 24

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?

Question 25

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?