Interactive SQL tutor

Purpose

This application is designed to help students learn SQL. It supports any number of worksheets.

Each worksheet explores an aspect of the SQL language. A worksheet contains multiple steps which the student will work through. There are several kinds of step.

• Read: Some steps are simple explanations. Read the content, follow any links of interest, then go on to the next step.
• Demo:Other steps are demonstrations of SQL statements. Each describes a task and shows the SQL required to carry out the task, and the result of the SQL statement when executed against the example database. Study the example before proceeding to the next step.
• Task: These pose a task to be carried out and prompt the student to enter an SQL statement to accomplish the task. When submitted, the SQL is executed and the result is compared with the result which is returned when the model answer statement is executed. If they are different, the student's result and the model result are shown, any diagnostics or hints output and the student prompted to correct the SQL statement. When the correct statement has been entered, the student can move on to the next step.

Examples

Here are a couple of worksheets - more are being developed.

• Introduction to the emp-dept case study
• Simple select statements

Navigation

The student is free to move around the worksheets and between steps in a worksheet at will. However they are encouraged to follow the steps in order, and to try to get a correct answer to each exercise.

SQL statements

Model SQL statements are shown with reserved words in upper-case, user-defined words in lower-case. This is purely for explanatory purposes since SQL is not case-sensitive. However, table names must be entered in the same case as that used in the database creation because they correspond to UNIX file names.

Model SQL statements are terminated with a semi-colon but this is optional in this interface.

Diagnostics

When there is a difference between the result of the query generated by the student's statement and the model answer statement, the application attempts to diagnose the problem. Some of this is automatic, based on a comparison of the resultant tables and the two SQL statements (rudimentary at present). These are combined with authored, step-specific hints.

Comparison between the two results allows for different headings (because columns may be aliased) and different column ordering, since column order is not significant . Where the results are equivalent but there is a difference between the student's SQL statement and the model answer, the model answer statement is also output for reference.

This application does not require any registration so we are not tracking the performance of individual students. However all input is logged so that tutors can identify common problems with exercises and improve the hints provided .

The Application

The database and back-end server to be used to execute SQL statements is defined in the worksheet configuration. This is limited to databases running on a MySQL 5.1 server at the University. The client has only select access to the database.

Worksheets are written in an XML vocabulary and may be hosted within the XML database or accessed externally.

This application has been created by Chris Wallace to whom all queries or suggestions for improvement should be addressed. The application is written in XQuery running an eXist XML database, in which the XML descriptions of each worksheet are held.

Key Bindings

You can use the following keys to move about a worksheet and link to related resources:

• Page Up - next Step
• Page Down - previous Step
• Home - first Step
• End - last Step