WHERE clause in solution requires explicit knowledge of individual records
I feel a little cheated by the solution given. To get the first ten employees and all possible department combinations for each, you have to explicitly know the first ten employees numbers. What if I wanted the last ten? Or if the department managers changed? I would have to explicitly know the employee numbers.
I tried to find a way to select the first ten employees from the dept_manager table without first knowing them. I came up with this:
SELECT dm.*, d.* FROM departments d CROSS JOIN dept_manager dm WHERE dm.emp_no < (SELECT (MIN(emp_no)+10) FROM dept_manager) ORDER BY dm.emp_no ASC, d.dept_no ASC;
This would have worked in the regular employees table since each record is exactly one higher than the last but department managers don't follow that rule. The first manager is employee number 110022 and the next is 110039 so my solution won't work without also knowing the employee numbers in the table before I write the code.
It might be a little more advanced than where we are now but how can I retrieve the first ten records WITHOUT knowing the employee numbers beforehand? Or maybe if the managers changed at a later date and I needed to pull this same query?
You can sort by the employee number and apply a limit of 10 at the end of the query, that should produce the result you are looking for.