AdventureWorks Database: Return rows with different firstname values from Adam

SQL Query - AdventureWorks: Exercise-172 with Solution

172. From the following table write a query in SQL to return the rows with different firstname values from Adam. Return businessentityid, persontype, firstname, middlename,and lastname. Sort the result set in ascending order on firstname.

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:

-- This SQL query selects specific columns from the person.person table, applying a condition to filter the results based on distinct values in the FirstName column.
-- Selecting columns: BusinessEntityID, PersonType, FirstName, MiddleName, and LastName.
SELECT businessentityid, persontype, firstname, middlename, lastname
-- From the person.person table.
FROM person.person
-- Filtering the results where the FirstName column is distinct from 'Adam'.
WHERE firstname IS DISTINCT FROM 'Adam'
-- Ordering the results by the FirstName column in ascending order.
ORDER BY firstname;

Explanation:

• The query selects several columns from the person.person table: BusinessEntityID, PersonType, FirstName, MiddleName, and LastName.
• It filters the results to include only rows where the FirstName column is different from 'Adam'.
• The IS DISTINCT FROM operator is used to check for inequality while treating NULL values as distinct.
• Finally, the results are ordered by the FirstName column in ascending order.
• This query retrieves records from the person table where the FirstName is not 'Adam', ensuring that each selected FirstName value is distinct, and orders the results alphabetically by FirstName.

Sample Output:

businessentityid|persontype|firstname               |middlename      |lastname              |
----------------+----------+------------------------+----------------+----------------------+
            2321|GC        |A.                      |Scott           |Wright                |
            1305|SC        |A.                      |Francesca       |Leonetti              |
             222|EM        |A. Scott                |                |Wright                |
            5495|IN        |Aaron                   |J               |Hughes                |
            5504|IN        |Aaron                   |                |Bryant                |
            5494|IN        |Aaron                   |W               |Patterson             |
           16664|IN        |Aaron                   |                |Hill                  |
            5508|IN        |Aaron                   |                |Alexander             |
            5509|IN        |Aaron                   |                |Russell               |
             727|SC        |Aaron                   |                |Con                   |
            5512|IN        |Aaron                   |                |Griffin               |
            5514|IN        |Aaron                   |C               |Diaz                  |
            5515|IN        |Aaron                   |A               |Hayes                 |
            5516|IN        |Aaron                   |A               |Zhang                 |
...	

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Customers with BirthDate after January 1, 1970 and last name Smith.
Next: Retrieve the rows where Adam is the firstname.