MCQs on Advanced Query Techniques | AWS Amazon Athena MCQs Questions

Amazon Athena is a serverless query service that simplifies analyzing big data using standard SQL. Chapter 4, Advanced Query Techniques, delves into essential topics like complex SQL queries, joins, aggregations, functions, and effective table management. These Amazon Athena MCQ questions and answers are ideal for honing your expertise and preparing for assessments.

Complex SQL Queries

1. Which of the following is a valid example of a subquery?
   a) SELECT * FROM table WHERE column > 100;
   b) SELECT * FROM (SELECT column FROM table);
   c) SELECT column FROM table GROUP BY column;
   d) SELECT * FROM table ORDER BY column;
2. What keyword is used in SQL to filter groups of data?
   a) FILTER
   b) HAVING
   c) WHERE
   d) GROUP
3. How can you limit the number of rows returned by a query?
   a) LIMIT
   b) FETCH
   c) TOP
   d) ALL
4. Which operator is used to compare a column value against a range of values?
   a) LIKE
   b) IN
   c) BETWEEN
   d) ALL
5. A query with a UNION operator does what?
   a) Combines results from multiple SELECT statements without duplicates
   b) Combines rows from different tables
   c) Filters rows using a condition
   d) Merges columns in a table

Joins, Aggregations, and Functions

6. Which type of JOIN returns all rows from both tables, even when there is no match?
   a) INNER JOIN
   b) OUTER JOIN
   c) CROSS JOIN
   d) LEFT JOIN
7. What is the primary purpose of the GROUP BY clause?
   a) Aggregate data
   b) Sort rows
   c) Filter rows
   d) Join tables
8. Which function calculates the average value of a column?
   a) AVG()
   b) SUM()
   c) COUNT()
   d) MIN()
9. What does the COUNT(*) function return?
   a) The sum of all values in a column
   b) The number of rows in a table
   c) The maximum value in a column
   d) The distinct count of a column
10. What does a LEFT JOIN do?
    a) Selects matching rows from both tables
    b) Selects all rows from the left table and matching rows from the right table
    c) Combines rows without matching conditions
    d) Only selects matching rows from the right table

Views and Table Management

11. What is a view in SQL?
    a) A copy of a table stored in memory
    b) A virtual table based on a query
    c) A permanent table
    d) A temporary table
12. How do you update an existing view?
    a) ALTER VIEW
    b) MODIFY VIEW
    c) REFRESH VIEW
    d) UPDATE VIEW
13. What is the benefit of partitioning in Athena tables?
    a) Speeds up query performance by filtering data
    b) Merges duplicate rows
    c) Creates an exact copy of the table
    d) Combines columns
14. Which SQL statement is used to create a table?
    a) CREATE TABLE
    b) MAKE TABLE
    c) BUILD TABLE
    d) FORM TABLE
15. How do you drop a view in SQL?
    a) REMOVE VIEW
    b) DELETE VIEW
    c) DROP VIEW
    d) TRUNCATE VIEW
16. What does the MSCK REPAIR TABLE command do in Athena?
    a) Updates an existing table schema
    b) Recalculates the statistics for a table
    c) Adds metadata for new partitions
    d) Deletes unused partitions
17. What is the default storage format for Athena tables?
    a) CSV
    b) ORC
    c) Parquet
    d) JSON
18. Which command is used to rename a table in Athena?
    a) ALTER TABLE
    b) RENAME TABLE
    c) MODIFY TABLE
    d) CHANGE TABLE
19. What happens if you query an unpartitioned table?
    a) Results are faster
    b) Queries scan the entire dataset
    c) Query fails
    d) Only first partition is scanned
20. Which of the following statements creates a partitioned table in Athena?
    a) CREATE TABLE table_name PARTITIONED BY (column_name);
    b) CREATE TABLE table_name WITH PARTITION(column_name);
    c) CREATE PARTITIONED TABLE table_name;
    d) CREATE TABLE_PARTITION table_name(column_name);

Answers Table