queries.md

List total number of projects, then same for tasks (COUNT)

select count(*) from projects;

List incomplete tasks (single-table; WHERE) (how many are there?)

select * from tasks where completed = false;

List overdue tasks (single-table; WHERE with AND)

select * from tasks where (completed = false) AND (due_date < '2019-07-22');

List upcoming tasks which are incomplete (WHERE, AND, ORDER BY)

select * from tasks where (completed = false) AND (due_date > '2019-07-22') ORDER BY due_date;

List all tasks, along with their project information, etc (INNER JOIN)

SELECT *
FROM tasks
INNER JOIN projects
ON tasks.project_id = projects.id;

List all projects along with how many tasks each project has (GROUP BY with INNER JOIN and COUNT)

select COUNT(*), projects.name
from projects
JOIN tasks
ON projects.id = tasks.project_id
GROUP BY projects.id;

schema.sql

drop table tasks;
drop table projects;

CREATE TABLE projects (id SERIAL UNIQUE PRIMARY KEY,name varchar(90), owner varchar(90), due_date DATE);

CREATE TABLE tasks (id SERIAL UNIQUE PRIMARY KEY, name varchar(90), completed BOOLEAN, completed_by varchar(90), due_date DATE, project_id SERIAL REFERENCES projects);

seeds.sql

INSERT INTO projects (id, name, owner, due_date) VALUES (1, 'nimas house', 'nima', '2019-12-31');
INSERT INTO projects (id, name, owner, due_date) VALUES (2, 'gardening', 'aidan', '2020-02-15');
INSERT INTO projects (id, name, owner, due_date) VALUES (3, 'school', 'julia', '2019-09-15');
INSERT INTO projects (id, name, owner, due_date) VALUES (4, 'shopping', 'julia', NULL);

INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (1, 'build the walls', false, 'nima', '2018-03-20', 1);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (2, 'plumbing', true, 'haibin', '2017-09-15', 1);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (3, 'set up a door', true, 'jiadan', '2012-09-15', 1);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (4, 'fertilize', false, 'nima', '2013-08-05', 2);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (5, 'buy some pots', true, 'nancy', '2019-10-15', 2);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (6, 'water the plants', true, 'rene', '2019-09-15', 2);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (7, 'buy tshirt', true, 'rene', '2019-09-15', 4);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (8, 'buy pants', false, 'james', '2020-09-15', 4);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (9, 'buy shoes', true, 'ralph', null, 4);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (12, 'buy shorts', false, 'grayson', '2021-02-01', 4);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (10, 'buy cosmetics', false, 'aidan', '2020-10-15', 4);
INSERT INTO tasks (id, name, completed, completed_by, due_date, project_id) VALUES (11, 'buy long sleeved shirts', false, 'james', '2020-12-15', 4);