Athena INSERT INTO SELECT for Data Subset

DEA-C01 By islever Editorial Last updated Oct 12, 2026

Transform and process data.

Short answer

A Use INSERT INTO cities_usa (city,state) SELECT city, state FROM cities_world WHERE country=’usa’;

Question

A data engineer needs to create an Amazon Athena table based on a subset of data from an existing Athena table named cities_world. The cities_world table contains cities that are located around the world. The data engineer must create a new table named cities_us to contain only the cities from cities_world that are located in the US. Which SQL statement should the data engineer use to meet this requirement?

INSERT INTO cities_usa (city,state) SELECT city, state FROM cities_world WHERE country=’usa’; Correct Answer
MOVE city, state FROM cities_world TO cities_usa WHERE country=’usa’;
INSERT INTO cities_usa SELECT city, state FROM cities_world WHERE country=’usa’;
UPDATE cities_usa SET (city, state) = (SELECT city, state FROM cities_world WHERE country=’usa’);

Key Takeaways

The core trap is confusing standard SQL INSERT syntax with non-existent commands like MOVE or UPDATE, and ensuring column lists match when inserting into specific columns.

This question tests the correct SQL syntax for copying a subset of data from an existing Amazon Athena table to a new or existing target table using the INSERT INTO ... SELECT statement.

Common mistake: Learners often select Option C because it looks simpler, but they fail to realize that explicitly listing columns in the INSERT clause (Option A) is safer and more precise, especially if the target table has more columns than the source selection.

Expert Analysis

Why the Answer Is Correct

Option A uses the standard SQL INSERT INTO <table> (<columns>) SELECT <columns> FROM <source> WHERE <condition> syntax. This correctly specifies the target table (cities_usa), the columns to populate (city, state), and filters the source data (cities_world) to include only rows where country='usa'. This is the robust way to insert a subset of data.

Why the Other Options Are Wrong

Option B (MOVE) is not a valid SQL command in Athena or standard SQL for this purpose. Option C is syntactically valid but less precise; if cities_usa has additional columns beyond city and state, omitting the column list might cause errors or unexpected behavior depending on schema definitions. Option D (UPDATE) modifies existing rows rather than inserting new ones, which contradicts the requirement to 'create' a table with the subset (or populate it).

Community Comment Notes

The community overwhelmingly voted for A. One user noted that while the prompt says 'create a new table', none of the options actually contain a CREATE TABLE statement, implying the table already exists or the question focuses purely on the insertion logic. Another comment highlighted that specifying the column list in Option A is best practice to avoid failures if the target table schema differs slightly from the source selection.

Exam Strategy

Always look for explicit column lists in INSERT INTO statements when the target schema might have more columns than the selected source data. It prevents ambiguity and errors.

Frequently Asked Questions

Why is CREATE TABLE AS SELECT not an option?

The question asks to create a table based on a subset, but all options use INSERT. This implies the table structure is already defined, and we are populating it.

Is Option C also correct?

Option C is valid SQL but less safe. Explicitly listing columns (Option A) ensures data goes to the right place even if the target table has extra columns.

More DEA-C01 FAQ →

Community Votes

100%

100% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 5 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 5

Eleftheriia 👍 4 Answered A

INSERT INTO cities_usa (city,state) SELECT city,state FROM cities_world WHERE country='usa'
truongnguyen86 👍 1 Answered A

should be A or C but C will failed if cities_usa contains more than 2 columns so specify the list of column want to insert is the good one.
MerryLew 👍 1 Answered A

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
saqib839 👍 1 Answered A

but he said create table so neither of them is true.
Parandhaman_Margan 👍 1

Answer:D

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide