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 Classlink

Continue with Clever

or continue with

Microsoft

Apple

Others

Already have an account?

Similar Resources on Wayground

49 questions

Public Safety 1 Final Review Quiz 1

Quiz

•

Professional Development

53 questions

từ câu 151 đến 200

Quiz

•

Professional Development

50 questions

Simulado AZ-900

Quiz

•

Professional Development

Popular Resources on Wayground

20 questions

STAAR Review Quiz #3

Quiz

•

8th Grade

20 questions

Equivalent Fractions

Quiz

•

3rd Grade

6 questions

Marshmallow Farm Quiz

Quiz

•

2nd - 5th Grade

20 questions

Main Idea and Details

Quiz

•

5th Grade

20 questions

Context Clues

Quiz

•

6th Grade

20 questions

Inferences

Quiz

•

4th Grade

19 questions

Classifying Quadrilaterals

Quiz

•

3rd Grade

12 questions

What makes Nebraska's government unique?

Quiz

•

4th - 5th Grade

Discover more resources for Instructional Technology

20 questions

Easter trivia

Quiz

•

12th Grade - Professi...