-
Explain what a SQL database is and compare/contrast it with a spreadsheet.
-
Using a reference,
Today we’re going to talk about the concept of ‘persistence’, or storing data outside a program. Data inside a program is not saved anywhere! We can write data to text files, but this data isn’t necessarily easy to parse and search through.
A database is a system for managing data. Data are written to files (and read from those files) in such a way to maximize efficiency of storing and searching/querying large amounts of data.
There are many different kinds of databases, but most common are ones that follow the SQL standard (e.g. Postgres, MySQL). In a SQL database, data are stored in tables of columns and rows, similar to a spreadsheet. Unlike a spreadsheet, although rows can easily be added/removed/modified, columns cannot be changed without scrapping your table, creating a new one, and copying over (‘migrating’) the data over to the new table.
Think of it like having one table per sheet of paper. Any time you want to change its structure, you need to take a new sheet, create a new table with that structure, and copy over all the information. A single database can have many different tables.
Generally, when we interact with databases, we do so through a database server which listens for requests and reads from (or makes changes to) a given database.
You can access Postgres through the database client in the console, psql.
Alternatively, there are other programs that can communicate directly with that
server through its API. The Ruby gem pg is a tool for just that purpose -
it allows you to manipulate a Postgres database using Ruby code.
To create a new database, type psql in the console to open up the database
client, and inside the client, type CREATE DATABASE myDatabase;.
To destroy that database, write DROP DATABASE myDatabase;
The point of having a database is to manipulate tables and table rows, so let's get to it.
Let’s create a new table and give it some rows.
CREATE TABLE people (name varchar, age integer, eye_color varchar);
INSERT INTO people (name, age, eye_color) VALUES ('Matt', 29, 'hazel'),
('David', 33, 'brown');Now let’s try querying the database, to read what we’ve just written.
SELECT * FROM people;
SELECT * FROM people WHERE name=’Matt’;
SELECT * FROM people WHERE age < 30;
SELECT * FROM people ORDER BY age (DESC);
SELECT name, age FROM people;
Time: 5 minutes
On your own, create a new table called ‘pets’, with columns ‘name’, ‘species’, and ‘weight’.
Then, insert the following three pets into the table:
| name | species | weight |
|---|---|---|
| Lassie | dog | 25 lbs |
| Tom | cat | 15 lbs |
| Jerry | mouse | 1 lbs |
To update rows, use the ‘Update’ command
UPDATE people SET age = age + 1;
UPDATE people SET age = 29 WHERE name=’Matt’;
UPDATE people SET age = 30 WHERE name='David' AND eye_color='blue'
DELETE FROM people WHERE name=’David’;
DELETE FROM people WHERE age < 30;
Don’t waste time memorizing these - focus on remembering where the reference pages are and learning how to read and understand them.
Time: 5 minutes
Let's go back to the table from the previous exercise.
| name | species | weight |
|---|---|---|
| Lassie | dog | 25 lbs |
| Tom | cat | 15 lbs |
| Jerry | mouse | 1 lbs |
Oh no! Tom has eaten Jerry! We need to update our table to reflect the change (and then call the vet). Change Tom's weight from 15 lbs to 16 lbs. Then, remove Jerry from the table of pets.
When you run SELECT * FROM pets;, you should see something like this:
| name | species | weight |
|---|---|---|
| Lassie | dog | 25 lbs |
| Tom | cat | 16 lbs |
As a challenge, let's suppose that our pets have gone on a diet -- any pets weighing more than 10 lbs will lose 1 lb. Write a SQL command to update the table accordingly.