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: HumanResources.Employee

businessentityid|nationalidnumber|loginid                     |jobtitle                                |birthdate |maritalstatus|gender|hiredate  |salariedflag|vacationhours|sickleavehours|currentflag|rowguid                             |modifieddate           |organizationnode|
----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+
               1|295847284       |adventure-works\ken0        |Chief Executive Officer                 |1969-01-29|S            |M     |2009-01-14|true        |           99|            69|true       |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/               |
               2|245797967       |adventure-works\terri0      |Vice President of Engineering           |1971-08-01|S            |F     |2008-01-31|true        |            1|            20|true       |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/             |
               3|509647174       |adventure-works\roberto0    |Engineering Manager                     |1974-11-12|M            |M     |2007-11-11|true        |            2|            21|true       |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/           |
               4|112457891       |adventure-works\rob0        |Senior Tool Designer                    |1974-12-23|S            |M     |2007-12-05|false       |           48|            80|true       |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/         |
               5|695256908       |adventure-works\gail0       |Design Engineer                         |1952-09-27|M            |F     |2008-01-06|true        |            5|            22|true       |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/         |
               6|998320692       |adventure-works\jossef0     |Design Engineer                         |1959-03-11|M            |M     |2008-01-24|true        |            6|            23|true       |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/         |
               7|134969118       |adventure-works\dylan0      |Research and Development Manager        |1987-02-24|M            |M     |2009-02-08|true        |           61|            50|true       |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/         |
               8|811994146       |adventure-works\diane1      |Research and Development Engineer       |1986-06-05|S            |F     |2008-12-29|true        |           62|            51|true       |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/       |
               9|658797903       |adventure-works\gigi0       |Research and Development Engineer       |1979-01-21|M            |F     |2009-01-16|true        |           63|            51|true       |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/       |
              10|879342154       |adventure-works\michael6    |Research and Development Manager        |1984-11-30|M            |M     |2009-05-03|true        |           16|            64|true       |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/       |
			  -- more --

Click to view Full table

Sample Solution:

-- Concatenating the Last Name, a comma, a space, the first initial of the First Name, and a period, and aliasing it as Name
SELECT CONCAT(LastName, ',', ' ', SUBSTRING(FirstName, 1, 1), '.')  AS Name, e.JobTitle  
-- From the Person.Person table aliased as p, and the HumanResources.Employee table aliased as e
FROM Person.Person AS p  
    JOIN HumanResources.Employee AS e  
    -- Joining the tables on the BusinessEntityID column
    ON p.BusinessEntityID = e.BusinessEntityID  
-- Filtering records where the JobTitle starts with 'Vice%'
WHERE e.JobTitle LIKE 'Vice%'  
-- Ordering the result set by LastName in ascending order
ORDER BY LastName ASC;

Explanation:

• This SQL query retrieves data from the Person and Employee tables.
• Comments are added to explain each part of the query for better understanding and maintenance.
• Here's a breakdown of what the query does:
• The SELECT clause concatenates the Last Name, a comma, a space, the first initial of the First Name, and a period, and aliases it as 'Name'.
• The CONCAT function is used to concatenate multiple strings together.
• The SUBSTRING function extracts the first character of the First Name.
• The FROM clause specifies the Person.Person table aliased as 'p' and the HumanResources.Employee table aliased as 'e'.
• The JOIN clause joins the Person and Employee tables on the BusinessEntityID column.
• The WHERE clause filters records where the JobTitle starts with 'Vice%'.
• The LIKE operator is used for pattern matching with 'Vice%' matching any title starting with 'Vice'.
• The ORDER BY clause sorts the result set by LastName in ascending order.

OR

-- Concatenating LastName, a comma, a space, the first character of FirstName, and a period to form the Name
SELECT LastName || ',' ||' ' || SUBSTRING(FirstName, 1, 1) || '.'  AS Name, e.JobTitle  
-- From the Person.Person table aliased as 'p' and joined with the Employee table aliased as 'e'
FROM Person.Person AS p  
    JOIN HumanResources.Employee AS e  
    ON p.BusinessEntityID = e.BusinessEntityID  
-- Filtering records where the JobTitle starts with 'Vice'
WHERE e.JobTitle LIKE 'Vice%'  
-- Ordering the result set by LastName in ascending order
ORDER BY LastName ASC;

Explanation:

• This SQL query retrieves data from the Person and Employee tables.
• Comments are added to explain each part of the query for better understanding and maintenance.
• Here's a breakdown of what the query does:
• The SELECT clause concatenates the LastName, a comma, a space, the first character of the FirstName, and a period to form the Name column.
• The concatenation is achieved using the || operator in PostgreSQL.
• The SUBSTRING function extracts the first character of the FirstName.
• The FROM clause specifies that data is being retrieved from the Person.Person table aliased as 'p' and joined with the Employee table aliased as 'e' based on BusinessEntityID.
• The WHERE clause filters records where the JobTitle starts with 'Vice'.
• The ORDER BY clause arranges the result set by LastName in ascending order.

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.