How would you extract the year from a date column in Spark DataFrame?

from pyspark.sql.functions import year\n\ndf.withColumn(\Year\", year(\"Date\"))"

df.selectExpr(\YEAR(Date) AS Year\")"

df.createOrReplaceTempView(\table\")\nspark.sql(\"SELECT YEAR(Date) AS Year FROM table\")"

df.withColumn(\Year\", df.Date.year())"

What is the correct way to calculate the 90th percentzasdasdile of a column in PySpark?

from pyspark.sql.functions import expr\n\ndf.selectExpr(\percentile_approx(column, 0.9)\")"

from pyspark.sql.functions import percentile_approx\n\ndf.select(percentile_approx(\column\", 0.9))"

df.selectExpr(\approx_percentile(column, 0.9)\")"

df.select(\column\").approx_percentile(0.9)"

How would you add a unique identifier to each row in a Spark DataFrame?

from pyspark.sql.functions import monotonically_increasing_id\n\ndf.withColumn(\unique_id\", monotonically_increasing_id())"

df.createOrReplaceTempView(\table\")\nspark.sql(\"SELECT *, ROW_NUMBER() OVER (ORDER BY some_column) AS unique_id FROM table\")"

df.withColumn(\unique_id\", row_number())"

df.withColumn(\unique_id\", dense_rank())"

Which query selects all employees who do not belong to a department?

SELECT * FROM employees WHERE department_id IS NULL;

SELECT * FROM employees WHERE department_id = NULL;

SELECT * FROM employees WHERE department_id != NULL;

SELECT * FROM employees WHERE department_id IS NOT NULL;

What is the difference between UNION and UNION ALL?

UNION removes duplicates; UNION ALL includes duplicates

UNION includes duplicates; UNION ALL removes duplicates

How do you calculate the average salary per department?

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

SELECT department_id, AVG(salary) FROM employees;

SELECT AVG(salary) FROM employees GROUP BY department_id;

SELECT AVG(salary) FROM employees;

Which query retrieves the first 5 rows of a table?

SELECT * FROM employees LIMIT 5;

SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;

How do you rename a column in a SELECT query?

SELECT column_name AS new_name FROM table;

SELECT column_name RENAME TO new_name FROM table;

RENAME column_name TO new_name;

SELECT RENAME column_name TO new_name FROM table;

How do you filter rows using a calculated column in SQL?

FILTER BY calculated_column > 10

SELECT * WHERE calculated_column > 10

What does this query return? SELECT MIN(salary) FROM employees;

The highest salary in the table

The average salary in the table

How do you find employees whose names contain 'Smith'?

SELECT * FROM employees WHERE name LIKE '%Smith%';

SELECT * FROM employees WHERE name = '%Smith%';

SELECT * FROM employees WHERE name CONTAINS 'Smith';

SELECT * FROM employees WHERE name IN 'Smith';

What does this query do? SELECT COUNT(DISTINCT department_id) FROM employees;

Counts all departments, including duplicates

Counts all employees in each department

How do you write a query to find the second highest salary?

SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

SELECT MAX(salary) FROM employees WHERE salary != MAX(salary);

SELECT salary FROM employees ORDER BY salary ASC LIMIT 2;

SELECT TOP 2 salary FROM employees;

What is the difference between ROW_NUMBER() and RANK()?

RANK() allows ties; ROW_NUMBER() does not

ROW_NUMBER() allows ties; RANK() does not

How do you calculate the cumulative sum of sales?

SELECT SUM(sales) OVER (ORDER BY date) FROM sales;

SELECT SUM(sales) PARTITION BY date FROM sales;

SELECT sales + SUM(sales) FROM sales;

Which query finds employees hired in 2020?

SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';

SELECT * FROM employees WHERE hire_date = '2020';

SELECT * FROM employees WHERE hire_date LIKE '2020%';

SELECT * FROM employees WHERE YEAR(hire_date) = 2020;

What does this query return? SELECT COUNT(*) FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);

Employees earning more than anyone in department 10

Employees earning less than anyone in department 10

Employees earning the most in department 10

A type of table used to track changes to a dimension over time.

Slowly Changing Dimension Table

Data Lab PreTest

Authored by Sinh Hoàng Văn

Instructional Technology

Professional Development

AI Actions

Add similar questions

Adjust reading levels

Convert to real-world scenario

Translate activity

More...

Content View
Student View

50 questions

Show all answers

MULTIPLE SELECT QUESTION

1 min • 1 pt

You have the following dataset and need to calculate the average salary per department, but only include departments where the average salary is greater than 4000:

df.groupBy(\Department\").avg(\"Salary\").filter(\"avg(Salary) > 4000\")"

df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, AVG(Salary) AS AvgSalary FROM employees GROUP BY Department HAVING AVG(Salary) > 4000\")"

from pyspark.sql.functions import col, avg\n\ndf.groupBy(\Department\").agg(avg(col(\"Salary\")).alias(\"AvgSalary\")).filter(col(\"AvgSalary\") > 4000)"

df.filter(\Salary > 4000\").groupBy(\"Department\").avg(\"Salary\")"

MULTIPLE SELECT QUESTION

1 min • 1 pt

You need to find all employees in departments with a budget greater than 8000. Which code snippet achieves this?

employees_df.join(departments_df, \Department\").filter(\"Budget > 8000\").select(\"Name\", \"Department\")"

employees_df.createOrReplaceTempView(\employees\")\ndepartments_df.createOrReplaceTempView(\"departments\")\nspark.sql(\"SELECT e.Name, e.Department FROM employees e JOIN departments d ON e.Department = d.Department WHERE d.Budget > 8000\")"

from pyspark.sql.functions import col\n\nemployees_df.join(departments_df, \Department\").where(col(\"Budget\") > 8000).select(\"Name\", \"Department\")"

employees_df.join(departments_df, employees_df.Department == departments_df.Department).filter(\Budget > 8000\")"

MULTIPLE SELECT QUESTION

1 min • 1 pt

From the dataset below, find the top-earning employee in each department, including their department name and salary. Use Spark SQL:

df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, Name, Salary FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS rank FROM employees) t WHERE rank = 1\")"

df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, Name, MAX(Salary) AS Salary FROM employees GROUP BY Department, Name\")"

df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, Name, MAX(Salary) AS MaxSalary FROM employees GROUP BY Department\")"

from pyspark.sql.window import Window\nfrom pyspark.sql.functions import col, row_number\n\nwindow_spec = Window.partitionBy(\Department\").orderBy(col(\"Salary\").desc())\ndf.withColumn(\"rank\", row_number().over(window_spec)).filter(\"rank == 1\").select(\"Department\", \"Name\", \"Salary\")"

MULTIPLE SELECT QUESTION

1 min • 1 pt

Which of the following codes computes the total sales per region efficiently in Spark SQL?

df.createOrReplaceTempView(\sales\")\nspark.sql(\"SELECT Region, SUM(Sales) AS TotalSales FROM sales GROUP BY Region\")"

df.groupBy(\Region\").sum(\"Sales\")"

from pyspark.sql.functions import col, sum\n\ndf.groupBy(col(\Region\")).agg(sum(col(\"Sales\")).alias(\"TotalSales\"))"

df.filter(\Sales IS NOT NULL\").groupBy(\"Region\").sum(\"Sales\")"

MULTIPLE SELECT QUESTION

1 min • 1 pt

How can you rank products within each category by their sales in descending order?

from pyspark.sql.window import Window\nfrom pyspark.sql.functions import col, rank\n\nwindow_spec = Window.partitionBy(\Category\").orderBy(col(\"Sales\").desc())\ndf.withColumn(\"rank\", rank().over(window_spec))"

df.createOrReplaceTempView(\products\")\nspark.sql(\"SELECT Category, Product, RANK() OVER (PARTITION BY Category ORDER BY Sales DESC) AS Rank FROM products\")"

df.groupBy(\Category\", \"Product\").agg(rank().alias(\"Rank\"))"

from pyspark.sql.functions import row_number\n\nwindow_spec = Window.partitionBy(\Category\").orderBy(\"Sales DESC\")\ndf.withColumn(\"row_number\", row_number().over(window_spec))"

MULTIPLE SELECT QUESTION

1 min • 1 pt

Which function would you use to replace all NULL values in a Spark DataFrame with default values?

df.na.fill({\Column1\": 0, \"Column2\": \"N/A\"})"

df.na.replace(NULL, {\Column1\": 0, \"Column2\": \"N/A\"})"

df.fillna({\Column1\": 0, \"Column2\": \"N/A\"})"

df.replace(NULL, {\Column1\": 0, \"Column2\": \"N/A\"})"

MULTIPLE SELECT QUESTION

1 min • 1 pt

How do you efficiently join two DataFrames on multiple keys in PySpark?

df1.join(df2, (df1.Key1 == df2.Key1) & (df1.Key2 == df2.Key2))

df1.join(df2, \Key1\", \"inner\")"

df1.createOrReplaceTempView(\df1\")\ndf2.createOrReplaceTempView(\"df2\")\nspark.sql(\"SELECT * FROM df1 JOIN df2 ON df1.Key1 = df2.Key1 AND df1.Key2 = df2.Key2\")"

df1.merge(df2, \Key1\")"

Access all questions and much more by creating a free account

Create resources

Host any resource

Get auto-graded reports

Continue with Google

Continue with Email

Continue with Microsoft

or continue with

Facebook

Apple

Others

By signing up, you agree to our Terms of Service & Privacy Policy

Already have an account?

Similar Resources on Wayground

51 questions

Cuestionario sobre Inteligencia Artificial 50P

Quiz

•

Professional Development

Popular Resources on Wayground

21 questions

Continents and Oceans

Quiz

•

6th Grade

20 questions

Parts of Speech

Quiz

•

5th Grade

16 questions

Subject & Predicate

Quiz

•

5th Grade

25 questions

Multiplication Facts

Quiz

•

5th Grade

12 questions

Map Skills

Quiz

•

3rd Grade

22 questions

Continents and Oceans

Quiz

•

5th Grade

15 questions

Rounding Decimals

Quiz

•

5th Grade

20 questions

Brand Labels

Quiz

•

5th - 12th Grade

Discover more resources for Instructional Technology

10 questions

How to Email your Teacher

Quiz

•

Professional Development

40 questions

Flags of the World

Quiz

•

KG - Professional Dev...

4 questions

Exit ticket for the end of class: Spotlight

Quiz

•

KG - Professional Dev...

20 questions

90s Cartoons

Quiz

•

Professional Development

20 questions

Block Buster Movies

Quiz

•

10th Grade - Professi...

10 questions

How to Email your Teacher

Quiz

•

Professional Development