Sql Crowdfunding Lab

Learning Goals

  • Identify table example
  • Create a new table, records, and queries

Identify Table Example

SQL is a programming language that allows you to "nest" statements inside each other. Let's quickly consider an example using grocery data.

Create a Grocery Table

CREATE TABLE groceries (id INTEGER PRIMARY KEY, name TEXT, quantity INTEGER, aisle INTEGER);

INSERT INTO groceries VALUES (1, "Apples", 2, 1);
INSERT INTO groceries VALUES (2, "Oranges", 4, 2);
INSERT INTO groceries VALUES(3, "Peaches", 6, 3);

Selecting Data from the Grocery Table

Given the example SQL above, we'd be able to run a query like

SELECT * FROM groceries;

And get back the inserted data (represented in a table below)

id                 name        quantity      aisle
------------    ----------  ----------  ----------  
1                Apples          2          1
2                Oranges         4          2
3                Peaches         6          3

SUM

SUM will return the total of the column you select. In this case, the total number of items in the groceries column is 12.

SELECT SUM(quantity) FROM groceries;

Returns

SUM(quantity)
-------------
12

To make it a little more useful we can GROUP BY the aisle that the items are in and ORDER BY the sum of the aisle.

SELECT aisle, SUM(quantity) FROM groceries GROUP BY aisle ORDER BY SUM(quantity);

Returns

aisle    SUM(quantity)
-------  --------------
1           2
2           4
3           6

Instructions

In this lab, you have two tasks - first, you will need to create three tables in lib/create.sql, and populate them with data in lib/insert.sql. Then, you will need to write the necessary SQL statements inside the methods in lib/sql_queries.rb that would allow you to query data from your newly created tables.

Create New Table, Records and Queries

Now we're going to 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.
  • 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.

Create the Tables

In the create.sql file, model your tables. You should have a table for projects, users, and pledges.

Insert Records into Database

Within the insert.sql file, insert 10 projects, 20 users, and 30 pledges into the database.

Write the Queries

Write SQL queries as strings in lib/sql_queries in each of the provided methods.

Run learn as you go and read the test messages for additional guidance.

Resources

View SQL Crowdfunding Lab on Learn.co and start learning to code for free.

Unlock your future in tech
Learn to code.

Learn about Flatiron School's Mission

With a new take on education that falls somewhere between self-taught prodigy and four-year computer science degree, the Flatiron School promises to turn students with little programming experience into developers.

In the six months since the Manhattan coding school was acquired by WeWork, it has spawned locations in Washington, D.C., Brooklyn, and London. Now, WeWork is opening a fourth Flatiron School location, this time in Houston.

Adam Enbar, Flatiron School's cofounder, believes now is the time to grow. "How the world is changing has impacted working and learning in very similar ways. We think education fundamentally is about one thing: enabling people to pursue a better life."

Learn. Love. Code.
Students come to Flatiron School to change their lives. Join our driven community of career-changers and master the skills you need to become a software engineer or a data scientist.
Find Us