AS/A2 Computing: Standard Query Language (SQL)
- SQL is a "fourth generation" computer programming language.
- No conventional rules for declaring variables or procedures apply in SQL.
- It is setup to extract and manipulate data from a database.
- Used solely in relational databases. SQL could be defined as the standard language for communication with a relational database management system.
- SQL is not case sensitive, but it is conventional to capitalise statements and write everything else in lower case, though this is optional.
- All statements in SQL finish with the semicolon character, ";". SQL does not recognise "white space" so you can format your code as you wish.
- Rows can be referred to as tuples and columns as domains.
SQL can be categorised into to areas, DDL (data definition language) and DML (data manipulation language).
Data definition language
- Allows the structure of a database to be setup.
- This includes creating, deleting and altering tables and indexes.
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
- SELECT: choose data to display, print or perform another operation with.
- INSERT: insert values into a table by adding a complete row or one or more columns at a time.
- UPDATE: edit/change/modify one or more rows or columns of data.
- DELETE: delete a row or more than one row.
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.
