AdventureWorks Database: Retrieve the rows where Adam is the firstname

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

173. From the following table write a query in SQL to find the rows where firstname doesn't differ from Adam's firstname. 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 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 not distinct from 'Adam'.
WHERE firstname IS NOT 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 not distinct from 'Adam'.
• The IS NOT DISTINCT FROM operator is used to check for equality while treating NULL values as equal.
• Finally, the results are ordered by the FirstName column in ascending order.
• This query retrieves records from the person table where the FirstName is either 'Adam' or NULL, ensuring that NULL values are considered equal to 'Adam', and orders the results alphabetically by FirstName.

Sample Output:

businessentityid|persontype|firstname|middlename|lastname  |
----------------+----------+---------+----------+----------+
             413|SC        |Adam     |          |Barr      |
            1679|VC        |Adam     |J.        |Reynolds  |
            5231|IN        |Adam     |          |Ross      |
            5234|IN        |Adam     |          |Henderson |
            5236|IN        |Adam     |M         |Jenkins   |
            5238|IN        |Adam     |          |Perry     |
            5239|IN        |Adam     |          |Powell    |
            5240|IN        |Adam     |          |Long      |
            5242|IN        |Adam     |M         |Patterson |
            5243|IN        |Adam     |          |Hughes    |
            5244|IN        |Adam     |L         |Flores    |
            5245|IN        |Adam     |G         |Washington|
            5246|IN        |Adam     |          |Butler    |
            5248|IN        |Adam     |          |Simmons   |
...

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Return rows with different firstname values from Adam.
Next: Find the rows where middlename differs from NULL.