SQL queries on DataFrames in Apache Spark - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this Spark SQL query on a DataFrame?

Given a Spark DataFrame df with columns id and value:

+---+-----+
|id |value|
+---+-----+
|1  |10   |
|2  |20   |
|3  |30   |
|4  |40   |
+---+-----+

What will be the result of this SQL query?

df.createOrReplaceTempView("table1")
spark.sql("SELECT id, value * 2 AS double_value FROM table1 WHERE id > 2 ORDER BY id").show()

Apache Spark

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data = [(1,10),(2,20),(3,30),(4,40)]
df = spark.createDataFrame(data, ["id", "value"])
df.createOrReplaceTempView("table1")
result = spark.sql("SELECT id, value * 2 AS double_value FROM table1 WHERE id > 2 ORDER BY id")
result.show()

A [Row(id=3, double_value=60), Row(id=4, double_value=80)]

B [Row(id=1, double_value=20), Row(id=2, double_value=40)]

C [Row(id=3, double_value=30), Row(id=4, double_value=40)]

D [Row(id=2, double_value=40), Row(id=3, double_value=60)]

💡 Hint

Remember the WHERE clause filters rows where id is greater than 2.

The query selects rows with id greater than 2, which are 3 and 4. It multiplies the value by 2, so 30*2=60 and 40*2=80.

❓ data_output

intermediate

How many rows are returned by this SQL query on a DataFrame?

Consider a Spark DataFrame df with the following data:

+---+-------+
|id |status |
+---+-------+
|1  |active |
|2  |inactive|
|3  |active |
|4  |inactive|
|5  |active |
+---+-------+

What is the number of rows returned by this SQL query?

df.createOrReplaceTempView("users")
spark.sql("SELECT * FROM users WHERE status = 'active'").count()

Apache Spark

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data = [(1,"active"),(2,"inactive"),(3,"active"),(4,"inactive"),(5,"active")]
df = spark.createDataFrame(data, ["id", "status"])
df.createOrReplaceTempView("users")
count_active = spark.sql("SELECT * FROM users WHERE status = 'active'").count()

A 3

B 2

C 5

D 4

💡 Hint

Count only rows where status is exactly 'active'.

Rows with id 1, 3, and 5 have status 'active', so total count is 3.

🔧 Debug

advanced

What error does this Spark SQL code raise?

Given a Spark DataFrame df with columns name and age, what error will this code produce?

df.createOrReplaceTempView("people")
spark.sql("SELECT name, age FROM people WHERE age > '30'").show()

Apache Spark

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data = [("Alice", 25), ("Bob", 35), ("Charlie", 30)]
df = spark.createDataFrame(data, ["name", "age"])
df.createOrReplaceTempView("people")
spark.sql("SELECT name, age FROM people WHERE age > '30'").show()

A AnalysisException: cannot resolve 'age > '30'' due to data type mismatch

B No error, outputs rows with age > 30

C RuntimeError: invalid comparison between int and string

D SyntaxError: invalid syntax in SQL query

💡 Hint

Check how Spark SQL handles comparisons between integers and strings.

Spark SQL automatically casts string '30' to integer 30 for comparison, so no error occurs and rows with age > 30 are returned.

❓ visualization

advanced

Which option shows the correct bar chart output from this Spark SQL aggregation?

Given a Spark DataFrame sales with columns product and amount:

+---------+-------+
|product  |amount |
+---------+-------+
|A        |100    |
|B        |200    |
|A        |150    |
|B        |50     |
|C        |300    |
+---------+-------+

After running this SQL query and plotting a bar chart of total sales per product, which chart matches the data?

sales.createOrReplaceTempView("sales_table")
result = spark.sql("SELECT product, SUM(amount) AS total FROM sales_table GROUP BY product ORDER BY product")
result.show()

Apache Spark

from pyspark.sql import SparkSession
import matplotlib.pyplot as plt
spark = SparkSession.builder.getOrCreate()
data = [("A",100),("B",200),("A",150),("B",50),("C",300)]
sales = spark.createDataFrame(data, ["product", "amount"])
sales.createOrReplaceTempView("sales_table")
result = spark.sql("SELECT product, SUM(amount) AS total FROM sales_table GROUP BY product ORDER BY product")
pandas_df = result.toPandas()
plt.bar(pandas_df['product'], pandas_df['total'])
plt.xlabel('Product')
plt.ylabel('Total Sales')
plt.title('Total Sales per Product')
plt.show()

A Bar heights: A=250, B=300, C=200

B Bar heights: A=100, B=200, C=300

C Bar heights: A=150, B=50, C=300

D Bar heights: A=250, B=250, C=300

💡 Hint

Sum amounts per product: add all amounts for each product.

Product A total: 100+150=250, B total: 200+50=250, C total: 300.

🧠 Conceptual

expert

Which option best describes the behavior of Spark SQL when joining two DataFrames with duplicate column names?

Suppose you join two Spark DataFrames df1 and df2 on a key column, and both have a column named value. What happens to the value columns in the resulting DataFrame?

A Spark SQL requires manual aliasing to avoid duplicate column names.

B Spark SQL throws an AnalysisException due to ambiguous column names.

C Spark SQL automatically renames duplicate columns by appending suffixes like <code>value</code> and <code>value_1</code>.

D Spark SQL keeps only one <code>value</code> column, dropping the other silently.

💡 Hint

Think about how Spark handles columns with the same name after join.

Spark SQL appends suffixes like _1 to duplicate columns to keep both without error.

Practice

1. What does the createOrReplaceTempView method do in Apache Spark?

easy

A. It registers a DataFrame as a temporary SQL table for querying.

B. It permanently saves the DataFrame to disk as a table.

C. It converts a SQL query into a DataFrame.

D. It deletes a temporary view from Spark.

Solution

1. Step 1: Understand the purpose of createOrReplaceTempView

   This method registers a DataFrame as a temporary table in Spark's SQL context.

2. Step 2: Recognize its use in SQL queries

   Once registered, you can run SQL queries on the DataFrame using spark.sql().

3. Final Answer:

   It registers a DataFrame as a temporary SQL table for querying. -> Option A

4. Quick Check:

   Temporary view = SQL table for queries [OK]

Hint: Temp view means temporary SQL table for queries [OK]

Common Mistakes:

• Thinking it saves data permanently
• Confusing it with running SQL queries
• Assuming it deletes tables

2. Which of the following is the correct syntax to run a SQL query on a DataFrame registered as a temp view named people?

easy

A. spark.execute("SELECT * FROM people")

B. spark.query("SELECT * FROM people")

C. spark.sql("SELECT * FROM people")

D. spark.runSQL("SELECT * FROM people")

Solution

1. Step 1: Identify the correct method to run SQL queries

   The method to run SQL queries on Spark is spark.sql().

2. Step 2: Check the syntax for querying the temp view

   The query string should be passed inside spark.sql() as shown in spark.sql("SELECT * FROM people").

3. Final Answer:

   spark.sql("SELECT * FROM people") -> Option C

4. Quick Check:

   Use spark.sql() to run SQL queries [OK]

Hint: Use spark.sql() to run SQL queries on temp views [OK]

Common Mistakes:

• Using incorrect method names like spark.query
• Forgetting to use quotes around SQL query
• Confusing method names with other APIs

3. Given the following code, what will be the output of the SQL query?

data = [(1, "Alice", 29), (2, "Bob", 31), (3, "Cathy", 25)]
columns = ["id", "name", "age"]
df = spark.createDataFrame(data, columns)
df.createOrReplaceTempView("people")
result = spark.sql("SELECT name FROM people WHERE age > 28 ORDER BY age DESC")
result.show()

medium

A. [["Bob"], ["Cathy"]]

B. [["Alice"], ["Bob"]]

C. [["Cathy"]]

D. [["Bob"], ["Alice"]]

Solution

1. Step 1: Analyze the data and filter condition

   Rows with age > 28 are Alice (29) and Bob (31).

2. Step 2: Apply ORDER BY age DESC

   Ordering by age descending gives Bob (31) first, then Alice (29).

3. Final Answer:

   [["Bob"], ["Alice"]] -> Option D

4. Quick Check:

   Filter age > 28 and order desc = Bob, Alice [OK]

Hint: Filter then order by age descending to get correct order [OK]

Common Mistakes:

• Mixing ascending and descending order
• Including rows with age <= 28
• Confusing column names in SELECT

4. Identify the error in the following code snippet:

df.createOrReplaceTempView("employees")
result = spark.sql("SELECT * FROM employee WHERE salary > 50000")
result.show()

medium

A. The DataFrame was not created before creating temp view.

B. The temp view name is 'employees' but query uses 'employee'.

C. The SQL query syntax is incorrect.

D. The show() method cannot be used on SQL query results.

Solution

1. Step 1: Check temp view name and SQL query table name

   The temp view is named "employees" but the query uses "employee" (missing 's').

2. Step 2: Understand impact of name mismatch

   This causes a runtime error because the table "employee" does not exist.

3. Final Answer:

   The temp view name is 'employees' but query uses 'employee'. -> Option B

4. Quick Check:

   Temp view and query table names must match exactly [OK]

Hint: Check temp view and query table names match exactly [OK]

Common Mistakes:

• Assuming SQL syntax error instead of name mismatch
• Thinking show() can't be used on results
• Ignoring case sensitivity in table names

5. You have a DataFrame sales with columns product, region, and amount. You want to find the total sales amount per region but only for regions where total sales exceed 1000. Which SQL query on the temp view sales will achieve this?

hard

A. SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region HAVING total_sales > 1000

B. SELECT region, SUM(amount) AS total_sales FROM sales WHERE total_sales > 1000 GROUP BY region

C. SELECT region, SUM(amount) FROM sales GROUP BY region WHERE SUM(amount) > 1000

D. SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region WHERE total_sales > 1000

Solution

1. Step 1: Understand filtering on aggregated data

   Filtering on aggregated results requires the HAVING clause, not WHERE.

2. Step 2: Check correct use of HAVING with alias

   Using HAVING total_sales > 1000 after GROUP BY is correct as in SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region HAVING total_sales > 1000.

3. Final Answer:

   SELECT region, SUM(amount) AS total_sales FROM sales GROUP BY region HAVING total_sales > 1000 -> Option A

4. Quick Check:

   Use HAVING to filter grouped aggregates [OK]

Hint: Use HAVING, not WHERE, to filter grouped results [OK]

Common Mistakes:

• Using WHERE instead of HAVING for aggregates
• Placing WHERE after GROUP BY
• Not aliasing SUM(amount) before HAVING