Hands on: Using SQL sub-queries

It’s a complete statement that runs if you remove the brackets and returns the Sales Person ID (SPID) for both people from the Horticulture department. As SQL is a set-based language, it returns the values as a set.

The first three lines of code:

SELECT FirstName, LastName, Dept
FROM SalesStaff
WHERE SPID IN

ask for the first and last names from the SalesStaff table of anyone with an SPID that is in the set returned by the sub-query.

Sub-queries are logical, easy to write and can be useful. Use them, ­ they are your friends.

Poll position
In the past, I have run polls asking which database engines people want me to cover. On the last four or five occasions, the answer has been an unequivocal ‘Access’. So I’m not asking which engine this time, but which version.

Although Access 2007 has been out for a while, I have continued to illustrate this column using Access 2003. It always takes people time to move to any new version of software, but I feel this column should move with the times.

It has been clear that the step to 2007 is quite a stretch and it seems many people don’t like the new Office look and feel. Many question, for example, whether the ribbon adds anything at all to Access’ driveability.

So, if 99 per cent of you find 2007 detestable and have decided to stick with earlier versions, that’s what I should do, too, right? So it’s poll time. If you have an opinion on this, please email me with either ‘2003’ or ‘2007’ in the header, but read on a little before you reach for your email system.

Readers have also expressed an interest in more coverage of Microsoft SQL Server and the upgrade path from Access.

For instance, Dick Drinkrow writes: “With SQL Server Express now widely available, could we have some focus on the migration from Access thinking to SQL thinking as part of the database column … SQL Express would be a nice touch for those of us who, for various reasons, have to move to a more substantial environment but don’t wish to tackle Microsoft’s multiple 10in-thick training manuals.”

What do you think? If you wish to vote, include either ‘Yes SQL Server’ or ‘No SQL Server’ in an email header. Anyone wishing to vote on both issues can combine choices in a single header, as in ‘2003 No SQL Server’. I look forward to seeing your choices.

Dbase
Despite what I said above, I do receive occasional emails about other database engines, and in the past month or so I’ve had two about Dbase from Ron Dodge and Peter Moon.

Both readers have Dbase skills and useful Dbase programs. Ron has an application, written in the days of Dos and still running under Windows XP with Visual Dbase, but which fails to print following a change of PC. Peter has many programs he’d like to run again.

It’s worth investigating the website of Databased Intelligence (DBI), an organisation describing itself as “the legal heir to the Dbase legend”. Dbase Plus is the latest incarnation of this long-lived database engine.

Dbase entered public life in 1981 as an Ashton-Tate product and quickly became the prime platform on the PC for database development. It was, to be blunt, a little short on friendliness.

You fired it up, the screen went blank, and if you looked very carefully, you found a single full stop in the bottom left-hand corner. It was called the ‘dot prompt’. And then you just started typing.

It sounds dire from a 2008 perspective, but it was a great program and I retain more than a passing fondness for it. Dbase developed over the years, was bought by Borland in 1991 and then went through a somewhat chequered patch. In the hands of DBI it has staged something of a comeback and was given a major overhaul in 1999.

Dbase Plus runs under 32-bit Windows and has an object-oriented programming language called DBL. It can access data from Oracle, SQL Server, Sybase, MySQL, Informix, DB2, Interbase, Firebird, Pervasive SQL, Access and others via ODBC.

It supports Foxpro, Visual Fox and Paradox (.DB) and, possibly of more interest to Ron and Peter, Dbase native .DBF tables (levels 3, 4, 5 and 7). Tools include a Project Manager to oversee the building of Dbase applications, a debugger, SQL Query Designer and a Table Designer for creating or modifying tables.

More mapping
In the May column, I wrote about mapping Ordnance Survey (OS) co-ordinates stored in Access on to a Map Point map, and I received a clever piece of coding with a mapping theme from Mike Webb, but without the map.

Mike works with maintenance and emergency callout engineers and wanted a way to optimise the engineers’ travelling time. If an engineer is called to a location for an emergency, Mike wanted to identify other nearby locations where a maintenance visit could be made.

Mike stores the OS grid references of each location to the accuracy of a metre square. He calculates the location of each as distance in metres east and north from the origin of the whole Ordnance Survey coverage of the UK. This is a point slightly south-west of the Isles of Scilly (a false origin devised by the OS to ensure all co-ordinates are positive).

Using Pythagoras’ theorem (the square of the hypotenuse is equal to the sum of the squares of the other two sides) Mike then works out the distance from the engineer’s current location to other locations. Finally, he translates these into eight directions on a compass rose (south, south-west, west, north-west, etc).

The final solution tells him how far away, and in which direction, lie locations where the engineer could usefully pay a maintenance visit. It has minor imperfections.

As Mike says: “It falls down somewhat where an estuary is in the way, but has proved to be generally successful.” He also says: “I know it could be simplified, but once I found out that it worked, I just wanted to tiptoe quietly away.”

This is, I think, a great example of what you can do with a database and imagination, and I am grateful to Mike for sharing this interesting project.

End note
Mark Whitehorn has been writing the database column in PCW for about 15 years. Selected highlights are available in book form. Visit the Penguinsoft Consulting website for details.