- 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 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;
SUM(quantity) ------------- 12
To make it a little more useful we can
GROUP BY the aisle that the items are
ORDER BY the sum of the
SELECT aisle, SUM(quantity) FROM groceries GROUP BY aisle ORDER BY SUM(quantity);
aisle SUM(quantity) ------- -------------- 1 2 2 4 3 6
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
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
create.sql file, model your tables. You should have a table for
projects, users, and pledges.
Insert Records into Database
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.
learn as you go and read the test messages for additional guidance.
- Seldom Blog - About SQL Joins: The 3 Ring Binder Model
- Coding Horror - A Visual Explanation of SQL Joins
- Geeky is Awesome - SQL Joins Tutorial
View SQL Crowdfunding Lab on Learn.co and start learning to code for free.