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 into Student (Student_ID, Surname, First_Names) values (123, 'Jones', 'Jenny');
or modified using the
update Student set First_Names = 'Jennifer' where Student_ID = 123;
or removed using the
delete from Student where Student_ID = 123;
Data can be retrieved (queried) using the
select Student_ID, First_Names || ' ' || Surname from Student where Student_ID = 123 order by Surname, First_Names;
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.