AS/A2 Computing: Standard Query Language (SQL)

SQL can be categorised into to areas, DDL (data definition language) and DML (data manipulation language).

Data definition language

To create a table the following syntax is used:

CREATE TABLE <tablename> (<Columntitle> <Datatype> <Constraints>, ...)

For example:

CREATE TABLE books 
(id int not null unique, 
title varchar(200));

This creates a table called books with two fields, id and title. The field "id" is of type integer (int) and the field "title" is of type variable length string, of maximum length 200 characters. The field "id" has two constraints applied to it, it must be both unique and contain a value (not null). The uniqueness of the field "id" does not imply that it is a primary key.

A primary key can be defined as follows:

CREATE TABLE books (id int not null unique, title varchar(200) primary key(id));

Data manipulation language

For example, to insert data into the table books:

INSERT INTO books VALUES(1,'David Copperfield');

Note that the quotes around David Copperfield are necessary as the entry is text; a string of characters. Here are two further examples for using INSERT.

INSERT INTO books VALUES(2,'Oliver Twist');
INSERT INTO books VALUES(3,'Mary Poppins');

UPDATE can be used to update a single record or a group of records that match given criteria:

UPDATE books SET title='Wayne Twist' WHERE id=1;

...and DELETE can be used to delete a record or a group of records that match given criteria:

DELETE FROM books WHERE id=1;

...and SELECT can be used to select fields from a table or from specific records where a given criteria is met, as in the following examples:

SELECT title FROM books;
SELECT id,title FROM books;
SELECT * FROM books;
SELECT * FROM books
WHERE title LIKE 'David';

The asterisk (*) wildcard character is used to select all fields. The like statement searches for fields where the title contains "David".

More on SQL can be found at the following locations:

These notes are from a lesson on 24/06/2004.

Click here to view this page as a PDF file