SQL Build Kickstarter, Day 9

insert.sql

INSERT INTO users(id, name, age)
VALUES
  (1, "Casey", 24),
  (2, "Angie", 20),
  (3, "Samantha", 17),
  (4, "Greg", 40),
  (5, "Andrew", 43),
  (6, "John", 64),
  (7, "Camille", 20),
  (8, "Cynthia", 50),
  (9, "Andrea", 20),
  (10, "Dre", 42),
  (11, "Candy", 15),
  (12, "Matt", 40),
  (13, "Alan", 42),
  (14, "Cassie", 24),
  (15, "Rosie", 29),
  (16, "Anthony", 31),
  (17, "Pam", 32),
  (18, "Rhonda", 76),
  (19, "Chad", 41),
  (20, "Cassie", 24);


INSERT INTO projects(id, users_id, title, funding_cat, funding_goal, start_date, end_date)
VALUES
(1, 4, "Community Art Project", "charity", 20000, "October 3, 2013", "January 2, 2014"),
(2, 20, "Kiddie Rock Classics", "music", 5000, "October 31, 2013", "January 30, 2014"),
(3, 6, "Magic Music Festival", "music", 28000, "October 2, 2013", "January 1, 2014"),
(4, 18, "Solar on our Church", "charity", 250000, "October 3, 2013", "January 2, 2014"),
(5, 3, "Sponsor my Violin", "music", 800, "October 25, 2013", "January 24, 2014"),
(6, 9, "Awesome Novel", "books", 12000, "October 30, 2013", "January 29, 2014"),
(7, 18, "Cancer Wish", "charity", 10000, "October 3, 2013", "January 2, 2014"),
(8, 24, "Story of Me", "books", 2000, "October 15, 2013", "January 14, 2014"),
(9, 4, "Blue Grass Blue", "music", 1000, "October 11, 2013", "January 10, 2014"),
(10, 7, "Techno Wow", "music", 25000, "October 18, 2013", "January 17, 2014");


INSERT INTO pledges(id, users_id, projects_id, amount)
VALUES
(1, 4, 2, 20),
(2, 6, 4, 500),
(3, 9, 6, 200),
(4, 14, 6, 25),
(5, 11, 3, 20),
(6, 12, 10, 20),
(7, 15, 1, 50),
(8, 20, 9, 20),
(9, 18, 10, 10),
(10, 12, 5, 20),
(11, 6, 2, 20),
(12, 6, 4, 500),
(13, 8, 6, 200),
(14, 12, 6, 25),
(15, 15, 3, 20),
(16, 19, 10, 20),
(17, 28, 1, 50),
(18, 20, 9, 20),
(19, 2, 10, 10),
(20, 2, 5, 20),
(21, 10, 2, 20),
(22, 17, 4, 50),
(23, 8, 6, 200),
(24, 9, 6, 25),
(25, 11, 3, 20),
(26, 6, 10, 20),
(27, 7, 1, 50),
(28, 11, 9, 20),
(29, 10, 10, 10),
(30, 9, 5, 20);

questions.sql

## Select the titles of all projects and their pledge amounts.
SELECT projects.title, pledges.amount
FROM pledges
LEFT JOIN projects 
ON pledges.projects_id = projects.id
GROUP BY title;

## Select the user name, age, and pledge amount for all pledges.
SELECT users.name, users.age, pledges.amount
FROM pledges
JOIN users 
ON pledges.users_id = users.id;


## Select the titles of all projects that have met their funding goal.
SELECT projects.title, projects.funding_goal, sum(pledges.amount)
FROM projects
JOIN pledges ON projects.id = pledges.id;


## Select user names and amounts of all pledges. Order them by the amount.
SELECT users.name, pledges.amount
FROM pledges
JOIN users 
ON pledges.users_id = users.id
ORDER BY pledges.amount;


## Select the category names, and pledge amounts of all pledges in the music category.
  ## Also returning books? Not sure why.
SELECT projects.funding_cat, pledges.amount
FROM pledges
LEFT projects 
ON pledges.project_id = projects.id
WHERE projects.funding_cat = "music";


## Select the category names and the sum total of the pledge amounts of all the pledges in the book category.
SELECT projects.funding_cat, sum(pledges.amount)
FROM pledges
JOIN projects 
ON pledges.project_id = projects.id
WHERE projects.funding_cat = "books";


##FORMAT
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

schema.sql

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

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

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