Task 2 : Solution, how to generalize it?

Question

In task 2, the question is to

"

Return a list with the first 10 employees with all the departments they can be assigned to.

Hint: Don’t use LIMIT; use a WHERE clause.

"

In it's solution we have the line of code:

"

WHERE

e.emp_no < 10011

"

Here we have written emp_no < 10011 because we only want the cross join with first 10 employees, but we are only able to use the number '10011' because we know the starting number of emp_no.

Is there any way to generalize the code in such a way that even if we don't know the emp_no of first 10 employees we are still able to perform the same operation.

It could be helpful in a case where we have a emp_no missing, maybe because that employee is no more in the company and the same emp_no is not given to any other employee yet.

Regards,

Yayin Vashist

Answer 1

Hi Yayin!

Thanks for reaching out.

You can achieve this with the help of a subquery. Please, use the following code:

SELECT e.emp_no, d.dept_name
FROM employees e
CROSS JOIN departments d
WHERE e.emp_no IN (
    SELECT emp_no
    FROM (
        SELECT emp_no, ROW_NUMBER() OVER (ORDER BY emp_no) AS row_num
        FROM employees ) AS numbered_employees
    WHERE row_num <= 10 ) ;

Hope this helps.
Kind regards,
Martin

Task 2 : Solution, how to generalize it?

Submit an answer

related questions