This is the curriculum for the 2019 Alpha 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: [email protected]

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:          [email protected]

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

HTTP Response Codes

Part of the HTTP protocol is issuing a response code with every response. This ensures that there is at least some understandable piece of data, no matter what else the response contains. These codes are specified by the W3C, and all webservers everywhere, along with all the web browsers agree on them. You'll eventually learn some of these and throw them out like they are common knowledge: "So I just got a 404 back on that request.", and to your web developer friends, they will be. There are some codes that you will never ever use, and even some codes that are just for fun. Don't worry about memorizing them, you'll remember the important ones. Just learn to recognize them when you see them, and be able to look them up when you need to.

General categorization of HTTP codes

All response codes are 3 digits long, and the first digit is meaningful. If you only know what the first digit meant for response codes, you'd already know a lot about what that code means.

  • 1xx : Information about connection that is still in progress
  • 2xx : Successful response
  • 3xx : Redirection
  • 4xx : Client side error
  • 5xx : Server side error

The Internet is not without a sense of humor.

Remember when I said there were a few joke response codes in the specification? Take a look at 418, its a valid response code:

  • 418 I'm a teapot (RFC 2324) This code was defined in 1998 as one of the traditional IETF April Fools' jokes, in RFC 2324, Hyper Text Coffee Pot Control Protocol, and is not expected to be implemented by actual HTTP servers. The RFC specifies this code should be returned by teapots requested to brew coffee.[49] This HTTP status is used as an Easter egg in some websites, including Google.com.[50]

Webservers can send their own codes.

Some webservers like Apache and Nginx may send their own codes as well. You'll want to watch carefully for these, as they are less common and could cause bugs in your code. Check this Wikipedia article for more information.

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