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
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.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises