I've been an engineer for last 12 years and during my career I've been mentoring people and helping them learn SQL. I used different approaches but did not find any better approach from learning by [doing] writing SQL queries and solving tasks. Even if it was by leveraging the AdventureWorks database, and/or a real world production database.
So my question to people who just entering into any developer role and/or learning just SQL....
How do you learn SQL now, and what is the best approach for you personally?
Whenever I want to explain how one can do things in SQL I pull up a google/excel sheet and write down some sample data that resembles the problem.
When multiple tables are involved I just write the samples in two different locations within the same sheet.
After that I can just jot down the resulting table below (without writing the actual sheet commands) and maybe some instruction.
Makes all concepts very clear, for example when explaining n:m joins or window functions. Or just the general "table programming" concepts where you add columns rather than variables when you want something done.
Particularly well suited now in covid times when I have to explain things remotely.
SQL and sheets programming are just very closely related.
When I got hired as a SQL Developer with little experience. I got a rundown on my employers database and tables and then tried my best. Google and Stack was the best teacher and with each work assignment I learned something new or a better way to do what I did before. Often the SQL queries I wrote would require updates and I would put in better solutions.
There’s still a ton of stuff I don’t know and I made a lot of mistakes along the way , but I still feel like this was the best learning experience for me.
I never make it a task to know completely what a language could do. I just try to familiarize my self with the basics (syntax) it’s novelty features (the reason why it even exists as opposed to other solutions) and pray what I think the best solution for a given problem is doable via research (Google & Stackoverflow)
I worked as a BA and now work in BI so it's common for me to write long and complex queries. Most of them due to bad table structure but we can't do much so have to go around.
One job that I have never seen a company does well is the transalation of business requirements through programmers and database designers/admins. Each has its own agenda and you need someone who is excel in all three fields to make the connection smooth and lossless. I have yet to find someone who is.
I'd also like to add that if business teams (analysts) stop complaining about database qualities, it doesn't mean you have implemented the right fixes. It usually means that they are so desperate that they decide to just live with them.
Relations are another name for the tables, relations are not the joins between them.
The information about this is stored in the "Relationships Window"
The only reason I learned SQL is to perform SQL injections and hack shit..
Now I get to fix bugs in SQLite3 on a monthly basis.. I guess karma exists after all lol.
1. Performance is not usually as important for one-time scripts like these.
2. It gets you familiar with your database and the relationships between entities in your product.
3. It shows you how powerful SQL is. With a few hours writing a single script, I saved users many more hours that would've been spent entering it all manually.
The other benefit of this approach, it's easier for senior developers to review too. You can give feedback like, "this works fine for this use-case, but here's something you can do in situations where performance is a concern" and provide some useful tips in PR comments.
Postgres documentation: https://www.postgresql.org/docs/
Being forced to solve a challenging problem keeps me going regardless of what it is. I am not a huge fan of exercises without an end result.
When you say solving tasks without an end goal what do you mean? Like what would be the most blocker for you from practicing writing SQL queries?
(1) I was starting with Oracle, so I read through Oracle's Database Concepts (https://docs.oracle.com/cd/B19306_01/server.102/b14220/toc.h...). This contains unnecessarily deep technical detail (which I skimmed), but I learn more easily if I have a strong footing in the concepts so I don't feel disoriented. For example, it was helpful to understand transactions (section 4), undo (section 3), and the data dictionary (section 7; later made it easier to understanding the DDL / DML distinction), consistency (section 13). It also contains an overview of some important SQL concepts in section 24 such as cursors and the fact that there's an optimizer which figures out how to run your query.
(2) I read about database normalization. Understanding how to put things in first, second, and third normal forms was extremely helpful in reshaping how I think of data structures. Learning to use a database is not just about how to write a query. It's also about how to model data. (What does relational, as in relational database, mean anyway?) Even if you're not creating tables, understanding how they are probably organized helps you query them. And once you understand basic modeling stuff like normalization, you can move on to advanced stuff like denormalization. Another part of modeling is keys (natural vs. surrogate/synthetic keys, composite keys, foreign keys, etc.).
(3) I practiced writing SQL queries and learned all the joins, aggregate functions, analytic functions, etc.
Blurb from their homepage:
Welcome to PostgreSQL Exercises! This site was born when I noticed that there's a load of material out there to help people learn about SQL, but not a great deal to make it easy to learn by doing. PGExercises provides a series of questions and explanations built on a single, simple dataset
Thing is I want to cover the latest stable version of tools im using so i can take advantage of their latest features that might be absent on older ones and not covered by older third party content. The latest stable docs and changelogs are my best source for that.
I made https://interactivesql.com to help all those people who just start to learn databases, SQL, queries, and more.
I am compiling 120+ lessons now to cover everything from the basics (select, join, group by, to CTEs, Analytic Functions, performance, reporting, and more)
Hope this helps. Feel free to let me know what do you think about it.
Need to find that book again, but I think sql is something you need to drill.
I’ll take a look at some of the links in this thread, and I’m hoping one of them is just a giant bank of sql drills, and would be great if anyone knows of similar books/sites.
