SQL queries contain clauses like select, order by and where.
How is this internally written?
Which language do they use?
Is that a programming language?
What kind of environment is required to implement this kind of complex database?
1) In schematic form you would proceed as follows :
a) Split the query in its components and create a Abstract Syntax Tree (AST) of the query. There are tools to do this, in the olden days lex and yacc were used for this, now there is a lot more choice.
b) In a first step an optimizer will reorganize the tree by applying known equal transformations so the query will be most performant way by using indexes, doing queries which return little results first so you have less to join, etc....
c) You can walk this tree to implement the small operations on the database and the data returned. Typically this results in "virtual temporary" tables in the nodes of your AST
d) Collect the stuff from your top node and return it to the client
2-3) I do not think there are special languages. Many are in C, but there are Java and other languages used too
4) I think the best environment is a quiet environment for this kind of work. ;-)
The real hard work is not in the SQL interpreter/compiler but in the detailed datastructures and the nitty gritty of keeping everything efficiently organised and dynamically tuned to the situation in order to keep the database performant.