5 ms·

I love Django -- but what it does is mostly magic to me. That being said... I'm fairly competent in being effective in Django -- although not for advanced and

I love Django -- but what it does is mostly magic to me. That being said... I'm fairly competent in being effective in Django -- although not for advanced and efficient querying. I want to get strong in SQL -- where/how do I start?

wesd 9y ago
Start by having a strong understanding the various joins first: http://www.sql-join.com/sql-join-types/ http://www.sql-join.com/sql-join-types/
NumberCruncher 9y ago
I used w3schools for learning webdev related things but their SQL tutorial seems to be good as well. [1] https://www.w3schools.com/sql/default.asp https://www.w3schools.com/sql/default.asp
walshemj 9y ago
If you can get an employer to pay for it the Into course that Oracle does is fairly good it certainly got me up to speed to be able to work on an oracle project.
AlisdairO 9y ago
[self plug] Take a look at https://pgexercises.com/ https://pgexercises.com/ . It's a learn-by-doing set of SQL exercises. Focused on Postgres, but the large majority is standard SQL.
3131s 9y ago
Write raw SQL queries. My feeling on the Django ORM is that it's useful for defining models and managing migrations, but I prefer to write raw SQL for all queries. The PostgreSQL documentation is really good too.
- collyw 9y ago
  The ORM takes a whole lot of pain out of updates as well. Filtering also work fairly nicely for simple stuff, as putting conditions in a dictionary and passing it to filter(kwargs) is a lot nicer than messing about with SQL strings. I agree that the ORM is limited if you want moderately complex queries though.
- Daishiman 9y ago
  > but I prefer to write raw SQL for all queries. If you're doing this you're missing out on the biggest advantage of the ORM, which is that it allows you to compose queries. Using the Q objects, query expressions, and custom Queryset objects, you can filter objects by pretty much every imaginable criteria out there. This would be horrendously difficult and error-prone writing plain SQL. The Django ORM, despite having limitations if you want to run analytics (and that's really a use case for with plain SQL excels), generally writes out exactly the same SQL I'd write by hand.
  - 3131s 9y ago
    > This would be horrendously difficult and error-prone writing plain SQL. I do it all the time :) I love looking at long SQL queries that are formatted nicely and use good naming conventions. It's also part of a more general philosophy I have of reducing dependencies whenever possible. With Django it makes sense to use the ORM wherever it suits you, since it's already built in, but there are times when you need to know raw SQL anyway (e.g. try populating a large database without COPY, only using the Django ORM).
- ubernostrum 9y ago
  Meh. My feeling is that Django and other web frameworks are too magical -- I always worry they might be doing something inefficient. That's why I write all my web applications in hand-tuned assembly. It even has reusable abstractions for when I need them, and nice hand-formatted assembly is super readable!
  - 3131s 9y ago
    You're seriously comparing SQL to assembly? There's a point where it doesn't make sense to add another layer, another dependency, etc.
    - ubernostrum 9y ago
      In both the "never use an ORM" and "always use assembly" cases, the argument is based entirely on having full control of what's going on and not trusting any intermediate layer to get it right or eke out every last femtosecond worth of performance. So it's a perfectly fair comparison.
collyw 9y ago
Practice would be my advice. First thing to do would be to start printing the queries generated by the ORM to see what it is doing (though it produces some fairly verbose SQL, but its usually easy enough to understand). Then when you are asked to get some one off numbers out of the database try doing it in SQL. In one query. Its usually possible but takes a different way of thinking - in sets. Build up queries gradually. Start with the main table. Join in the next table and see the results. Add some conditions in your where clause or join conditions. See what the result is and how it changes. As for thinking in sets, that is easily said but difficult to translate into words. I guess describing the set of data that you want back from the database is a good start. My previous team leader would always describe the results in terms of "if x then y" (imperative thinking). When you are find yourself doing that, instead try to describe the data without the "if" statements and instead describe it as "the set where condition x and y are met". That will get you halfway there. Once you start thinking like that you will start to see the beauty in the relational model. I was a good few years into my career before I started thinking this way - now I try to do as much work in the database as possible - it avoids a whole categories of bugs, usually keeps your code shorter and is one of the easiest ways to improve performance.
combatentropy 9y ago
> I want to get strong in SQL -- where/how do I start? 1. I suggest the PostgreSQL documentation itself (https://www.postgresql.org/docs/current/static/ https://www.postgresql.org/docs/current/static/). It's well-written, but still wordier than need be, and if you try to read it from start to finish, you will probably die. However, the beginning chapters are overviews of SQL. Once you feel in the mud, you are probably in very specific, technical chapters. You can just skim these, jump to ones that interest you at the moment, etc. 2. There also a separate wiki (https://wiki.postgresql.org/wiki/Main_Page https://wiki.postgresql.org/wiki/Main_Page). I haven't gone there much, but it is a nice complement. It has practical summaries of things that the main documentation spins out of control (like setting up replication). It also has some examples of how to to do really advanced things in SQL. 3. A well-reviewed, short book. The first book I read was SQL Demystified. It's a short and easy intro to SQL in general. Find something like that. I have tried hard to find good books on SQL, but most are huge tomes that will crush your soul. 4. Practice. For the past decade at my job I have been forced to learn complex SQL for various business reports, across a variety of tables. And I still feel like an intermediate.

wesd 9y ago

Start by having a strong understanding the various joins first: http://www.sql-join.com/sql-join-types/ http://www.sql-join.com/sql-join-types/

NumberCruncher 9y ago

I used w3schools for learning webdev related things but their SQL tutorial seems to be good as well. [1] https://www.w3schools.com/sql/default.asp https://www.w3schools.com/sql/default.asp

walshemj 9y ago

If you can get an employer to pay for it the Into course that Oracle does is fairly good it certainly got me up to speed to be able to work on an oracle project.

AlisdairO 9y ago

[self plug] Take a look at https://pgexercises.com/ https://pgexercises.com/ . It's a learn-by-doing set of SQL exercises. Focused on Postgres, but the large majority is standard SQL.

3131s 9y ago

Write raw SQL queries. My feeling on the Django ORM is that it's useful for defining models and managing migrations, but I prefer to write raw SQL for all queries. The PostgreSQL documentation is really good too.

collyw 9y ago
The ORM takes a whole lot of pain out of updates as well. Filtering also work fairly nicely for simple stuff, as putting conditions in a dictionary and passing it to filter(kwargs) is a lot nicer than messing about with SQL strings. I agree that the ORM is limited if you want moderately complex queries though.
Daishiman 9y ago
> but I prefer to write raw SQL for all queries. If you're doing this you're missing out on the biggest advantage of the ORM, which is that it allows you to compose queries. Using the Q objects, query expressions, and custom Queryset objects, you can filter objects by pretty much every imaginable criteria out there. This would be horrendously difficult and error-prone writing plain SQL. The Django ORM, despite having limitations if you want to run analytics (and that's really a use case for with plain SQL excels), generally writes out exactly the same SQL I'd write by hand.
- 3131s 9y ago
  > This would be horrendously difficult and error-prone writing plain SQL. I do it all the time :) I love looking at long SQL queries that are formatted nicely and use good naming conventions. It's also part of a more general philosophy I have of reducing dependencies whenever possible. With Django it makes sense to use the ORM wherever it suits you, since it's already built in, but there are times when you need to know raw SQL anyway (e.g. try populating a large database without COPY, only using the Django ORM).
ubernostrum 9y ago
Meh. My feeling is that Django and other web frameworks are too magical -- I always worry they might be doing something inefficient. That's why I write all my web applications in hand-tuned assembly. It even has reusable abstractions for when I need them, and nice hand-formatted assembly is super readable!
- 3131s 9y ago
  You're seriously comparing SQL to assembly? There's a point where it doesn't make sense to add another layer, another dependency, etc.
  - ubernostrum 9y ago
    In both the "never use an ORM" and "always use assembly" cases, the argument is based entirely on having full control of what's going on and not trusting any intermediate layer to get it right or eke out every last femtosecond worth of performance. So it's a perfectly fair comparison.

collyw 9y ago

Practice would be my advice. First thing to do would be to start printing the queries generated by the ORM to see what it is doing (though it produces some fairly verbose SQL, but its usually easy enough to understand). Then when you are asked to get some one off numbers out of the database try doing it in SQL. In one query. Its usually possible but takes a different way of thinking - in sets. Build up queries gradually. Start with the main table. Join in the next table and see the results. Add some conditions in your where clause or join conditions. See what the result is and how it changes. As for thinking in sets, that is easily said but difficult to translate into words. I guess describing the set of data that you want back from the database is a good start. My previous team leader would always describe the results in terms of "if x then y" (imperative thinking). When you are find yourself doing that, instead try to describe the data without the "if" statements and instead describe it as "the set where condition x and y are met". That will get you halfway there. Once you start thinking like that you will start to see the beauty in the relational model. I was a good few years into my career before I started thinking this way - now I try to do as much work in the database as possible - it avoids a whole categories of bugs, usually keeps your code shorter and is one of the easiest ways to improve performance.

combatentropy 9y ago

> I want to get strong in SQL -- where/how do I start? 1. I suggest the PostgreSQL documentation itself (https://www.postgresql.org/docs/current/static/ https://www.postgresql.org/docs/current/static/). It's well-written, but still wordier than need be, and if you try to read it from start to finish, you will probably die. However, the beginning chapters are overviews of SQL. Once you feel in the mud, you are probably in very specific, technical chapters. You can just skim these, jump to ones that interest you at the moment, etc. 2. There also a separate wiki (https://wiki.postgresql.org/wiki/Main_Page https://wiki.postgresql.org/wiki/Main_Page). I haven't gone there much, but it is a nice complement. It has practical summaries of things that the main documentation spins out of control (like setting up replication). It also has some examples of how to to do really advanced things in SQL. 3. A well-reviewed, short book. The first book I read was SQL Demystified. It's a short and easy intro to SQL in general. Find something like that. I have tried hard to find good books on SQL, but most are huge tomes that will crush your soul. 4. Practice. For the past decade at my job I have been forced to learn complex SQL for various business reports, across a variety of tables. And I still feel like an intermediate.