If you compare C vs SQL, this is the argument:
In contrast to procedural languages such as C, which describe how things should be done, SQL is nonprocedural and describes what should be done.
So, the how part for languages like SQL is specified by the language itself, is it? What if I want to change the way some query works. Suppose I want to change the way a SELECT is handled. Is that possible?
This is actually exaggerating the difference. There is no clear-cut point at which one is telling how things are done and the other only telling what it done. Rather, one may have to specify what/how things are done at a greater level of detail than the other. A typical SQL implementation allows the user to control such things as what indexes are used (or ignored), what kind of locking to do, and so on.
If you were to do the same job in C, you would (at some point) have to specify a great deal more detail (unless you used something like ODBC). Nonetheless, you're still telling what should be done, not all the details of how it should be done (e.g., despite being about as low-level as possible short of assembly language, C will still do some type conversions automatically, so you don't have to tell it how to do something like adding an integer to a floating point number -- you just tell it to add them, and it handles the details).
Bottom line: trying to talk about one as procedural and the other as non-procedural is misleading. SQL doesn't always require as much detail, but it's a difference of degree, not really "how" versus "what".
So, the how part for languages like SQL is specified by the language itself, is it?
Not strictly by the language (ie. SQL), but normally by the database and its optimiser. As such, even where the same data is being queried from tables with the same structures and the same indexes, some databases will build the resultset in a different way to others.
Suppose I want to change the way a SELECT is handled. Is that possible?
To some degree, yes. You can either:
Neither of these directly instruct the database engine which approach to use, but both of them will affect how the resultset is returned - this is likely to vary between databases.
Additionally, I understand that some databases have additional interfaces that allow more low-level interaction with the database engine, enabling greater control over how a query is built than is possible from plain SQL. (However, your question did specify SQL.)