Data Warehousing on AWS Training Data Warehousing on AWS: SQL 3 — Questions and Answers
Question 1: When using a COPY command in Redshift, which SQL technique best avoids duplicate rows if the load is rerun?
- Using UPSERT with ON CONFLICT
- Using a staging table with DELETE/INSERT or MERGE (Correct answer)
- Adding a UNIQUE constraint to the target table
- Running VACUUM before each load
Correct answer: Using a staging table with DELETE/INSERT or MERGE
Loading into a staging table and using DELETE or MERGE to update the target prevents duplicates when rerunning incremental loads.
Question 2: Which Redshift SQL clause partitions rows into groups for window function calculations without reducing the result set?
- GROUP BY
- PARTITION BY inside OVER() (Correct answer)
- HAVING
- CLUSTER BY
Correct answer: PARTITION BY inside OVER()
PARTITION BY inside the OVER() clause divides rows into groups for window functions while keeping all rows in the output.
Question 3: In Redshift SQL, what is the effect of using ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in a window frame?
- Selects all rows in the entire partition
- Computes a running calculation from the first row to the current row (Correct answer)
- Limits the frame to three rows before the current
- Excludes the current row from the calculation
Correct answer: Computes a running calculation from the first row to the current row
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines a cumulative window frame from the partition start to the current row.
Question 4: Which data type should you use in Redshift to store variable-length character strings up to 65,535 bytes?
- CHAR
- TEXT
- VARCHAR (Correct answer)
- NVARCHAR
Correct answer: VARCHAR
VARCHAR (or CHARACTER VARYING) stores variable-length strings and accepts a maximum length up to 65,535 bytes in Redshift.
Question 5: What is a lateral join (LATERAL) used for in Amazon Redshift SQL?
- Joining tables horizontally across clusters
- Allowing a subquery to reference columns from preceding table references (Correct answer)
- Performing cross-region joins
- Joining two tables on a range condition
Correct answer: Allowing a subquery to reference columns from preceding table references
LATERAL allows a subquery or function in the FROM clause to reference columns from tables listed earlier in the same FROM clause.
Question 6: In Redshift, which SQL command is used to change the owner of a database object?
- GRANT OWNERSHIP
- ALTER TABLE ... OWNER TO (Correct answer)
- REASSIGN OWNED
- CHANGE OWNER
Correct answer: ALTER TABLE ... OWNER TO
ALTER TABLE ... OWNER TO (or ALTER VIEW, ALTER SCHEMA) changes the owner of the specified Redshift database object.
Question 7: Which aggregate function in Redshift returns the statistical median of a numeric column?
- MEDIAN()
- AVG()
- PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) (Correct answer)
- MIDDLE()
Correct answer: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col)
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) computes the median by interpolating the 50th percentile of an ordered set.
Data Warehousing on AWS Training Data Warehousing on AWS: SQL 3
When using a COPY command in Redshift, which SQL technique best avoids duplicate rows if the load is rerun?