Data Warehousing on AWS Training Data Warehousing on AWS: SQL 4 — Questions and Answers
Question 1: Which Redshift SQL feature allows you to query data stored in Amazon S3 without loading it into Redshift tables?
- Redshift Spectrum (Correct answer)
- S3 Select
- Redshift Data API
- Glue ETL
Correct answer: Redshift Spectrum
Redshift Spectrum enables SQL queries directly against data in S3 using external tables defined in an external schema.
Question 2: In Redshift SQL, what does the NVL function do?
- Returns the number of NULL values in a column
- Returns the first non-NULL expression from its arguments (Correct answer)
- Converts NULL to zero
- Validates that a column is not NULL
Correct answer: Returns the first non-NULL expression from its arguments
NVL(expr1, expr2) returns expr1 if it is not NULL; otherwise it returns expr2, equivalent to COALESCE with two arguments.
Question 3: Which SQL construct in Redshift lets you define a reusable named subquery within a single SQL statement?
- Stored procedure
- Temporary table
- Common Table Expression (WITH clause) (Correct answer)
- Materialized view
Correct answer: Common Table Expression (WITH clause)
A Common Table Expression (CTE) using the WITH clause defines a named, reusable subquery scoped to a single SQL statement.
Question 4: What is the result of performing integer division in Redshift SQL, e.g., SELECT 7 / 2?
- 3.5
- 3 (Correct answer)
- 4
- Error: division requires FLOAT cast
Correct answer: 3
Redshift follows standard SQL integer division rules: dividing two integers truncates the decimal, so 7 / 2 = 3.
Question 5: In Redshift, which function converts a string to lowercase?
- DOWNCASE()
- LOWER() (Correct answer)
- LCASE()
- TO_LOWER()
Correct answer: LOWER()
LOWER() is the standard SQL function in Redshift that converts all characters in a string to lowercase.
Question 6: Which Redshift SQL statement is used to grant a user the ability to run SELECT queries on a table?
- ASSIGN SELECT ON TABLE tbl TO user;
- GRANT SELECT ON TABLE tbl TO user; (Correct answer)
- ALLOW SELECT ON tbl FOR user;
- PERMIT user TO SELECT tbl;
Correct answer: GRANT SELECT ON TABLE tbl TO user;
GRANT SELECT ON TABLE tbl TO user is the standard SQL DCL statement for giving read access to a table in Redshift.
Question 7: In Redshift SQL, what does the COALESCE function return when all its arguments are NULL?
- 0
- Empty string
- NULL (Correct answer)
- Error
Correct answer: NULL
COALESCE returns the first non-NULL argument; if every argument is NULL, it returns NULL.
Data Warehousing on AWS Training Data Warehousing on AWS: SQL 4
Which Redshift SQL feature allows you to query data stored in Amazon S3 without loading it into Redshift tables?