AdventureWorks Database: Find all telephone numbers that have area code 415

SQL Query - AdventureWorks: Exercise-154 with Solution

154. From the following table write a query in SQL to find all telephone numbers that have area code 415. Returns the first name, last name, and phonenumber. Sort the result set in ascending order by lastname.

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

businessentityid|phonenumber        |phonenumbertypeid|modifieddate           |
----------------+-------------------+-----------------+-----------------------+
               1|697-555-0142       |                1|2009-01-07 00:00:00.000|
               2|819-555-0175       |                3|2008-01-24 00:00:00.000|
               3|212-555-0187       |                1|2007-11-04 00:00:00.000|
               4|612-555-0100       |                1|2007-11-28 00:00:00.000|
               5|849-555-0139       |                1|2007-12-30 00:00:00.000|
               6|122-555-0189       |                3|2013-12-16 00:00:00.000|
               7|181-555-0156       |                3|2009-02-01 00:00:00.000|
               8|815-555-0138       |                1|2008-12-22 00:00:00.000|
               9|185-555-0186       |                1|2009-01-09 00:00:00.000|
              10|330-555-2568       |                3|2009-04-26 00:00:00.000|
              11|719-555-0181       |                1|2010-11-28 00:00:00.000|
              12|168-555-0183       |                3|2007-12-04 00:00:00.000|
			  -- more --

Click to view Full table

Sample Solution:

-- Selecting first name, last name, and phone number from the PersonPhone and Person tables
SELECT 
    -- Selecting the FirstName column from the Person table
    p.FirstName, 
    -- Selecting the LastName column from the Person table
    p.LastName, 
    -- Selecting the PhoneNumber column from the PersonPhone table
    ph.PhoneNumber  
-- Joining the PersonPhone table with the Person table based on BusinessEntityID
FROM 
    Person.PersonPhone AS ph  
INNER JOIN 
    Person.Person AS p  
    ON ph.BusinessEntityID = p.BusinessEntityID  
-- Filtering records to include only those where the phone number starts with '415'
WHERE 
    ph.PhoneNumber LIKE '415%'  
-- Ordering the result set by last name
ORDER by 
    p.LastName;

Explanation:

• This SQL code retrieves first names, last names, and phone numbers of persons whose phone numbers start with '415'.
• The SELECT statement specifies the columns to be included in the result set.
• The FROM clause indicates the tables being used in the query, with aliases 'ph' for PersonPhone and 'p' for Person.
• The INNER JOIN clause joins the PersonPhone table with the Person table based on BusinessEntityID, ensuring that only matching records are included.
• The WHERE clause filters records to include only those with phone numbers starting with '415'.
• The ORDER BY clause sorts the result set by last name.

Sample Output:

firstname|lastname |phonenumber |
---------+---------+------------+
Ruben    |Alonso   |415-555-0124|
Shelby   |Cook     |415-555-0121|
Karen    |Hu       |415-555-0114|
David    |Long     |415-555-0123|
John     |Long     |415-555-0147|
Gilbert  |Ma       |415-555-0138|
Meredith |Moreno   |415-555-0131|
Alexandra|Nelson   |415-555-0174|
Taylor   |Patterson|415-555-0170|
Gabrielle|Russell  |415-555-0197|
Dalton   |Simmons  |415-555-0115|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find a match between salesorderheadersalesreason and SalesReason table.
Next: Identify people whose first name 'Gail' with area codes except 415.