SQL_Joins

CREATE TABLE projects (
  id int,
  title text,
  category text,
  funding_goal int,
  start_date text,
  end_date text
);

CREATE TABLE users (
  id int,
  name text,
  age int
);

CREATE TABLE pledges (
  id int,
  amount int,
  users_id int,
  projects_id int
);



INSERT INTO projects (id, title, category, funding_goal, start_date, end_date) VALUES
  (1, 'Project 1', 'Music', 100, 'January 1', 'January 30'),
  (2, 'Project 2', 'Books', 1001, 'January 1', 'February 20'),
  (3, 'Project 3', 'Charity', 10, 'January 1', 'March 30'),
  (4, 'Project 4', 'Music', 10011, 'January 1', 'April 30'),
  (5, 'Project 5', 'Music', 100111, 'January 1', 'May 30'),
  (6, 'Project 6', 'Books', 1, 'January 1', 'June 30'),
  (7, 'Project 7', 'Music', 1004, 'January 1', 'July 30'),
  (8, 'Project 8', 'Music', 109, 'January 1', 'August 30'),
  (9, 'Project 9', 'Charity', 10066, 'January 1', 'September 30'),
  (10, 'Project 10', 'Music', 10055, 'January 1', 'October 30');



INSERT INTO users (id, name, age) VALUES
  (1, 'Bob', 21),
  (2, 'Jim', 22),
  (3, 'Jeff', 23),
  (4, 'Rob', 24),
  (5, 'Mike', 25),
  (6, 'Steven', 26),
  (7, 'Gregory', 27),
  (8, 'David', 28),
  (9, 'Don', 29),
  (10, 'Lee', 30),
  (11, 'Derek', 31),
  (12, 'Carl', 32),
  (13, 'Kenny', 33),
  (14, 'Seamus', 34),
  (15, 'Joe', 35),
  (16, 'Chris', 36),
  (17, 'George', 37),
  (18, 'Micah', 38),
  (19, 'Carmen', 39),
  (20, 'Dino', 40);



INSERT INTO pledges (id, amount, users_id, projects_id) VALUES
  (1, 1, 1, 1),
  (2, 11, 2, 2),
  (3, 111, 3, 3),
  (4, 2, 4, 4),
  (5, 22, 5, 5),
  (6, 222, 6, 6),
  (7, 3, 7, 7),
  (8, 33, 8, 8),
  (9, 333, 9, 9),
  (10, 4, 10, 10),
  (11, 44, 11, 1),
  (12, 444, 12, 2),
  (13, 5, 13, 3),
  (14, 55, 14, 4),
  (15, 555, 15, 5),
  (16, 6, 16, 6),
  (17, 66, 17, 7),
  (18, 666, 18, 8),
  (19, 7, 19, 9),
  (20, 77, 20, 10),
  (21, 777, 1, 1),
  (22, 8, 2, 2),
  (23, 88, 3, 3),
  (24, 888, 4, 4),
  (25, 9, 5, 5),
  (26, 99, 6, 6),
  (27, 999, 7, 7),
  (28, 10, 8, 8),
  (29, 100, 9, 9),
  (30, 1000, 10, 10);




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


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


  SELECT pledges.id, projects.id AS help
  FROM pledges
  JOIN projects
  ON pledges.projects_id = projects.id
  #unable to finish


  SELECT users.name, pledges.amount
  FROM pledges
  JOIN users
  ON pledges.users_id = users.id
  ORDER BY pledges.amount DESC;


  SELECT projects.category, pledges.amount
  FROM pledges
  JOIN projects
  ON pledges.projects_id = projects.id
  WHERE projects.category = 'music';


  SELECT category, SUM(amount)
  FROM pledges
  JOIN projects
  ON pledges.projects_id = projects.id
  WHERE projects.category = "books";