7 ms·

Piggybacking.. What's a good resource to learn SQL from scratch? Maybe out of ignorance, but in all the years I've programmed I've never had a situation when I

by geokon 5y ago

Piggybacking.. What's a good resource to learn SQL from scratch? Maybe out of ignorance, but in all the years I've programmed I've never had a situation when I said to myself "I need a database"! I either have data in maps/vectors in memory or if I need tables I'll use some table datastructure (like in R/Matlab). So reading a CSV file into a SQL table seems a bit weird - though maybe some people have multi-gig CSV files? I guess at a certain size (when it can't fit in RAM?) you gotta turn to SQL - or maybe I should be using SQL for smaller/simpler problems as well - but I don't really know where to start.

wenc 5y ago
Jennifer Widom’s Databases course is great https://online.stanford.edu/courses/soe-ydatabases-databases https://online.stanford.edu/courses/soe-ydatabases-databases Otherwise SQL is best learned by solving problems and googling when you’re stuck. Most developers only need a few SQL constructs like SELECT, WHERE, WITH, JOIN, INSERT, CREATE and GROUP BY. Most devs learn and forget because they don’t use SQL very often. It’s too bad because the relational data structure (or more generally the table) is a super useful abstraction and often much more powerful than lists and dicts. Data analysts need a few more like OVER (PARTITION BY) and maybe PIVOT/UNPIVOT. Data engineers of course need many more specifically those related to database object manipulation, and stuff around performance like INDEX. Really advanced SQL experts do stuff like this. https://modern-sql.com/ https://modern-sql.com/
- geokon 5y ago
  Oh the course starts today.. Is there way to view the lectures without enrolling? Just more generally, do you have some mental model when you move from using something like a dataframe to using SQL? For instance people working in R rarely talk about using an SQL database - even when they have huge datasets. And I guess I don't really see when you should be thinking about making the switch over. The only thing I see distinct about databases is it's always hand in hand with web-tech - and it more focused on mutability (which if you are doing some stats your data is usually immutable)
  - wenc 5y ago
    I’m not aware of other ways to get at the material though I’m sure Widom’s website at Stanford might have some. It’s not just a size thing but also a concurrency thing. Data frames are like tiny local databases. SQL databases are like central databases that many parties read and write from. There’s no switchover point as such. A common use case is to use SQL to query a subset of data from a central database, and that subset becomes a local data frame that an R user can work off of. Data frames and relational databases are very similar. When you’re using dplyr many of the operations conceptually map 1-to-1 to SQL. SQL is just a data frame manipulation DSL for databases. It’s all about filtering, aggregation and projections. Also not true about mutability and web tech. Most SQL operations do not mutate. Also SQL is more associate with data analytics, data engineering and report creation from data warehouses than web tech. The web tech use case came about because of PHP/MySQL and Django etc. but there SQL is usually used as an API language for the underlying data store rather than the full query engine that it is.
    - geokon 5y ago
      Right! the dplyr functionality seems to match 1-to-1 to what a cursory look at SQL told me - hence my confusion as to why they reinvented the wheel instead of using an industry standard The async table access is an interesting angle - assuming you're doing mutations that probably places some constraints. But in the case of in-app databasese my SQLite that's generally not a concern. From what you've said, at the end of the day, it sounds like the people using tables/dataframes are just too lazy to setup and use a SQL? I don't really see any distinction btwn the two domains then
      - wenc 5y ago
        I think it's more of a case of preference and ergonomics. For local dataframes, it's still more ergonomic to use a language's own constructs than to adopt a totally separate DSL for data manipulation. SQL is a different "language" which has its own cognitive overheads. So things like dplyr, pandas, and LINQ (in C#) were invented as language-native ways to manipulate tabular data structures without the overhead of context-switching [1]. After all, learning SQL and being good at it on top of mastering one's own programming language takes something extra. That said, it's possible and indeed sometimes preferable to use pure SQL for local data tables. I use duckdb to do complex manipulations on dataframes in Python because it's a lot faster than Pandas (due to some columnar optimizations) and because I can express certain things more succinctly in SQL than in Pandas (it's true -- I appreciate Pandas for the achievements that it is, but its syntax can be verbose with all its .apply() and df[df[col=="abc"]] incantations). Using SQL via sqlite to manipulate local tables confer similar benefits. [1] To be fair, pandas, dplyr and LINQ -- by virtue of being built into their respective languages -- can do things that SQL can't (or can't do easily) because it's not constrained by some of SQL's design.
pierredewet 5y ago
I found that it depends on where your interest lies. If you just want to learn practical SQL then I have historically found the Celko books (https://en.wikipedia.org/wiki/Joe_Celko https://en.wikipedia.org/wiki/Joe_Celko) as well as, more recently, the No Starch Press books (https://nostarch.com/practical-sql-2nd-edition https://nostarch.com/practical-sql-2nd-edition) very well written. Everyone learns differently however. I have a colleague who really enjoyed the O’Reilly books specific to SQL from a data analytics perspective. (Apologies, no link for that) You might frequently find the No Starch and O’Reilly books on Humble Bundle, (https://www.humblebundle.com/books?hmb_source=navbar https://www.humblebundle.com/books?hmb_source=navbar) if that is available in your location.There’s often loads of overlap between bundles. I’m sure I’ve bought the python book about 5 times so far but I don’t mind as it’s great value. If you want to learn about database theory, however, as well as the practicalities of SQL, then I found that most of the resources I used when I did this at uni were online. The book we used was the Connolly/Begg book (https://www.pearson.com/us/higher-education/program/Connolly-Database-Systems-A-Practical-Approach-to-Design-Implementation-and-Management-6th-Edition/PGM116956.html?tab=overview https://www.pearson.com/us/higher-education/program/Connolly...) I don’t think this effectively answered the “why” of your question, however. My guess is that SQL is a very well established domain language and when it comes to data normalised across many tuples it’s the standard for manipulation. I don’t think that dataset size is the primary reason to manipulate data via SQL; I think that the moment your programme starts to need more than a single flat file data source, you naturally start to think about normalisation, indexes etc for performance and sanity. If I may, however: I am coming at this from the other way in that I am far more often manipulating CSV and excel data and would like some good resources in how to quickly load that in to a dataframe in pandas or similar vs using SQL. (If I’m responding to a thread hijack I may as well go all in and totally derail it)
- bbkane 5y ago
  The SQLite shell supports importing and writing CSVs pretty easily - here are some snippets I reach for often enough to copy to a blog post:https://www.bbkane.com/blog/sqlite3-snippets/ https://www.bbkane.com/blog/sqlite3-snippets/
- geokon 5y ago
sdoering 5y ago
I don't want to advertise and am not affiliated in any way. For a first cursory understanding I do recommend the 'Mode Analytics' tutorial [0] to juniors interested in starting sql. [0]: https://mode.com/sql-tutorial/introduction-to-sql/ https://mode.com/sql-tutorial/introduction-to-sql/
simonw 5y ago
I published a new introductory SQL tutorial last week, which I hope to keep improving and expanding over time. Would very much appreciate any feedback on what I've got so far! https://datasette.io/tutorials/learn-sql https://datasette.io/tutorials/learn-sql
faizshah 5y ago
Two major reasons I use SQLite are the built in full text search https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html and fuzzy matching https://www.sqlite.org/spellfix1.html https://www.sqlite.org/spellfix1.html these are two things you often want to do with your data frames but other solutions are usually pretty slow whereas SQLite is fast. Other reasons: every language can use a SQLite db, you can add indexes, you can add multiple tables in one file, you can store large amounts of data on disk, it has built in json queries, there’s a great ecosystem of SQLite libraries, support for it is built into python.
- geokon 5y ago
  So then the question is.. why have R/Python/etc. reinvented the wheel with their own datatable structures instead of using SQL?
  - faizshah 5y ago
    It’s not really reinventing the wheel, I make use of sql, pandas, pyspark, sparksql, data.table, bigquery, it all depends on the use case. Dataframe abstractions are really easy to understand so I have worked with journalists and biologists who are able to work productively with pandas or R. Transforming and analyzing data is often easier to write with pyspark while spark sql makes it easy to access and extract data from some data sources. It all really depends on who is your customer, who are your coworkers, how much volume of data do you have, how much time do you have to accomplish your task etc.
bigDinosaur 5y ago
Once you've learnt basic SQL, I found https://15445.courses.cs.cmu.edu/fall2021/homework1/ https://15445.courses.cs.cmu.edu/fall2021/homework1/ to be a nice little way of consolidating the basic concepts.
jodrellblank 5y ago
For the very basics, Julia Evans has https://jvns.ca/blog/2020/02/03/new-zine--become-a-select-star/ https://jvns.ca/blog/2020/02/03/new-zine--become-a-select-st... (I think most/all of the comics are entries on her blog if you desperately don't want to pay).
thejosh 5y ago
You'll find with decent SQL and a good backend your SQL will be much quicker than your R/Matlab/Python programs depending on what you want to do.
goto11 5y ago
Relational databases are most useful when you have multiple tables you need to query across and when the data changes over time.
jamapy 5y ago
https://selectstarsql.com/ https://selectstarsql.com/ is an interactive book which aims to be the best place on the internet for learning SQL. It helps you learn by running queries against a real-world dataset to complete projects of consequence. It is free of charge, free of ads and doesn't require registration or downloads.
8n4vidtmkvmk 5y ago
never needed a database huh? you and i live in different worlds. every web app I've written has needed a database. need somewhere to put user data and transaction logs
- tonyedgecombe 5y ago
  I don't think it's that uncommon. I've done very little database work over a forty year career. Web development is a subset of the industry, there is a whole lot of other stuff going on.
jcagalawan 5y ago
https://executeprogram.com https://executeprogram.com has a SQL course. I’ve tried other resources but this one made it click for me after years if relying on SQLAlchemy.
DocTomoe 5y ago
Not necessarily SQL, but to get a firm understanding of (relational) databases, I like to recommend "The Manga Guide to Databases". Yes, it's a comic. Yes, not everyone reacts well to the story of a teenage faux-fantasy princess being helped by the database fairy to get her fruit export business to run efficiently. But it teaches you the basic concepts you'll be using 95% of the time way better than any documentation I've ever read.

by geokon 5y ago

wenc 5y ago

Jennifer Widom’s Databases course is great https://online.stanford.edu/courses/soe-ydatabases-databases https://online.stanford.edu/courses/soe-ydatabases-databases Otherwise SQL is best learned by solving problems and googling when you’re stuck. Most developers only need a few SQL constructs like SELECT, WHERE, WITH, JOIN, INSERT, CREATE and GROUP BY. Most devs learn and forget because they don’t use SQL very often. It’s too bad because the relational data structure (or more generally the table) is a super useful abstraction and often much more powerful than lists and dicts. Data analysts need a few more like OVER (PARTITION BY) and maybe PIVOT/UNPIVOT. Data engineers of course need many more specifically those related to database object manipulation, and stuff around performance like INDEX. Really advanced SQL experts do stuff like this. https://modern-sql.com/ https://modern-sql.com/

geokon 5y ago
Oh the course starts today.. Is there way to view the lectures without enrolling? Just more generally, do you have some mental model when you move from using something like a dataframe to using SQL? For instance people working in R rarely talk about using an SQL database - even when they have huge datasets. And I guess I don't really see when you should be thinking about making the switch over. The only thing I see distinct about databases is it's always hand in hand with web-tech - and it more focused on mutability (which if you are doing some stats your data is usually immutable)
- wenc 5y ago
  I’m not aware of other ways to get at the material though I’m sure Widom’s website at Stanford might have some. It’s not just a size thing but also a concurrency thing. Data frames are like tiny local databases. SQL databases are like central databases that many parties read and write from. There’s no switchover point as such. A common use case is to use SQL to query a subset of data from a central database, and that subset becomes a local data frame that an R user can work off of. Data frames and relational databases are very similar. When you’re using dplyr many of the operations conceptually map 1-to-1 to SQL. SQL is just a data frame manipulation DSL for databases. It’s all about filtering, aggregation and projections. Also not true about mutability and web tech. Most SQL operations do not mutate. Also SQL is more associate with data analytics, data engineering and report creation from data warehouses than web tech. The web tech use case came about because of PHP/MySQL and Django etc. but there SQL is usually used as an API language for the underlying data store rather than the full query engine that it is.
  - geokon 5y ago
    Right! the dplyr functionality seems to match 1-to-1 to what a cursory look at SQL told me - hence my confusion as to why they reinvented the wheel instead of using an industry standard The async table access is an interesting angle - assuming you're doing mutations that probably places some constraints. But in the case of in-app databasese my SQLite that's generally not a concern. From what you've said, at the end of the day, it sounds like the people using tables/dataframes are just too lazy to setup and use a SQL? I don't really see any distinction btwn the two domains then
    - wenc 5y ago
      I think it's more of a case of preference and ergonomics. For local dataframes, it's still more ergonomic to use a language's own constructs than to adopt a totally separate DSL for data manipulation. SQL is a different "language" which has its own cognitive overheads. So things like dplyr, pandas, and LINQ (in C#) were invented as language-native ways to manipulate tabular data structures without the overhead of context-switching [1]. After all, learning SQL and being good at it on top of mastering one's own programming language takes something extra. That said, it's possible and indeed sometimes preferable to use pure SQL for local data tables. I use duckdb to do complex manipulations on dataframes in Python because it's a lot faster than Pandas (due to some columnar optimizations) and because I can express certain things more succinctly in SQL than in Pandas (it's true -- I appreciate Pandas for the achievements that it is, but its syntax can be verbose with all its .apply() and df[df[col=="abc"]] incantations). Using SQL via sqlite to manipulate local tables confer similar benefits. [1] To be fair, pandas, dplyr and LINQ -- by virtue of being built into their respective languages -- can do things that SQL can't (or can't do easily) because it's not constrained by some of SQL's design.

pierredewet 5y ago

I found that it depends on where your interest lies. If you just want to learn practical SQL then I have historically found the Celko books (https://en.wikipedia.org/wiki/Joe_Celko https://en.wikipedia.org/wiki/Joe_Celko) as well as, more recently, the No Starch Press books (https://nostarch.com/practical-sql-2nd-edition https://nostarch.com/practical-sql-2nd-edition) very well written. Everyone learns differently however. I have a colleague who really enjoyed the O’Reilly books specific to SQL from a data analytics perspective. (Apologies, no link for that) You might frequently find the No Starch and O’Reilly books on Humble Bundle, (https://www.humblebundle.com/books?hmb_source=navbar https://www.humblebundle.com/books?hmb_source=navbar) if that is available in your location.There’s often loads of overlap between bundles. I’m sure I’ve bought the python book about 5 times so far but I don’t mind as it’s great value. If you want to learn about database theory, however, as well as the practicalities of SQL, then I found that most of the resources I used when I did this at uni were online. The book we used was the Connolly/Begg book (https://www.pearson.com/us/higher-education/program/Connolly-Database-Systems-A-Practical-Approach-to-Design-Implementation-and-Management-6th-Edition/PGM116956.html?tab=overview https://www.pearson.com/us/higher-education/program/Connolly...) I don’t think this effectively answered the “why” of your question, however. My guess is that SQL is a very well established domain language and when it comes to data normalised across many tuples it’s the standard for manipulation. I don’t think that dataset size is the primary reason to manipulate data via SQL; I think that the moment your programme starts to need more than a single flat file data source, you naturally start to think about normalisation, indexes etc for performance and sanity. If I may, however: I am coming at this from the other way in that I am far more often manipulating CSV and excel data and would like some good resources in how to quickly load that in to a dataframe in pandas or similar vs using SQL. (If I’m responding to a thread hijack I may as well go all in and totally derail it)

bbkane 5y ago
The SQLite shell supports importing and writing CSVs pretty easily - here are some snippets I reach for often enough to copy to a blog post:https://www.bbkane.com/blog/sqlite3-snippets/ https://www.bbkane.com/blog/sqlite3-snippets/
geokon 5y ago

sdoering 5y ago

I don't want to advertise and am not affiliated in any way. For a first cursory understanding I do recommend the 'Mode Analytics' tutorial [0] to juniors interested in starting sql. [0]: https://mode.com/sql-tutorial/introduction-to-sql/ https://mode.com/sql-tutorial/introduction-to-sql/

simonw 5y ago

I published a new introductory SQL tutorial last week, which I hope to keep improving and expanding over time. Would very much appreciate any feedback on what I've got so far! https://datasette.io/tutorials/learn-sql https://datasette.io/tutorials/learn-sql

faizshah 5y ago

Two major reasons I use SQLite are the built in full text search https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html and fuzzy matching https://www.sqlite.org/spellfix1.html https://www.sqlite.org/spellfix1.html these are two things you often want to do with your data frames but other solutions are usually pretty slow whereas SQLite is fast. Other reasons: every language can use a SQLite db, you can add indexes, you can add multiple tables in one file, you can store large amounts of data on disk, it has built in json queries, there’s a great ecosystem of SQLite libraries, support for it is built into python.

geokon 5y ago
So then the question is.. why have R/Python/etc. reinvented the wheel with their own datatable structures instead of using SQL?
- faizshah 5y ago
  It’s not really reinventing the wheel, I make use of sql, pandas, pyspark, sparksql, data.table, bigquery, it all depends on the use case. Dataframe abstractions are really easy to understand so I have worked with journalists and biologists who are able to work productively with pandas or R. Transforming and analyzing data is often easier to write with pyspark while spark sql makes it easy to access and extract data from some data sources. It all really depends on who is your customer, who are your coworkers, how much volume of data do you have, how much time do you have to accomplish your task etc.

bigDinosaur 5y ago

Once you've learnt basic SQL, I found https://15445.courses.cs.cmu.edu/fall2021/homework1/ https://15445.courses.cs.cmu.edu/fall2021/homework1/ to be a nice little way of consolidating the basic concepts.

jodrellblank 5y ago

For the very basics, Julia Evans has https://jvns.ca/blog/2020/02/03/new-zine--become-a-select-star/ https://jvns.ca/blog/2020/02/03/new-zine--become-a-select-st... (I think most/all of the comics are entries on her blog if you desperately don't want to pay).

thejosh 5y ago

You'll find with decent SQL and a good backend your SQL will be much quicker than your R/Matlab/Python programs depending on what you want to do.

goto11 5y ago

Relational databases are most useful when you have multiple tables you need to query across and when the data changes over time.

jamapy 5y ago

https://selectstarsql.com/ https://selectstarsql.com/ is an interactive book which aims to be the best place on the internet for learning SQL. It helps you learn by running queries against a real-world dataset to complete projects of consequence. It is free of charge, free of ads and doesn't require registration or downloads.

8n4vidtmkvmk 5y ago

never needed a database huh? you and i live in different worlds. every web app I've written has needed a database. need somewhere to put user data and transaction logs

tonyedgecombe 5y ago
I don't think it's that uncommon. I've done very little database work over a forty year career. Web development is a subset of the industry, there is a whole lot of other stuff going on.

jcagalawan 5y ago

https://executeprogram.com https://executeprogram.com has a SQL course. I’ve tried other resources but this one made it click for me after years if relying on SQLAlchemy.

DocTomoe 5y ago

Not necessarily SQL, but to get a firm understanding of (relational) databases, I like to recommend "The Manga Guide to Databases". Yes, it's a comic. Yes, not everyone reacts well to the story of a teenage faux-fantasy princess being helped by the database fairy to get her fruit export business to run efficiently. But it teaches you the basic concepts you'll be using 95% of the time way better than any documentation I've ever read.