One more vote for use-the-index-Luke. Clear, concise and comprehensive - I used this quite a bit when studying for interviews or optimizing database performance. Great resource!
His book (SQL Performance Explained) is also very good. The book is not too long and not too dense. I'd buy it again (in fact, I probably will, because I left my copy with a former employer because they paid for it).
I love use the index luke. Some of the best query advice I can get when I have to get something done faster.
Personally, the most important part of getting advanced with SQL is being able to vizualize the DB in your mind and how the query should look. Also, sometimes bad design is at fault than a query. If the design is too complicated or wrong a query is gonna be slow in relation to the amount of data it has to go over.
Search for a copy of the DB2 SQL Cookbook. Lots of good explanations and examples. It's free and no longer updated as I guess it is just too darn useful...
One of the problems with advanced SQL is you get into how the vendors implement advanced features.
In Oracle you will get familiar with ORA-xxx and coding around those.
DB2 throws different error messages and so does SQL Server. I have had to rewrite SQL going from one implementation to another because of these differences.
What worked well for me was getting a job in a small industrial business with a lot of IOT sensors. This brought a lot of SQL queries to optimize...
Using tools like MySQL WorkBench or dBeaver is essential to try queries in a friendly interface (tools that show time spent on queries & better tables than command line). Ideally, you should also search for database normalization.
If you don't have a database to practice with, you can analyze a small business need (ex.: managing employee salary, address & tasks), then create the schema. Then, you should generate tons of fake data to have a substantial time difference when optimizing queries. Try selecting all employees and their tasks, with different sorts. Try finding all employees with an address that starts with 4. Find all employees with a "i" in their name and where the task name is "plumber", etc.
Also, read about rails/laravel migrations, as they are the best methods I know to manage database versions.
I've found the leetcode[1] database problems have helped me quite a lot - especially to see different ways of solving problems and how to do things in SQL that you didn't know you could.
Start generating and reviewing explain plans for your queries. There's a ton of information available about how the engine is implementing your queries, and learning how to think like the database engine will really help you level-up the way you think about SQL.
For SQL optimization, learn to use window functions and/or Common Table Expressions if available in your variant of SQL. For joins, others have mentioned EXPLAIN: you can use that command to see the "query plan" for any query, which is sort of the equivalent of assembly code. You should learn which of your SQL variant's "join algorithms" at the query plan level are the most performant, and aim to use those types of joins in your queries.
If you'd like to gamify the process, I recommend CodeWars[0]
They give you challenges that progressively get harder. You can choose from many different languages but SQL is one of them. I'm not affiliated with them, but I used them for a few months to get incredibly good with Ruby.
https://use-the-index-luke.com/
You might also want to follow my other project:
https://modern-sql.com/