Week 6 homework, Mason's class

homework.md

Week 6 Homework

Find all time entries:

  SELECT * from time_entries

• 500 results

Find the developer who joined most recently:

SELECT * from developers ORDER BY joined_on DESC LIMIT 1

• 1 result
• "34" "Dr. Danielle McLaughlin" "shakira.carter@kohler.org" "2015-07-10" "2015-07-14 16:15:19.224045" "2015-07-14 16:15:19.224045" "2015-07-10"

Find the number of projects for each client.

 SELECT client_id, name as client_name, COUNT(*) as project_count
 FROM projects
 GROUP BY client_id

• 9 results. All had a count of 3 except client_id 6, which had a count of 6.

Find all time entries, and show each one's client name next to it:

• SELECT t.*, c.name as client_name
    FROM time_entries t
    JOIN projects p
      ON p.id=t.project_id
    JOIN clients c
      ON c.id=p.client_id
    ORDER BY client_name

• 500 results

Find all developers in the "Ohio sheep" group:

SELECT *
 FROM developers d
 JOIN group_assignments ga
   ON ga.developer_id=d.id
 JOIN groups g
   ON ga.group_id=g.id
 WHERE g.name="Ohio sheep"

• 3 results

Find the total number of hours worked for each client:

• SELECT clients.id, clients.name, count(*) as total_hours
  FROM clients
  JOIN projects
    ON projects.client_id=clients.id
  JOIN time_entries te
    ON te.project_id=projects.id
  GROUP BY te.duration

• 9 results

Find the client for whom Mrs. Lupe Schowalter (the developer) has worked the greatest number of hours:

 SELECT clients.*, projects.id as project_id, count(*) as total_hours
   FROM clients
   JOIN projects
    ON projects.client_id=clients.id
   JOIN developers
   JOIN project_assignments
    ON project_assignments.developer_id=developers.id
   JOIN time_entries
    ON time_entries.project_id=projects.id
   WHERE developers.name='Mrs. Lupe Schowalter'
   GROUP BY time_entries.duration
   ORDER BY total_hours DESC
   LIMIT 1

• 1 result
• "6" "Ortiz, Gislason and Rutherford" "Computers & Health" "2015-07-14 16:15:18.320556" "2015-07-14 16:15:18.320556" "15" "75"

List all client names with their project names (multiple rows for one client is fine). Make sure that clients still show up even if they have no projects:

  SELECT clients.name as client_name, projects.name as project_name
   FROM clients
   LEFT JOIN projects
     ON projects.client_id=clients.id
   ORDER BY clients.name

• 33 results

Find all developers who have written no comments:

SELECT developers.*
  FROM developers
  LEFT JOIN comments
    ON comments.developer_id=developers.id
  WHERE comments.comment IS NULL
  ORDER BY developers.name

• 13 results