How can we rewrite sub‐queries into simple select statements or with joins?

Yes we can write using Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statemnt. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

E.g.
USE AdventureWorks
GO WITH EmployeeDepartment_CTE AS
( SELECT EmployeeID,DepartmentID,ShiftID FROM HumanResources.EmployeeDepartmentHistory )
SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID
FROM HumanResources.Department ed
INNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID = ed.DepartmentID
GO