SQL: JOINs and SUMs exercise, crowd funding database

crowd_funding_create.sql

CREATE TABLE projects(
  id INTEGER,
  title TEXT,
  category_id INTEGER,
  funding_goal INTEGER,
  start_date INTEGER,
  end_date INTEGER
);

CREATE TABLE categories(
  id INTEGER,
  category TEXT
);

CREATE TABLE users(
  id INTEGER,
  name TEXT,
  age INTEGER
);

CREATE TABLE pledges(
  id INTEGER,
  amount INTEGER,
  user_id INTEGER,
  project_id INTEGER
);

crowd_funding_insert.sql

INSERT INTO
  categories (id, category)
VALUES
  (1, "Music"),
  (2, "Books"),
  (3, "Charity");

  INSERT INTO
    projects (id, title, category_id, funding_goal, start_date, end_date)
  VALUES
    (1, "The Best Album Ever, When It Is Done", 1, 1000000, 20130928, 20131028),
    (2, "Seriously The Best Book You Will Ever Read", 2, 1000000, 20130930, 20131023),
    (3, "Feel Like The Best Person Alive Charity", 3, 1000000, 20130929, 20131030),
    (4, "I'm Rich But NEED Money For My Book", 2, 500000, 20131001, 20131101),
    (5, "Great Charity", 3, 100000, 20131101, 20140630),
    (6, "New Album From Your Favorite Band", 1, 50000, 20130930, 20140930),
    (7, "Tom Clancy's Unfinished Work", 2, 100000, 20131015, 20131225),
    (8, "The New Pickaxe Book", 2, 10000, 20110903, 20120101),
    (9, "Teach Kids To Code", 3, 100000, 20131201, 20141201),
    (10, "Matz's Heavy Metal Album", 1, 1000000, 20130930, 20131002);

  INSERT INTO
    users (id, name, age)
  VALUES
    (1, "David", 25),
    (2, "Avi", 29),
    (3, "Greg", 26),
    (4, "Emily", 25),
    (5, "Sonja", 29),
    (6, "Ian", 27),
    (7, "Kyle", 28),
    (8, "Rosie", 30),
    (9, "Raymond", 30),
    (10, "Josh", 27),
    (11, "Margeret", 40),
    (12, "Stephanie", 28),
    (13, "Logan", 23),
    (14, "Manuel", 28),
    (15, "Edina", 28),
    (16, "Scott", 30),
    (17, "John", 28),
    (18, "Spencer", 29),
    (19, "Dan", 24),
    (20, "Jeanne", 25);

INSERT INTO
  pledges (id, amount, user_id, project_id)
VALUES
  (1, 5088, 1, 10),
  (2, 15440, 2, 1),
  (3, 1045, 1, 9),
  (4, 250, 2, 9),
  (5, 500, 3, 5),
  (6, 1504, 4, 3),
  (7, 1000, 8, 6),
  (8, 2500, 18, 8),
  (9, 50000, 10, 5),
  (10, 15034, 2, 3),
  (11, 4400, 1, 4),
  (12, 2500, 2, 9),
  (13, 54023, 3, 5),
  (14, 45000, 4, 3),
  (15, 1600, 8, 6),
  (16, 90044, 18, 8),
  (17, 1500, 20, 3),
  (18, 100400, 1, 4),
  (19, 25220, 2, 9),
  (20, 152000, 2, 3),
  (21, 10123, 14, 4),
  (22, 25000, 2, 9),
  (23, 5420, 3, 5),
  (24, 487000, 4, 3),
  (25, 1600, 8, 8),
  (26, 9001, 18, 8),
  (27, 1500, 20, 8),
  (28, 10000, 1, 8),
  (29, 20000, 2, 9),
  (30, 2500, 2, 8);

crowd_funding_joins.sql

SELECT
  projects.title, pledges.amount
FROM projects
  JOIN pledges ON
    projects.id = pledges.project_id;

SELECT
  users.name, users.age, pledges.amount
FROM pledges
  JOIN users ON
    pledges.user_id = users.id;

-- Also shows sum of pledges and funding_goal for information
SELECT
  projects.title, sum(pledges.amount), projects.funding_goal
FROM projects
  JOIN pledges ON
    projects.id = pledges.project_id
GROUP BY projects.title
  HAVING sum(pledges.amount) > projects.funding_goal;

-- Added in the project name the pledge contributed to, for fun
SELECT
  users.name, pledges.amount, projects.title
FROM pledges
  JOIN users ON
    pledges.user_id = users.id
  JOIN projects ON
    pledges.project_id = projects.id
ORDER BY pledges.amount DESC;

-- Added in the project name here too, for fun
SELECT
  categories.category, projects.title, pledges.amount
FROM projects
  JOIN categories ON
    categories.id = projects.category_id
  JOIN pledges ON
    pledges.project_id = projects.id
WHERE categories.category = "Music";

SELECT
  categories.category, sum(pledges.amount)
FROM projects
  JOIN categories ON
    categories.id = projects.category_id
  JOIN pledges ON
    pledges.project_id = projects.id
WHERE categories.category = "Books";