In the same vein, https://selectstarsql.com/
SQLBolt – Interactive lessons and exercises to learn SQL
71–80 of 87 posts
Re: SQLBolt – Interactive lessons and exercises to learn SQL
#72Earlier quoted context omitted.
From that point of view the $100 for the book is even less relevant.
I've been looking on brushing up on my SQL lately and have been going through these resources. - Practical SQL, No Starch Press. ($30) https://nostarch.com/practicalSQL - Use The Index Luke ($15) https://use-the-index-luke.com/ - Database Systems Concepts & Design by Georgia Tech on Udacity (free) https://www.udacity.com/course/database-systems-concepts-des... I can easily pay the $100, I cannot easily find more time…
They sound good, too. Be good to hear a review if you get any of them.
Re: SQLBolt – Interactive lessons and exercises to learn SQL
#73Earlier quoted context omitted.
I'm not sure what arbitrary symbols* as operators gets you. "It's built on concepts from relational algebra" does not mean "it must literally be a 100% compatible relational algebra mathematical engine." That is not the purpose of an RDBMS, and the purpose of relational algebra isn't to find a use for sigma, pi, and rho. Nevermind how eye-bleeding the difference between ⟖, ⟕, ⟗, ⋈, ⋉, ⋊, and ▷ will be in a sea of tab…
I just really hate seeing/typing RIGHT OUTER JOIN when it could be just ⟖. It feels like it takes me a microsecond to read the symbol, translating the glyph straight into its meaning in my head instead of subvocalizing it first and thinking what does this mean then. This takes way more time and struggle with the words. Also the whole query consisting of many such words becomes too big to read easily or split into log…
You generally don't have to specify outer since the outer is inferred by not being a straight "inner join" or more simply, "join".
I dislike writing outer too so simply don't. My joins are either:
Join
Left join
Right join
Full join
Cross join
Much better imo
Re: SQLBolt – Interactive lessons and exercises to learn SQL
#74Earlier quoted context omitted.
I haven’t read this yet but I love your take. Far too often do I see developers doing analytics by slurping an entire table across the network and performing calculations on it in the application. Of course it appears to work in development with tens of kilobytes of records and a local database, but as soon as it’s deployed to production it unleashes chaos. I consider myself very good at SQL but I do prefer to use OR…
One argument I've often heard is that since it's easier to scale your application compared to your database, pushing the calculations on your application is a way to get a better scalability in the long term. I wonder how true that is though.
I’ve seen applications go down hundreds of times from pulling entire tables locally to compute on them. I’ve seen maybe once where an application grew so successful that scaling the database became an extremely challenging topic.
And even in that case, I/O and contention were inevitably the problems. Not CPU.
I’ve similarly heard myths that you should be judicious when writing indexes because they can affect insertion performance. I’ve again seen hundreds of cases where under-indexing killed performance and zero where over-indexing caused problems.
99.9% of the time, you’re not the crazy special case. And if somehow you are, the solutions required are going to be nuanced and involve a ton of specific measurement. It’s widely unlikely you’ll accidentally avoid these problems through something like this.
Re: SQLBolt – Interactive lessons and exercises to learn SQL
#75I'm of the firm opinion that you can have a very successful career purely as an SQL genie.
We are trying to revive the art in our shop. Just got done building a really clean 3NF model for our problem domain and we are in the process of moving 100% of our custom configuration needs to SQL queries. From a biz strategy perspective, we can scale up a lot faster if all we need to do is find people who know (or can be taught) SQL. Consider the amount of time it would take to ramp someone on 1 SQL schema vs the e…
This is great if you have highly-connected client apps. Its not as doable when you can't guarantee connectivity. In those cases, you need to ship/duplicate business logic to the clients to work around the network issues. Its a hard problem.
Re: SQLBolt – Interactive lessons and exercises to learn SQL
#76Earlier quoted context omitted.
I remember looking at a MS Dynamics AX database, not hugely complex by ERP standards with about 5000 tables, and realising it didn't use foreign keys. That was fun.
I can, sadly, one up you on that: The same table stores: Addresses (primary, secondary), sex, names (up to 3), date of birth, what currency, and many MANY more values. Yes, that's one table. Also: Another table literally stores full tables in it. (Basically some kinda key with which to identify the subtable so you can select on it.) Progress has no real concept of set based queries, instead it accesses all tables lik…
> The same table stores: Addresses (primary, secondary), sex, names (up to 3), date of birth, what currency, and many MANY more values.
Some denormalization is sometimes warranted. As for the rest I agree with you. Sounds like madness.
Re: SQLBolt – Interactive lessons and exercises to learn SQL
#77Earlier quoted context omitted.
I can, sadly, one up you on that: The same table stores: Addresses (primary, secondary), sex, names (up to 3), date of birth, what currency, and many MANY more values. Yes, that's one table. Also: Another table literally stores full tables in it. (Basically some kinda key with which to identify the subtable so you can select on it.) Progress has no real concept of set based queries, instead it accesses all tables lik…
> The same table stores: Addresses (primary, secondary), sex, names (up to 3), date of birth, what currency, and many MANY more values. Some denormalization is sometimes warranted. As for the rest I agree with you. Sounds like madness.
Denormalization implies having it thought out
This DB is many things, but most definitely not thiught out.
Basically nothing is normalized.
Re: SQLBolt – Interactive lessons and exercises to learn SQL
#78Earlier quoted context omitted.
> The same table stores: Addresses (primary, secondary), sex, names (up to 3), date of birth, what currency, and many MANY more values. Some denormalization is sometimes warranted. As for the rest I agree with you. Sounds like madness.
Denormalization implies having it thought out This DB is many things, but most definitely not thiught out. Basically nothing is normalized.
I distinguish that practice as “Abnormalization” (HaHaOnlySerious).
Re: SQLBolt – Interactive lessons and exercises to learn SQL
#79Earlier quoted context omitted.
I remember looking at a MS Dynamics AX database, not hugely complex by ERP standards with about 5000 tables, and realising it didn't use foreign keys. That was fun.
I can, sadly, one up you on that: The same table stores: Addresses (primary, secondary), sex, names (up to 3), date of birth, what currency, and many MANY more values. Yes, that's one table. Also: Another table literally stores full tables in it. (Basically some kinda key with which to identify the subtable so you can select on it.) Progress has no real concept of set based queries, instead it accesses all tables lik…
In some cases, we aren't in a position to normalize the database due to legacy situations.
However, one thing that could be done is to use views (maybe scoped in their own schema) to make the database look normalized (Facade the database). This would help with writing future SQL.
It is then possible to iteratively normalize the underlying tables by pointing legacy code at the normalized views until the denormalized tables are no longer in use. Finally, "convert" the views into tables and drop the denormalized tables.
Re: SQLBolt – Interactive lessons and exercises to learn SQL
#80Earlier quoted context omitted.
I can, sadly, one up you on that: The same table stores: Addresses (primary, secondary), sex, names (up to 3), date of birth, what currency, and many MANY more values. Yes, that's one table. Also: Another table literally stores full tables in it. (Basically some kinda key with which to identify the subtable so you can select on it.) Progress has no real concept of set based queries, instead it accesses all tables lik…
In some cases, we aren't in a position to normalize the database due to legacy situations. However, one thing that could be done is to use views (maybe scoped in their own schema) to make the database look normalized (Facade the database). This would help with writing future SQL. It is then possible to iteratively normalize the underlying tables by pointing legacy code at the normalized views until the denormalized t…
That is actually basically what we are doing. Except the whole application gets replaced too.
(I have to write the sync code from the new system to the old one)