Day 31 (week 7) — SQL

We started 2 hours late today because of flooding in the area and most public schools also had a two hour delay. So today was a bit shorter.

SQL stands for Structured Query Language. There is a ton of information on the web about SQL and its history, so I won’t go into those details here. I have quite a bit of experience with SQL from my days at Motorola. In that job, I put together a system that collected test data from a family of 8-bit microconrollers and populated a database. I served that data from an Apache webserver with PHP. It was a 2 year project, and I gained a lot of knowledge about how to do things like database normalization and schema design.

So today wasn’t necessarily anything new for me. In class, it looks like we will be using Postgres as our SQL server. We talked about everything from basic SELECT queries to isolating just the data you want using WHERE clauses. We spent some time installing Postgres with homebrew, but I already had things installed on my system.

The daily project was pretty straightforward. All we needed to do was write a sql script file to create a table, populate it with some data, run a few queries against the table, and the delete all the records from the table.

I ended up with this:

/* drop the table if it's already there */
DROP TABLE IF EXISTS todos;

/* create the todo table per specifications */
CREATE TABLE todos (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
details VARCHAR(1000),
priority INTEGER NOT NULL DEFAULT 1,
created_at TIMESTAMP NOT NULL,
completed_at TIMESTAMP
);

/* insert 5 new todos */
INSERT INTO todos (title, details, priority, created_at, completed_at)
VALUES('todo1', 'this is todo details', 1, date('2017-8-28'), NULL);

INSERT INTO todos (title, details, priority, created_at, completed_at)
VALUES('todo2', 'this is todo details', 2, date('2017-8-28'), NULL);

INSERT INTO todos (title, details, priority, created_at, completed_at)
VALUES('completed', 'this is todo details', 1, date('2017-8-28'), date('2017-8-29'));

INSERT INTO todos (title, details, priority, created_at, completed_at)
VALUES('todo4', 'this is todo details', 2, date('2017-8-28'), NULL);

INSERT INTO todos (title, details, priority, created_at, completed_at)
VALUES('incomplete', 'this is todo details', 1, date('2017-8-28'), NULL);

/* get all the todos that are not completed */
SELECT * from todos WHERE completed_at IS NULL;

/* select all the todos that have priority greater than 1 */
SELECT * from todos WHERE priority > 1;

/* update the todo with title='incomplete' to be completed */
UPDATE todos SET completed_at = date('2017-8-30') WHERE id = (SELECT id FROM todos WHERE title='incomplete');

/* show all the todos to verify the update */
SELECT * from todos;

/* delete all the todos */
DELETE FROM todos;

SQL syntax is pretty easy to read. I’m proud of two statements in this code that I remembered from 13 years ago.

The first is the DROP TABLE IF EXISTS todos. What this says is that if there is a table in the db named todos, delete it. If it doesn’t exist, just ignore this statement.

The second is in the UPDATE statement towards the end. Because we needed to update an incomplete todo to be completed by id, and we didn’t know the id of the incomplete todo, I used an embedded WHERE to get the id of the todo with a title of incomplete.

Last, my weekly project of the code snippet editor is coming along nicely. I have the ability to register new users on the site, they can author their own code snippets, view other user’s code snippets, and logout. I haven’t done much in the way of visually designing the site, but here is what it looks like right now. I’m using CodemirrorJS to handle the syntax highlighting of each code snippet.

Over the next few days, I will add some functionality and clean up the appearance of the site. The code is here.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.