Hacker News Reader

Comment by parpfish | Hacker News Reader

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.

View on HN

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

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

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

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/ looks very slick e.g. as demonstrated in https://antonz.org/sql-upsert/ - discussed on HN previously [0]

Inspired by 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-fi... - the backend here is 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

[1] https://starlight.astro.build/

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://duckdb.org/docs/data/csv/overview.html

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.

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!

self-plug, but you could give https://pgexercises.com/ a try. No need to spin up your own DB etc.

Also, if you don't know any SQL yet what are the chances you'd feel comfortable spinning up your own db?

Forget online tutorials, you want to pick up thick red Wrox SQL books from your local used bookstore or co-op. Nobody selling you anything, densely packed, comprehensive info.

SQL has changed very little over the past 25 years, and while there are some syntax variations across different databases, the most important 80+% is universal. Even if the book is for SQL Server the vast majority will work on Postgres, SQLite, etc.

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.

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.

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.

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.

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

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.

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”)

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.

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

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.

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.

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.

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!

SQLite is good for this.

Reply on news.ycombinator.com