4 ms·

i love SQL but it was really hard to get started for two reasons: - it's hard to start tinkering unless you have a database to write queries against. most tuto

i love SQL but it was really hard to get started for two reasons: - it's hard to start tinkering unless you have a database to write queries against. most tutorials never really get past the most rudimentary hello-world-esque stuff because they're limited by the size and complexity of the example data. - imperative programming teaches you to be very precise and explicit about what exactly should happen, so it feels very weird to start working with something declarative where you can just trust the computer to figure it out and do things well automatically.

giancarlostoro 2y ago
Which is why I think we should always teach SQL basics with SQLite. You can just share the db file as-is. You can probably find dozens of GUIs and even web GUIs for SQLite that you could load a db into.
hnthrowaway0328 2y ago
It's not that hard. You can use any SQL tutorial site for practice. If you want real life data, find the stackoverflow data dump on archive.org and install a PostgreSQL instance to play with it. IMO the biggest issue of SQL is that it brings bread and butter for the BI/DA people, who are so close to the business that they usually don't care about code quality and such. It's not the fault of the language, but the fault of erroneous team structure.
pmarreck 2y ago
SQLite is good for this.
fifilura 2y ago
I use to say that you have learned SQL the moment you forgot everything about C/C++. Imperative programming is a strong habit to kick. You just have to let go. And once you have you will see things from a very different perspective. Also in other programming you'll start seeing the map/fold/groupby/dict patterns everywhere.
- parpfish 2y ago
  every once in a while i start thinking "it'd be really nice to build a declarative language to do this", but then I realize that: - that just means that I'm bored and would rather be writing out the logic to handle a more general/abstract representation of the problem - whenever I come across a DSL that somebody else made to "make a really simple declarative-like interface", i hate it and wish that they didn't do it
  - fifilura 2y ago
    Was your point that that goes for SQL? One nifty thing with SQL is that you get all the threading/remote processing for free without having to think about it. That can be pretty hairy to write yourself.
simonw 2y ago
I've been building SQL tutorials against my Datasette web UI for SQLite as a solution for getting people started without having to install anything: https://datasette.io/tutorials/learn-sql https://datasette.io/tutorials/learn-sql
- refset 2y ago
  Integrating live code editors within docs and tutorials is great. Another example of this I saw recently using SQLite (compiled to Wasm) in the browser: https://docs.sqlitecloud.io/docs/sqlite https://docs.sqlitecloud.io/docs/sqlite And if you ever want something similar for more general backend APIs (without relying on Wasm or the browser to run the software), https://codapi.org/ https://codapi.org/ looks very slick e.g. as demonstrated in https://antonz.org/sql-upsert/ https://antonz.org/sql-upsert/ - discussed on HN previously [0] Inspired by https://www.db-fiddle.com/ https://www.db-fiddle.com/ my colleagues ended up building a fairly bespoke setup for XTDB's docs (XTDB doesn't yet compile to Wasm) shortly before I came across Codapi, although our requirements were even more particular, e.g. see https://docs.xtdb.com/tutorials/financial-usecase/time-in-finance.html https://docs.xtdb.com/tutorials/financial-usecase/time-in-fi... - the backend here is https://github.com/xtdb/xt-fiddle https://github.com/xtdb/xt-fiddle which runs purely on top of Lambda Snapstart, and embedded within docs based on Astro's Starlight [1] and Web Components [0] https://news.ycombinator.com/item?id=38663717 https://news.ycombinator.com/item?id=38663717 [1] https://starlight.astro.build/ https://starlight.astro.build/
itishappy 2y ago
Agreed, but data is so easy to find online! My breakthrough was that Python comes with SQLite built-in. Pointing DuckDB at a CSV file looks even easier. https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html https://duckdb.org/docs/data/csv/overview.html https://duckdb.org/docs/data/csv/overview.html
- parpfish 2y ago
  sure, you can find data and spin up your own db but you still need a tutorial to a) explain what the data/tables are and b) guide you through some things to build. for most programming languages you can make some sort of little widget or itneractive program that lends itself well to learning via curious exploration. SQL does not lend itself to that because you don't really make something 'fun'. You just make more tables. People aren't going to naturally come up with the kind of boring-but-challenging things that you use SQL for like "come up with a daily aggregate of pageviews, but exclude users that had account status X in the previous 30 days OR if they have a special status set in this other table as of the date of the pageview. And make sure that days with 0 page views show up with 0 pageviews and are not just missing from the table". Also, it's very easy to make queries that complete and look correct but give wrong or incomplete results. You've not going to notice that from just running queries in self-directed practice.
  - edmundsauto 2y ago
    From a slightly different perspective, some people like these tasks :). That's part of the mindset of being a good data engineer, for example - I rarely have a problem coming up with my own set of queries to try out a new database or dataset. But I'm also not really interested in a lot of SWE-specific things. Maybe this just isn't something you really want to dive into. That's ok!
    - deleted 2y ago
      [deleted]
  - AlisdairO 2y ago
craigkerstiens 2y ago
This is very much why we built the Postgres playground, which has Postgres embedded in your browser with guided tutorials - https://www.crunchydata.com/developers/tutorials https://www.crunchydata.com/developers/tutorials
kccqzy 2y ago
I think subqueries are a useful feature that doesn't get taught often to beginners. Using subqueries it's much easier to think clearly and intuitively. Even imperative programmers who don't have a ton of functional programming experience still understand nested function calls `f(g(h()))`. With this you don't even have to know when the different parts of a SELECT are applied, and you don't even have to know things like HAVING.
- NortySpock 2y ago
  I have found Common Table Expressions -- which are slightly easier for the developer to re-order in the query -- to be even more readable than subqueries.
  - cwbriscoe 2y ago
    Yes, CTE's are a much more readable than subqueries IMO. I can't really explain it but it just makes queries easier to build and read to me.
    - fifilura 2y ago
      Two benefits: 1) The flow is clearer: take this, then transform this, then filter this 2) CTEs makes it easy to debug parts. You just change what you select from on the last line. Number 2 is the kicker IMO
      - disgruntledphd2 2y ago
        And this is why you should always, always have a `final` CTE and the last line should be `select * from final` so that you can easily change this. We use a LOT of SQL at $current_dayjob and I almost always end up adding this to the queries. Clearly I need to do a better job of evangelising my approach.
        deleted 2y ago
        [deleted]
therobots927 2y ago
Actually with R it’s very easy. Just install the sqldf package. Then you can read in a CSV or whatever format you’re using to R (even if you don’t know R, ChatGPT could probably write the code for you). Once the data is inside an R data frame you can query it directly with sqldf like: sqldf(“select count(*) from csv_dataframe”)
xarope 2y ago
1) sqlite is so easy to start with, I'm not sure what you mean to "hard to start tinkering"? 2) I'm not sure about your personal experience, but having worked with make files during C programming days, I think you get used to declarative state stuff pretty fast!
SoftTalker 2y ago
The imperative vs. declarative was also a toe-stubber for me at first. It ties in with SQL being set-oriented, which is not natural for a lot of imperative-trained developers. If you find yourself looping over a rows with a cursor you are probably doing it wrong, or at least suboptimally.

giancarlostoro 2y ago

Which is why I think we should always teach SQL basics with SQLite. You can just share the db file as-is. You can probably find dozens of GUIs and even web GUIs for SQLite that you could load a db into.

hnthrowaway0328 2y ago

It's not that hard. You can use any SQL tutorial site for practice. If you want real life data, find the stackoverflow data dump on archive.org and install a PostgreSQL instance to play with it. IMO the biggest issue of SQL is that it brings bread and butter for the BI/DA people, who are so close to the business that they usually don't care about code quality and such. It's not the fault of the language, but the fault of erroneous team structure.

pmarreck 2y ago

SQLite is good for this.

fifilura 2y ago

I use to say that you have learned SQL the moment you forgot everything about C/C++. Imperative programming is a strong habit to kick. You just have to let go. And once you have you will see things from a very different perspective. Also in other programming you'll start seeing the map/fold/groupby/dict patterns everywhere.

parpfish 2y ago
every once in a while i start thinking "it'd be really nice to build a declarative language to do this", but then I realize that: - that just means that I'm bored and would rather be writing out the logic to handle a more general/abstract representation of the problem - whenever I come across a DSL that somebody else made to "make a really simple declarative-like interface", i hate it and wish that they didn't do it
- fifilura 2y ago
  Was your point that that goes for SQL? One nifty thing with SQL is that you get all the threading/remote processing for free without having to think about it. That can be pretty hairy to write yourself.

simonw 2y ago

I've been building SQL tutorials against my Datasette web UI for SQLite as a solution for getting people started without having to install anything: https://datasette.io/tutorials/learn-sql https://datasette.io/tutorials/learn-sql

refset 2y ago
Integrating live code editors within docs and tutorials is great. Another example of this I saw recently using SQLite (compiled to Wasm) in the browser: https://docs.sqlitecloud.io/docs/sqlite https://docs.sqlitecloud.io/docs/sqlite And if you ever want something similar for more general backend APIs (without relying on Wasm or the browser to run the software), https://codapi.org/ https://codapi.org/ looks very slick e.g. as demonstrated in https://antonz.org/sql-upsert/ https://antonz.org/sql-upsert/ - discussed on HN previously [0] Inspired by https://www.db-fiddle.com/ https://www.db-fiddle.com/ my colleagues ended up building a fairly bespoke setup for XTDB's docs (XTDB doesn't yet compile to Wasm) shortly before I came across Codapi, although our requirements were even more particular, e.g. see https://docs.xtdb.com/tutorials/financial-usecase/time-in-finance.html https://docs.xtdb.com/tutorials/financial-usecase/time-in-fi... - the backend here is https://github.com/xtdb/xt-fiddle https://github.com/xtdb/xt-fiddle which runs purely on top of Lambda Snapstart, and embedded within docs based on Astro's Starlight [1] and Web Components [0] https://news.ycombinator.com/item?id=38663717 https://news.ycombinator.com/item?id=38663717 [1] https://starlight.astro.build/ https://starlight.astro.build/

itishappy 2y ago

Agreed, but data is so easy to find online! My breakthrough was that Python comes with SQLite built-in. Pointing DuckDB at a CSV file looks even easier. https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html https://duckdb.org/docs/data/csv/overview.html https://duckdb.org/docs/data/csv/overview.html

parpfish 2y ago
sure, you can find data and spin up your own db but you still need a tutorial to a) explain what the data/tables are and b) guide you through some things to build. for most programming languages you can make some sort of little widget or itneractive program that lends itself well to learning via curious exploration. SQL does not lend itself to that because you don't really make something 'fun'. You just make more tables. People aren't going to naturally come up with the kind of boring-but-challenging things that you use SQL for like "come up with a daily aggregate of pageviews, but exclude users that had account status X in the previous 30 days OR if they have a special status set in this other table as of the date of the pageview. And make sure that days with 0 page views show up with 0 pageviews and are not just missing from the table". Also, it's very easy to make queries that complete and look correct but give wrong or incomplete results. You've not going to notice that from just running queries in self-directed practice.
- edmundsauto 2y ago
  From a slightly different perspective, some people like these tasks :). That's part of the mindset of being a good data engineer, for example - I rarely have a problem coming up with my own set of queries to try out a new database or dataset. But I'm also not really interested in a lot of SWE-specific things. Maybe this just isn't something you really want to dive into. That's ok!
  - deleted 2y ago
    [deleted]
- AlisdairO 2y ago

craigkerstiens 2y ago

This is very much why we built the Postgres playground, which has Postgres embedded in your browser with guided tutorials - https://www.crunchydata.com/developers/tutorials https://www.crunchydata.com/developers/tutorials

kccqzy 2y ago

I think subqueries are a useful feature that doesn't get taught often to beginners. Using subqueries it's much easier to think clearly and intuitively. Even imperative programmers who don't have a ton of functional programming experience still understand nested function calls `f(g(h()))`. With this you don't even have to know when the different parts of a SELECT are applied, and you don't even have to know things like HAVING.

NortySpock 2y ago
I have found Common Table Expressions -- which are slightly easier for the developer to re-order in the query -- to be even more readable than subqueries.
- cwbriscoe 2y ago
  Yes, CTE's are a much more readable than subqueries IMO. I can't really explain it but it just makes queries easier to build and read to me.
  - fifilura 2y ago
    Two benefits: 1) The flow is clearer: take this, then transform this, then filter this 2) CTEs makes it easy to debug parts. You just change what you select from on the last line. Number 2 is the kicker IMO
    - disgruntledphd2 2y ago
      And this is why you should always, always have a `final` CTE and the last line should be `select * from final` so that you can easily change this. We use a LOT of SQL at $current_dayjob and I almost always end up adding this to the queries. Clearly I need to do a better job of evangelising my approach.
      - deleted 2y ago
        [deleted]

therobots927 2y ago

Actually with R it’s very easy. Just install the sqldf package. Then you can read in a CSV or whatever format you’re using to R (even if you don’t know R, ChatGPT could probably write the code for you). Once the data is inside an R data frame you can query it directly with sqldf like: sqldf(“select count(*) from csv_dataframe”)

xarope 2y ago

1) sqlite is so easy to start with, I'm not sure what you mean to "hard to start tinkering"? 2) I'm not sure about your personal experience, but having worked with make files during C programming days, I think you get used to declarative state stuff pretty fast!

SoftTalker 2y ago

The imperative vs. declarative was also a toe-stubber for me at first. It ties in with SQL being set-oriented, which is not natural for a lot of imperative-trained developers. If you find yourself looping over a rows with a cursor you are probably doing it wrong, or at least suboptimally.