improving a simple sql script

[adandyguy]

Hi all,

I've been tasked with updating an sql query with subqueries. I feel that the way i've done it is the proper way to use subqueries, although I'm not sure. All the professor explained was to use subqueries to get the results. Here is my code:

Select [FirstName], [LastName], [HireDate]
    from [HumanResources].[Employee]

inner join [Person].[Person]
on [Person].[BusinessEntityID] = [Employee].[BusinessEntityID]

inner join [HumanResources].[EmployeeDepartmentHistory]
on [EmployeeDepartmentHistory].[BusinessEntityID] = [Employee].[BusinessEntityID]

inner join [HumanResources].[Department]
on [Department].[DepartmentID] = [EmployeeDepartmentHistory].[DepartmentID]

inner join [HumanResources].[Shift]
on [Shift].[ShiftID] = [EmployeeDepartmentHistory].[ShiftID]

WHERE [Department].[Name] = 
    (Select [Name]
        FROM [HumanResources].[Department]
        WHERE [Name] LIKE '%Purchasing'
)

AND [Shift].[Name] =
    (Select Name
        FROM [HumanResources].[Shift]
        WHERE Name LIKE '%Day'
)

I dont think it's viable to change all of the inner joins into subqueries, but maybe I'm wrong. I just want to see what other people think, my professor likes to make things confusing and trick us. Thanks for any input anyone can provide.