How to get better at indexing?
How to get better at joins?
How to get better in SQL optimization?<p>Must to know things about SQL and maybe relational databases?
Since you ased about indexing in particular, here is my site about indexing:<p><a href="https://use-the-index-luke.com/" rel="nofollow">https://use-the-index-luke.com/</a><p>You might also want to follow my other project:<p><a href="https://modern-sql.com/" rel="nofollow">https://modern-sql.com/</a>
Ask HN: What is the best online resource to learn advanced SQL? <i>(Jan 2017: 448 points, 97 comments)</i><p><a href="https://news.ycombinator.com/item?id=13417326" rel="nofollow">https://news.ycombinator.com/item?id=13417326</a>
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...<p>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.
Just by doing advanced exercises. For example take a look at the SQL homework for one of the CMU courses on databases:<p><a href="https://15445.courses.cs.cmu.edu/fall2018/homework1/" rel="nofollow">https://15445.courses.cs.cmu.edu/fall2018/homework1/</a><p>While doing the exercises you'll be doing some research and looking at question in stack overflow.
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...<p>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.<p>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.<p>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.<p>1. <a href="https://leetcode.com/problemset/database/" rel="nofollow">https://leetcode.com/problemset/database/</a>
I would reccomend this book:<p><a href="https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945" rel="nofollow">https://www.amazon.com/Art-SQL-Stephane-Faroult/dp/059600894...</a>
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]<p>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.<p>[0]: referral link: www.codewars.com/r/EbsMWQ
Learn relational algebra. Learn relational calculus. You'll probably want to read a book or take a class on DB theory; blog posts won't cut it.