The normal forms are about how data is stored, rather than how it looks after a query. After all, most joins will violate second normal form.

But yes, SQL doesn't work well with this solution.

1nf is surprisingly tricky to define.

If you consider it as a single atomic list then it is still in 1nf.

It's only not in 1nf with respect to future queries IF you want to subselect from within the list values.

1NF is defined as eliminating nested relations. I assume "[ cryptography, databases]" in the example represent a nested relation.

1NF is not defined in terms of what you want to do with the the data, it is defined in terms of domains, i.e data types. If a column allows nested tables, it violates 1NF.

"Atomic" in the context of 1NF means atomic wrt. the relational algebra.

Eg. if we have:

   Student: Bob, classes: [ cryptography, databases]
   Student: Alice, classes: [] 

Is it possible to select from this all students which has cryptography as a class, using the standard relational operators (project, filter, join)?

I think we're mostly in agreement!

You're right, it's not possible to select from this all students which has cryptography as a class. No more than if we did concat('cryptography', ',', 'databases').