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
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
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)