9. Write a SQL query, which shows a list of all employees last name, their depar

Question

9. Write a SQL query, which shows a list of all employees last name, their department names and their department city where that department is located from the employees table. If an employee does not belong to a department, then their department number should show as “N/A” and their department city should show up as “No City info Found”. MAKE SURE you have all the employees displayed including the employees who do not have a department.

(Here, the employees and departments table are two different tables. The last name coloumn is in the employees table and the department name and department city is in the departments table.)

Explanation / Answer

SELECT a.last_name,
DECODE(b.department_name, NULL, 'N/A', b.department_name) AS department_name, DECODE(b.department_city, NULL, 'No City Info Found', b.department_city) AS department_city
FROM employee_table a
LEFT OUTER JOIN department_table b
ON a.department_id = b.department_id;

Related Questions

Navigate

• Browse (All)
• Browse 9
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.