In my experience I have used many queries like select, order by, where clause etc.. in mysql, sql-server, oracle etc For a moment i have thought,
1)how is this internally written to implement the above queries
2) which language do they use?
3) is that programming language? if yes which language?
4)what kind of environment required to implement this kind of complex database
I believe the Oracle DBMS was all originally written in C, and probably still is.
MySQL is written in C,C++ according to the MySQL Launchpad page
Check the code to find out more about how MySQL is implemented.
Have a look at the Postgresql code.
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.
In many (all?) databases you can view an explain/execution plan. This will give you a rough approximation of what is going on inside the database. In commercial RDBMSes, that's the closest you will get to any understanding of how the optimizations work. The algorithms in the query analyzers are closely guarded secrets.
Joe Chang wrote an excellent article about how SQL Server's cost based optimizer works
Likewise you can find similar information about PostgreSQL and MySQL