August 2019 Newsletter Competition - Spot the SQL Mistakes
Wally Owl wants to extract information from this database of films:
The database contains 3 tables linked by relationships, showing for each film its director and its genre.
Specifically, Wally wants to list the film name, film length, director name and genre name for all films which have either won Oscars or whose titles have at most 4 characters. Here's what Wally's query should show (for the Wise Owl Movies database):
You absolutely don't need to have access to the database to enter the competition.
Can you advise Wally of the 8 syntax mistakes he's made in the following query, which will stop it doing what he wants it to do? You can assume that all of the referenced database table and field names are correct. Watch out for red herrings (things which look like errors, but aren't):
/* list out the film names */
f.Title AS 'Film name',
f.RunTimeMinutes AS [Film length],
-- show the director name
Director AS d
INNER JOIN Film AS f ON d.DirectorID == f.DirectorID
JOIN Genre AS g ON f.GenreID == g.GenreID
-- must have won Oscars and ...
f.OscarWins > 0 and
-- ... title can't be longer than 4 characters
length('Film name') =< 4
Please send your answers in any intelligible form to this email address. The first correct answer drawn out of the randomised electronic Wise Owl hat will win a £50 Amazon voucher.
Prizes will be awarded shortly before the next monthly newsletter is sent out, and the result (and correct answers) announced in this newsletter. The editor's (my) decision is final!