Review and Practice

JOIN Practice

Group and Filter Totals

A practical SQL review should connect rows from related tables, group them, and filter aggregate totals.

Program

Play the script to choose the minimum logged hours and review the grouped project totals.

join_aggregate_practice.sql 
CREATE TABLE projects (id INTEGER, name TEXT);
INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');
CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);
INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);
WITH params(min_hours) AS (VALUES ()), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;

CREATE TABLE projects (id INTEGER, name TEXT);
INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');
CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);
INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);
WITH params(min_hours) AS (VALUES ()), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;

CREATE TABLE projects (id INTEGER, name TEXT);
INSERT INTO projects VALUES (1, 'docs'), (2, 'api'), (3, 'ops');
CREATE TABLE task_logs (project_id INTEGER, hours INTEGER);
INSERT INTO task_logs VALUES (1, 2), (1, 1), (2, 3), (2, 4), (3, 1);
WITH params(min_hours) AS (VALUES ()), totals AS (SELECT projects.name, SUM(task_logs.hours) AS total_hours FROM projects JOIN task_logs ON task_logs.project_id = projects.id GROUP BY projects.name) SELECT name, total_hours FROM totals WHERE total_hours >= (SELECT min_hours FROM params) ORDER BY total_hours DESC, name;
join The JOIN connects task log rows to their project names.
group aggregate SUM plus GROUP BY collapses many logs into one total per project.
aggregate filter Filtering after grouping keeps only projects whose total meets the selected threshold.