Question

SQL

From employees table, display the first_name, last_name, phone_number and Phone number Type. Phone number type should be "USA Phone Number" (Length=12) or "International Phone Number" (Length=18) or "Unknown" based on the length of the phone number. Sort the display in desc order of phone number type

Use Either CASE or DECODE.

Output Column Heading: First Name, Last Name, Phone Number, Phone Number Type

Answer 1

NOTE: BELOW QUERIES ARE IN MS SQL SERVER

If "phone_number" column in SQL is a TEXT column then consider below query:

select first name as [First Name], last name as [Last Name], phone_number as [Phone Number], case when LEN (phone number) = 12 THEN 'USA Phone Number when LEN (phone number) = 18 THEN 'International Phone Number else 'Unknown' end as [Phone NumberType] from employees order by [Phone Number Type] desc

If "phone_number" column in SQL is a NUMERIC column then consider below query:

select first name as [First Name] , last_name as [Last Name], phone_number as [Phone Numberl, case when LEN (CONVERT (varchar (50),phone_number)12 then 'USA Phone Number' when LEN (CONVERT (varcha r (50), phonenumber))-18 then ,International Phone Number else 'Unknown' end as [Phone Number Type] - from employees int order by [Phone Number Type] desd
OUTPUT

E ResultsMessages First Name ast Name Phone Number 1 Franklin Jones Phone Number Type USA Phone Number USA Phone Number Unknown Unknown 100252248555 2 Nick 3 Robert James 7543010 4 Steve 5 Mania Thomas 100252248555 Jobs 5417543010 Sharapova 100252248555985963 Intemational Phone Number