Home

SQL tutor

The main script generates a page to set an SQL task, accept SQL input, execute that SQL and compare the results with the expected results. The script is driven by an XML description of the exercises.

Emp-dept case Study

• User Help
• Introduction to the case study
• Worksheet and subject index
• Tutor view of worksheets and logged inputs

Implementation

• XML definition of the Introductory worksheet.
• Worksheet XML schema

SQL coverage missing

Syntax

• Sub selects
• String operators - concatenate, uppercase, lower case , trim
• Case
• EXISTS
• COALESCE
• UNION, INTERSECTION and EXCEPT - and workarounds - see fehilly p230

Creating and Updated

Add a worksheet which takes them through the task of setting up a database for themselves. This will require logging in, entering the database username and password tobe held in a session variable, and then commands to create the database and load it then the student can then follow through the exercises - going back to drop and reload the database as needed if it gets in a mess

In some ways this would also provide a simple web-based interface to their database for development

Bugs

Active

• Commands should be the same if single or double quotes are used
• Spaces are not significant in some places in SQL so insignificant differences are sometimes detected
• Bare < in a error message generates an XML parse error - will be fixed with switch to SQL module

Fixed

• Discussions coming out when the step is initially displayed, not when the query has been executed correctly
• Hints should shown if there is a syntax error
• Column comparison not working -too lax
• Table comparison was broken and too lax
• A print view in which problems are accompanied by the answers

Changes needed

• students dont read the discussion note and go straight on to the next page - perhaps should confirm they have read it first.
• detect rows in a different order if not ORDER BY
• Improve diagnostics
• Allow user to comment on questions - to add alternative ways of achieving the same effect - as per slides
• Add search amongst a set of worksheets
• Add registration and tracking of user activity
• Add other types of question - eg text, multiple choice
• Use the SQL module rather than use the PHP helper - this opens the way for different databases to be used - problem with SQL module which throws a Java exception if the sql isinvalid - needs a Java change before use
• support database creation and data insert, update and delete?
• Hints should be associated with different kinds of error
• Some entries are just text (Hint) others expect elements - no consistancy here - should be able to cope with both.
• Description/ Discussion distinction could be made by placement in the script rather than different names
• Randomised Queries
• Student can't review an answer they have correctly entered because the application does not remember where the individual student has been - registration and user state for that.
• Students can get to see the correct result whatever invalid SQL they enter - needs some staged release of information - but that would need state too.
• Replace the generic Step with Reading, Demo , Exercise , ScratchPad elements to facilitate validation of a worksheet and procesing with typeswitch - major upheaval!
• Need to be able to define a common set of nav links for a set of worksheets - to link to data model and references.