5. Tricky One

Display department id, department name, city and number of different jobs in each department. If city is null, you should print Not Assigned Yet. This column should have alias City. Column that shows # of different jobs in a department should have the heading # of Jobs.

You should display ONE row per output line by limiting the width of the City to 25 characters.

You need to show complete situation from the department point of view,

meaning empty departments (without employees) and in no city located yet, but do NOT display free lancers (employees NOT working for any department) and do NOT display cities without departments.

You need to use Outer Joining with the NEW method.

But, firstly you will need to update a row in the DEPARTMENTS table like:

SQL> UPDATE departments

SET location_id = null

WHERE department_id = 110;

1 row updated.

Then after producing the output you will perform the Rollback.

SQL> ROLLBACK;