9 ms·

Great idea, SQL is an undervalued skill Feedback: I did this exercise: https://lost-at-sql.therobinlord.com/challenge-page/case https://lost-at-sql.therobinl

Great idea, SQL is an undervalued skill Feedback: I did this exercise: https://lost-at-sql.therobinlord.com/challenge-page/case https://lost-at-sql.therobinlord.com/challenge-page/case The specification says: > Clownfish are between 3-7 inches in length, weigh around half a pound, and live in the coral reef. around half a pound is meaningless, the spec should be exact. It was annoying having to scroll from the input at the bottom of the page to the specification at the top of the page to refer to it. The test cases are insufficient. I only wrote this: > select *, CASE WHEN species_name = "clownfish" AND length NOT BETWEEN 3 AND 7 AND weight != .5 AND habitat_type != "coral reef" THEN "imposter" ELSE "not imposter" END imposter_status from marine_life; And passed the check at the end. I didn't like that it kept track of the number of syntax errors and how long it took me to finish, that doesn't seem conducive to learning/practicing. There seemed to be a lot of preamble to get to the challenge page. It seems like those should be linked directly from the homepage. The format button didn't work on my code above. Syntax highlighting seems broken for some functions, like IIF. It would be nice if multiple SQL dialects were supported, forcing SQLite makes this more of an exercise in 'translate the dialect you know into SQLite'. I didn't love the challenge I did overall, it was a single CASE statement, which seems to be testing logic more than any SQL knowledge. Maybe because it's a warmup?

justinclift 4y ago
> forcing SQLite It might be the case that it's running SQLite via wasm. If so, then other database engines would need to be runnable in a browser too. PostgreSQL has been shown to work in the browser (eg https://www.crunchydata.com/blog/learn-postgres-at-the-playground https://www.crunchydata.com/blog/learn-postgres-at-the-playg..., and also https://github.com/snaplet/postgres-wasm https://github.com/snaplet/postgres-wasm), so that might be an option. Not sure about others.
- faxmeyourcode 4y ago
  duckdb-wasm could also be an option https://duckdb.org/2021/10/29/duckdb-wasm.html https://duckdb.org/2021/10/29/duckdb-wasm.html
- robinLord 4y ago
  Thanks, yea as you say this was a matter of having a library that could reliably/realistically handle the SQL. I found some other libraries but they were a facsimile of SQL rather than behaving like any specific standard. Thanks for the links to those other libraries! I had to fight hard against scope creep to just get this out but I'll def add this to the list for updates!
eeiaeaa 4y ago
That challenge was annoying because of the terrible spec. In addition to the "around 1/2 lbs" spec, it falsely claims that length and weight are metric (they are not), and it spells it "impostor" in the specified output, but expects "imposter". Both spellings are used in the spec. Frustrating. However, students will learn not to trust the spec, so I suppose that's a valuable lesson.
victor106 4y ago
> SQL is an undervalued skill Wondering if chatgpt would make sql irrelevant sometime in the future?
- cellularmitosis 4y ago
  You’d still need to be able to specify _exactly_ what you want from the database in order for gpt to provide the corresponding sql. You’ll still need to understand the database structure and be able to think in terms of relations and what’s possible via queries. Make irrelevant? Probably not. Make more convenient? Probably.
  - sysstemlord 4y ago
    I guess the usefulness would be from client software like plsql or any other, implement a natural language processing powered by chatgpt, since the client already has access to the structure then it can include it in the prompt, the user would only write something vague like i want all the clients who bought with more than 500$ this year.
    - throwaway049 4y ago
      For everyday stuff like that it's easier to put the NLP in a BI tool, and some of them already did that. If you're doing more involved stuff then writing a good prompt will probably need as much analytical and domain knowledge as writing SQL.
      - robinLord 4y ago
        This is a great thread! As you might imagine I put in a lot of thought about whether Chat GPT made learning this stuff obsolete. Conclusion I came to is that those tools make this all more accessible (in terms of helping people to avoid things like frustrating single character mistakes) but that kind of makes knowing some SQL MORE valuable because it makes it much easier to know what direction to move in, and helps a person more easily debug confidently incorrect suggestions. A big problem with SQL is that sometimes the answers can LOOK right if a person doesn't know the pitfalls of, say, table joins, but can in fact be quite off the rails. I also think that there's still space for highly skilled experts doing the REALLY advanced stuff but for the average user - just knowing the basics probably helps them get even more out of LLM helpers
autoexec 4y ago
> I didn't like that it kept track of the number of syntax errors and how long it took me to finish, that doesn't seem conducive to learning/practicing. I wouldn't want that in an interactive tutorial or training program, but it sounds exactly like something a game would do. It means you can try to beat your previous times, decrease the number of errors next time, or even compare yourself against others. It provides a measure to gauge progress. I'm not usually the type to care about that sort of thing in games, but I know a lot of people are.
tetha 4y ago
Mh. The sarcastic, cynic admin in me is strongly advising against being good at SQL, understanding how SQL is executed in your specific database, how optimizations work and such. Like, it is extremely impressive what a PostgreSQL server with some well-written queries can do. There is this running gag of how hard it is to replace a reporting solution based off of one database system and a bunch of smartly written queries with supporting indexes and it's honestly not wrong. But that's also the problem - suddenly, you end up being the guy being paged for emergency "databases are on fire" situations, and "these queries are impossible to optimize to un-fire the database" and such. And once you get some time to think about these queries, you can kinda rewrite them to take 10 seconds instead of 10 hours by eliminating some subquery dependencies with some acrobatics. So, imo: Do learn SQL and how it works, so my job gets easier. But keep it very, very secret that you know it well. I also very much enjoy this game. It's very cute and fun.
- skeeter2020 4y ago
  I run a reporting & analytics team at a ~500 person SW company and I've never been paged to optimize a query because the database is on fire. That's like designing your sprinkler system while the building burns down. I've also never found a query that I can reduce from 10 hrs to 10 seconds. Maybe one that never finishes to actually running, but not the kind of magnitude improvement you reference.
  - vajrabum 4y ago
    My guess is that if you moved from reporting and analytics to a DBA role in web operations in a large scale shop you'd see something very different. It starts with programmers who don't know SQL (they use some ORM instead) and who don't know how to get or interpret a query plan and who aren't aware of indexes with the results you might imagine. These same developers tend not know how to get decent performance from a NoSQL database either. And since the code is in an ORM it can be difficult for DBAs to do a decent code review. Also, there's a very big difference between the impact of performance requirements for reports which run once or even 20-30 times per day and something that runs in every transaction where that transaction runs 10s to 1000s of times per second. If you optimize a report to run 3% faster that's unlikely to make a difference. If you optimize a hot path transaction 3% on a large website that could mean you have a few less hosts/instances to provision and the impact is a direct budget impact of $5K/yr or more. Oh and as an SRE I've found several queries where I've made 2 orders of magnitude performance improvements in one of those cases the process was beginning to fail regularly because the queries were so bad.
8n4vidtmkvmk 4y ago
I'm only willing to spend so much time on this but the answer seems horribly broken. The problem statement spells it "impostor" but the answer key says "imposter". The question says length in meters, but then the table seems to assume inches. Then the answer thingy says "order matters" but I can't find anything about that in the question.

justinclift 4y ago

> forcing SQLite It might be the case that it's running SQLite via wasm. If so, then other database engines would need to be runnable in a browser too. PostgreSQL has been shown to work in the browser (eg https://www.crunchydata.com/blog/learn-postgres-at-the-playground https://www.crunchydata.com/blog/learn-postgres-at-the-playg..., and also https://github.com/snaplet/postgres-wasm https://github.com/snaplet/postgres-wasm), so that might be an option. Not sure about others.

faxmeyourcode 4y ago
duckdb-wasm could also be an option https://duckdb.org/2021/10/29/duckdb-wasm.html https://duckdb.org/2021/10/29/duckdb-wasm.html
robinLord 4y ago
Thanks, yea as you say this was a matter of having a library that could reliably/realistically handle the SQL. I found some other libraries but they were a facsimile of SQL rather than behaving like any specific standard. Thanks for the links to those other libraries! I had to fight hard against scope creep to just get this out but I'll def add this to the list for updates!

eeiaeaa 4y ago

That challenge was annoying because of the terrible spec. In addition to the "around 1/2 lbs" spec, it falsely claims that length and weight are metric (they are not), and it spells it "impostor" in the specified output, but expects "imposter". Both spellings are used in the spec. Frustrating. However, students will learn not to trust the spec, so I suppose that's a valuable lesson.

victor106 4y ago

> SQL is an undervalued skill Wondering if chatgpt would make sql irrelevant sometime in the future?

cellularmitosis 4y ago
You’d still need to be able to specify _exactly_ what you want from the database in order for gpt to provide the corresponding sql. You’ll still need to understand the database structure and be able to think in terms of relations and what’s possible via queries. Make irrelevant? Probably not. Make more convenient? Probably.
- sysstemlord 4y ago
  I guess the usefulness would be from client software like plsql or any other, implement a natural language processing powered by chatgpt, since the client already has access to the structure then it can include it in the prompt, the user would only write something vague like i want all the clients who bought with more than 500$ this year.
  - throwaway049 4y ago
    For everyday stuff like that it's easier to put the NLP in a BI tool, and some of them already did that. If you're doing more involved stuff then writing a good prompt will probably need as much analytical and domain knowledge as writing SQL.
    - robinLord 4y ago
      This is a great thread! As you might imagine I put in a lot of thought about whether Chat GPT made learning this stuff obsolete. Conclusion I came to is that those tools make this all more accessible (in terms of helping people to avoid things like frustrating single character mistakes) but that kind of makes knowing some SQL MORE valuable because it makes it much easier to know what direction to move in, and helps a person more easily debug confidently incorrect suggestions. A big problem with SQL is that sometimes the answers can LOOK right if a person doesn't know the pitfalls of, say, table joins, but can in fact be quite off the rails. I also think that there's still space for highly skilled experts doing the REALLY advanced stuff but for the average user - just knowing the basics probably helps them get even more out of LLM helpers

autoexec 4y ago

> I didn't like that it kept track of the number of syntax errors and how long it took me to finish, that doesn't seem conducive to learning/practicing. I wouldn't want that in an interactive tutorial or training program, but it sounds exactly like something a game would do. It means you can try to beat your previous times, decrease the number of errors next time, or even compare yourself against others. It provides a measure to gauge progress. I'm not usually the type to care about that sort of thing in games, but I know a lot of people are.

tetha 4y ago

Mh. The sarcastic, cynic admin in me is strongly advising against being good at SQL, understanding how SQL is executed in your specific database, how optimizations work and such. Like, it is extremely impressive what a PostgreSQL server with some well-written queries can do. There is this running gag of how hard it is to replace a reporting solution based off of one database system and a bunch of smartly written queries with supporting indexes and it's honestly not wrong. But that's also the problem - suddenly, you end up being the guy being paged for emergency "databases are on fire" situations, and "these queries are impossible to optimize to un-fire the database" and such. And once you get some time to think about these queries, you can kinda rewrite them to take 10 seconds instead of 10 hours by eliminating some subquery dependencies with some acrobatics. So, imo: Do learn SQL and how it works, so my job gets easier. But keep it very, very secret that you know it well. I also very much enjoy this game. It's very cute and fun.

skeeter2020 4y ago
I run a reporting & analytics team at a ~500 person SW company and I've never been paged to optimize a query because the database is on fire. That's like designing your sprinkler system while the building burns down. I've also never found a query that I can reduce from 10 hrs to 10 seconds. Maybe one that never finishes to actually running, but not the kind of magnitude improvement you reference.
- vajrabum 4y ago
  My guess is that if you moved from reporting and analytics to a DBA role in web operations in a large scale shop you'd see something very different. It starts with programmers who don't know SQL (they use some ORM instead) and who don't know how to get or interpret a query plan and who aren't aware of indexes with the results you might imagine. These same developers tend not know how to get decent performance from a NoSQL database either. And since the code is in an ORM it can be difficult for DBAs to do a decent code review. Also, there's a very big difference between the impact of performance requirements for reports which run once or even 20-30 times per day and something that runs in every transaction where that transaction runs 10s to 1000s of times per second. If you optimize a report to run 3% faster that's unlikely to make a difference. If you optimize a hot path transaction 3% on a large website that could mean you have a few less hosts/instances to provision and the impact is a direct budget impact of $5K/yr or more. Oh and as an SRE I've found several queries where I've made 2 orders of magnitude performance improvements in one of those cases the process was beginning to fail regularly because the queries were so bad.

8n4vidtmkvmk 4y ago

I'm only willing to spend so much time on this but the answer seems horribly broken. The problem statement spells it "impostor" but the answer key says "imposter". The question says length in meters, but then the table seems to assume inches. Then the answer thingy says "order matters" but I can't find anything about that in the question.