SQL_Joins

DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS pledges;

CREATE TABLE projects (
  id int,
  user_id int,
  category_id int,
  title text,
  funding_goal int,
  start_date date,
  end_date date
);

DELETE FROM projects;

INSERT INTO projects (id, user_id, title, category_id, funding_goal, start_date, end_date)
VALUES (1, 1, 'Pizazz', 1, 10000, 2013-07-10, 2013-12-10),
        (2, 2, 'PhillipCo', 2, 5000, '2013-05-09', '2013-09-10'),
        (3, 1, 'Shape-ser', 3, 2000, '2013-02-10', '2013-10-10'),
        (4, 3, 'HangTime', 3, 100000, '2013-09-04', '2013-09-10'),
        (5, 5, 'HORROR-STOREEZ', 1, 900, '2013-06-20', '2013-09-09'),
        (6, 9, 'Standipede', 1, 500, '2013-04-17', '2013-09-01'),
        (7, 10, 'Cavalry Candets', 2, 16000, '2013-07-10', '2013-09-28'),
        (8, 15, 'Bakersby', 1, 750, '2013-06-23', '2013-09-10'),
        (9, 14, 'Omaha', 3, 1000, '2013-04-11', '2013-09-14'),
        (10, 8, 'Documentary Splash Page', 1, 7000, '2013-06-10', '2013-09-10');

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

DELETE FROM users;

INSERT INTO users (id, name, age)
VALUES (1, 'Bob', 22),
        (2, 'Sam', 25),
        (3, 'Dick', 29),
        (4, 'Harry', 23),
        (5, 'Bob', 22),
        (6, 'Dick', 21),
        (7, 'Harry', 32),
        (8, 'Don', 49),
        (9, 'Karen', 39),
        (10, 'Rebecca', 55),
        (11, 'Carl', 23),
        (12, 'Tina', 26),
        (13, 'Bobby', 29),
        (14, 'Gwen', 31),
        (15, 'Chris', 33),
        (16, 'Cassandra', 65),
        (17, 'Len', 44),
        (18, 'Bernard', 50),
        (19, 'Greta', 38),
        (20, 'Liza', 17);

CREATE TABLE category (
  id int,
  type text
);

INSERT INTO category (id, type)
VALUES (1, 'music'),
        (2, 'books'),
        (3, 'charity');

CREATE TABLE pledges (
  id int,
  user_id int,
  project_id int,
  amount int --(belongs to user, project) 
);

DELETE FROM pledges;

INSERT INTO pledges (id, user_id, project_id, amount)
VALUES (1, 1, 4, 10),
      (2, 7, 2, 100),
      (3, 3, 7, 40),
      (4, 8, 18, 20),
      (5, 1, 19, 5),
      (6, 11, 20, 50),
      (7, 18, 14, 15),
      (8, 6, 2, 25),
      (9, 9, 2, 135),
      (10, 3, 6, 10),
      (11, 6, 3, 10),
      (12, 7, 1, 70),
      (13, 9, 2, 100),
      (14, 8, 13, 1000),
      (15, 8, 12, 100),
      (16, 2, 16, 500),
      (17, 4, 1, 5),
      (18, 6, 2, 8),
      (19, 2, 5, 12),
      (20, 1, 8, 15),
      (21, 9, 9, 40),
      (22, 11, 14, 100),
      (23, 13, 8, 35),
      (24, 2, 9, 10),
      (25, 5, 2, 56),
      (26, 8, 1, 13),
      (27, 11, 1, 101),
      (28, 16, 8, 99),
      (29, 19, 5, 99),
      (30, 1, 1, 1);


-- Questions
-- Answer the following questions with SQL. You'll use different kinds of SQL joins to arrive at the answers for most.

-- Select the titles of all projects and their pledge amounts.

SELECT projects.title, pledges.amount
FROM projects
INNER JOIN pledges
WHERE projects.id = pledges.project_id

-- Select the user name, age, and pledge amount for all pledges.

SELECT users.name, users.age, pledges.amount
FROM users
INNER JOIN pledges
WHERE users.id = pledges.user_id

-- Select the titles of all projects that have met their funding goal.

--this doesn't work:
-- SELECT projects.title, 
-- FROM projects
-- INNER JOIN pledges 
-- WHERE SUM(pledges.amount) >= projects.funding_goal

-- Select user names and amounts of all pledges. Order them by the amount.

--WORK IN PROGRESS....

-- Select the category names, and pledge amounts of all pledges in the music category.

-- Select the category names and the sum total of the pledge amounts of all the pledges in the book category.