33 ms·

Challenges students face when learning to work with relational databases and SQL

138 points by gousiosg 5y ago

patrakov 5y ago
Direct link to the research paper, instead of the summary: https://dl.acm.org/doi/pdf/10.1145/3446871.3469759 https://dl.acm.org/doi/pdf/10.1145/3446871.3469759 (pdf)
DaiPlusPlus 5y ago
I noticed that the article doesn't mention relational-calculus at all, only relational-algebra. That's a huge oversight, imo - as I feel one needs to understand both RA and RC in order to grok SQL and other RC-like systems, like Linq in C#/.NET and List-comprehensions in Python (or even use those before RC/RA and SQL). ------- Rather than improve how SQL is taught (which seems to be the paper's objective), why not improve SQL so it isn't as horrible to try to learn in the first place? The barriers to grokking SQL could be lowered considerably if SQL made minor adjustments like moving the projection part of a SELECT query to being below or syntactically after the WHERE clause instead of being at the top, and making SQL more "natural" to write-in without needing excessively verbose inner-derived-table expressions when all you want is to do perform some repetitive calculation which will be reused in later query steps. Also, the GROUP BY clause really needs to be renamed to "AGGREGATE BY" or similar, because when normal people think "group" they're probably thinking of sorting/ORDER BY or PARTITION BY and they certainly don't imagine "don't display these rows at all, lol". I just don't understand what drives the ISO SQL language design committee - I'd have thought that the newer revisions (e.g. SQL-2003) would have improved the language's ergonomics - on the contrary: the language's grammar and verbosity gets worse every release, and the team has strange priorities: apparently they feel needing to generate in-memory XML is more important than deferrable constraints - and I only ever see ISO SQL's XML features being abused to make-up for a lack of decent string-aggregation functions. (...I could talk for hours about everything wrong with SQL.)
- de6u99er 5y ago
  The trick of becoming really good at SQL (applies to all areas of IT) is having a certain ambition to produce high performance beautiful (readable) code. This requires experience which can only be gained by rolling up your sleves and working on stuff until the high ambition has been satisfied. Sometimes when I see old code from myself, and I can follow what I have been doing I get really proud of myself. Many times I end up slightly improving it based on new knowledge I have acquired since I initially wrote it.
  - minism 5y ago
    High performance and readable certainly. Not sure why beautiful would be something to strive for though
    - ducharmdev 5y ago
      I believe they're intending to say that beautiful code == readable code.
  - AdrianB1 5y ago
    Readable code can be easily done via good formatting, but performance requires a combination of writing the query in the right way and the indexes to support it. The second part is not even visible from the query and most of the time is not self-explanatory, but the best part is that indexing is not even universally valid, statistics decide execution plans and the same query with the same indexes can result in very different performance on 2 different instances.
- jimbob45 5y ago
  If the general computing community can agree on anything over the last 20 years, it’s that Python 2->3 was a disaster. Even though Python 3 made several highly necessary (and irreversibly transformative) changes to Python 2, no one liked it because it fundamentally changed the language to something unfamiliar. I’m guessing the SQL and C++ committees looked at that transition and decided that such transformative changes really need to be done in new languages (like the Perl -> Raku change) rather than in a new version which risks alienating your existing base.
  - darksaints 5y ago
    Python 2->3 was a disaster, but I'd refrain from extrapolation because a lot of that difficulty was very specific to dynamic typing or python itself. There are tons of languages that have gone through far more transformative changes in the core semantics of the language, and have gone a lot smoother.
  - DaiPlusPlus 5y ago
    Oh of course - I have no doubt the ISO SQL committee is so conservative (no... they're regressive) is because of the sheer collective industry investment in not-only SQL tooling and SQL-compatible databases, but just energy-spent in teaching non-CS/SE/programmer types in businesses how to express their data-queries in SQL. It's very, very difficult to get the kind of industry cohesiveness around any technical standard, so the fact that SQL is so widely supported is a miracle (though it probably has something to do with US federal government requirements for information systems to support it, just like how POSIX is a thing because of the fed pushing for it). To be clear: I am not advocating for a brand new query-language syntax or any kind of Python3-style overhaul, but I'd like to see SQL start to take small steps towards integrating the lessons learned from the past 60+ years of language design rather than doing the complete opposite.
  - btilly 5y ago
    The Python 2 to Python 3 migration was such a disaster that Python 3 is now used by both more programmers, and a higher proportion of programmers, than Python 2 ever managed. This is not to minimize the pain of switching. But it does not seem to ahve limited the success of the language.
dehrmann 5y ago
> For example, some students wrote queries containing ,≠, instead of != or <>. Was this done on paper? Typing ≠ takes some doing.
- turnerc 5y ago
  From the study: > Participants wrote their notes and answers on paper, which they showed in front of the webcam. Yes it seems they did
  - dehrmann 5y ago
    That's pretty flawed methodology since you'd want to know what problems people encounter in the real world and how quickly they solve them.
  - jve 5y ago
    I mean, who writes queries on paper? And by pressing Execute, SQL will gladly provide error message that the student will, I think, quickly resolve. Is this a contest that measures how well students can write compile-able code without executing it or what? Moreover, IDE would have helped those students for sure: SQL keywords are highlighted in different color + autocomplete.
- pcblues 5y ago
  Easy if you have an APL keyboard :) (Hint, it's on the 8) https://www.dyalog.com/uploads/images/Business/products/us_rc.jpg https://www.dyalog.com/uploads/images/Business/products/us_r...
- Wevah 5y ago
  Option-= on a Mac with the US layout, fwiw.
  - JadeNB 5y ago
    Man, Mac's keyboard shortcuts for special characters irritate me so much. First, they're there, and it's absolutely wonderful! I use far more semantically accurate Unicode rather than lossy ASCII approximations than I did back in my old Windows days. (If you don't know the special characters you can get, turn on Keyboard Viewer and whack your keyboard, especially modifier keys, a bit.) But … I can't customise them. Even back in the days when macOS was OS X and believed in user customisation, these specific shortcuts were frozen and un-customizable. (Like the folder shortcuts in Finder. Maybe it makes sense to you for CMD-SHIFT-D to open the Downloads folder, not the Desktop. Too bad!) (Boy, I hope I'm wrong and someone will come along and explain my stupidity to me.)
hahamrfunnyguy 5y ago
In my experience, inexperienced database developers pick up SQL fairly quickly under the guidance of an experienced mentor.
- deleted 5y ago
  [deleted]
- Spooky23 5y ago
  This. Usually I see people struggling to formulate questions. They know what they want, but don’t understand how to get there. Left to their own devices, they hack up some nightmare in Excel. I worked with a summer intern on creating reports and learning SQL. She was a really smart business major who ended up with the wrong work assignment. I was getting 5-7 questions a day from her in June, 1-2 a week in July and by the time I got back from vacation in August, she had basically done about 90% of a project that was going to be hired out and was showing me some features of the database we were using that I didn’t know! It inspired her to switch majors and she is a fancy data scientist somewhere! Awesome mentor experience.
- edoceo 5y ago
  +1 for mentoring and pairing. Or mentoring via pairing. Getting with and expert, or even just another set of experienced eyes is a big help
tracyhenry 5y ago
SQL has a steep learning curve. It expose almost zero insights into the underlying query execution. As a result, increasingly amount of inefficient queries are being written by ML engineers, who in general care little about query efficiency. The solution right now seems to have a team of data engineers to optimize the queries. Should we think about an alternative, at least for ML ETL workloads?
- dspillett 5y ago
  > SQL has a steep learning curve. Overall I don't think it is that steep, though maybe I'm blinded by having worked with various implementations of it for more than two decades. The key sticking point is jumping to thinking in a set based manner to get best results. The rest of the difficult parts are when you need to think about implementation details because the query planners are no perfect (index hints and such) or being aware of limitations (like postgres before the latest major version having optimisation fences around CTEs). > It expose almost zero insights into the underlying query execution. That is pretty much by design. It is intended that you say what you want and let the query planner worry about implementation details. Of course how you tell it what you want involves learning to express those intentions in SQL. It does fall apart a bit when implementation limitations become an issue, at which point you are forced to think about the underlying implementation and how you might prod this more imperative code so that it interprets and process your relational descriptions most efficiently. > As a result, increasingly amount of inefficient queries are being written by ML engineers That isn't specific to ML. I see a lot of inefficient data interaction from code written by other devs. This seems to be for two reasons: 1. People seem to have taken to heart “make it work, make it work correctly, only then worry about making it work fast” to heart but tend to skip that last part and assume because all is well with their test sets of data at hundreds or thousands of rows (or sometimes tens and singles) that it'll scale just find to the hundreds of thousands or more that the clients datasets will eventually contain. 2. People using further abstractions without much care for how they implement their directives (again, in an ideal world they shouldn't have to), resulting in massively overcomplex queries as the framework tries to be clever and helpful and preempt what might be needed, getting everything whether needed or not (effectively `SELECT `) meaning the query planner can't apply families of its internal tricks for better performance, or getting many rows individually instead of as a set which sometimes means a lot of extra work for each row. There is a definite “we'll worry about that when it happens attitude in both cases which is dangerous. While a live system has practically ground to a halt and the client needs their report by EOP or someone will get it in the neck (and be sure: they will pass that on to you!) is not a good time to be optimising data access, or worse finding out the structure just doesn't support efficient generation of the required data. Another common failing is applying what would idealy be UI or BLL concerns (timezone conversions etc) in the SQL statements in a way that blocks index use. > Should we think about an alternative, at least for ML ETL workloads?* I don't work with ML so that is a little outside my day-to-day wexpertise, but I'd wager ETL there has the same problem as everywhere: the basics are all well known and very well optimised for already. The rest differ so much between applications that no one abstraction would be optimal for more than a small portion of real world needs. I'd be wary of a separate team for optimising queries. I suggest a reasonable understanding in the whole dev team with a data expert embedded who is involved in design work and code reviews so issues are caught early and junior devs can be tutored as needed so by the time they are seniors they don't need the data expert except for really gnarly problems or long-term planning.
monkeydust 5y ago
Have been using openai codex for a week and it's shockingly good at SQL with well defined prompts.
pcblues 5y ago
I have been developing software that includes SQL for twenty years, and watched my own mental progress from misunderstanding to understanding. I found the biggest initial problem is that I used to imagine SQL queries as an imperative language rather than as expressions of data. Maybe in the teaching of SQL, this should be highlighted so absolute beginners can have that mental model when they are formulating solutions and grappling with the syntax.
- k__ 5y ago
  I don't know if that's enough. Understanding the difference between declarative and imperative programming is rather hard with all the abstractions we have today. People always say, declarative programming is defining what you want, not doing the steps needed to get it. But today no imperative interface requires you to do all the steps either, plus, most programming languages use both paradigms at the same time.
  - pcblues 5y ago
    I guess my point was that if you are trying to achieve results in a language paradigm that isn't the one the language was designed for, the learning curve is _really_ steep, and to use the declarative features of any language still requires you to understand the paradigms' differences. When I was at uni it wasn't until the final year that Programming Paradigms was a course, but even a rough introduction to them in any of the languages I studied earlier would have helped. Something like, "This language is used like this. It is not used like this, for example."
    - k__ 5y ago
      I'm not even sure, I totally understood the difference now, 10 years after I studied CS.
  - btilly 5y ago
    Understanding the difference between declarative and imperative programming is rather hard with all the abstractions we have today. The distinction is are you telling the computer *how* to do it, or telling it *what* steps to take. If, even with access to all of the code, you'd have to ask the computer how it chose to do it to figure out what it did, you have a declarative system. If the code reads like instructions for a recipe, it is imperative. The complications come with the fact that these two paradigms do not describe all of the possibilities. Notably object oriented and functional designs are neither imperative or declarative. (But may share some features with both.)
sdevonoes 5y ago
I takes days/weeks to pick up the core of SQL: - create tables, update the schema, insert rows, add an index - select, filters, joins, order by, limit, inner queries It takes forever to be comfortable with: - anything that involves summarizing, grouping, having, min, max, windows
- deleted 5y ago
  [deleted]
- 542458 5y ago
  Personally I feel that it took me a while to get really comfortable with more complex joins. There’s an problem they used in the study that required joining a table with itself, and honestly I would probably take a while to come up with that answer, if at all.
  - weaksauce 5y ago
    A basic approach(probably what they are going for in a basics study) would be something like this off the top of my head select c.cid, c2.cid from customer as c inner join customer as c2 on c.street = c2.street where c.city <> c2.city though that has reflective duplicates say (1, 5) would also have (5, 1) in the output. So I'm not sure if that's "allowed"
    - fifilura 5y ago
      There are lots of cases where a join with yourself is applicable, although they are mostly superseded by window functions these days. For example normalisation (join with a groupby/sum of yourself) or rank (join each row with all rows that have lower value than yourself and count those rows). But as I mentioned above. A good start is to sketch that out in excel. You will realize that what you need is another column (e.g. total sum for this id). And from that you can work yourself backwards to figure out what is the table you need to join with to create that column.
      - weaksauce 5y ago
agumonkey 5y ago
To me the most difficult part is learning sql before knowing what can be done with a computer, both on the complexity and the language design part. Before learning interpreters/compilers/prolog, I'd spend a lot of time trying to figure out about naming/namespaces in queries, while after doing some PLT, it all becomes very very obvious, you can now focus on the operators and since you'd know how far can programming go, you'd see faster how nested queries could make sense, what aggregating functions meants etc
gnat 5y ago
https://dl.acm.org/doi/pdf/10.1145/3446871.3469759 https://dl.acm.org/doi/pdf/10.1145/3446871.3469759 has the actual paper.
da39a3ee 5y ago
I've done backend web development with a relational DB via an ORM for 10 years. I'm OK at that, but I'm fucking hopeless at SQL. I know that my opinions are thus undermined, but I really wish we could get rid of SQL and replace it with something like logica [1] like today. SQL's pseudo-natural language syntax is an embarrassment and its lack of composability is even more of an embarrassment. [1] https://opensource.google/projects/logica https://opensource.google/projects/logica
- simonw 5y ago
  Have you used CTEs much (aka the WITH statement)? I find them to be a huge step forwards in terms of adding composability to complex queries.
  - odipar 5y ago
    Yes I concur: CTEs is closer to the spirit of relational algebra: every step/expression should yield a table/relation. As data munging is about combining/correlating/sorting/grouping data, why not have a sound (bag) algebra to do that? Such algebra would give us equational reasoning, proofs, etc. And consequently: students would be learning an algebra which is easier to learn IMO.
  - da39a3ee 5y ago
    Thanks yes I have learned to use the WITH statement, and I agree it gives more composability. But still, a half way house wouldn't you say?
ipaddr 5y ago
One of the best ways to learn advanced oracle specifically is through the ask tom q/a. The question are difficult and the answers teach more than any course. https://asktom.oracle.com/pls/apex/f?p=100:1000 https://asktom.oracle.com/pls/apex/f?p=100:1000::::::
odipar 5y ago
My first encounter with 'SQL' was a course on relational algebra that was taught at my university. It started out with defining relations as a mathematical construct, and continued with various operators on such relations. Then they continued explaining the various normal forms up the fifth normal form. I was completely out of my depth, but at least it was good and solid theory that could be learned. What really messed with my head is they then introduced SQL as a 'practical' implementation of relational algebra. I'm still having nightmares where I try to understand nested HAVING statements that where asked at the exams. Hey relations don't contain duplicates! But that's OK. We should call (modern) SQL 'BAG ALGEBRA'.
- melony 5y ago
  Don't forget loops
- geophile 5y ago
  Yes, SQL is about bags, not sets. And anything having to do with aggregation cannot be expressed in relational algebra.
TrackerFF 5y ago
I see they mentioned previous course knowledge - this is something you see in many (programming) classes. Students that have zero prior knowledge in programming, are able to pick up functional programming pretty easy. Students that have studied and used paradigms like OOP, seem to have a hard time grokking functional programming - as they see everything through the lens of OOP (and the languages they've used).
- btilly 5y ago
  Functional and OOP techniques do not seem to be best friends. http://steve-yegge.blogspot.com/2006/03/execution-in-kingdom-of-nouns.html http://steve-yegge.blogspot.com/2006/03/execution-in-kingdom... uses Java to discuss what this can look like in an extreme case.
ttfkam 5y ago
Step 1: "SQL sucks!" Step 2: Let's make a database engine that doesn't use SQL. Step 3: "This is hard!" Step 4: Make SQL access layer. Wash. Rinse. Repeat. See: PartiQL Those who ignore the lessons of SQL are doomed to reimplement them...poorly.
- ttfkam 5y ago
  Corollary: 1. "SQL doesn't scale!" 2. We made this database engine that's "web scale"! 3. "This is hard to use!" 4. Make SQL access layer. See: Spanner Those who blame SQL for their performance problems are doomed to repeat them using a proprietary syntax.
  - int_19h 5y ago
    This is completely orthogonal to having a saner query language for a relational database. SQL is like C - it does the job, but it has so many legacy warts, it's not all that hard to do better. The only real problem is overcoming how entrenched it is in the industry, which requires any replacement to be substantially better than SQL - so that the gains can justify the logistical costs of switching.
zxcvbn4038 5y ago
I can’t count how many times I’ve found developers doing “select *” and doing all of the logic and sorting themselves. That goes horribly wrong when you get to production and the database is larger then ram. Then they just stare at you and blink in total disbelief that such a thing is possible.
tanin 5y ago
I've built a desktop app where you can load a CSV and start writing SQL on it. A lower barrier to try it out might be helpful in learning SQL. See: https://superintendent.app https://superintendent.app
listenallyall 5y ago
SQL is hard not because of terrible syntax, but because the underlying logic of defining exactly what you want, is difficult. SQL, while not perfect, is very compact and direct. It allows you to express what you want succinctly and without boilerplate. No classes, no variable declarations (sure you can DECLARE a variable, but it is rarely needed), no dependencies or imports. There's a reason why, despite the promises of every BI tool that it will "simplify" your database and "empower users", none of them have toppled SQL or even added anything useful that SQL could incorporate. Graphical query designers are nice but have limited capabilities. SQL could occasionally be less verbose and IDEs could probably do better in reducing keystrokes (better autocomplete), but SQL itself, overall, is pure and brilliant.
- Groxx 5y ago
  Yeah - my class covered the language and meanings of the terms in a couple days. It's wonderfully simple. Which makes it hard in the same ways programming is hard - arrays are trivial! People still screw up bounds checks routinely! Figuring out what you want, and understanding your data well enough to know what's viable and what's nonsense, is infinitely harder. And it changes every time. Getting good at that part is "expertise" in a nutshell - gradually learning what strategies work and when, and getting better and better at your guesses. That takes more than a few weeks; that's an entire career.
- nojito 5y ago
  > There's a reason why, despite the promises of every BI tool that it will "simplify" your database and "empower users", none of them have toppled SQL or even added anything useful that SQL could incorporate. Pivoting data is still extremely painful using raw SQL
  - listenallyall 5y ago
    That's a semi-valid objection but I'd argue that's not really within the scope of SQL (and the PIVOT keyword is not an official part of the language, I believe). SQL is a language to interact with databases, SELECT queries are specifically for extracting data out of a database. Pivoting is typically something you do with data you've already extracted, that currently resides in memory, which is why it is fast, there's no additional data retrieval each time you change the aggregations or filters in a pivot table. Put it this way -- a SQL-based pivot table engine, that re-queries the database with every change, would perform awfully compared to a simple Excel PivotTable. Similarly, you can't use SQL to apply color formatting to any of the result rows or values -- but that was never a goal SQL intended to achieve.
deleted 5y ago
[deleted]
jimjimjim 5y ago
gone are the days of: - SELECT name, address - FROM user, location - WHERE user.locationid = location.id
chmod775 5y ago
> This was not always the case for our participants, who wrote queries with synonyms of the correct words, leading to queries that will not be executed. This shows that those participants lack very basic foundational knowledge. It doesn't surprise me, because in my experience all programming courses that taught SQL early have been terrible. Nobody who already has a basic understanding of computer science would make this mistake. On the flipside there's really no point of devoting much time to teaching SQL later, because once you have a good understanding of data structures and algorithms, it is rather easy to make educated guesses of what is happening behind the scenes in a database - and you would have no trouble of teaching yourself SQL if necessary at some point. Not to mention that teaching databases before what makes up their implementation is teaching software development in precisely the opposite way it is practiced: the composition of lower level concepts into higher level abstractions. Last but not least, when you're teaching future software engineers, at the end you don't want them to just say "I can use this", you want them to say "I could build this". Teaching SQL early smells like surrender.

138 points by gousiosg 5y ago

Direct link to the research paper, instead of the summary: https://dl.acm.org/doi/pdf/10.1145/3446871.3469759 https://dl.acm.org/doi/pdf/10.1145/3446871.3469759 (pdf)

DaiPlusPlus 5y ago

I noticed that the article doesn't mention relational-calculus at all, only relational-algebra. That's a huge oversight, imo - as I feel one needs to understand both RA and RC in order to grok SQL and other RC-like systems, like Linq in C#/.NET and List-comprehensions in Python (or even use those before RC/RA and SQL). ------- Rather than improve how SQL is taught (which seems to be the paper's objective), why not improve SQL so it isn't as horrible to try to learn in the first place? The barriers to grokking SQL could be lowered considerably if SQL made minor adjustments like moving the projection part of a SELECT query to being below or syntactically after the WHERE clause instead of being at the top, and making SQL more "natural" to write-in without needing excessively verbose inner-derived-table expressions when all you want is to do perform some repetitive calculation which will be reused in later query steps. Also, the GROUP BY clause really needs to be renamed to "AGGREGATE BY" or similar, because when normal people think "group" they're probably thinking of sorting/ORDER BY or PARTITION BY and they certainly don't imagine "don't display these rows at all, lol". I just don't understand what drives the ISO SQL language design committee - I'd have thought that the newer revisions (e.g. SQL-2003) would have improved the language's ergonomics - on the contrary: the language's grammar and verbosity gets worse every release, and the team has strange priorities: apparently they feel needing to generate in-memory XML is more important than deferrable constraints - and I only ever see ISO SQL's XML features being abused to make-up for a lack of decent string-aggregation functions. (...I could talk for hours about everything wrong with SQL.)

de6u99er 5y ago
The trick of becoming really good at SQL (applies to all areas of IT) is having a certain ambition to produce high performance beautiful (readable) code. This requires experience which can only be gained by rolling up your sleves and working on stuff until the high ambition has been satisfied. Sometimes when I see old code from myself, and I can follow what I have been doing I get really proud of myself. Many times I end up slightly improving it based on new knowledge I have acquired since I initially wrote it.
- minism 5y ago
  High performance and readable certainly. Not sure why beautiful would be something to strive for though
  - ducharmdev 5y ago
    I believe they're intending to say that beautiful code == readable code.
- AdrianB1 5y ago
  Readable code can be easily done via good formatting, but performance requires a combination of writing the query in the right way and the indexes to support it. The second part is not even visible from the query and most of the time is not self-explanatory, but the best part is that indexing is not even universally valid, statistics decide execution plans and the same query with the same indexes can result in very different performance on 2 different instances.
jimbob45 5y ago
If the general computing community can agree on anything over the last 20 years, it’s that Python 2->3 was a disaster. Even though Python 3 made several highly necessary (and irreversibly transformative) changes to Python 2, no one liked it because it fundamentally changed the language to something unfamiliar. I’m guessing the SQL and C++ committees looked at that transition and decided that such transformative changes really need to be done in new languages (like the Perl -> Raku change) rather than in a new version which risks alienating your existing base.
- darksaints 5y ago
  Python 2->3 was a disaster, but I'd refrain from extrapolation because a lot of that difficulty was very specific to dynamic typing or python itself. There are tons of languages that have gone through far more transformative changes in the core semantics of the language, and have gone a lot smoother.
- DaiPlusPlus 5y ago
  Oh of course - I have no doubt the ISO SQL committee is so conservative (no... they're regressive) is because of the sheer collective industry investment in not-only SQL tooling and SQL-compatible databases, but just energy-spent in teaching non-CS/SE/programmer types in businesses how to express their data-queries in SQL. It's very, very difficult to get the kind of industry cohesiveness around any technical standard, so the fact that SQL is so widely supported is a miracle (though it probably has something to do with US federal government requirements for information systems to support it, just like how POSIX is a thing because of the fed pushing for it). To be clear: I am not advocating for a brand new query-language syntax or any kind of Python3-style overhaul, but I'd like to see SQL start to take small steps towards integrating the lessons learned from the past 60+ years of language design rather than doing the complete opposite.
- btilly 5y ago
  The Python 2 to Python 3 migration was such a disaster that Python 3 is now used by both more programmers, and a higher proportion of programmers, than Python 2 ever managed. This is not to minimize the pain of switching. But it does not seem to ahve limited the success of the language.

dehrmann 5y ago

> For example, some students wrote queries containing ,≠, instead of != or <>. Was this done on paper? Typing ≠ takes some doing.

turnerc 5y ago
From the study: > Participants wrote their notes and answers on paper, which they showed in front of the webcam. Yes it seems they did
- dehrmann 5y ago
  That's pretty flawed methodology since you'd want to know what problems people encounter in the real world and how quickly they solve them.
- jve 5y ago
  I mean, who writes queries on paper? And by pressing Execute, SQL will gladly provide error message that the student will, I think, quickly resolve. Is this a contest that measures how well students can write compile-able code without executing it or what? Moreover, IDE would have helped those students for sure: SQL keywords are highlighted in different color + autocomplete.
pcblues 5y ago
Easy if you have an APL keyboard :) (Hint, it's on the 8) https://www.dyalog.com/uploads/images/Business/products/us_rc.jpg https://www.dyalog.com/uploads/images/Business/products/us_r...
Wevah 5y ago
Option-= on a Mac with the US layout, fwiw.
- JadeNB 5y ago
  Man, Mac's keyboard shortcuts for special characters irritate me so much. First, they're there, and it's absolutely wonderful! I use far more semantically accurate Unicode rather than lossy ASCII approximations than I did back in my old Windows days. (If you don't know the special characters you can get, turn on Keyboard Viewer and whack your keyboard, especially modifier keys, a bit.) But … I can't customise them. Even back in the days when macOS was OS X and believed in user customisation, these specific shortcuts were frozen and un-customizable. (Like the folder shortcuts in Finder. Maybe it makes sense to you for CMD-SHIFT-D to open the Downloads folder, not the Desktop. Too bad!) (Boy, I hope I'm wrong and someone will come along and explain my stupidity to me.)

hahamrfunnyguy 5y ago

In my experience, inexperienced database developers pick up SQL fairly quickly under the guidance of an experienced mentor.

deleted 5y ago
[deleted]
Spooky23 5y ago
This. Usually I see people struggling to formulate questions. They know what they want, but don’t understand how to get there. Left to their own devices, they hack up some nightmare in Excel. I worked with a summer intern on creating reports and learning SQL. She was a really smart business major who ended up with the wrong work assignment. I was getting 5-7 questions a day from her in June, 1-2 a week in July and by the time I got back from vacation in August, she had basically done about 90% of a project that was going to be hired out and was showing me some features of the database we were using that I didn’t know! It inspired her to switch majors and she is a fancy data scientist somewhere! Awesome mentor experience.
edoceo 5y ago
+1 for mentoring and pairing. Or mentoring via pairing. Getting with and expert, or even just another set of experienced eyes is a big help

tracyhenry 5y ago

SQL has a steep learning curve. It expose almost zero insights into the underlying query execution. As a result, increasingly amount of inefficient queries are being written by ML engineers, who in general care little about query efficiency. The solution right now seems to have a team of data engineers to optimize the queries. Should we think about an alternative, at least for ML ETL workloads?

dspillett 5y ago
> SQL has a steep learning curve. Overall I don't think it is that steep, though maybe I'm blinded by having worked with various implementations of it for more than two decades. The key sticking point is jumping to thinking in a set based manner to get best results. The rest of the difficult parts are when you need to think about implementation details because the query planners are no perfect (index hints and such) or being aware of limitations (like postgres before the latest major version having optimisation fences around CTEs). > It expose almost zero insights into the underlying query execution. That is pretty much by design. It is intended that you say what you want and let the query planner worry about implementation details. Of course how you tell it what you want involves learning to express those intentions in SQL. It does fall apart a bit when implementation limitations become an issue, at which point you are forced to think about the underlying implementation and how you might prod this more imperative code so that it interprets and process your relational descriptions most efficiently. > As a result, increasingly amount of inefficient queries are being written by ML engineers That isn't specific to ML. I see a lot of inefficient data interaction from code written by other devs. This seems to be for two reasons: 1. People seem to have taken to heart “make it work, make it work correctly, only then worry about making it work fast” to heart but tend to skip that last part and assume because all is well with their test sets of data at hundreds or thousands of rows (or sometimes tens and singles) that it'll scale just find to the hundreds of thousands or more that the clients datasets will eventually contain. 2. People using further abstractions without much care for how they implement their directives (again, in an ideal world they shouldn't have to), resulting in massively overcomplex queries as the framework tries to be clever and helpful and preempt what might be needed, getting everything whether needed or not (effectively `SELECT `) meaning the query planner can't apply families of its internal tricks for better performance, or getting many rows individually instead of as a set which sometimes means a lot of extra work for each row. There is a definite “we'll worry about that when it happens attitude in both cases which is dangerous. While a live system has practically ground to a halt and the client needs their report by EOP or someone will get it in the neck (and be sure: they will pass that on to you!) is not a good time to be optimising data access, or worse finding out the structure just doesn't support efficient generation of the required data. Another common failing is applying what would idealy be UI or BLL concerns (timezone conversions etc) in the SQL statements in a way that blocks index use. > Should we think about an alternative, at least for ML ETL workloads?* I don't work with ML so that is a little outside my day-to-day wexpertise, but I'd wager ETL there has the same problem as everywhere: the basics are all well known and very well optimised for already. The rest differ so much between applications that no one abstraction would be optimal for more than a small portion of real world needs. I'd be wary of a separate team for optimising queries. I suggest a reasonable understanding in the whole dev team with a data expert embedded who is involved in design work and code reviews so issues are caught early and junior devs can be tutored as needed so by the time they are seniors they don't need the data expert except for really gnarly problems or long-term planning.

monkeydust 5y ago

Have been using openai codex for a week and it's shockingly good at SQL with well defined prompts.

pcblues 5y ago

I have been developing software that includes SQL for twenty years, and watched my own mental progress from misunderstanding to understanding. I found the biggest initial problem is that I used to imagine SQL queries as an imperative language rather than as expressions of data. Maybe in the teaching of SQL, this should be highlighted so absolute beginners can have that mental model when they are formulating solutions and grappling with the syntax.

k__ 5y ago
I don't know if that's enough. Understanding the difference between declarative and imperative programming is rather hard with all the abstractions we have today. People always say, declarative programming is defining what you want, not doing the steps needed to get it. But today no imperative interface requires you to do all the steps either, plus, most programming languages use both paradigms at the same time.
- pcblues 5y ago
  I guess my point was that if you are trying to achieve results in a language paradigm that isn't the one the language was designed for, the learning curve is _really_ steep, and to use the declarative features of any language still requires you to understand the paradigms' differences. When I was at uni it wasn't until the final year that Programming Paradigms was a course, but even a rough introduction to them in any of the languages I studied earlier would have helped. Something like, "This language is used like this. It is not used like this, for example."
  - k__ 5y ago
    I'm not even sure, I totally understood the difference now, 10 years after I studied CS.
- btilly 5y ago
  Understanding the difference between declarative and imperative programming is rather hard with all the abstractions we have today. The distinction is are you telling the computer *how* to do it, or telling it *what* steps to take. If, even with access to all of the code, you'd have to ask the computer how it chose to do it to figure out what it did, you have a declarative system. If the code reads like instructions for a recipe, it is imperative. The complications come with the fact that these two paradigms do not describe all of the possibilities. Notably object oriented and functional designs are neither imperative or declarative. (But may share some features with both.)

sdevonoes 5y ago

I takes days/weeks to pick up the core of SQL: - create tables, update the schema, insert rows, add an index - select, filters, joins, order by, limit, inner queries It takes forever to be comfortable with: - anything that involves summarizing, grouping, having, min, max, windows

deleted 5y ago
[deleted]
542458 5y ago
Personally I feel that it took me a while to get really comfortable with more complex joins. There’s an problem they used in the study that required joining a table with itself, and honestly I would probably take a while to come up with that answer, if at all.
- weaksauce 5y ago
  A basic approach(probably what they are going for in a basics study) would be something like this off the top of my head select c.cid, c2.cid from customer as c inner join customer as c2 on c.street = c2.street where c.city <> c2.city though that has reflective duplicates say (1, 5) would also have (5, 1) in the output. So I'm not sure if that's "allowed"
  - fifilura 5y ago
    There are lots of cases where a join with yourself is applicable, although they are mostly superseded by window functions these days. For example normalisation (join with a groupby/sum of yourself) or rank (join each row with all rows that have lower value than yourself and count those rows). But as I mentioned above. A good start is to sketch that out in excel. You will realize that what you need is another column (e.g. total sum for this id). And from that you can work yourself backwards to figure out what is the table you need to join with to create that column.
    - weaksauce 5y ago

agumonkey 5y ago

To me the most difficult part is learning sql before knowing what can be done with a computer, both on the complexity and the language design part. Before learning interpreters/compilers/prolog, I'd spend a lot of time trying to figure out about naming/namespaces in queries, while after doing some PLT, it all becomes very very obvious, you can now focus on the operators and since you'd know how far can programming go, you'd see faster how nested queries could make sense, what aggregating functions meants etc

gnat 5y ago

https://dl.acm.org/doi/pdf/10.1145/3446871.3469759 https://dl.acm.org/doi/pdf/10.1145/3446871.3469759 has the actual paper.

da39a3ee 5y ago

I've done backend web development with a relational DB via an ORM for 10 years. I'm OK at that, but I'm fucking hopeless at SQL. I know that my opinions are thus undermined, but I really wish we could get rid of SQL and replace it with something like logica [1] like today. SQL's pseudo-natural language syntax is an embarrassment and its lack of composability is even more of an embarrassment. [1] https://opensource.google/projects/logica https://opensource.google/projects/logica

simonw 5y ago
Have you used CTEs much (aka the WITH statement)? I find them to be a huge step forwards in terms of adding composability to complex queries.
- odipar 5y ago
  Yes I concur: CTEs is closer to the spirit of relational algebra: every step/expression should yield a table/relation. As data munging is about combining/correlating/sorting/grouping data, why not have a sound (bag) algebra to do that? Such algebra would give us equational reasoning, proofs, etc. And consequently: students would be learning an algebra which is easier to learn IMO.
- da39a3ee 5y ago
  Thanks yes I have learned to use the WITH statement, and I agree it gives more composability. But still, a half way house wouldn't you say?

ipaddr 5y ago

One of the best ways to learn advanced oracle specifically is through the ask tom q/a. The question are difficult and the answers teach more than any course. https://asktom.oracle.com/pls/apex/f?p=100:1000 https://asktom.oracle.com/pls/apex/f?p=100:1000::::::

odipar 5y ago

My first encounter with 'SQL' was a course on relational algebra that was taught at my university. It started out with defining relations as a mathematical construct, and continued with various operators on such relations. Then they continued explaining the various normal forms up the fifth normal form. I was completely out of my depth, but at least it was good and solid theory that could be learned. What really messed with my head is they then introduced SQL as a 'practical' implementation of relational algebra. I'm still having nightmares where I try to understand nested HAVING statements that where asked at the exams. Hey relations don't contain duplicates! But that's OK. We should call (modern) SQL 'BAG ALGEBRA'.

melony 5y ago
Don't forget loops
geophile 5y ago
Yes, SQL is about bags, not sets. And anything having to do with aggregation cannot be expressed in relational algebra.

TrackerFF 5y ago

I see they mentioned previous course knowledge - this is something you see in many (programming) classes. Students that have zero prior knowledge in programming, are able to pick up functional programming pretty easy. Students that have studied and used paradigms like OOP, seem to have a hard time grokking functional programming - as they see everything through the lens of OOP (and the languages they've used).

btilly 5y ago
Functional and OOP techniques do not seem to be best friends. http://steve-yegge.blogspot.com/2006/03/execution-in-kingdom-of-nouns.html http://steve-yegge.blogspot.com/2006/03/execution-in-kingdom... uses Java to discuss what this can look like in an extreme case.

ttfkam 5y ago

Step 1: "SQL sucks!" Step 2: Let's make a database engine that doesn't use SQL. Step 3: "This is hard!" Step 4: Make SQL access layer. Wash. Rinse. Repeat. See: PartiQL Those who ignore the lessons of SQL are doomed to reimplement them...poorly.

ttfkam 5y ago
Corollary: 1. "SQL doesn't scale!" 2. We made this database engine that's "web scale"! 3. "This is hard to use!" 4. Make SQL access layer. See: Spanner Those who blame SQL for their performance problems are doomed to repeat them using a proprietary syntax.
- int_19h 5y ago
  This is completely orthogonal to having a saner query language for a relational database. SQL is like C - it does the job, but it has so many legacy warts, it's not all that hard to do better. The only real problem is overcoming how entrenched it is in the industry, which requires any replacement to be substantially better than SQL - so that the gains can justify the logistical costs of switching.

zxcvbn4038 5y ago

I can’t count how many times I’ve found developers doing “select *” and doing all of the logic and sorting themselves. That goes horribly wrong when you get to production and the database is larger then ram. Then they just stare at you and blink in total disbelief that such a thing is possible.

tanin 5y ago

I've built a desktop app where you can load a CSV and start writing SQL on it. A lower barrier to try it out might be helpful in learning SQL. See: https://superintendent.app https://superintendent.app

listenallyall 5y ago

SQL is hard not because of terrible syntax, but because the underlying logic of defining exactly what you want, is difficult. SQL, while not perfect, is very compact and direct. It allows you to express what you want succinctly and without boilerplate. No classes, no variable declarations (sure you can DECLARE a variable, but it is rarely needed), no dependencies or imports. There's a reason why, despite the promises of every BI tool that it will "simplify" your database and "empower users", none of them have toppled SQL or even added anything useful that SQL could incorporate. Graphical query designers are nice but have limited capabilities. SQL could occasionally be less verbose and IDEs could probably do better in reducing keystrokes (better autocomplete), but SQL itself, overall, is pure and brilliant.

Groxx 5y ago
Yeah - my class covered the language and meanings of the terms in a couple days. It's wonderfully simple. Which makes it hard in the same ways programming is hard - arrays are trivial! People still screw up bounds checks routinely! Figuring out what you want, and understanding your data well enough to know what's viable and what's nonsense, is infinitely harder. And it changes every time. Getting good at that part is "expertise" in a nutshell - gradually learning what strategies work and when, and getting better and better at your guesses. That takes more than a few weeks; that's an entire career.
nojito 5y ago
> There's a reason why, despite the promises of every BI tool that it will "simplify" your database and "empower users", none of them have toppled SQL or even added anything useful that SQL could incorporate. Pivoting data is still extremely painful using raw SQL
- listenallyall 5y ago
  That's a semi-valid objection but I'd argue that's not really within the scope of SQL (and the PIVOT keyword is not an official part of the language, I believe). SQL is a language to interact with databases, SELECT queries are specifically for extracting data out of a database. Pivoting is typically something you do with data you've already extracted, that currently resides in memory, which is why it is fast, there's no additional data retrieval each time you change the aggregations or filters in a pivot table. Put it this way -- a SQL-based pivot table engine, that re-queries the database with every change, would perform awfully compared to a simple Excel PivotTable. Similarly, you can't use SQL to apply color formatting to any of the result rows or values -- but that was never a goal SQL intended to achieve.

deleted 5y ago

[deleted]

jimjimjim 5y ago

gone are the days of: - SELECT name, address - FROM user, location - WHERE user.locationid = location.id

chmod775 5y ago

> This was not always the case for our participants, who wrote queries with synonyms of the correct words, leading to queries that will not be executed. This shows that those participants lack very basic foundational knowledge. It doesn't surprise me, because in my experience all programming courses that taught SQL early have been terrible. Nobody who already has a basic understanding of computer science would make this mistake. On the flipside there's really no point of devoting much time to teaching SQL later, because once you have a good understanding of data structures and algorithms, it is rather easy to make educated guesses of what is happening behind the scenes in a database - and you would have no trouble of teaching yourself SQL if necessary at some point. Not to mention that teaching databases before what makes up their implementation is teaching software development in precisely the opposite way it is practiced: the composition of lower level concepts into higher level abstractions. Last but not least, when you're teaching future software engineers, at the end you don't want them to just say "I can use this", you want them to say "I could build this". Teaching SQL early smells like surrender.