Oracle SQL: Formatting Strings, DECODE, UPPER(), LOWER()


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!

Advertisements

About Anatoly Spektor

My name is Anatoly Spektor (originally Anatolijs Spektors) I am Software and Web Developer. I have worked in Seneca Center for Development of Open Technology on Big Blue Button Add-on - Polling Module, Red Hat and some other places :) I am an author of the book 'Eclipse Debugging How To', Muay Thai fighter and amateur photographer ;)
This entry was posted in Database, ORACLE, SQL and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s