Out here watching some Russian #bot#spam my #game server in real time with a wide variety of #sql injection attacks this morning and suddenly realized how much less often I have to deal with this shit since #crypto mining became a thing..
An example:
```
SELECT common_name, COUNT(*)
FROM biology
WHERE common_name LIKE '%lady%'
GROUP BY common_name
ORDER BY common_name;
```
gives the same results as
```
SELECT common_name, COUNT(common_name)
FROM biology
WHERE common_name LIKE '%lady%'
GROUP BY common_name
ORDER BY common_name;
```
when there are no nulls (missing values) in the common_name field.
And without some clues in the assignment text, it isn't possible to know which one their JS-based checker will accept. So in the majority of assignments, I get things like this "wrong" in the first pass.
I've lately been vocal about my perception that quality control and beta testing should have exposed such things and led to their correction before deployment to paying customers.
In the case of the #SQL courses, I did recently have a multi-part assignment where they asked for the NOT NULL version from the start, but only in the 3rd or 4th part of the assignment did they expressly say they wanted that and use pre-entered SQL scaffolding to show why it matters in that specific case.
In other news, I've temporarily de-emphasized both #Python and #R-lang (and delayed my exploration of #Julia) because I'm on a "track" that focuses on SQL. At the current rate, I should be finished with the SQL track & emphasis in a month or so.
I've also noticed they have some courses that cover MSExcel / PowerBI, Tableau, Google Sheets, and (of course) "AI". I expect to take the intro courses for most of these topics. I don't typically use spreadsheets except as gridded formatting tools for lists, but many years ago I used Lotus 1-2-3 and Quattro Pro and even took some classes. So relearning such things as formulas and internal scripting may be helpful in making these tools more broadly useful.
I have used joins and subqueries (in the where clause) in the past. But when I look at many of the exercises, there's a partially-written #SQL query that uses these features and CTEs and it is difficult to reason about the query and its pieces. Normally, that's when I'd write some exploratory queries to understand how to go from a set of tables to a specific result set (itself a table).
But the table rows seen in the preview may not be easily visible in the query results, which makes it more difficult to see whether one is on the right track.
With SQL, at least, it seems to be an artifact of the way their hands-on code runner works (Displays a short `head` of the relevant tables ... so when you're working on queries, you may not have a direct way to see whether your query does specifically what you expected and intended.)
With R-Lang, it is just that it isn't always apparent what the language will do. Some things are inexplicably backwards compared to most other languages I've seen, so mentally I tend to go with the wrong choice. Also, the practice question set is too small. I've reached the point where some of the practice exercises are familiar enough that I know which answer to choose immediately without having any understanding of why that is the correct choice.
People wonder why I like ORMs even when they're unnecessary. Firstly, I've never liked SQL. I think that writing queries to a RDBMS is something that a computer should do, akin to compilation. In the few times when extreme optimization is warranted, low level code can be generated to suit that specific case. In other times, ORMs usually provide a more natural interface to data that increases readability and code flow.
@aral@jan It is insanely bad query programming to have 3.5k IDs in WHERE clause 🤯 Considering table is properly indexed and vacuumed, I would split them into 100-200 microbatches and if each involves pinging a separate server of a follower account, even make those batches smaller. LIMIT and OFFSET should be the start from database side and then some task queueing from backend 🧐 #postgresql#sql#performance :postgresql:
#intro I'm an old netizen coming from IRC and the dawn of open source and the web. I work as an engineer in the #observability team at digital ocean. I'm firmly of the "visual" camp, enjoying art, design & math made pretty. My favorite #scifi novel remains the shockwave rider, my favorite movie blade runner. I enjoy #go AND #js. I really like #SQL. I'm a political scientist by training so I do antifa , eco, feminism & privacy since 1983. I love cats and my partner O. and chocolate and cheese. :)