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:

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:

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!

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.