Time Entry SQL Practice

Time Entry SQL Practice

Find all time entries.
  SELECT *
  FROM time_entries;

  # Results Returned: 500

Find the developer who joined the company most recently.
  SELECT *
  FROM developers
  ORDER BY joined_on DESC
  LIMIT 1;

  Results Returned:
  "34"	"Dr. Danielle McLaughlin"	"[email protected]"	"2015-07-10"	"2015-07-14 16:15:19.224045"	"2015-07-14 16:15:19.224045"

Find the number of projects for each client.
  SELECT client_id, count(*)
  FROM projects
  GROUP BY client_id;

  # Results Returned: 10

Find all projects, and show each one's company's industry next to it.
  SELECT projects.name, clients.industry
  FROM projects
  JOIN clients
  ON projects.client_id = clients.id;

  # Results Returned: 30

Find all developers in the "Ohio sheep" group.
  SELECT *
  FROM groups
  JOIN group_assignments
  ON group_assignments.group_id = groups.id
  WHERE groups.name = "Ohio sheep";

  # Results Returned: 3

Find the total number of hours worked for each company.
  SELECT *, SUM(time_entries.duration) AS total
  FROM time_entries
  JOIN projects
  ON projects.id = time_entries.project_id
  GROUP BY projects.client_id;

  # Results Returned: 10

Find the client for whom Mrs. Lupe Schowalter (the developer) has worked the greatest number of hours.
  SELECT client_id, developer_id, SUM(duration) AS total_hours
  FROM time_entries
  JOIN projects
  ON projects.id = time_entries.project_id
  WHERE developer_id = 28
  GROUP BY projects.client_id
  ORDER BY total_hours DESC
  LIMIT 1;

  Results Returned:
  client_id developer_id  total_hours
  "9"	        "28"	         "11"

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, projects.name
  FROM clients
  LEFT JOIN projects
  ON clients.id = projects.client_id;

  # Results Returned: 30

Find all developers who have written no comments.
  SELECT *
  FROM developers
  LEFT JOIN comments
  ON developers.id = comments.developer_id
  WHERE comment is null
  GROUP BY developers.id;

  # Results Returned: 13


########## Begin Hard Mode ##########

Find all developers with at least five comments.
  SELECT *, count(comment) AS total_comments
  FROM developers
  JOIN comments
  ON developers.id = comments.developer_id
  GROUP BY developer_id
  HAVING total_comments >= 5;

  Results Returned:
  "45"	"Joelle Hermann"	"[email protected]"	"2014-11-05"	"2015-07-14 16:15:19.485675"	"2015-07-14 16:15:19.485675"	"60"	"30"	"Project"	"45"	"redefine rich architectures"	"2015-07-14 16:15:18.415332"	"2015-07-14 16:15:18.415332"	"5"

Find the developer who worked the fewest hours in January of 2015.
  SELECT *, SUM(duration) AS total_hours
  FROM time_entries
  WHERE worked_on BETWEEN '2015-01-01'
  AND '2015-01-31'
  GROUP BY developer_id
  ORDER BY total_hours ASC
  LIMIT 1;

  Results Returned:
  "65"	"7"	"3"	"2015-01-26"	"0"	"2015-07-14 16:15:18.597869"	"2015-07-14 16:15:18.597869"	"0"

Find all time entries which were created by developers who were not assigned to that time entry's project.
  SELECT *
  FROM time_entries
  JOIN project_assignments
  ON time_entries.developer_id = project_assignments.developer_id
  WHERE project_assignments.project_id != time_entries.project_id;

  # Results Returned: 481