Problem Statement: -
- Get all employee details from EmployeeDetail table whose joining month is Jan(1).
- Get all employee details from EmployeeDetail table whose joining date between 2013-01-01" and "2013-12-01".
- Get how many employee exist in "EmployeeDetail" table.
- Select all employee detail with First name "Vikas","Ashish", and "Nikhil".
df.filter(month("Joining_Date") == 1).show()
df.filter(col("Joining_Date").between("2013-01-01","2013-12-01")).show()
cnt = df.count()
print(f"Number of employees = {cnt}")
+----------+----------+---------+---------+--------------------+----------+------+
|EmployeeID|First_Name|Last_Name| Salary| Joining_Date|Department|Gender|
+----------+----------+---------+---------+--------------------+----------+------+
| 2| nikita| Jain| 530000.0|2014-01-09 17:31:...| HR|Female|
| 3| Ashish| Kumar|1000000.0|2014-01-09 10:05:...| IT| Male|
| 4| Nikhil| Sharma| 480000.0|2014-01-09 09:00:...| HR| Male|
| 5| anish| kadian| 500000.0|2014-01-09 09:31:...| Payroll| Male|
+----------+----------+---------+---------+--------------------+----------+------+
+----------+----------+---------+--------+--------------------+----------+------+
|EmployeeID|First_Name|Last_Name| Salary| Joining_Date|Department|Gender|
+----------+----------+---------+--------+--------------------+----------+------+
| 1| Vikas| Ahlawat|600000.0|2013-02-15 11:16:...| IT| Male|
+----------+----------+---------+--------+--------------------+----------+------+
Number of employees = 5
isin()
df.filter(lower(col("First_Name")).isin("vikas","ashish","nikhil")).show()
+----------+----------+---------+---------+--------------------+----------+------+
|EmployeeID|First_Name|Last_Name| Salary| Joining_Date|Department|Gender|
+----------+----------+---------+---------+--------------------+----------+------+
| 1| Vikas| Ahlawat| 600000.0|2013-02-15 11:16:...| IT| Male|
| 3| Ashish| Kumar|1000000.0|2014-01-09 10:05:...| IT| Male|
| 4| Nikhil| Sharma| 480000.0|2014-01-09 09:00:...| HR| Male|
+----------+----------+---------+---------+--------------------+----------+------+