A Gentle Introduction to


Notes for users

This tutorial is intended to be used "hands-on". The student should be reading the material and trying out the examples. The output from the SQL engine should appear in a new window.

If no results window appears it may because:

The engine you are using is broken
Check the list below to see which engines are working right now.
Your browser does not allow pop-up windows
If you turned off pop-ups because of annoying adverts then get a copy of ad-aware (see electronic self defense ) - you can then turn pop-up on again and use this site.
I could provide a frames version of this site - but I shall not unless I am convinced there is a demand for it.

How to learn

When learning any new skill students should start with the basics. The simple functions should be practised over and over until he or she is comfortable with them and confident enough to execute them almost automatically.

Only when the student has absorbed the basics should more complex techniques be attempted. Even if you find this style pedestrian you must not skip material unless


The answers to the questions are given. Do not look at the answers until you have had a go yourself.

sqlpass-latam Engines

The engines run a variety of SQL implementations on a number of different machines. You can see from the table below which of the engines is working right now.

The following links may repair broken or slow engines: Darius Priya Ryka Justin Gumbo


Every SQL attempt is logged along with information that can be gathered about the user (net address and an identifying cookie). This data is available is not held securely and will be given away to anyone who is interested - just ask.

It is unlikely that anyone could or would monitor an individual, users should be aware that the possibilty exists. Paranoid users should avoid using sqlpass-latam Engines.

The data is collected and analysed for educational research. We occasionally conduct experiments and have had several successful student projects which have used the tutorial.

Getting hold of SQL

You may wish to use your own SQL engine - the "connected" servers will inevitably have a variable performance.

Implementation Adherence to SQL Standard Installation notes Platforms
Mimer excellent free downloads available for developers unix, windows ...
Oracle good free downloads available, hideous installation unix, windows ...
MS SQL Server good 30 day trial available Windows NT or 2000 or more
MySQL no views or nested SELECT free, easy to install unix, windows...
PostgreSQL very good free, easy to install unix only
Access many "quirks", none serious easy to install windows only

The zip file gisq.zip contains all of the tutorial material.

Obtaining the data

The data is available in Microsoft Access format gisq.mdb

SQL commands to create and populate the tables are available.

The individual tables are available in tab delimited format. This can be imported into pretty well anything.

Tutorial SQL commands Tables plain text (tab delimited)
CIA World Factbook cia
Movie Database movie actor casting
Holyrood tabmsp.txt tabparty.txt
ACME products badguy product shipped receipt
Edinburgh Buses stops route
Top of the Pops tabtotp.txt
Congestion Charging

Notes for teachers

This material was designed to be used in supervised tutorials at Napier University. Teachers from other institutions are welcome to use it in any way they see fit, however I have a few requests/suggestions:

Reliability of the static web pages:
Take a copy of the tutorial material to run on your own server. My server fails with distressing regularity. Copies of this material are at the following locations. Please let me have the address of your copy if you take one.
#SQL, blueyonder Napier, sqlpass-latam
Reliability of the SQL Engines
Sometimes long running processes accumulate. kill scotts processes on Ryka
Because of the nature of the task the various engines may fail or perform badly. You can run your own engine - running for the exclusive use of your students if you want to be mean.
Instructions for running your own sqlpass-latam engine
Be warned that you should only run a sqlpass-latam engine on a "spare" computer. Both Unix and Windows work fine.
Install the following... Apache, Perl and at least one SQL implementation such as MySQL, Oracle, Postgres, Access, SQL Server.
Create the users gisq and scott. Scott should have password tiger. The gisq password should be kept secret.
Copy the files gisq.cgi and localGISQ.pl to an "executable" directory.
Edit the file localGISQ.pl to reflect your installation.
Visit your gisq.cgi page and keep fixing the installation/installing perl modules till you get the "Cannot find table cia" error.
Go to the data distributor to create and populate your tables.
Send an email to a.cumming at napier.ac.uk and I will add your engine to the list.
If you are making use of this material please let me know what worked well and what didn't.
Let me know if any of the questions are poorly phrased or confusing. I have tried to keep the language as simple as possible. Many students do not have English as their first language - I would like to hear more from them.
There are a number of assessments (including Neeps, Musicians and Southwind) - these are larger, more complicated databases. In each case there are 15 questions graded as easy, medium and difficult. I allow students to select any 5 for their formal assessment the marks that can be obtained depend on the difficulty. A student can get a safe pass by completing the 5 easy questions correctly. Students can (theoretically) get 100% for completing the five hard questions with elegant code.
The answers to the assessments will not be made available on line. Please let me know if you find answers to these questions anywhere on line - I will find a way to stop it. I do have model answers but I will only release them to folk who can prove they are teachers not students.
Plug Napier University
Student applications are always welcome.


The red error messages that appear in tutorial one are generated in JavaScript from code developed by Emma Oram as part of her excellent MSc. project 2002.

Readers should assume that errors or omissions in the data are mine and not the fault of following sources.

Thanks are due to the following fine organisations: