That Blue Square Thing

Computer Science GCSE

A record, but not in a database

Basic SQL

SQL is Structured Query Language. It's the language that can be used to search (query) databases. And you need to know bits of it...

Modern Database Management Systems (DBMS) don't usually require any knowledge of SQL to be able to use them. But it's the fundamental language that underlies all databases so it's important that you understand the basics.

The Basics of SQL

You need to understand and be able to use five basic SQL commands. You'll need to be able to use these in an exam, so it's crucial that you can read them and write code using them by hand.

You can use the SQL interpretor at Tutorials Pointwiki link to work on using bits and pieces of SQL. Just bear in mind that you'll need to be able to write code by hand!

There's an OK video dealing with What is SQL on YouTubewiki link. The first 3 and a bit minutes are really the bit you want to look at - the rest is an advert more or less. You'll, obviously, find all sorts of linked videos from the side bar if you want to explore further.

Notes about Syntax:

When I write SQL I try and keep SQL commands in UPPERCASE and make sure that all table and field names are in lowercase. It just makes it easier to understand.

And, where I can, I'll try and break code down over different lines.

And don't forget the semi-colon at the end of code!

1. CREATE TABLE:

Code to create a new SQL table. This is slightly tricky code to start with, but if you want to do anything else in the Tutorials Point interpretor then you need to be able to make a table first!

It's important to set the data type properly for each field. Different data type options include:

PDF iconSQL - the CREATE TABLE command

2. INSERT INTO:

Once you have a table made you need to fill it with some data if you're going to be able to do anything useful with it. INSERT INTO does that.

It's important to remember to insert the data in the right order as the fields were defined in the CREATE TABLE command. You also need to make sure that you have the right type of data being entered in each field and that you don't have data that's too long for any CHAR or VARCHAR fields - otherwise the command won't enter any data.

PDF iconSQL - the INSERT INTO command

The importance of NULL values:
When you use INSERT INTO, each field must have a value entered into it. If you don't have a value to enter into a field for some reason you can't leave it blank. Instead you need to enter NULL - a special value which tells the SQL interpretor that there is missing data.
INSERT INTO animals VALUES("Pete", "Aardvark", NULL, "M")

3. SELECT:

SELECT is the key to writing SQL queries - in other words, searching a database to retrieve some data.

The first command you need is to know how to print everything in the database to the screen. That's a simple command:

SELECT * FROM animals;

SELECT is used alongside:

You only need SELECT and FROM for a query to work, but WHEERE and ORDER BY allow much more interesting queries to be written.

PDF iconSQL - the SELECT command

PDF iconSQL - Operators - how different operators (>, <>, AND, OR, BETWEEN, IN, LIKE and NOT NULL) can be used in SQL.

Note that there are different ways to use wildcards with LIKE commands. % is usually used, but some forms of SQL use * instead.

4. UPDATE:

UPDATE is used to change the value in a particular field. You need to use the SET command with it to set the new value of the field and it is usually useful to use it alongside WHERE.

PDF iconSQL - the UPDATE command

Take care using UPDATE. If you're not careful you can end up changing all the animal's names to the same thing...

5. DELETE:

DELETE can be used to delete a record from a database (to delete a row from the table). It is used alongside FROM and WHERE to specify which record to delete.

PDF iconSQL - the DELETE command

One of the interesting things about DELETE is that it really helps if each record in the database has some kind of way to identify it uniquely. If, for example, I have two animals both called Molly in my zoo, how can I tell each one apart if I need to use a DELETE command?

This bring into play the idea of KEY fields - and that's one of the things the Complex SQL page gets into...