Some of you may not have encountered the database language SQL before, so this section will introduce the basics. SQL databases are collections of named tables, each with a uniform column structure. Each column has a name and a type (e.g. text, numeric, date). Rows in a table have a value for each column (although SQL does provide for NULL markers for missing or unknown data). Each table should have a primary key: a set of columns whose values must, in combination, be unique across all rows in the table. They may also have foreign keys, which are sets of columns whose values must reference or correspond to values in another set of columns (usually the primary key columns in another table). For example, a table for paper enrolments would have a foreign key referencing the student, and another foreign key referencing the paper. A database design can be depicted using an Entity/Relationship Diagram (ERD).

SQL statements are divided into a number of categories, primarily the data definition language (DDL) for creating and managing database objects such as tables, and the data manipulation language (DML) for querying and modifying user data.

The most common DDL statement is CREATE TABLE, which creates a new table in the database with the specified structure:

create table Student (
  Student_ID number,
  Surname varchar,
  First_Names varchar,
  Birth_Date date,

  constraint Student_PK primary key (Student_ID)
);

Data can then be added to the table using the INSERT statement:

insert into Student (Student_ID, Surname, First_Names) values (123, 'Jones', 'Jenny');

or modified using the UPDATE statement:

update Student set First_Names = 'Jennifer' where Student_ID = 123;

or removed using the DELETE statement:

delete from Student where Student_ID = 123;

Data can be retrieved (queried) using the SELECT statement:

select Student_ID, First_Names || ' ' || Surname
from Student
where Student_ID = 123
order by Surname, First_Names;

Exercise

Adapt this code (make up a few more rows of data) to set up a small student database using the http://sqlfiddle.com site. Set the database engine (top left) to SQLite (WebSQL). Note that you will need to combine the DDL code and run it in the left-hand Schema Pane. You can then try running some queries in the Query Pane on the right.