Those kinds of demos always show the simple queries that connect at most two things. Those are trivial to query by hand as well though. I'd be interested in seeing how it handles some self-joins, multiple exists subqueries, date operations, etc. - all in one query.
It works pretty well. AI can generated SQL with Common table expressions, window functions, and many kinds of joins.
If you ask it to do a query that it doesn't actually know how to do does it say so or just give the wrong answer?
Answers from AI could be wrong sometimes(close to 20%). The better database schema and purpose provide for AI, the better results you can get. This can help the AI better understand what you are asking it to do and improve the accuracy.