BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 23 September 2019
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
August 2019 Newsletter Competition - Answers
Last month you were invited to find the 8 errors in Wally Owl's SQL query. Many thanks to the 18 people who entered (a couple of the answers are shown at the bottom of this blog). I wrote a quick VBA program in Outlook to read the senders' names into Excel, then used the RAND() function followed by Paste Special / Values and sorting to randomly sort the entries into winning order. The winner after all of this is ... Ian Lynch!
Ian was actually the 6th person on the list, but all of the others before him had made at least one mistake.
Below are the answers, and after that some non-errors which caught a few people out:
The errors in the query (see the table below for more details).
The errors are as follows:
|1||Comments can't begin with an apostrophe (Wally seems to have lapsed into Visual Basic).|
|2||Once you've referred to a table by its alias, you can't then use the original name (so this should be g.Genre).|
|3||The most common mistake in SQL - an extra comma at the end of a list.|
|4||To test equality in SQL you use a single = sign (this time Wally appears to have switched from SQL to C# or Java).|
|5||SFWGHO - the order of the keywords is wrong.|
|6||There's no such SQL function as length (len, sure).|
|7||If you've created an alias for a column (as here with Film Name) you can reference this in an ORDER BY clause but not in a WHERE clause.|
|8||The relational operator should read >=.|
Things which may be bad practice, but aren't actual errors include the following:
These aren't mistakes, as shown below.
Here's why these are acceptable:
|1||You can put comments /* like this */, although not many people do.|
|2||If a column name contains spaces, you can put it in either square brackets or (as here) quote marks.|
|3||You don't need the word AS when putting an alias (although it's good practice IMHO to include it).|
|4||You don't need to put INNER JOIN (although again I think doing so makes your query clearer).|
Finally, answers varied in their length. My favourite was this economical one from MT:
Not a word wasted (or indeed used!).
Whereas this one from ET was probably the neatest:
The product of a tidy mind?
Thanks to everyone who took part!