Creating Tables

To play around with SQL we need to create a set of mock data. (Unless you already have some database to play on.) My favorite application for this is DB Browser for SQLite. If you are using any other database application you will need to search the internet and find out how to create a database you can add tables to. Fortunately, one of the reasons for SQL to exist is to create a standardized method to create and manipulate tables. So once you have a playground database you can use SQL statements to work it no matter the application.

Start DB Browser for SQLite. On the menu bar click File → New Database or press Control-N. DB Browser for SQLite saves databases in a file, so you will be prompted to choose a file path and name. Once the file is created you may get a popup to help you create your first table. Press the cancel button. We are going to create the table the hard way.

Click the Execute SQL tab. On this tab we have three text boxes aligned vertically. Enter SQL in the top text box. Execute the code by pressing the execute button. The output from the query will appear in the middle text box and messages will appear in the bottom text box.

SQL is a written language to create database tables, insert data into those tables, update the data, and extract data. All database applications have some method for executing an SQL query. DB Browser for SQLite has a simple text editor where you enter queries. Click on the Execute SQL tab to display the editor.

We are going to create a table to hold student data. To create a table we need to consider the "thing" we are describing. In this case it is a student. We need a secret ID number for the student, an ID number we give to the student (which is actually going to be text, not a number), the first name and the last name. (I will explain more below.)

Creating the PERSON Table

This is the SQL to create the PERSON table.

CREATE TABLE person (
	person_id CHAR(6),
	person_first VARCHAR(30),
	person_last VARCHAR(30)
);

The keywords CREATE TABLE tells the database to create a table. The word that follows those keywords, person, are the name of the new table. SQL is case insensitive. The "CREATE TABLE" is the same as "create table" as far as the database is concerned. Traditionally keywords are written in upper case to make the code clearer.

After the table name we have set of open and close parenthesis enclosing a list of columns to create. Each column has a column name followed by the type of data that will be stored in the column. Each column specification, aside from the last one, has a comma at the end to separate it from the other columns. I have prefixed each column name with person_ to help

The person_id is a character (CHAR) data type. The number six in parenthesis tells the database that there will be exactly six characters for each record. We are going to give each student a unique ID number in the format "X00000". So we only need six characters.

The person_first and person_last columns are variable character strings (VARCHAR). This means they are text (or strings of characters) that may vary in size. The number in parenthesis is the maximum number of characters that will fit in the column. Declaring text as VARCHAR saves space in the database because it will only allocate enough storage space to contain the data. The drawback is decreased efficiency as the database program must figure out how many characters to allocate and make appropriate adjustments.

The SQL command is terminated with a semicolon. This tells the database application when the SQL ends. For most applications you must terminate every command with a semicolon. But some applications do not require the terminator because you can only execute one SQL statement at a time. DB Browser for SQLite allows multiple SQL statements as long as they are semicolon terminated but it only displays the results for the last statement executed.

Creating the AWARDS Table

And now we create an awards table with the following SQL.

CREATE TABLE award (
	award_id CHAR(6),
	award_fund CHAR(6),
	award_offered FLOAT,
	award_accepted FLOAT,
	award_disbursed FLOAT
);

This table will hold financial aid awards for the students. The award id is the same id as the person_id on the person table so it also only needs six characters. This sets up what is called a "one to many" relationship between the person and award tables. For each one student in the person table there will be zero or more awards in the awards table.

The award_fund column is the six character fund code such as PELL, SEOG, SUB (subsidized loan), and UNSUB (unsubsidised loan).

The award_offered, award_accepted, and award_disbursed columns are the amount that the student is eligible for, the amount the student agrees to receive, and the amount that was disbursed to the student respectively. We define these columns as FLOAT to make them floating point decimal values.

Epilog

If you are using DB Browser for SQLite you can save your code by pressing the Save SQL File button. You can save it with any extension but traditionally it should be saved it with ".sql".

These are the bare bones instructions for creating tables. They are intended for you to be able to create simple tables that can be used for practising SQL. I have left out a lot that I may revisit later on. You might want to search online for "sql keys", "primary keys", "foreign keys", "sql constraints", NOT NULL, and "sql datatypes". But for now we are moving on to get to the queries as quickly as reasonably possible.

We have two tables set up. Now we need to insert some data. But first, let's take a quick, but very important, diversion into code comments and quoting.

Notes

1: The column identifier name PIDM is used in a popular ERP program that I use at work. I'm using it so I can more effectively teach people at work who are using the data warehouse.