AdventureWorks Database: Identify people whose first name 'Gail' with area codes except 415

SQL Query - AdventureWorks: Exercise-155 with Solution

155. From the following tables write a query in SQL to identify all people with the first name 'Gail' with area codes other than 415. Return first name, last name, telephone number. Sort the result set in ascending order on 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 does not start with '415' and the first name is 'Gail'
WHERE 
    ph.PhoneNumber NOT LIKE '415%' 
    AND p.FirstName = 'Gail'  
-- 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 do not start with '415' and whose first name is 'Gail'.
• 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 where the phone number does not start with '415' and the first name is 'Gail'.
• The ORDER BY clause sorts the result set by last name.

Sample Output:

firstname|lastname |phonenumber        |
---------+---------+-------------------+
Gail     |Alexander|1 (11) 500 555-0120|
Gail     |Butler   |1 (11) 500 555-0191|
Gail     |Erickson |849-555-0139       |
Gail     |Erickson |834-555-0132       |
Gail     |Griffin  |450-555-0171       |
Gail     |Moore    |155-555-0169       |
Gail     |Russell  |334-555-0170       |
Gail     |Westover |305-555-0100       |

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Find all telephone numbers that have area code 415.
Next: Find all Silver colored bicycles with a standard price under $400.