SQL, Part I

  1. SQL Introductions
  2. Manipulating SQL Databases
  3. Applied SQL: The Prom, Part I

SQL Introductions

Quick Terms

To learn how to create and manipulate databses, we will be using SQL, which is a relational database model. As such, the database is comprised of tables, each table with a specific name so it can be called.

In every table, there are fields you need to create, each with their own name and a set type of data that can be inputted into them. The data types you can add are listed below, with their type, and what they allow. (Based off the list at SQLCourse here.)

char(size) A String (that is, a specific sequence of symbols) of a specific length. The Size is specified in the parenthesis.
varchar(size) A string with a variable length. The (size) is the maximum length.
number(size) a numeric value, with a maximum number of digits specified in (size).
date A date (duh).

SQL Basics

So you know how what to put in a table, but now you need to create a database and make the table itself. Using SQL, you will fill up and mess with items you put in the database itself. First off, download SQlite3. This is a lightweight implementation of SQL from the command line; while it does not have a GUI like OpenOffice Database or a similar program, it is still quite helpful as a learning tool. Get the program by entering the following in a terminal window:

sudo apt-get install sqlite3

Once you have installed, type

sqlite3 test.db

and the following will appear, telling you you have entered the program:

SQLite version 3.3.13
Enter ".help" for instructions
sqlite> 

Now you have created a new database called test.db, where your tables will be stored. You create a table using the following syntax:

create table "tablename"
("column1" "data type",
 "column2" "data type",
 "column3" "data type");

So let us say you want to create a two-column table, one column with the name of a person and the next with age. The resulting code would be:

create table nameandage
(name varchar(30),
 age number(2));

Note a few features, especially that you do not need quotes around named things, and that all SQL statements end in a semicolon (;).

Now that we have our table, we need to add stuff into it, with a statement much like this:

insert into nameandage
  (name, age)
  values ('Tim the Enchanter', 45);

Note that the type of data we want to input requires some differences in input; for instance, the string of Tim the Enchanter is encased in single quotes, while numbers are not. When entering these statements in the terminal, you can hit 'Enter' as much as you want for as many lines as you need- only after you end the statement with a semicolon will the statement be processed.

There is one other thing you have to learn how to do, to create statements that select data. This is the format:

select "columnx"
  from "table"
  where "conditionx" /*this line is optional */;

From specifies from what table the data is to be drawn from. The where line is entirely optional, but it sets up conditions so you can be choosy (literally) about what data you get. There are conditionals that are used in these statements. They are:

= Equal to
> / >= Greater than / Greater than or equal to
< / <= Less than / Less than or equal to
<> not equal to

There is one other that operates differently from the others, called like. For instance, if we wanted to look for everyone in the table who had Tim in their name, we would do this:

select name
from nameandage
where name LIKE 'Tim%';

If we ran this is sqlite3, we would get this:

sqlite> select name
   ...> from nameandage
   ...> where name LIKE 'Tim%';
Tim the Enchanter
sqlite> 

Manipulating SQL Databases

Of course, creating a database is one thing, and adding new entries is another. However, having a static database does you no good. You have to be able to change it, when you want. And that is what we are going to do.

Here is the basic format. Before you read on, try and figure out what each part does (do not worry, it is explained, but try and see how good you are getting...

update "tablename"
set "columnx" = "newvaluex" where "columnx" 
OPERATOR "value";

As you could guess, the first line tells the database which table to update. The next line sets a column to a new value. (if you wanted, you could just have more columns changed as well, for instance like

set "columnx" = 
"newvaluex" ,"columny" = "newvaluey"

...and so on. Here is a real world example, where 'Tim the Enchanter' has become 'John Smith':

update nameandage
  set name = 'John Smith'
  where name = 'Tim the Enchanter';

Then, check for our new name change:

sqlite> select name
   ...> from nameandage
   ...> where name LIKE 'John%';
John Smith
sqlite> 

Of course, anything we do, we should like to remove. Here is the code for removing something from a table:

delete from "table"
where "columnx" OPERATOR "y";

The first line quite obviously specifies the table you wish to delete a record from; the second line specifies conditions. In this case, delete whatever is in "table" when the identified column is (=/>/</etc) to 'y'. In action, let us say that we will create a new person in our table, and suddenly decide to delete them. Let us create Ronald McDonald, age 40. (If you cannot remember how to do this, look back through this lesson!)

Anyway, the code to remove him would be:

delete from nameandage
where name = 'Ronald McDonald';

So by performing a select query, we can check if Ronald is gone, and as the code below shows, nothing was returned, therefor the entire row that was Ronald has been erased.

sqlite> select name
   ...> from nameandage;
   ...> where name LIKE 'Ronald%'
sqlite> 

And what about erasing entire tables, you say? Look no farther than the simple command drop table 'tablename'.

Finally, there are some sqlite commands that you can find helpful. The .tables command lists all the tables in a given database; the .schema command shows all the tables and the way they are structured. To actually see what a given

Applied SQL: The Prom, Part I

Now let's start using all this SQL basics for a project, which you will be adding onto. Say that a school dance is coming up. As manager of this dance, it is up to you to determine who is coming, make changes to the participants list, and other functions. Of course, annoyingly, the school administrators aren't fully sure what they will need the database for, and will be pestering you with changes.

Task 1: So far, you have been given the following instructions; to create a table which contains the first name, last name, and grade level of each person attending, as well as whether they have paid for their tickets yet.

Select the area below for one possible way to complete task 1:

create table promlist
(lastname varchar(20), firstname varchar(15), grade number(2), paid char(1));

Task 2: The administrators are happy with your table, however they want you to start adding in the people who have signed up for the prom so far. They have sent you the following list. Add all these people to the table "promlist":

  1. Ray Donahoe (Junior) -paid
  2. Joe Lee (Senior)
  3. David Ross (Sophomore) -paid
  4. Ben Yue (Sophomore)
  5. Will Cox (Junior)
  6. Valerie Clark (Senior)
  7. Jessica Presley (Junior)
  8. Courtney Zito (Senior)
  9. Samantha Giobbi (Senior)

Select the area below for one possible way to complete task 2:

/* repeat the following for each person */
insert into promlist
  (last, first, grade, paid)
  values ('Donahoe', 'Ray', 11, 'y');

Now that you have completed this first part, its Time to continue to Part II of SQL.

The full sqlite transcript for this set of lessons (not tasks) can be found here.

[top] | [lessons] | SQL: Part I, Part II