kickstarter database to learn sql :)

kickstarter.sql

-- Create a schema based on the following information:

-- A project has a title, a category, a funding goal, a start date, and an end date. Valid categories are: music, books, charity.

-- A user has a name and an age

-- A pledge has an amount. It belongs to a user, and it also belongs to a project.

-- Insert some records into the datbase

-- Create 10 projects, 20 users, and 30 pledges.

CREATE TABLE projects(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT,
  category TEXT,
  funding_goal INTEGER,
  start_date TEXT,
  end_date TEXT  
);

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

CREATE TABLE pledges(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  amount INTEGER,
  user_id INTEGER REFERENCES users, 
  project_id INTEGER REFERENCES projects
);

INSERT INTO projects (title, category, funding_goal, start_date, end_date) VALUES
  ("Plus Pool", "charity", 125000, "2013-10-01", "2013-10-15"),
  ("trsst", "books", 100500, "2013-10-08", "2013-10-25"),
  ("Books for Tots", "books", 50000, "2013-10-22", "2013-11-08"),
  ("SHADOW", "music", 75000, "2013-11-01", "2013-11-15"),
  ("C0d3 Skewl", "charity", 200000, "2013-09-01", "2013-12-15"),
  ("Free the People", "music", 50000, "2013-12-11", "2013-12-30"),
  ("Naadam", "charity", 20000, "2013-10-04", "2013-10-20"),
  ("Solar for All", "charity", 100500, "2013-10-01", "2013-10-15"),
  ("bk knows rap", "music", 45000, "2013-09-01", "2013-10-01"),
  ("the band", "music", 60000, "2013-10-05", "2013-10-25");

INSERT INTO users (name, age) VALUES
  ("Sarah", 24), 
  ("Jim", 14), 
  ("Stacey", 52), 
  ("Meredith", 61), 
  ("Jeremy", 27), 
  ("Matt", 34), 
  ("Mark", 45), 
  ("Nina", 28), 
  ("Julia", 25), 
  ("Austin", 42), 
  ("Chris", 17), 
  ("Amber", 50), 
  ("Jimmy", 13),
  ("Angie", 34), 
  ("Tiffany", 21), 
  ("Meryl", 52), 
  ("Brian", 23), 
  ("Nick", 26), 
  ("Candice", 32), 
  ("Buffy", 41);

INSERT INTO pledges (user_id, project_id, amount) VALUES
  (1, 1, 25), 
  (2, 2, 700), 
  (3, 3, 1005), 
  (4, 4, 231), 
  (5, 5, 755), 
  (6, 6, 35), 
  (7, 7, 198), 
  (8, 8, 200), 
  (9, 9, 301), 
  (10, 10, 400), 
  (11, 1, 430), 
  (12, 2, 45), 
  (13, 3, 980), 
  (14, 4, 36), 
  (15, 5, 380), 
  (16, 6, 470), 
  (17, 7, 800), 
  (18, 8, 450), 
  (19, 9, 230), 
  (20, 10, 320), 
  (1, 1, 650), 
  (2, 2, 570), 
  (3, 3, 480), 
  (4, 4, 930), 
  (5, 5, 370), 
  (6, 6, 310), 
  (7, 7, 150), 
  (8, 8, 670), 
  (9, 9, 1000), 
  (10, 10, 31);

kickstarter_queries.sql

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

SELECT projects.title, SUM(pledges.amount)
  FROM pledges INNER JOIN projects ON pledges.project_id = projects.id
  GROUP BY projects.title
  ORDER BY SUM(pledges.amount) DESC;

SELECT
(
  SELECT SUM(amount)
  FROM pledges INNER JOIN projects ON pledges.project_id = projects.id
);

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

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

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

SELECT p.title, sums.less
  FROM (
    SELECT projects.title as titular, projects.funding_goal-SUM(pledges.amount) as less
      FROM pledges INNER JOIN projects ON pledges.project_id = projects.id
      GROUP BY projects.title
      ORDER BY projects.funding_goal-SUM(pledges.amount) DESC) as sums, projects p
where sums.less > 0 and sums.titular = p.title;

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

SELECT users.name, sum(pledges.amount)
  FROM users INNER JOIN pledges ON pledges.user_id = users.id
  group by users.name
  ORDER BY sum(pledges.amount) DESC;

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

SELECT projects.category, projects.title, projects.id FROM projects WHERE category = "music";

SELECT projects.title, projects.category, pledges.amount
  FROM projects INNER JOIN pledges ON pledges.project_id = projects.id
  WHERE projects.category = "music"
  ORDER BY pledges.amount desc;

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

SELECT projects.title, projects.category, SUM(pledges.amount)
  FROM pledges INNER JOIN projects ON pledges.project_id = projects.id
  WHERE projects.category = "books"
  GROUP BY projects.title
  ORDER BY SUM(pledges.amount) DESC;