Write an SQL query to get the list of customers' full names with their countries and phone numbers The customer full name is a space delimited concatenation of the title (if it exists), first name, and last name fields If the suffix field is present, it should be appended enclosed in parenthesis In other words, output the full name in the following format (suffix) Output phone numbers in the following format Be sure to include all customers, even if they are missing some information Sort the results alphabetically by country and then by phone number in ascending order within each country I currently have this select CONCAT(c title,' ' ,c first name, ' ',c last name,' ',c suffix) AS full name , pa country, concat(p phone country code, ' ', p phone area code, ' ', p phone number) as phone number from customer as c from customer table with an alias name c left join customer address AS ca on c customer id ca customer id left join physical address AS pa on ca physical address id pa physical address id left join phone number as p on c customer id p customer id order by pa country, p phone number asc but I am unsure of how to put the parenthesis arround suffix if exists and the order seems to be off

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 22, 2024

Write an SQL query to get the list of customers' full names with their countries and phone numbers. The customer full name is a space-delimited

Write an SQL query to get the list of customers' full names with their countries and phone numbers.

The customer full name is a space-delimited concatenation of the title (if it exists), first_name, and last_name fields. If the suffix field is present, it should be appended enclosed in parenthesis. In other words, output the full name in the following format:

<first_name> <last_name> (suffix) Output phone numbers in the following format: <phone_country_code>-<phone_area_code>-<phone_number></phone_number></phone_area_code></phone_country_code> Be sure to include all customers, even if they are missing some information. <img alt="image text in transcribed" src="https://dsd5zvtm8ll6.cloudfront.net/si.experts.images/questions/2024/09/66f0cbce4e40b_84666f0cbce23aa9.jpg" style="height:754px; width:1218px"> Sort the results alphabetically by country and then by phone number in ascending order within each country. I currently have this: select CONCAT(c.title,' ' ,c.first_name, ' ',c.last_name,' ',c.suffix) AS "full name", pa.country, concat(p.phone_country_code, '-', p.phone_area_code, '-', p.phone_number) as "phone number" from customer as c -- from customer table with an alias name c left join customer_address AS ca on c.customer_id = ca.customer_id left join physical_address AS pa on ca.physical_address_id = pa.physical_address_id left join phone_number as p on c.customer_id = p.customer_id order by pa.country, p.phone_number asc but I am unsure of how to put the parenthesis arround suffix if exists and the order seems to be off </last_name></first_name>