If the position you're filling is directly dependent on more-than-average SQL experience - creating a DB driver, an ORM, for ex. - then SQL-specific questions are applicable. But, by and large, this type of specific-knowledge testing is not very useful. I want to see a developer's general abilities at problem solving and the source code to back it up. If you have solved complex problems in C# - and can prove it - then you certainly as hell can solve complex problems in Go despite not having any experience there yet. Sure, if I'm trying to fill a Go position and someone has proof they're an excellent developer _and_ it's in Go then they'll get top consideration.
Being able to write SQL queries from memory has little correlation to a candidate's level of ability. Personally I consider myself a fairly strong developer and it hasn't been only until the last year that I can now write pretty complex joins from memory. And I've been developing for 20 years. Only because of a recent project and the volume of queries I had to write did my method change from using a graphical query writer to simply memorizing the syntax I need. Indeed, this very type of adaptation is something I look for in candidates.
If you can't handle these sorts of queries in a forgiving interview format, then by definition you are not a strong developer in SQL. That is not to say that you are not a strong developer in general; or that you couldn't handle a job where you had to interact with a SQL datastore; merely that the interviewer is not going to be able to talk SQL with you.
Since SQL is a declarative language, you have to use it very differently than procedural or functional languages. I've met many people who don't understand the difference and just write procedural code in their SQL queries (cursors, etc). The output may be correct, but performance on anything bigger than a toy dataset is terrible.
If your team builds massively parallel systems in Erlang, you need to make sure a candidate understands at least the basics of its process model and message passing. If your team build high-performance web apps, you need to make sure a candidate understands at least the basics of HTTP and the difference between client and server. For SQL, the same is true: they need to understand at least the basics of the relational model and declarative programming.
These are very simple, though. If you say that you know SQL (or have known SQL, but are a bit rusty) and you can't at least make a strong showing on these questions, you are lying.
This is not complex problem solving, this is problem solving. I'm not sure what memorization has to do with any of this. SQL is a language with only maybe 10-15 important words. It's not like trying to get around Paris without a phrasebook as a non-French speaker.
I've found that using graphical query writers (I only know of the one in access) and ORMs are generally harder than writing the SQL. ORMs are good for keeping code db agnostic, though.
It's called Query by Example and there are quite a few DB management GUIs that have it. I found it pretty intuitive for setting up joins by right-clicking join lines and setting the particular properties of that join. Of course now that I have committed all types of joins to memory it's far faster to write them simply by hand. But my lacking that memorization 10 months ago wasn't really insite into my ability (or lack thereof) as a developer.
Even the worst developers I have worked with could figure out SQL queries like this in an afternoon. If they study before the test, congratulations, we just hired a dud.
You made my point much more succinctly than I. Mainly, this type of candidate testing tells you very little (positively or negatively) whether they're strong developers. Consider an OS developer - the person may have never EVER been exposed to SQL beyond "select * from table" but is probably superior to any candidate that may ace this little SQL test. Mrs. OS developer can learn any amount of SQL syntax (and deep understanding of it as @joshyeager points out) within weeks of being hired.
I think you are assuming that the company is using SQL interview questions to test whether or not the candidate is a strong overall developer than a strong SQL developer. It seems to me that they are testing for a very specific skill and area.
Being able to write SQL queries from memory has little correlation to a candidate's level of ability. Personally I consider myself a fairly strong developer and it hasn't been only until the last year that I can now write pretty complex joins from memory. And I've been developing for 20 years. Only because of a recent project and the volume of queries I had to write did my method change from using a graphical query writer to simply memorizing the syntax I need. Indeed, this very type of adaptation is something I look for in candidates.