Font size
S
M
L
XL
WorksheetsData Lab PreTest
Total questions: 50
Worksheet time: 59mins
Name
Class
Date
1.
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:
a)
df.groupBy(\Department\").avg(\"Salary\").filter(\"avg(Salary) > 4000\")"
b)
df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, AVG(Salary) AS AvgSalary FROM employees GROUP BY Department HAVING AVG(Salary) > 4000\")"
c)
from pyspark.sql.functions import col, avg\n\ndf.groupBy(\Department\").agg(avg(col(\"Salary\")).alias(\"AvgSalary\")).filter(col(\"AvgSalary\") > 4000)"
d)
df.filter(\Salary > 4000\").groupBy(\"Department\").avg(\"Salary\")"
2.
You need to find all employees in departments with a budget greater than 8000. Which code snippet achieves this?
a)
employees_df.join(departments_df, \Department\").filter(\"Budget > 8000\").select(\"Name\", \"Department\")"
b)
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\")"
c)
from pyspark.sql.functions import col\n\nemployees_df.join(departments_df, \Department\").where(col(\"Budget\") > 8000).select(\"Name\", \"Department\")"
d)
employees_df.join(departments_df, employees_df.Department == departments_df.Department).filter(\Budget > 8000\")"
3.
From the dataset below, find the top-earning employee in each department, including their department name and salary. Use Spark SQL:
a)
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\")"
b)
df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, Name, MAX(Salary) AS Salary FROM employees GROUP BY Department, Name\")"
c)
df.createOrReplaceTempView(\employees\")\nspark.sql(\"SELECT Department, Name, MAX(Salary) AS MaxSalary FROM employees GROUP BY Department\")"
d)
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\")"
4.
Which of the following codes computes the total sales per region efficiently in Spark SQL?
a)
df.createOrReplaceTempView(\sales\")\nspark.sql(\"SELECT Region, SUM(Sales) AS TotalSales FROM sales GROUP BY Region\")"
b)
df.groupBy(\Region\").sum(\"Sales\")"
c)
from pyspark.sql.functions import col, sum\n\ndf.groupBy(col(\Region\")).agg(sum(col(\"Sales\")).alias(\"TotalSales\"))"
d)
df.filter(\Sales IS NOT NULL\").groupBy(\"Region\").sum(\"Sales\")"
5.
How can you rank products within each category by their sales in descending order?
a)
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))"
b)
df.createOrReplaceTempView(\products\")\nspark.sql(\"SELECT Category, Product, RANK() OVER (PARTITION BY Category ORDER BY Sales DESC) AS Rank FROM products\")"
c)
df.groupBy(\Category\", \"Product\").agg(rank().alias(\"Rank\"))"
d)
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))"
6.
Which function would you use to replace all NULL values in a Spark DataFrame with default values?
a)
df.na.fill({\Column1\": 0, \"Column2\": \"N/A\"})"
b)
df.na.replace(NULL, {\Column1\": 0, \"Column2\": \"N/A\"})"
c)
df.fillna({\Column1\": 0, \"Column2\": \"N/A\"})"
d)
df.replace(NULL, {\Column1\": 0, \"Column2\": \"N/A\"})"
7.
How do you efficiently join two DataFrames on multiple keys in PySpark?
a)
df1.join(df2, (df1.Key1 == df2.Key1) & (df1.Key2 == df2.Key2))
b)
df1.join(df2, \Key1\", \"inner\")"
c)
df1.createOrReplaceTempView(\df1\")\ndf2.createOrReplaceTempView(\"df2\")\nspark.sql(\"SELECT * FROM df1 JOIN df2 ON df1.Key1 = df2.Key1 AND df1.Key2 = df2.Key2\")"
d)
df1.merge(df2, \Key1\")"
8.
How would you extract the year from a date column in Spark DataFrame?
a)
from pyspark.sql.functions import year\n\ndf.withColumn(\Year\", year(\"Date\"))"
b)
df.selectExpr(\YEAR(Date) AS Year\")"
c)
df.withColumn(\Year\", df.Date.year())"
d)
df.createOrReplaceTempView(\table\")\nspark.sql(\"SELECT YEAR(Date) AS Year FROM table\")"
9.
What is the correct way to calculate the 90th percentzasdasdile of a column in PySpark?
a)
from pyspark.sql.functions import expr\n\ndf.selectExpr(\percentile_approx(column, 0.9)\")"
b)
df.selectExpr(\approx_percentile(column, 0.9)\")"
c)
from pyspark.sql.functions import percentile_approx\n\ndf.select(percentile_approx(\column\", 0.9))"
d)
df.select(\column\").approx_percentile(0.9)"
10.
How would you add a unique identifier to each row in a Spark DataFrame?
a)
from pyspark.sql.functions import monotonically_increasing_id\n\ndf.withColumn(\unique_id\", monotonically_increasing_id())"
b)
df.withColumn(\unique_id\", row_number())"
c)
df.createOrReplaceTempView(\table\")\nspark.sql(\"SELECT *, ROW_NUMBER() OVER (ORDER BY some_column) AS unique_id FROM table\")"
d)
df.withColumn(\unique_id\", dense_rank())"
11.
Which query selects all employees who do not belong to a department?
a)
SELECT * FROM employees WHERE department_id = NULL;
b)
SELECT * FROM employees WHERE department_id IS NULL;
c)
SELECT * FROM employees WHERE department_id != NULL;
d)
SELECT * FROM employees WHERE department_id IS NOT NULL;
12.
What is the difference between UNION and UNION ALL?
a)
UNION removes duplicates; UNION ALL includes duplicates
b)
UNION includes duplicates; UNION ALL removes duplicates
c)
Both remove duplicates
d)
Both include duplicates
13.
Which function calculates the total number of rows in a table?
a)
COUNT(*)
b)
SUM(*)
c)
TOTAL(*)
d)
ROW_COUNT()
14.
How do you calculate the average salary per department?
a)
SELECT department_id, AVG(salary) FROM employees;
b)
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
c)
SELECT AVG(salary) FROM employees GROUP BY department_id;
d)
SELECT AVG(salary) FROM employees;
15.
Which query retrieves the first 5 rows of a table?
a)
SELECT * FROM employees LIMIT 5;
b)
SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;
c)
Both A and B
d)
SELECT TOP 5 * FROM employees;
16.
What happens when NULL is compared to any value using =?
a)
Returns TRUE
b)
Returns FALSE
c)
Returns NULL
d)
Returns an error
17.
How do you rename a column in a SELECT query?
a)
SELECT column_name RENAME TO new_name FROM table;
b)
SELECT column_name AS new_name FROM table;
c)
RENAME column_name TO new_name;
d)
SELECT RENAME column_name TO new_name FROM table;
18.
What is the purpose of the DISTINCT keyword?
a)
Removes NULL values
b)
Sorts the data
c)
Removes duplicate rows
d)
Returns a count of unique rows
19.
How do you filter rows using a calculated column in SQL?
a)
WHERE calculated_column > 10
b)
HAVING calculated_column > 10
c)
FILTER BY calculated_column > 10
d)
SELECT * WHERE calculated_column > 10
20.
What does this query return? SELECT MIN(salary) FROM employees;
a)
The highest salary in the table
b)
The lowest salary in the table
c)
The average salary in the table
d)
An error if there are NULLs
21.
What is the default order of rows in a SELECT query?
a)
Ascending
b)
Descending
c)
Based on primary key
d)
Unordered
22.
Which clause allows filtering aggregated data?
a)
WHERE
b)
GROUP BY
c)
HAVING
d)
ORDER BY
23.
How do you find employees whose names contain 'Smith'?
a)
SELECT * FROM employees WHERE name LIKE '%Smith%';
b)
SELECT * FROM employees WHERE name = '%Smith%';
c)
SELECT * FROM employees WHERE name CONTAINS 'Smith';
d)
SELECT * FROM employees WHERE name IN 'Smith';
24.
What is the result of this query? SELECT NULL + 10;
a)
10
b)
NULL
c)
Throws an error
d)
Depends on the database
25.
What does this query do? SELECT COUNT(DISTINCT department_id) FROM employees;
a)
Counts all departments, including duplicates
b)
Counts all employees in each department
c)
Counts unique department IDs
d)
Counts all employees
26.
How do you write a query to find the second highest salary?
a)
SELECT MAX(salary) FROM employees WHERE salary != MAX(salary);
b)
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
c)
SELECT salary FROM employees ORDER BY salary ASC LIMIT 2;
d)
SELECT TOP 2 salary FROM employees;
27.
What is the difference between ROW_NUMBER() and RANK()?
a)
ROW_NUMBER() allows ties; RANK() does not
b)
RANK() allows ties; ROW_NUMBER() does not
c)
Both allow ties
d)
Both do not allow ties
28.
How do you calculate the cumulative sum of sales?
a)
SELECT SUM(sales) FROM sales;
b)
SELECT SUM(sales) OVER (ORDER BY date) FROM sales;
c)
SELECT SUM(sales) PARTITION BY date FROM sales;
d)
SELECT sales + SUM(sales) FROM sales;
29.
Which query finds employees hired in 2020?
a)
SELECT * FROM employees WHERE hire_date = '2020';
b)
SELECT * FROM employees WHERE hire_date LIKE '2020%';
c)
SELECT * FROM employees WHERE YEAR(hire_date) = 2020;
d)
SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2020-12-31';
30.
What does this query return? SELECT COUNT(*) FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);
a)
Employees earning less than anyone in department 10
b)
Employees earning more than anyone in department 10
c)
Employees earning the most in department 10
d)
Throws an error
31.
A central repository that integrates data from multiple sources, primarily used for analysis and reporting.
a)
Operational Database
b)
Data Warehouse
c)
Data Mart
d)
ETL System
32.
A process of extracting data from source systems, transforming it into a usable format, and loading it into the data warehouse.
a)
Data Loading
b)
Data Cleaning
c)
ETL
d)
Data Migration
33.
A schema design that uses a single central fact table connected to multiple dimension tables.
a)
Star Schema
b)
Snowflake Schema
c)
Galaxy Schema
d)
Hierarchical Schema
34.
A process that involves breaking down large data tables into smaller ones to reduce redundancy and dependency.
a)
Data Normalization
b)
Data Partitioning
c)
Data Denormalization
d)
Data Compression
35.
A type of dimension table that stores attributes changing slowly over time.
a)
Rapidly Changing Dimension
b)
Slowly Changing Dimension
c)
Fixed Dimension
d)
Conformed Dimension
36.
A unique, system-generated identifier used as a primary key in data warehouse tables.
a)
Natural Key
b)
Composite Key
c)
Surrogate Key
d)
Foreign Key
37.
A type of table designed to store quantitative metrics related to business processes.
a)
Dimension Table
b)
Fact Table
c)
Metadata Table
d)
Lookup Table
38.
A design concept where dimension tables are normalized into multiple related tables.
a)
Star Schema
b)
Snowflake Schema
c)
Fact Constellation
d)
Hybrid Schema
39.
A tool or model that allows for multidimensional data analysis in a data warehouse.
a)
Data Cube
b)
Star Schema
c)
OLAP Cube
d)
ETL Process
40.
A smaller subset of a data warehouse focused on a specific business area.
a)
Data Lake
b)
Data Mart
c)
OLAP Cube
d)
Fact Table
41.
A process in data warehousing used to detect and correct errors in data.
a)
Data Mining
b)
Data Cleaning
c)
Data Migration
d)
Data Profiling
42.
A data warehouse structure that provides low latency for detailed data queries.
a)
MOLAP
b)
ROLAP
c)
HOLAP
d)
Hybrid OLAP
43.
A type of table used to track changes to a dimension over time.
a)
Historical Table
b)
Snapshot Table
c)
Slowly Changing Dimension Table
d)
Fact Table
44.
The smallest unit of data stored in a data warehouse, representing the most detailed level.
a)
Data Grain
b)
Data Granularity
c)
Data Scope
d)
Data Partition
45.
A system that combines real-time operational and analytical processing.
a)
OLAP
b)
OLTP
c)
Hybrid OLAP (HOLAP)
d)
ROLAP
46.
A dimension shared across multiple fact tables to ensure consistency.
a)
Conformed Dimension
b)
Shared Dimension
c)
Normalized Dimension
d)
Global Dimension
47.
A central repository that stores both structured and unstructured data for analysis.
a)
Data Warehouse
b)
Data Lake
c)
Operational Database
d)
Data Mart
48.
A design that optimizes data storage for faster analytical queries.
a)
Normalized Schema
b)
Dimensional Model
c)
Entity-Relationship Model
d)
Hierarchical Model
49.
A process of summarizing and aggregating detailed data in a data warehouse.
a)
Data Cleansing
b)
Data Summarization
c)
Data Profiling
d)
Data Loading
50.
Câu hỏi khó về thuật toán và python
4 lines
Reset
