> It's interesting to note that the results depend on the order of the 'A' and 'a'... I'm not sure it's meaningful or not.
No its not because they are the same when compared using collate nocase. Which one you get depends on which one is "first" in the comparison. And while which one is "first" in the comparison is entirely deterministic, it is an implementation detail on which you should not rely.
> I'm not a language expert but in the example I think the "collate nocase" is being assigned to the column
No. The collation applies to the compare operator, although it is "carried along as an attribute of a column or a data item", the collate applies to the comparison operator. That is to say that `'A' collate nocase` has absolutely no effect on the value of `'A'` but rather on `comparison operations` which refer to the value.
> so if I added a FROM the COLLATE would need to be attributed to the column and not the from... (no examples in the doc that I could see)
Cannot parse your meaning here.
> can there be multiple collate attributes(can they be stacked)?
Cannot parse your meaning here either. If you mean can a single comparison be carried out with multiple collations, the answer is no. In the face of multiple collations applying to a operation, the documentation explains how the "winning collation in the tournament" as selected. If you mean can you specify a chain of comparisons and have a different collation attached to each comparison, the answer is yes.
> I read someplace that NOACCENT was left to the programmer? The reason given was that it was too big to implement. SHRUG. Given the use-cases for SQLite one might think it should be baked in.(see below)
This is correct. Even the NOCASE collation only handles plain ASCII. Plain ASCII does not have accents. If you wish to handle a larger character set than plain ASCII, you have to provide your own collation (comparator function).
If you want to extend the collation comparator functions you can do so. There are numerous extensions to do this including built-in support for using ICU.