August 2019 Newsletter Competition - Spot the SQL Mistakes

Wally Owl wants to extract information from this database of films:

Films database

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):

Query results

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):

SELECT

/* list out the film names */

f.Title AS 'Film name',

f.RunTimeMinutes AS [Film length],

-- show the director name

d.FullName Director,

'genre name

Genre.Genre,

FROM

Director AS d

INNER JOIN Film AS f ON d.DirectorID == f.DirectorID

JOIN Genre AS g ON f.GenreID == g.GenreID

ORDER BY

[Film name]

 

WHERE

-- must have won Oscars and ...

f.OscarWins > 0 and

-- ... title can't be longer than 4 characters

length('Film name') =< 4

Newsletter email

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!

This page has 0 threads Add post