kickstarter.sql

HOMEWORK
1. SELECT title, sum(amount) 
    FROM projects 
    JOIN pledges 
    ON projects.id = pledges.project_id 
    GROUP BY title;

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

3. SELECT title, sum(amount), funding_goal 
    FROM projects JOIN pledges 
    ON projects.id = pledges.project_id 
    WHERE funding_goal < amount 
    GROUP BY title;

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

5. SELECT category, amount, title
    FROM projects JOIN pledges
    on projects.id = pledges.project_id
    WHERE category="music";

6. SELECT category, sum(amount), title
    FROM projects JOIN pledges
    ON projectS.id = pledges.project_id
    WHERE category="books";


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

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

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