SQL queries on DataFrames in Apache Spark - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to register the DataFrame as a temporary SQL view named 'people'.

Apache Spark

df.[1]('people')

Drag options to blanks, or click blank then click option'

AcreateOrReplaceTempView

BcreateGlobalTempView

CregisterTempTable

DcreateTempView

Attempts:

3 left

💡 Hint

Common Mistakes

Using deprecated methods like registerTempTable.

Confusing global and temporary views.

The method createOrReplaceTempView registers the DataFrame as a temporary view for SQL queries.

2fill in blank

medium

Complete the code to run a SQL query selecting all columns from the 'people' view.

Apache Spark

result = spark.sql('SELECT [1] FROM people')

Drag options to blanks, or click blank then click option'

Acolumns

Ball

C*

Deverything

Attempts:

3 left

💡 Hint

Common Mistakes

Using words like 'all' or 'columns' instead of '*'.

Leaving the SELECT clause empty.

The asterisk (*) selects all columns in SQL queries.

3fill in blank

hard

Fix the error in the SQL query to select people older than 30.

Apache Spark

result = spark.sql('SELECT * FROM people WHERE age [1] 30')

Drag options to blanks, or click blank then click option'

A>

B=

C<

D==

Attempts:

3 left

💡 Hint

Common Mistakes

Using '==' instead of '>'.

Using '<' which selects younger people.

In SQL, the greater than operator is >. The double equals == is not valid in SQL.

4fill in blank

hard

Fill both blanks to select distinct cities from the 'people' view and order them alphabetically.

Apache Spark

result = spark.sql('SELECT [1] city FROM people ORDER BY city [2]')

Drag options to blanks, or click blank then click option'

ADISTINCT

BDESC

CASC

DALL

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ALL' instead of 'DISTINCT'.

Ordering by 'DESC' which sorts in reverse alphabetical order.

DISTINCT selects unique cities, and ASC orders them alphabetically ascending.

5fill in blank

hard

Fill all three blanks to count the number of people in each city, grouping by city and ordering by count descending.

Apache Spark

result = spark.sql('SELECT [1], COUNT(*) as [2] FROM people GROUP BY [3] ORDER BY [2] DESC')

Drag options to blanks, or click blank then click option'

Acity

Bcount

Dage

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'age' instead of 'city' for grouping.

Not using the same alias for count in ORDER BY.

Select the city, count the rows as 'count', group by city, and order by count descending.