Case:
Write a query that displays the employee’s Full Name and Job Title in the following format:
DAVIES, CURTIES is Store Clerk
for all employees whose last name ends with S and first name starts with C or K.
Give this column appropriate label like Person and Job
Sort the result by the employees’ last names.
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT Upper(FIRST_NAME) || ' , ' || Upper(LAST_NAME) || ' is ' || DECODE(job_id, 'IT_PROG', 'Programmer', 'AC_ACCOUNT', 'Accountant', 'AC_MGR', 'Manager', 'ST_CLERK', 'Sales Clerk', 'SA_REP', 'Sales Rep', 'ST_MAN', 'Sales Manager', 'Not Known') "Person and Job" FROM EMPLOYEES WHERE last_name LIKE LOWER('%s') AND ( first_name LIKE UPPER('c%') or first_name LIKE UPPER('k%') ) ORDER BY last_name desc |
Output:
|
1 2 3 4 |
Person and Job --------------------------------- KEVIN , MOURGOS is Sales Manager CURTIS , DAVIES is Sales Clerk |
Explanation:
It was the hardest Case I had for today, but I finally managed it. So first of all we need to SELECT last_name, first_name, make sure that they are uppercase by using function UPPER, also pipe them together with “||” operator. Also i used comma and “is” as connectors of strings. After getting Full Name done we definitely need to create kind of hash tables, that will change our Job_ID to corresponding description. Unfortunately, database does not have job_desc field, so I needed to code every job_id, I was lucky that there was only few of them. The last “Not Known” value is shown where there is NULL in the Job_ID. We call our column “Person and Job”. All columns were taken from one table called Employees. Also we are checking for “s” last letter and “c” or “k” first letters. I used UPPER there to make my search consistent. I also ORDER my output by Last Name in alphabetic order.
That’s it!
Have Fun!
