Originally published by Robert Beisert at fortcollinsprogram.robert-beisert.com

Building a Simple SQL Database

So, we’ve looked at the four basic SQL commands, but we haven’t really seen any useful examples of how they would work. In a moment, we’ll look at a set of commands that will create a basic database, fill it with some values, and run a simple SELECT command.

These commands are designed to run in MySQL, which is cross-platform and easy to install. I selected this system for the following reasons:

  • Transient databases – we can build up a long set of commands and execute them in a batch, then see what happened
  • Easy to manipulate – There are a few macro commands employed to make the output prettier
  • Ubiquitous – MySQL is an industry standard

NOTE: to run these commands as one batch (as intended), you will need to be somewhat familiar with your command line interface

The Code

Once you have set up MySQL with a default username and password (there are many excellent tutorials online), run the command:

mysql -u username -p

This will get you into the program, and you can run the following set of commands as a copy-paste.

-- We are assuming that you are using SQLite.
-- MySQL can and usually does have slightly different syntax
-- check out w3schools.com for the differences

/*
Library Database, Phase I
By Robert Beisert
Created 1/19/2016

Designed to run in SQLITE interface
*/

-- First, we create and use a new database
create database librarian;
use librarian;

-- We're going to create a table of people
-- Note: we add a CHECK to ensure that no one has an age that makes no sense
CREATE TABLE people (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT NOT NULL,
age INTEGER CHECK (age > 0 AND age < 120)
);

-- Pretend we work for a library - here are the books
CREATE TABLE books (
book_id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
author TEXT NOT NULL,
copies INTEGER CHECK (copies > 0 AND copies < 500)
);

-- This is a "relationship table" which connects two tables together
-- We'll use foreign keys in this relationship table, because it makes sense to do so
-- You don't HAVE to use foreign keys, but it helps
CREATE TABLE checked_out (
book_id INTEGER,
person_id INTEGER,
checked_out DATE NOT NULL,
checked_in DATE CHECK (checked_in > checked_out OR checked_in = NULL)
);

-- Let's populate the tables a bit
INSERT INTO people VALUES
(1, "Bob", "Sagat", 40),
(2, "Steve", "Austin", 35),
(3, "Michael", "Moore", 60),
(4, "Justin", "Bieber", 20);

INSERT INTO books VALUES
(1, "Harry Potter and the Sorcerer's Stone", "J. K. Rowling", 10),
(2, "Ender's Game", "Orson Scott Card", 5),
(3, "The Holy Bible", "KJV", 3),
(4, "That one book", "That guy", 1);

-- Now we'll say some people checked out some books
INSERT INTO checked_out VALUES
(1, 1, '2015-12-31', '2016-01-05'),
(4, 1, '2014-01-05', NULL),
(2, 2, '2016-01-01', NULL),
(3, 2, '2013-05-16', '2014-06-12');

-- Get a list of all checked-out books and their holders
SELECT first_name, last_name, title
FROM books, people, checked_out
WHERE people.id = checked_out.person_id
AND books.book_id = checked_out.book_id
AND checked_out.checked_in IS NULL;

What that looks like in Linux

Screenshot of Starting MySQL
Logging in

 

Screenshot of Successful SQL Operations
Running the commands in one big copy-paste
Select Results in SQL
The SELECT statement should show you something like this

Further Study

Don’t make too many changes to the database, because we’ll come back to this example, but you should try to do the following:

  • SELECT the ages of everyone who has checked out “Harry Potter and the Sorcerer’s Stone”
  • DROP “Bob Sagat” from the table and INSERT him back in
  • If you’re already familiar with these topics, try to figure out how to COUNT the number of checked out books
    • AND/OR find the number of remaining copies of each book (hint: you can do math operations in SQL)
photo by: