Kick stater SQL type Exercise Join tables

gistfile1.txt

CREATE TABLE project

(
project_id INT,
title TEXT,
category TEXT,
funding_goal INT, 
start_date TEXT,
end_date TEXT
);

CREATE TABLE user

(
user_id INT,
name TEXT,
age INT
);

CREATE TABLE pledges

(
amount INT,
user_id INT,
project_id INT
);

INSERT INTO project (title,project_id,category,funding_goal,start_date,end_date)
VALUES 
(1,"Oculus Rift","music",1000000,"2012jan","2012sept"),
(2,"jelly fish farm","book",100000,"2013jan","2013may"),
(3,"custom boxes","charity",20300,"2013feb","2013april"),
(4,"remote control ant","music",349000,"2012aprl","2013may"),
(5,"anti gravity boots","book",9000000,"2012jan","2014sept"),
(6,"kitch knives","charity",1000,"2013jan","2013oct"),
(7,"robot painter","music",100,"2011jan","2011may"),
(8,"wallet made from coke cans","book",1099,"2013april","2013oct"),
(9,"RUBY BOOKS","book",45630,"2013feb","2013may"),
(10,"jelly fish farm","book",100000,"2013jan","2013may")
;


INSERT INTO user (user_id,name,age)
VALUES
(1,"john",27),
(2,"jack",19),
(3,"jill",29),
(4,"joe",14),
(5,"joel",26),
(6,"jim",97),
(7,"jasper",37),
(8,"johnny",47),
(9,"joseph",22),
(10,"jaehyra",21),
(11,"jackson",28),
(12,"jaqueline",57),
(13,"jojo",37),
(14,"job",28),
(15,"jinny",19),
(16,"jin",74),
(17,"jax",63),
(18,"jay",54),
(19,"june",45),
(20,"joplin",167)
;

INSERT INTO pledges (amount,user_id,project_id)
VALUES
(100000, 1, 1),
(100, 3, 2),
(1000, 10, 3),
(100, 20, 4),
(23400, 13, 5),
(100, 4, 6),
(10, 5, 7),
(1000, 6, 8),
(100, 7, 5),
(2300, 8, 4),
(4300, 9, 9),
(10, 6, 10),
(1000, 3, 4),
(3300, 17, 2),
(200, 17, 1),
(100, 18, 4),
(23000, 19, 5),
(3030, 11, 6),
(100, 12, 7),
(10000, 14, 9),
(3000, 7, 3),
(30000, 2, 5),
(100, 9, 8),
(3000, 13, 9),
(100, 14, 7),
(10000, 15, 3),
(4000, 17, 5),
(100000, 3, 3),
(1, 8, 2),
(30, 1, 5)
;


 
 
-- #Select the titles of all projects and their pledge amounts.
SELECT project.title, pledges.amount FROM project
JOIN pledges
ON project.project_id = pledges.project_id;

-- #Select the user name, age, and pledge amount for all pledges.
 
SELECT user.name, user.age, pledges.amount
  FROM pledges
  JOIN user
  ON pledges.user_id = user.user_id;
 
 
 
-- #Select the titles of all projects that have met their funding goal.
 
SELECT pledges.user_id, project.project_id AS help
  FROM pledges
  JOIN project
  ON pledges.project_id = project.project_id
    WHERE project.funding_goal < (SELECT SUM(amount)
      FROM pledges WHERE project_id = help);
 
 
 
-- #Select user names and amounts of all pledges. Order them by the amount.
 
 
SELECT user.name, pledges.amount
  FROM pledges
  JOIN user
  ON pledges.user_id = user.user_id
  ORDER BY pledges.amount DESC;
 
 
 
-- #Select the category names, and pledge amounts of all pledges in the music category.
 
 
 
SELECT project.category, pledges.amount
  FROM pledges
  JOIN project
  ON pledges.project_id = project.project_id
  WHERE project.category = 'music';
 
 
-- #Select the category names and the sum total of the pledge amounts of all the pledges in the book category.
 
 
 
SELECT category, SUM(amount)
  FROM pledges
  JOIN project
  ON pledges.project_id = project.project_id
  WHERE project.category = "books";