Unit 4: Running SQL Queries Using Spark SQL - Practice Quiz
1 What is the primary purpose of Spark SQL?
2 Which Spark SQL component optimizes query execution plans?
3 Which object is the main entry point for working with Spark SQL?
4 What is a DataFrame in Spark SQL?
5 Which approach converts an RDD of case class objects into a DataFrame by automatically inferring its schema?
6 Which schema type is commonly created when converting an RDD to a DataFrame programmatically?
7 After importing Spark implicits, which method commonly converts an RDD of tuples into a DataFrame?
toDF()
collect()
toRDD()
persist()
8 Which method registers a DataFrame as a temporary SQL view?
createGlobalDatabase()
saveTemporaryFile()
registerPermanentTable()
createOrReplaceTempView()
9 Which join returns only rows that have matching values in both datasets?
10 Which join returns all rows from the left dataset and matching rows from the right dataset?
11 Which SQL keyword specifies the condition used to match rows in a join?
HAVING
ON
LIMIT
INTO
12
What is the purpose of the SQL GROUP BY clause?
13 Which query operation counts employees in each department?
GROUP BY department with COUNT(*)
JOIN department with MAX(*)
ORDER BY department with SUM(*)
WHERE department with AVG(*)
14
What does the SQL ORDER BY clause do?
15 Which keyword sorts values from highest to lowest?
DISTINCT
ASC
BETWEEN
DESC
16 Which SQL aggregate function returns the number of rows?
AVG()
MAX()
SUM()
COUNT()
17 Which aggregate function calculates the arithmetic mean of numeric values?
SUM()
AVG()
COUNT()
MIN()
18 Which aggregate function calculates the total of numeric values in a column?
COUNT()
MAX()
SUM()
AVG()
19
In a SQL LIKE pattern, what does the % wildcard represent?
20
In a SQL LIKE pattern, what does the _ wildcard represent?
21 An application must analyze Parquet files and JSON records using both SQL queries and DataFrame operations. Which Spark SQL feature best supports this requirement?
22 A query reads a Parquet table but selects only two columns and filters rows by date. Which Spark SQL optimization can reduce the amount of data read?
23
In Scala, an RDD[Employee] contains instances of a case class Employee. After importing spark.implicits._, which expression converts it to a DataFrame using schema inference?
employeeRDD.toDF()
employeeRDD.collect()
employeeRDD.mapPartitions()
employeeRDD.toLocalIterator
24
An RDD[Row] is created from a text file, and the column types must be explicitly controlled. Which approach should be used?
StructType and call spark.createDataFrame(rowRDD, schema)
spark.read.json() directly
rowRDD.reduce() and infer columns from the final row
rowRDD.collect() and register the resulting local array
25
In Scala, pairRDD is an RDD[(Int, String)]. Which expression creates a DataFrame with columns named emp_id and emp_name, assuming Spark implicits are imported?
pairRDD.schema("emp_id", "emp_name")
pairRDD.toDF("emp_id", "emp_name")
pairRDD.asRow("emp_id", "emp_name")
pairRDD.columns("emp_id", "emp_name")
26
A DataFrame named salesDF must be queried with spark.sql("SELECT * FROM sales") in the current Spark session. What should be done first?
salesDF.write.saveAsTextFile("sales")
salesDF.repartition("sales")
salesDF.persist(StorageLevel.NONE)
salesDF.createOrReplaceTempView("sales")
27
A program creates a DataFrame with several select and filter operations, but no Spark job appears in the UI. Which operation will normally trigger execution?
count() on the final DataFrame
withColumnRenamed()
filter() transformation
alias()
28
A report should include only orders whose customer_id exists in the customer table. Which join type should be used between orders and customers?
29
A DataFrame employees must return only employees whose department_id has a match in departments, without adding any department columns. Which join is most suitable?
30
A report must list every department, including departments that currently have no employees. If departments is the left DataFrame, which join should be used?
31
Two DataFrames both contain a column named id. After joining them using an expression such as a.id == b.id, selecting id produces an ambiguous-column error. What is the best solution?
id before selecting the column
id columns to strings before the join
a.id
32
Which SQL query returns regions whose total sales amount exceeds 100000?
SELECT region, SUM(amount) FROM sales ORDER BY SUM(amount) > 100000
SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 100000
SELECT region, SUM(amount) FROM sales WHERE SUM(amount) > 100000 GROUP BY region
SELECT region, SUM(amount) FROM sales HAVING amount > 100000 GROUP BY region
33
What is the result of applying salesDF.groupBy("region", "product").sum("amount")?
34
Which statement correctly distinguishes ORDER BY from SORT BY in Spark SQL?
ORDER BY provides global ordering, while SORT BY orders rows within partitions
ORDER BY supports numbers, while SORT BY supports only text columns
ORDER BY removes duplicates, while SORT BY preserves duplicate rows
ORDER BY orders within partitions, while SORT BY provides global ordering
35 A result must show employees by descending salary, with employees having equal salaries arranged by ascending name. Which clause is correct?
ORDER BY salary ASC, name DESC
ORDER BY name ASC, salary DESC
ORDER BY name DESC, salary ASC
ORDER BY salary DESC, name ASC
36
A table has 100 rows, and the bonus column contains 15 NULL values. What will COUNT(*) and COUNT(bonus) return?
100 and 100, respectively
100 and 85, respectively
85 and 100, respectively
85 and 85, respectively
37
The values in a column are 10, 20, NULL, and 30. What does Spark SQL return for AVG(value)?
12
15
20
NULL
38 A grouped query must collect the distinct product names purchased in each region into an array. Which Spark SQL aggregate function is appropriate?
first(product)
collect_list(product)
concat(product)
collect_set(product)
39
Which LIKE condition matches values that contain the substring data anywhere in the text?
LIKE '_data_'
LIKE '%data%'
LIKE '%data'
LIKE 'data%'
40
What type of value is matched by the SQL pattern LIKE 'A_%'?
A and containing no additional characters
A
A and containing at least one earlier character
A and containing at least one more character
41 A pipeline filters adult users and converts names to uppercase. Which implementation gives Catalyst the greatest opportunity for predicate optimization, column pruning, and whole-stage code generation?
filter, select, and the built-in upper function
42 A DataFrame is registered as a temporary view, queried through SQL, and then filtered again through the DataFrame API. No caching operation is used. Which statement best describes execution?
43
Given RDD[(String, String)] containing (name, ageText) and case class Person(name: String, age: Int), which conversion produces columns named name and age with age represented as an integer?
spark.implicits._, map each tuple to Person(n, a.toInt), and call toDF()
spark.createDataFrame(rdd) and attach the Person schema after execution
rdd.toDF("name", "age") and rely on column naming to convert the type
(n, a) and cast the entire resulting DataFrame to Person
44
An RDD[Row] is converted using spark.createDataFrame(rows, schema). The schema declares (id: Long, amount: Double), but each row contains (amountValue, idValue) in that order. What is the key correctness issue?
Row values to schema fields by their runtime data types
45
An RDD[String] contains JSON records, but a numeric field is absent from many initial records. Which approach both enforces the intended numeric type and avoids a schema-inference pass?
spark.createDataFrame(jsonRDD, expectedSchema) to parse each JSON document
spark.read.schema(expectedSchema).json(jsonRDD) with an explicit StructType
jsonRDD.toDF("json") because JSON fields are automatically expanded
spark.read.json(jsonRDD) and assume later records will revise inferred types
46
Session S1 creates both a local temporary view v and a global temporary view g. A new Spark session S2 is created within the same Spark application. Which access pattern is valid?
S2 can query only v, using the name global_temp.v
S2 can query only g, using the name global_temp.g
S2 can query both views directly as v and g
S2 cannot query either view because all temporary views are session-local
47
A nullable column score must be filtered so that rows with score = 5 are removed while rows with other values or NULL are retained. Which predicate has the required semantics?
score <> 5 AND score IS NOT NULL
NOT (score = 5) AND score IS NULL
NOT (score = 5 OR score IS NULL)
score <> 5 OR score IS NULL
48
The query employees e LEFT JOIN departments d ON e.dept_id = d.id WHERE d.active = TRUE unexpectedly removes employees with no department. Which rewrite retains every employee while attaching only active department data?
e.dept_id IS NOT NULL to the existing WHERE condition
d.active = TRUE into the ON condition of the left join
49
Two join-key columns may contain NULL. The requirement is that two rows match when their keys are equal or when both keys are NULL, but not when only one key is NULL. Which Spark SQL condition satisfies this requirement?
l.key <=> r.key
l.key IS NULL OR r.key IS NULL
l.key <> r.key
l.key = r.key
50
The left relation contains (1,a), (1,b), (NULL,c), and (2,d). The right relation contains keys 1, 1, and NULL. What does a left-semi join using ordinary equality on the key return?
1, because every matching right row duplicates output
1, each appearing exactly once
1 rows and the key-NULL row, each appearing once
51
Two DataFrames both contain a column named id. How does joining them by a key-name list differ from joining them with the explicit condition left.id = right.id?
id, while the explicit condition retains both key columns
id because Catalyst removes every duplicate column name
id columns because join conditions never influence output schemas
id columns, while the explicit condition merges them automatically
52
A query uses GROUP BY CUBE(region, product), and both source columns may already contain NULL. Which statement correctly describes the result?
GROUPING or GROUPING_ID can identify subtotal nulls
53
Rows are (A,10), (A,NULL), (A,10), and (B,NULL). For each group, Spark computes COUNT(*), COUNT(value), COUNT(DISTINCT value), and SUM(value). Which results are correct?
A: (3,2,2,20) and B: (1,0,1,NULL)
A: (3,2,1,20) and B: (1,0,0,NULL)
A: (2,2,1,20) and B: (0,0,0,NULL)
A: (3,3,2,20) and B: (1,1,1,0)
54 A grouped query must collect all integer values, preserve duplicates, and produce a deterministic ascending array for each key. Which expression best satisfies the requirement?
sort_array(collect_list(value))
sort_array(collect_set(value))
first(value) followed by an array conversion
collect_list(value) after globally ordering the input
55
For values NULL, 2, and 1, what are Spark SQL's default results for ORDER BY value ASC and ORDER BY value DESC, respectively?
ASC: 1,2,NULL; DESC: NULL,2,1
ASC: NULL,1,2; DESC: NULL,2,1
ASC: 1,2,NULL; DESC: 2,1,NULL
ASC: NULL,1,2; DESC: 2,1,NULL
56
A DataFrame has many partitions. Which statement correctly compares orderBy(key) with sortWithinPartitions(key)?
orderBy orders only inside partitions, while sortWithinPartitions establishes global ordering
orderBy establishes global ordering, while sortWithinPartitions orders only inside each partition
sortWithinPartitions always performs an additional shuffle
57
A global aggregation is executed over an empty DataFrame: SELECT COUNT(*), COUNT(v), SUM(v), AVG(v) FROM t. What result does Spark SQL produce?
(NULL, NULL, NULL, NULL)
(0, 0, NULL, NULL)
(0, NULL, 0, 0)
58
Orders (1,100) and (2,100) have two and three matching item rows, respectively. Which query computes total qualifying order revenue as 200 without being affected by the one-to-many join or equal order totals?
SELECT SUM(o.total) / COUNT(DISTINCT i.item_id) FROM orders o JOIN items i ON o.order_id = i.order_id
SELECT SUM(DISTINCT o.total) FROM orders o INNER JOIN items i ON o.order_id = i.order_id
SELECT SUM(o.total) FROM orders o LEFT SEMI JOIN items i ON o.order_id = i.order_id
SELECT SUM(o.total) FROM orders o INNER JOIN items i ON o.order_id = i.order_id
59
Given strings A, AB, A_, A12, and BA1, which set matches the Spark SQL predicate value LIKE 'A_%'?
AB, A12, and BA1
A_ and A12 only
A, AB, A_, and A12
AB, A_, and A12
60
Which predicate matches every code that begins with the literal characters A_, treating the underscore as data rather than as a wildcard?
code LIKE 'A!_%' ESCAPE '!'
code LIKE 'A!!_%' ESCAPE '!'
code LIKE 'A_%' ESCAPE '!'
code LIKE 'A_!%' ESCAPE '!'
Did this save you a night before the exam?
LPU Notes is free, and it stays free. Ads cover part of the server bill. The rest comes out of a student's own pocket: the domain, the storage, and keeping the site up through the weeks everyone needs it at once.
The payment button didn't load. An ad blocker or a filtered network is the usual reason. to try again.
Nothing here is ever locked, and nothing unlocks. Chip in only if it was worth it. What it pays for →