w3resource

AdventureWorks Database: Form one long string to display the last and first name

SQL Query - AdventureWorks: Exercise-192 with Solution

192. From the following table write a query in SQL to form one long string to display the last name and the first initial of the vice presidents. Sort the result set in ascending order on lastname.

Sample table: sales.salesorderheader


Click to view Full table

Sample Solution:

SELECT concat(LastName ,',' ,' ' , SUBSTRING(FirstName, 1, 1), '.')  AS Name, e.JobTitle  
FROM Person.Person AS p  
    JOIN HumanResources.Employee AS e  
    ON p.BusinessEntityID = e.BusinessEntityID  
WHERE e.JobTitle LIKE 'Vice%'  
ORDER BY LastName ASC;

OR

SELECT LastName || ',' ||' ' || SUBSTRING(FirstName, 1, 1) || '.'  AS Name, e.JobTitle  
FROM Person.Person AS p  
    JOIN HumanResources.Employee AS e  
    ON p.BusinessEntityID = e.BusinessEntityID  
WHERE e.JobTitle LIKE 'Vice%'  
ORDER BY LastName ASC;

Sample Output:

name        |jobtitle                     |
------------+-----------------------------+
Duffy, T.   |Vice President of Engineering|
Hamilton, J.|Vice President of Production |
Welcker, B. |Vice President of Sales      |

SQL AdventureWorks Editor:

Practice Online


Contribute your code and comments through Disqus.

Previous: Concatenate character and date data types.
Next: Return with a product line of R and a days to manufacture less than 4.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

ROW_NUMBER() in MySQL

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

Ref : https://bit.ly/3VX3Jzv