To set the range of records that will be used for each "GROUP" within the OVER clause, you can use PARTITION BY clause.
In above code:
SQL, DEPT_COUNT will return the number of employees within that department for every employee record.
It is like you're de-nomalising the emp table; and you still return every record in the emp table.
emp_no dept_no DEPT_COUNT
1 10 3
2 10 3
3 10 3 <- Here, 3 because there are three "dept_no = 10" records
4 20 2
5 20 2 <- Here, 2 because there are two "dept_no = 20" records
If there was another column (say state) then you would count how many departments in that State.
It is like getting the results of a GROUP BY clause (SUM, AVG, etc.) without aggregating the result-set (i.e. removing matching records).
Example:
This is helpful once you use the LAST OVER or MIN OVER functions to induce, the lowest and highest salary within the department and so use that in an exceedingly calculation against this records salary while not a sub choose, that is far quicker.