I'm working on an assignment and am having trouble with this question:
*Display the department name and the name of all employees plus their manager status. Status should show message ‘is a Manager’ for those who are managers. For those who are not managers show the message ‘is NOT a Manager’.
Include also empty departments as well, where the status value should be ‘NO Manager yet’
Display those people who are managers first followed by those whoa are NOT managers and empty departments last. Within those groupings sort by the employee name alphabetically.
Here is the heading sample.
Department Name Employee Manager Status*
I know NVL can be used to account for null values, but this question's stumped me.
The tables relevant to this question are:
emp:
Name Null Type
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NOT NULL NUMBER(2)
dept:
desc dept
Name Null Type
------ -------- ------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
If anyone could give me even a hint as to which function I could possibly use to display the strings, instead of the values I would be very greatful!
Depending of which database you use there is several ways to view strings instead of NULL results. For your DB try look at NVL, NULLIF, NVL2, COALESCE, CASE-WHEN clauses (Oracle) or IIF function (Firebird/Interbase). I don't know which DB you use but most of these are common for most popular DBs.