Quick Ref.

Some frequently asked questions about SQLzoo

Why do you have those horrible adverts?

Sorry about that. The point of this site is to teach, not to make money. It's no excuse, but I do use the income for good works, mostly sending me to conferences in hot countries.

The advert-free version is http://sqlzoo.net/noads/

Which Engines are working?

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.

Please report any broken or slow engines to ajcumming@gmail.com

The following links may repair broken or slow engines: Dawn (SQL Server) Priya Ryka Justin Ernest Tilly

Who runs this site?

Andrew Cumming is a lecturer at Napier University in Edinburgh, Scotland, UK.

Can I shake his hand/buy him a drink

Next time you are visiting Edinburgh, UK you can shake his hand at the Tollcross State Circus which meets on Monday, 7pm to 9pm in Tollcross primary school.

You can buy him a drink afterwards, in the Blue Blazer, Spittal Street; a pint of 80 Shilling please.

How does this site work?

The source of the main tutorial section is stored in xml files such as: http://sqlzoo.net/s2/home.xml . These files contain an xml format that is close to html but has a handful of additional tags (such as topbit and qu). The additional tags are documented in http://sqlzoo.net/zoo.dtd. I like to think of this as html+ - basically html but expanded to include the tags I want.

The xml files are processed by an xsl style sheet http://sqlzoo.net/cmn/0.xsl which translates them into proper html. The proper html includes javascript to take care of issues such as:

  • Hiding and revealing hints.
  • Substituing default or answer text in the textboxes.
  • Recording SQL attempts from users.
  • Supplying previous SQL attempts (by cookie).

Additionally the xml files are translated into various languages and stored in language directories such as http://sqlzoo.net/de/home.xml. The translations are initially provided by Google - but can be edited by human translators.

I maintain a "zoo" of SQL engines that process users SQL attempts.

Why is it called SQLzoo?

The animals of this zoo are SQL engines - one of each species. They have been caged and tamed. The public can poke, prod and gawp - the exhibits and the public are protected from each other. Andrew is the zoo keeper, he feeds them and shovels away the waste.

Can I link to your site?

Yes please do - link from as many places as possible. Link from your page, link from bulletin boards, ask for a link from any directories.

If at all possible include the word sql in the text of the link - for example: sqlzoo.net: an interactive SQL tutorial

Since you're asking - there's two kinds of link that bother me -

  • Links from behind content management systems such as WebCT or Blackboard. These sites are password protected and so the web crawlers can't find them.
  • Links that reproduce the top few entries from Google for search term SQL.

Neither of these do me any harm - but they don't do me any good either. Universities (including my own, Napier) should not use gated systems like this.

Getting hold of SQL for free

Many vendors have a free downloadable version available:

Implementation Adherence to SQL Standard Installation notes Platforms
Mimer excellent free downloads available for developers unix, windows, mac osx, symbian ...
Oracle good Oracle 11g1 Express: totally free, but limited to
- max 1 GB bufferpool
- max 1 processor
- max 4 GB per database
unix, windows, mac osx
MS SQL Server good SQL Server 2005/2008 Express versions are totally free, but limited to
- max 1 GB bufferpool
- max 1 processor
- max 4 GB per database
Windows NT or 2000 or more
MySQL good, improving. MySQL Community Server is free, easy to install unix, windows, mac osx...
DB2 very good DB2 9.5 Express-C totally free and no database size limit, but limited to
- max 2 GB bufferpool
- max 2 processors.
linux and windows
PostgreSQL very good free, easy to install, often bundled with linux linux and windows
Access many "quirks", none serious Easy to install. Not free but widely available. windows only

Obtaining the data

Sample data used in SQL zoo has been collected from a variety of sources. While I'm happy for you to publicise sqlzoo as widely as possible you should not attribute the data to me. Some was collected from agencies without their consent some was adapted from the work of colleagues (or their colleagues). My only contribution has been to present it in a convenient format.

The zip file gisq.sql.zip contains all of the tutorial material in MySQL format.

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)
BBC Countries Dataset bbc
CIA World Factbook cia
Nobel Prize Winners nobel
Music album track style
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
Southwind
Congestion Charging
Timetable (Neeps)

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:

Assessments
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.

I use country data from the BBC web site. Their country profile pages include details of just under 200 countries. It makes fascinating reading.

Acknowledgements

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: