Free Top Amazon/AWS DEA-C01 Actual Exam Questions - Question 6 Discussion
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?
It’s B here because it uses CREATE TABLE AS with the right WHERE filter for US cities and explicitly defines the table location, which Athena needs to know to create the new table properly. Options C and D miss setting the location, so they might fail or default wrongly. Option A doesn’t filter at all. So B covers filtering and table creation details more completely.
C/D? Option C creates the new table with SELECT but doesn’t filter for US cities, while D filters correctly. So D seems more aligned with the requirement to include only US cities.
It’s D because it uses CREATE TABLE AS SELECT with a WHERE clause filtering for US cities. But the question doesn’t say if the new table should be partitioned or if the data needs to be stored in a specific format. Does anyone know if those details matter here? Also, I’m wondering if the original table is external or managed; would that affect how we create the new one?