Oracle SQL: 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:

Output:

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!

Anatoly Spektor

IT Consultant with 6 years experience in Software Development and IT Leadership. Participated in such projects as Eclipse IDE, Big Blue Button, Toronto 2015 Panam Games.

Join the Discussion

Your email address will not be published. Required fields are marked *

arrow