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

Quiz

•

Instructional Technology

•

Professional Development

•

Practice Problem

•

Hard

Sinh Hoàng Văn

FREE Resource

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\")"

Create a free account and access millions of resources

Create resources

Host any resource

Get auto-graded reports

Continue with Google

Continue with Email

Continue with Classlink

Continue with Clever

or continue with

Microsoft

Apple

Others

Already have an account?

Similar Resources on Wayground

49 questions

FFH - CLS PureFibre New Hire Quiz 2

Quiz

•

Professional Development

45 questions

đề 2 tud

Quiz

•

Professional Development

Popular Resources on Wayground

10 questions

Honoring the Significance of Veterans Day

Interactive video

•

6th - 10th Grade

9 questions

FOREST Community of Caring

Lesson

•

1st - 5th Grade

10 questions

Exploring Veterans Day: Facts and Celebrations for Kids

Interactive video

•

6th - 10th Grade

19 questions

Veterans Day

Quiz

•

5th Grade

14 questions

General Technology Use Quiz

Quiz

•

8th Grade

25 questions

Multiplication Facts

Quiz

•

5th Grade

15 questions

Circuits, Light Energy, and Forces

Quiz

•

5th Grade

19 questions

Thanksgiving Trivia

Quiz

•

6th Grade

Discover more resources for Instructional Technology

10 questions

Identifying Phishing Emails Quiz

Quiz

•

Professional Development

14 questions

2019 Logos

Quiz

•

Professional Development

10 questions

Thanksgiving Day Quiz - reading passage

Passage

•

Professional Development

20 questions

Prepositions of Place

Quiz

•

Professional Development