043 SQL

18 May 2013

SQL is a structured query language for databases. In this episode we will create a simple database on our Solar System planets and their moons. We will explore both a GUI Sequel Pro for viewing SQL as well as SQLfiddle, a web based version to run SQL statements. Then we will display the data create using php and sinatra (ruby).

Download video: mp4

Sample code: Github

Version: MySQL 5.5.25

Similar episodes: 009 Package Managers, 014 Local web server, 040 Sinatra

Background on SQL

  1. What is SQL and databases
  2. Differences between the types of SQL - PostgreSQL, SQLite, MySQL
  3. Comparison on RDMS
  4. MySQL data types
  5. Using with php and sinatra application with active record

Things to learn with SQL

1. install MySQL

  1. install MAMP which comes with MySQL and start the servers
  2. open the MAMP's start page to know the connection parameters

    Host      localhost
    Port      3306
    User      root
    Password   root
    
  3. log in to MAMP's MySQL as the root in the command line

    $ /Applications/MAMP/Library/bin/mysql -u root -p
    Enter password: root
    ...
    mysql>
    
  4. Install database viewer Sequel Pro and connect in the Standard mode

    Name:     Learning SQL
    Host:     127.0.0.1
    Username: root
    Password: root
    Port:     3306
    
  5. clear screen cmd + k

  6. Quit from the MySQL

    mysql> quit
    

2. information on databases

  1. show all databases

    mysql> SHOW databases;
    
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | another_db         |
    +--------------------+
    4 rows in set (0.00 sec)
    
  2. character sets

     mysql> SHOW CHARACTER SET;
    
  3. checkout mysql data types

2. create and delete database

  1. create new database and use it to show that it has zero created tables

    mysql> CREATE DATABASE computer_networks;
    mysql> SHOW databases;
    mysql> USE computer_networks
    mysql> SHOW tables;
    
  2. delete database

    mysql> DROP DATABASE computer_networks;
    mysql> SHOW tables;
    

3. create/read/update/delete data

  1. create database

    mysql> CREATE DATABASE solar_system;
    mysql> SHOW databases;
    mysql> USE solar_system;
    mysql> SHOW tables;
    
  2. create table 1 on planets and show the table structure

    mysql> CREATE TABLE planets(
        -> id INT NOT NULL AUTO_INCREMENT,
        -> name varchar(20),
        -> distance float,
        -> mass float,
        -> atmosphere text,
        -> CONSTRAINT id PRIMARY KEY (id)
        -> );
    
    mysql> DESC planets;
    
    mysql> SHOW tables;
    
  3. insert data and show them

    mysql> INSERT INTO planets(name, distance, mass, atmosphere)
    -> VALUES("Mercury", 0.4, 0.55, "molecular oxygen");
    
    mysql> SELECT * FROM planets;
    
    mysql> INSERT INTO planets(name, distance, mass, atmosphere) VALUES("Venus", 0.7, 0.815, "carbon dioxide");
    mysql> INSERT INTO planets(name, distance, mass, atmosphere) VALUES("Earth", 1, 1, "nitrogen");
    mysql> INSERT INTO planets(name, distance, mass, atmosphere) VALUES("Mars", 1.5, 0.107, "carbon dioxide");
    mysql> INSERT INTO planets(name, distance, mass, atmosphere) VALUES("Jupiter", 5.2, 318, "hydrogen and helium");
    mysql> INSERT INTO planets(name, distance, mass, atmosphere) VALUES("Saturn", 9.5, 95.152, "hydrogen");
    mysql> INSERT INTO planets(name, distance, mass, atmosphere) VALUES("Uranus", 19.6, 14, "hydrogen and helium");
    mysql> INSERT INTO planets(name, distance, mass, atmosphere) VALUES("Neptune", 30, 17, "hydrogen and helium");
    mysql> INSERT INTO planets(name, distance, mass, atmosphere) VALUES("Pluto", 48.9, 0.002, "nitrogen, methane and carbon monoxide");
    
    mysql> SELECT * FROM planets;
    

| ID | NAME | DISTANCE | MASS | ATMOSPHERE | +------------------------------------------------------------------------------------------- | 1 | Mercury | 0.40000000596 | 0.550000011921 | Molecular oxygen | | 2 | Venus | 0.699999988079 | 0.814999997616 | Carbon dioxide | | 3 | Earth | 1 | 1 | Nitrogen | | 4 | Mars | 1.5 | 0.107000000775 | Carbon dioxide | | 5 | Jupiter | 5.199999809265 | 318 | hydrogen and helium | | 6 | Saturn | 9.5 | 95.152000427246 | hydrogen | | 7 | Uranus | 19.60000038147 | 14 | hydrogen and helium | | 8 | Neptune | 30 | 17 | hydrogen and helium | | 9 | Pluto | 48.900001525879 | 0.002000000095 | nitrogen, methane and carbon monoxide | ```

  1. refresh databases in Seqel Pro to see the new database
  2. delete Pluto

    mysql> DELETE FROM planets WHERE name="Pluto";
    
  3. create and relate another table

    mysql> CREATE TABLE moons(
        -> id INT NOT NULL AUTO_INCREMENT,
        -> planet_id INTEGER NOT NULL,
        -> name varchar(20),
        -> CONSTRAINT moon_pk PRIMARY KEY (id),
        -> CONSTRAINT planet_fk FOREIGN KEY (planet_id) REFERENCES planets(id)
        -> );
    
    mysql> DESC moons;
    
    mysql> SHOW tables;
    
  4. insert some moon data and show them

    mysql> INSERT INTO moons(name, planet_id) VALUES("Moon", 3);
    mysql> INSERT INTO moons(name, planet_id) VALUES("Phobos", 4);
    mysql> INSERT INTO moons(name, planet_id) VALUES("Deimos", 4);
    mysql> INSERT INTO moons(name, planet_id) VALUES("Io", 5);
    mysql> INSERT INTO moons(name, planet_id) VALUES("Europa", 5);
    mysql> INSERT INTO moons(name, planet_id) VALUES("Ganymede", 5);
    mysql> INSERT INTO moons(name, planet_id) VALUES("Callisto", 5);
    
    mysql> SELECT * FROM moons;
    
    | ID | PLANET_ID |     NAME |
    +----------------------------
    |  1 |         3 |     Moon |
    |  2 |         4 |   Phobos |
    |  3 |         4 |   Deimos |
    |  4 |         5 |       Io |
    |  5 |         5 |   Europa |
    |  6 |         5 | Ganymede |
    |  7 |         5 | Callisto |
    

4. query/join data

  1. play with the data in SQLfiddle
  2. select by columns

    SELECT name, distance, mass FROM planets;
    
  3. limit the number of results

    SELECT name, distance, mass FROM planets LIMIT 5;
    
  4. order the results

    SELECT * FROM planets ORDER BY mass;
    
  5. display moons of each planets

    SELECT moons.id AS "#", moons.name AS Moons, planets.name AS "Belongs to"
    FROM moons
    INNER JOIN planets ON planets.id = moons.planet_id
    
    | # |    MOONS | BELONGS TO |
    +----------------------------
    | 1 |     Moon |      Earth |
    | 2 |   Phobos |       Mars |
    | 3 |   Deimos |       Mars |
    | 4 |       Io |    Jupiter |
    | 5 |   Europa |    Jupiter |
    | 6 | Ganymede |    Jupiter |
    | 7 | Callisto |    Jupiter |
    
  6. display each planets and then their moons

    SELECT planets.name as Planets, GROUP_CONCAT(moons.name) as Moons
    FROM (planets LEFT JOIN moons ON moons.planet_id = planets.id)
    WHERE moons.name IS NOT NULL
    GROUP BY planets.id
    
    | PLANETS |                       MOONS |
    +-----------------------------------------
    |   Earth |                        Moon |
    |    Mars |               Deimos,Phobos |
    | Jupiter | Europa,Callisto,Io,Ganymede |
    

5. connect with applications

with php

  1. start the MAMP server
  2. create a file index.php in the document root of the MAMP's web server with the code for php and MySQL extension and amend 3 lines accordingly:

    ...
    $link = mysql_connect('localhost', 'root', 'root')
    ...
    mysql_select_db('solar_system')
    ...
    $query = 'SELECT * FROM planets';
    ...
    

with ruby, sinatra

  1. install mysql2 gem for MAMP's MySQL with Ruby 1.9.3
  2. create a file app.rb to create a sinatra application with active record

    require 'rubygems'
    require 'sinatra'
    require 'mysql2'
    require 'active_record'
    
    ActiveRecord::Base.establish_connection(
      :adapter  => 'mysql2',
      :host     => 'localhost',
      :database => 'solar_system',
      :username => 'root',
      :password => 'root',
      :socket => '/Applications/MAMP/tmp/mysql/mysql.sock'
    )
    
    class Planet < ActiveRecord::Base
    end
    
    get '/' do
      @planets = Planet.all()
      erb :index
    end
    
  3. create a file views/index.erb

    <% for planet in @planets %>
      <p><%= planet.name %> at <%= planet.distance %>AU from the Sun with weight of <%= planet.mass %> Earth Mass and mostly <%= planet.atmosphere %> in the atmosphere.</p>
    <% end %>
    
  4. go to the command lin in the root of the file app.rb

    $ ruby app.rb
    

More Resources on SQL

  1. SQL Essentials screencast course
  2. SQL
  3. Learning SQL book by O'Reilley
  4. Online SQL - SQLfiddle
  5. Visual explanation of SQL joins
  6. PostgreSQL, SQLite, MySQL compared
  7. When to use SQLite

Build Link of this episode

Google I/O 2013 videos