Even if you didn't need to write it in your code, it's pretty handy to be able to use it when you have terminal access to a database server.
Also, most of what makes programming a challenge is working within the restrictions that life sets us- often we are working with old code, or old versions of databases and don't have the opportunity to install the latest ORM library for whatever language we are working with. In that situation you will need any tool at your disposal.
The rest of the time you may not need SQL for your CRUD stuff, but there is a lot more to SQL than simple SELECT, INSERT, UPDATE and basic JOIN queries. You can do very clever things with it and although you may not use them often, it's useful to know what they are.
Increasingly, I think we will find ourselves in a post SQL world, however- most of the Cloud services use non-sql table storage and for simple CRUD type work the full power of SQL is unnecessary. But that doesn't mean there will be no value in understanding it.
Also, of course, someone has to know enough to write a better ORM system if the current ones aren't up to much. It would help them if they knew SQL...