db_queries.md

assignment per: https://courses.thinkful.com/node-postgres-v1/checkpoint/18#assignment

How many people work in the Sales department?

SELECT COUNT(e.*)
FROM
  department d
  JOIN 
  employee e
  ON e.department = d.id
WHERE
  d.dept_name = 'Sales';

Returns

 count 
-------
     4

to get a list of names

SELECT
  e.emp_name as Employee,
  d.dept_name as Department
FROM 
...

List the names of all employees assigned to the 'Plan Christmas party' project.

SELECT
  e.emp_name as People,
  p.project_name as Project
FROM 
  employee e
  JOIN
  employee_project ep
  ON e.id = ep.emp_id
  JOIN
  project p
  ON ep.project_id = p.id
WHERE 
  p.project_name = 'Plan christmas party';

Returns

     people      |       project        
-----------------+----------------------
 Toby Flenderson | Plan christmas party
(1 row)

List the names of employees from the Warehouse department that are assigned to the 'Watch paint dry' project.

Kind of a trick question, as there is no one in the 'Warehouse' department working on the 'Watch paint dry' project.

SELECT
  e.emp_name, 
  d.dept_name, 
  p.project_name
FROM
  department d
  JOIN 
  employee e
  ON e.department = d.id
  JOIN
  employee_project ep
  ON e.id = ep.emp_id
  JOIN
  project p
  ON ep.project_id = p.id
WHERE 
  p.project_name = 'Watch paint dry'
  AND 
  d.dept_name = 'Warehouse';

Returns...

 emp_name | dept_name | project_name 
----------+-----------+--------------
(0 rows)

To confirm, queried department and projects associated with every employee

SELECT
  e.emp_name, 
  d.dept_name, 
  p.project_name
FROM
  department d
  JOIN 
  employee e
  ON e.department = d.id
  JOIN
  employee_project ep
  ON e.id = ep.emp_id
  JOIN
  project p
  ON ep.project_id = p.id;

Which returns...

    emp_name     |    dept_name    |     project_name     
-----------------+-----------------+----------------------
 Edgar Djikstra  | Development     | Build Database
 Toby Flenderson | Human Resources | Plan christmas party
 Meredith Palmer | Warehouse       | Remove old stock
 Pam Beasley     | Sales           | Watch paint dry
 Jim Halpert     | Sales           | Watch paint dry
 Dwight Schrute  | Sales           | Watch paint dry
 Michael Scott   | Sales           | Watch paint dry
(7 rows)

Alternatively, if add WHERE params/filter to that search...

...

WHERE d.dept_name = 'Warehouse';

Returns...

    emp_name     | dept_name |   project_name   
-----------------+-----------+------------------
 Meredith Palmer | Warehouse | Remove old stock
(1 row)

Which projects are the Sales department employees assigned to?

SELECT
  d.dept_name as Department,
  e.emp_name as Employee,
  p.project_name as Project                    
FROM
  department d
  JOIN
  employee e
  ON e.department = d.id
  JOIN
  employee_project ep
  ON ep.emp_id = e.id
  JOIN
  project p
  ON ep.project_id = p.id
WHERE
  d.dept_name = 'Sales';

Returns...

 department |    employee    |     project     
------------+----------------+-----------------
 Sales      | Michael Scott  | Watch paint dry
 Sales      | Dwight Schrute | Watch paint dry
 Sales      | Jim Halpert    | Watch paint dry
 Sales      | Pam Beasley    | Watch paint dry
(4 rows)

Since the focus is on Sales department and not the employees, per e, can reduce the duplication if add COUNT and ORDER BY into the mix...

SELECT 
  d.dept_name as Department,
  p.project_name as Project,
  COUNT(p.*)
FROM
  department d
  JOIN
  employee e
  ON e.department = d.id
  JOIN
  employee_project ep
  ON ep.emp_id = e.id
  JOIN
  project p
  ON ep.project_id = p.id
WHERE
  d.dept_name = 'Sales'
GROUP BY 
  d.dept_name, p.project_name;

Returns...

 department |     project     | count 
------------+-----------------+-------
 Sales      | Watch paint dry |     4

List only the managers that are assigned to the 'Watch paint dry' project.

SELECT
  e.emp_name as Manager,
  p.project_name as Project
FROM
  department d
  JOIN
  employee e
  ON d.manager = e.id
  JOIN
  employee_project ep
  ON ep.emp_id = e.id
  JOIN project p
  ON ep.project_id = p.id
WHERE 
  p.project_name = 'Watch paint dry';

Returns...

   manager   |     project     
-------------+-----------------
 Jim Halpert | Watch paint dry
(1 row)