AdventureWorks Database: Form one long string to display the last and first name
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 --
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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.