That Blue Square Thing

Computer Science GCSE

A record, but not in a database

More Complex SQL

You need to be able to do some slightly more complex things with SQL. It's nothing too complex once you give it a go, but it is a little tricky at first and there are some important ideas to develop here as well.

The first idea comes back the problem you might have deleting items in databases when they have the same name.

Primary Keys

It is really helpful if each record in a database has some sort of unique way of identifying it. It makes it much harder to search for a specific object, particularly when you're using UPDATE or DELETE commands.

Almost all real world databases have this. Your passport has a unique number that identifies you on it and your National Insurance number and Driving License number are unique as well - as is your bank account number, your cell phone number and your school username. Once you look around you'll find unique numbers everywhere.

The field in a database which contains the unique identifier is called the Primary Key. You can define a field as the primary key in a CREATE TABLE command:

CREATE TABLE (id CHAR (5) PRIMARY KEY, name CHAR(20), stock);

This will mean that it's impossible to enter the same value for a second record in the primary key field - you can still have two names which are the same, but not two identical id fields.

PDF iconPrimary Keys - what they are and how to use them

> Watch Mr DBMS provide a good explanation of why a Primary Key is importantwiki link on YouTube. Watch up to about 1:37 for the minute. You can watch the rest after the next section!

Text file iconCode to create the zoo database with a primary key field. This can be used in the SQL interpretor at Tutorials Pointwiki link.

Note that some forms of SQL require you to deal with primary keys in slightly different ways. The W3Schools tutorial page on primary keyswiki link is excellent if you ever need it.

Multi-table Databases

Good databases will often contain more than one table. This will reduce the problem of having duplicated data in a table.

Duplicated data means the same pieces of data repeated in several records for no good reason. This is called redundant data and makes your database harder to update and less efficient.

PDF iconWhy multiple tables are needed - this is particularly true of very large databases

PDF iconFood Supplier Database Tables - how the tables would be split

Text file iconCode to create the food supplier database. This can be used in the SQL interpretor at Tutorials Pointwiki link.

> Now watch the rest of the video where Mr DBMS provide a good explanation of why a Primary Key is importantwiki link. This gets into foreign keys.

> MR DBMS has another video which goes through keys againwiki link It has the same information but is still worth a look as keys are complex to try to understand.

Multiple table databases need one last SQL command to make them easy to retrieve data from - the JOIN command.

If you're looking for a more in depth discussion of using multiple table databases then you might want to read some of the stuff I wrote for A Level Applied ICT (not Computer Science note) students a few years ago. The PDF documents in the first section of that page are probably most useful.

SQL with Multiple Table Databases - using JOIN

The JOIN command allows us to retrieve data efficiently from multiple table databases.

Essentially it lets you link tables together to retrieve data from both. It can be a little tricky to get the syntax for this right first time, but it's the sort of thing that comes with practise.

PDF iconUsing JOIN commands - worth going through with the Food Supplier database.

The W3Schools tutorial on INNER JOINwiki link is worth a look if you need to clarify how it works.