Ask HN: Junior SQL developers – How do you learn SQL?
I have a question to junior software developers.....
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? I maintain the most popular SQLite3 library for a common programming language (2 million downloads per month). 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. It is karma indeed =) https://sqlbolt.com/ and then https://sqlzoo.net/ were great places for me to start. Thanks for sharing these resources. Can I ask you what was your initial context or environment that made you learn SQL first? I mean what did encourage you to go and look for a solution? +1 to SQLBolt, which is a nice quick introduction you can do in an hour or two. The automatic feedback of execute-as-you-type is great. 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 I didn't really learn SQL until being given small tasks at work that involved some basic scripting. Requests for populating tables with new data so admins wouldn't have to manually input it from the web UI was a common one. That in particular was helpful because: 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. Totally agree...these are some nice ways to learn and practice. So to make sure I follow your initial need back then what was your main goal when you were solving such tasks as writing a script to automate data entry? Like what was the main and most important goal back then? Not so much about how I learned but how I teach. 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. Yep...a plain spreadsheet or tables do the trick. It helps get away from the frustration of not knowing SQL and helps think just in table terms. This is exactly how I teach SQL to my students. I had a bit of experience my freshmen year of college by working on a personal project that I abandoned. 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. Thanks for sharing this. You are saying that there is a ton of stuff to learn....how do you solve this problem now? You just learn as you go. For something like SQL, unless they’re doing something doing a query that’s never been done before , the majority of one’s issues is a Google search away of a function or thing they didn’t know they could do before. 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) CMU database youtube channel: https://youtube.com/c/CMUDatabaseGroup 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. The youtube channel looks very advanced. Definitely need to go over it in detail. 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? This is about how I learned it many years back, but it worked well for me. (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. Is there anything (any context, environment, or mindset) that could stop you from practicing writing SQL and learning the language? T-sql with pencil and paper. Visualize the problem with tables and relationships (boxes and lines with crows feet). Going through normal forms may be valuable but I like to tell them to just jump to entities and relationships. Once they can draw a “map” of their dB, and it’s usually much simpler than they first expected, then code it out on the nearest computer box. So you are talking about the db structure itself. How would you approach the SELECT part? All those nested sub-queries, CTEs, and complex EXISTS cases? There is a book on ANSI SQL that I've used: https://www.amazon.com/SQL-Complete-Reference-James-Groff/dp... I think I had some similar book at my desk back then... When you were just starting and found this book what was the most negative experience you've had during the learning? Make them read ask tom questions and answers. It really helped me understand real world cases. I just learned the basics in sqlzoo and the rest on job, should be quick. The problem with learning is that students have no idea about real world requirements so they can only go for very generetic ones (average revenue last week). The key, IMO, is to connect with real world business requirements and start writing queries. 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. This is interesting. Could you elaborate on the real world requirements please? What do you mean? In my case the database is designed in a way that analysts have to use many tricks (nested windows functions for example) to get what they need. This technically can be fixed by a re-design of the whole database but in reality it's not practical. 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. Understood. Let's imagine you found such real-world tasks and trying to solve them. You write SQL queries, play around to get dome results but now you need to confirm your solution is right. And given you are playing around on some website that gives you sandbox and interactive SQL tasks — I am not sure there is a way to confirm whether a solution you provided is right or wrong without involving a person (a mentor) to take a look. Reading official docs (be it postgresql, clickhouse, elasticsearch, redis), a lot of googling and stackoverflow, reading blogs and watching presentations on latest developments (like Altinity does for ClickHouse). 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. Thanks to everyone for all comments here. 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. In college my prof had this book she made with maybe a thousand sql drills with answers. I never went to the class and just drilled all the problems and somehow got good at sql around that time. 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. I leveled up by helping people on #postgresql (freenode) if I wasn't getting help, myself. I still think highly of the practice. Is there any certain way for you to do the practice in SQL? I'm not sure I understand the question. PostgreSQL is a SQL database. You could potentially level up on the support questions posed to any forum but I think #postgresql is one of the best communities around. Momentum doesn't happen overnight. I'm not a junior, but my best learning came from real examples in my first role. Then when I had some experience, researching advanced functions such as window functions and CTEs, AKA the result of answering "why does this query take ten minutes to run and how do I optimize it?" Understood. When you were doing research for those tasks in the past what was your main goal except to solve the task itself? Learning by doing is the best. It doesn't matter if some other resource is used, if I'm not getting at least one piece of work per month requiring me to meaningfully use it, then I'm not going to get/stay good at it. I learned by doing queries in Microsoft Access 2000, then looking at the query in "SQL View". The nice thing about Access is that it managed table relations for you, so master/detail tables are almost trivial. Bit of a nitpick. Relations are another name for the tables, relations are not the joins between them. If you have a master table with detail records, access just handles it, you don't have to do any SQL yourself to be able to just have a form that accesses them and lets you do all the CRUD operations. The information about this is stored in the "Relationships Window" Yes I'm familiar with it. What I'm saying is that : relation = table.
join = relationship. So I have been learning sql since the past month and I have been using the course on udemy called SQL bootcamp by Colt Steele it’s pretty good and the instructor explains stuff in an easy and detailed manner I found hacking on Wordpress was a great way to learn about joins