Categories

Country Code

Date
Author

A company wants to contact each of its customers regarding the policy changes. They have international customers, but their database does not include country codes with their phone numbers.

There are two tables in the database: customers and country_codes. The first table contains details of every customer including customer_id, name, phone_number, and country. The second table contains the country code for every country.

Write a query to retrieve a list of all customer ids, names, and phone numbers, with their country codes concatenated with their phone numbers. Sort the output in the order of their customer_id.

Note: The phone number should be in the following format: +COUNTRY_CODE PHONENUMBER (without spaces)

Schema

There are 2 tables: customers, country_codes.

customers
NameTypeDescription
customer_idINTEGERThis is the unique customer id. It is the primary key.
nameSTRINGThe name of the customers having [1, 20] characters.
phone_number

STRING

The phone number of the customer having [6,13] characters.

countrySTRING

The country of the customer. It is a foreign key taken from country_codes

 

country_codes
NameTypeDescription
countrySTRINGThe name of the country. It is the primary key.
country_codeSTRINGThe country code of this country.
Sample Data Tables 
customers
customer_idnamephone_numbercountry
1Raghav951341341India
2Jake52341351USA
3Alice61341351USA

 

country_codes
countrycountry_code
USA1
India91

Sample Output

1 Raghav +91951341341
2 Jake +152341351
3 Alice +161341351

Explanation:

  • Since Raghav is from India, the country code is 91, so the complete phone number is +91951341341
  • Since Jake is from the USA, the country code is 1, and the complete phone number is +152341351
  • Since Alice is from the USA, the country code is 1, and the complete phone number is +161341351

SOLUTION:

				
					SELECT a.customer_id, a.name, CONCAT("+", b.country_code, a.phone_number) AS full_phone_number
FROM customers AS a
LEFT JOIN country_codes AS b ON a.country = b.country
ORDER BY a.customer_id;
Tags basiccertificationcertifyhackerranksql

You Might Also Like:

Something That I Don’t Understand Myself
LinuxNerdy
APNG to GIF script
Nerdy
What I Need to Do After Installing My VPS
LinuxNerdy