Top PySpark Interview Questions on Employee and Department Datasets (with Examples)

SAS
SAS
June 23, 2025
0
Top PySpark Interview Questions on Employee and Department Datasets (with Examples)
Answer Thumbnail

In this post, you'll find all possible PySpark questions and solutions based on the classic Employee and Department datasets. Each question includes the input code, clear explanations, and practical use cases to help you understand core PySpark concepts. Whether you're preparing for an interview or brushing up on your PySpark skills, this guide is packed with helpful examples.

Input List

from pyspark.sql.types import *

dept_schema = StructType([
    StructField("DepartmentID", IntegerType(), False),
    StructField("DepartmentName", StringType(), False),
    StructField("Location", StringType(), True)
])

emp_schema = StructType([
    StructField("EmployeeID", IntegerType(), False),
    StructField("FirstName", StringType(), True),
    StructField("LastName", StringType(), True),
    StructField("DepartmentID", IntegerType(), True),
    StructField("ManagerID", IntegerType(), True),
    StructField("JobTitle", StringType(), True),
    StructField("Salary", DecimalType(10,2), True),
    StructField("HireDate", DateType(), True),
    StructField("BirthDate", DateType(), True),
    StructField("Email", StringType(), True),
    StructField("Bonus", DecimalType(10,2), True),
    StructField("RemoteWork", BooleanType(), True)
])

emp_hist_schema = StructType([
    StructField("HistoryID", IntegerType(), False),
    StructField("EmployeeID", IntegerType(), False),
    StructField("DepartmentID", IntegerType(), True),
    StructField("JobTitle", StringType(), True),
    StructField("Salary", DecimalType(10,2), True),
    StructField("StartDate", DateType(), False),
    StructField("EndDate", DateType(), True)
])


from pyspark.sql import functions as F

# Convert date strings to date objects for Employee and History data
def parse_dates(data, date_cols):
    for row in data:
        for col in date_cols:
            if row[col]:
                row[col] = datetime.strptime(row[col], "%Y-%m-%d")
    return data

employees_data = parse_dates(employees_data, ["HireDate", "BirthDate"])
employee_history_data = parse_dates(employee_history_data, ["StartDate", "EndDate"])

departments_df = spark.createDataFrame(departments_data, schema=dept_schema)
employees_df = spark.createDataFrame(employees_data, schema=emp_schema)
employee_history_df = spark.createDataFrame(employee_history_data, schema=emp_hist_schema)

# Show sample
departments_df.show(3)
employees_df.show(5)
employee_history_df.show(5)





employees_df.createOrReplaceTempView("Employee")
departments_df.createOrReplaceTempView("Department")
employee_history_df.createOrReplaceTempView("EmployeeHistory")

# SQL example: find employees without manager
spark.sql("SELECT * FROM Employee WHERE ManagerID IS NULL").show()

# PySpark example: employees without manager
employees_df.filter(F.col("ManagerID").isNull()).show()



departments_data = [
    {"DepartmentID":1, "DepartmentName":"Sales", "Location":"New York"},
    {"DepartmentID":2, "DepartmentName":"Engineering", "Location":"San Francisco"},
    {"DepartmentID":3, "DepartmentName":"HR", "Location":"Chicago"},
    {"DepartmentID":4, "DepartmentName":"Finance", "Location":"Boston"},
]






employees_data = [
    # Sales
    {"EmployeeID":101, "FirstName":"Alice", "LastName":"Anderson", "DepartmentID":1, "ManagerID":None, "JobTitle":"Sales Manager", "Salary":90000.00, "HireDate":"2015-03-10", "BirthDate":"1980-05-12", "Email":"alice.anderson@example.com", "Bonus":5000.00, "RemoteWork":False},
    {"EmployeeID":102, "FirstName":"Bob", "LastName":"Brown", "DepartmentID":1, "ManagerID":101, "JobTitle":"Sales Executive", "Salary":60000.00, "HireDate":"2018-06-21", "BirthDate":"1990-11-22", "Email":"bob.brown@example.com", "Bonus":2000.00, "RemoteWork":False},
    {"EmployeeID":103, "FirstName":"Carol", "LastName":"Clark", "DepartmentID":1, "ManagerID":101, "JobTitle":"Sales Executive", "Salary":62000.00, "HireDate":"2020-01-15", "BirthDate":"1992-07-15", "Email":"carol.clark@example.com", "Bonus":2500.00, "RemoteWork":True},
    {"EmployeeID":104, "FirstName":"David", "LastName":"Davis", "DepartmentID":1, "ManagerID":102, "JobTitle":"Sales Executive", "Salary":58000.00, "HireDate":"2019-09-01", "BirthDate":"1988-08-08", "Email":"david.davis@example.com", "Bonus":2200.00, "RemoteWork":False},
    {"EmployeeID":105, "FirstName":"Eva", "LastName":"Evans", "DepartmentID":1, "ManagerID":102, "JobTitle":"Sales Executive", "Salary":61000.00, "HireDate":"2017-04-11", "BirthDate":"1991-02-27", "Email":"eva.evans@example.com", "Bonus":2100.00, "RemoteWork":True},
    {"EmployeeID":106, "FirstName":"Frank", "LastName":"Foster", "DepartmentID":1, "ManagerID":103, "JobTitle":"Sales Associate", "Salary":45000.00, "HireDate":"2022-02-10", "BirthDate":"1995-12-20", "Email":"frank.foster@example.com", "Bonus":None, "RemoteWork":True},
    {"EmployeeID":107, "FirstName":"Grace", "LastName":"Green", "DepartmentID":1, "ManagerID":103, "JobTitle":"Sales Associate", "Salary":46000.00, "HireDate":"2023-01-05", "BirthDate":"1994-06-13", "Email":"grace.green@example.com", "Bonus":None, "RemoteWork":False},
    {"EmployeeID":108, "FirstName":"Henry", "LastName":"Hill", "DepartmentID":1, "ManagerID":104, "JobTitle":"Sales Associate", "Salary":47000.00, "HireDate":"2021-11-23", "BirthDate":"1996-03-30", "Email":"henry.hill@example.com", "Bonus":None, "RemoteWork":True},
    {"EmployeeID":109, "FirstName":"Ivy", "LastName":"Ingram", "DepartmentID":1, "ManagerID":105, "JobTitle":"Sales Associate", "Salary":48000.00, "HireDate":"2020-12-15", "BirthDate":"1993-09-10", "Email":"ivy.ingram@example.com", "Bonus":None, "RemoteWork":False},
    {"EmployeeID":110, "FirstName":"Jack", "LastName":"Jones", "DepartmentID":1, "ManagerID":105, "JobTitle":"Sales Associate", "Salary":44000.00, "HireDate":"2016-08-29", "BirthDate":"1987-01-07", "Email":"jack.jones@example.com", "Bonus":None, "RemoteWork":False},

    # Engineering
    {"EmployeeID":201, "FirstName":"Kevin", "LastName":"King", "DepartmentID":2, "ManagerID":None, "JobTitle":"Engineering Manager", "Salary":110000.00, "HireDate":"2014-07-10", "BirthDate":"1978-12-05", "Email":"kevin.king@example.com", "Bonus":7000.00, "RemoteWork":False},
    {"EmployeeID":202, "FirstName":"Laura", "LastName":"Lee", "DepartmentID":2, "ManagerID":201, "JobTitle":"Software Engineer", "Salary":85000.00, "HireDate":"2019-08-19", "BirthDate":"1991-04-17", "Email":"laura.lee@example.com", "Bonus":3000.00, "RemoteWork":True},
    {"EmployeeID":203, "FirstName":"Mike", "LastName":"Morris", "DepartmentID":2, "ManagerID":201, "JobTitle":"Software Engineer", "Salary":87000.00, "HireDate":"2020-10-05", "BirthDate":"1990-10-25", "Email":"mike.morris@example.com", "Bonus":3200.00, "RemoteWork":False},
    {"EmployeeID":204, "FirstName":"Nina", "LastName":"Nelson", "DepartmentID":2, "ManagerID":202, "JobTitle":"Software Engineer", "Salary":83000.00, "HireDate":"2018-03-12", "BirthDate":"1992-01-30", "Email":"nina.nelson@example.com", "Bonus":2900.00, "RemoteWork":False},
    {"EmployeeID":205, "FirstName":"Oscar", "LastName":"Owens", "DepartmentID":2, "ManagerID":202, "JobTitle":"QA Engineer", "Salary":70000.00, "HireDate":"2021-01-18", "BirthDate":"1993-07-02", "Email":"oscar.owens@example.com", "Bonus":1800.00, "RemoteWork":True},
    {"EmployeeID":206, "FirstName":"Pam", "LastName":"Peters", "DepartmentID":2, "ManagerID":203, "JobTitle":"QA Engineer", "Salary":71000.00, "HireDate":"2022-06-20", "BirthDate":"1994-11-15", "Email":"pam.peters@example.com", "Bonus":1900.00, "RemoteWork":True},
    {"EmployeeID":207, "FirstName":"Quinn", "LastName":"Quincy", "DepartmentID":2, "ManagerID":203, "JobTitle":"DevOps Engineer", "Salary":75000.00, "HireDate":"2017-09-01", "BirthDate":"1989-05-23", "Email":"quinn.quincy@example.com", "Bonus":2500.00, "RemoteWork":False},
    {"EmployeeID":208, "FirstName":"Rachel", "LastName":"Reed", "DepartmentID":2, "ManagerID":204, "JobTitle":"DevOps Engineer", "Salary":74000.00, "HireDate":"2016-12-30", "BirthDate":"1988-08-11", "Email":"rachel.reed@example.com", "Bonus":2400.00, "RemoteWork":False},
    {"EmployeeID":209, "FirstName":"Steve", "LastName":"Smith", "DepartmentID":2, "ManagerID":205, "JobTitle":"Software Engineer", "Salary":86000.00, "HireDate":"2019-11-14", "BirthDate":"1991-02-19", "Email":"steve.smith@example.com", "Bonus":3100.00, "RemoteWork":True},
    {"EmployeeID":210, "FirstName":"Tina", "LastName":"Turner", "DepartmentID":2, "ManagerID":205, "JobTitle":"Software Engineer", "Salary":88000.00, "HireDate":"2023-03-03", "BirthDate":"1995-06-22", "Email":"tina.turner@example.com", "Bonus":3300.00, "RemoteWork":False},

    # HR
    {"EmployeeID":301, "FirstName":"Uma", "LastName":"Underwood", "DepartmentID":3, "ManagerID":None, "JobTitle":"HR Manager", "Salary":80000.00, "HireDate":"2013-05-07", "BirthDate":"1977-07-14", "Email":"uma.underwood@example.com", "Bonus":4500.00, "RemoteWork":False},
    {"EmployeeID":302, "FirstName":"Victor", "LastName":"Vance", "DepartmentID":3, "ManagerID":301, "JobTitle":"HR Executive", "Salary":55000.00, "HireDate":"2019-07-12", "BirthDate":"1990-01-20", "Email":"victor.vance@example.com", "Bonus":None, "RemoteWork":False},
    {"EmployeeID":303, "FirstName":"Wendy", "LastName":"Williams", "DepartmentID":3, "ManagerID":301, "JobTitle":"HR Executive", "Salary":56000.00, "HireDate":"2020-08-25", "BirthDate":"1992-03-16", "Email":"wendy.williams@example.com", "Bonus":None, "RemoteWork":False},
    {"EmployeeID":304, "FirstName":"Xander", "LastName":"Xiong", "DepartmentID":3, "ManagerID":302, "JobTitle":"Recruiter", "Salary":52000.00, "HireDate":"2021-04-19", "BirthDate":"1993-09-12", "Email":"xander.xiong@example.com", "Bonus":None, "RemoteWork":True},
    {"EmployeeID":305, "FirstName":"Yara", "LastName":"Young", "DepartmentID":3, "ManagerID":303, "JobTitle":"Recruiter", "Salary":53000.00, "HireDate":"2022-09-30", "BirthDate":"1994-12-24", "Email":"yara.young@example.com", "Bonus":None, "RemoteWork":False},
    {"EmployeeID":306, "FirstName":"Zane", "LastName":"Zimmerman", "DepartmentID":3, "ManagerID":304, "JobTitle":"HR Associate", "Salary":48000.00, "HireDate":"2017-01-15", "BirthDate":"1988-11-05", "Email":"zane.zimmerman@example.com", "Bonus":None, "RemoteWork":True},
    {"EmployeeID":307, "FirstName":"Anna", "LastName":"Adams", "DepartmentID":3, "ManagerID":304, "JobTitle":"HR Associate", "Salary":47000.00, "HireDate":"2018-03-11", "BirthDate":"1990-06-28", "Email":"anna.adams@example.com", "Bonus":None, "RemoteWork":False},
    {"EmployeeID":308, "FirstName":"Ben", "LastName":"Baker", "DepartmentID":3, "ManagerID":305, "JobTitle":"HR Associate", "Salary":46000.00, "HireDate":"2019-05-05", "BirthDate":"1991-08-19", "Email":"ben.baker@example.com", "Bonus":None, "RemoteWork":True},
    {"EmployeeID":309, "FirstName":"Cathy", "LastName":"Cole", "DepartmentID":3, "ManagerID":305, "JobTitle":"HR Executive", "Salary":54000.00, "HireDate":"2016-11-17", "BirthDate":"1987-10-03", "Email":"cathy.cole@example.com", "Bonus":None, "RemoteWork":False},
    {"EmployeeID":310, "FirstName":"Dan", "LastName":"Duke", "DepartmentID":3, "ManagerID":306, "JobTitle":"Recruiter", "Salary":51000.00, "HireDate":"2023-02-28", "BirthDate":"1995-07-30", "Email":"dan.duke@example.com", "Bonus":None, "RemoteWork":True},

    # Finance
    {"EmployeeID":401, "FirstName":"Ellen", "LastName":"Evans", "DepartmentID":4, "ManagerID":None, "JobTitle":"Finance Manager", "Salary":95000.00, "HireDate":"2012-10-21", "BirthDate":"1979-04-22", "Email":"ellen.evans@example.com", "Bonus":6000.00, "RemoteWork":False},
    {"EmployeeID":402, "FirstName":"Fred", "LastName":"Fisher", "DepartmentID":4, "ManagerID":401, "JobTitle":"Accountant", "Salary":65000.00, "HireDate":"2018-12-01", "BirthDate":"1989-09-10", "Email":"fred.fisher@example.com", "Bonus":2200.00, "RemoteWork":True},
    {"EmployeeID":403, "FirstName":"Gina", "LastName":"Garcia", "DepartmentID":4, "ManagerID":401, "JobTitle":"Accountant", "Salary":66000.00, "HireDate":"2019-01-10", "BirthDate":"1990-05-25", "Email":"gina.garcia@example.com", "Bonus":2300.00, "RemoteWork":False},
    {"EmployeeID":404, "FirstName":"Harry", "LastName":"Hughes", "DepartmentID":4, "ManagerID":402, "JobTitle":"Financial Analyst", "Salary":70000.00, "HireDate":"2020-06-15", "BirthDate":"1991-11-30", "Email":"harry.hughes@example.com", "Bonus":2600.00, "RemoteWork":True},
    {"EmployeeID":405, "FirstName":"Isabel", "LastName":"Ives", "DepartmentID":4, "ManagerID":402, "JobTitle":"Financial Analyst", "Salary":71000.00, "HireDate":"2021-09-22", "BirthDate":"1992-02-14", "Email":"isabel.ives@example.com", "Bonus":2700.00, "RemoteWork":False},
    {"EmployeeID":406, "FirstName":"John", "LastName":"Jones", "DepartmentID":4, "ManagerID":403, "JobTitle":"Accountant", "Salary":64000.00, "HireDate":"2017-07-19", "BirthDate":"1988-07-07", "Email":"john.jones@example.com", "Bonus":2100.00, "RemoteWork":False},
    {"EmployeeID":407, "FirstName":"Karen", "LastName":"King", "DepartmentID":4, "ManagerID":403, "JobTitle":"Accountant", "Salary":63000.00, "HireDate":"2016-05-27", "BirthDate":"1987-03-29", "Email":"karen.king@example.com", "Bonus":2000.00, "RemoteWork":True},
    {"EmployeeID":408, "FirstName":"Larry", "LastName":"Long", "DepartmentID":4, "ManagerID":404, "JobTitle":"Financial Analyst", "Salary":72000.00, "HireDate":"2015-08-09", "BirthDate":"1986-12-18", "Email":"larry.long@example.com", "Bonus":2800.00, "RemoteWork":False},
    {"EmployeeID":409, "FirstName":"Mona", "LastName":"Moore", "DepartmentID":4, "ManagerID":404, "JobTitle":"Accountant", "Salary":65000.00, "HireDate":"2022-11-11", "BirthDate":"1993-10-02", "Email":"mona.moore@example.com", "Bonus":2300.00, "RemoteWork":False},
    {"EmployeeID":410, "FirstName":"Nate", "LastName":"Norris", "DepartmentID":4, "ManagerID":405, "JobTitle":"Accountant", "Salary":64000.00, "HireDate":"2023-04-01", "BirthDate":"1994-01-15", "Email":"nate.norris@example.com", "Bonus":2200.00, "RemoteWork":True},
]






employee_history_data = [
    # Sales
    {"HistoryID":1, "EmployeeID":101, "DepartmentID":1, "JobTitle":"Sales Associate", "Salary":85000.00, "StartDate":"2015-03-10", "EndDate":"2018-12-31"},
    {"HistoryID":2, "EmployeeID":101, "DepartmentID":1, "JobTitle":"Sales Manager", "Salary":90000.00, "StartDate":"2019-01-01", "EndDate":None},

    {"HistoryID":3, "EmployeeID":102, "DepartmentID":1, "JobTitle":"Sales Executive", "Salary":55000.00, "StartDate":"2018-06-21", "EndDate":"2020-12-31"},
    {"HistoryID":4, "EmployeeID":102, "DepartmentID":1, "JobTitle":"Sales Executive", "Salary":60000.00, "StartDate":"2021-01-01", "EndDate":None},

    {"HistoryID":5, "EmployeeID":103, "DepartmentID":2, "JobTitle":"Software Engineer", "Salary":70000.00, "StartDate":"2018-03-01", "EndDate":"2019-12-31"},
    {"HistoryID":6, "EmployeeID":103, "DepartmentID":1, "JobTitle":"Sales Executive", "Salary":62000.00, "StartDate":"2020-01-01", "EndDate":None},

    {"HistoryID":7, "EmployeeID":104, "DepartmentID":1, "JobTitle":"Sales Executive", "Salary":57000.00, "StartDate":"2019-09-01", "EndDate":"2021-06-30"},
    {"HistoryID":8, "EmployeeID":104, "DepartmentID":1, "JobTitle":"Sales Executive", "Salary":58000.00, "StartDate":"2021-07-01", "EndDate":None},

    {"HistoryID":9, "EmployeeID":105, "DepartmentID":1, "JobTitle":"Sales Executive", "Salary":60000.00, "StartDate":"2017-04-11", "EndDate":"2019-12-31"},
    {"HistoryID":10, "EmployeeID":105, "DepartmentID":1, "JobTitle":"Sales Executive", "Salary":61000.00, "StartDate":"2020-01-01", "EndDate":None},

    {"HistoryID":11, "EmployeeID":106, "DepartmentID":1, "JobTitle":"Sales Associate", "Salary":45000.00, "StartDate":"2022-02-10", "EndDate":None},

    {"HistoryID":12, "EmployeeID":107, "DepartmentID":1, "JobTitle":"Sales Associate", "Salary":45500.00, "StartDate":"2023-01-05", "EndDate":None},

    {"HistoryID":13, "EmployeeID":108, "DepartmentID":1, "JobTitle":"Sales Associate", "Salary":46000.00, "StartDate":"2021-11-23", "EndDate":None},

    {"HistoryID":14, "EmployeeID":109, "DepartmentID":1, "JobTitle":"Sales Associate", "Salary":47000.00, "StartDate":"2020-12-15", "EndDate":None},

    {"HistoryID":15, "EmployeeID":110, "DepartmentID":1, "JobTitle":"Sales Associate", "Salary":44000.00, "StartDate":"2016-08-29", "EndDate":None},

    # Engineering
    {"HistoryID":16, "EmployeeID":201, "DepartmentID":2, "JobTitle":"Junior Engineer", "Salary":90000.00, "StartDate":"2014-07-10", "EndDate":"2016-12-31"},
    {"HistoryID":17, "EmployeeID":201, "DepartmentID":2, "JobTitle":"Engineering Manager", "Salary":110000.00, "StartDate":"2017-01-01", "EndDate":None},

    {"HistoryID":18, "EmployeeID":202, "DepartmentID":2, "JobTitle":"Junior Engineer", "Salary":70000.00, "StartDate":"2019-08-19", "EndDate":"2021-12-31"},
    {"HistoryID":19, "EmployeeID":202, "DepartmentID":2, "JobTitle":"Software Engineer", "Salary":85000.00, "StartDate":"2022-01-01", "EndDate":None},

    {"HistoryID":20, "EmployeeID":203, "DepartmentID":2, "JobTitle":"Software Engineer", "Salary":85000.00, "StartDate":"2020-10-05", "EndDate":None},

    {"HistoryID":21, "EmployeeID":204, "DepartmentID":2, "JobTitle":"Software Engineer", "Salary":82000.00, "StartDate":"2018-03-12", "EndDate":"2020-06-30"},
    {"HistoryID":22, "EmployeeID":204, "DepartmentID":2, "JobTitle":"Software Engineer", "Salary":83000.00, "StartDate":"2020-07-01", "EndDate":None},

    {"HistoryID":23, "EmployeeID":205, "DepartmentID":2, "JobTitle":"QA Engineer", "Salary":68000.00, "StartDate":"2021-01-18", "EndDate":"2021-12-31"},
    {"HistoryID":24, "EmployeeID":205, "DepartmentID":2, "JobTitle":"QA Engineer", "Salary":70000.00, "StartDate":"2022-01-01", "EndDate":None},

    {"HistoryID":25, "EmployeeID":206, "DepartmentID":2, "JobTitle":"QA Engineer", "Salary":71000.00, "StartDate":"2022-06-20", "EndDate":None},

    {"HistoryID":26, "EmployeeID":207, "DepartmentID":2, "JobTitle":"DevOps Engineer", "Salary":74000.00, "StartDate":"2017-09-01", "EndDate":"2020-12-31"},
    {"HistoryID":27, "EmployeeID":207, "DepartmentID":2, "JobTitle":"DevOps Engineer", "Salary":75000.00, "StartDate":"2021-01-01", "EndDate":None},

    {"HistoryID":28, "EmployeeID":208, "DepartmentID":2, "JobTitle":"DevOps Engineer", "Salary":74000.00, "StartDate":"2016-12-30", "EndDate":None},

    {"HistoryID":29, "EmployeeID":209, "DepartmentID":2, "JobTitle":"Software Engineer", "Salary":86000.00, "StartDate":"2019-11-14", "EndDate":None},

    {"HistoryID":30, "EmployeeID":210, "DepartmentID":2, "JobTitle":"Software Engineer", "Salary":88000.00, "StartDate":"2023-03-03", "EndDate":None},

    # HR
    {"HistoryID":31, "EmployeeID":301, "DepartmentID":3, "JobTitle":"HR Associate", "Salary":65000.00, "StartDate":"2013-05-07", "EndDate":"2016-12-31"},
    {"HistoryID":32, "EmployeeID":301, "DepartmentID":3, "JobTitle":"HR Manager", "Salary":80000.00, "StartDate":"2017-01-01", "EndDate":None},

    {"HistoryID":33, "EmployeeID":302, "DepartmentID":3, "JobTitle":"HR Assistant", "Salary":48000.00, "StartDate":"2019-07-12", "EndDate":"2020-12-31"},
    {"HistoryID":34, "EmployeeID":302, "DepartmentID":3, "JobTitle":"HR Executive", "Salary":55000.00, "StartDate":"2021-01-01", "EndDate":None},

    {"HistoryID":35, "EmployeeID":303, "DepartmentID":3, "JobTitle":"HR Executive", "Salary":56000.00, "StartDate":"2020-08-25", "EndDate":None},

    {"HistoryID":36, "EmployeeID":304, "DepartmentID":3, "JobTitle":"Recruiter", "Salary":51000.00, "StartDate":"2021-04-19", "EndDate":None},

    {"HistoryID":37, "EmployeeID":305, "DepartmentID":3, "JobTitle":"Recruiter", "Salary":53000.00, "StartDate":"2022-09-30", "EndDate":None},

    {"HistoryID":38, "EmployeeID":306, "DepartmentID":3, "JobTitle":"HR Associate", "Salary":48000.00, "StartDate":"2017-01-15", "EndDate":None},

    {"HistoryID":39, "EmployeeID":307, "DepartmentID":3, "JobTitle":"HR Associate", "Salary":47000.00, "StartDate":"2018-03-11", "EndDate":None},

    {"HistoryID":40, "EmployeeID":308, "DepartmentID":3, "JobTitle":"HR Associate", "Salary":46000.00, "StartDate":"2019-05-05", "EndDate":None},

    {"HistoryID":41, "EmployeeID":309, "DepartmentID":3, "JobTitle":"HR Executive", "Salary":54000.00, "StartDate":"2016-11-17", "EndDate":None},

    {"HistoryID":42, "EmployeeID":310, "DepartmentID":3, "JobTitle":"Recruiter", "Salary":51000.00, "StartDate":"2023-02-28", "EndDate":None},

    # Finance
    {"HistoryID":43, "EmployeeID":401, "DepartmentID":4, "JobTitle":"Accountant", "Salary":85000.00, "StartDate":"2012-10-21", "EndDate":"2015-12-31"},
    {"HistoryID":44, "EmployeeID":401, "DepartmentID":4, "JobTitle":"Finance Manager", "Salary":95000.00, "StartDate":"2016-01-01", "EndDate":None},

    {"HistoryID":45, "EmployeeID":402, "DepartmentID":4, "JobTitle":"Accountant", "Salary":62000.00, "StartDate":"2018-12-01", "EndDate":"2019-12-31"},
    {"HistoryID":46, "EmployeeID":402, "DepartmentID":4, "JobTitle":"Accountant", "Salary":65000.00, "StartDate":"2020-01-01", "EndDate":None},

    {"HistoryID":47, "EmployeeID":403, "DepartmentID":4, "JobTitle":"Accountant", "Salary":66000.00, "StartDate":"2019-01-10", "EndDate":None},

    {"HistoryID":48, "EmployeeID":404, "DepartmentID":4, "JobTitle":"Financial Analyst", "Salary":68000.00, "StartDate":"2020-06-15", "EndDate":"2020-12-31"},
    {"HistoryID":49, "EmployeeID":404, "DepartmentID":4, "JobTitle":"Financial Analyst", "Salary":70000.00, "StartDate":"2021-01-01", "EndDate":None},

    {"HistoryID":50, "EmployeeID":405, "DepartmentID":4, "JobTitle":"Financial Analyst", "Salary":71000.00, "StartDate":"2021-09-22", "EndDate":None},

    {"HistoryID":51, "EmployeeID":406, "DepartmentID":4, "JobTitle":"Accountant", "Salary":60000.00, "StartDate":"2017-07-19", "EndDate":"2018-12-31"},
    {"HistoryID":52, "EmployeeID":406, "DepartmentID":4, "JobTitle":"Accountant", "Salary":64000.00, "StartDate":"2019-01-01", "EndDate":None},

    {"HistoryID":53, "EmployeeID":407, "DepartmentID":4, "JobTitle":"Accountant", "Salary":61000.00, "StartDate":"2016-05-27", "EndDate":"2017-12-31"},
    {"HistoryID":54, "EmployeeID":407, "DepartmentID":4, "JobTitle":"Accountant", "Salary":63000.00, "StartDate":"2018-01-01", "EndDate":None},

    {"HistoryID":55, "EmployeeID":408, "DepartmentID":4, "JobTitle":"Financial Analyst", "Salary":70000.00, "StartDate":"2015-08-09", "EndDate":"2016-12-31"},
    {"HistoryID":56, "EmployeeID":408, "DepartmentID":4, "JobTitle":"Financial Analyst", "Salary":72000.00, "StartDate":"2017-01-01", "EndDate":None},

    {"HistoryID":57, "EmployeeID":409, "DepartmentID":4, "JobTitle":"Accountant", "Salary":65000.00, "StartDate":"2022-11-11", "EndDate":None},

    {"HistoryID":58, "EmployeeID":410, "DepartmentID":4, "JobTitle":"Accountant", "Salary":64000.00, "StartDate":"2023-04-01", "EndDate":None},
]


Salary-related Queries

  1. How can you find the employee with the second-highest salary?
  2. How do you retrieve the nth highest salary from the employee records?
  3. How can you list all employees earning more than the average salary?
  4. How do you fetch the top five employees with the highest salaries?
  5. How can you calculate the total sum of all employees’ salaries?
  6. How can you select employees earning between 50,000 and 100,000?
  7. How do you select employees whose salary is a multiple of 10,000?
  8. How can you find employees who share the same salary?
  9. How do you increase the salary of all employees by 10%?
  10. How do you update salaries with different increments for different departments?
  11. How do you find the highest-paid employee in every department?
  12. How do you find the maximum and minimum salaries within each department?
  13. How do you calculate the total and average salary per department?
  14. How do you find employees earning more than the average salary of their department?
  15. How do you find employees earning more than their managers?
  16. How do you find the top three highest-paid employees in each department?
  17. How can you calculate the average salary of the top five earners in each department?
  18. How can you identify employees whose salaries are in the top 10 percentile?
  19. How do you display the highest, lowest, and average salary for each job role?
  20. How do you group employees by salary ranges such as 0–20K and 20K–50K?

Employee Count and Grouping Queries

  1. How do you get the count of employees in each department?
  2. How can you find the department with the highest number of employees?
  3. How can you find the department with the lowest average salary?
  4. How do you count the total number of departments in the company?
  5. How can you get the total number of employees grouped by their job titles?
  6. How can you find managers who have more than five direct reports?
  7. How do you calculate the percentage distribution of employees across departments?
  8. How do you group employees based on their age ranges (e.g., 20–30, 31–40)?

Employee Identification and Filtering by Attributes

  1. How do you find employees who joined the company in the year 2022?
  2. How do you find employees hired within the last six months?
  3. How can you display employees with more than five years of tenure?
  4. How can you list employees with more than five years of experience in each department?
  5. How do you find employees older than 30 years?
  6. How do you select employees earning between 50,000 and 100,000?
  7. How do you list employees who do not receive any bonus?
  8. How can you find employees whose names start with the letter 'A'?
  9. How do you find employees whose names end with the letter ‘n’?
  10. How do you count employees whose names start and end with the same letter?
  11. How can you find employees with the same first name?
  12. How can you find employees whose email addresses contain the domain '@example.com'?
  13. How can you find employees who do not have a department?
  14. How can you find employees who do not have a manager?
  15. How can you find employees who do not have any subordinates?

Hierarchy and Reporting Structure Queries

  1. How can you list employees reporting to a specific manager?
  2. How can you list employees reporting to the same manager?
  3. How can you find employees who joined in the same month and year as their managers?
  4. How do you find employees earning more than their managers?
  5. How can you find managers who have more than five direct reports?

Formatting and Output Queries

  1. How do you export all employee records in XML format?
  2. How can you concatenate employee names and their salaries into a single string?
  3. How do you display employee names alongside their hire dates formatted as “Name - MM/DD/YYYY”?
  4. How do you display the hire date along with the day of the week for each employee?

Data Modification Queries

  1. How do you increase the salary of all employees by 10%?
  2. How do you update salaries with different increments for different departments?
  3. How do you delete all employees belonging to a specific department?
  4. How do you remove employees who have worked for more than 15 years?



Stay tuned for answers for these question.




Tags:

You Might Like

Show more
PySpark-Q&A

Post a Comment

0Comments

Post a Comment (0)
Share to other apps
Copy Post Link