Lesson: SQL (Continued)

SQL2.md

SQL

Lesson Objectives

• Be able to insert new data into the database
• Be able to query existing data
• Be able to update existing data
• Be able to delete data
• Explain common keywords within queries and use them
• Explain inner joins
• Explain left, right, and full outer joins
• Connect a Ruby program to a database and run queries

Review

• Why do we need databases?
• Normalization
• Data types
  • Boolean
  • Integer
  • Float
  • Text/VARCHAR
  • NULL
  • Date
  • Time
  • And many more...
• Syntax
  • All statements end with a semicolon
  • White space doesn't matter
  • Use only single quotes to denote strings; double quotes are for table or column identifiers
  • Group things with parentheses
  • Keywords tend to be uppercased (although this is just a convention)
• Constraints
  • primary keys
  • foreign keys
  • unique
• Data relationships
  • One to one
  • One to many
  • Many to many

CRUD Statements

9:45, 30 minutes

pgAdmin

We could do everything we wanted in psql, but let's use pgAdmin to visualize what's happening better.

If you're not able to login with the postgres superuser, we might have to run \du in psql to see who the superuser is. Don't use a password when logging into localhost

Inserting records

• INSERT INTO wdi (first_name, last_name, age) VALUES ('Sean', 'Shannon', 26);

Reading records

So now that we have this data saved, we're going to need to access it at some point, right? We're going to want to select particular datapoints in our dataset provided certain conditions. The PostgreSQL SELECT statement is used to fetch the data from a database table which returns data in the form of result table. These result tables are called result-sets. The syntax is just what you would have guessed:

SELECT column1, column2, columnN FROM table_name;

We can pass in what columns we want to look - like above - at or even get all our table records:

SELECT * FROM table_name;

• SELECT * FROM wdi;
• The WHERE clause
  • To use the where clause, we need a field to compare, an operator specifying the type of comparison to be made, and a value that represents our criterion
  • SELECT * FROM wdi WHERE last_name = 'Shannon';
  • SELECT * FROM wdi WHERE age >= 25;
• Count
  • SELECT count(*) FROM wdi;
• Order by
  • SELECT * FROM wdi ORDER BY last_name ASC;
  • SELECT * FROM wdi ORDER BY last_name DESC;
• Limit
  • SELECT * FROM wdi LIMIT 10;
• Offset
  • SELECT * FROM wdi LIMIT 10 OFFSET 3;

Updating records

• UPDATE students SET ss_num = 987654321 WHERE age > 25;

Deleting records

• DELETE FROM students WHERE age > 25;

LAB EXERCISE

10:15, 15 minutes

https://github.com/ga-students/wdi-atx-2-class/blob/master/w05/d01/sql_afternoon/sql_practice/README.md

BREAK, 10 minutes

Loading seed data

Let's load the worlds.sql seed data that will help us with the rest of the lecture

Open up terminal

psql

Create the worlds database

create database worlds

Switch to the new database

\c worlds

Copy the contents of the worlds.sql file from the class repo and paste it into terminal

Joins

10:35, 30 minutes

• Inner Join
  • Combines only the records that match both sides

INNER JOIN TableB ON TableA.name = TableB.name ```

• Left/Right Join
  • Combines all the records of one side with the matching record from the other side, and fills in null for remaining
• Full Outer Join
  • Combines all record of both sides with matching record from the other side, and fills in null for remaining. Potential for duplicate data.
• http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

LAB EXERCISE

11:15, 20 minutes

## Accessing PG from Scripts **11:35, 30 minutes**

• For now we can simply

    require "pg"
    conn = PG.connect(:hostaddr => "127.0.0.1", :port => 5432, :dbname => "wdi")
    res = conn.exec("SELECT * FROM students")
    res.each do |item|
      puts item
    end
  

• The PG object allows us to access and interface with the Postgres database. It is the API (or bridge) from our app to the database.

• Here, we're saving the result of the PG connection to a variable named conn, then we're using the exec method of that object to send a SQL query to the database. Finally, we're looping through the results of the query and puts-ing them.

EXERCISE: Write a simple program that will take input from the user and insert their information as a row in the students table we created using the PG object to communicate with the database.

LAB EXERCISE

12:05, Remainder of class

Using SQL, create a Ruby class for Student and add class methods that would implement the basic CRUD functionality to interact with a database by calling a Ruby method on the class.

BONUS: Try thinking of additional helpful SQL commands that you can implement to act on multiple records at a time, and implement those.