nerdexam
CompTIA

DA0-002 · Question #29

A data analyst needs to remove all duplicate values between two tables, "Employees" and "Managers," using SQL SELECT statements. Which of the following should the analyst use for this task?

The correct answer is B. SELECT * FROM Employees UNION SELECT * FROM Managers. To combine results from two tables and remove duplicate rows between them, the UNION operator in SQL is the correct choice. UNION implicitly handles distinct rows, effectively removing duplicates from the combined result set.

Data Acquisition and Preparation

Question

A data analyst needs to remove all duplicate values between two tables, "Employees" and "Managers," using SQL SELECT statements. Which of the following should the analyst use for this task?

Options

  • ASELECT * FROM Employees UNION ALL SELECT * FROM Managers
  • BSELECT * FROM Employees UNION SELECT * FROM Managers
  • CSELECT * FROM Employees JOIN SELECT * FROM Managers
  • DSELECT * FROM Employees CROSS JOIN SELECT * FROM Managers

How the community answered

(27 responses)
  • A
    4% (1)
  • B
    85% (23)
  • C
    7% (2)
  • D
    4% (1)

Why each option

To combine results from two tables and remove duplicate rows between them, the UNION operator in SQL is the correct choice. UNION implicitly handles distinct rows, effectively removing duplicates from the combined result set.

ASELECT * FROM Employees UNION ALL SELECT * FROM Managers

UNION ALL combines the result sets of two or more SELECT statements but includes all rows, including duplicates, which is explicitly contrary to the requirement of removing duplicate values.

BSELECT * FROM Employees UNION SELECT * FROM ManagersCorrect

The UNION operator in SQL combines the result sets of two or more SELECT statements and automatically removes duplicate rows from the final result. This is precisely what's needed to remove all duplicate values when merging data from the 'Employees' and 'Managers' tables.

CSELECT * FROM Employees JOIN SELECT * FROM Managers

A JOIN clause combines rows from two or more tables based on a related column between them, not for combining result sets and removing duplicates across distinct tables without a join condition. The syntax 'JOIN SELECT * FROM Managers' is also incorrect.

DSELECT * FROM Employees CROSS JOIN SELECT * FROM Managers

A CROSS JOIN returns the Cartesian product of the rows from the joined tables, meaning every row from the first table is combined with every row from the second table, creating a large result set and not removing duplicates.

Concept tested: SQL UNION operator

Source: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-all-transact-sql

Topics

#SQL#Set operators#Data cleaning#Duplicate removal

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DA0-002 Practice