Quick Ref.
Functions
date
number
string
Data Types
date
number
string

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: Darius Priya Ryka Justin Gumbo 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

Wenn Sie das nächste Mal Edinburgh besuchen, können Sie seine Hand schütteln im 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/source/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?

Ja bitte, verlinken Sie von so vielen Orten wie möglich, von Ihrer Seite, von Foren etc.

Wenn möglich, fügen Sie das Wort SQL in den Linktext, z.B.: sqlzoo.net: an interactive SQL tutorial

Da Sie schon fragen - es gibt zwei Arten von Links, die mich stören -

  • 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, die die Top-Einträge von Gogle für den Suchbegriff SQL wiedergeben.

Keine dieser Punkte schaden mir - aber sie tun mir auch nichts Gutes. Universities (including my own, Napier) should not use gated systems like this.

Coolest incoming link: MIT

Getting hold of SQL

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

Implementation Einhaltung des SQL-Standards Installationshinweise Plattformen
Mimer ausgezeichnet freie Downloads für Entwickler verfügbar Unix, Windows ...
Oracle gut freie Downloads verfügbar, sehr umfangreiche Installation Unix, Windows ...
MS SQL Server gut 30-Tage-Testversion verfügbar Windows NT, 2000 und folgende
MySQL keine Views oder verschachteltes SELECT. Ist in Arbeit. frei, leicht zu installieren Unix, Windows...
PostgreSQL sehr gut free, easy to install nur Unix
Access viele Eigenarten, nicht schwerwiegend leicht zu installieren nur Windows

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

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 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
Music album track style
Film-Datenbank movie actor casting
Parlament tabmsp.txt tabparty.txt
ACME-Produkte badguy product shipped receipt
Edinburgh-Busse stops route
Top of the Pops tabtotp.txt
Schneider
Musiker
Southwind
Staugebühren
Stundenplan (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:

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 SQLzoo engine
Be warned that you should only run a SQLzoo 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.
Feedback
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.
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.

What happened to the CIA database?

Prior to 2006 the early tutorials were based on data from the CIA World Factbook. The data now comes from the BBC Country Profiles pages.

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

The CIA World Factbook is still a remarkable result, and the CIA should be congratulated for being one of the first authoritative resources on the the internet. In the early days of the internet most large organisations were worrying about how they could protect themselves from the internet.

When I first took the data from the world factbook I associated the CIA with Mission Impossible and cool spy equipment. Since then the CIA is more associated with extraordinary rendition. They seem less cool.

The CIA World Factbook also has some bizarre inclusions. The RAF base in Akrotiri in Cyprus is a place I love. While it may have the fire power of a small country it is not a country by any civil measure.

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: