AdventureWorks Database: List people with three-letter first names ending in 'an'

Last update on December 20 2024 11:57:07 (UTC/GMT +8 hours)

158. From the following table write a query in SQL to list all people with three-letter first names ending in 'an'. Sort the result set in ascending order on first name. Return first name and last name.

Sample table: Person.Person

businessentityid|persontype|namestyle|title|firstname               |middlename      |lastname              |suffix|emailpromotion|additionalcontactinfo|demographics|rowguid                             |modifieddate           |
----------------+----------+---------+-----+------------------------+----------------+----------------------+------+--------------+---------------------+------------+------------------------------------+-----------------------+
               1|EM        |false    |     |Ken                     |J               |Sánchez               |      |             0|                     |[XML]       |92c4279f-1207-48a3-8448-4636514eb7e2|2009-01-07 00:00:00.000|
               2|EM        |false    |     |Terri                   |Lee             |Duffy                 |      |             1|                     |[XML]       |d8763459-8aa8-47cc-aff7-c9079af79033|2008-01-24 00:00:00.000|
               3|EM        |false    |     |Roberto                 |                |Tamburello            |      |             0|                     |[XML]       |e1a2555e-0828-434b-a33b-6f38136a37de|2007-11-04 00:00:00.000|
               4|EM        |false    |     |Rob                     |                |Walters               |      |             0|                     |[XML]       |f2d7ce06-38b3-4357-805b-f4b6b71c01ff|2007-11-28 00:00:00.000|
               5|EM        |false    |Ms.  |Gail                    |A               |Erickson              |      |             0|                     |[XML]       |f3a3f6b4-ae3b-430c-a754-9f2231ba6fef|2007-12-30 00:00:00.000|
               6|EM        |false    |Mr.  |Jossef                  |H               |Goldberg              |      |             0|                     |[XML]       |0dea28fd-effe-482a-afd3-b7e8f199d56f|2013-12-16 00:00:00.000|
               7|EM        |false    |     |Dylan                   |A               |Miller                |      |             2|                     |[XML]       |c45e8ab8-01be-4b76-b215-820c8368181a|2009-02-01 00:00:00.000|
               8|EM        |false    |     |Diane                   |L               |Margheim              |      |             0|                     |[XML]       |a948e590-4a56-45a9-bc9a-160a1cc9d990|2008-12-22 00:00:00.000|
               9|EM        |false    |     |Gigi                    |N               |Matthew               |      |             0|                     |[XML]       |5fc28c0e-6d36-4252-9846-05caa0b1f6c5|2009-01-09 00:00:00.000|
			   -- more --

Click to view Full table

Sample Solution:

-- Selecting first name and last name from the Person table
SELECT 
    -- Selecting the FirstName column from the Person table
    FirstName, 
    -- Selecting the LastName column from the Person table
    LastName  
-- From the Person table
FROM 
    Person.Person  
-- Filtering records to include only those where the first name ends with 'an' and is preceded by one character (wildcard '_' matches any single character)
WHERE 
    FirstName LIKE '_an'  
-- Ordering the result set by first name
ORDER BY 
    FirstName;

Explanation:

This SQL code retrieves the first name and last name of persons whose first name ends with 'an' and is preceded by one character.
The SELECT statement specifies the columns to be included in the result set.
The FROM clause indicates the table from which data is being retrieved, which is the Person table.
The WHERE clause filters records to include only those where the first name meets the specified pattern using the LIKE operator with the wildcard '_' to match any single character before 'an'.
The ORDER BY clause sorts the result set by first name.

Sample Output:

firstname|lastname  |
---------+----------+
Dan      |Bacon     |
Dan      |Wilson    |
Dan      |Wilson    |
Dan      |Bacon     |
Dan      |Baker     |
Dan      |Moyer     |
Dan      |Wilson    |
Ian      |Stewart   |
Ian      |Yuhasz    |
Ian      |Brooks    |
Ian      |Kelly     |
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Identify Quality Assurance personnel who work evenings or nights.
Next: Convert order date in the 'America/Denver' time zone.