Must to know things about SQL and maybe relational databases?
https://use-the-index-luke.com/
You might also want to follow my other project:
This is deeper SQL-fu that many of us typically encounter. Highly recommend.
http://tatiyants.com/pev/#/plans
(Postgres Explain Visualizer)
When dealing with query optimization in practice, it helps a lot since it enables a clear, convenient and synthetic view of an EXPLAIN (ANALYZE).
Just yesterday, it helped me improve a big analytic query from executing in 10 minutes to 5 seconds by just adding a small index.
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.
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.
https://15445.courses.cs.cmu.edu/fall2018/homework1/
While doing the exercises you'll be doing some research and looking at question in stack overflow.
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.
https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/059600894...
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.
[0]: referral link: www.codewars.com/r/EbsMWQ
