Why my query does not return correct data (NORTHWIND)?
There are two orders with an orderdate=1997-09-05.  One for employee=4 and one for employee=7.  Now, for employee=4, there are 156 rows on the order table and for employee=7, there are 72 rows on the order table.  That totals to 228.  Your query is returning all orders where the employeeid matches those two id's.

Alter your query to include a check for o2.OrderDate=o.OrderDate

SELECT *
FROM orders o
WHERE EXISTS (
        SELECT 1
        FROM orders o2
        WHERE o2.EmployeeID = o.EmployeeID
            AND YEAR(o2.OrderDate) = 1997
            AND MONTH(o2.OrderDate) = 9
            AND DAY(o2.OrderDate) = 5
            AND o2.OrderDate = o.OrderDate
        )




Based on your example, you would get the same results by simplifying the query to:

select * from orders where orderdate = '1997-09-05'