SQL question: How can I extract this information from these tables?

Question

I have these 3 tables:

EMPLOYEES 

Emp_id PK || First_Name || Last_Name || Hiring_Date


Department

Dep_Name || emp_id


SALARIES

salary || emp_id

Two months ago, the company hired new employees.

I need to write a SQL statement, that counts how many employees were hired. In the SAME statement, I need to find out, what are the financial increases by each department, after the new hirings.

For the first thing, I think this is the query:

SELECT COUNT(emp_id) FROM employees
WHERE YEAR(NOW()) - YEAR(hiring_date) = 0 AND (MONTH(NOW()) - MONTH(hiring_date) = 2 OR MONTH(NOW()) - MONTH(hiring_date) =  - 2);

but, I don't know how can I extract the information for the 2nd thing. (I know I need to make a join, but I don't know how to extract the increases by each department)

Once again, the 1st and 2nd must be IN THE SAME SQL STATEMENT.

Answer 1

SELECT COUNT(emp_id), SUM(salary) 
FROM employees e JOIN salaries s ON (s.emd_id = e.emp_id)
WHERE YEAR(NOW()) - YEAR(hiring_date) = 0 
      AND (MONTH(NOW()) - MONTH(hiring_date) = 2 
           OR MONTH(NOW()) - MONTH(hiring_date) =  - 2)

Answer 2

This variant needs all three tables. It uses Standard SQL interval notations; not many DBMS actually support it, but this works when the current date is in January and the question's version does not:

SELECT Dep_Name, COUNT(*), SUM(SALARY)
  FROM Employees AS E NATURAL JOIN Salaries AS S ON E.Emp_ID = S.Emp_ID
       NATURAL JOIN Department AS D ON E.Emp_ID = D.Emp_ID
 WHERE CURRENT_DATE - Hiring_Date <= INTERVAL(2) MONTH
 GROUP BY Dep_Name;

I note that the Department table is a little unusual - more normally, it would be called something like Department_Emps; as it stands, its primary key is the Emp_ID column, not the Dep_Name column.

[For the record, the query below is what I used with IBM Informix Dynamic Server.]

SELECT Dep_Name, COUNT(*), SUM(SALARY)
  FROM employees AS E JOIN salaries AS S ON E.Emp_ID = S.Emp_ID
       JOIN department AS D ON E.Emp_ID = D.Emp_ID
 WHERE CURRENT YEAR TO DAY <= INTERVAL(2) MONTH TO MONTH + Hiring_Date 
 GROUP BY Dep_Name;