Data Warehousing on AWS Training Data Warehousing on AWS: SQL 2

Question 1

Which Redshift SQL function returns the current timestamp in UTC?

Accepted Answer

CURRENT_TIMESTAMP

Answer

CURRENT_TIMESTAMP is the ANSI-standard function that returns the current date and time in UTC in Redshift.

Question 2

In Amazon Redshift, what does the LISTAGG aggregate function do?

Accepted Answer

Concatenates values from multiple rows into a single string

Answer

LISTAGG concatenates values from multiple rows within a group into a single delimited string.

Question 3

What is the purpose of the QUALIFY clause in Redshift SQL?

Accepted Answer

Filters the results of window functions without a subquery

Answer

QUALIFY filters the results of window functions similarly to how HAVING filters aggregates, avoiding the need for a subquery.

Question 4

Which SQL command in Redshift removes all rows from a table but retains the table structure and does NOT generate a transaction log per row?

Accepted Answer

TRUNCATE

Answer

TRUNCATE removes all rows instantly without logging individual row deletions, making it much faster than DELETE for large tables.

Question 5

In Redshift, which JOIN type returns all rows from the left table and matching rows from the right table, filling NULLs where no match exists?

Accepted Answer

LEFT OUTER JOIN

Answer

LEFT OUTER JOIN returns every row from the left table plus matching rows from the right table; non-matching right-side columns are NULL.

Data Warehousing on AWS Training Practice Test