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 it does not appear or takes too long try switching to a different engine.
The engines run different SQL implementations. You can see from the table below which of the engines is working right now.
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 to anyone - via SQL of course... gisqlog - while it is extremely unlikely that anyone could or would monitor an individual, users should be aware that the possibilty exists. Paranoid users should avoid using GISQ Engines.
I would like to provide a variety of GISQ Engines. Ideally each user would be assigned one of a large number of engines selected at random. I would also like to provide a variety of platforms. If you have a machine which is permanently connected to the internet which can spare some processing capacity please get in touch.
I would also like to process the logs to find out what the common errors are and how error messages might be improved. It could make a fine student project. andrew@dcs.napier.ac.uk
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 |
|---|---|---|---|
| Oracle | excellent, non-standard (but better) join syntax | free downloads available hideous installation | unix, windows ... |
| MS SQL Server | excellent | 30 day trial available | requires NT Server :-( |
| MySQL | no views or nested SELECT | free, easy to install | unix, windows... |
| PostgreSQL | buggy views | 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.
The data is available in Microsoft Access format gisq.mdb If you have MS Access you can create and execute SQL queries using the "Queries" tab.
The individual tables are available in tab delimited format. This can be imported into pretty well anything. It may be much faster.
| 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 | |
| Dressmaker | ||
| Musician |
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.
Comments, corrections and criticisms are welcome. Please contact andrew@dcs.napier.ac.uk