-- Find the employees whose last names are Doe

SELECT * FROM Employees WHERE LastName = N'Doe';

SELECT FirstName, LastName FROM Employees where LastName = N'Doe';


-- Find the names of the employees who were hired in 2015

SELECT CONCAT(FirstName, ' ', LastName) AS Name FROM Employees
    WHERE YEAR(DateHired) = 2015;

-- Find the name of the leader of the project Blue

SELECT CONCAT(e.FirstName, ' ', e.LastName) AS [Leader Name]
    FROM Employees e, Projects p
    WHERE e.EmployeeId = p.LeaderId and p.Name = N'Blue';


SELECT CONCAT(e.FirstName, ' ', e.LastName) AS [Leader Name]
    FROM Employees e INNER JOIN Projects p on e.EmployeeId = p.LeaderId
    WHERE p.Name = N'Blue';

-- List the name and the number of employees for each project

SELECT p.Name, count(m.EmployeeId) AS [Number of Employees]
    FROM Projects p, ProjectMembers m
    WHERE p.ProjectId = m.ProjectId
    GROUP BY p.Name;

SELECT p.Name, count(m.EmployeeId) AS [Number of Employees]
    FROM Projects p INNER JOIN ProjectMembers m on p.ProjectId = m.ProjectId
    GROUP BY p.Name;