This is the curriculum for the 2018 Foxtrot Web Developer Bootcamp.

SQL Introduction from LEARN on Vimeo

Introduction to SQL

A model is a representation

Model of a person

Given Name: Korben
Family Name: Dallas
Date of Birth: 6/1/12097
Address: Apt 3497 1 Main St CityVille, Earth
Email: korben_dallas@gmail.com

In our program, a person has an email, a family_name, and several other attributes that we care about. We need a way to keep track of them and keep their information organized.

Relations or tables

A single row entry in a database can also be called an "instance". We need a way to keep track of all the instances of our Person model.

A collection of instances that contain the same attributes is called a relation or a table. Tables allow us to better organize models.
table
A schema is a description of the names and types of fields in a table.

table

table

table

One More Thing

The Primary Key

Each row must have a unique way of identifying itself to other parts of the program.

In Rails, every row of a table has a primary key named "id", and that ID is guaranteed to be unique. This prevents problems like two Person instances being confused for each other because they have the same name. We can always tell them apart by their ID.

Our Model of a person

We've added in a primary key for this instance of Person:

id:             1 
Given Name:     Korben
Family Name:    Dallas
Date of Birth:  6/1/12097
Address:        Apt 3497 1 Main St CityVille, Earth
Email:          korben_dallas@gmail.com

What primary keys - or unique identifiers - do you have? For example, a person might have an SSN or a fingerprint.

What other primary keys - or unique identifiers - can you think of?

Summary

In this lesson, we learned about organizing a program's data into models. We were introduced to the database concept of a row, which represents a model instance. Rows are grouped together into tables. Relational databases define the name, data type, and several other characteristics of the fields using a schema. One very important attribute of every row in every table in a Rails application is the id. Primary keys are guaranteed to be unique, and are how we identify individual rows.

Now that you are familiar with how databases organize data, you understand the foundation that the Rails apps we make class will be built on. Relational databases were around long before Rails and will be here long after new technologies have take its place. Rails, and all web frameworks, can be thought of as tools to efficiently and securely bridge web pages and data stored in relational databases.

Simple Queries from LEARN on Vimeo

Database Operations

Open the 'Country' database that already exists on your class computer

Have a look around, and get familiar with the tables and columns

You can also do some SELECT statements to see the data

    SELECT
      code,
      name,
      population
    FROM
      country;

simple select

What's up with that order?

  • Not by code
  • Not by name
  • Not by population

SQL is a function of sets

  • A set is just a collection of things
  • Not ordered by default
  • No Loops
  • IN SQL, think about sets, which are groups of similar records, not loops

Try these queries on for size

A Set

Set
Where

    SELECT
      code,
      name,
      population,
      gnp
    FROM
      country
    where
      gnp > 1e+6

Set Result

Negation

Negation

    SELECT
      *        -- means all columns
    FROM
      country
    WHERE
      NOT(continent = 'North America')

Union

Union
Union Continuity

Intersection

Intersection

    SELECT code, name, population, gnp FROM country WHERE population > 1e+8
    INTERSECT
    SELECT code, name, population, gnp FROM country WHERE gnp > 1e+6

Union Results

Difference

Difference

    SELECT * FROM country WHERE NOT(continent = 'North America')
    EXCEPT
    SELECT * FROM country WHERE lifeexpectancy > 50

Difference Results

Boolean Expressions

Writing this:

    SELECT code, name, population, gnp FROM country WHERE population > 1e+8
    UNION
    SELECT code, name, population, gnp FROM country WHERE gnp > 1e+6

Is the same as this:

    SELECT
      code,
      name,
      population,
      gnp
    FROM
      country
    WHERE
      population > 1e+8
      OR
      gnp > 1e+6

Writing this:

    SELECT code, name, population, gnp FROM country WHERE population > 1e+8
    INTERSECT
    SELECT code, name, population, gnp FROM country WHERE gnp > 1e+6

Is the same as this:

    SELECT
      code,
      name,
      population,
      gnp
    FROM
      country
    WHERE
      population > 1e+8
      AND
      gnp > 1e+6;

Functions and Expressions

    SELECT
      code,
      name,
      population,
      gnp,
      gnp / population AS gnp_per_capita  # give expressions a name
    FROM
      country
    WHERE
      population > 1e+8
      AND
      gnp > 1e+6;

ORDER BY

    SELECT
      code,
      name,
      population,
      gnp
    FROM
      country
    where
      gnp > 1e+6
    ORDER BY
      name DESC;

WITH Subqueries

WITH
    populated_countries AS
        (SELECT
            code,
            name,
            population,
            gnp
        FROM
            country
        WHERE
            population >0
        )
SELECT
    code,
    name,
    population,
    gnp,
    gnp / population AS gnp_per_capita
FROM
    populated_countries
WHERE
    gnp / population > 0.03;

Summary

In this section we've taken a look at the different types of SQL queries. You can use to create sets. We tackled WHERE clauses, Negation, Unions, Intersections, Differences, and the boolean expressions AND and OR. Then we evaluated functions and ORDER BY clauses. The SQL language is very mature, and full featured. We've only touched the surface of what you can do with SQL.

Resources

The country database is from http://www.stat.fi/worldinfigures

Group by and Aggregate Functions

Understanding Group By

So far, we've stored items into tables and selected lists of items from them. But what if we wanted to do more than print lists? In SQL, we can also perform operations on a list like addition or computing the average.

If we've created a table called "cars" that contains a make, a color, and mileage per gallon:

 id | make  | color  | mileage  
----+-------+--------+---------
  1 | BMW   | blue   |     6
  2 | Mazda | blue   |     7
  3 | BMW   | yellow |     8

If we want to group by color, we can:

SELECT color 
FROM cars 
GROUP BY color;

However, the output will show us only that, the color:

color
-----
yellow
blue

Asking to print a column like make:

SELECT color, make
FROM cars 
GROUP BY color;

would result in an error like this one:

ERROR: column "cars.make" must appear in the GROUP BY clause or be used in an aggregate function.

What this means is that that when you group by color, each row of the make column would have to contain a set of values instead of one single value.

We can ask for the number of blue cars that exist, however -- which would be using an aggregate function, COUNT -- and would look like this:

SELECT color, count(cars.id) AS number_of_cars 
FROM cars 
GROUP BY color;

which returns:

color   | number_of_cars
--------+---------------
 yellow |     1
 blue   |     2

SQL has a variety of aggregate functions that can be used with GROUP BY in addition to COUNT, such as SUM, AVG, MIN, and MAX. For example, if one wanted to view the average miles per gallon of cars by color:

SELECT color, avg(cars.mileage) AS avg_mileage 
FROM cars 
GROUP BY color;
color   |    avg_mileage     
--------+--------------------
 yellow | 8.0000000000000000
 blue   | 6.5000000000000000

Or, for the greatest mileage by car color:

SELECT color, max(cars.mileage) AS max_mileage 
FROM cars 
GROUP BY color;
 color  | max_mileage 
--------+-------------
 yellow |           8
 blue   |           7

Comparisons

A WHERE clause accepts the following operators:

=, !=, >, <, >=, <=,
BETWEEN, LIKE, and IN,
NOT, AND, OR.

Use of operators can be grouped with parenthesis.

Here are examples of the last set of operators:

> SELECT * FROM persons WHERE age  BETWEEN 13 AND 17;
> SELECT * FROM persons WHERE name BETWEEN 'Judith' AND 'Wilma';
> SELECT * FROM persons WHERE hair IN ('red', 'brown');
> SELECT * FROM persons WHERE name LIKE 'We%';

The IN operator refers to a set of values.
For the LIKE operator, the % is a wildcard, meaning it can stand for any number of any characters.

Ordering

You can order the result set of a query by adding an ORDER BY clause after the WHERE clause.
For instance your can sort the results by name:

> SELECT * 
  FROM persons
  WHERE age BETWEEN 13 AND 17
  ORDER BY name;

You can append ASC or DESC to make the order ascending or descending.

Limits

A SELECT can be limited to only return a maximum number of rows by adding a LIMIT after the WHERE clause.
The following examples limits the number of rows in the result set to ten:

> SELECT * 
  FROM persons 
  WHERE age BETWEEN 13 AND 17 
  LIMIT 10;

Working with NULL

NULL is a special value that used to denote "no value", similar to nil in Ruby, and null or undefined in Javascript.
The strange thing about NULL is that is not equal to anything, including itself.
So to specify NULL in a WHERE clause we have to use IS NULL or IS NOT NULL.

  • Most functions and expressions yield NULL if NULL is an argument
  • You can account for NULL with
    • IS NULL,
    • IS NOT NULL or
    • COALESCE which returns first non-NULL value

rails generate

Rails has a generate command that allows developers to save time by creating all necessary files for an app to work.

rails generate controller

To generate a controller you must give the controller a name. Then within that controller you will want a method. You can generate the method in the same line. The command will look like:

rails generate controller Cars create

In the Terminal:

$ rails generate controller Cars create
      create  app/controllers/cars_controller.rb
       route  get 'cars/create'
      invoke  erb
      create    app/views/cars
      create    app/views/cars/create.html.erb
      invoke  test_unit
      create    test/controllers/cars_controller_test.rb
      invoke  helper
      create    app/helpers/cars_helper.rb
      invoke    test_unit
      invoke  assets
      invoke    coffee
      create      app/assets/javascripts/cars.coffee
      invoke    scss
      create      app/assets/stylesheets/cars.scss

In your rails app it will:

  • create a CarsController
  • define a create method within the CarsController

controllers/cars_controller.rb

class CarsController < ApplicationController
  def create
  end
end
  • create a route in the routes file

config/routes.rb

  get 'cars/create'
  • create a cars view folder and create.html.erb file.

views/cars/create.html.erb

<h1>Car#create</h1>
<p>Find me in app/views/car/my_auto.html.erb</p>

The controller and view can be tested by going to http://localhost3000/car/create

rails destroy

If you so happen to make a mistake when generating your controller, you can destroy it and try again.

The command to do that is:

rails destroy controller Cars create 

The command will delete all files associated with the CarController.


Resource

rails generate
rails destroy

Workflows in Rails from LEARN on Vimeo

Making Complete Flows

In Rails, there are suddenly a lot of moving pieces to keep in mind. Controller methods often correspond to a view, and in order to load a view in the browser we need to create a route for it. Let's practice building an app using the Rails workflow. For this app, we're going to build two forms that take in some user input, a name and a number, a page that greets the user and tells them whether their number is odd or even, and the routes and controllers that make it all run.

Creating a New Thing

Generator

To make life easier for developers, Rails can generate code for you. Rather than creating controllers by hand, we can have Rails create one for us with a single line in the terminal. Click here for more information from the official documentation.

We are going to use the noun-verb pattern: we want to register a user.
However, we are going to need two routes:

  • http://localhost:3000/users/index to display an index web page.
  • http://localhost:3000/users/register to display a register page, which is also where the information is going to be sent when users click on the submit button.

Technically index is not a verb but read it as "display index page"; index is often used for a starting or landing page for historial reasons.

To craete the corresponding routes, controller and views, enter the following in the terminal:

rails generate controller Users index register

This command will create a controller called Users with two methods in it, index and register, among other things.

Notice: it is Rails convention for controller names to be pluralized nouns and method names to be verbs.

This yields something like this:

create  app/controllers/users_controller.rb
route  get 'users/register'
invoke  erb
create    app/views/users
create    app/views/users/index.html.erb
create    app/views/users/register.html.erb
invoke  rspec
create    spec/controllers/users_controller_spec.rb
create    spec/views/users
create    spec/views/users/index.html.erb_spec.rb
create    spec/views/users/register.html.erb_spec.rb
invoke  helper
create    app/helpers/users_helper.rb
invoke    rspec
create      spec/helpers/users_helper_spec.rb
invoke  assets
invoke    coffee
create      app/assets/javascripts/users.coffee
invoke    scss
create      app/assets/stylesheets/users.scss

See how, in addition to the controller, Rails has also made an .html.erb file for us?

Test that the newly created implementation works by visiting the routes:
http://localhost:3000/users/index
http://localhost:3000/users/register

We should be able to see the default view that our generator created.

The generator gives us a lot of files. Here are the files we care about right now:

config/routes.rb
controllers/users_controller.rb
views/users/index.html.erb
views/users/register.html.erb

Get Name

The default functionality on our register view isn't terribly interesting. Let's make the UsersController keep presenting the register view until the user inputs a valid name.

class UsersController < ApplicationController
  def index
  end

  def register
    if params.has_key?(:user_name) && !params[:user_name].strip.empty?
      # User has entered a valid name
      @name = "User name is " + params[:user_name]
    else
      # User has not entered a valid name so ask again
      @name = "Name missing"
    end
  end
end

views/users/register.html.erb:

<%= @name %>

<form action="/users/register" method="get">
  <input type="text" name="user_name"/>
  <input type="submit"/>
</form>

config/routes.rb:

  # Show the index page
  get '/users/index'
  # Run the register method and show the register page.
  get '/users/register'

Play Game

Now, we would like our Rails app to tell the user whether a number is odd or even. Like before, this will involve a some views, some routes, and some methods, and a controller to coordinate everything behind the scenes. How can we break it down?

For the game we are going to show results and submit a new number on the same page so we only need one route/controller/view.
Following the noun-verb pattern, we want route to be: http://localhost/games/play.
We can start by creating another controller:

rails generate controller Games play

See for yourself that the route, controller and view was created.

In views/games/play.html.erb insert:

Number is: <%= @result_string %>
<hr/>
<form action="play" method="get">
  <input type="number" name="number" required/>
  <input type="submit" value="Submit Number"/>
</form>

And now, our method that sends the number from the form on play.html.erb is:

class GamesController < ApplicationController
  def play
    @result_string = params[:number].to_i.odd? ? "Odd" : "Even"
  end
end

Put a comment in config/routes.rb for the new route:

  # Play the game after user has registered
  get 'games/play'

After Getting Name Go To Game

What if we want to pass information from our UsersController to our GamesController? We can do that with cookies.

Putting the name parameter into a cookie and forwarding (redirecting) to the GamesController would look like this:

class UsersController < ApplicationController
  def index
  end

  def register
    if !params.has_key?(:user_name) || params[:user_name].strip.empty?
        @name = "Name missing"
      render "register.html.erb"
    else
      # save name in a cookie, so it can be accessed in the game
      cookies[:user_name] = params[:user_name]
      # redirect to the game play page
      redirect_to "/games/play"
    end
  end
end

We use redirect_to here to change the URL in the browser's address bar.
If we had used render then the GamesController play method would not have been invoked which makes the play.html.erb fail bacause the @result_string has not been set.

Using an Object

We can use objects in our code as well. Let's create a User class to hold given and family names as attributes:

models/user.rb

class User
  def initialize(given, family)
    @given = given
    @family = family
  end

  # To be called in the view
  def full_name
    @given + " " + @family
  end

  # Necessary for YAML
  def given= new_given
    @given = new_given
  end

  def family= new_family
    @family = new_family
  end
end

Modify the controller:

class UsersController < ApplicationController
  def index
  end

  def register
    if !params.has_key?(:given) || params[:given].strip.empty? ||
       !params.has_key?(:family) || params[:family].strip.empty?
      @name = "Name missing"
      render "register.html.erb"
    else
      # create a user object with information submitted
      user = User.new(params[:given], params[:family])
      # save user object in session, so it can be accessed in the game
      session[:user] = user.to_yaml
      # rediect to the game play page
      redirect_to "/games/play"
    end
  end
end

Modify the play view:

User: <%= @user.full_name %>
<br/>
Number is: <%= @result_string %>
<hr/>
<form action="play" method="get">
  <input type="number" name="number" required/>
  <input type="submit" value="Submit Number"/>
</form>

Modify the GamesController to extract the user object from the session and generate a result:

class GamesController < ApplicationController
  def play
    @result_string = params[:number].to_i.odd? ? "Odd" : "Even"
    @user = YAML.load(session[:user])
  end
end

SQL Country Database Challenges

Save your queries in a file if you want to keep them for posterity.

WHERE

  • What is the population of the US? (HINT: starts with 2, ends with 000)
  • What is the area of the US? (starts with 9, ends with million square miles)
  • List the countries in Africa that have a population smaller than 30,000,000 and a life expectancy of more than 45? (all 37 of them)
  • Which countries are something like a republic? (are there 122 or 143 countries or ?)
  • Which countries are some kind of republic and achieved independence after 1945?
  • Which countries achieved independence after 1945 and are not some kind of republic?

ORDER BY

  • Which fifteen countries have the lowest life expectancy? highest life expectancy?
  • Which five countries have the lowest population density? highest population density?
  • Which is the smallest country, by area and population? the 10 smallest countries, by area and population?
  • Which is the biggest country, by area and population? the 10 biggest countries, by area and population?

WITH

  • Of the smallest 10 countries, which has the biggest gnp? (hint: use WITH and LIMIT)
  • Of the smallest 10 countries, which has the biggest per capita gnp?
  • Of the biggest 10 countries, which has the biggest gnp?
  • Of the biggest 10 countries, which has the biggest per capita gnp?
  • What is the sum of surface area of the 10 biggest countries in the world? The 10 smallest?

GROUP BY

  • How big are the continents in terms of area and population?
  • Which region has the highest average gnp?
  • Who is the most influential head of state measured by population?
  • Who is the most influential head of state measured by surface area?
  • What are the most common forms of government? (hint: use count(*))
  • What are the forms of government for the top ten countries by surface area?
  • What are the forms of government for the top ten richest nations? (technically most productive)
  • What are the forms of government for the top ten richest per capita nations? (technically most productive)

Stretch Challenges

  • What year is this country database from? Cross reference various pieces of information to determine the age of this database.
  • How many countries are in North America?
  • Which countries gained their independence before 1963?
  • What is the total population of all continents?
  • What is the average life expectancy for all continents?
  • Which countries have the letter z in the name? How many?

Today's Tentative Schedule

9:15am - Stand Up

9:30am - Rails Generate, The Asset Pipeline & Workflow

11:00am - Challenge: Car Accelerator

12:00 noon - Lunch

1:00pm - continue with Car Accelerator Challenge

4.30pm - Review

5:00pm - Class Ends