create a sql and db for a crowdfunding site

fis002-crowdfund-alex-au

--schema

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

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

CREATE TABLE pledges (
  id int,
  user_id int,
  project_id int,
  amount int
);

--inserts

insert into users ( name, age, id) 
            values ("Boris", 17, 1),
                   ("Janice", 24, 2),
                   ("Zeus", 53, 3),
                   ("Bartleby", 33, 4),
                   ("Sally", 15, 5),
                   ("Gary", 88, 6),
                   ("Amanda", 34, 7),
                   ("Phil", 31, 8),
                   ("Pox", 14, 9),
                   ("Jack", 15, 10),
                   ("Simon", 53, 11),
                   ("Miranda", 25, 12),
                   ("Phillis", 26, 13),
                   ("George", 83, 14),
                   ("Allan", 43, 15),
                   ("Greg", 51, 16),
                   ("Rosa", 21, 17),
                   ("Alex", 25, 18),
                   ("Thomas", 26, 19),
                   ("Jojo", 29, 20);

insert into projects (id, title, category, funding_goal) 
            VALUES (1, 'The best', 'music', 1000),
                   (2, 'The worst', 'charity', 1000),
                   (3, 'The meanest', 'charity', 1000),
                   (4, 'The fastest', 'charity', 1000),
                   (5, 'The soundest', 'charity', 1000),
                   (6, 'The wordiest', 'charity', 1000),
                   (7, 'The sonic boom', 'charity', 1000),
                   (8, 'The longest', 'charity', 1000),
                   (9, 'The shortest', 'charity', 1000),
                   (10, 'The thinnest', 'books', 1000);

insert into pledges (id, user_id, project_id, amount)
            VALUES (1, 1, 1, 55),                    
                   (2, 2, 2, 555), 
                   (3, 3, 3, 555), 
                   (4, 4, 4, 55), 
                   (5, 5, 5, 555), 
                   (6, 6, 6, 555), 
                   (7, 7, 7, 55), 
                   (8, 8, 8, 555), 
                   (9, 9, 9, 55), 
                   (10, 10, 2, 555), 
                   (11, 11, 6, 55), 
                   (12, 12, 2, 555), 
                   (13, 13, 7, 555), 
                   (14, 14, 4, 55), 
                   (15, 15, 6, 555), 
                   (16, 16, 4, 55), 
                   (17, 17, 8, 555), 
                   (18, 18, 2, 555), 
                   (19, 19, 8, 55), 
                   (20, 20, 1, 555), 
                   (21, 11, 9, 555), 
                   (22, 12, 6, 55), 
                   (23, 13, 4, 555), 
                   (24, 14, 3, 555), 
                   (25, 15, 2, 55), 
                   (26, 16, 5, 555), 
                   (27, 17, 7, 555), 
                   (28, 18, 3, 55), 
                   (29, 19, 8, 55), 
                   (30, 20, 10, 45);

--queries

-- Select the titles of all projects and their pledge amounts.
SELECT title, pledges.amount 
FROM projects
JOIN pledges
ON 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
JOIN pledges
ON users.id = pledges.user_id;

-- Select the titles of all projects that have met their funding goal.
SELECT DISTINCT projects.title
FROM pledges
JOIN projects
ON pledges.project_id = projects.id
WHERE projects.funding_goal <= (
  SELECT SUM(pledges.amount)
  FROM pledges
  WHERE pledges.project_id = projects.id);

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

-- Select the category names, and pledge amounts of all pledges in the music category.
SELECT projects.category, pledges.amount
FROM pledges
JOIN projects
ON pledges.project_id = projects.id
WHERE projects.category = "music";

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