5 ms·

How does one get this level of SQL proficiency? I am not sure how to go beyond my current level of understanding, and these queries don't make much sense to me.

by spraak 9y ago

How does one get this level of SQL proficiency? I am not sure how to go beyond my current level of understanding, and these queries don't make much sense to me. I don't often do more than some selects and maybe an inner join occasionally, so far.

matte_black 9y ago
The best way is through experience. Building an ERP system for a business is a great way to run into problems that need these kinds of solutions. You’ll be writing triggers, indexes, stored procedures, views, etc. And don’t use ORMs.
- jrumbut 9y ago
  Ha! I had the pre-ORM ERP experience myself, definitely a way to learn how to go beyond the basics. Probably not available to the newest programmers though. For Postgres and other feature rich RDBMS's one important thing is to take some time to learn what's possible. generate_series() is at the heart of a lot of good reporting tricks, as shown in the article.
AlisdairO 9y ago
(self plug) I made https://pgexercises.com/ https://pgexercises.com/ a while back to help people learn SQL in a learn-by-doing exercise format. You can run the queries in the browser, so it's easy to just to an exercise when you have a spare few minutes.
- barryhoodlum 9y ago
  I came across this when it was posted to HN a year or two ago and ended up finishing the whole thing. Thank you for making it.
  - AlisdairO 9y ago
    Pleasure - glad you found it useful :-)
- mercer 9y ago
  I've been working through your exercises and they helped a lot! Thanks so much for your work!
  - AlisdairO 9y ago
    Thank you for using it - it's great to know they've been helpful :-)
- starpilot 9y ago
  These are outstanding. Once I finish them in SQL, I plan to do them all with Python/Pandas.
  - AlisdairO 9y ago
    Hope you find them useful!
bitexploder 9y ago
It is kind of what you might expect for many of us. Tough problems pushed me to learn more. I got started before the time of ORMs being popular. It forced me to learn a lot of fundamentals. Find data sets and interesting things to do with them. Teach yourself about the database internals. RDBMS is rows and columns. Queries are planned. Planner tries to use most optimized query. Example, you join on a column that isn't indexed. To find every matching row DB has to scan entire table. Hello O(n^2). The details a query planner gives can be overwhelming. Learn every bit of it. Find ways to query data efficiently you would just deal with it app code. Learn subqueries. Learn aggregation. Learn aggregation with subqueries. But here is the real, fundamental truth about RDBMS and SQL. It is essentially a functional data environment, like a spreadsheet. Once you adapt to the functional mindset of SQL it can feel effortless to do things you might not have even thought possible with SQL.
- twic 9y ago
  > Example, you join on a column that isn't indexed. To find every matching row DB has to scan entire table. Hello O(n^2). It's only O(n^2) if your DBMS uses nested loops to do this join. If it uses a merge join it's O(n log n) (plus you get an order by for 'free'), and if it uses a hash join it's O(n). MySQL, last I checked, only supported various forms of nested loops, but first-class databases like PostgreSQL support the others.
  - bitexploder 9y ago
    Good point, and a good reason to learn how your DBMS of choice does common operations. Most query planners will explicitly tell you what yours is doing. I remember even back a long time ago MSSQL 7 had a nice GUI for its "query explain" and pgAdmin also gives a really nice graphical view of query plans, which really helps visualize when it is looping, doing hash lookups, etc. I suppose that would be part of the recommendation for learning: use a graphical query plan view if you can find a client that supports it :) http://www.postgresonline.com/journal/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html http://www.postgresonline.com/journal/archives/27-Reading-Pg... (example pictures if anyone reading this hasn't seen it before), it is pretty cool.
barrkel 9y ago
The queries in the article seem trivial to me, very basic introductory level stuff. I got to my current level of understanding by (a) writing 1000 queries as part of my college degree; we got to define our own schema, but then were given a list of query features to use, and several months to write them for our schema. This was perhaps 20% of the score for our databases class. I know of no better way than exercises of gradually increasing complexity. Even the school of experience is less complete and may have inconsistent difficulty jumps that leave you stuck. And (b) exhaustive examination of query plans, while having mental models of what btrees and hash maps are. To understand performance and adjust your query to encourage a different plan, you need to understand what's possible and develop an intuition about what the database will choose to do. I write fairly large SQL programs composed from successive queries in order to process large inputs (millions of rows) efficiently. It's cheaper to load the data into the database and compose operations over it than to loop in memory and iteratively perform queries.
- mercer 9y ago
  Would you say (b) is worth properly understanding even when performance is not likely to be a concern? As in, is it like knowing a lower level abstraction that improves the higher-level understanding (in a way that, for example, knowing javascript makes using jQuery a lot easier)?
  - zbentley 9y ago
    I'm not GP, but I would say it is essential to understand query plan debugging if you're doing, or plan on doing, database development at any scale--scale of data throughput, or scale of the size in code/developers of the application. It's essential in three ways: in principle, practically, and career-wise. It's essential in principle because RDBMSs are not magic, but are asked, as an application grows, to do increasingly magical things. Understanding how, and more importantly why the DB chooses to execute your query in a given way is absolutely key to understanding how to design data flows and distribution across the non-DB parts of your application. The people who designed your RDBMS are smarter than you; the query planner is (albeit limited) insight into how they applied their intelligence rather than just how you benefit from it. Understanding that databases are collections of variously-traversed data structures underneath, chosen carefully to balance multiple use cases, can lead you to better understanding of other code you write. It's essential practically because without consistent discipline and coordination (things that diminish, typically, as companies and codebases grow), queries and schemas tend to grow over time. Even outside of the complex reporting use cases, it's not uncommon to have really big/weird queries running on a very high utilization production database. Those queries interact with other stuff that's running in sneaky ways, causing everything from slowdowns to absolute deadlocks, and understanding exactly what is breaking them requires a thorough understanding of how the query plan works. "Add indexes and don't use the DB as a message queue" is excellent advice, and will get you very far, but past a certain size/cruft level, you need to go deeper. It's essential career-wise because every company with more than a handful of engineers has a database guy or gal, and that person is typically considered essential and worthy of lucrative reward--even in environments where other backend engineers are considered replaceable cost centers. This is usually not the DBA, but an application developer who really deeply understands how the queries from your code hit the database, how it behaves if more than one of them run at the same time, and how to fix it if it breaks. A DBA debugging an unfamiliar/new query has to start from closer to first principles: how is it broken, how to tweak it to fix it, how to isolate it, etc. The database guy starts from the other end: is the code that generated it sane, given the data access patterns it's requesting? The querying code is a solution to whatever problem its writer was trying to solve, but is it the right solution? The query planner and database statistics/activity views are this person's bread and butter.
dizzystar 9y ago
Build a 100+ table database. Try redoing the havok some ORM did on a solo project. Also, books are far better than any online tutorial. I'd suggest doing the database before reading the books, as that will show you all the mistakes fresh in your memory.

by spraak 9y ago

matte_black 9y ago

The best way is through experience. Building an ERP system for a business is a great way to run into problems that need these kinds of solutions. You’ll be writing triggers, indexes, stored procedures, views, etc. And don’t use ORMs.

jrumbut 9y ago
Ha! I had the pre-ORM ERP experience myself, definitely a way to learn how to go beyond the basics. Probably not available to the newest programmers though. For Postgres and other feature rich RDBMS's one important thing is to take some time to learn what's possible. generate_series() is at the heart of a lot of good reporting tricks, as shown in the article.

AlisdairO 9y ago

(self plug) I made https://pgexercises.com/ https://pgexercises.com/ a while back to help people learn SQL in a learn-by-doing exercise format. You can run the queries in the browser, so it's easy to just to an exercise when you have a spare few minutes.

barryhoodlum 9y ago
I came across this when it was posted to HN a year or two ago and ended up finishing the whole thing. Thank you for making it.
- AlisdairO 9y ago
  Pleasure - glad you found it useful :-)
mercer 9y ago
I've been working through your exercises and they helped a lot! Thanks so much for your work!
- AlisdairO 9y ago
  Thank you for using it - it's great to know they've been helpful :-)
starpilot 9y ago
These are outstanding. Once I finish them in SQL, I plan to do them all with Python/Pandas.
- AlisdairO 9y ago
  Hope you find them useful!

bitexploder 9y ago

It is kind of what you might expect for many of us. Tough problems pushed me to learn more. I got started before the time of ORMs being popular. It forced me to learn a lot of fundamentals. Find data sets and interesting things to do with them. Teach yourself about the database internals. RDBMS is rows and columns. Queries are planned. Planner tries to use most optimized query. Example, you join on a column that isn't indexed. To find every matching row DB has to scan entire table. Hello O(n^2). The details a query planner gives can be overwhelming. Learn every bit of it. Find ways to query data efficiently you would just deal with it app code. Learn subqueries. Learn aggregation. Learn aggregation with subqueries. But here is the real, fundamental truth about RDBMS and SQL. It is essentially a functional data environment, like a spreadsheet. Once you adapt to the functional mindset of SQL it can feel effortless to do things you might not have even thought possible with SQL.

twic 9y ago
> Example, you join on a column that isn't indexed. To find every matching row DB has to scan entire table. Hello O(n^2). It's only O(n^2) if your DBMS uses nested loops to do this join. If it uses a merge join it's O(n log n) (plus you get an order by for 'free'), and if it uses a hash join it's O(n). MySQL, last I checked, only supported various forms of nested loops, but first-class databases like PostgreSQL support the others.
- bitexploder 9y ago
  Good point, and a good reason to learn how your DBMS of choice does common operations. Most query planners will explicitly tell you what yours is doing. I remember even back a long time ago MSSQL 7 had a nice GUI for its "query explain" and pgAdmin also gives a really nice graphical view of query plans, which really helps visualize when it is looping, doing hash lookups, etc. I suppose that would be part of the recommendation for learning: use a graphical query plan view if you can find a client that supports it :) http://www.postgresonline.com/journal/archives/27-Reading-PgAdmin-Graphical-Explain-Plans.html http://www.postgresonline.com/journal/archives/27-Reading-Pg... (example pictures if anyone reading this hasn't seen it before), it is pretty cool.

barrkel 9y ago

The queries in the article seem trivial to me, very basic introductory level stuff. I got to my current level of understanding by (a) writing 1000 queries as part of my college degree; we got to define our own schema, but then were given a list of query features to use, and several months to write them for our schema. This was perhaps 20% of the score for our databases class. I know of no better way than exercises of gradually increasing complexity. Even the school of experience is less complete and may have inconsistent difficulty jumps that leave you stuck. And (b) exhaustive examination of query plans, while having mental models of what btrees and hash maps are. To understand performance and adjust your query to encourage a different plan, you need to understand what's possible and develop an intuition about what the database will choose to do. I write fairly large SQL programs composed from successive queries in order to process large inputs (millions of rows) efficiently. It's cheaper to load the data into the database and compose operations over it than to loop in memory and iteratively perform queries.

mercer 9y ago
Would you say (b) is worth properly understanding even when performance is not likely to be a concern? As in, is it like knowing a lower level abstraction that improves the higher-level understanding (in a way that, for example, knowing javascript makes using jQuery a lot easier)?
- zbentley 9y ago
  I'm not GP, but I would say it is essential to understand query plan debugging if you're doing, or plan on doing, database development at any scale--scale of data throughput, or scale of the size in code/developers of the application. It's essential in three ways: in principle, practically, and career-wise. It's essential in principle because RDBMSs are not magic, but are asked, as an application grows, to do increasingly magical things. Understanding how, and more importantly why the DB chooses to execute your query in a given way is absolutely key to understanding how to design data flows and distribution across the non-DB parts of your application. The people who designed your RDBMS are smarter than you; the query planner is (albeit limited) insight into how they applied their intelligence rather than just how you benefit from it. Understanding that databases are collections of variously-traversed data structures underneath, chosen carefully to balance multiple use cases, can lead you to better understanding of other code you write. It's essential practically because without consistent discipline and coordination (things that diminish, typically, as companies and codebases grow), queries and schemas tend to grow over time. Even outside of the complex reporting use cases, it's not uncommon to have really big/weird queries running on a very high utilization production database. Those queries interact with other stuff that's running in sneaky ways, causing everything from slowdowns to absolute deadlocks, and understanding exactly what is breaking them requires a thorough understanding of how the query plan works. "Add indexes and don't use the DB as a message queue" is excellent advice, and will get you very far, but past a certain size/cruft level, you need to go deeper. It's essential career-wise because every company with more than a handful of engineers has a database guy or gal, and that person is typically considered essential and worthy of lucrative reward--even in environments where other backend engineers are considered replaceable cost centers. This is usually not the DBA, but an application developer who really deeply understands how the queries from your code hit the database, how it behaves if more than one of them run at the same time, and how to fix it if it breaks. A DBA debugging an unfamiliar/new query has to start from closer to first principles: how is it broken, how to tweak it to fix it, how to isolate it, etc. The database guy starts from the other end: is the code that generated it sane, given the data access patterns it's requesting? The querying code is a solution to whatever problem its writer was trying to solve, but is it the right solution? The query planner and database statistics/activity views are this person's bread and butter.

dizzystar 9y ago

Build a 100+ table database. Try redoing the havok some ORM did on a solo project. Also, books are far better than any online tutorial. I'd suggest doing the database before reading the books, as that will show you all the mistakes fresh in your memory.