Q:

What would the query be for the following sample table?

Can you please help me with a query that would display a table like this:

Dept_ID                Dept_Name

10                     Admin
10                     Whalen
20                     Sales
20                     James
20                     King
20                     Smith
40                     Marketing
40                     Neena

and so on...The Schema is HR
Display the Department Id and the Department Name and then the subsequent employees last names working under that department 

A:

When you union two data sets, there is NO implicit ordering, you could get the results in any order.
The get a particular order you must use ORDER BY.
To use ORDER BY, then you must have fields to do that ordering by.
In your case, the pseudo code would be...
- ORDER BY [dept_id], [depts-then-employees], [dept_name]
The middle of those three is something that YOU are going to have to create.
One way of doing that is as follows.
note: Just because you have a field to order by, does not mean that you have to select it.
SELECT
    dept_id,
    dept_name
FROM
(
    SELECT
        d.dept_id,
        d.dept_name,
        0   AS entity_type_ordinal
    FROM
        department   d

    UNION ALL

    SELECT
        d.dept_id,
        e.employee_name,
        1    AS entity_type_ordinal
    FROM
        department   d
    INNER JOIN
        employee     e
            ON  e.dept_id = d.dept_id
)
   dept_and_emp
ORDER BY
   dept_id,
   entity_type_ordinal,
   dept_name

