Databases
SQL
Structured query language (SQL) is the language with which we create, update and interrogate databases. It is a declarative language. At GCSE we looked at the select, insert, update and delete queries.
INSERT INTO
The INSERT INTO query is used to insert data into a table. You can enter data into specific fields by specifying the fields in brackets on the INSET INTO line or else insert data into all fields in the table. The data is
enetered into the fields in the order it is given. In the example below a Team ID of 3, Team name of "Kangaroos" and Colours of "Gold/Green" would be enetered in the Teams table.
UPDATE
The UPDATE query can be used to update the value stored in a field in a table. You provide the new value and the field to update to the query and the conditions to identify which record to update.
SELECT
The select query allows you to select and sort information from related tables within the database.
It is possible to search for data from more than one related table. When you do this the fields must be written as
tablename.fieldname where the same fieldname appears in more than one table. In the example below we are able to retrieve the team name from the team table while searching up its members in the players table. The order by
command lets us sort the results by one or ore fields in ascending or descending order.
SQL operators
In order to make more complex conditions for the WHERE part of a SELECT, UPDATE and DELETE queries we can use SQL operators.
The most basic of these allow us to use all the normal comparison operators such as equal to, not equal to, greater than, less than etc. We can also use basic Boolean operators AND, OR and NOT to combine conditions.
In addition, there are a number of special terms that we can use:
IN allows us to specify a tuple with multiple values we can search e.g. WHERE Genre IN ("Comedy","Action","Horror")
LIKE allows us to search
for terms that meet a certain pattern and is most powerful when used with either the _ or % signs which are both wildcards. An underscore represents exactly one character while a % sign represents 0 or more e.g. WHERE surname
LIKE "S%" would get all surnames beginning with "S".
BETWEEN lets you return numeric or date based values in a certain range e.g. WHERE dob BETWEEN #01/01/2001# AND #31/12/2001# would select all the people who have a
date of birth in the year 2001.
ISNULL lets you search for records where a certain field is blank e.g. WHERE email IS NULL would return records where the email field is blank
Defining tables with SQL
We can use the CREATE TABLE command to create tables defining each of the fields in terms of their name, data type and any additional information reuired. Data types can be seen below.
We also need to specify primary and foreign keys when we define our table. We can also specify mandatory fields by adding NOT NULL. A loans table in a library might look like:
CREATE TABLE Students
(
StudentID INT, NOT NULL, PRIMARY KEY
Forename VARCHAR(20), NOT NULL
Surname VARCHAR(25), NOT NULL
DOB DATE, NOT NULL
TakesBus BOOLEAN
)