What kind of a query is this? How does it work?

SQL coins the term query as the name for its commands. Basically, all SQL code is written in the form of a query statement and then executed against a database. All SQL queries perform some type of data operation such as selecting data, inserting/updating data, or creating data objects such as SQL databases and SQL tables. Each query statement begins with a clause such as SELECT,UPDATE, CREATE or DELETE. SQL Query Examples:-----

Code
-- Inserts data into a SQL Database/Table
INSERT INTO orders (customer,day_of_order,product, quantity)
VALUES(Tizag,8/1/08,Pen,4);
 
-- Selects data from a SQL Database/Table
SELECT * FROM orders;
 
-- Updates data in a Database/Table
UPDATE orders SET quantity = 6
WHERE id = 1

Youre using a Sub-query -- [(select dname from dept where deptno = e.deptno)] -- to get around using a join clause like -- [FROM EMP E INNER JOIN DEPT D ON (D.DEPTNO = E.DEPTNO)] -- however, you are not actually getting away with not joining the two tables, youre just defining the join in a more complex method. For future coding of your queries it would be better to follow the guide line of Occams razor: "All things being equal, a simpler explanation is better than a more complex one."
To that end, a query that uses a JOIN clause to relate tables is easier to understand, than a query that tries to get around using a JOIN clause.