Data Warehousing on AWS Training Data Warehousing on AWS: SQL 2 — Questions and Answers
Question 1: Which Redshift SQL function returns the current timestamp in UTC?
- NOW()
- GETDATE()
- CURRENT_TIMESTAMP (Correct answer)
- SYSDATE
Correct answer: CURRENT_TIMESTAMP
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?
- Returns the maximum value in a group
- Concatenates values from multiple rows into a single string (Correct answer)
- Lists all aggregate functions available
- Counts distinct values in a column
Correct answer: Concatenates values from multiple rows into a single string
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?
- Filters rows based on aggregate conditions
- Filters the results of window functions without a subquery (Correct answer)
- Qualifies column names with table aliases
- Validates data types before insertion
Correct answer: Filters the results of window functions without a subquery
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?
- DELETE
- DROP
- TRUNCATE (Correct answer)
- PURGE
Correct answer: TRUNCATE
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?
- INNER JOIN
- CROSS JOIN
- LEFT OUTER JOIN (Correct answer)
- RIGHT OUTER JOIN
Correct answer: LEFT OUTER JOIN
LEFT OUTER JOIN returns every row from the left table plus matching rows from the right table; non-matching right-side columns are NULL.
Question 6: Which Redshift SQL function calculates a running total using a window frame?
- SUM() OVER (ORDER BY ...) (Correct answer)
- GROUP BY SUM()
- ROLLUP()
- AGGREGATE SUM()
Correct answer: SUM() OVER (ORDER BY ...)
SUM() OVER (ORDER BY ...) is a window function that computes a cumulative sum across ordered rows without collapsing them into groups.
Question 7: What does the DECODE function do in Amazon Redshift SQL?
- Decrypts an encrypted column value
- Performs conditional value substitution similar to a CASE expression (Correct answer)
- Converts binary data to text
- Decodes URL-encoded strings
Correct answer: Performs conditional value substitution similar to a CASE expression
DECODE compares an expression to search values and returns corresponding results, functioning like a simplified CASE WHEN expression.
Data Warehousing on AWS Training Data Warehousing on AWS: SQL 2
Which Redshift SQL function returns the current timestamp in UTC?